What is the best way to map each row in a DF to a row from another DF after comparing the first row with multiple rows from the second DF?

user_16772906 0
Viewed 270

I have two DataFrames, One contains different dates and different player_id. The second one contains the history of each player's rating, which means it contains different dates and different player_id and player_rating with a unique id of each row.

Now, I want to add a column to the first DataFrame with the one value of the player_rating by comparing the date columns in the two DataFrames.

For example:

print(df_1)

   date  player_id
0  2006          1
1  2006          2
2  2012          3
3  2008          2
4  2010          1

print(df_2)

   date  player_id  player_rating
0  2005          1             50
1  2007          2             55
2  2005          3             70
3  2005          2             65
4  2009          1             60
5  2011          2             75
6  2011          3             85

Here, I should compare the first row in df_1 with the first and fifth rows in df_2 (the same player_id) then choose the row from df_2 with the most updated date and smaller than the date in df_1. So, I should choose the first row and set the player_rating to 50.

Finally, I should get:

print(df_1)
   date  player_id  player_rating
0  2006          1             50
1  2006          2             65
2  2012          3             85
3  2008          2             55
4  2010          1             60

I have tried many solutions and get the following idea but it takes much time to run relative to a huge dataset (about one million rows of df_1 and +100k rows of df_2):

def get_rating(date, player_id):
    return df_2[(df_2['player_id']==player_id) & (df_2['date'] < date)].sort_values(by=['date'], ascending=False).player_rating.iloc[0]
df_1['player_rating'] = [get_rating(x,y) for x, y in zip(df_1['date'], df_1['player_id'])]

If there is a better way to save some time here? Thank you in advance.

pandas dataframe
2 Answers

You might have to test for efficiency though:

(df1.merge(df2, on='player_id', how = 'left', suffixes=(None, '_y'))
    .sort_values(['date', 'date_y'])
    .query('date > date_y')
    .groupby(['date', 'player_id'])
    .tail(1)
    .drop(columns='date_y')
    .sort_index()
  )
 
     date  player_id  player_rating
0   2006          1             50
3   2006          2             65
6   2012          3             85
7   2008          2             55
11  2010          1             60
user_71757130

Here you can try to merge the two dataframe on player_id and then use your logic regarding dates.

Data

import pandas as pd
df1 = pd.DataFrame(
    {'date': {0: 2006, 1: 2006, 2: 2012, 3: 2008, 4: 2010},
     'player_id': {0: 1, 1: 2, 2: 3, 3: 2, 4: 1}})

df2 =  pd.DataFrame(
    {'date': {0: 2005, 1: 2007, 2: 2005, 3: 2005, 4: 2009, 5: 2011, 6: 2011},
     'player_id': {0: 1, 1: 2, 2: 3, 3: 2, 4: 1, 5: 2, 6: 3},
     'player_rating': {0: 50, 1: 55, 2: 70, 3: 65, 4: 60, 5: 75, 6: 85}})

Merge

df = pd.merge(df1, df2, on=["player_id"])

Where the data looks like

    date_x  player_id  date_y  player_rating
0     2006          1    2005             50
1     2006          1    2009             60
2     2010          1    2005             50
3     2010          1    2009             60
4     2006          2    2007             55
5     2006          2    2005             65
6     2006          2    2011             75
7     2008          2    2007             55
8     2008          2    2005             65
9     2008          2    2011             75
10    2012          3    2005             70
11    2012          3    2011             85

Filter

Now you ask for date_x bigger than date_y

df = df[df["date_x"].gt(df["date_y"])].reset_index(drop=True)

And you have


   date_x  player_id  date_y  player_rating
0    2006          1    2005             50
1    2010          1    2005             50
2    2010          1    2009             60
3    2006          2    2005             65
4    2008          2    2007             55
5    2008          2    2005             65
6    2012          3    2005             70
7    2012          3    2011             85

Use groupby to find the latest date for each year

Here we look for the index where date_y is bigger within the group.

grp = df.groupby(["date_x", "player_id"])["date_y"].idxmax()
print(grp)

date_x  player_id
2006    1            0
        2            3
2008    2            4
2010    1            2
2012    3            7
Name: date_y, dtype: int64

So we filter with these indices

df = df[df.index.isin(grp.values)].reset_index(drop=True)

Which looks like

   date_x  player_id  date_y  player_rating
0    2006          1    2005             50
1    2010          1    2009             60
2    2006          2    2005             65
3    2008          2    2007             55
4    2012          3    2011             85

and we drop columns and rename the first one.

df = df.drop(columns=["date_y"])\
    .rename(columns={"date_x":"date"})

Which returns


   date  player_id  player_rating
0  2006          1             50
1  2010          1             60
2  2006          2             65
3  2008          2             55
4  2012          3             85
user_48193760
Related