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

And other with the selling prices by year

So I want to get a column ratio like this:

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)

CodePudding user response：

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)

CodePudding user response：

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