教师分级SABC数据处理-优快云博客

案例：根据业务数据把每项指标评分SABC，最后结合SABC等得分次数来综合定级。

import pandas as pd
import numpy as np
import datetime
today=datetime.date.today()
import re

filepath='/Users/kangyongqing/Documents/kangyq/英文信息获取/ESL教师分级/ESL分级V3/'
file1='06业务数据模块得分分布2025-03-04_副本.xlsx'

df1=pd.read_excel(filepath+file1,sheet_name='明细')
print(df1.keys())
df1['fix_level']=np.where(df1['fixcnt']>=1.2,'S',np.where(df1['fixcnt']>=0.9,'A',np.where(df1['fixcnt']>=0.6,'B','C')))
df1['ppt_level']=np.where(df1['pptslot']>=0.6,'S',np.where(df1['pptslot']>=0.4,'A',np.where(df1['pptslot']>=0.2,'B','C')))
df1['lvyue_level']=np.where(df1['lvyue']>=-0.2,'S',np.where(df1['lvyue']>=-0.3,'A',np.where(df1['lvyue']>=-0.5,'B','C')))
df1['quality_level']=np.where(df1['quality']>=0.2,'S','C')
print(df1.head(1).T)

df1['level']=df1['fix_level'].str.cat(df1['ppt_level']).str.cat(df1['lvyue_level']).str.cat(df1['quality_level'])
df1['综合评级']=np.where((df1['level'].str.findall(r'[S]',flags=re.I).str.len()>=3)&(df1['lvyue_level'].isin(['S','A']))&(df1['quality_level'].isin(['S','A'])),'S',
                         np.where((df1['level'].str.findall(r'[S,A]', flags=re.I).str.len() >= 3) & (df1['lvyue_level'].isin(['S', 'A','B'])) & (df1['quality_level'].isin(['S', 'A','B'])), 'A',
                         np.where((df1['level'].str.findall(r'[S,A,B]', flags=re.I).str.len() >= 3) & (df1['lvyue_level'].isin(['S', 'A','B'])) & (df1['quality_level'].isin(['S', 'A','B'])),'B',
                          'C')
                                  )
                         )
print(df1.head(1).T)

writer=pd.ExcelWriter(filepath+f'07ESL教师分级{str(today)}.xlsx',engine='openpyxl')
df1.to_excel(writer,sheet_name='明细',index=False)
writer._save()

综合评定如下：