如何用Pandas对多重复标识行的员工休假数据做聚合计算？

我是Pandas新手，从PostgreSQL数据库通过以下查询语句获取了数据集：

SELECT e_code, e_name, status,date FROM {table_name} WHERE month=(%s) AND year=(%s) AND status !='P' AND status !='OFF' GROUP BY ("e_code","status","e_name","date") ORDER BY("e_code") ;

对应的DataFrame如下：

e_code  e_name       date  status
26    40        A 2023-04-21      H
24    40        A 2023-04-07      H
25    40        A 2023-04-14      H
28    42        B 2023-04-14      H
29    42        B 2023-04-21      H
..    ...      ...        ...     ...
79    80        S 2023-04-21      H
16    50        T 2023-04-10      1AL
80    50        T 2023-04-07      H
81    50        T 2023-04-14      H
82    50        T 2023-04-21      H

需求：

• 按员工统计各类休假情况：
  • status为"H"时，holiday_count加1，并记录对应的休假日期；
  • status匹配*AL（如1AL）时，other_leave_count加1，并记录对应的日期；
• 计算各类剩余休假：用对应类型的总休假数减去已用次数；
• 将最终结果输出到文件。

之前尝试的三种方法均未得到正确结果：

1. 添加holiday_date、casual_date等列，用mask标记日期并按行计数，结果错误（按行统计而非员工聚合）；
2. 使用apply方法判断status，结果同上；
3. 使用df.iterrows()遍历行，无法区分不同员工的记录。

预期输出格式：

步骤1：数据预处理 #

先确保date列是datetime类型，方便后续日期处理：

import pandas as pd

# 假设你的DataFrame名为df
df['date'] = pd.to_datetime(df['date'])

步骤2：按员工分组聚合休假数据 #

使用groupby按e_name（或e_code，确保唯一标识员工）分组，聚合各类休假的次数和日期：

# 定义聚合函数
def aggregate_leaves(group):
    # 统计H类休假：次数和日期（格式化为dd.mm.yyyy）
    holiday_mask = group['status'] == 'H'
    holiday_count = holiday_mask.sum()
    holiday_dates = ' '.join(group[holiday_mask]['date'].dt.strftime('%d.%m.%Y')) if holiday_count > 0 else 'NA'
    
    # 统计*AL类休假：匹配包含AL的status，次数和日期
    other_mask = group['status'].str.contains('AL')
    other_count = other_mask.sum()
    other_dates = ' '.join(group[other_mask]['date'].dt.strftime('%d.%m.%Y')) if other_count > 0 else 'NA'
    
    return pd.Series({
        'holiday_leave_count': holiday_count,
        'holiday_dates': holiday_dates,
        'other_leave_count': other_count,
        'other_dates': other_dates
    })

# 分组聚合
agg_result = df.groupby('e_name').apply(aggregate_leaves).reset_index()

步骤3：合并总休假额度数据 #

假设你有员工的总休假额度数据（比如从另一数据源获取，或预设字典），这里用字典示例：

# 示例：员工总休假额度，key是e_name，value是(总带薪假期数, 总其他假期数)
total_leaves = {
    'A': (14, 4),
    'B': (14, 4),
    'S': (14, 4),
    'T': (14, 4)
}

# 将总额度合并到聚合结果中
agg_result[['total_holiday_leaves', 'Total_leaves']] = agg_result['e_name'].map(total_leaves).apply(pd.Series)

步骤4：计算剩余休假数 #

# 计算剩余带薪假期
agg_result['available_holiday_leaves'] = agg_result['total_holiday_leaves'] - agg_result['holiday_leave_count']
# 计算剩余其他假期
agg_result['available_other_leaves'] = agg_result['Total_leaves'] - agg_result['other_leave_count']

步骤5：整理成预期输出格式 #

调整列顺序和名称，匹配预期输出：

# 重新排列列并命名
final_df = agg_result[[
    'e_name', 'holiday_dates', 'holiday_dates',
    'total_holiday_leaves', 'holiday_leave_count', 'available_holiday_leaves',
    'other_dates', 'other_dates', 'Total_leaves', 'other_leave_count', 'available_other_leaves'
]]

final_df.columns = [
    '员工姓名', '假期起始日期', '假期结束日期',
    '总带薪假期数', '已用带薪假期数', '剩余带薪假期数',
    '其他假期起始日期', '其他假期结束日期', '总其他假期数', '已用其他假期数', '剩余其他假期数'
]

# 替换空值为NA
final_df = final_df.fillna('NA')

步骤6：输出到文件 #

# 输出为CSV文件
final_df.to_csv('员工休假统计.csv', index=False, encoding='utf-8-sig')

# 若需要Excel格式，可使用to_excel（需安装openpyxl）
# final_df.to_excel('员工休假统计.xlsx', index=False)

关键说明 #

• 分组聚合是核心：通过groupby('e_name')确保按员工维度统计，避免之前按行统计的问题；
• 日期格式化使用dt.strftime统一格式为dd.mm.yyyy；
• 总休假额度需根据实际情况替换（比如从数据库读取或配置文件导入）。

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