How to transform and create a pandas column with 0 and 1 based on specific condition

user_5283160 0
Viewed 175

I want to create a column churn as shown. The code should group and compare each year's column Col and assign 0 if it finds Col value in next year.

In this example 3rd row is missing from 2017. Hence assigning 1.

How do I do this in pandas?

State ID    Col   Year  cost  Churn
CT    123   M     2016  10    0
CT    123   C     2016  15    0
CT    123   A     2016  10    1
CT    123   C     2016  20    0
CT    123   M     2017  10    0
CT    123   C     2017  15    0
churn pandas-groupby pandas python
1 Answers

First add all missing combinations of first 4 columns by Series.reindex with MultiIndex.from_product, then shift per first 3 columns by DataFrameGroupBy.shift and last use DataFrame.merge for original order and remove all added rows (if no parameter on it use all columns wich are same in both DataFrames):

s = df.assign(Churn=0).set_index(['State','ID','Col','Year'])['Churn']
df1 = df.merge(s.reindex(pd.MultiIndex.from_product(s.index.levels), fill_value=1)
                .groupby(level=[0,1,2])
                .shift(-1, fill_value=0)
                .reset_index())
print (df1)
  State   ID Col  Year  Churn
0    CT  123   M  2016      0
1    CT  123   C  2016      0
2    CT  123   A  2016      1
3    CT  123   M  2017      0
4    CT  123   C  2017      0
user_29010020
Related