[Python] Rolling window selection with groupby in pandas

I have the following pandas dataframe:

# Create the DataFrame
df = pd.DataFrame({
'id': [1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2],
'date': [1, 2, 3, 4, 5, 6, 7, 8, 5, 6, 7, 8, 9, 10, 11, 12],
'value': [11, 12, 13, 14, 15, 16, 17, 18, 21, 22, 23, 24, 25, 26, 27, 28]
})
df

id date value
0 1 1 11
1 1 2 12
2 1 3 13
3 1 4 14
4 1 5 15
5 1 6 16
6 1 7 17
7 1 8 18
8 2 5 21
9 2 6 22
10 2 7 23
11 2 8 24
12 2 9 25
13 2 10 26
14 2 11 27
15 2 12 28


I want to query the above dataframe, in a rolling window manner, for both ids. The rolling window should be of size n.

So, if n==2, in the 1st iteration I would like to query this:

df.query('(id==1 and (date==1 or date==2)) or (id==2 and (date==5 or date==6))')

id date value
0 1 1 11
1 1 2 12
8 2 5 21
9 2 6 22


in the 2nd iteration I would like to query this:

df.query('(id==1 and (date==2 or date==3)) or (id==2 and (date==6 or date==7))')

id date value
1 1 2 12
2 1 3 13
9 2 6 22
10 2 7 23


in the 3rd iteration I would like to query this:

df.query('(id==1 and (date==3 or date==4)) or (id==2 and (date==7 or date==8))')

id date value
2 1 3 13
3 1 4 14
10 2 7 23
11 2 8 24


etc. How could I do that in pandas ? My data has around 500 ids

Continue reading...