Load Excel into Pandas DataFrames and Export to Excel

Use cases for code example

Use case examples – Load Excel into Pandas DataFrames and Exporting Pandas Dataframes to Excel

Load excel into Pandas DataFramesExcel is used in many organisations. It is VERY common to find data stored and captured in Excel files. Hence, we are sometimes faced with the situation where our raw data is in Excel and we need to load Excel into Pandas Dataframe for analysis.

Exporting Pandas dataframes to support Charting & Financial analysis in Excel
You can also export Pandas Dataframe data into Excel. This is particularly useful as it allows one to use the exported data for charting and analysis in Excel.

Sample Dataset for this example

You can download the following Excel file for this example.
The file contains a list of International Football Teams and their associated confederations.

Walkthrough and explanation of code

Walkthrough of code – Load Excel into Pandas Dataframes and Export Dataframes to Excel

In our example code, we will be performing the following:

  • load the Pandas library, allowing us to create and use Pandas DataFrame functions
  • loading the libraries ExcelFile and ExcelWriter, these are the libraries that allow us to perform the reading and exporting in Excel functions
  • Read our excel file confed.xlsx into our Pandas Dataframe, df_teams and listing the first 5 records using the head() statement
  • Export the same Pandas Dataframe into an Excel file which we name as export_df_teams.xlsx

Example code

Example Code: Load Excel into Pandas Dataframes and exporting to Excel

Install the ExcelWriter libraries

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile

Load Excel into Pandas DataFrame

You can download the following Excel file for this example.

The file contains a list of International Football Teams and their associated confederations.

#read excel file confed.xlsx
#identify worksheet for pandas to read, in this example the worksheet name is Teams

#load data into dataframe called df_teams

df_teams =  pd.read_excel('confed.xlsx', sheetname='Teams')

#get 1st 5 rows in your df_teams dataframe

df_teams.head()

 

Export your pandas dataframe to Excel

You can also export your Pandas Dataframe to Excel. We export our newly created Pandas Dataframe df_teams to an Excel file called export_df_teams.xlsx

#save the file as export_df_teams.xlsx 
writer = ExcelWriter('export_df_teams.xlsx')

#export the df_teams dataframe to the file you have specified and create worksheet named 'Export'
df_teams.to_excel(writer,'Export')
writer.save()

See below to view what the Excel file looks like

Exported Pandas Dataframe in Excel

Exported File from Pandas

Was this article helpful?

Related Articles