Automating dataframe extracting from different workbook of excel file using python

Question

Below is the script that I am using

For reading file

excel = pd.ExcelFile('data.xlsx')

To get the different sheets I am using

excel.sheet_names

Let say the sheet_names are [A,B,C,D,E,F,G,H]. I am parsing each sheet_name to extract the data like this

df_A = excel.parse(sheet_name = 'A')
df_B = excel.parse(sheet_name = 'B')

Need to automate all dataframe creation in one go ? Any suggestion would be much appreciated!

Perhaps will be more efficient to store each sheet as a key in a dictionary, with excel_sheets = pd.read_excel(file_fullpath, sheetname=None), and access each sheet as a dataframe like this: excel_sheets['A'], excel_sheets['B'], instead of storing all different sheets as separate DF's. — sophocles
– sophocles, Commented Aug 2, 2021 at 13:17

sanzo213 · Accepted Answer · 2021-08-02 13:27:04Z

1

I think you it would be better to use loop with locals() see my code below. the reason why "locals()[a]" is used instead of a directly is that if you use "a=pd.read_excel()", only variable a will has recent sheet of data.xlsx, H sheet, and there would be no df_A, df_B, ... df_H variables

for i in pd.ExcelFile('data.xlsx').sheet_names:
 
    print(i)
    a='df_'+i
    locals()[a]=pd.read_excel('data.xlsx', sheet_name=i, header=0)
    print(locals()[a])

edited Aug 2, 2021 at 13:27

answered Aug 2, 2021 at 13:13

sanzo213

1494 bronze badges

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

Collectives™ on Stack Overflow

Automating dataframe extracting from different workbook of excel file using python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related