Read and concat multiple excel files based on a specific column

Question

path = '/Desktop/somefolder'
for filename in os.listdir(path):
    with open(path+filename) as f:
         - read the 3-4 excel files and attach the path
         - be able to concat them based on a specific column

filename gives me the name of the file I have in the directory. My idea was to concat the filename with the path to be able to read and concat them.

I am not sure how to use the filename that I get to be able to load it as a df and concat it.

Possible duplicate https://stackoverflow.com/questions/28405182/combine-multiple-workbooks-into-a-single-xlsx-workbook-using-python — Greg, Nov 10 '22 at 19:44

score 1 · Answer 1 · answered Nov 10 '22 at 19:52

1

Read the data into data frames

df1 = pd.read_excel('file1.xlsx')
df2 = pd.read_excel('file2.xlsx')

Create filtered data frames

df1Filtered = df1[df1["YourColumnName"].("YourColumnValues")
df2Filtered = df2[df2["YourColumnName"].("YourColumnValues")

Concat the filtered data frames

NewDF = pd.concat([df1Filtered, df2Filtered])

Write a new file to excel

NewDF.to_excel('NewFile.xlsx')

answered Nov 10 '22 at 19:52

SteveSchoepfer

11
3

Thank you for the solution, but this starts to get ugly if you have 15 to 16 excel files... – FalloutATS21 Nov 10 '22 at 19:52
1

You're welcome, and that is subjective, original post said 3-4 excel files so your mileage may vary. – SteveSchoepfer Nov 10 '22 at 20:00

score 1 · Accepted Answer · answered Nov 10 '22 at 19:55

1

Try:

import pandas as pd
import os

path = '/Desktop/somefolder/'

dfs = []
for filename in os.listdir(path):
    dfs.append(pd.read_excel(path+filename, engine='openpyxl'))

pd.concat(dfs, axis=0)

answered Nov 10 '22 at 19:55

Python16367225

121
6

I am getting an error : --> Unknown engine: @user16367225 – FalloutATS21 Nov 10 '22 at 20:01
Try installing `conda install -c anaconda openpyxl` in Anaconda Prompt in your environment. Make sure you install in it in the right environment. – Python16367225 Nov 10 '22 at 20:10

Read and concat multiple excel files based on a specific column

2 Answers2