CODEWITHSUNDEEP
pythonpandas
Michael
Michael

Reputation: 351

Joining pandas dataframes based on date

I have two dataframes, df1 and df2

df1: 

Player_A    Player_B    Date
  Sam         Casey     10/4
  Steve       Sam       11/4
  Casey       Steve     12/4

df2: 

Player    Date    Count
 Sam      10/4      5
 Casey    10/4      3
 Steve    11/4      4
 Sam      11/4      4
 Casey    12/4      4
 Steve    12/4      5

I am aiming to add two new columns in df1, 'Count_A' and 'Count_B', which pulls out the count of each player from df2

So my output would be this:

Player_A    Player_B    Date    Count_A     Count_B
  Sam         Casey     10/4      5            3
  Steve       Sam       11/4      4            4
  Casey       Steve     12/4      4            5

I am trying merges and joins but can't quite get the parameters right

Any help is appreciated

Upvotes: 1

Views: 54

Answers (3)

ansev
ansev

Reputation: 30920

To do this for N types of players and not only A and B we can generalize in this way with Dataframe.melt

df1.columns=df1.columns.str.replace('Player','')
new_df= ( df1.melt('Date',var_name='Type_Player',value_name='Player')
             .merge(df2,on=['Date','Player'],how='left')
             .set_index(['Date','Type_Player'])
             .unstack('Type_Player')
             .reset_index() )
new_df.columns=[x+y for x,y in new_df.columns]
print(new_df)

   Date Player_A Player_B  Count_A  Count_B
0  10/4      Sam    Casey        5        3
1  11/4    Steve      Sam        4        4
2  12/4    Casey    Steve        4        5

Upvotes: 0

CypherX
CypherX

Reputation: 7353

An alternative is to use df.apply. 

import pandas as pd

df1['Count_A'] = df1.apply(lambda x: df2.Count[(df2.Player==x.Player_A) & (df2.Date==x.Date)].reset_index(drop=True), axis=1)[0].tolist()
df1['Count_B'] = df1.apply(lambda x: df2.Count[(df2.Player==x.Player_B) & (df2.Date==x.Date)].reset_index(drop=True), axis=1)[0].tolist()
df1

Upvotes: 0

Andy L.
Andy L.

Reputation: 25239

Use merge and map

df3 = df2.merge(df1, on='Date')
df1['Count_A'] = df1.Player_A.map(df3[df3.Player_A == df3.Player].set_index('Player_A')['Count'])
df1['Count_B'] = df1.Player_B.map(df3[df3.Player_B == df3.Player].set_index('Player_B')['Count'])

Out[697]:
  Player_A Player_B  Date  Count_A  Count_B
0      Sam    Casey  10/4        5        3
1    Steve      Sam  11/4        4        4
2    Casey    Steve  12/4        4        5

Upvotes: 2

Related Questions