Pandas: Merge multiple DataFrames using one common column

Question

I'm working with multiple dataframes each of which has one column in common, which is post_id. An example of how each df looks like:

  post_id   post_likes  
  0          1
  1          2
  2          3
  3          4
  4          5
  5          6

So each df has one column, that has post_id, but also another column such as likes, total count, names etc. in each consequent df. Is there any way I can group all these dfs into one based on post_id because my ultimate goal is to write this data frame into a csv.

score 1 · Accepted Answer · answered Apr 14 '17 at 01:42

suppose I have a long list of dataframes all of which have a column labeled post_id and one other column.

lodf = [df1, df2, df3, df4, df5]

You can put them all together with pd.concat. You just have to set the index first

df = pd.concat([d.set_index('post_id') for d in lodf], axis=1).reset_index()

demo

df1 = pd.DataFrame(dict(post_id=[1, 2, 3], col1=[1, 2, 3]))
df2 = pd.DataFrame(dict(post_id=[1, 2, 3], col2=[1, 2, 3]))
df3 = pd.DataFrame(dict(post_id=[1, 2, 3], col3=[1, 2, 3]))
df4 = pd.DataFrame(dict(post_id=[1, 2, 3], col4=[1, 2, 3]))
df5 = pd.DataFrame(dict(post_id=[1, 2, 3], col5=[1, 2, 3]))

lodf = [df1, df2, df3, df4, df5]

df = pd.concat([d.set_index('post_id') for d in lodf], axis=1).reset_index()
df

   post_id  col1  col2  col3  col4  col5
0        1     1     1     1     1     1
1        2     2     2     2     2     2
2        3     3     3     3     3     3

@wolverinejohn don't forget to accept the answer if it solved your problem. — piRSquared, Apr 14 '17 at 14:43

score 0 · Answer 2 · answered Apr 14 '17 at 00:45

import pandas as pd
df1 = pd.DataFrame(columns=['post_id','post_likes'], data={'post_id': range(6), 'post_likes': range(1,7)})
df2 = pd.DataFrame(columns=['post_id','post_shares'], data={'post_id': range(6), 'post_shares': range(11,17)})
pd.merge(df1,df2,on='post_id')

Out[12]:
   post_id  post_likes  post_shares
0        0           1           11
1        1           2           12
2        2           3           13
3        3           4           14
4        4           5           15
5        5           6           16

Pandas: Merge multiple DataFrames using one common column

2 Answers2