CODEWITHSUNDEEP
pandas
user12600329
user12600329

Reputation: 3

Pandas sum corresponding values based on values in another column

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.

Upvotes: 0

Views: 489

Answers (1)

BENY
BENY

Reputation: 323226

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)

Upvotes: 1

Related Questions