Pandas按Department Name列分组统计工时利用率groupby失效问题求解

原代码错误点 #

• 匹配Billable状态的条件错误：原始数据Billable字段值为Yes/No，原代码判断条件写的是df['Billable'] == 'Billable'，完全无法命中正确值
• 分组引用了不存在的列：计算计费/非计费利用率时，原代码用了不存在的Billable Status列，实际应该用修改后的Billable列
• 重复分组导致数据异常：最后一步多余的groupby求和操作，会把已经计算好的百分比类数据错误叠加，导致结果完全不符合预期
• 计算总时长时未排除非数值列：原代码total = df.sum(axis=1)会把字符串类列也计入求和，需要只针对科目时长列计算总和

修正后代码 #

import pandas as pd
import numpy as np

# 第一步：构建透视表，按部门、员工、计费状态聚合各科目时长
df = pd.pivot_table(df,
                    index=['Department Name','Employee Name','Billable'],
                    columns=['Subjects'],
                    values='Hours',
                    aggfunc={'Hours': np.sum})
df = df.reset_index()

# 第二步：计算单人单计费状态的科目总时长
subject_cols = [col for col in df.columns if col not in ['Department Name','Employee Name','Billable']]
df['Total'] = df[subject_cols].sum(axis=1)

# 第三步：计算单类计费状态的利用率，重命名计费状态字段
available = 168
df['utilization_rate'] = (df['Total'] / available * 100).round(2)
df['Billable'] = np.where(df['Billable'] == 'Yes', 'Billable Utilization','Non Billable Utilization')

# 第四步：统计每个员工的计费、非计费利用率
df_util = df.pivot(index=['Department Name','Employee Name'],
                   columns='Billable',
                   values='utilization_rate').reset_index()

# 第五步：统计每个员工的各科目总时长、总工时、总利用率
df_subject_total = df.groupby(['Department Name','Employee Name'], as_index=False)[subject_cols].sum(min_count=1)
df_subject_total['Total'] = df_subject_total[subject_cols].sum(axis=1).round(2)
df_subject_total['Available'] = available
df_subject_total['Utilization'] = (df_subject_total['Total'] / available).round(2)

# 第六步：合并两个统计结果得到最终输出
final_df = df_subject_total.merge(df_util, on=['Department Name','Employee Name'])

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