python pandas how to multiply columns by other values in different dataframe-CodePudding

I have 2 DataFrames:

df1 (original has about 3000 rows x 200 columns: 190 to add and multiply, rest has some other information):

           tag    A35    A37    A38
ITEM 
B1         SAS    8.0    3.0    1.0
B2         HTW    1.0    3.0    3.0
B3         ASD    0.0    8.0    0.0
B4         KLD    1.0   10.0    0.0

df2 (in row have matching "prices" to columns in df1):

         day1      day2  
prices
A35           1       3
A37           2       2
A38           3       1

I'd like to add columns in df1 with overall day1_price and day2_price with scheme:

df1.B1-day1_price = df1.B1_A35 * df2.A35_day1   
                    df1.B1_A37 * df2.A37_day1   
                    df1.B1_A38 * df2.A38_day1

So it should be for row1 day1: (b1) = 8*1 3*2 1*3= 17

           tag    A35    A37    A38    day1_price  day2_price
ITEM 
B1         SAS    8.0    3.0    1.0     17.0         31.0
B2         HTW    1.0    3.0    3.0     16.0         12.0
B3         ASD    0.0    8.0    0.0     16.0         16.0
B4         ASD    1.0   10.0    0.0     21.0         23.0

So I want to add and multiply columns with matching prices from df2.

CodePudding user response：

Use dot for the multiplication and join to df1:

#set indices if needed
df1 = df1.set_index("ITEM")
df2 = df2.set_index("prices")

output = df1.join(df1.drop("tag", axis=1).dot(df2).add_suffix("_price"))

>>> output
  ITEM  tag  A35  A37  A38  day1_price  day2_price
0   B1  SAS    8    3    1          17          31
1   B2  HTW    1    3    3          16          12
2   B3  ASD    0    8    0          16          16
3   B4  KLD    1   10    0          21          23