Counting the number of consecutive binary indicators in a time series

I have a dataframe that uses binary indicators to reflect whether a customer is live during a particular month. If the customer is live, there is a 1, if not there is a 0. The dataframe looks like the below:

Customer A B C D E F G H I J
11/30/2015 1 0 1 0 0 1 1 0 0 0
12/31/2015 0 1 0 1 0 1 1 0 0 1
1/31/2016 0 0 0 0 0 1 1 0 0 1
2/29/2016 1 1 1 1 1 1 0 1 1 1
3/31/2016 1 1 0 1 1 0 1 1 0 1
4/30/2016 0 1 1 1 0 1 1 1 0 1
5/31/2016 1 1 1 1 1 1 0 1 0 1


When a customer is live, they get a 1 for the particular month. Similarly, if they are live in the following month (or any month) they get a 1 for that month also.

I want to add a column at the end of the dataframe which counts the number of customers live in the month, who were also live in the previous month.

I have calculated this in excel using this method but I am not clear on how to go about this in Python.
This is the excel formula I used.

COUNTIFS(B1:TE1,1,B2:TE2,1)


The resulting dataframe would look like this:

Customer A B C D E F G H I J Customers_live_consecutive_months
11/30/2015 1 0 1 0 0 1 1 0 0 0 0
12/31/2015 0 1 0 1 0 1 1 0 0 1 2
1/31/2016 0 0 0 0 0 1 1 0 0 1 3
2/29/2016 1 1 1 1 1 1 0 1 1 1 2
3/31/2016 1 1 0 1 1 0 1 1 0 1 6
4/30/2016 0 1 1 1 0 1 1 1 0 1 5
5/31/2016 1 1 1 1 1 1 0 1 0 1 6


2 Answers
2

With rolling:

rolling

>>> (df.rolling(2).sum() == 2).sum(1)
0 0
1 2
2 3
3 2
4 6
5 5
6 6
dtype: int64

# df['Customers_live_consecutive_months'] = (df.rolling(2).sum() == 2).sum(1)


You can do with shift

shift

((df.shift()==1)&(df.shift()==df)).sum(1)
Out[80]:
0 0
1 2
2 3
3 2
4 6
5 5
6 6
dtype: int64


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.