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:

Excel file created by exporting a pandas DataFrame

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

 

 

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


Copyright 2024 © pythontic.com