Use case examples – Load Excel into Pandas DataFrames and Exporting Pandas Dataframes to Excel
Excel 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.
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: 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