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!