How to use Pandas to do a conditional VLOOKUP using two columns as an index for the VLOOKUP?

user_13771657 0
Viewed 309

I'm pretty new to Pandas and Python, and I simply cannot figure out how to do something that is very easily done in Excel. I was hoping to get a bit of help from the community.

Assume I have the following, which is a df relating to fantasy football that has three columns - 'Name', 'Year', and 'FantasyPts'. Code below.

import pandas as pd

df = pd.DataFrame({'Name': ['Tom Brady', 'Tom Brady', 'Tom Brady', 'Patrick Mahomes', 'Patrick Mahomes', 'Patrick Mahomes'],
                   'Year': [2019, 2018, 2017, 2019, 2018, 2017],
                   'FantasyPts': [300, 350, 400, 500, 400, 50],
                   })

I want to add another column to the table called 'FantasyPtsPreviousYear' but am having a ton of difficulty figuring out how to do so in Pandas / Python.

What I want to do is:

  1. For each row in the table, have python / pandas check the name and year in that row of the df.
  2. Look up the fantasy points scored by the same player in the previous year (i.e., Year - 1)
  3. Populate that number in a new row of the df called 'FantasyPtsPreviousYear' or, if there is no data for the previous year for that player, enter 0.

In Excel, I would simply create new columns and use those columns with VLOOKUPs. The closest thing I have been able to find to VLOOKUP in Pandas is merge but that doesn't seem to work here (or at least I can't figure out how to make it work with this specific application). After trying to find the answer, I think it might have something to do with the loc() function and a For loop, but I can't get it to work.

Thanks for any help you can provide! I greatly appreciate it and think this community is awesome for all of the assistance it provides!

vlookup pandas dataframe python
3 Answers

I believe this can be achieved with a combination of where() and shift(). However, this requires the data to be previously sorted. Here is the code given the data you provide:

df = df.sort_values(['Name','Year'],ascending=[True,True])
df['FantasyPtsPreviousYear'] = df['FantasyPts'].shift().where(df['Name'].eq(df['Name'].shift())).fillna(0)
print(df)

This outputs:

              Name  Year  FantasyPts  FantasyPtsPreviousYear
5  Patrick Mahomes  2017          50                     0.0
4  Patrick Mahomes  2018         400                    50.0
3  Patrick Mahomes  2019         500                   400.0
2        Tom Brady  2017         400                     0.0
1        Tom Brady  2018         350                   400.0
0        Tom Brady  2019         300                   350.0
user_118970070

Let us try groupby with shift

df = df.sort_values(['Name','Year'],ascending=[True,True])
df['FantasyPtsPreviousYear'] = df['FantasyPts'].groupby(df['Name']).shift().fillna(0)
user_79645270

merge the DataFrame with itself where you add 1 to the year. This explicitly defines the match on Year -> Year-1 for each player

(Be careful with shift, unless you are certain you have a row for every year. Without explicitly reindexing to all years a shift within group will give you the last year you have data for, regardless of whether that is 1 year ago or 10 years ago (if missing data))

df = df.merge((df.assign(Year=df['Year']+1)
                 .rename(columns={'FantasyPts': 'FantasyPts_prev'})), 
              how='left', on=['Name', 'Year'])
df['FantasyPts_prev'] = df['FantasyPts_prev'].fillna(0, downcast='infer')

              Name  Year  FantasyPts  FantasyPts_prev
0        Tom Brady  2019         300              350
1        Tom Brady  2018         350              400
2        Tom Brady  2017         400                0
3  Patrick Mahomes  2019         500              400
4  Patrick Mahomes  2018         400               50
5  Patrick Mahomes  2017          50                0
user_43333590
Related