Pandas: conditional shift in blocks with reset

March 23, 2022

I am trying to shift data in a Pandas dataframe in the following manner from this:

time	value
1	1
2	2
3	3
4	4
5	5
1	6
2	7
3	8
4	9
5	10

To this:

time	value
1
2
3	1
4	2
5	3
1
2
3	6
4	7
5	8

In short, I want to move the data 3 rows down each time a new cycle for a time block begins.

Have not been able to find solution on this, as it seems my English is quite limited not knowing how to describe the problem without an example.

Edit:

Both solutions work. Thank you.

>Solution :

IIUC, you can shift per group:

df['value_shift'] = df.groupby(df['time'].eq(1).cumsum())['value'].shift(2)

output:

   time  value  value_shift
0     1      1          NaN
1     2      2          NaN
2     3      3          1.0
3     4      4          2.0
4     5      5          3.0
5     1      6          NaN
6     2      7          NaN
7     3      8          6.0
8     4      9          7.0
9     5     10          8.0