Last Updated on July 14, 2022 by Jay
In the previous post, we touched on how to read an Excel file into Python. Here we’ll attempt to read multiple Excel sheets (from the same file) with Python pandas. We can do this in two ways: use pd.read_excel() method, with the optional argument sheet_name; the alternative is to create a pd.ExcelFile object, then parse data from that object.
pd.read_excel() method
In the below example:
- Select sheets to read by index:
sheet_name = [0,1,2]means the first three sheets. - Select sheets to read by name:
sheet_name = ['User_info', 'compound']. This method requires you to know the sheet names in advance. - Select all sheets: sheet_name = None.
import pandas as pd
df = pd.read_excel('users.xlsx', sheet_name = [0,1,2])
df = pd.read_excel('users.xlsx', sheet_name = ['User_info','compound'])
df = pd.read_excel('users.xlsx', sheet_name = None) # read all sheetsWe will read all sheets from the sample Excel file, then use that dataframe for the examples going forward.
The df returns a dictionary of dataframes. The keys of the dictionary contain sheet names, and values of the dictionary contain sheet content.
>>> df.keys()
dict_keys(['User_info', 'purchase', 'compound', 'header_row5'])
>>> df.values()
dict_values([ User Name Country City Gender Age
0 Forrest Gump USA New York M 50
1 Mary Jane CANADA Tornoto F 30
2 Harry Porter UK London M 20
3 Jean Grey CHINA Shanghai F 30,
ID Customer purchase Date
0 101 Forrest Gump Dragon Ball 2020-08-12
1 102 Mary Jane Evangelion 2020-01-01
2 103 Harry Porter Kill la Kill 2020-08-01
3 104 Jean Grey Dragon Ball 1999-01-01
4 105 Mary Jane Evangelion 2019-12-31
5 106 Harry Porter Ghost in the Shell 2020-01-01
6 107 Jean Grey Evangelion 2018-04-01,
....
]To obtain data from a specific sheet, simply reference the key in the dictionary. For example, df['header_row5'] returns the sheet in which data starts from row 5.
>>> df['header_row5']
Unnamed: 0 Unnamed: 1 Unnamed: 2 Unnamed: 3
0 NaN NaN NaN NaN
1 NaN NaN NaN NaN
2 NaN NaN NaN NaN
3 ID Customer purchase Date
4 101 Forrest Gump Dragon Ball 2020-08-12 00:00:00
5 102 Mary Jane Evangelion 2020-01-01 00:00:00
6 103 Harry Porter Kill la Kill 2020-08-01 00:00:00
7 104 Jean Grey Dragon Ball 1999-01-01 00:00:00
8 105 Mary Jane Evangelion 2019-12-31 00:00:00
9 106 Harry Porter Ghost in the Shell 2020-01-01 00:00:00
10 107 Jean Grey Evangelion 2018-04-01 00:00:00pd.ExcelFile()
With this approach, we create a pd.ExcelFile object to represent the Excel file. We do not need to specify which sheets to read when using this method. Note that the previous read_excel() method returns a dataframe or a dictionary of dataframes; whereas pd.ExcelFile() returns a reference object to the Excel file.
f = pd.ExcelFile('users.xlsx')
>>> f
<pandas.io.excel._base.ExcelFile object at 0x00000138DAE66670>To get sheet names, we can all the sheet_names attribute from the ExcelFile object, which returns a list of the sheet names (string).
>>> f.sheet_names
['User_info', 'purchase', 'compound', 'header_row5']
To get data from a sheet, we can use the parse() method, and provide the sheet name.
>>> f.parse(sheet_name = 'User_info')
User Name Country City Gender Age
0 Forrest Gump USA New York M 50
1 Mary Jane CANADA Tornoto F 30
2 Harry Porter UK London M 20
3 Jean Grey CHINA Shanghai F 30One thing to note is that the pd.ExcelFile.parse() method is equivalent to the pd.read_excel() method, so that means you can pass in the same arguments used in read_excel().
Moving on…
We have learned how to read data from Excel or CSV files, next we’ll cover how to save a dataframe back into an Excel (or CSV) file.
One comment