第一步生成“课时费统计.xlsx”表

• 先用 ouput_teachers_classes_table() 生成 “教师授课表.xlsx”
• 再用后面的程序生成“课时费统计.xlsx”表

如果表格格式都规范，则可以一次生成

import pandas as pd


def ouput_teachers_classes_table(path='data/10/2019-2020第一学期开放教育开课一览表.xlsx'):
    '''
    输出“教师授课表”，这个表是后面所有操作的依据
    :param path: 上课一览表（这个表包含教师上课的所有信息，主要依据列为'课程名称', '任课教师', '授课方式'）
    :return:
    '''
    # todo 从教师授课表提取数据
    df = pd.read_excel(io=path, sheet_name=0)
    df = df[['课程名称', '任课教师', '授课方式']]

    # 保留唯一值（根据“课程名称”）,当keep='first'时，就是保留第一次出现的重复行
    df = df.drop_duplicates(subset='课程名称', keep='first')

    # todo 统计教师“月课时”数
    # 提取'授课方式'列
    month_classes_num = df['授课方式']
    # 添加'月课时'列
    df['月课时'] = month_classes_num
    # 替换课时为指定数值
    df['月课时'].replace({'网授': 6, '专业': 0, '面授': 3,'自开':3}, inplace=True)
    # df['月课时'].replace('责任', 0, inplace=True)
    # 输出到excel表，表名为“教师授课表”
    df.to_excel('output/教师授课表.xlsx', sheet_name='教师授课表', index=False)


ouput_teachers_classes_table()


def get_dict(path, key, value):
    '''
    提取需要的字典
    :param path: 文件路径
    :param key: 需要提取的键
    :param value: 需要提取的值
    :return: 返回字典
    '''
    # 读取“专兼职教师表”，建立“教师：专兼职”字典
    df = pd.read_excel(io=path, sheet_name=0)
    dt = df.set_index(key).to_dict()[value]
    return dt


# 统计“月课时总计”
path = 'output/教师授课表.xlsx'
df = pd.read_excel(io=path, sheet_name=0)
df_month = df.groupby(by='任课教师').sum()['月课时']
df_month = pd.DataFrame(df_month)  # 转换为DataFrame（'任课教师'为索引）
df_month.rename(columns={'月课时': '月课时总计'}, inplace=True)


# todo 下面所有的操作都是基于上面这个表
def add_col(df, col_name, dt):
    '''
    根据索引增加列，并将列值替换为相应字典值
    :param df: 传入表名称
    :param col_name: 需要添加的列名
    :param dt: 需要替换的字典
    :return: 返回表
    '''
    # 添加'专兼职'列，列值复制索引（索引是'任课教师'）
    df[col_name] = df_month.index
    # 替换'专兼职'列内容
    df_month[col_name].replace(dt, inplace=True)
    return df


dt = get_dict(path='data/专兼职教师表.xlsx', key='任课教师', value='专兼职')  # 提取“教师：专兼职”字典
df = add_col(df=df_month, col_name='专兼职', dt=dt)  # 增加'专兼职'列

dt = get_dict(path='data/专兼职教师表.xlsx', key='任课教师', value='职称')  # 提取“教师：职称”字典
df = add_col(df=df, col_name='职称', dt=dt)  # 增加'职称'列

df['标准'] = 30  # 添加'标准'列

dt = get_dict(path='data/作业.xlsx', key='任课教师', value='金额')  # 提取“教师：作业”字典
df = add_col(df=df, col_name='作业论文其它', dt=dt)  # 增加'专兼职'列
df.loc[df['作业论文其它'] == df.index, '作业论文其它'] = 0  # 把没有键值的替换为0


def yingfa_keshi_cost(col_a, col_b, col_c):
    '''
    应发课时津贴
    :param col_a: 专兼职
    :param col_b: 月课时总计
    :param col_b: 作业论文其它
    :return: 计算结果
    '''
    if '专职' in col_a:
        return col_b * 30
    else:
        return col_b * 30 + col_c


df['应发课时津贴'] = df.apply(lambda col: yingfa_keshi_cost(col.专兼职, col.月课时总计, col.作业论文其它), axis=1)  # 添加'应发课时津贴'列


def ying_wancheng_keshi(col_a, col_b):
    '''
    增加'应发课时津贴'列
    :param col_a: 列名称a
    :param col_b: 列名称b
    :return:
    '''
    if '专职' in col_a and '教授' in col_b:
        return 4 * 11
    elif '专职' in col_a and '讲师' in col_b:
        return 4 * 10
    elif '专职' in col_a and '助教' in col_b:
        return 4 * 9
    elif '兼职' in col_a:
        return 0


def chao_keshi(col_a, col_b):
    '''
    超课时
    :param col_a: 月课时总计
    :param col_b: 应完成课时
    :return: 计算结果
    '''
    if col_a < col_b:
        return 0
    else:
        return col_a - col_b


df['应完成课时'] = df.apply(lambda col: ying_wancheng_keshi(col.专兼职, col.职称), axis=1)  # 添加'应发课时津贴'列
df['超课时'] = df.apply(lambda col: chao_keshi(col.月课时总计, col.应完成课时), axis=1)  # 添加'超课时'列
df['超课时津贴'] = df['超课时'] * 20 * 1.5  # 添加'超课时津贴'列

# '应发课时津贴'合计
total = df['应发课时津贴'].sum()
standard = total * 0.014


# 增加'基本工作量'列
def base_work_cost(col_a, col_b, standard):
    '''
    基本工作量
    :param col_a: 月课时总计
    :param col_b: 应完成课时
    :return: 计算结果
    '''
    if col_a < col_b:
        return standard * col_a / col_b
    else:
        return standard


df['基本工作量'] = df.apply(lambda col: base_work_cost(col.月课时总计, col.应完成课时, standard), axis=1)  # 添加'基本工作量'列

df['月课时津贴'] = df['作业论文其它'] + df['月课时总计'] + df['基本工作量']

df.to_excel('output/课时费统计.xlsx', sheet_name='课时费统计')
print(df)

添加教师授课班级（终表）

import pandas as pd

path = 'data/10/2019-2020第一学期开放教育开课一览表.xlsx'
df = pd.read_excel(io=path, sheet_name=0)

# todo 唯一值列表

ls_courses = df['课程名称'].unique().tolist()
ls_classes = df['专业班级'].unique().tolist()
ls_teachers = df['任课教师'].unique().tolist()


def get_dict(df, col_name, ls_teachers):
    '''
    以'任课教师'为键，建立相应的字典
    :param df: 表
    :param col_name: 列名
    :return: 返回字典
    '''
    col_ls = df.columns.values.tolist()  # 获取列名，并转化为列表，用来获取'任课教师'所在列的索引值
    dict_ = dict()  # 创建空字典，用来存放最终结果
    for data in ls_teachers:  # 从ls_teachers列表中取'任课教师'姓名
        ls = []  # 创建一个空列表，用来存放'课程名称'
        for i in range(df.shape[0]):  # 从表中找到'任课教师'姓名，生成该'任课教师':'col_name'列表
            if df.iloc[i, col_ls.index('任课教师')] == data:  # 如果找到相同的'任课教师'
                ls.append(df.iloc[i, col_ls.index(col_name)])
        ls = list(set(ls))  # 去重
        dict_.update({data: ls})  # 得到"任课教师:班级"字典
    return dict_


dict_teachers_courses = get_dict(df, '课程名称', ls_teachers)  # 班级
dict_teachers_classes = get_dict(df, '专业班级', ls_teachers)  # 课程


def add_col_to_excel(col, dict_teachers_classes, ls_teachers):
    for key in ls_teachers:
        v_ls = dict_teachers_classes[key]
        if key == col:
            data = ''
            if len(v_ls) > 5:
                for v in v_ls[0:5]:
                    if v == v_ls[0:5][-1]:
                        data += v + '等'
                    else:
                        data += v + ','
            else:
                for v in v_ls:
                    if v == v_ls[-1]:
                        data += v
                    else:
                        data += v + ','
            return data


path = 'data/10/课时费统计.xlsx'
df = pd.read_excel(io=path, sheet_name=0)

df['专业班级'] = df.apply(lambda col: add_col_to_excel(col.任课教师, dict_teachers_classes, ls_teachers), axis=1)  # 添加'专业班级'列
df['课程名称'] = df.apply(lambda col: add_col_to_excel(col.任课教师, dict_teachers_courses, ls_teachers), axis=1)  # 添加'课程名称'列
df.to_excel('data/10/课时费统计.xlsx', sheet_name='课时费统计', index=False)