Pandas loop through Excel sheets and append to df

Question

I am trying to loop through an Excel sheet and append the data from multiple sheets into a data frame.

So far I have:

master_df = pd.DataFrame()
for sheet in target_sheets:
    df1 = file.parse(sheet, skiprows=4) 
    master_df.append(df1, ignore_index=True)

But then when I call master_df.head() it returns __

The data on these sheets is in the same format and relate to each other.

So I would like to join them like this:

Sheet 1 contains:

A1
B1
C1

Sheet 2 contains:

A2
B2
C2

Sheet 3:

A3
B3
C3

End result:

A1
B1
C1
A2
B2
C2
A3
B3
C3

Is my logic correct or how can I achieve this?

Mayank Porwal · Accepted Answer · 2018-11-02 15:00:38Z

9

Below code will work even if you don't know the exact sheet_names in the excel file. You can try this:

import pandas as pd

xls = pd.ExcelFile('myexcel.xls')

out_df = pd.DataFrame()

for sheet in xls.sheet_names:
    df = pd.read_excel('myexcel.xls', sheet_name=sheet)
    out_df.append(df)  ## This will append rows of one dataframe to another(just like your expected output)

print(out_df)

## out_df will have data from all the sheets

Let me know if this helps.

edited Nov 2, 2018 at 15:00

answered Nov 2, 2018 at 14:53

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

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

2 Comments

Maverick Over a year ago

Thanks for this, it is useful, but I do know the sheet names I want in this instance.

Mayank Porwal Over a year ago

Sure. I just gave you a more general solution which does not involve any hardcoding of sheet-names.

rahlf23 · Accepted Answer · 2018-11-02 14:55:00Z

2

Simply use pd.concat():

pd.concat([pd.read_excel(file, sheet_name=sheet) for sheet in ['Sheet1','Sheet2','Sheet3']], axis=1)

For example, will yield:

   A1  B1  C1  A2  B2  C2  A3  B3  C3
0   1   2   3   1   2   3   1   2   3
1   4   5   6   4   5   6   4   5   6
2   7   8   9   7   8   9   7   8   9

answered Nov 2, 2018 at 14:55

rahlf23

9,0494 gold badges30 silver badges57 bronze badges

Comments

Blaine Mooers · Accepted Answer · 2022-03-30 16:04:03Z

0

The output desired in the question is obtained by setting axis=0.

import pandas as pd

df2 = pd.concat([pd.read_excel(io="projects.xlsx", sheet_name=sheet) for sheet in ['JournalArticles','Proposals','Books']], axis=0)
df2

answered Mar 30, 2022 at 16:04

Blaine Mooers

413 bronze badges

Collectives™ on Stack Overflow

Pandas loop through Excel sheets and append to df

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related