[FIXED] Grouping by two columns and by week with Pandas

Issue

I have a dataframe like this one:

ID timestamp field2
ABC 456788   event1
ABC 456688   event2
DEF 856788   event1
ABC 96788    event1

and I want to have this result, grouped by week:

ID timestamp      field2 count
ABC 29/03/2020    event1   2
ABC 22/03/2020    event2   1
DEF 29/03/2020    event1   1

So I manage to group and convert the timestamp to date with this code:

grouper = dftest.groupby(['ID', 'field2', pd.Grouper(key='timestamp', freq='W')])

but when I do this to see the results:

grouper.count().reset_index()[(grouper.count().reset_index().ID == "ABC")]

I don't get the count. Also, it just leaves one of the rows with the (same field2, by the same user that is in the same week).

It's like it's grouping but without the count (because in this case the user ABC have two event1 on the same week (29/03/2020)). The results that I get are these ones below:

    ID timestamp      field2
    ABC 29/03/2020    event1
    ABC 22/03/2020    event2

I have already read lots of posts here and the doc but I cannot figure it out. What can I try to resolve it?

Solution

If use count function is necessary specify column name after groupby and then for new column is used Series.reset_index with name parameter:

df = (dftest.groupby(['ID', 'field2', pd.Grouper(key='timestamp', freq='W')])['ID']
            .count()
            .reset_index(name='count'))

Answered By - jezrael