CODEWITHSUNDEEP
pythonpython-3.xpandas
Maverick
Maverick

Reputation: 799

Pandas loop through Excel sheets and append to df

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?

Upvotes: 2

Views: 11949

Answers (3)

Blaine Mooers
Blaine Mooers

Reputation: 31

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

Upvotes: 0

Mayank Porwal
Mayank Porwal

Reputation: 34056

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.

Upvotes: 9

rahlf23
rahlf23

Reputation: 9019

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

Upvotes: 2

Related Questions