CODEWITHSUNDEEP
pythonpandasdataframemerge
raptorzee
raptorzee

Reputation: 167

Merging multiple CSV files to one dataframe using Pandas

I have have 26 "CSV" files with file names similar to the below:

LA 2016
LA 2017
LA 2019
LA 2020
NY 2016
NY 2017
NY 2019
NY 2020

All the files have similar column names. The Column names are :

Month   A   B   C   Total
Jan    156  132 968 1256
Feb    863  363 657 1883
Mar    142  437 857 1436

I am trying to merge them all on Month. I tried pd.concat but for some reason the dataframe is not merging.

I am using the below code:

list=[]
city=['LA ','NY ','MA ','TX ']
year=['2016','2017','2018', '2019','2020']

for i in city:
 for j in year:
   list.append(i+j+".csv")

df=pd.concat([pd.read_csv(i) for i in list])

Can someone help me with this.

Upvotes: 0

Views: 332

Answers (1)

IoaTzimas
IoaTzimas

Reputation: 10624

The following should work:

from functools import reduce    
list_of_dataframes=[]
for i in list:
    list_od_dataframes.append(pd.read_csv(i))
df_final = reduce(lambda left,right: pd.merge(left,right,on='Month'), list_of_dataframes)

Upvotes: 2

Related Questions