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

Question

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:

jezrael · Accepted Answer · 2022-06-28 11:47:36Z

1

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

edited Jun 28, 2022 at 11:47

answered Jun 28, 2022 at 11:41

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kimchi Over a year ago

jezrael thank you very much! it is working but it duplicates the length of the final dataframe.

Kimchi Over a year ago

I think this is happening as my id col in df2 has duplicate ids

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related