pandas group by week

Question

I have the following test dataframe:

       date                 user  answer  
0      2018-08-19 19:08:19  pga   yes   
1      2018-08-19 19:09:27  pga   no   
2      2018-08-19 19:10:45  lry   no   
3      2018-09-07 19:12:31  lry   yes
4      2018-09-19 19:13:07  pga   yes   
5      2018-10-22 19:13:20  lry   no

I am using the following code to group by week:

test.groupby(pd.Grouper(freq='W'))

I'm getting an error that Grouper is only valid with DatetimeIndex, however I'm unfamiliar on how to structure this in order to group by week.

user_7388477 · Answer

Probably you have date column as a string.

In order to use it in a Grouper with a frequency, start from converting this column to DateTime:

df['date'] = pd.to_datetime(df['date'])

Then, as date column is an "ordinary" data column (not the index), use key='date' parameter and a frequency.

To sum up, below you have a working example:

import pandas as pd

d = [['2018-08-19 19:08:19', 'pga', 'yes'],
     ['2018-08-19 19:09:27', 'pga', 'no'],
     ['2018-08-19 19:10:45', 'lry', 'no'],
     ['2018-09-07 19:12:31', 'lry', 'yes'],
     ['2018-09-19 19:13:07', 'pga', 'yes'],
     ['2018-10-22 19:13:20', 'lry', 'no']]
df = pd.DataFrame(data=d, columns=['date', 'user', 'answer'])
df['date'] = pd.to_datetime(df['date'])
gr = df.groupby(pd.Grouper(key='date',freq='W'))
for name, group in gr:
    print(' ', name)
    if len(group) > 0:
        print(group)

Note that the group key (name) is the ending date of a week, so dates from group members are earlier or equal to the date printed above.

You can change it passing label='left' parameter to Grouper.

pandas group by week

1 Answers