Overview:
- A pandas DataFrame is a two dimensional container suitable for processing huge volume of matrix-like data in-memory. Pandas library uses the ndarray as its underlying data structure.
- Excel is a popular spreadsheet format, which helps manipulating data in two dimensions.
- The pandas DataFrame class has an instance method to_excel() that exports the contents of a DataFrame object into an excel file.
- Using the to_excel() method either an Excel workbook with single sheet or multiple sheets can be created.
- The pre-requisite for the to_excel() method is to have the openpyxl module installed if the file extension is .xlsx. If the target excel file is of the extension .xls the pre-requisite is to have the module xlwt installed in the system.
- The module openpyxl can be installed using the command
- pip install openpyxl
- The module xlwt can be installed using the command
- pip install xlwt
Example – Exporting a DataFrame to an Excel file(.xlsx extension):
|
# Example Python program that writes a pandas DataFrame to an excel file import pandas as pds
# A Python dictionary dateVsExpense = {"1/11/2019": [50,40,13], "2/11/2019": [40,20,10], "3/11/2019": [5,90,None], "4/11/2019": [3, 43, 100], "5/11/2019": [20, 35, 45] };
# Create a DataFrame from the dictionary df = pds.DataFrame(dateVsExpense); print("Contents of the DataFrame:"); print(df);
# Write the DataFrame to an excel file df.to_excel("./Expense.xlsx");
|
Output:
|
Contents of the DataFrame: 1/11/2019 2/11/2019 3/11/2019 4/11/2019 5/11/2019 0 50 40 5.0 3 20 1 40 20 90.0 43 35 2 13 10 NaN 100 45 |
Pandas DataFrame Exported into an Excel File:
Example - Exporting multiple pandas DataFrames into multiple worksheets of an Excel file(.xls extension):
|
# Example Python program that saves multiple pandas DataFrames # into multiple excel worksheets import pandas as pds
# Path of the Excel file excelPath = "./multidf.xls";
# A matrix as list of tuples matrix1 = [(0, 0, 1), (0, 1, 0), (1, 0, 0)];
# A matrix as a dictionary matrix2 = {"11/12/2019":(140.1,142), "12/12/2019":(141.2,143), "13/12/2019":(131.5,133) };
# Matrices as DataFrames dataFrameA = pds.DataFrame(matrix1); dataFrameB = pds.DataFrame(matrix2);
# Create a customized excel writer and use it with pds.ExcelWriter(excelPath, date_format='YYYY-MM-DD') as exwriter: dataFrameA.to_excel(exwriter, sheet_name='One'); dataFrameB.to_excel(exwriter, sheet_name='Two'); exwriter.save(); |
