Append Columns From Multiple CSVs into One File - Python

33 views Asked by At

I am trying to use the Pandas library to append columns multiple CSV files of the same format into a single file, but cannot seem to get the syntax correct in my script. Here are the CSVs I am trying to parse, all located in the same file path, and the desired output csv file:

CSV #1:

Application, Classifier
Request, 14
Timeframe, 3
Adjudication, 10

CSV #2:

Application, Processor
Request, 15
Timeframe, 5
Adjudication, 20

CSV #3:

Application, Receiver
Request, 12
Timeframe, 10
Adjudication, 21

Desired CSV:

Application, Classifier, Processor, Receiver
Request, 14, 15, 12
Timeframe, 3, 5, 10
Adjudication, 10, 20, 21

Below is the code I am trying to implement to write to the desired CSV to a single file:

import os
import pandas as pd

path = 'C:\\Users\\mdl518\\Desktop\\'
extension = '.csv'

files = [file for file in os.listdir(path) if file.endswith(extension)]

dfs = []
for file in files:
    df = pd.read_csv(os.path.join(path, file))
    dfs.append(df)
    
df1 = pd.concat(dfs, ignore_index=True)
df2 = df1.apply(lambda x: pd.Series(x.dropna().values)).dropna() 
df2.to_csv('df_results.csv', index = False)

I feel there must be something small I am missing to append each column of the individual CSVs to a single output, any assistance is most appreciated!

0

There are 0 answers