Pandas have a few limitations when working with multiple sheets in an Excel file. However, there is a small hack that can help us work around these limitations and make working with numerous sheets more efficient.
One of the challenges when working with multiple sheets in Pandas is keeping track of which sheet data is coming from. We can alleviate this issue by creating a dictionary of sheet names and DataFrames. We can reference the sheets by name instead of remembering which sheet number corresponds to which DataFrame.
Another limitation when working with multiple sheets in Pandas is that the read_excel() function can only read one sheet at a time. If we want to read data from various sheets, we must use the read_excel() function multiple times.
The trick to efficiently reading Excel files with multiple sheets in Pandas
We can work around this limitation by setting the sheet name argument to None in the read_excel function.
The above function creates a dictionary with sheet names in the Excel files as keys and dataframe as values. You can now access the dataframe with its sheet name.
Combining multiple sheets in an Excel file using Pandas
Often data scientists and analysts receive Excel files with multiple sheets. However, they are just a logical partitioning of a larger dataset in most cases.
For instance, a dataset is partitioned by month and stored in individual sheets rather than one big file.
It is also possible to conveniently combine sheets in an Excel file using the values method of the df_dict.
Please don’t loop through sheet names to read them
Before I learned this trick, I was, in fact, looping through the sheet names and reading them individually. The code snippet to do this would look like the one below.
This code does the job with not much difficulty. However, we can eliminate these extra lines of code by simply setting sheetname=None when reading the file.