Python Pandas: Join on unique column values and concatenate

concatenation, join, merge, pandas, python

Solution

There may be a better way with `concat`, but this should work:

In [48]: pd.merge(df1, df2, how='outer', on='id').merge(df3, how='outer', on='id')
Out[48]: 
      id  score_x  score_y  score
0    one       56       35    NaN
1    two       45      NaN     66
2  three       78      NaN    NaN
3   five      NaN       81     23
4   four      NaN       90    NaN
5    six      NaN      NaN     42

[6 rows x 4 columns]

To get your desired answer:

In [54]: merged = pd.merge(df1, df2, how='outer', on='id').merge(df3, how='outer', on='id')

In [55]: merged.set_index('id').rename(columns={'score_x': 'score(df1)', 'score_y': 'score(df2)
', 'score': 'score(df3)'})
Out[55]: 
       score(df1)  score(df2)  score(df3)
id                                       
one            56          35         NaN
two            45         NaN          66
three          78         NaN         NaN
five          NaN          81          23
four          NaN          90         NaN
six           NaN         NaN          42

[6 rows x 3 columns]

Problem

I have three Pandas dataframes, `df1`, `df2,` and `df3`, as below: ``` import pandas as pd import numpy as np df1 = pd.DataFrame({'id' : ['one', 'two', 'three'], 'score': [56, 45, 78]}) df2 = pd.DataFrame({'id' : ['one', 'five', 'four'], 'score': [35, 81, 90]}) df3 = pd.DataFrame({'id' : ['five', 'two', 'six'], 'score': [23, 66, 42]}) ``` How can I join these dataframes based on `id` and then concatenate their columns together? The desired output is as below: ``` #join_and_concatenate by id: id score(df1) score(df2) score(df3) one 56 35 NaN two 45 NaN 66 three 78 NaN NaN four NaN 90 NaN five NaN 81 23 six NaN NaN 42 ``` I found a relevant page that talks about `merge()`, `concatenate()` and `join()` but I am not sure any of these give what I want.

Original source