[Python] Efficiently calculate time to first 'purchase' event per user in Pandas DataFrame

How can I compute time to first target event per user using Pandas efficiently (with edge cases)?

I'm analyzing user behavior using a Pandas DataFrame that logs events on an app. Each row includes a user_id, event_type, and timestamp. I want to calculate the time (in seconds) from each user's first recorded event to their first occurrence of a target event (e.g., "purchase").

However, there are a few requirements that complicate things:

Some users never trigger the target event, so I want to exclude or mark them as NaN.

The timestamp column is a datetime.

I’d like this to be vectorized and efficient (not using for loops).

I want to return a DataFrame with user_id and seconds_to_first_purchase.

import pandas as pd

data = [
{'user_id': 'u1', 'event_type': 'login', 'timestamp': '2023-01-01 10:00:00'},
{'user_id': 'u1', 'event_type': 'purchase', 'timestamp': '2023-01-01 10:05:00'},
{'user_id': 'u2', 'event_type': 'login', 'timestamp': '2023-01-01 09:00:00'},
{'user_id': 'u2', 'event_type': 'scroll', 'timestamp': '2023-01-01 09:03:00'},
{'user_id': 'u3', 'event_type': 'login', 'timestamp': '2023-01-01 11:00:00'},
{'user_id': 'u3', 'event_type': 'purchase', 'timestamp': '2023-01-01 11:20:00'},
]

df = pd.DataFrame(data)
df['timestamp'] = pd.to_datetime(df['timestamp'])


What’s the cleanest and most efficient way to compute the time to first "purchase" event per user?

What I tried: I grouped the DataFrame by user_id and tried to extract the first timestamp for each user using groupby().first(), and then did the same for the first "purchase" event using a filtered DataFrame.

Then I tried merging both results to calculate the time difference like this:

first_event = df.groupby('user_id')['timestamp'].min()
first_purchase = df[df['event_type'] == 'purchase'].groupby('user_id')['timestamp'].min()
result = (first_purchase - first_event).dt.total_seconds()


What I expected: I expected this to give me a clean Series or DataFrame with user_id and the number of seconds between the user's first event and their first "purchase".

What went wrong: It mostly works, but:

• Users who never purchased are missing from the result and I want to keep them (with NaN).
• I'm not sure this is the most efficient or cleanest approach.

I’m also wondering if there's a better way to avoid intermediate merges or repetitive groupby operations.

Continue reading...