Load One or Multiple Sheets of Excel by Pandas sheet_name
We will show you how to use read_excel method of Pandas library to load:One excel sheet, Two Excel sheets annd All excel sheets
How to load multiple sheets in Excel using Python Pandas library
In this tutorial, we will show you how to use read_excel method of Pandas library to load:
- One excel sheet
- Two Excel sheets
- All excel sheets
From the specified Workbook.
Loading one excel worksheet example
By default, Pandas read_excel loads the first worksheet (active or not) from the specified Excel file.
For our examples, we have the following worksheets in our “test_Excel.xlsx” workbook:
Python program below loads the default sheet (one sheet) only by using read_excel method.
Code:
|
1 2 3 4 5 6 7 8 9 |
import pandas as pan_excel #Loading Single/Default sheet df_single = pan_excel.read_excel('test_Excel.xlsx') #Display Sheet's contents print(df_single) |
Output:
Loading two Sheets by sheet_name parameter example
As we know, read_excel method has many parameters and for the purpose of loading two sheets, our solution is using sheet_name.
By using sheet_name parameter, you may specify one or more sheets in the Workbook.
You may provide the sheet names as follows:
- sheet_name=[0,1,2…]
- sheet_name=[‘0,’Product Information’]
- sheet_name=[‘Sales’,’Product Information’]
So, the index number of the sheet starting from 0 can be given. Also, you may also use sheet names or mix of both as well.
We will use the third way of loading two worksheets and display their data in the example below.
Python program:
|
1 2 3 4 5 6 7 8 9 10 |
import pandas as pan_excel #Loading Two/Multiple sheets by names df_multiple = pan_excel.read_excel('test_Excel.xlsx',sheet_name=['Sales','Product Information']) #Display Sheets contents print(df_multiple) |
Output:
An important note: If sheet contains charts then it is not counted in the indexed-based position. We will show you in the example later.
Using sheet number and name example
Now we used sheet number (index starting at zero) and name in the example below to load two sheets from our workbook and then display data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
import pandas as pan_excel #Loading Two/Multiple sheets by index and name df_multiple = pan_excel.read_excel('test_Excel.xlsx',sheet_name=[2, 'Sales']) #Display Sheets contents print(df_multiple) |
Output:
The index position 2 represents the “Employee” sheet in our sample Workbook.
Loading all sheets example
For loading all sheets from the Workbook, just specify sheet_name = None:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
sheet_name=None and you are done. The program below loads all three sheets from our sample Workbook: import pandas as pan_excel #Loading All sheets without column index df_all = pan_excel.read_excel('test_Excel.xlsx',sheet_name=None, index_col=0) #Display All Sheets data print(df_all) |
Output:
In our sample sheets, you can see we have three sheets and all are loaded in the same sequence.
As we also specified the index_col parameter, so this time row number (or column index) is not displayed.
What if we have a sheet with charts?
For this example, we are adding another sheet in between the above sample sheets. It contains a chart for the demo only:
The date for the chart comes from “Sales” sheet.
As we fetch a program to get all sheets by Pandas read_excel, this is what we get the output:
|
1 2 3 4 5 6 7 8 9 |
import pandas as pan_excel #Loading All sheets including a chart sheet df_all_chart = pan_excel.read_excel('test_Excel.xlsx',sheet_name=None, index_col=0) #Display All Sheets data - what about chart sheet? print(df_all_chart) |
Output:
