how to create columns from another dataframe rows and populate them based on values from both datafr-CodePudding

say I have two dfs: df1:

new_col	attribute	y1
petrol	car	0.3
diesel	car	0.4
rigid	hgv	0.25
artic	hgv	0.35

the other data frame (df2) has the attribute column from df1 in common:

id	car	hgv
1	10	1000
2	1000	10
3	100	10000

I am trying to add the names of new_col from df1 as new columns in df2 and populate it by multiplying y1/y2 by the corresponding attribute column in df2 for y1 and y2.

That is what I am trying to get:

CodePudding user response：

Create MultiIndex Series by new_col,attribute and multiple all columns in df2 without id, remove second level of MultiIndex by DataFrame.droplevel, join df2 and last use DataFrame.convert_dtypes for integers columns:

s = df1.set_index(['new_col','attribute'])['y1']
df = df2.join(df2.drop('id',1).mul(s, level=1).droplevel(1, axis=1)).convert_dtypes()
print (df)
   id   car    hgv  petrol  diesel   rigid   artic
0   1    10   1000       3       4   250.0   350.0
1   2  1000     10     300     400     2.5     3.5
2   3   100  10000      30      40  2500.0  3500.0