有了以下数据,我想我想要一个列(DESIRED_DURATION_COL
)来计算连续真理的持续时间(根据start_datetime
):
project_id | start_datetime | diag_local_code | DESIRED_DURATION_COL | 1 | 2017-01-18 | 假 | 0 | 1
---|---|---|---|
2019-04-14 | 真正 | 0 | |
2019-04-17 | 真正 | 3 | |
2019-04-19 | 假 | 0 | |
2019-04-23 | 真正 | 0 | |
2019-04-25 | 真正 | 2 | |
2019-04-30 | 真正 | 7 | |
2019-05-21 | 假 | 0 |
计算duration
的解:
m = df['diag_local_code']
dt = df[m].groupby(['project_id', (~m).cumsum()])['start_datetime'].transform('first')
df['duration'] = df['start_datetime'].sub(dt).dt.days.fillna(0)
这是如何工作的?
在反向diag_local_code
上使用cumsum
来识别每个project_id
的连续组,然后过滤diag_local_code
为True
的行,然后将过滤后的数据帧分组,并将start_datetime
与first
转换为跨每组广播第一个日期值,最后从start_datetime
中减去广播的日期值以计算所需的持续时间
结果
project_id start_datetime diag_local_code duration
0 1 2017-01-18 False 0.0
1 1 2019-04-14 True 0.0
2 1 2019-04-17 True 3.0
3 1 2019-04-19 False 0.0
4 1 2019-04-23 True 0.0
5 1 2019-04-25 True 2.0
6 1 2019-04-30 True 7.0
7 1 2019-05-21 False 0.0
计算True
值平均连续持续时间的解
m = df['diag_local_code']
(
df[m].groupby(['project_id', (~m).cumsum()])['start_datetime']
.agg(np.ptp).dt.days.groupby(level=0).mean().reset_index(name='avg_duration')
)
结果: project_id avg_duration
0 1 5.0
您可以按project_id
列分组,并将每组拆分为连续的值组。然后检查groups值是否全部为True
def avg_duration(group):
subgroup = group.groupby(group['diag_local_code'].diff().ne(0).cumsum())
true_count = subgroup.apply(lambda g: g['diag_local_code'].all()).sum()
true_last_sum = subgroup.apply(lambda g: g.iloc[-1]['DESIRED_DURATION_COL'] if g['diag_local_code'].all() else 0).sum()
return true_last_sum/true_count
out = df.groupby('project_id').apply(avg_duration).to_frame('avg_duration').reset_index()
print(out)
project_id avg_duration
0 1 5.0