基于Python(Pandas)计算用户订阅逾期状态的累计持续天数

首先先做数据预处理，保证时间格式正确且按用户+时间排序：

import pandas as pd

# 转换时间列为datetime格式
churnsubset['subscription_event_timestamp'] = pd.to_datetime(churnsubset['subscription_event_timestamp'])
# 按用户ID分组后按时间升序排列，保证事件顺序正确
churnsubset = churnsubset.sort_values(by=['customer_id', 'subscription_event_timestamp']).reset_index(drop=True)

方案1：循环实现（逻辑直观，适合小数据量） #

遍历每个用户的所有事件，跟踪最后一次付费成功时间和逾期开始时间，直接计算逾期天数：

churnsubset['days_past_due'] = 0

for cust_id, group in churnsubset.groupby('customer_id'):
    past_due_start = None
    for idx, row in group.iterrows():
        curr_event = row['subscription_event']
        curr_time = row['subscription_event_timestamp']
        if curr_event == 'charged_successfully':
            # 付费成功，重置逾期标记
            past_due_start = None
            churnsubset.loc[idx, 'days_past_due'] = 0
        elif curr_event == 'subscription_went_past_due':
            # 首次进入逾期，记录开始时间，当前逾期天数为0
            past_due_start = curr_time
            churnsubset.loc[idx, 'days_past_due'] = 0
        elif curr_event == 'subscription_past_due':
            # 逾期存续期，计算当前时间和逾期开始时间的天数差
            if past_due_start:
                delta = curr_time - past_due_start
                churnsubset.loc[idx, 'days_past_due'] = round(delta.total_seconds()/(3600*24), 1)
            else:
                churnsubset.loc[idx, 'days_past_due'] = 0

方案2：pandas向量化实现（效率更高，适合十万级以上数据量） #

通过打周期标记的方式批量计算，避免逐行循环：

# 生成周期ID：用户每次付费成功后开启新的周期
churnsubset['period_id'] = churnsubset.groupby('customer_id')['subscription_event'].transform(
    lambda x: (x == 'charged_successfully').cumsum()
)

# 提取每个用户每个周期内的逾期开始时间
period_due_start = churnsubset[
    churnsubset['subscription_event'] == 'subscription_went_past_due'
].groupby(['customer_id', 'period_id'])['subscription_event_timestamp'].min().reset_index(name='due_start_time')

# 关联回原数据表
churnsubset = churnsubset.merge(period_due_start, on=['customer_id', 'period_id'], how='left')

# 批量计算逾期天数
churnsubset['days_past_due'] = churnsubset.apply(
    lambda x: round((x['subscription_event_timestamp'] - x['due_start_time']).total_seconds()/(3600*24), 1)
    if x['subscription_event'] == 'subscription_past_due' and pd.notna(x['due_start_time'])
    else 0,
    axis=1
)

# 清理临时辅助列
churnsubset = churnsubset.drop(columns=['period_id', 'due_start_time'])

注意：你提供的示例输入和期望输出中部分时间戳存在不一致，比如用户A最后一次付费成功的输入时间为2020-11-30，期望输出中为2020-11-19，实际运行时代码会按你真实数据集的时间戳计算对应天数。

内容的提问来源于stack exchange，提问作者datadummy