import numpy as np
import pandas as pd
from sklearn.preprocessing import minmax_scale

# 设置种子数，使得每次运行结果一样，方便读者对照
np.random.seed(20230713)
# 考生人数
N = 1800000
# 生成模拟数据，9门课程的成绩
df = pd.DataFrame(np.random.randint(1,101,(N,9)),
                  columns=['语文','数学','英语','物理','化学','生物',
                           '历史','地理','思想政治'],
                  index=range(1,N+1))
# 前三列满分为150分
df.iloc[:, :3] = df.iloc[:, :3] * 1.5
# 在6个选考科目成绩中随机生成一些缺失值，表示考生没有选考该科目
for i in range(3,9):
    df.iloc[np.random.randint(0,N,600000), i] = np.nan
# 每个考生必须并且只能考6门课程，只保留恰好具有6个成绩的行
df = df.loc[(df.count(axis=1)==6).values,:]
# 重新编号，从1开始，在行标签数字前面加上“考生”二字
df.reset_index(drop=True, inplace=True)
df.index = '考生' + (df.index+1).astype(str)
# 计算总分，自动忽略缺失值
df['总分'] = df.sum(axis=1, numeric_only=True)

# 数据写入Excel文件，并指定工作表
writer = pd.ExcelWriter('学生成绩.xlsx', engine='openpyxl')
df.to_excel(writer, sheet_name='原始成绩')
# 删除“总分”列，后面对选考科目成绩赋分后重新计算总分
df.drop('总分', axis=1, inplace=True)

def process(i):
    # 对第i列的选考科目成绩进行赋分，按指定科目的成绩降序排列，然后分段处理
    df.sort_values(by=df.columns[i], ascending=False, inplace=True)
    # 该列有效成绩数量
    n = df[df.columns[i]].count()
    # A等级（排名前3%）==>[91,100]
    df.iloc[:int(n*0.03), i] = minmax_scale(df.iloc[:int(n*0.03), i].values,
                                            feature_range=(91,100))
    # B+等级（3%-10%）==>[81,90]
    df.iloc[int(n*0.03):int(n*0.1), i] = minmax_scale(df.iloc[int(n*0.03):int(n*0.1), i],
                                                      feature_range=(81,90)).round(2)
    # B等级（10%-26%）==>[71,80]
    df.iloc[int(n*0.1):int(n*0.26), i] = minmax_scale(df.iloc[int(n*0.1):int(n*0.26), i],
                                                      feature_range=(71,80)).round(2)
    # C+等级（26%-50%）==>[61,70]
    df.iloc[int(n*0.26):int(n*0.5), i] = minmax_scale(df.iloc[int(n*0.26):int(n*0.5), i],
                                                      feature_range=(61,70)).round(2)
    # C等级（50%-74%）==>[51,60]
    df.iloc[int(n*0.5):int(n*0.74), i] = minmax_scale(df.iloc[int(n*0.5):int(n*0.74), i],
                                                      feature_range=(51,60)).round(2)
    # D+等级（74%-90%）==>[41,50]
    df.iloc[int(n*0.74):int(n*0.9), i] = minmax_scale(df.iloc[int(n*0.74):int(n*0.9), i],
                                                      feature_range=(41,50)).round(2)
    # D等级（90%-97%）==>[31,40]
    df.iloc[int(n*0.9):int(n*0.97), i] = minmax_scale(df.iloc[int(n*0.9):int(n*0.97), i],
                                                      feature_range=(31,40)).round(2)
    # E等级（97%-100%）==>[21,30]
    df.iloc[int(n*0.97):int(n*1), i] = minmax_scale(df.iloc[int(n*0.97):int(n*1), i],
                                                    feature_range=(21,30)).round(2)
# 计算后面6列选考科目成绩赋分
for i in range(3,9):
    process(i)
# 重新按行标签的考生序号升序排列
func = np.vectorize(lambda i: int(i[2:]))
df.sort_index(ascending=True, inplace=True, key=func)
# 重新计算总分
df['总分'] = df.sum(axis=1)

# 降序排序，增加“位次”列
# 总分相同的考生依次按语文、数学、英语确定位次，4项完全相同的考生并列取位次最小值
df.sort_values(by=['总分','语文','数学','英语'], ascending=False, inplace=True)
weici = [1]
def rank(i):
    if (df.iloc[i,[9,0,1,2]] == df.iloc[i-1,[9,0,1,2]]).all():
        weici.append(weici[-1])
    else:
        weici.append(weici[-1]+1)
# 只创建map对象并不会执行任何计算，访问其中元素时才会执行
# 这里转换为列表、元组、集合都可以，转换为集合比较节约空间
set(map(rank, range(1,len(df))))
df['位次'] = weici

# 查找并列的位次，设置为红色，结果写入Excel文件，然后可以按颜色筛选来过滤相同位次的考生
#weici = pd.value_counts(weici)
#repeats = weici[:len(df)-len(weici)].keys()
# 下一行功能相同，除此之外还可以在rank()函数中直接记录重复的位次
repeats = df[df.duplicated('位次')]['位次'].values
df.style.applymap(lambda r: 'color:red;' if r in repeats else '',
                  subset=['位次']).to_excel(writer, sheet_name='赋分后成绩')
writer.close()