Pandas sum corresponding values based on values in another column

Question

df1 contains Itemlist1 and Itemlist2 where each cell can contain any number of items. df2 contains Price and Cost of each item.

Want to obtain a final df with 2 new columns, Totalprice and Totalcost, added to df1. The Totalprice and Totalcost is the sum of all the items in each row of df1.

Managed to arrive at df3 where each item is put in a separate cell. Any suggestion from here please. Thank you.

BENY · Accepted Answer · 2022-01-05 02:06:13Z

1

From your df3, do the replace, then sum with axis=1

cost_dict = dict(zip(df2.Itemcode,df2.Cost))
price_dict = dict(zip(df2.Itemcode,df2.Price))
df1['totalcost'] = df3.replace(cost_dict).sum(axis=1)
df1['totalprice'] = df3.replace(price_dict).sum(axis=1)

answered Jan 5, 2022 at 2:06

BENY

324k22 gold badges176 silver badges250 bronze badges

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

1 Comment

user12600329 Over a year ago

Thank you @BENY, your solution works well.

Collectives™ on Stack Overflow

Pandas sum corresponding values based on values in another column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related