如何在Python薪资计算代码中添加员工总工资列？

我有一份员工考勤数据表格，已经用Python的Pandas库实现了工时统计、加班时长计算和每日薪资核算，现在需要添加「总工资」列，按员工姓名（Nama）分组汇总每日薪资，但用groupby分组求和时出错，求实现方法。

考勤数据表格 #

现有代码 #

!pip install xlrd
import pandas as pd
from datetime import time, timedelta
import openpyxl

from google.colab import drive
drive.mount('/content/drive')

# Read the Excel file
path = '/content/drive/MyDrive/Colab Notebooks/Book1.xlsx'
df = pd.read_excel(path)


# Convert the 'Tgl/Waktu' column to datetime format
df['Tgl/Waktu'] = pd.to_datetime(df['Tgl/Waktu'])

# Extract the date and time from the 'Tgl/Waktu' column
df['Date'] = df['Tgl/Waktu'].dt.date
df['Time'] = df['Tgl/Waktu'].dt.time

# Group the data by employee name and date
grouped_df = df.groupby(['Nama', 'Date'])

# Set the overtime threshold to 16:30:00
overtime_threshold = time(hour=16, minute=30)

# Iterate over the grouped data
for (name, date), group in grouped_df:
    # Calculate the total work hours and overtime hours for each employee on each day
    start_time = group['Time'].min()
    end_time = group['Time'].max()
    total_hours = (timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - 
                   timedelta(hours=start_time.hour, minutes=start_time.minute, seconds=start_time.second)).total_seconds() / 3600
    if total_hours > 8:
        hours_worked = 8
        if end_time > overtime_threshold:
          overtime_hours += (end_time - overtime_threshold).total_seconds() / 3600
    else:
        hours_worked = total_hours
        overtime_hours = 0
    if end_time > overtime_threshold:
        overtime_hours += (end_time - overtime_threshold).total_seconds() / 3600
    # Calculate the payment for each employee on each day
    payment_each_date = 75000 * hours_worked + 50000 * overtime_hours
    
       # Add the total work hours, overtime hours, and payment as new columns to the dataframe
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Hours Worked'] = hours_worked
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Overtime Hours'] = overtime_hours
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Payment Each Date'] = payment_each_date

# Print the resulting dataframe
print(df)

# write DataFrame to excel
df.to_excel(excel_writer=r'/content/drive/MyDrive/Colab Notebooks/test.xlsx')

解决方案 #

1. 修复原代码的加班时长计算错误 #

原代码存在overtime_hours未初始化、重复累加的问题，导致计算结果异常，先修正每日工时逻辑：

for (name, date), group in grouped_df:
    start_time = group['Time'].min()
    end_time = group['Time'].max()
    total_hours = (timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - 
                   timedelta(hours=start_time.hour, minutes=start_time.minute, seconds=start_time.second)).total_seconds() / 3600
    
    # 初始化加班时长
    overtime_hours = 0
    if total_hours > 8:
        hours_worked = 8
        # 超过8小时的部分计入加班
        overtime_hours += total_hours - 8
    else:
        hours_worked = total_hours
    
    # 下班时间晚于16:30的部分额外计算加班（保留原代码逻辑）
    if end_time > overtime_threshold:
        overtime_duration = timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - timedelta(hours=16, minutes=30)
        overtime_hours += overtime_duration.total_seconds() / 3600
    
    payment_each_date = 75000 * hours_worked + 50000 * overtime_hours
    
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Hours Worked'] = hours_worked
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Overtime Hours'] = overtime_hours
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Payment Each Date'] = payment_each_date

2. 添加「总工资」列 #

使用groupby+transform方法，将每个员工的每日薪资求和后映射到每一行：

# 按姓名分组汇总每日薪资，将结果添加为新列
df['总工资'] = df.groupby('Nama')['Payment Each Date'].transform('sum')

完整修改后的代码 #

!pip install xlrd
import pandas as pd
from datetime import time, timedelta
import openpyxl

from google.colab import drive
drive.mount('/content/drive')

# Read the Excel file
path = '/content/drive/MyDrive/Colab Notebooks/Book1.xlsx'
df = pd.read_excel(path)

# Convert the 'Tgl/Waktu' column to datetime format
df['Tgl/Waktu'] = pd.to_datetime(df['Tgl/Waktu'])

# Extract the date and time from the 'Tgl/Waktu' column
df['Date'] = df['Tgl/Waktu'].dt.date
df['Time'] = df['Tgl/Waktu'].dt.time

# Group the data by employee name and date
grouped_df = df.groupby(['Nama', 'Date'])

# Set the overtime threshold to 16:30:00
overtime_threshold = time(hour=16, minute=30)

# Iterate over the grouped data
for (name, date), group in grouped_df:
    start_time = group['Time'].min()
    end_time = group['Time'].max()
    total_hours = (timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - 
                   timedelta(hours=start_time.hour, minutes=start_time.minute, seconds=start_time.second)).total_seconds() / 3600
    
    # 初始化加班时长
    overtime_hours = 0
    if total_hours > 8:
        hours_worked = 8
        # 超过8小时的部分计入加班
        overtime_hours += total_hours - 8
    else:
        hours_worked = total_hours
    
    # 下班时间晚于16:30的部分额外计算加班
    if end_time > overtime_threshold:
        overtime_duration = timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - timedelta(hours=16, minutes=30)
        overtime_hours += overtime_duration.total_seconds() / 3600
    
    payment_each_date = 75000 * hours_worked + 50000 * overtime_hours
    
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Hours Worked'] = hours_worked
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Overtime Hours'] = overtime_hours
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Payment Each Date'] = payment_each_date

# 添加总工资列
df['总工资'] = df.groupby('Nama')['Payment Each Date'].transform('sum')

# Print the resulting dataframe
print(df)

# write DataFrame to excel
df.to_excel(excel_writer=r'/content/drive/MyDrive/Colab Notebooks/test.xlsx')

关键说明 #

• 使用transform('sum')而非直接groupby.sum()，是因为transform会保留原DataFrame的行数，将分组求和结果映射到每一行，确保每个员工的所有记录都显示对应的总工资。
• 原代码中overtime_hours未初始化且重复累加，会导致数值错误，修复后先初始化再按条件累加。

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