Pandas - Reading multiple excel files into a single pandas Dataframe

Question

I am trying to read multiple excel files into a Pandas Dataframe.

I have got the below code in place:

allFiles = glob.glob(base2 + "/*.xls"). <<-- Reading from path where all files are stored
list_ = []
for file_ in allFiles:
    df = pd.read_excel(io.BytesIO(open(file_, 'rb').read()), sheet_name='Sheet1') <<-- using io module to read the file as there are some issue with the text format
    list_.append(df)

print(list_)
file = pd.DataFrame(list_)

When I open the Dataframe csv file, I see entire contents of a single file all in a single row. I am trying to have each row from the source file said in a separate row in the file output.

fuglede · Accepted Answer · 2019-12-11 10:09:43Z

1

Rather than create the pd.DataFrame based on the list, use pd.concat to concatenate them, i.e.

file = pd.concat(list_)

answered Dec 11, 2019 at 10:09

fuglede

18.3k3 gold badges62 silver badges107 bronze badges

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

1 Comment

fuglede Over a year ago

You're welcome. If you found the answer helpful, you can mark it as accepted.

Collectives™ on Stack Overflow

Pandas - Reading multiple excel files into a single pandas Dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related