Python: Perform math between two dataframes of different structure-CodePudding

I have the coefficient results of regression in one DataFrame, and am looking to apply that to current data and later add the intercept to get a model value.

Cofficient Result:

   item  value
0  ab   0.0145
1  bc   -0.043
2  de   0.17
3  hi   0.006

Current data:

    ab    ac   bc   cd    de     fg    hi
0  4.9  .76  -.13   0.06  -1.8   -.09  .32

How am I able to multiply each the coefficient values to the corresponding column of the current data ? I am hoping to be able to create something like:

   item  value   current
0  ab   0.0145   0.07105
1  bc   -0.043   0.00559
2  de   0.17    -0.306
3  hi   0.006    0.00192

The regression does not produce coefficients for all values, so I need to be able to match the coeff item to the column name of the current data to get the model then later sum it and add an intercept value.

Any help would be much appreciated.

CodePudding user response：

You can first transpose the current data df. Then set_index of the coefficient df to "item" and element-wise multiply the "value" column to its corresponding "item" value in current data df. Since some item values that are in the current data df don't exist the coefficient df, use dropna to drop the NaN values:

coef_df['current'] = coef_df.set_index('item')['value'].mul(current_df.T[0]).dropna().to_numpy()

Output:

  item   value  current
0   ab  0.0145  0.07105
1   bc -0.0430  0.00559
2   de  0.1700 -0.30600
3   hi  0.0060  0.00192

CodePudding user response：

Let us try

df['new'] = df['value'] * df['item'].map(curr.T[0])
df
Out[34]: 
  item   value      new
0   ab  0.0145  0.07105
1   bc -0.0430  0.00559
2   de  0.1700 -0.30600
3   hi  0.0060  0.00192