Read multiple excels according to the date and year you have

user_8969923 0
Viewed 87

How can I read multiple xlsx files in order and place them in the same dataframe?

Right now I have the following code which is giving me Error: No such file or directory: 'C:\\Users\\HN_1701.xlsx'

months=['01','02','03','04','05','06','07','08','09','10','11','12']

years=['21','20','19','18','17']

years=list(reversed(years))

for i in years:
    for j in months:
        
        
        print(r'C:\HN_'+j+i+'.xlsx')
        xls = pd.read_excel(r'C:\Users\HN_'+j+i+'.xlsx', sheet_name="ER", header=9)
pandas excel python
2 Answers

You could try something like this. Here, I do this for csv-files but it works the same with xlxs files. I look at a private folder but you can translate this to your own. First, define all the years, months and days you need. Create an empty df.

import pandas as pd
months=['04']
day = ['13','14','21']
years=['2021']

df = []
for years in years:
    for months in months:
        for day in day:
            path = r"C:\Users\k_sego\repos\wagon_report_weekly\Data_by_day\\" 
            filename = "{}all_vehicle_day_agg_{}-{}-{}.csv".format(path, years, months,day)
            print(filename)
            df.append(pd.read_csv(filename, sep=";"))

which returns:


C:\Users\k_sego\repos\wagon_report_weekly\Data_by_day\\all_vehicle_day_agg_2021-04-13.csv
C:\Users\k_sego\repos\wagon_report_weekly\Data_by_day\\all_vehicle_day_agg_2021-04-14.csv
C:\Users\k_sego\repos\wagon_report_weekly\Data_by_day\\all_vehicle_day_agg_2021-04-21.csv

and your dataframe.

user_53636860

You could go the regex way to get the list of all files that match like this:

import os
import re
file_list = [file for file in os.listdir('test/') if re.search(r'HN_(01|02|03|04|05|06|07|08|09|10|11|12).{2}(21|20|19|18|17)+\.xlsx$', file)]
for file in file_list:
    xls = pd.read_excel(f'C:\Users\{file}', sheet_name="ER", header=9)

Play with the regex and see what works best for you.

user_65738890
Related