使用Python Pandas透视表计算状态停留时长的时间差累加问题

核心逻辑说明 #

你原先直接对DATE字段求和的方式不符合业务逻辑，正确的实现流程是：

• 按ID、OPERATOR_ID分组后按时间排序
• 补全统计月度的月初、月末作为时间边界，解决首尾行无法计算时差的问题
• 计算每个状态对应的停留时长，筛选目标状态后累加得到统计结果

完整实现代码 #

import pandas as pd
import numpy as np
from pandas.tseries.offsets import MonthEnd, MonthBegin

# 1. 读取数据，暂不将DATE设为索引
df = pd.read_csv('StateMonthly.csv', sep=';', header=1, parse_dates=['DATE'])

# 2. 确定统计区间的月初、月末（取数据中DATE字段所在的自然月）
stat_month = df['DATE'].dt.to_period('M').iloc[0]
month_start = (stat_month + MonthBegin(0)).to_timestamp()
month_end = (stat_month + MonthEnd(0)).to_timestamp() + pd.Timedelta(days=1) - pd.Timedelta(seconds=1) # 取月末最后一秒作为边界

# 3. 按ID、OPERATOR_ID分组，每组内按时间升序排序
df = df.sort_values(['ID', 'OPERATOR_ID', 'DATE']).reset_index(drop=True)

# 4. 分组计算每个状态的结束时间，末行结束时间设为月末
df['end_time'] = df.groupby(['ID', 'OPERATOR_ID'])['DATE'].shift(-1)
df['end_time'] = df['end_time'].fillna(month_end)

# 5. 处理首行的开始时间，若首行时间晚于月初，补全月初到首行的状态时长
first_rows = df.groupby(['ID', 'OPERATOR_ID']).head(1).index
df.loc[first_rows, 'DATE'] = df.loc[first_rows, 'DATE'].apply(lambda x: min(x, month_start))

# 6. 计算单条状态记录的停留时长，单位可按需调整，此处以小时为例
df['duration_hour'] = (df['end_time'] - df['DATE']).dt.total_seconds() / 3600

# 7. 筛选DOWN和RUNNING状态，按维度累加时长得到最终结果
result = df[df['STATE'].isin(['DOWN', 'RUNNING'])].groupby(['ID', 'OPERATOR_ID', 'STATE'], as_index=False)['duration_hour'].sum()
result.rename(columns={'duration_hour': 'Time_in_State'}, inplace=True)

# 输出结果查看
print(result)

结果说明 #

最终输出的result表包含你需要的全部字段：ID、STATE、OPERATOR_ID、Time_in_State，其中Time_in_State单位为小时，你可以根据需要调整代码中时长计算的单位（比如除以60得到分钟）。

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