python - 如何为特定 work_item 的不同阶段设置时间差

Question

在下图中，我想为每个阶段的特定 work_item 设置时间差

print (df)
         timestamp  work_item from_phase to_phase
0   1/2/2015 14:39  WI_000001      Start  Analyze
1   1/5/2015 11:48  WI_000001    Analyze   Design
2   1/5/2015 12:35  WI_000001     Design  Analyze
3   1/7/2015 11:04  WI_000001    Analyze   Deploy
4  1/27/2015 11:36  WI_000001     Deploy      End
5   1/2/2015 15:04  WI_000002      Start  Analyze
6   1/14/2015 9:46  WI_000002    Analyze   Design
7   1/14/2015 9:46  WI_000002     Design    Build
8   1/14/2015 9:46  WI_000002      Build      End

Answer 1

如果始终从每组 from_phase 和 to_phase 之间的上一行开始，则解决方案有效。

第一个转换列 to_datetime并通过DataFrameGroupBy.diff为每组差异创建新列.

然后按 dropna 删除每组的第一个 NaN 行，聚合 sum，将时间增量转换为 total_seconds最后添加 reset_index :

df['timestamp'] = pd.to_datetime(df['timestamp'])
df['diff'] = df.groupby('work_item')['timestamp'].diff()

df = (df.dropna(subset=['diff'])
        .groupby(['work_item','from_phase'])['diff']
        .sum()
        .dt.total_seconds()
        .astype(int)
        .reset_index(name='sum of differencies'))
print (df)

   work_item from_phase  sum of differencies
0  WI_000001    Analyze               416280
1  WI_000001     Deploy              1729920
2  WI_000001     Design                 2820
3  WI_000002    Analyze              1017720
4  WI_000002      Build                    0
5  WI_000002     Design                    0