如何对工单DataFrame按日期分组统计四类工单数量指标？

问题背景 #

现有如下DataFrame：

ID  Date        Date_Solved      Ticket_ID  
1   01.01.2023  NULL             123    
2   01.01.2023  02.01.2023      
3   01.01.2023  02.01.2023      
4   02.01.2023  NULL             456    
5   02.01.2023  NULL        
…   …   …   …   

需要按日期分组统计为以下格式：

Date        Sum Sum_Open Sum_Solved Sum_Ticket
01.01.2023  3   3        Null       1
02.01.2023  2   3        2          2

指标定义 #

• Sum：每日新增工单总数
• Sum_Open：所有未解决（Date_Solved为空）或解决日期晚于当前统计日期的工单总数
• Sum_Solved：解决日期等于当前统计日期的工单总数
• Sum_Ticket：所有未解决且Ticket_ID不为空的工单总数

实现步骤与代码 #

1. 数据预处理 #

先将日期列转换为datetime类型，避免字符串比较出错：

import pandas as pd

# 转换日期格式
df['Date'] = pd.to_datetime(df['Date'], format='%d.%m.%Y')
df['Date_Solved'] = pd.to_datetime(df['Date_Solved'], format='%d.%m.%Y')

2. 计算各指标 #

方法一：遍历统计日期计算（直观易理解）

# 获取所有需要统计的日期并排序
stats_dates = df['Date'].unique().sort()

# 初始化结果容器
result_list = []

for current_date in stats_dates:
    # 计算当日新增工单数
    sum_val = df[df['Date'] == current_date].shape[0]
    # 计算当日解决的工单数
    sum_solved = df[df['Date_Solved'] == current_date].shape[0]
    # 计算当前未关闭的工单总数
    sum_open = df[(df['Date_Solved'].isna()) | (df['Date_Solved'] > current_date)].shape[0]
    # 计算未解决且有Ticket_ID的工单总数
    sum_ticket = df[(df['Date_Solved'].isna()) & (df['Ticket_ID'].notna())].shape[0]
    
    # 将结果存入列表
    result_list.append({
        'Date': current_date.strftime('%d.%m.%Y'),
        'Sum': sum_val,
        'Sum_Open': sum_open,
        'Sum_Solved': sum_solved if sum_solved != 0 else None,
        'Sum_Ticket': sum_ticket
    })

# 转换为目标DataFrame
result_df = pd.DataFrame(result_list)

方法二：向量化计算（大数据量下更高效）

# 生成统计日期与所有工单的笛卡尔积
date_df = pd.DataFrame({'stats_date': stats_dates})
cross_df = pd.merge(date_df, df, how='cross')

# 生成各指标的布尔判断列
cross_df['is_daily_add'] = cross_df['Date'] == cross_df['stats_date']
cross_df['is_daily_solved'] = cross_df['Date_Solved'] == cross_df['stats_date']
cross_df['is_open'] = cross_df['Date_Solved'].isna() | (cross_df['Date_Solved'] > cross_df['stats_date'])
cross_df['is_valid_ticket'] = cross_df['Date_Solved'].isna() & cross_df['Ticket_ID'].notna()

# 按统计日期分组聚合
result_df = cross_df.groupby('stats_date').agg(
    Sum=('is_daily_add', 'sum'),
    Sum_Solved=('is_daily_solved', 'sum'),
    Sum_Open=('is_open', 'sum'),
    Sum_Ticket=('is_valid_ticket', 'sum')
).reset_index()

# 格式化日期并处理Sum_Solved的空值
result_df['stats_date'] = result_df['stats_date'].strftime('%d.%m.%Y')
result_df['Sum_Solved'] = result_df['Sum_Solved'].replace(0, None)
result_df.rename(columns={'stats_date': 'Date'}, inplace=True)

3. 结果输出 #

最终result_df即为符合要求的统计结果，可直接输出或进一步调整显示格式。

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