如何用Python按部门与月度分组计算员工总薪资

原始HR员工数据 #

Department       Start         End  Salary per month
0      Sales  01.01.2020  30.04.2020              1000
1     People  01.05.2020  30.07.2022              3000
2  Marketing  01.02.2020  30.12.2099              3200
3      Sales  01.03.2020  01.08.2023              1200
4   Engineer  01.04.2020  30.12.2099              3500

需求 #

使用Python实现：从最早入职员工的时间开始，按月份和部门分组计算总薪资，且每个部门单独列为一列（宽表格式）。

已尝试的代码 #

month_starts = pd.date_range(data.start.min(),data.end.max(),freq = 'MS').to_numpy()
contained = np.logical_and(
    np.greater_equal.outer(month_starts, data['start'].to_numpy()),
    np.less.outer(month_starts,data['end'].to_numpy())
)
masked = np.where(contained, np.broadcast_to(data[['salary_per_month']].transpose(), contained.shape),np.nan)
df = pd.DataFrame(masked, index = month_starts).agg('sum',axis=1).to_frame().reset_index()
df.columns = ['month', 'total_cost']

解决方案 #

可以通过生成每个员工的月度记录，再用透视表转换为宽表来实现，步骤如下：

1. 转换日期格式，统一处理在职起止时间
2. 为每个员工生成其在职期间的所有月份
3. 按月份和部门分组求和，最后透视成宽表

完整代码：

import pandas as pd

# 读取原始数据
data = pd.DataFrame({
    'Department': ['Sales', 'People', 'Marketing', 'Sales', 'Engineer'],
    'Start': ['01.01.2020', '01.05.2020', '01.02.2020', '01.03.2020', '01.04.2020'],
    'End': ['30.04.2020', '30.07.2022', '30.12.2099', '01.08.2023', '30.12.2099'],
    'Salary per month': [1000, 3000, 3200, 1200, 3500]
})

# 转换日期格式（适配日/月/年的输入格式）
data['Start'] = pd.to_datetime(data['Start'], format='%d.%m.%Y')
data['End'] = pd.to_datetime(data['End'], format='%d.%m.%Y')

# 为每个员工生成在职期间的所有月份（取每月第一天）
def generate_months(row):
    # 处理结束日期：如果结束日是当月第一天，则取前一个月作为最后一个在职月
    end_month = row['End'] if row['End'].day == 1 else row['End'] + pd.offsets.MonthEnd(0)
    end_month = end_month.replace(day=1)
    return pd.date_range(row['Start'].replace(day=1), end_month, freq='MS')

# 展开所有月度记录
data['Month'] = data.apply(generate_months, axis=1)
data_exploded = data.explode('Month').reset_index(drop=True)

# 分组求和并转换为宽表
result = data_exploded.groupby(['Month', 'Department'])['Salary per month'].sum().unstack(fill_value=0).reset_index()

# 重命名列名更贴合中文场景
result = result.rename(columns={'Month': '月份'})
print(result.head(10))

预期输出示例 #

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