Resampling data by week

Question

I have a time series dataset of 100 users looking at 365 days of transactions. The dataframe is currently set up like the following:

      date    2018-04-01 2018-04-02 2018-04-03 2018-04-04  2018-04-05 ...
userid
   1              0          0             0        0          13.40
   2            12.32        0             0        0           3.60
   3            11.32        0           14.22      0            0
   4              0          0             0        0           9.87
   5              0          0           19.52      0            0
  ...

I want to resample this to 52 weeks, taking the sums of the transactions every 7 days. How would I do this?

Quang Hoang · Answer 1 · 2019-11-01T19:41:40.680

1

You can use weekofyear:

df.groupby(df.columns.weekofyear, axis=1).sum()

Output:

date       13     14
userid              
1        0.00  13.40
2       12.32   3.60
3       11.32  14.22
4        0.00   9.87
5        0.00  19.52

You can also groupby every seven days:

(df.groupby(np.repeat(np.arange(df.shape[1]), 7)[:df.shape[1]],
            axis=1)
   .sum()
)

edited Nov 01 '19 at 19:41

answered Nov 01 '19 at 19:36

Quang Hoang

146,074
10
56
74

ansev · Accepted Answer · 2019-11-01T19:59:31.757

1

For this there is a method DataFrame.resample:

#df.columns=pd.to_datetime(df.columns)
df_resamp=df.resample('W',axis=1).sum()
print(df_resamp)

        2018-04-01  2018-04-08
userid                        
1             0.00       14.50
2            12.32        5.60
3            11.32       17.22
4             0.00       12.87
5             0.00       21.52

edited Nov 01 '19 at 19:59

answered Nov 01 '19 at 19:44

ansev

30,322
5
17
31

Resampling data by week

2 Answers2