CODEWITHSUNDEEP
pythonpandasnumpy
CodeGeek123
CodeGeek123

Reputation: 4501

Merging Two Dataframes on Different Columns

Merging two dataframes on two different columns and rename the other two columns.

 df1
 Male    height 
 Alex    156
 Philip  178

 df2
 Female  height
 Alex    144
 Janice  150

I want a dataframe like this

 df3
 Person   M_Height  F_Height
 Alex      156        144 
 Philip    178         0
 Janice     0         150

How do i achieve this? Merge gives me both columns separately if i say right on and left on.

Upvotes: 1

Views: 144

Answers (3)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

In [21]: pd.merge(df1.rename(columns={'Male':'Person'}),
                  df2.rename(columns={'Female':'Person'}),
    ...:          on='Person', how='outer', suffixes=['_M','_F']) \
    ...:   .fillna(0)
Out[21]:
   Person  height_M  height_F
0    Alex     156.0     144.0
1  Philip     178.0       0.0
2  Janice       0.0     150.0

Upvotes: 4

BENY
BENY

Reputation: 323226

Or you can try this to fix your code (Merge gives me both columns separately if i say right on and left on)

df=df1.merge(df2,left_on='Male',right_on='Female',how='outer',suffixes=['_M','_L'])
df.assign(Person=(df[['Male','Female']].ffill(axis=1)).Female).drop(['Male','Female'],axis=1).fillna(0)

Out[890]: 
   height_M  height_L  Person
0     156.0     144.0    Alex
1     178.0       0.0  Philip
2       0.0     150.0  Janice

Upvotes: 3

Elrond
Elrond

Reputation: 2112

The first step is to change the column names to be non-overlapping

df1.columns = ['M_Height']
df2.columns = ['F_Height']

The second step is to merge the dataframes.

df = pd.merge([df1, df2], how='outer')

Upvotes: 0

Related Questions