how to create columns from another dataframe rows and populate them based on values from both dataframes

user_15163647 0
Viewed 27

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: enter image description here

pandas python
1 Answers

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
user_29010020
Related