0

I have two dataframes (df1 and df2) in pandas with both having different columns except for the first column. Following are the dataframes for example:

df1
----------------
c1    c2    c3
----------------
1     x     10
2     y     16
3     z     20

df2
----------------
c1    c4     c5
----------------
1     xx     30
2     ym     46
2     zq     50
3     xa     60
3     ys     16
4     zm     20

I want to merge the two df such that the resulting df looks like the following:

----------------------------
c1    c2    c3    c4     c5
----------------------------
1     x    10     xx     30
2     y    16     ym     46
2     y    16     zq     50
3     z    20     xa     60
3     z    20     ys     16
4                 zm     20

I have used the 

pd.merge(df1, df2, how='left') 
pd.merge(df1, df2, how='right') 
pd.merge(df1, df2, how='inner') 
pd.merge(df1, df2, how='outer')

but couldn't get the above-desired data frame. Can anybody help me with this problem? Thanks!

Ali Akbarpour
  • 958
  • 2
  • 18
  • 35
Hanif
  • 377
  • 4
  • 19
  • 2
    It seems like `pd.merge(df1, df2, how='right')` works perfectly. What's the problem with that? Alternatively, you may want to try `df1.merge(df2, on='c1', how='right')`. Check if your columns have the same data type. – cs95 Mar 06 '18 at 10:52

1 Answers1

2

I have checked below that a right merge works fine.

Please don't vote / accept this answer, as there are dozens of duplicates on this.

df1 = pd.DataFrame({'c1': {0: 1, 1: 2, 2: 3},
                    'c2': {0: 'x', 1: 'y', 2: 'z'},
                    'c3': {0: 10, 1: 16, 2: 20}})

df2 = pd.DataFrame({'c1': {0: 1, 1: 2, 2: 2, 3: 3, 4: 3, 5: 4},
                    'c4': {0: 'xx', 1: 'ym', 2: 'zq', 3: 'xa', 4: 'ys', 5: 'zm'},
                    'c5': {0: 30, 1: 46, 2: 50, 3: 60, 4: 16, 5: 20}})

result = pd.merge(df1, df2, how='right')

#    c1   c2    c3  c4  c5
# 0   1    x  10.0  xx  30
# 1   2    y  16.0  ym  46
# 2   2    y  16.0  zq  50
# 3   3    z  20.0  xa  60
# 4   3    z  20.0  ys  16
# 5   4  NaN   NaN  zm  20
jpp
  • 159,742
  • 34
  • 281
  • 339