Python pandas - merge csv files in directory into one

Question

I have a directory with csv files:

frames/df1.csv
       df2.csv

frames are structured like this:

df1.csv

               artist            track        plays
1            Pearl Jam           Jeremy         456
2   The Rolling Stones   Heart of Stone         546

df2.csv

                artist            track        likes
3            Pearl Jam           Jeremy         5673
9   The Rolling Stones   Heart of Stone         3456

and I would like to merge all frames into one, ending up with:

              artist            track          plays       likes    
0          Pearl Jam           Jeremy            456        5673       
1 The Rolling Stones   Heart of Stone            546        3456

I've tried:

path = 'frames'
all_files = glob.glob(path + "/*.csv")
list_ = []
for file_ in all_files:
    df = pd.read_csv(file_,index_col=None, header=0)
    list_.append(df)
frame = pd.concat(list_)

to no avail. what is the best way to approach this?

BENY · Accepted Answer · 2017-10-15 04:36:23Z

2

I just simply using your code create the list of DataFrame

path = 'frames'
all_files = glob.glob(path + "/*.csv")
l= []
for file_ in all_files:
    df = pd.read_csv(file_,index_col=None, header=0)
    l.append(df)

Then using functools.reduce, merge the list dataframe into one

import functools
l= [df1, df2, df3....]
merged_df = functools.reduce(lambda left,right: pd.merge(left,right,on=['artist','track']), l)

answered Oct 15, 2017 at 4:36

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

avigil · Accepted Answer · 2017-10-15 04:35:58Z

0

DataFrame.join is useful. Its analogous to a SQL join. Something like:

df1.join(df2, on=('artist', 'track'))

answered Oct 15, 2017 at 4:35

avigil

2,25614 silver badges19 bronze badges

1 Comment

avigil Over a year ago

oh well join makes assumptions about indexing. merge mentioned in other solutions is the proper choice when you don't want to go about normalizing all the indexes for your dataframes.

Collectives™ on Stack Overflow

Python pandas - merge csv files in directory into one

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related