Cfrgtkky

I have a dataset:

 app id geo  date        count

    90  NO  2018-09-04  27

    66  HK  2018-09-03  2

    66  HK  2018-09-02  4   

    80  QA  2018-04-22  5  

    85  MA  2018-04-20  1   

    80  BR  2018-04-19  68

I am trying to generate a field which would aggregate data for each date for last seven days. My dataset should look like that:

 app id geo  date        count   count_last_7_days

        90  NO  2018-09-04  27    33

        66  HK  2018-09-03  2     6

        66  HK  2018-09-02  4     4

        80  QA  2018-04-22  5     74

        85  MA  2018-04-20  1     69

        80  BR  2018-04-19  68    68

I am trying this code:

 df['date'] = pd.to_datetime(df['date']) - pd.to_timedelta(7, unit='d')

    df = df.groupby(['geo','app_id', pd.Grouper(key='date', freq='W')]) . 

   ['count'].sum().reset_index().sort_values('date')

But even thought I use Grouper with weekly frequency (freq='W' ), It considers start of the week on Sunday and I don't have 7 days lag for non-Sunday entries.

Please, suggest how I can calculate that field.

A dirty one-liner would be

import numpy as np

df['count_last_7_days'] = [np.sum(df['count'][np.logical_and(df['date'][i] - df['date'] < pd.to_timedelta(7,unit='d'),df['date'][i] - df['date'] >= pd.to_timedelta(0,unit='d'))]) for i in range(df.shape[0])]

Note that I converted the time column to datetime using pd.to_datetime() first.

What this does is: for each day it finds all other rows within the desired one-week timespan, flags them with a boolean value and sums them after