Pandas Element-Wise Variability by Rows using Monthly Averages

Question

I have a df called "df" that looks like this:

     year    month    adjusted_power
333  2018    10       4
334  2018    11       2
335  2018    12       1
336  2019    01       6
337  2019    02       8
338  2019    03       2
339  2019    04       12
340  2019    05       10
341  2019    06       6
342  2019    07       2
343  2019    08       2
344  2019    09       4
345  2019    10       4
346  2019    11       8
347  2019    12       10

And, I need to divide it element-wise by the monthly averages dataframe called "dfavgs"

      month  adjusted_power_average
0     01            2
1     02            4
2     03            6
3     04            8
4     05            6
5     06            2
6     07            4
7     08            8
8     09            8
9     10            6
10    11            4
11    12            4

so, that the output looks like this in terms of variability for a new df "dfvar" = (df/dfavgs) -1

     year    month    var
333  2018    10       0.66
334  2018    11       0.25
335  2018    12       0.25
336  2019    01       3
337  2019    02       2
338  2019    03       0.33
339  2019    04       1.5
340  2019    05       1.66
341  2019    06       3
342  2019    07       0.5
343  2019    08       0.25
344  2019    09       0.5
345  2019    10       0.66
346  2019    11       2
347  2019    12       3.16

I'm not sure how to proceed whether to compute the element-wise variability in a loop structure, using a lamba variable or something like this but it gives me only the first 12 variability numbers and the rest of the column is NaN:

dfvar = (df.loc[:,'adjusted_power'].div(dfavgs.loc[:,'adjusted_power_average']) - 1)

Thank you for any help!

Answer 1

Try with

df['new'] = df.adjust_power/dfavgs.set_index('month')['adjusted_power_average'].reindex(df['month']).values

Answer 2

Use map:

df['var'] = df['adjusted_power']/df['month'].map(dfavgs.set_index('month')['adjusted_power_average'])

Output:

     year  month  adjusted_power       var
333  2018     10               4  0.666667
334  2018     11               2  0.500000
335  2018     12               1  0.250000
336  2019      1               6  3.000000
337  2019      2               8  2.000000
338  2019      3               2  0.333333
339  2019      4              12  1.500000
340  2019      5              10  1.666667
341  2019      6               6  3.000000
342  2019      7               2  0.500000
343  2019      8               2  0.250000
344  2019      9               4  0.500000
345  2019     10               4  0.666667
346  2019     11               8  2.000000
347  2019     12              10  2.500000