python - 给定一组可能重叠的开始和结束时间，如何计算订阅长度？

Question

我有一个表格，其中列出了多个客户对各种产品的订阅开始和结束日期。我希望获得客户与公司的订阅时长(无论产品如何)的一个值，但他们可以在不同时间开始和停止不同产品的订阅，并且我不想重复计算重叠产品的时间段订阅。我该如何计算这个？

示例数据框:

a = pd.DataFrame( {'index': {0: 9123, 1: 9919, 2: 191, 3: 8892, 4: 8528, 5: 8893, 6: 9124, 7: 192, 8: 8928, 9: 8602, 10: 9629}, 'user_id': {0: 163486, 1: 163486, 2: 163486, 3: 163486, 4: 163486, 5: 163486, 6: 163486, 7: 163486, 8: 545619, 9: 545619, 10: 545619}, 'prod_id': {0: 110, 1: 507, 2: 511, 3: 488, 4: 506, 5: 488, 6: 110, 7: 511, 8: 488, 9: 506, 10: 508}, 'created_at': {0: Timestamp('2016-08-13 11:38:21.706000'), 1: Timestamp('2016-08-13 11:38:21.712000'), 2: Timestamp('2016-08-13 11:38:21.719000'), 3: Timestamp('2016-08-21 15:29:02.863000'), 4: Timestamp('2016-08-21 15:29:02.877000'), 5: Timestamp('2017-01-25 00:26:24.096000'), 6: Timestamp('2017-01-25 00:27:00.205000'), 7: Timestamp('2017-01-25 00:27:00.212000'), 8: Timestamp('2016-08-10 13:55:15.608000'), 9: Timestamp('2016-08-10 13:55:15.623000'), 10: Timestamp('2016-08-10 13:55:15.636000')}, 'removed_at': {0: Timestamp('2017-01-25 00:27:00.220000'), 1: Timestamp('2017-01-25 00:27:00.231000'), 2: Timestamp('2017-01-25 00:27:00.240000'), 3: Timestamp('2017-01-25 00:26:24.108000'), 4: Timestamp('2017-01-25 00:26:24.123000'), 5: NaT, 6: NaT, 7: NaT, 8: Timestamp('2017-02-01 15:52:32.951000'), 9: Timestamp('2017-02-01 15:52:32.968000'), 10: Timestamp('2017-02-01 15:52:32.980000')}, 'length_of_sub': {0: Timedelta('164 days 12:48:38.514000'), 1: Timedelta('164 days 12:48:38.519000'), 2: Timedelta('164 days 12:48:38.521000'), 3: Timedelta('156 days 08:57:21.245000'), 4: Timedelta('156 days 08:57:21.246000'), 5: NaT, 6: NaT, 7: NaT, 8: Timedelta('175 days 01:57:17.343000'), 9: Timedelta('175 days 01:57:17.345000'), 10: Timedelta('175 days 01:57:17.344000')}} )

会产生这样的结果:

    index  user_id              prod_id              created_at  \
0    9123   163486                  110 2016-08-13 11:38:21.706   
1    9919   163486                  507 2016-08-13 11:38:21.712   
2     191   163486                  511 2016-08-13 11:38:21.719   
3    8892   163486                  488 2016-08-21 15:29:02.863   
4    8528   163486                  506 2016-08-21 15:29:02.877   
5    8893   163486                  488 2017-01-25 00:26:24.096   
6    9124   163486                  110 2017-01-25 00:27:00.205   
7     192   163486                  511 2017-01-25 00:27:00.212   
8    8928   545619                  488 2016-08-10 13:55:15.608   
9    8602   545619                  506 2016-08-10 13:55:15.623   
10   9629   545619                  508 2016-08-10 13:55:15.636   

                removed_at            length_of_sub  
0  2017-01-25 00:27:00.220 164 days 12:48:38.514000  
1  2017-01-25 00:27:00.231 164 days 12:48:38.519000  
2  2017-01-25 00:27:00.240 164 days 12:48:38.521000  
3  2017-01-25 00:26:24.108 156 days 08:57:21.245000  
4  2017-01-25 00:26:24.123 156 days 08:57:21.246000  
5                      NaT                      NaT  
6                      NaT                      NaT  
7                      NaT                      NaT  
8  2017-02-01 15:52:32.951 175 days 01:57:17.343000  
9  2017-02-01 15:52:32.968 175 days 01:57:17.345000  
10 2017-02-01 15:52:32.980 175 days 01:57:17.344000

我希望输出是一个具有 user_id 索引和列 length_of_sub 的数据框，该数据框为用户 545619 获取值 175 天，为用户 163486 获取值 164 天。不过，我认为这不是一个简单的最大值，因为从技术上讲，用户可以重叠产品创建/删除的日期。我还想排除他们根本没有订阅任何内容的时期。

有人知道如何编写一个可以传递给 .apply 的函数来计算给定用户的实际 length_of sub 吗？

Answer 1

我采取的方法是将每个 created_at 和 removed_at 视为不同的事件。当我迭代一组已排序的 created_at/removed_at 时，如果满足以下条件，我会在名为 has_sub 的变量中累积 1:事件为 created_at，如果为 removed_at，则为 -1。如果此变量大于0，我们就有订阅。

def count_sub_time(d):
    m = {'created_at': 1, 'removed_at': -1}
    d = d.rename(columns=m).stack().sort_values()

    has_sub = 0
    start_sub = pd.NaT
    count = pd.Timedelta(0)
    for (_, s), t in d.iteritems():
        if has_sub == 0 and s == 1:
            start_sub = t
        elif has_sub == 1 and s == -1:
            count += t - start_sub
        has_sub += s
    return count


b = a.set_index('user_id')[['created_at', 'removed_at']]
b.dropna().groupby(level=0).apply(count_sub_time)

user_id
163486   164 days 12:48:38.534000
545619   175 days 01:57:17.372000
dtype: timedelta64[ns]

我/你可能可以把这个问题加强一点，但逻辑就在那里。