Aggregation by week & country in pandas for any month

Question

I have a dataframe starting from the 1st of February

Date    Countries   Value
01-Feb  A   46
02-Feb  A   27
03-Feb  A   38
04-Feb  A   19
05-Feb  A   10
06-Feb  A   20
07-Feb  A   21
08-Feb  A   31
09-Feb  A   32
10-Feb  A   50
11-Feb  A   25
12-Feb  A   19
13-Feb  A   34
14-Feb  A   14
15-Feb  A   43
16-Feb  A   38
17-Feb  A   16
18-Feb  A   38
01-Feb  B   34
02-Feb  B   23
03-Feb  B   36
04-Feb  B   43
05-Feb  B   20
06-Feb  B   41
07-Feb  B   17
08-Feb  B   28
09-Feb  B   30
10-Feb  B   28
11-Feb  B   39
12-Feb  B   32
13-Feb  B   46
14-Feb  B   29
15-Feb  B   49
16-Feb  B   42
17-Feb  B   38
18-Feb  B   11

I need to create sum of every 7 days starting from the start of the month for each country. How can it be done in python.

Answer 1

The date column is indexed by '7D' in 'resample'.

 df.set_index('Date', inplace=True)
 df.groupby('Countries').resample('7D').sum()

        Value
Countries   Date    
A   01-feb  181
    08-feb  205
    15-feb  135
B   01-feb  214
    08-feb  232
    15-feb  140

Answer 2

You could try using pandas package - here's one version (perhaps this could be done in an easier way:

import pandas as pd

data = '''
Date    Value
 01-Feb 34
 02-Feb 47
 03-Feb 13
 04-Feb 44
 05-Feb 18
 06-Feb 34
 07-Feb 50
 08-Feb 28
 09-Feb 10
10-Feb  27
11-Feb  18
12-Feb  11
13-Feb  24
14-Feb  49
15-Feb  51
16-Feb  27
17-Feb  37
18-Feb  45
'''

rows = data.split("\n")
data = [row.split() for row in rows if row]

df = pd.DataFrame(data[1:], columns=data[0])

df['Date'] = pd.to_datetime(df.Date + '-2020')
df['Value'] = df['Value'].astype(int)

df.groupby(df.Date.dt.day // 7).agg({'Date':'first', 'Value':'sum'})