count cumulative number of rows since a condition is et in a Pandas DataFrame

Question

I have a pandas DF that has two columns, Day, and Data, reading from a csv file.

enter image description here

After reading, I add 3 columns "Days with condition 0", 1, and 2. For example, for the columns 'Days with condition 2' I do this:

DF['Days with condition 2'] = ''
DF['Days with condition 2'][DF['Data']==2]=1

What I need to do and can't figure out is how to calculate 'Days since condition' 0,1,2. For example, the 'Days since condition 2' should display 11 in index 19, since that's the number of rows since the last condition was triggered (index 8). Is there any pandas function to do this?

possible duplicate of [Pandas: conditional rolling count](http://stackoverflow.com/questions/25119524/pandas-conditional-rolling-count) — maxymoo, May 17 '15 at 23:27
in your example, what would be the value in "Days since condition" at index 8? 1? — Bob Haffner, May 18 '15 at 01:47
Sorry, i worded my question poorly. What would you like it to be? — Bob Haffner, May 18 '15 at 01:56

score 2 · Accepted Answer · answered May 18 '15 at 02:27

Starting with your two original columns

   Day  Data
0     1     1
1     2     0
2     3     0
3     4     0
4     5     0
5     6     0
6     7     1
7     8     0
8     9     2
9    10     0
10   11     0
11   12     1
12   13     0
13   14     0
14   15     0
15   16     1
16   17     0
17   18     1
18   19     0
19   20     2
20   21     0
21   22     0
22   23     0

Here's how you could populate "Days with condition 2". Filter for the 2s using boolean indexing. Then we subtract our previous Day using shift().

The next couple of steps filters for the first occurrence of 2 and updates "Days with condition 2" equal to Day, but it could be whatever you want it to be

Then a fillna() to get rid of the NaNs. The same pattern could be used for the other two columns you want to add

filter = (df["Data"] == 2) 
df.loc[filter,"Days with condition 2"] =  df[filter]["Day"] - df[filter]["Day"].shift(1)
filter = filter & (df["Days with condition 2"].isnull())
df.loc[filter,"Days with condition 2"] =  df[filter]["Day"]
df = df.fillna(0)
df
    Day  Data  Days with condition 2
0     1     1                      0
1     2     0                      0
2     3     0                      0
3     4     0                      0
4     5     0                      0
5     6     0                      0
6     7     1                      0
7     8     0                      0
8     9     2                      9
9    10     0                      0
10   11     0                      0
11   12     1                      0
12   13     0                      0
13   14     0                      0
14   15     0                      0
15   16     1                      0
16   17     0                      0
17   18     1                      0
18   19     0                      0
19   20     2                     11
20   21     0                      0
21   22     0                      0
22   23     0                      0

count cumulative number of rows since a condition is et in a Pandas DataFrame

1 Answers1