Pandas - Determine if Churn occurs with missing years

user_1302551 0
Viewed 76

I have a large pandas dataframe which contains ids, years, spend values, and a slew of other columns, as shown below:

id   year    spend  .... n_columns
1    2015     321   ...    ...
1    2016     342   ...    ...
1    2017     843
1    2018     483
2    2015     234
2    2018     321
2    2019     232   ... ...

I am trying to create a new column which classifies the years based on the next years value. Something akin to:

id   year    spend   cat
1    2015     321    increase
1    2016     342    increase
1    2017     843    decrease
1    2018     483    churned           #as there is no 2019 data
2    2015     234    churned           #as there is no 2016 data
2    2018     321    decreased
2    2019     232    decreased
2    2020     200    nan               #max data only goes up to 2020

I have been trying to do this with something like the below, to get the difference between years to determine the category:

def categorize(x):
    if math.abs(x['diff']) == x['value']:
      return "churned"
    elif x['diff'] < 0:
      return "decrease"
    elif x['diff' > 0: 
      return "increase"
    else:
      return None


df = df.sort_values(['id', 'year'], ascending = True)
df['diff'] = df.groupby('id')['spend'].diff(-1)
df = df.apply(categorize, axis = 1)

However, this method and all similar methods seem to fail as there are years missing for some ids (such as id = 2 and year = 2015 above). Is there an easy way to ensure all ids all contain all of the years, even if the values are all zeroed or nulled out? Is there a better way to determine if a year is an increase/decrease/churn than how I am doing it?

Thanks!

pandas dataframe group-by python
1 Answers

Here is one way to solve it:

Expand the dataframe to include the missing rows of years; I'll use the complete function from pyjanitor for this - it exposes explicitly missing values:

# pip install pyjanitor

import janitor

tempo = (df.complete(columns=["id", 
                            {"year": lambda df: np.arange(df.year.min(), 
                                                         df.year.max() + 1)}]
                    )
          .assign(temp=lambda df: df.spend.ffill(), 
                  temp_diff=lambda df: df.temp.diff(-1)
                 )
       )

tempo

   id  year  spend   temp  temp_diff
0   1  2015  321.0  321.0      -21.0
1   1  2016  342.0  342.0     -501.0
2   1  2017  843.0  843.0      360.0
3   1  2018  483.0  483.0        0.0
4   1  2019    NaN  483.0      249.0
5   2  2015  234.0  234.0        0.0
6   2  2016    NaN  234.0        0.0
7   2  2017    NaN  234.0      -87.0
8   2  2018  321.0  321.0       89.0
9   2  2019  232.0  232.0        NaN

Next step is to create conditions, and combine with np.select:

cond2 = (tempo.spend.shift(-1).notna()) & (tempo.temp_diff.ge(0))

cond1 = (tempo.spend.shift(-1).notna()) & (tempo.temp_diff.lt(0))

cond3 = (tempo.spend.shift(-1).isna()) & (tempo.temp_diff.eq(0))

tempo["cat"] = np.select([cond1, cond2, cond3],
                         ["increase", "decrease", "churn"],
                         np.nan)


   id  year  spend   temp  temp_diff       cat
0   1  2015  321.0  321.0      -21.0  increase
1   1  2016  342.0  342.0     -501.0  increase
2   1  2017  843.0  843.0      360.0  decrease
3   1  2018  483.0  483.0        0.0     churn
4   1  2019    NaN  483.0      249.0  decrease
5   2  2015  234.0  234.0        0.0     churn
6   2  2016    NaN  234.0        0.0     churn
7   2  2017    NaN  234.0      -87.0  increase
8   2  2018  321.0  321.0       89.0  decrease
9   2  2019  232.0  232.0        NaN       nan

Filter out the null rows in spend column:

   tempo.query("spend.notna()").drop(columns = ['temp_diff', 'temp'])


   id  year  spend       cat
0   1  2015  321.0  increase
1   1  2016  342.0  increase
2   1  2017  843.0  decrease
3   1  2018  483.0     churn
5   2  2015  234.0     churn
8   2  2018  321.0  decrease
9   2  2019  232.0       nan

I used your original dataframe ( which stopped at 2019); let me know how it goes.

user_71757130
Related