Merging rows and summing values by date in Python [duplicate]

Question

I have a data frame that has multiple duplicated instances, sorted by date. It looks like this:

And I am trying to merge the rows by date for matching "Keywords", and sum the "Views" count.

The result I am looking to obtain is the following:

Could anyone hint me how I could achieve that in Python? Thank you.

The data frame:

 df = pd.DataFrame([["3/8/14", "adapter", 2], ["3/8/14", "adapter", 5], ["3/8/14", "charger", 1],
                   ["13/8/14", "adapter", 1], ["13/8/14", "battery-case", 0]],
                  columns=['Date', 'Keyword', 'Views'])

Twit

Simply aggregate -pandas 101: df.groupby(['Date', 'Keyword'])['Views'].sum().reset_index() – Albie

Answer 1

You want a groupby!

import pandas as pd

df = pd.DataFrame([["3/8/14", "adapter", 2], ["3/8/14", "adapter", 5], ["3/8/14", "charger", 1],
                   ["13/8/14", "adapter", 1], ["13/8/14", "battery-case", 0]],
                  columns=['Date', 'Keyword', 'Views'])
print df

#       Date       Keyword  Views
# 0   3/8/14       adapter      2
# 1   3/8/14       adapter      5
# 2   3/8/14       charger      1
# 3  13/8/14       adapter      1
# 4  13/8/14  battery-case      0

df2 = df.groupby(['Date','Keyword'],as_index=False).agg({'Views': 'sum'})

print df2

#       Date       Keyword  Views
# 0  13/8/14       adapter      1
# 1  13/8/14  battery-case      0
# 2   3/8/14       adapter      7
# 3   3/8/14       charger      1

Radiograph