sheet_name param on pandas.read_excel() is used to read multiple sheets from excel. This supports reading excel sheets by name or position. When you read multiple sheets, it creates a Dict of DataFrame, each key in Dictionary is represented as Sheet name and DF for Dict value.
pandas Read Excel Multiple Sheets
sheet_name param also takes a list of sheet names as values that can be used to read multiple sheets into pandas DataFrame. Not that while reading multiple sheets it returns a Dict of DataFrame. The key in Dict is a sheet name and the value would be DataFrame.
This param takes values str, int, list, or None, default 0. When None is used it reads all sheets from excel. By default, it is set to 0 meaning the first sheet.
Let’s see with an example, I have an excel file with two sheets named 'Technologies' and 'Schedule'.
import pandas as pd
# Read excel file with sheet name
dict_df = pd.read_excel('c:/apps/courses_schedule.xlsx',
sheet_name=['Technologies','Schedule'])
Since we are reading two sheets from excel, this function returns Dict of DataFrame. You can get the DataFrames from Dict as follows.
# Get DataFrame from Dict
technologies_df = dict_df .get('Technologies')
schedule_df = df.get('Schedule')
# Print DataFrame's
print(technologies_df)
print(schedule_df)
The 'Technologies' sheet is converted into DataFrame as follows. Note that empty values are converted into NaN on DataFrame.
# Output:
Courses Fee Duration Discount
0 Spark 25000 50 Days 2000
1 Pandas 20000 35 Days 1000
2 Java 15000 NaN 800
3 Python 15000 30 Days 500
4 PHP 18000 30 Days 800
And the 'Schedule' sheet is converted into DataFrame as follows.
# Output:
Courses Days Time
0 Spark MON, THU 7:00 AM to 9:00 AM
1 Pandas MON, WED 8:00 AM to 10:00 AM
2 Java WEN, FRI 7:00 PM to 9:00 PM
3 Python TUE, THU 6:00 PM to 8:00 PM
4 PHP WEN, THU 8:00 AM to 10:00 AM
Conclusion
In this article, you have learned how to read an excel with multiple sheets and convert it to pandas DataFrame. Since it returns a Dict of DataFrame, you have also learned how to get each DF from the dict.
Happy Learning !!
