Exporting pandas DataFrame contents to Excel

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();