CODEWITHSUNDEEP
python-3.x
Ganesh M
Ganesh M

Reputation: 628

Python merging excel files in directory

I have thousands of files inside a directory with this pattern YYYY/MM/DD/HH/MM:

201901010000.xlsx
201901010001.xlsx,
201901010002.xlsx,
201801010000.xlsx,
201801010001.xlsx,
201801010002.xlsx,

I wants to merge file by begin with same YYYY(2018 & 2019 separate file) wise into one excel file.like below

this is first file

201901010000.xlsx,
201901010001.xlsx,
201901010002.xlsx,

this is second file

201801010000.xlsx,
201801010001.xlsx,
201801010002.xlsx,

Upvotes: 2

Views: 175

Answers (1)

crayxt
crayxt

Reputation: 2405

You will need to parse each file and concatenate by pandas:

import pandas as pd
import glob

my_path = "c:\\temp\\"

for year in ['2008', '2009']:
    buf = []
    year_files = glob.glob(my_path + year+"*.xlsx")
    for file in year_files:
        df = pd.read_excel(file)
        buf.append(df)
    year_df = pd.concat(buf)
    year_df.to_excel(year+".xlsx")

Upvotes: 1

Related Questions