CODEWITHSUNDEEP
pythonpandas
user3447653
user3447653

Reputation: 4158

Join 2 pandas dataframes

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

Answers (2)

Quang Hoang
Quang Hoang

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

BENY
BENY

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

Related Questions