Divide/Multiply certain rows in a DF by other Df with conditions

Question

I got 2 df The first one with the purchase prices by country and year

year	purchase	country
1999	23	Canada
2000	24	Canada
1999	21	China
2999	22	China

And other with the selling prices by year

year	price
1999	25
2000	27

So I want to get a column ratio like this:

year	purchase	country	ratio
1999	23	Canada	1.086
2000	24	Canada	1.25
1999	21	China	1.19
2999	22	China	1.22

Which is like dividing by the selling price where the years are the same. I tried something like:

np.divide(selling["price"],purchase.Price, where= selling["year"]== purchase["years"])

but It was unsuccessful.

I'm trying to do it in just one line of code without creating additional elements. Since I think that I can solve it by creating another variable running in the years like

for years in purchase.years: 
   purchase["ratio"] = np.divide(selling[selling.years].Price,purchase[purchase.years ==  years].Price)

Answer 1

You can do reindex and rdiv

df1['ratio'] = df1['purchase'].rdiv(df2.set_index('year')['price'].reindex(df1['year']).values)
df1
Out[316]: 
   year  purchase country     ratio
0  1999        23  Canada  1.086957
1  2000        24  Canada  1.125000
2  1999        21   China  1.190476
3  2999        22   China       NaN 

Answer 2

You could join both the tables together.

merged_df = df1.merge(df2, 
                      on = ['year'],
                      how = 'left')

merged_df['ratio'] = merged_df['price']/merged_df['purchase']
merged_df.drop('price', axis = 1)