CODEWITHSUNDEEP
pythonpandas
Phil
Phil

Reputation: 11

Mutliplying dataframe values by another dataframe and returning 'sumproduct' of columns for all rows

How do I summarise this data into one table using Pandas? I have two existing tables..

df1.., Cost table

> Month   A    B   C
> Jan    10    5   4 
> Feb     5   10   5
> Mar    20   10   8 
> Apr    10   10  10
> May     5   20  10

df2... Weighting Option

>        A     B     C
> Option
> x     1.00  0.90  0.80
> y     0.95  0.75  0.60
> x     0.90  0.85  0.65

I want a new dataframe (d3) showing the totals for columns A+B+C when weighted by each of the options (x,y,z). It would look like this...

df3.., total cost in each month by weighting option

>        x    y    z   
> month
>  Jan
>  Feb       
>  Mar       
>  Apr
>  May

E.g.. (Mar,y) above would be (0.95*20)+(0.75*10)+(0.60*8) = 31.3 and so on.

Upvotes: 0

Views: 718

Answers (1)

Jianxun Li
Jianxun Li

Reputation: 24742

It looks like you just want a standard matrix multiplication, which can be done using np.dot()

pd.DataFrame(df1.values.dot(df2.T.values), columns=list('xyz'), index=df1.index)

          x      y      z
Month                    
Jan    17.7  15.65  15.85
Feb    18.0  15.25  16.25
Mar    35.4  31.30  31.70
Apr    27.0  23.00  24.00
May    31.0  25.75  28.00

Edit:

As suggested by @Mr.F, 

pd.DataFrame(np.dot(df1, df2.T), columns=list('xyz'), index=df1.index)

Upvotes: 1

Related Questions