Rolling count on condition

Question

I have a dataframe with columns PERMNO, year, DIVAMT & Consecutives indicating the group, the year, the amount of dividend paid in that year and the number of consecutive years of dividend payments per group, respectively.

As the description indicates I would like to count the number of consecutive years of dividend payments in the column Consecutives, whereby the count is increased if a dividend is paid (DIVAMT != 0) in the respective year. However, the first year of dividend payment should be 0 as there is no previous year of dividend payments. I tried implementing this the following way:

df["Consecutives"] = df.groupby(["PERMNO",(df['DIVAMT'] == 0).cumsum()]).cumcount()

Index 2-3, 78-79 & 108-109 suggest that my code is correct as during the year of the first dividend payment, the counter remains on zero and increases only after the second year. However, when inspecting the rest of the dataframe (e.g. index 20-21, 40-41, 60-64) it becomes apparent that there is some sort of mistake in the code as it already starts to count in the first year of the dividend payment.

Due to the different behaviour it is also not possible to transform it e.g. like this:

np.where(df["Consecutives"] == 0, 0, df["Consecutives"]-1)

Any ideas how to implement this correctly?

df:

    PERMNO  year    DIVAMT  Consecutives
0   10000   1986    0.00000 0
1   10000   1987    0.00000 0
2   10001   1986    0.41000 0
3   10001   1987    0.42000 1
4   10001   1988    0.44000 2
5   10001   1989    0.49000 3
6   10001   1990    0.53750 4
7   10001   1991    0.58750 5
8   10001   1992    0.62250 6
9   10001   1993    0.68250 7
10  10001   1994    0.55500 8
11  10001   1995    0.39500 9
12  10001   1996    0.41500 10
13  10001   1997    0.43500 11
14  10001   1998    0.45500 12
15  10001   1999    0.47500 13
16  10001   2000    0.49500 14
17  10001   2001    0.51500 15
18  10001   2002    0.53500 16
19  10001   2003    0.13500 17
20  10001   2004    0.00000 0
21  10001   2005    0.04000 1
22  10001   2006    0.35000 2
23  10001   2007    0.66400 3
24  10001   2008    0.47800 4
25  10001   2009    0.52500 5
26  10001   2010    0.54000 6
27  10001   2011    0.54000 7
28  10001   2012    0.54000 8
29  10001   2013    0.54000 9
30  10001   2014    0.54000 10
31  10001   2015    0.54000 11
32  10001   2016    0.30000 12
33  10001   2017    13.25000    13
34  10002   1986    0.00000 0
35  10002   1987    0.00000 0
36  10002   1988    0.00000 0
37  10002   1989    0.00000 0
38  10002   1990    0.00000 0
39  10002   1991    0.00000 0
40  10002   1992    0.00000 0
41  10002   1993    0.18000 1
42  10002   1994    0.26000 2
43  10002   1995    0.32000 3
44  10002   1996    0.40000 4
45  10002   1997    0.67000 5
46  10002   1998    0.40750 6
47  10002   1999    0.35500 7
48  10002   2000    0.40000 8
49  10002   2001    0.44000 9
50  10002   2002    0.48000 10
51  10002   2003    0.52000 11
52  10002   2004    0.52000 12
53  10002   2005    0.52000 13
54  10002   2006    0.52000 14
55  10002   2007    0.52000 15
56  10002   2008    0.52000 16
57  10002   2009    0.03500 17
58  10002   2010    0.00000 0
59  10002   2011    0.00000 0
60  10002   2012    0.00000 0
61  10002   2013    3.01250 1
62  10003   1986    0.00000 0
63  10003   1987    0.35000 1
64  10003   1988    1.00000 2
65  10003   1989    0.77500 3
66  10003   1990    0.00000 0
67  10003   1991    0.00000 0
68  10003   1992    0.00000 0
69  10003   1993    0.00000 0
70  10003   1994    0.00000 0
71  10003   1995    5.44880 1
72  10005   1986    0.00000 0
73  10005   1987    0.00000 0
74  10005   1988    0.00000 0
75  10005   1989    0.00000 0
76  10005   1990    0.00000 0
77  10005   1991    0.00000 0
78  10006   1963    1.10000 0
79  10006   1964    2.25000 1
80  10006   1965    3.07500 2
81  10006   1966    2.10000 3
82  10006   1967    2.20000 4
83  10006   1968    2.25000 5
84  10006   1969    2.40000 6
85  10006   1970    2.40000 7
86  10006   1971    2.40000 8
87  10006   1972    2.40000 9
88  10006   1973    2.40000 10
89  10006   1974    2.50000 11
90  10006   1975    2.60000 12
91  10006   1976    2.20000 13
92  10006   1977    1.95000 14
93  10006   1978    2.07500 15
94  10006   1979    2.20500 16
95  10006   1980    2.43500 17
96  10006   1981    2.69500 18
97  10006   1982    2.76000 19
98  10006   1983    1.74000 20
99  10006   1984    54.85000    21
100 10007   1986    0.00000 0
101 10007   1987    0.00000 0
102 10007   1988    0.00000 0
103 10007   1989    0.00000 0
104 10007   1990    0.00000 0
105 10008   1986    0.00000 0
106 10008   1987    0.00000 0
107 10008   1988    0.01000 1
108 10009   1986    0.07500 0
109 10009   1987    0.35000 1
110 10009   1988    0.45000 2

desired output

    PERMNO  year    DIVAMT  Consecutives
0   10000   1986    0.00000 0
1   10000   1987    0.00000 0
2   10001   1986    0.41000 0
3   10001   1987    0.42000 1
4   10001   1988    0.44000 2
5   10001   1989    0.49000 3
6   10001   1990    0.53750 4
7   10001   1991    0.58750 5
8   10001   1992    0.62250 6
9   10001   1993    0.68250 7
10  10001   1994    0.55500 8
11  10001   1995    0.39500 9
12  10001   1996    0.41500 10
13  10001   1997    0.43500 11
14  10001   1998    0.45500 12
15  10001   1999    0.47500 13
16  10001   2000    0.49500 14
17  10001   2001    0.51500 15
18  10001   2002    0.53500 16
19  10001   2003    0.13500 17
20  10001   2004    0.00000 0
21  10001   2005    0.04000 0
22  10001   2006    0.35000 1
23  10001   2007    0.66400 2
24  10001   2008    0.47800 3
25  10001   2009    0.52500 4
26  10001   2010    0.54000 5
27  10001   2011    0.54000 6
28  10001   2012    0.54000 7
29  10001   2013    0.54000 8
30  10001   2014    0.54000 9
31  10001   2015    0.54000 10
32  10001   2016    0.30000 11
33  10001   2017    13.25000    12
34  10002   1986    0.00000 0
35  10002   1987    0.00000 0
36  10002   1988    0.00000 0
37  10002   1989    0.00000 0
38  10002   1990    0.00000 0
39  10002   1991    0.00000 0
40  10002   1992    0.00000 0
41  10002   1993    0.18000 0
42  10002   1994    0.26000 1
43  10002   1995    0.32000 2
44  10002   1996    0.40000 3
45  10002   1997    0.67000 4
46  10002   1998    0.40750 5
47  10002   1999    0.35500 6
48  10002   2000    0.40000 7
49  10002   2001    0.44000 8
50  10002   2002    0.48000 9
51  10002   2003    0.52000 10
52  10002   2004    0.52000 11
53  10002   2005    0.52000 12
54  10002   2006    0.52000 13
55  10002   2007    0.52000 14
56  10002   2008    0.52000 15
57  10002   2009    0.03500 16
58  10002   2010    0.00000 0
59  10002   2011    0.00000 0
60  10002   2012    0.00000 0
61  10002   2013    3.01250 0
62  10003   1986    0.00000 0
63  10003   1987    0.35000 0
64  10003   1988    1.00000 1
65  10003   1989    0.77500 2
66  10003   1990    0.00000 0
67  10003   1991    0.00000 0
68  10003   1992    0.00000 0
69  10003   1993    0.00000 0
70  10003   1994    0.00000 0
71  10003   1995    5.44880 0
72  10005   1986    0.00000 0
73  10005   1987    0.00000 0
74  10005   1988    0.00000 0
75  10005   1989    0.00000 0
76  10005   1990    0.00000 0
77  10005   1991    0.00000 0
78  10006   1963    1.10000 0
79  10006   1964    2.25000 1
80  10006   1965    3.07500 2
81  10006   1966    2.10000 3
82  10006   1967    2.20000 4
83  10006   1968    2.25000 5
84  10006   1969    2.40000 6
85  10006   1970    2.40000 7
86  10006   1971    2.40000 8
87  10006   1972    2.40000 9
88  10006   1973    2.40000 10
89  10006   1974    2.50000 11
90  10006   1975    2.60000 12
91  10006   1976    2.20000 13
92  10006   1977    1.95000 14
93  10006   1978    2.07500 15
94  10006   1979    2.20500 16
95  10006   1980    2.43500 17
96  10006   1981    2.69500 18
97  10006   1982    2.76000 19
98  10006   1983    1.74000 20
99  10006   1984    54.85000    21
100 10007   1986    0.00000 0
101 10007   1987    0.00000 0
102 10007   1988    0.00000 0
103 10007   1989    0.00000 0
104 10007   1990    0.00000 0
105 10008   1986    0.00000 0
106 10008   1987    0.00000 0
107 10008   1988    0.01000 0
108 10009   1986    0.07500 0
109 10009   1987    0.35000 1
110 10009   1988    0.45000 2

Related: Similar Post

----------------------------------

EDIT

Bigger sample:

PERMNO  year    DIVAMT  Consecutives
0   10000   1986    0.00000 0
1   10000   1987    0.00000 0
2   10001   1986    0.41000 0
3   10001   1987    0.42000 1
4   10001   1988    0.44000 2
5   10001   1989    0.49000 3
6   10001   1990    0.53750 4
7   10001   1991    0.58750 5
8   10001   1992    0.62250 6
9   10001   1993    0.68250 7
10  10001   1994    0.55500 8
11  10001   1995    0.39500 9
12  10001   1996    0.41500 10
13  10001   1997    0.43500 11
14  10001   1998    0.45500 12
15  10001   1999    0.47500 13
16  10001   2000    0.49500 14
17  10001   2001    0.51500 15
18  10001   2002    0.53500 16
19  10001   2003    0.13500 17
20  10001   2004    0.00000 0
21  10001   2005    0.04000 1
22  10001   2006    0.35000 2
23  10001   2007    0.66400 3
24  10001   2008    0.47800 4
25  10001   2009    0.52500 5
26  10001   2010    0.54000 6
27  10001   2011    0.54000 7
28  10001   2012    0.54000 8
29  10001   2013    0.54000 9
30  10001   2014    0.54000 10
31  10001   2015    0.54000 11
32  10001   2016    0.30000 12
33  10001   2017    13.25000    13
34  10002   1986    0.00000 0
35  10002   1987    0.00000 0
36  10002   1988    0.00000 0
37  10002   1989    0.00000 0
38  10002   1990    0.00000 0
39  10002   1991    0.00000 0
40  10002   1992    0.00000 0
41  10002   1993    0.18000 1
42  10002   1994    0.26000 2
43  10002   1995    0.32000 3
44  10002   1996    0.40000 4
45  10002   1997    0.67000 5
46  10002   1998    0.40750 6
47  10002   1999    0.35500 7
48  10002   2000    0.40000 8
49  10002   2001    0.44000 9
50  10002   2002    0.48000 10
51  10002   2003    0.52000 11
52  10002   2004    0.52000 12
53  10002   2005    0.52000 13
54  10002   2006    0.52000 14
55  10002   2007    0.52000 15
56  10002   2008    0.52000 16
57  10002   2009    0.03500 17
58  10002   2010    0.00000 0
59  10002   2011    0.00000 0
60  10002   2012    0.00000 0
61  10002   2013    3.01250 1
62  10003   1986    0.00000 0
63  10003   1987    0.35000 1
64  10003   1988    1.00000 2
65  10003   1989    0.77500 3
66  10003   1990    0.00000 0
67  10003   1991    0.00000 0
68  10003   1992    0.00000 0
69  10003   1993    0.00000 0
70  10003   1994    0.00000 0
71  10003   1995    5.44880 1
72  10005   1986    0.00000 0
73  10005   1987    0.00000 0
74  10005   1988    0.00000 0
75  10005   1989    0.00000 0
76  10005   1990    0.00000 0
77  10005   1991    0.00000 0
78  10006   1963    1.10000 0
79  10006   1964    2.25000 1
80  10006   1965    3.07500 2
81  10006   1966    2.10000 3
82  10006   1967    2.20000 4
83  10006   1968    2.25000 5
84  10006   1969    2.40000 6
85  10006   1970    2.40000 7
86  10006   1971    2.40000 8
87  10006   1972    2.40000 9
88  10006   1973    2.40000 10
89  10006   1974    2.50000 11
90  10006   1975    2.60000 12
91  10006   1976    2.20000 13
92  10006   1977    1.95000 14
93  10006   1978    2.07500 15
94  10006   1979    2.20500 16
95  10006   1980    2.43500 17
96  10006   1981    2.69500 18
97  10006   1982    2.76000 19
98  10006   1983    1.74000 20
99  10006   1984    54.85000    21
100 10007   1986    0.00000 0
101 10007   1987    0.00000 0
102 10007   1988    0.00000 0
103 10007   1989    0.00000 0
104 10007   1990    0.00000 0
105 10008   1986    0.00000 0
106 10008   1987    0.00000 0
107 10008   1988    0.01000 1
108 10009   1986    0.07500 0
109 10009   1987    0.35000 1
110 10009   1988    0.45000 2
111 10009   1989    0.50000 3
112 10009   1990    0.37500 4
113 10009   1991    0.50000 5
114 10009   1992    0.50000 6
115 10009   1993    0.53000 7
116 10009   1994    0.56000 8
117 10009   1995    0.52000 9
118 10009   1996    0.32000 10
119 10009   1997    0.36000 11
120 10009   1998    0.40000 12
121 10009   1999    0.44000 13
122 10009   2000    33.61000    14
123 10010   1986    0.00000 0
124 10010   1987    0.00000 0
125 10010   1988    0.00000 0
126 10010   1989    0.00000 0
127 10010   1990    0.00000 0
128 10010   1991    0.00000 0
129 10010   1992    0.00000 0
130 10010   1993    0.00000 0
131 10010   1994    0.00000 0
132 10010   1995    7.26250 1
133 10011   1988    0.00000 0
134 10011   1989    0.00000 0
135 10011   1990    0.00000 0
136 10011   1991    0.00000 0
137 10011   1992    0.00000 0
138 10011   1993    0.00000 0
139 10011   1994    0.00000 0
140 10011   1995    0.00000 0
141 10011   1996    0.00000 0
142 10011   1997    0.00000 0
143 10011   1998    12.00000    1
144 10012   1986    0.00000 0
145 10012   1987    0.00000 0
146 10012   1988    0.00000 0
147 10012   1989    0.00000 0
148 10012   1990    0.00000 0
149 10012   1991    0.00000 0
150 10012   1992    0.00000 0
151 10012   1993    0.00000 0
152 10012   1994    0.00000 0
153 10012   1995    0.00000 0
154 10012   1996    0.00000 0
155 10012   1997    0.00000 0
156 10012   1998    0.00000 0
157 10012   1999    0.00000 0
158 10012   2000    0.00000 0
159 10012   2001    0.00000 0
160 10012   2002    0.00000 0
161 10012   2003    0.00000 0
162 10012   2004    0.00000 0
163 10012   2005    0.00000 0
164 10013   1986    0.00000 0
165 10013   1987    0.00000 0
166 10014   1963    0.00000 0
167 10014   1964    0.00000 0
168 10014   1965    0.00000 0
169 10014   1966    0.00000 0
170 10014   1967    0.00000 0
171 10014   1968    0.00000 0
172 10014   1969    0.00000 0
173 10014   1970    0.00000 0
174 10014   1971    0.00000 0
175 10014   1972    0.00000 0
176 10014   1973    0.00000 0
177 10014   1974    0.00000 0
178 10014   1975    0.00000 0
179 10014   1976    0.00000 0
180 10014   1977    5.00000 1
181 10015   1983    0.00000 0
182 10015   1984    0.00000 0
183 10015   1985    0.00000 0
184 10015   1986    14.13500    1
185 10016   1986    0.79000 0
186 10016   1987    0.89000 1
187 10016   1988    1.14000 2
188 10016   1989    1.20000 3
189 10016   1990    0.50000 4
190 10016   1991    0.50000 5
191 10016   1992    0.50000 6
192 10016   1993    0.43500 7
193 10016   1994    0.00000 0
194 10016   1995    0.00000 0
195 10016   1996    0.00000 0
196 10016   1997    0.00000 0
197 10016   1998    0.00000 0
198 10016   1999    0.00000 0
199 10016   2000    0.00000 0
200 10016   2001    22.82000    1
201 10017   1986    0.00000 0
202 10017   1987    0.00000 0
203 10017   1988    20.00000    1
204 10018   1986    0.00000 0
205 10018   1987    0.00000 0
206 10018   1988    0.00000 0
207 10018   1989    0.00000 0
208 10018   1990    0.00000 0
209 10018   1991    0.00000 0
210 10018   1992    0.00000 0
211 10018   1993    0.00000 0
212 10018   1994    0.00000 0
213 10018   1995    0.00000 0
214 10019   1986    0.00000 0
215 10019   1987    0.07500 1
216 10019   1988    0.15000 2
217 10019   1989    0.00000 0
218 10019   1990    0.00000 0
219 10019   1991    0.00000 0
220 10019   1992    0.00000 0
221 10019   1993    0.00000 0
222 10019   1994    0.00000 0
223 10019   1995    0.00000 0
224 10019   1996    0.00000 0
225 10019   1997    0.10000 1
226 10019   1998    0.00000 0
227 10019   1999    0.00000 0
228 10019   2000    0.00000 0
229 10019   2001    0.00000 0
230 10019   2002    1.35000 1
231 10020   1986    1.00000 0
232 10020   1987    1.00000 1
233 10020   1988    0.76000 2
234 10020   1989    0.54000 3
235 10020   1990    0.61000 4
236 10020   1991    0.65000 5
237 10020   1992    0.68000 6
238 10020   1993    24.35464    7
239 10021   1986    0.00000 0
240 10021   1987    0.00000 0
241 10021   1988    0.00000 0
242 10021   1989    0.00000 0
243 10021   1990    0.00000 0
244 10021   1991    0.00000 0
245 10021   1992    0.00000 0
246 10021   1993    0.00000 0
247 10021   1994    0.00000 0
248 10021   1995    0.00000 0
249 10021   1996    6.50000 1
250 10023   1972    0.00000 0
251 10023   1973    0.15000 1
252 10024   1986    0.00000 0
253 10024   1987    0.00000 0
254 10024   1988    0.00000 0
255 10024   1989    0.00000 0
256 10024   1990    0.00000 0
257 10024   1991    0.00000 0
258 10024   1992    0.00000 0
259 10025   1986    0.00000 0
260 10025   1987    0.00000 0
261 10025   1988    0.00000 0
262 10025   1989    0.00000 0
263 10025   1990    0.00000 0
264 10025   1991    0.00000 0
265 10025   1992    0.00000 0
266 10025   1993    0.07500 1
267 10025   1994    0.08000 2
268 10025   1995    0.07500 3
269 10025   1996    0.00000 0
270 10025   1997    0.00000 0
271 10025   1998    0.00000 0
272 10025   1999    0.00000 0
273 10025   2000    0.00000 0
274 10025   2001    0.00000 0
275 10025   2002    0.00000 0
276 10025   2003    0.00000 0
277 10025   2004    0.00000 0
278 10025   2005    0.00000 0
279 10025   2006    0.00000 0
280 10025   2007    0.00000 0
281 10025   2008    0.00000 0
282 10025   2009    0.00000 0
283 10025   2010    0.00000 0
284 10025   2011    0.00000 0
285 10025   2012    0.00000 0
286 10025   2013    0.00000 0
287 10025   2014    0.00000 0
288 10025   2015    0.00000 0
289 10025   2016    1.00000 1
290 10025   2017    110.23100   2
291 10026   1986    0.00000 0
292 10026   1987    0.00000 0
293 10026   1988    0.00000 0
294 10026   1989    0.00000 0
295 10026   1990    0.00000 0
296 10026   1991    0.00000 0
297 10026   1992    0.00000 0
298 10026   1993    0.00000 0
299 10026   1994    0.00000 0
300 10026   1995    0.00000 0
301 10026   1996    0.00000 0
302 10026   1997    0.00000 0
303 10026   1998    0.00000 0
304 10026   1999    0.00000 0
305 10026   2000    0.00000 0
306 10026   2001    0.00000 0
307 10026   2002    0.00000 0
308 10026   2003    0.00000 0
309 10026   2004    0.12500 1
310 10026   2005    0.52500 2
311 10026   2006    0.31000 3
312 10026   2007    0.34750 4
313 10026   2008    0.37500 5
314 10026   2009    0.40000 6
315 10026   2010    0.44000 7
316 10026   2011    0.48250 8
317 10026   2012    0.55000 9
318 10026   2013    0.80000 10
319 10026   2014    1.32000 11
320 10026   2015    1.47000 12
321 10026   2016    1.59000 13
322 10026   2017    1.71000 14
323 10026   2018    1.85000 15
324 10026   2019    2.07500 16
325 10027   1986    0.00000 0
326 10027   1987    0.00000 0
327 10027   1988    0.00000 0
328 10027   1989    0.00000 0
329 10027   1990    7.00000 1
330 10028   1986    0.00000 0
331 10028   1987    0.00000 0
332 10028   1988    0.00000 0
333 10028   1989    0.00000 0
334 10028   1990    0.00000 0
335 10028   1991    0.00000 0
336 10028   1992    0.00000 0
337 10028   1993    0.00000 0
338 10028   1994    0.00000 0
339 10028   1995    0.00000 0
340 10028   1996    0.00000 0
341 10028   1997    0.00000 0
342 10028   1998    0.00000 0
343 10028   1999    0.00000 0
344 10028   2000    0.00000 0
345 10028   2001    0.00000 0
346 10028   2002    0.00000 0
347 10028   2003    0.00000 0
348 10028   2004    0.00000 0
349 10028   2005    0.00000 0
350 10028   2006    0.00000 0
351 10028   2007    0.00000 0
352 10028   2008    0.00000 0
353 10028   2009    0.00000 0
354 10028   2010    0.00000 0
355 10028   2011    0.00000 0
356 10028   2012    0.00000 0
357 10028   2013    0.00000 0
358 10028   2014    0.00000 0
359 10028   2015    0.00000 0
360 10028   2016    0.00000 0
361 10028   2017    0.00000 0
362 10028   2018    0.00000 0
363 10028   2019    0.00000 0
364 10029   1986    0.00000 0
365 10029   1987    0.00000 0
366 10029   1988    0.00000 0
367 10029   1989    0.00000 0
368 10029   1990    6.50000 1
369 10030   1963    1.20000 0
370 10030   1964    2.50000 1
371 10030   1965    2.85000 2
372 10030   1966    1.95000 3
373 10030   1967    1.60000 4
374 10030   1968    56.68000    5
375 10031   1983    0.00000 0
376 10031   1984    0.00000 0
377 10031   1985    0.00000 0
378 10031   1986    0.00000 0
379 10031   1987    0.00000 0
380 10031   1988    0.00000 0
381 10031   1989    0.00000 0
382 10032   1986    0.00000 0
383 10032   1987    0.00000 0
384 10032   1988    0.00000 0
385 10032   1989    0.00000 0
386 10032   1990    0.00000 0
387 10032   1991    0.00000 0
388 10032   1992    0.00000 0
389 10032   1993    0.00000 0
390 10032   1994    0.00000 0
391 10032   1995    0.00000 0
392 10032   1996    0.00000 0
393 10032   1997    0.00000 0
394 10032   1998    0.00000 0
395 10032   1999    0.00000 0
396 10032   2000    0.00000 0
397 10032   2001    0.00000 0
398 10032   2002    0.00000 0
399 10032   2003    0.00000 0
400 10032   2004    0.00000 0
401 10032   2005    0.00000 0
402 10032   2006    0.00000 0
403 10032   2007    0.00000 0
404 10032   2008    0.00000 0
405 10032   2009    0.00000 0
406 10032   2010    0.00000 0
407 10032   2011    0.00000 0
408 10032   2012    0.00000 0
409 10032   2013    0.00000 0
410 10032   2014    0.00000 0
411 10032   2015    0.00000 0
412 10032   2016    0.00000 0
413 10032   2017    0.00000 0
414 10032   2018    0.00000 0
415 10032   2019    0.00000 0
416 10033   1986    16.28000    0
417 10034   1986    0.00000 0
418 10034   1987    0.00000 0
419 10034   1988    0.36000 1
420 10034   1989    0.84000 2
421 10034   1990    1.14000 3
422 10034   1991    27.74000    4
423 10035   1986    0.00000 0
424 10035   1987    0.00000 0
425 10035   1988    0.00000 0
426 10035   1989    0.00000 0
427 10035   1990    0.00000 0
428 10035   1991    0.00000 0
429 10035   1992    0.00000 0
430 10035   1993    0.00000 0
431 10035   1994    0.00000 0
432 10035   1995    0.00000 0
433 10035   1996    0.00000 0
434 10035   1997    0.00000 0
435 10035   1998    0.00000 0
436 10035   1999    0.00000 0
437 10035   2000    0.00000 0
438 10035   2001    0.00000 0
439 10035   2002    0.00000 0
440 10035   2003    2.05000 1
441 10036   1986    0.00000 0
442 10036   1987    0.00000 0
443 10036   1988    0.00000 0
444 10036   1989    0.00000 0
445 10036   1990    0.00000 0
446 10036   1991    0.00000 0
447 10037   1986    0.00000 0
448 10037   1987    0.00000 0
449 10037   1988    0.00000 0
450 10037   1989    0.00000 0
451 10037   1990    0.00000 0
452 10037   1991    0.00000 0
453 10037   1992    0.00000 0
454 10037   1993    0.37500 1
455 10037   1994    0.29000 2
456 10037   1995    0.18000 3
457 10037   1996    0.24000 4
458 10037   1997    0.24000 5
459 10037   1998    0.21000 6
460 10037   1999    0.28000 7
461 10037   2000    0.28000 8
462 10037   2001    0.28000 9
463 10037   2002    0.31000 10
464 10037   2003    0.32000 11
465 10037   2004    21.16000    12
466 10038   1986    0.00000 0
467 10038   1987    0.00000 0
468 10038   1988    0.00000 0
469 10038   1989    0.00000 0
470 10038   1990    0.00000 0
471 10038   1991    0.00000 0
472 10039   1986    0.00000 0
473 10039   1987    0.00000 0
474 10039   1988    0.00000 0
475 10039   1989    0.00000 0
476 10039   1990    0.00000 0
477 10039   1991    0.00000 0
478 10039   1992    0.00000 0
479 10039   1993    0.00000 0
480 10039   1994    0.00000 0
481 10039   1995    0.00000 0
482 10039   1996    0.00000 0
483 10039   1997    0.00000 0
484 10039   1998    0.00000 0
485 10039   1999    0.00000 0
486 10039   2000    0.00000 0
487 10039   2001    0.00000 0
488 10041   1986    0.00000 0
489 10041   1987    0.00000 0
490 10041   1988    0.00000 0
491 10041   1989    0.00000 0
492 10041   1990    0.00000 0
493 10042   1986    0.00000 0
494 10042   1987    0.00000 0
495 10042   1988    0.00000 0
496 10042   1989    0.00000 0
497 10042   1990    0.00000 0
498 10042   1991    0.00000 0
499 10042   1992    0.00000 0
500 10042   1993    0.00000 0
501 10042   1994    0.00000 0
502 10042   1995    0.00000 0
503 10042   1996    0.00000 0
504 10042   1997    0.00000 0
505 10042   1998    0.00000 0
506 10042   1999    0.00000 0
507 10042   2000    0.00000 0
508 10042   2001    0.00000 0
509 10042   2002    0.00000 0
510 10042   2003    0.00000 0
511 10042   2004    0.00000 0
512 10042   2005    0.00000 0
513 10042   2006    0.00000 0
514 10042   2007    0.00000 0
515 10042   2008    0.40618 1
516 10043   1986    0.10000 0
517 10043   1987    0.11000 1
518 10043   1988    0.12500 2
519 10043   1989    0.14500 3
520 10043   1990    0.16500 4
521 10043   1991    0.18500 5
522 10043   1992    0.20000 6
523 10043   1993    0.22000 7
524 10043   1994    0.24000 8
525 10043   1995    0.26000 9
526 10043   1996    0.27000 10
527 10043   1997    0.30000 11
528 10043   1998    0.21000 12
529 10043   1999    26.00000    13
530 10044   1986    0.00000 0
531 10044   1987    0.00000 0
532 10044   1988    0.00000 0
533 10044   1989    0.00000 0
534 10044   1990    0.00000 0
535 10044   1991    0.00000 0
536 10044   1992    0.00000 0
537 10044   1993    0.00000 0
538 10044   1994    0.00000 0
539 10044   1995    0.00000 0
540 10044   1996    0.00000 0
541 10044   1997    0.00000 0
542 10044   1998    0.00000 0
543 10044   1999    0.00000 0
544 10044   2000    0.00000 0
545 10044   2001    0.00000 0
546 10044   2002    0.00000 0
547 10044   2003    0.15625 1
548 10044   2004    0.26000 2
549 10044   2005    0.31750 3
550 10044   2006    0.33000 4
551 10044   2007    0.38500 5
552 10044   2008    0.40000 6
553 10044   2009    0.40000 7
554 10044   2010    0.40000 8
555 10044   2011    0.40000 9
556 10044   2012    0.43000 10
557 10044   2013    0.44000 11
558 10044   2014    0.44000 12
559 10044   2015    0.48000 13
560 10044   2016    0.48000 14
561 10044   2017    0.48000 15
562 10044   2018    0.48000 16
563 10044   2019    0.48000 17
564 10045   1986    0.00000 0
565 10045   1987    0.00000 0
566 10046   1986    0.00000 0
567 10046   1987    0.00000 0
568 10046   1988    0.00000 0
569 10046   1989    0.00000 0
570 10046   1990    0.00000 0
571 10046   1991    0.00000 0
572 10046   1992    0.00000 0
573 10046   1993    0.00000 0
574 10046   1994    0.00000 0
575 10046   1995    0.00000 0
576 10047   1986    0.00000 0
577 10047   1987    0.00000 0
578 10047   1988    0.00000 0
579 10047   1989    0.00000 0
580 10047   1990    0.00000 0
581 10047   1991    0.00000 0
582 10047   1992    0.07065 1
583 10047   1993    0.56250 2
584 10047   1994    0.00000 0
585 10047   1995    0.00000 0
586 10048   1986    0.00000 0
587 10048   1987    0.00000 0
588 10048   1988    0.00000 0
589 10048   1989    0.00000 0
590 10048   1990    0.00000 0
591 10048   1991    0.00000 0
592 10048   1992    0.00000 0
593 10048   1993    0.00000 0
594 10048   1994    0.00000 0
595 10048   1995    0.00000 0
596 10048   1996    0.00000 0
597 10048   1997    0.00000 0
598 10048   1998    0.00000 0
599 10048   1999    44.21293    1
600 10050   1972    0.00000 0
601 10050   1973    0.07500 1
602 10050   1974    0.31500 2
603 10050   1975    0.37000 3
604 10050   1976    0.42000 4
605 10050   1977    0.48500 5
606 10050   1978    1.06680 6
607 10050   1979    17.06000    7
608 10051   1986    0.00000 0
609 10051   1987    0.00000 0
610 10051   1988    0.00000 0
611 10051   1989    0.00000 0
612 10051   1990    0.00000 0
613 10051   1991    0.00000 0
614 10051   1992    0.00000 0
615 10051   1993    0.00000 0
616 10051   1994    0.00000 0
617 10051   1995    0.00000 0
618 10051   1996    0.00000 0
619 10051   1997    0.00000 0
620 10051   1998    0.00000 0
621 10051   1999    0.00000 0
622 10051   2000    0.00000 0
623 10051   2001    0.00000 0
624 10051   2002    0.00000 0
625 10051   2003    0.00000 0
626 10051   2004    0.00000 0
627 10051   2005    0.00000 0
628 10051   2006    0.00000 0
629 10051   2007    0.00000 0
630 10051   2008    0.00000 0
631 10051   2009    0.00000 0
632 10051   2010    0.00000 0
633 10051   2011    0.00000 0
634 10051   2012    0.00000 0
635 10051   2013    0.00000 0
636 10051   2014    0.00000 0
637 10051   2015    0.00000 0
638 10051   2016    0.00000 0
639 10051   2017    0.00000 0
640 10051   2018    0.00000 0
641 10051   2019    0.00000 0
642 10052   1986    0.00000 0
643 10052   1987    0.00000 0
644 10052   1988    0.00000 0
645 10053   1986    0.00000 0
646 10053   1987    0.00000 0
647 10053   1988    0.00000 0
648 10053   1989    0.00000 0
649 10053   1990    0.00000 0
650 10054   1986    0.00000 0
651 10054   1987    0.00000 0
652 10055   1986    0.00000 0
653 10055   1987    0.00000 0
654 10055   1988    0.00000 0
655 10055   1989    0.00000 0
656 10055   1990    0.00000 0
657 10055   1991    0.00000 0
658 10055   1992    0.00000 0
659 10055   1993    0.00000 0
660 10055   1994    0.00000 0
661 10055   1995    0.00000 0
662 10056   1986    0.40000 0
663 10056   1987    1.60000 1
664 10056   1988    1.37500 2
665 10056   1989    0.55000 3
666 10056   1990    0.45000 4
667 10056   1991    0.00000 0
668 10056   1992    0.00000 0
669 10056   1993    0.00000 0
670 10056   1994    0.02000 1
671 10056   1995    0.11000 2
672 10056   1996    0.21000 3
673 10056   1997    0.26000 4
674 10056   1998    0.33000 5
675 10056   1999    0.38000 6
676 10056   2000    0.41000 7
677 10056   2001    0.44000 8
678 10056   2002    0.50000 9
679 10056   2003    0.60000 10
680 10056   2004    0.66000 11
681 10056   2005    0.80000 12
682 10056   2006    42.20000    13
683 10057   1963    2.25000 0
684 10057   1964    4.25000 1
685 10057   1965    3.17500 2
686 10057   1966    3.00000 3
687 10057   1967    2.50000 4
688 10057   1968    2.00000 5
689 10057   1969    1.40000 6
690 10057   1970    1.15000 7
691 10057   1971    0.80000 8
692 10057   1972    0.80000 9
693 10057   1973    0.87000 10
694 10057   1974    1.00000 11
695 10057   1975    0.50000 12
696 10057   1976    0.50000 13
697 10057   1977    0.55000 14
698 10057   1978    0.90000 15
699 10057   1979    1.20000 16
700 10057   1980    1.40000 17
701 10057   1981    1.40000 18
702 10057   1982    1.40000 19
703 10057   1983    0.65000 20
704 10057   1984    0.40000 21
705 10057   1985    0.40000 22
706 10057   1986    0.40000 23
707 10057   1987    0.40000 24
708 10057   1988    0.40000 25
709 10057   1989    0.40000 26
710 10057   1990    0.40000 27
711 10057   1991    0.40000 28
712 10057   1992    0.40000 29
713 10057   1993    0.42000 30
714 10057   1994    0.44000 31
715 10057   1995    0.48000 32
716 10057   1996    30.26000    33
717 10058   1972    0.00000 0
718 10058   1973    0.10000 1
719 10058   1974    0.00000 0
720 10058   1975    0.00000 0
721 10058   1976    0.00000 0
722 10058   1977    0.00000 0
723 10058   1978    0.00000 0
724 10058   1979    0.00000 0
725 10058   1980    0.00000 0
726 10058   1981    0.00000 0
727 10058   1982    0.00000 0
728 10058   1983    0.00000 0
729 10058   1984    0.00000 0

Expected Output

PERMNO  year    DIVAMT  Consecutives
....
182 10015   1984    0.00000 0
183 10015   1985    0.00000 0
184 10015   1986    14.13500    0
185 10016   1986    0.79000 0
186 10016   1987    0.89000 1
187 10016   1988    1.14000 2
188 10016   1989    1.20000 3
189 10016   1990    0.50000 4
....
227 10019   1999    0.00000 0
228 10019   2000    0.00000 0
229 10019   2001    0.00000 0
230 10019   2002    1.35000 0
231 10020   1986    1.00000 0
232 10020   1987    1.00000 1
233 10020   1988    0.76000 2
234 10020   1989    0.54000 3
.....
365 10029   1987    0.00000 0
366 10029   1988    0.00000 0
367 10029   1989    0.00000 0
368 10029   1990    6.50000 0
369 10030   1963    1.20000 0
370 10030   1964    2.50000 1
371 10030   1965    2.85000 2
372 10030   1966    1.95000 3

So added bigger sample, because not working my solution? – jezrael Aug 19 '20 at 08:36 — jezrael, Aug 19 '20 at 08:36

jezrael · Accepted Answer · 2020-08-19T10:50:22.927

1

Last solution working for count 0 is used consecutive groups by compare by shifted values and cumulative groups for Series g and last set 0 if match mask m by Series.mask:

m = df['DIVAMT'] == 0
g = m.ne(m.shift()).cumsum()
df["Consecutives1"] = m.groupby([df['PERMNO'], g]).cumcount().mask(m, 0)
    
print (pd.concat([df.loc[182:189],
                  df.loc[227:234],
                  df.loc[365:372]]))

     PERMNO  year  DIVAMT  Consecutives  Consecutives1
182   10015  1984   0.000             0              0
183   10015  1985   0.000             0              0
184   10015  1986  14.135             1              0
185   10016  1986   0.790             0              0
186   10016  1987   0.890             1              1
187   10016  1988   1.140             2              2
188   10016  1989   1.200             3              3
189   10016  1990   0.500             4              4
227   10019  1999   0.000             0              0
228   10019  2000   0.000             0              0
229   10019  2001   0.000             0              0
230   10019  2002   1.350             1              0
231   10020  1986   1.000             0              0
232   10020  1987   1.000             1              1
233   10020  1988   0.760             2              2
234   10020  1989   0.540             3              3
365   10029  1987   0.000             0              0
366   10029  1988   0.000             0              0
367   10029  1989   0.000             0              0
368   10029  1990   6.500             1              0
369   10030  1963   1.200             0              0
370   10030  1964   2.500             1              1
371   10030  1965   2.850             2              2
372   10030  1966   1.950             3              3

edited Aug 19 '20 at 10:50

answered Aug 19 '20 at 08:17

jezrael

822,522
95
1,334
1,252

Your solutions seem to work on the provided subsample. However, when I applied them to the entire sample, there still seems to be some sort of error when the group (```PERMNO```) changes (see e.g index 184-185, 230-231 or 368-369. I provided you a much bigger sample for recreation. @jezrael – Mcgroger Aug 19 '20 at 08:41
1

@Mcgroger - Testing. – jezrael Aug 19 '20 at 08:43
@Mcgroger - I got it, need groups by mask, not by `df` like `m.groupby` not like `df.groupby`, answr was edited. – jezrael Aug 19 '20 at 09:08
Thank you for your effort but I think this does not change anything, as the errors in e.g index 184-185, 230-231 or 368-369 still persist. Furthermore, ```m.groupby(m.cumsum()).cumcount().sub(1).clip(lower=0) == df.groupby(m.cumsum()).cumcount().sub(1).clip(lower=0)``` yields only ```True```and no ```False```. I think the problem is that if the last element of a group (```PERMNO```) has a dividend ```>0``` and the first element of the following group also has a dividend ```>0```, it counts them into the same group. – Mcgroger Aug 19 '20 at 09:31
@Mcgroger - Lunch time for me, please chck last EDIT. – jezrael Aug 19 '20 at 09:32
@Mcgroger - What is expcted output for `index 184-185, 230-231 or 368-369 ` ? – jezrael Aug 19 '20 at 10:14
See edit expected output. As the group changes (column ```PERMNO```), the counter needs to be reset to zero but I am afraid with your code it keeps counting across the groups since no .groupby() was perfromed on the column ```PERMNO```. – Mcgroger Aug 19 '20 at 10:22
1

@Mcgroger - Thank you for patience, I think I got it. Problem is you need group by consecutive values by `m` with column `PERMNO` – jezrael Aug 19 '20 at 10:43
1

That's it! Thank you for your patience and solution! – Mcgroger Aug 19 '20 at 10:56

Rolling count on condition

1 Answers1