Pandas: conditional shift in blocks with reset-CodePudding

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.

CodePudding user response：

Try with groupby:

df["value"] = df.groupby(df["time"].diff().lt(0).cumsum())["value"].shift(2)

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

CodePudding user response：

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