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

i have piece of 2 dfs:

df1 (original has about 3000 rows x 200 columns (190 to add and multiplicate, 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 multiplicate columns with match price from second df.

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