I have a Pandas DataFrame with users subscription dates in the following format:
UserId, StartingDate, EndingDate
And I try to calculate the Churn Rate metric for every day.
What Churn Rate is:
The churn rate, also known as the rate of attrition, is the percentage of subscribers to a service who discontinue their subscriptions to that service within a given time period.
So, for every day, I go back 1 month, I get a list of unique users that had an active subscription and check how many of those don't have it any more.
I wrote the code, but it takes ages to finish, so I am looking for any kind of performance issues that might have
import pandas as pd from datetime import datetime from datetime import timedelta df = pd.read_csv("subscritpions.csv") #make sure both columns are in datetime type df['StartingDate'] = pd.to_datetime(df['StartingDate']) df['EndingDate'] = pd.to_datetime(df['EndingDate']) #get the first date of the dataframe to start the loop with it and set the stop date as today start = pd.to_datetime(df.StartingDate.min()) minDate = start stop = datetime.now() def getUsersFromADate(df,date): return df.loc[(df['StartingDate'] <= date) & (df['EndingDate'] >= date)].UserId.unique() churn =  while start <= stop: # first 30 days doesn't have a churn rate. So just append a 0 value if start < minDate + pd.DateOffset(months=1): churn.append(0) else: usersBefore = getUsersFromADate(df, start - pd.DateOffset(months=1)) usersNow = getUsersFromADate(df, start) lost = 0 for u in usersBefore: if u not in usersNow: lost += 1 churn.append(lost/len(usersBefore)) start = start + timedelta(days=1) # increase day one by one
Example of my data:
UserId StartingDate EndingDate 0 1 2013-05-09 2015-04-24 1 1 2015-04-29 2017-04-02 2 1 2017-04-05 2017-12-06 3 2 2014-02-13 2018-02-07 4 3 2013-04-25 2018-04-19