0

I have excel file with multiple sheets and I have to create dataframes for each sheet using pd.read_excel. Post creation of dataframe I'd like to set header, set_index and drop few rows.

Is it possible to write a function to perform above operations in one go and create dataframes as sheet name?

Trying with

df=pd.read_excel('file.xlsx',sheetname="",header=0,skiprows=[1,2,3])
James Z
  • 12,209
  • 10
  • 24
  • 44
chandan
  • 93
  • 1
  • 11

2 Answers2

1

You can have dictionary with keys as sheet names and their corresponding values as the dataframe. To achieve this, simply pass None to sheet_name inside the pandas.read_excel function.

df=pd.read_excel('file.xlsx',sheet_name=None,header=0,skiprows=[1,2,3])
print(type(df))
#--> <class 'dict'>
df.keys()
#--> dict_keys(['Sheet1', 'Sheet2']) # assuming Sheet1 and Sheet2 are the two sheet names of file.xlsx
print(type(df['Sheet1']))
#--> <class 'pandas.core.frame.DataFrame'>

As you wanted to have the variable names as sheet names, add below:

for key in df.keys():
    globals()[key] = df[key] # now you will have Sheet name as variables
print(type(Sheet1))
#--> <class 'pandas.core.frame.DataFrame'>

Let me know if this suffice your request

Shrey
  • 1,242
  • 1
  • 13
  • 27
  • Thanks Shrey for the answer. Even I had this in my mind. How do we assign Sheet1, Sheet2 as dataframes with unique name (not using df['Sheet1']) My requirement is to read excel and create dataframe for each sheet. – chandan Jan 31 '22 at 12:46
  • you want Sheet1 as a variable name ? – Shrey Jan 31 '22 at 13:32
0

I believe if you get the sheet names then you can build that function. look up Pandas: Looking up the list of sheets in an excel file

Sunday Ikpe
  • 924
  • 10
  • 17