Pandas: Count down rows until value appears

Question

I have a pandas dataframe which looks like this:

                 Change  AdjPrc
Quarter                       
1994-03-31         0.0   10.33
1994-06-30         0.0    9.00
1994-09-30         0.0    8.67
1994-12-31         0.0    8.92
1995-03-31         0.0    8.08
1995-06-30         0.0   11.92
1995-09-30         0.0   12.17
1995-12-31         0.0   14.67
1996-03-31         0.0   13.58
1996-06-30         0.0   13.58
1996-09-30         1.0   15.42
1996-12-31         0.0   20.17
1997-03-31         0.0   20.00
1997-06-30         1.0   21.54
1997-09-30         0.0   22.25

I want to calculate how many rows it takes until the next 1.0 in column "Change" appears. Basically, I want my dataframe to look like this

                  Change  AdjPrc  Countdown
Quarter                                  
1994-03-31         0.0   10.33          10
1994-06-30         0.0    9.00          9
1994-09-30         0.0    8.67          8
1994-12-31         0.0    8.92          7
1995-03-31         0.0    8.08          6
1995-06-30         0.0   11.92          5
1995-09-30         0.0   12.17          4
1995-12-31         0.0   14.67          3
1996-03-31         0.0   13.58          2
1996-06-30         0.0   13.58          1
1996-09-30         1.0   15.42          3
1996-12-31         0.0   20.17          2
1997-03-31         0.0   20.00          1
1997-06-30         1.0   21.54          nan
1997-09-30         0.0   22.25          nan

I found a way to have increasing values (found here):

df.groupby((df["Change"] == 1).cumsum()).cumcount()

Yet, I don't know how to make them decreasing... Any ideas?

You were very close. Pass `ascending=False` to your `cumcount` and then `+1`, i.e. `df.groupby((df["Change"] == 1).cumsum()).cumcount(ascending=False)+1`. — Henry Yik, Feb 25 '20 at 15:16

Pandas: Count down rows until value appears

0 Answers0