我有一张表,列出了各种产品的几个客户的订购开始日期和结束日期。我想为客户在公司的订购期限(无论产品如何)获得一个价值,但是他们可以在不同时间启动和停止不同产品的订购,并且我不想重复计算重叠产品的时间段订阅。我该如何计算?
样本数据框:
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和column_length_of_sub索引的数据帧,该值对于用户545619的值为175天,对于用户163486的值为164天。但是,我认为这不是一个简单的最大值,因为从技术上讲,用户可能会重叠产品创建/删除日期。
我还想排除根本不订阅任何内容的时间段。
有谁知道我如何编写可以传递给.apply的函数,该函数将为给定用户计算实际的length_of子项?
最佳答案
您可以使用几个groupby语句(而不是“ apply”)来执行此操作以获得所需的答案:
start = a.groupby('user_id')['created_at'].min()
end = a.groupby('user_id')['removed_at'].max()
diff = (end - start).dt.days.rename('length_of_sub').to_frame()
print(diff)
length_of_sub
user_id
163486 164
545619 175
我假设您不在意给定客户可能在其他订阅之间根本没有订阅任何东西的间隙。
关于python - 给定一组可能重叠的开始时间和结束时间,如何计算订阅时间?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/57377636/