I have the following dataset, which is sorted by the grp (ascending) and then by the ts (descending) columns:
In [49]: df
Out[49]:
id grp ts
0 1 A 2018-12-30
1 2 A 2018-12-23
2 3 A 2018-12-22
3 4 A 2018-12-21
4 5 B 2018-11-11
5 6 B 2018-09-09
6 7 B 2018-09-03
7 8 B 2018-09-01
8 9 B 2018-08-01
9 10 C 2018-06-20
10 11 C 2018-06-17
11 12 C 2018-06-15
12 13 D 2018-01-01
I would like to count rows in the 7 dasys rolling window for each group, where ts (timestamp) is ordered in the descending order, so that i would get the following desired dataset:
grp ts count
0 A 2018-12-30 1
1 A 2018-12-23 2
2 A 2018-12-22 2
3 A 2018-12-21 3
4 B 2018-11-11 1
5 B 2018-09-09 1
6 B 2018-09-03 2
7 B 2018-09-01 2
8 B 2018-08-01 1
9 C 2018-06-20 1
10 C 2018-06-17 2
11 C 2018-06-15 3
12 D 2018-01-01 1
the date offset window works not as expected for the datetime index if it's sorted in the descending order:
In [56]: (df.set_index("ts")
.groupby("grp")
.rolling("7d", min_periods=1)
.count()
.reset_index()
.rename(columns={"id":"count"}))
Out[56]:
grp ts count
0 A 2018-12-30 1.0
1 A 2018-12-23 2.0
2 A 2018-12-22 3.0
3 A 2018-12-21 4.0
4 B 2018-11-11 1.0
5 B 2018-09-09 2.0
6 B 2018-09-03 3.0
7 B 2018-09-01 4.0
8 B 2018-08-01 5.0
9 C 2018-06-20 1.0
10 C 2018-06-17 2.0
11 C 2018-06-15 3.0
12 D 2018-01-01 1.0
It looks like the "7d" window is ignored (not respected)...
And if I sort index in the ascending order, then the "7d" window is respected, but it gives me not desired results:
In [57]: (df.sort_values("ts")
.set_index("ts")
.groupby("grp")
.rolling("7d", in_periods=1)
.count()
.reset_index()
.rename(columns={"id":"count"}))
Out[57]:
grp ts count
0 A 2018-12-21 1.0
1 A 2018-12-22 2.0
2 A 2018-12-23 3.0
3 A 2018-12-30 1.0
4 B 2018-08-01 1.0
5 B 2018-09-01 1.0
6 B 2018-09-03 2.0
7 B 2018-09-09 2.0
8 B 2018-11-11 1.0
9 C 2018-06-15 1.0
10 C 2018-06-17 2.0
11 C 2018-06-20 3.0
12 D 2018-01-01 1.0
Question: how do we get the desired dataset in an efficient manner?
PS i don't want to use resampling as my real dataset is very big and after resampling I doubt it'll fit in memory... :(
Setup for the sample dataset:
import numpy as np
import pandas as pd
from pandas import Timestamp
data = np.array([
[1, 'A', Timestamp('2018-12-30')],
[2, 'A', Timestamp('2018-12-23')],
[3, 'A', Timestamp('2018-12-22')],
[4, 'A', Timestamp('2018-12-21')],
[5, 'B', Timestamp('2018-11-11')],
[6, 'B', Timestamp('2018-09-09')],
[7, 'B', Timestamp('2018-09-03')],
[8, 'B', Timestamp('2018-09-01')],
[9, 'B', Timestamp('2018-08-01')],
[10, 'C', Timestamp('2018-06-20')],
[11, 'C', Timestamp('2018-06-17')],
[12, 'C', Timestamp('2018-06-15')],
[13, 'D', Timestamp('2018-01-01')]])
df = pd.DataFrame(data, columns=['id','grp','ts'])
df['ts'] = df['ts'].dt.floor('D')