How to use groupby and sum to remove duplicate

Question

My dataframe has four columns: date, source, campaign and spend. Now I have duplicated value in date, source and campaign, and I wanted to sum the spend if date, source and campaign (together) is the same. So each day there will be only one source, campaign and spend

and my code is:

marketing_spend_dict_df['spend_update'] = 
marketing_spend_dict_df.groupby(['date','source','campaign'])['spend'].sum()

I get an error saying "incompatible index of inserted column with frame index" How could I deal with it? I tried to search on Google but didn't find an optimal solution Thanks!

Would be useful if you included your sample data as text in your question instead of a linked image. — gbeaven, Jan 21 '20 at 19:14
Avoid images and external links. Edit the question instead to show the table as properly formatted text. — Michael Butscher, Jan 21 '20 at 19:15
Checkout the answer of this.. well explained https://stackoverflow.com/questions/20737811/attach-a-calculated-column-to-an-existing-dataframe — PraveenB, Jan 21 '20 at 19:29

score 0 · Answer 1 · answered Jan 21 '20 at 19:36

The issue could be due to multiindex. Checkout this link Attach a calculated column to an existing dataframe. I believe some SO user can mark it as duplicate with higher rep.

new_column = marketing_spend_dict_df.groupby(['date','source','campaign'], as_index=False)['spend'].sum()
marketing_spend_dict_df["spend_update"] = new_column.reset_index(level=0, drop=True)

How to use groupby and sum to remove duplicate

1 Answers1