Multiply each value in DataFrame cell according to multi-index name-CodePudding

Given this pandas Dataframe

list_index = pd.Series(['A' for i in range(2)]   ['B' for i in range(4)]   ['C' for i in range(3)]   ['D' for i in range(6)], name='indexes')
list_type = pd.Series(['a', 'c']   ['a', 'b','c','d']   ['f','g','i']   ['a','c','d','e','f','g'], name='types')
df = pd.DataFrame({
    'value' : [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]
}, index=[list_index, list_type])

indexes types   value
A       a       1
        c       2
B       a       3
        b       4
        c       5
        d       6
C       f       7
        g       8
        i       9
D       a       10
        c       11
        d       12
        e       13
        f       14
        g       15

I want to multiply each value by a factor (aka ratio) contained in another pandas.Dataframe

ratio_df = pd.DataFrame({
    'ratio' : [0.1, 0.2, 0.4, 0.5]
}, index=['A', 'B', 'C', 'D'])

    ratio
A   0.1
B   0.2
C   0.4
D   0.5

So that all values in df with 'indexes' == 'A' are multiplied by 0.1, and values with 'indexes' == 'B' are multiplied by 0.2 and so on.
I'm sure there is some smart way to do that but right now I can't really think of it. I know I can 'expand' ratio_df to the same length of df (with reset_index() and then creating a new column for df including ratios) and than simply perform * operation pairwise, but I'm not sure that's the fastest method.

I also looked at this answer but it's slightly different from my case.

CodePudding user response：

If just needing the product of the two columns Series.mul can be aligned based on index level.

Just select the columns and mul on index level:

df['value'].mul(ratio_df['ratio'], level='indexes')

or with index level number:

df['value'].mul(ratio_df['ratio'], level=0)

The result is an unnamed Series:

indexes  types
A        a        0.1
         c        0.2
B        a        0.6
         b        0.8
         c        1.0
         d        1.2
C        f        2.8
         g        3.2
         i        3.6
D        a        5.0
         c        5.5
         d        6.0
         e        6.5
         f        7.0
         g        7.5
dtype: float64

The resulting Series can be assigned back to df as needed:

df['new'] = df['value'].mul(ratio_df['ratio'], level='indexes')

df:

               value  new
indexes types            
A       a          1  0.1
        c          2  0.2
B       a          3  0.6
        b          4  0.8
        c          5  1.0
        d          6  1.2
C       f          7  2.8
        g          8  3.2
        i          9  3.6
D       a         10  5.0
        c         11  5.5
        d         12  6.0
        e         13  6.5
        f         14  7.0
        g         15  7.5

CodePudding user response：

Rename the ratio column to value then use mul on level=0:

df.mul(ratio_df.rename(columns={'ratio': 'value'}), level=0)

Result

               value
indexes types       
A       a        0.1
        c        0.2
B       a        0.6
        b        0.8
        c        1.0
        d        1.2
C       f        2.8
        g        3.2
        i        3.6
D       a        5.0
        c        5.5
        d        6.0
        e        6.5
        f        7.0
        g        7.5

CodePudding user response：

Here's one way resetting only first level; joining; multiplying and set_index back to original:

out = df.reset_index(level=1).join(ratio_df).assign(New=lambda x: x['value']*x['ratio']).set_index('types', append=True)

Output:

         value  ratio  New
  types                   
A a          1    0.1  0.1
  c          2    0.1  0.2
B a          3    0.2  0.6
  b          4    0.2  0.8
  c          5    0.2  1.0
  d          6    0.2  1.2
C f          7    0.4  2.8
  g          8    0.4  3.2
  i          9    0.4  3.6
D a         10    0.5  5.0
  c         11    0.5  5.5
  d         12    0.5  6.0
  e         13    0.5  6.5
  f         14    0.5  7.0
  g         15    0.5  7.5