Number of consecutive TRUE's with shifts of 4 quarters

user_14208556 0
Viewed 80

I have the following dataframe:

Quarter      Tic  var
0            AAL  False
1            AAL  True
2            AAL  True
3            AAL  False
4            MSFT True
5            MSFT True
6            MSFT False
7            MSFT False
8            MSFT False
9            MSFT True
10           MSFT True
11           MSFT False

I want to count the number of consecutive quarters with a True value in the same quarter last year (so shifts of 4 periods) and with the same tic value and make this a new column. So, I want to do this for each unique value for tic. The required output is:

Quarter      tic  var    output
0            AAL  False  0
1            AAL  True   1
2            AAL  True   1
3            AAL  False  0
4            MSFT True   1
5            MSFT True   1
6            MSFT False  0
7            MSFT False  0
8            MSFT False  0
9            MSFT True   2
10           MSFT True   1
11           MSFT False  0

How should I accomplish this?

pandas-groupby pandas dataframe python
1 Answers

You can try groupby:

quarters = np.arange(len(df))%4
blocks = (1-df['var'].astype(int)).groupby(quarters).cumsum()
df['output'] = df['var'].astype(int).groupby([blocks,quarters]).cumsum()

Out:

    Quarter    var  output
0         0  False       0
1         1   True       1
2         2   True       1
3         3  False       0
4         4   True       1
5         5   True       2
6         6  False       0
7         7  False       0
8         8  False       0
9         9   True       3
10       10   True       1
11       11  False       0
user_42384080
Related