CODEWITHSUNDEEP
pythonpandasdataframe
AB14
AB14

Reputation: 407

Automating dataframe extracting from different workbook of excel file using python

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!

Upvotes: 0

Views: 146

Answers (1)

sanzo213
sanzo213

Reputation: 139

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])

Upvotes: 1

Related Questions