Merging multiple CSV files to one dataframe using Pandas

Question

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.

IoaTzimas · Accepted Answer · 2020-09-09 08:53:25Z

2

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)

edited Sep 9, 2020 at 8:53

answered Sep 8, 2020 at 19:18

IoaTzimas

10.7k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

raptorzee Over a year ago

TypeError: Can only merge Series or DataFrame objects, a <class 'str'> was passed I am getting this error

IoaTzimas Over a year ago

Strange, can you confirm that 'Monthly' (exactly) column is included in all dataframes?

raptorzee Over a year ago

is it because of my list: here is my list list= [ 'LA2017.csv', 'LA2018.csv', 'LA2019.csv', 'LA2020.csv', 'NY2016.csv', 'NY2017.csv', 'NY2018.csv', 'NY2019.csv', 'NY2020.csv', 'FL 2016.csv', 'FL 2017.csv', 'FL 2018.csv', 'FL 2019.csv', 'FL 2020.csv', 'TX 2016.csv', 'TX 2017.csv', 'TX 2018.csv', 'TX 2019.csv', 'TX 2020.csv', 'NJ 2016.csv', 'NJ 2017.csv', 'NJ 2018.csv', 'NJ 2019.csv', 'NJ 2020.csv']

IoaTzimas Over a year ago

List must include the dataframes. I changed the code, please check again.

Collectives™ on Stack Overflow

Merging multiple CSV files to one dataframe using Pandas

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related