The pandas DataFrame contents can be exported to an excel file using the to_excel() method of the DataFrame class. The method to_excel() internally makes use of the Python module openpyxl.
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)
python
# 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
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
Excel file created by exporting a pandas DataFrame
Example - Exporting multiple pandas DataFrames into multiple worksheets of an Excel file(.xls extension)
python
# 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();
Sheet 1 of the XLS file from the DataFrame A
Sheet1 of the Excel Sheet exported from ExcelWriter
Sheet 2 of the XLS file from the DataFrame B
Sheet2 of the Excel Sheet exported from ExcelWriter
Your privacy choices
We'd like to use Google Analytics cookies to count visits and see which tutorials are useful.
They are only set if you accept. We don't use advertising cookies and we don't sell your data.
You can change your choice any time from “Your privacy choices” at the bottom of every page.
Privacy Policy
Your privacy choices
Your browser sent a Global Privacy Control signal, so analytics is off unless you turn it on here.
Strictly necessary
Remembers this choice in your browser. No cookies, nothing sent to anyone.Always on