Python Pandas Dataframe code takes too long to finish 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'] &lt;= date) &amp; (df['EndingDate'] &gt;= date)].UserId.unique()

churn = []
while start &lt;= stop:
    # first 30 days doesn't have a churn rate. So just append a 0 value
    if start &lt; 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
You could do the entire thing in pandas and numpy as their Cython implementation will be much faster than iterating over objects in Python. 

First simulate some data,

import pandas as pd, numpy as np
from datetime import datetime

num_samples = 50000
user_ids = np.arange(num_samples)
dates = pd.date_range("2012-01-01","2015-01-01")
start_dates = dates[np.random.randint(0,len(dates),50000)]

data = pd.DataFrame(data={"user_id": user_ids, "start_date":start_dates})
data["end_date"] = data.start_date.apply(lambda x: x + pd.DateOffset(days=np.random.randint(0,300)))


which results in data:

start_date  user_id   end_date
2013-12-15        0   2014-09-24 
2013-12-13        1   2014-01-17
2014-08-29        2   2015-03-25
2014-04-13        3   2015-01-04 
2014-01-21        4   2014-06-22


We need an output dataframe so that we don't have to painfully iterate over each date one by one:

output = pd.DataFrame({"date":pd.date_range(data.start_date.min(), datetime.now())})


and now define your functions to calculate the churn rate and apply it over the entire data frame:

def get_users_from_date(df, date):
    return df[(date &gt;= df.start_date) &amp; (date &lt;= df.end_date)].user_id

def calc_churn(df, date):
    users_before = get_users_from_date(df,date-pd.DateOffset(months=1))
    users_now = get_users_from_date(df,date)

    if len(users_before):
            return len(np.setdiff1d(users_before,users_now)) / len(users_before)
    else:
            return 0

output["churn"] = output.date.apply(lambda x: calc_churn(data,x))


This finishes for me in just:


  5.42 s ± 65.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)


Since we are using np.setdiff1d to find the difference, we don't need to unique each subset of user ids beforehand. Finding the unique subsets in get_user_from_date then passing them in, even with assume_unique=True, gets a time of:


  5.98 s ± 75 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)


while using Python set difference instead of numpy arrays gets a time of:


  7.5 s ± 114 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)


and finally, combining Python sets with list comprehensions, similar to your current implementation never finished running.

The main bottleneck is in comparing the sets of user ids, so doing that in either numpy or pandas instead of iterating over Python objects will improve performance.

On a different note, your code looks good and is really easy to read but you should use snake case instead of camel case to fit Python convention.