CODEWITHSUNDEEP
pythonpandasdataframenumpy
chandan
chandan

Reputation: 93

How to create pandas dataframes with sheet name in one pass

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

Upvotes: 0

Views: 2978

Answers (2)

Shrey
Shrey

Reputation: 1260

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

Upvotes: 1

Sunday Ikpe
Sunday Ikpe

Reputation: 974

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

Upvotes: 0

Related Questions