Merge/concat two dataframe by cols-CodePudding

I have two data frame :

import pandas as pd
from numpy import nan
df1 = pd.DataFrame({'key':[1,2,3,4],
                    'only_at_df1':['a','b','c','d'],
                    'col2':['e','f','g','h'],})

df2 = pd.DataFrame({'key':[1,9],
                    'only_at_df2':[nan,'x'],
                    'col2':['e','z'],})

How to acquire this:

df3 = pd.DataFrame({'key':[1,2,3,4,9],
                    'only_at_df1':['a','b','c','d',nan],
                    'only_at_df2':[nan,nan,nan,nan,'x'],
                    'col2':['e','f','g','h','z'],})

any help appreciated.

CodePudding user response：

The best is probably to use combine_first after temporarily setting "key" as index:

df1.set_index('key').combine_first(df2.set_index('key')).reset_index()

output:

   key col2 only_at_df1 only_at_df2
0    1    e           a         NaN
1    2    f           b         NaN
2    3    g           c         NaN
3    4    h           d         NaN
4    9    z         NaN           x

CodePudding user response：

This seems like a straightforward use of merge with how="outer":

df1.merge(df2, how="outer")

Output:

   key only_at_df1 col2 only_at_df2
0    1           a    e         NaN
1    2           b    f         NaN
2    3           c    g         NaN
3    4           d    h         NaN
4    9         NaN    z           x