By using the parameter sheet_name, and a list of names, we will get an ordered dictionary containing two dataframes:

df

Maybe we want to join the data from all sheets (in this case sessions). Merging Pandas dataframes are quite easy. We just use the concat function and loop over the keys (i.e., sheets):

df2 = pd.concat(df[frame] for frame in data.keys())

Now in the example Excel file there is a column identifying the dataset (e.g., session number). However, maybe we don’t have that kind of information in our Excel file. To merge the two dataframes and adding a column depicting which session we can use a for loop:

dfs = []
for framename in data.keys():
    temp_df = data[framename]
    temp_df['Session'] = framename
    dfs.append(temp_df)
    
df = pd.concat(dfs)

In the code above we start by creating a list and continue by looping through the keys in the list of dataframes. Finally, we create a temporary dataframe and take the sheet name and add it in the column ‘Session’.

Pandas Read Excel all Sheets

If we want to use read_excel to load all sheets from an Excel file to a dataframe it is, of ourse, possible. We can set the parameter sheet_name to None. 

all_sheets_df = pd.read_excel('example_sheets1.xlsx', sheet_name=None)

Reading Many Excel Files

In this section we will learn how to load many files into a Pandas dataframe because, in some cases, we may have a lot of Excel files containing data from, let’s say, different experiments. In Python we can use the modules os and fnmatch to read all files in a directory. Finally, we use list comprehension to use read_excel on all files we found:

import os, fnmatch
xlsx_files = fnmatch.filter(os.listdir('.'), '*concat*.xlsx')

dfs = [pd.read_excel(xlsx_file) for xlsx_file in xlsx_files]

If it makes sense we can, again, use the function concat to merge the dataframes:

df = pd.concat(dfs, sort=False)

There are other methods to reading many Excel files and merging them. We can, for instance, use the module glob:

import glob
list_of_xlsx = glob.glob('./*concat*.xlsx') 
df = pdf.concat(list_of_xlsx)

Setting the Data type for data or columns

We can also, if we like, set the data type for the columns. Let’s read the example_sheets1.xlsx again. In the Pandas read_excel example below we use the dtype parameter to set the data type of some of the columns.

df = pd.read_excel('example_sheets1.xlsx',sheet_name='Session1',
                   header=1,dtype={'Names':str,'ID':str,
                                        'Mean':int, 'Session':str})

We can use the method info to see the what data types the different columns have:

df.info()

Writing Pandas Dataframes to Excel

Excel files can, of course, be created in Python using the module Pandas. In this section of the post we will learn how to create an excel file using Pandas. We will start by creating a dataframe with some variables but first we start by importing the modules Pandas:

import pandas as pd

The next step is to create the dataframe. We will create the dataframe using a dictionary. The keys will be the column names and the values will be lists containing our data:

df = pd.DataFrame({'Names':['Andreas', 'George', 'Steve',
                           'Sarah', 'Joanna', 'Hanna'],
                  'Age':[21, 22, 20, 19, 18, 23]})

Then we write the dataframe to an Excel file using the *to_excel* method. In the Pandas to_excel example below we don’t use any parameters.

df.to_excel('NamesAndAges.xlsx')

In the output below the effect of not using any parameters is evident. If we don’t use the parameter sheet_name we get the default sheet name, ‘Sheet1’. We can also see that we get a new column in our Excel file containing numbers. These are the indices from the dataframe.

If we want our sheet to be named something else and we don’t want the index column we can do like this:

df.to_excel('NamesAndAges.xlsx', sheet_name='Names and Ages', index=False)

Writing Multiple Pandas Dataframes to an Excel File:

If we happen to have many dataframes that we want to store in one Excel file but on different sheets we can do this easily. However, we need to use ExcelWriter now:

df1 = pd.DataFrame({'Names': ['Andreas', 'George', 'Steve',
                           'Sarah', 'Joanna', 'Hanna'],
                   'Age':[21, 22, 20, 19, 18, 23]})

df2 = pd.DataFrame({'Names': ['Pete', 'Jordan', 'Gustaf',
                           'Sophie', 'Sally', 'Simone'],
                   'Age':[22, 21, 19, 19, 29, 21]})

df3 = pd.DataFrame({'Names': ['Ulrich', 'Donald', 'Jon',
                           'Jessica', 'Elisabeth', 'Diana'],
                   'Age':[21, 21, 20, 19, 19, 22]})

dfs = {'Group1':df1, 'Group2':df2, 'Group3':df3}
writer = pd.ExcelWriter('NamesAndAges.xlsx', engine='xlsxwriter')

for sheet_name in dfs.keys():
    dfs[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
    
writer.save()

In the code above we create 3 dataframes and then we continue to put them in a dictionary. Note, the keys are the sheet names and the cell names are the dataframes. After this is done we create a writer object using the xlsxwriter engine. We then continue by looping through the keys (i.e., sheet names) and add each sheet. Finally, the file is saved. This is important as leaving this out will not give you the intended results.

Summary: How to Work Excel Files using Pandas

That was it! In this post we have learned a lot! We have, among other things, learned how to:

• Read Excel files and Spreadsheets using read_excel
  • Load Excel files to dataframes:
    • Read Excel sheets and skip rows
    • Merging many sheets to a dataframe
    • Loading many Excel files into one dataframe
• Write a dataframe to an Excel file
• Taking many dataframes and writing them to one Excel file with many sheets

Leave a comment below if you have any requests or suggestions on what should be covered next! Check the post A Basic Pandas Dataframe Tutorial for Beginners to learn more about working with Pandas dataframe. That is, after you have loaded them from a file (e.g., Excel spreadsheets)