Merge two dataframes using pandas

Question

I have two dataframes

df1:

Person    Value
Jack        6
Jill        9
Sam         4
Tony        3


df2:

Home    Away
Jill    Sam
Tony    Jack

I wish to make a 3rd dataframe which grabs the number in the Value column from df1 and matches them to the names in df2

Goal output:

df3:

Home    Away    Value1    Value2
Jill    Sam       9         4
Tony    Jack      3         6

Is there a simple merge function I can perform on this?

Thanks

Can you please post the dataframes in machine readable code, and what have you tried so far? — Nils Werner
– Nils Werner, Commented Oct 23, 2019 at 8:24

jezrael · Accepted Answer · 2019-10-23 08:36:16Z

2

Use Series.map for both columns:

s = df1.set_index('Person')['Value']

df2['Value1'] = df2['Home'].map(s)
df2['Value2'] = df2['Away'].map(s)

Or use DataFrame.assign for new DataFrame:

s = df1.set_index('Person')['Value']
df3 = df2.assign(Value1 = df2['Home'].map(s),
                 Value2 = df2['Away'].map(s))
print (df3)
   Home  Away  Value1  Value2
0  Jill   Sam       9       4
1  Tony  Jack       3       6

Solution with double DataFrame.merge and rename columns:

df3 = (df2.merge(df1.rename(columns={'Person':'Home','Value':'Value1'}), how='left')
          .merge(df1.rename(columns={'Person':'Away','Value':'Value2'}), how='left'))
print (df3)
   Home  Away  Value1  Value2
0  Jill   Sam       9       4
1  Tony  Jack       3       6

edited Oct 23, 2019 at 8:36

answered Oct 23, 2019 at 8:26

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge two dataframes using pandas

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related