Multiply pandas DataFrame column with a Series-CodePudding

I want to multiply a pandas DataFrame df with Series s:

df = pd.DataFrame([[0,0,10],[0,1,11],[1,0,12],[1,1,13],[2,0,14],[2,1,15]], columns=['a','b','val'])
s = pd.Series([2,3])

df:                s:
    a b val          
___________        ______
0   0 0 10         0   2
1   0 1 11         1   3
2   1 0 12
3   1 1 13
4   2 0 14
5   2 1 15

such that the indices of the Series [0, 1] are matched with column b of the DataFrame [0, 1, 0, 1, 0, 1], i.e. I expect the column val to change from

[10, 11, 12, 13, 14, 15]

[20, 33, 24, 39, 28, 45]

It seems like a trivial problem, and I tried MultiIndex and GroupBy, .mul() .apply() .... I just can't seem to figure it out. Really appreciate your help!

CodePudding user response：

Map s on b then multiply by val:

df['val2'] = df['b'].map(s) * df['val']

Output:

>>> df
   a  b  val  val2
0  0  0   10    20
1  0  1   11    33
2  1  0   12    24
3  1  1   13    39
4  2  0   14    28
5  2  1   15    45

>>> df['b'].map(s)
0    2
1    3
2    2
3    3
4    2
5    3
Name: b, dtype: int64

CodePudding user response：

You can also do index matching by using the level argument of df.mul:

>>> df['new_val'] = df.set_index('b', append=True)['val']
                      .mul(s, level=1)
                      .reset_index(drop=True)