1

I have the following 2 tables, df and name_df, where the number of people is not limited to 2 and can increase. Under the name columns (ie. John and Mary), a 1 represents he/she consumed the item while 0 represents did not consume:

df
item   price John  Mary
apple  2     1     1
mango  5     0     1

name_df
name
John
Mary

I want to calculate the sumproduct of each person and join the column to the name_df column. I want the result to look like this:

name_df
name   sumproduct
John   2
Mary   7

My main problem is to find out how to calculate sumproduct using python/pandas. I know a .dot() function will get the result I want but how can I append those results to each respective person?

Jason Tay
  • 37
  • 5
  • Possible duplicate of [Adding new column to existing DataFrame in Python pandas](https://stackoverflow.com/questions/12555323/adding-new-column-to-existing-dataframe-in-python-pandas) – wwii Aug 11 '19 at 13:37

1 Answers1

4

You can melt using the name_df to determine id_vars, and groupby.

u = df.melt(df.columns ^ name_df['name'])

u.price.mul(u.value).groupby(u.variable).sum()


variable
John    2
Mary    7
dtype: int64
user3483203
  • 50,081
  • 9
  • 65
  • 94