How can I import several Excel sheets into separate pandas dataframes?

Question

I've reviewed this post: Pandas: Save multiple sheets into separate dataframes, however it doesn't seem to address my problem.

So this creates a dictionary with sheet names as keys, and dataframes as values:

sheets = pd.read_excel('Data Series.xlsx',sheet_name=None)

sheets["CPI"] outputs a dataframe.

What I want to do is assign that to its own dataframe like: df_CPI = sheets["CPI"], however I have about 10 sheets to do so I'd rather run it as a loop if possible. Something like

for sheet,dataframe in sheets.items():
   df_`sheet` = pd.read_excel(xls, sheet)

Corralien · Accepted Answer · 2022-03-22 23:22:09Z

1

To create dynamically variables, you have to use globals() or locals() (which is strongly discouraged). The dict version is better.

sheets = pd.read_excel('Data Series.xlsx',sheet_name=None)
for sheet, dataframe in sheets.items():
   globals()[f'df_{sheet}'] = dataframe

Now you can use df_CPI as a variable.

answered Mar 22, 2022 at 23:22

Corralien

121k8 gold badges43 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StCheese Over a year ago

Thanks for this. One follow up that I have, is would there be a "better" way to do this? The names of the df's don't need to correspond to the sheets exactly.

Collectives™ on Stack Overflow

How can I import several Excel sheets into separate pandas dataframes?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related