0

Suppose I have two pandas dataframes:

d1 = {'year': [1, 2, 1, 2], 'val1': [3, 6, 9, 12]}
df1 = pd.DataFrame(data=d1)


   year val1
0   1    3
1   2    6
2   1    9
3   2    12

and

d2 = {'year': [1, 2], 'val2': [3, 6]}
df2 = pd.DataFrame(data=d2)


   year val2
0   1    3
1   2    6

I would like to divide the two values of val1 for a given year by the value of val2 for the same year. So the output should look like this:

   year val1
0   1    1
1   2    1
2   1    3
3   2    2

An easy way to do this is with a for loop but I would prefer to have a non-loop solution. I tried different combinations of groupby, apply, and map without much success.

Here is the for loop solution:

df2 = df2.set_index('year')
df3 = df1.copy()
for t in [1, 2]:
    idx = df1['year'] == t
    df3.loc[idx, 'val1'] = df1.loc[idx, 'val1'].divide(df2.loc[t].item())
Thomas
  • 157
  • 1
  • 6

2 Answers2

2

Here's a simple solution using map:

df1['val1'] /= df1['year'].map(df2.set_index('year')['val2'])

Output:

>>> df1
   year  val1
0     1   1.0
1     2   1.0
2     1   3.0
3     2   2.0
1

Using merge and eval:

(df1.merge(df2, on='year', how='left', sort=False)
    .eval('val1=val1/val2')
 )

Output:

   year  val1  val2
0     1   1.0     3
1     2   1.0     6
2     1   3.0     3
3     2   2.0     6

Or performing an in place modification of df1:

NB. how='left', sort=False is critical for this solution!

df1['val1'] /= df1.merge(df2, on='year', how='left', sort=False)['val2']

Output:

   year  val1
0     1   1.0
1     2   1.0
2     1   3.0
3     2   2.0
mozway
  • 194,879
  • 13
  • 39
  • 75