使用Python模拟生成公司员工工资表

背景

       你是一家位于成都市的公司的人力资源部门的数据分析员。你需要为2024年1月份的员工工资进行计算和汇总。公司要求你使用Python编写一个脚本来自动化这一过程。

任务

1. 定义五险一金的缴纳比例：根据成都市的规定，定义员工个人和公司应缴纳的五险一金比例。
2. 计算五险一金总额：编写一个函数calculate_insurance，该函数接收员工的日薪作为参数，并返回个人和公司应缴纳的五险一金总额。
3. 生成员工信息：随机生成300名员工的姓名、性别和工号。姓名由常见的中文姓氏随机组合而成，性别随机指定为男或女，工号则按照"ID"加6位数字的格式生成。
4. 确定工作日和节假日：定义2024年1月的节假日，并生成一个工作日的日期范围，注意排除节假日。
5. 计算工资数据：对于每名员工，随机生成缺勤天数（不超过5天）和加班时长（0到20小时之间），然后计算出工资、加班费、五险一金、绩效奖金和实发工资。
6. 计算绩效奖金：定义一个函数calculate_performance_bonus，根据员工的缺勤情况和加班时长，以及项目价值（500万）来计算绩效奖金。
7. 创建工资表：将所有员工的工资数据收集到一个列表中，然后转换为一个DataFrame对象。
8. 保存工资表为Excel文件：将DataFrame对象保存为名为salary_2024_jan.xlsx的Excel文件。

要求

1. 使用pandas库来创建和操作DataFrame。
2. 使用numpy和random库来生成所需的随机数。
3. 确保所有生成的工号不重复，并且符合指定的格式。
4. 工资表应包括以下字段：工号、姓名、性别、应出勤天数、缺勤天数、实际出勤天数、基本工资、加班时长、加班费、五险一金总额、绩效奖金和实发工资。
5. 请确保Excel文件可以被正确保存，并且数据格式正确无误。

提示

1. 你可以参考给定的代码框架来完成这个任务。
2. 确保你的代码有适当的注释，以便于理解每个部分的功能。
3. 在编写代码之前，先规划好你的步骤和所需的函数。

提交

       请将你的Python脚本和生成的salary_2024_jan.xlsx文件一并提交。确保你的脚本能够在任何装有Python环境的机器上运行，并且生成正确的Excel文件。

代码

import pandas as pd
import numpy as np
import random

# 定义成都市五险一金的缴纳比例
insurance_rate = {
    'pension': 0.08,
    'medical': 0.02,
    'unemployment': 0.004,
    'housing_fund': 0.06,
    'company_pension': 0.12,
    'company_medical': 0.06,
    'company_unemployment': 0.01,
    'company_housing_fund': 0.08
}

# 计算个人和公司缴纳的五险一金总额
def calculate_insurance(salary):
    personal_insurance = salary * (insurance_rate['pension'] +
                                   insurance_rate['medical'] +
                                   insurance_rate['unemployment'] +
                                   insurance_rate['housing_fund'])
    company_insurance = salary * (insurance_rate['company_pension'] +
                                 insurance_rate['company_medical'] +
                                 insurance_rate['company_unemployment'] +
                                 insurance_rate['company_housing_fund'])
    return personal_insurance, company_insurance

# 随机生成员工信息
names = []
genders = []
employee_ids = []
id_prefix = 'ID'  # 添加一个前缀以确保首位不为0
id_counter = 100  # 起始ID编号，避免首位为0
for _ in range(300):
    name = ''.join(random.sample(['张', '李', '王', '赵', '钱', '孙', '周', '吴', '郑', '冯', '曹', '秦', '韩', '玉'],
                                 random.randint(2, 3)))
    gender = '男' if random.random() < 0.5 else '女'
    employee_id = f"{id_prefix}{id_counter:06d}"
    id_counter += 1
    names.append(name)
    genders.append(gender)
    employee_ids.append(employee_id)

# 2024年1月的节假日
holidays_jan_2024 = ['2024-01-01']

# 假设1月份工作日为22天，这里我们创建一个日期范围
workdays_jan_2024 = pd.date_range(start='2024-01-02', end='2024-01-31', freq='B')  # 'B' 是工作日的频率

# 移除节假日的工作日列表
workdays_jan_2024 = [day for day in workdays_jan_2024 if day.strftime('%Y-%m-%d') not in holidays_jan_2024]

# 生成1月份的工资表数据
salary_data = []
for i in range(300):
    employee_id = employee_ids[i]
    name = names[i]
    gender = genders[i]

    # 随机生成缺勤天数，不超过5天
    absence_days = min(random.randint(0, 5), len(workdays_jan_2024) - 1)
    actual_days_worked = len(workdays_jan_2024) - absence_days

    # 随机生成加班时长
    if random.random() < 0.5:
        overtime_hours = round(random.uniform(0, 20), 2)
    else:
        overtime_hours = 0.0

    # 计算工资
    daily_salary = 500
    gross_salary = actual_days_worked * daily_salary
    overtime_pay = overtime_hours * 30
    personal_insurance, company_insurance = calculate_insurance(daily_salary)

    # 假设项目价值为500万
    project_value = 5000000

    # 定义计算绩效奖金的函数
    def calculate_performance_bonus(absence_days, overtime_hours, project_value):
        # 定义四档绩效奖金比例
        bonus_percentages = {
            'high': 0.05,  # 全勤且加班的高奖金比例
            'mid_high': 0.04,  # 全勤无加班的较高奖金比例
            'mid_low': 0.02,  # 缺勤但加班的较低奖金比例
            'low': 0.01  # 无加班或全请假的最低奖金比例
        }

        # 根据缺勤和加班情况选择奖金百分比
        if absence_days == 0 and overtime_hours > 0:
            # 全勤且加班
            bonus_percentage = bonus_percentages['high']
        elif absence_days == 0:
            # 全勤无加班
            bonus_percentage = bonus_percentages['mid_high']
        elif absence_days > 0 and overtime_hours > 0:
            # 缺勤但加班，奖金比例根据缺勤天数和加班时长调整
            base_percentage = bonus_percentages['mid_low']
            # 缺勤天数对奖金的惩罚
            absence_penalty = (absence_days / len(workdays_jan_2024)) * 0.01
            # 加班时长对奖金的加成
            overtime_bonus = (overtime_hours / 40) * 0.01
            # 计算调整后的奖金比例，确保不低于最低奖金比例
            bonus_percentage = max(base_percentage - absence_penalty + overtime_bonus, bonus_percentages['low'])
        else:
            # 无加班或全请假
            bonus_percentage = bonus_percentages['low']

        # 计算绩效奖金
        performance_bonus = project_value * (bonus_percentage / 100)
        return performance_bonus

    # 计算绩效奖金
    performance_bonus = calculate_performance_bonus(absence_days, overtime_hours, project_value)

    net_salary = gross_salary + overtime_pay + performance_bonus - (personal_insurance + company_insurance)

    salary_data.append({
        '工号': employee_id,
        '姓名': name,
        '性别': gender,
        '应出勤天数': len(workdays_jan_2024),
        '缺勤天数': absence_days,
        '实际出勤天数': actual_days_worked,
        '基本工资': int(gross_salary),
        '加班时长': f"{overtime_hours:.2f}",
        '加班费': int(overtime_pay),
        '五险一金': int(personal_insurance + company_insurance),
        '绩效奖金': int(performance_bonus),
        '实发工资': int(net_salary)
    })

# 创建DataFrame
salary_df = pd.DataFrame(salary_data)

# 保存到Excel文件
salary_df.to_excel('salary_2024_jan.xlsx', index=False, engine='openpyxl')
print('2024年1月的工资表已保存到salary_2024_jan.xlsx文件中。')

部分效果图