How to read an excel file with multiple sheets using for loop in python

Question

This is what i try

from pathlib import Path
loc = Path('D:\DataSciSpec\Practice\Forloopindict.xlsx')
dict = pd.read_excel(loc,sheetname = None)

for i in dict.keys():
    print(i)

I get the name of sheets

Sheet4
Sheet3
Sheet2
Sheet1

I can also see the sheet content one by one

for i in dict.keys():
    print(dict[i].head())

But how put this data in n data frames (equal to no of sheets) and then append one to another

MarLeo · Accepted Answer · 2019-06-06 16:05:26Z

2

This will create a single dataframe (df_full) with the data from all sheets.

import pandas as pd

loc = r'D:\DataSciSpec\Practice\Forloopindict.xlsx'
workbook = pd.read_excel(loc,sheet_name = None)
df_full = pd.DataFrame()
for _, sheet in workbook.items():
    df_full = df_full.append(sheet)

# Reset index or you'll have duplicates
df_full = df_full.reset_index(drop=True)

edited Jun 6, 2019 at 16:05

answered Jun 6, 2019 at 15:44

MarLeo

2,6962 gold badges20 silver badges16 bronze badges

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

2 Comments

ShapeOfMatter Over a year ago

I don't think this would constitute a separate answer, because I'd just be copy-pasting a lot of your code. That said, for _, sheet in workbook.items(): df_full = df_full.append(sheet) would be the most normal, and possibly slightly more efficient, way of doing it.

Gupta Over a year ago

Thanks a ton ! learnt that "_" is a variable that is not used

Collectives™ on Stack Overflow

How to read an excel file with multiple sheets using for loop in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related