Reputation: 4158
I am trying to compute "price_lastyear" in Dataframe 1 by referencing Dataframe 2. In dataframe 1, for XXX, year is 2017. So last year of 2017 is 2016. Hence it has to reference 2016 for same make in dataframe 2, get the price and populate in the corresponding row in dataframe 1.
Dataframe 1:
car_model year price_lastyear
XXX 2017 16411
Dataframe 2:
car_model year price
XXX 2016 16411
XXX 2017 11432
XXX 2018 12345
Tried with joining 2 pandas dataframes using:
df3 = df1.merge(df2, how=inner, left_on=["car_model","year-1"], right_on=["car_model","year"]
Not sure whether join is the best operation to achieve the above result.
Upvotes: 1
Views: 64
Reputation: 150825
You can modify the year and merge:
df1.merge(df2.assign(year=df2['year']+1),
on=['car_model','year'],
how='left')
Output:
car_model year price_lastyear price
0 XXX 2017 16411 16411
Upvotes: 2
Reputation: 323396
Let us check merge_asof
out = pd.merge_asof(df1,df2.assign(Pyear=df2.year),on='year', by='car_model', allow_exact_matches=False)
car_model year price_lastyear price Pyear
0 XXX 2017 16411 16411 2016
Upvotes: 1