Weird results When 'groupby'ing by weekofyear

Question

Here is the data generator code and also grouby function used for grouping by weekofyear for my problem:

import pandas as pd
import numpy as np

np.random.seed(100)

pd.options.display.max_rows = 1000
df = pd.DataFrame(np.random.randint(0,10,size=(600, 2)), columns=list('AB'))
df['date'] = pd.DataFrame(pd.date_range(start='1/1/2018', end='8/23/2019'))

df['weekofyear'] = df.date.dt.weekofyear
df['year'] = df.date.dt.year

df1 = df.groupby(['year', 'weekofyear']).agg({'A':'sum', 'B':'mean'})

Here is the First 10 rows of df:

    A   B   date    weekofyear  year
0   8   8   2018-01-01  1   2018
1   3   7   2018-01-02  1   2018
2   7   0   2018-01-03  1   2018
3   4   2   2018-01-04  1   2018
4   5   2   2018-01-05  1   2018
5   2   2   2018-01-06  1   2018
6   1   0   2018-01-07  1   2018
7   8   4   2018-01-08  2   2018
8   0   9   2018-01-09  2   2018
9   6   2   2018-01-10  2   2018
10  4   1   2018-01-11  2   2018

Here is the First 10 rows of df1:

year      weekofyear   A     B  
2018      1           31    3.375000
          2           30    4.285714
          3           26    4.142857
          4           37    3.142857
          5           19    6.142857
          6           34    4.142857
          7           30    4.142857
          8           43    4.571429
          9           35    5.142857
          10          24    4.000000

But the first row in df1 ( corresponding to first 7 days of df ) shows incorrect accumulated value of column A ( **shows 31 which is incorrect and should be 30 **).

8+3+7+4+5+2+1 = 30

8+3+7+4+5+2+1 != 31

score 2 · Answer 1 · answered Sep 11 '18 at 06:10

There is problem it mach also row with 2018-12-31, because ISO week date:

print (df[df['weekofyear'] == 1])

     A  B       date  weekofyear  year
0    8  8 2018-01-01           1  2018
1    3  7 2018-01-02           1  2018
2    7  0 2018-01-03           1  2018
3    4  2 2018-01-04           1  2018
4    5  2 2018-01-05           1  2018
5    2  2 2018-01-06           1  2018
6    1  0 2018-01-07           1  2018
364  1  6 2018-12-31           1  2018
365  5  5 2019-01-01           1  2019
366  2  0 2019-01-02           1  2019
367  9  3 2019-01-03           1  2019
368  4  7 2019-01-04           1  2019
369  4  7 2019-01-05           1  2019
370  9  4 2019-01-06           1  2019

Thank you very much for the enlightenment on the source of my problem. I decided to use averaging for every 7 rows, as described in this link:`https://stackoverflow.com/questions/36810595/calculate-average-of-every-x-rows-in-a-table-and-create-new-table`, instead of using weekofyear. — Muser, Sep 11 '18 at 07:29

Weird results When 'groupby'ing by weekofyear

1 Answers1