Read multiple excel files in order from folder with Python

Question

I have a folder with several excel files in the format xls and xlsx and I am trying to read them and concatenate them in one single Dataframe. The problem that I am facing is that python does not read the files in the folder in the correct order.

My folder contains the following files: 190.xls , 195.xls , 198.xls , 202.xlsx , 220.xlsx and so on

This is my code:

import pandas as pd
from pathlib import Path

my_path = 'my_Dataset/'

xls_files = pd.concat([pd.read_excel(f2) for f2 in Path(my_path).rglob('*.xls')], sort = False)

xlsx_files = pd.concat([pd.read_excel(f1) for f1 in Path(my_path).rglob('*.xlsx')],sort = False)

all_files = pd.concat([xls_files,xlsx_files],sort = False).reset_index(drop=True))

I get what I want but the FILES ARE NOT CONCATENATED IN ORDER AS THEY WERE IN THE FOLDER!!!!! meaning that in the all_files Dataframe I first have data from 202.xlsx and then from 190.xls

How can I solve this problem? Thank you in advance!

what if u read the xls_files, append the xlsx files to xls and pd.concat only once? that way, the xls files are surely before xlsx files and ur concat also happens only once, which should count for some efficiency. just a suggestion. — sammywemmy
– sammywemmy, Commented Feb 24, 2020 at 9:44
Instead of Path(my_path).rglob('*.xls') use sorted(Path(my_path).rglob('*.xls')) — Sayandip Dutta
– Sayandip Dutta, Commented Feb 24, 2020 at 9:45
You can create a loop with order you need first and in that loop do your pd.concat. — MEdwin
– MEdwin, Commented Feb 24, 2020 at 9:46

Rakesh · Accepted Answer · 2020-02-24 09:55:57Z

1

Try using

import pandas as pd
from pathlib import Path

my_path = 'my_Dataset/'
all_files = pd.concat([pd.read_excel(f) for f in sorted(list(Path(my_path).rglob('*.xls')) + list(Path(my_path).rglob('*.xlsx')), key=lambda x: int(x.stem))],sort = False).reset_index(drop=True)

answered Feb 24, 2020 at 9:55

Rakesh

82.9k17 gold badges85 silver badges122 bronze badges

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

3 Comments

stud_eco Over a year ago

I have a doubt! What If I have the files in the following order in the folder: 190.xls , 195.xlsx , 198.xls , 202.xlsx So what if the file that I want to be the second to be concatenated is in xlsx format thus different from the previous one? Would this code work anyway? Would the 195.xlsx file put after 190xlsx or after 198.xls?

Rakesh Over a year ago

Try print(sorted(list(Path(my_path).rglob('*.xls')) + list(Path(my_path).rglob('*.xlsx')), key=lambda x: int(x.stem))) to check the result

stud_eco Over a year ago

Great1!! IT WORKKSSS! Thank you very much for your help!

chris neilsen · Accepted Answer · 2020-02-24 09:56:05Z

0

Update this

all_files = pd.concat([xls_files,xlsx_files],sort = False).reset_index(drop=True))

to this

all_files = pd.concat([xlsx_files,xls_files],sort = False).reset_index(drop=True))

edited Feb 24, 2020 at 9:56

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

answered Feb 24, 2020 at 9:53

Yugesh

261 bronze badge

Collectives™ on Stack Overflow

Read multiple excel files in order from folder with Python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related