Python+Excel数据分析实战：军事体能考核成绩评定(六)男女蛇形跑计算

原创

张国平

修改于 2021-02-16 20:02:17

1.8K0

修改于 2021-02-16 20:02:17

文章被收录于专栏：张国平_玩转树莓派张国平_玩转树莓派

这一章我们实现男子和女子30米x2蛇形跑成绩的计算，单位是数值，多少秒，全年龄段考核内容都是30米x2蛇形跑，相对也简单。

一、基本情况

通过分析男女30米x2蛇形跑“成绩计算标准表”，发现标准表也只是参照标准表，不是连续的全覆盖。比如标准中24岁以下，只规定了男子蛇形跑18.1秒的成绩为100分，18.7秒的成绩为95分，那么中间的情况多少分呢？

还是只能是我们根据公平原则去补充，在18.1秒、18.7秒的成绩之间去取平均分，数据量比较小，NO！数据量不算小，也不大，看下图就知道！

这时我也想到3公里测试的原始成绩跨度更大，使用这种手工添加补充的办法已经落后了，不过新算法再酝酿当中，也不复杂。本次我也选择手工计算，补充到成绩计算标准表里，精确到小数点后一位。

同样，通过Python的openpyxl模块读取补充成绩计算标准表中的数据，制成 {原始蛇形跑时长：分数} 格式的字典，以供主程序查询出换算成绩，再写入成绩表的对应位置中。

二、代码实现

1.我把计算男子和女子蛇形跑的文件分开，分别放入单独的模块中，男子的文件命名为serpentine_run_male.py。因为男女蛇形跑读取不同的工作簿中的标准表，分开做也方便。计算函数的原理与前面的文章相同，增加了一项判定，若原始数据类型不是整型和浮点型，则不执行计算，打印错误。

# 男子蛇形跑的成绩计算
# 从工作表“男子蛇形跑标准”中读取数据

import openpyxl
class Serpentine_run_male_standard_data():
    wb = openpyxl.load_workbook('通用训练课目考核成绩计算.xlsx')
    ws_Serpentine_run_male = wb['男子30米蛇形跑标准']

    age24 = {}     # 24岁以下，{原始男子蛇形跑秒数：分数}
    age25_27 = {}  # 25~27岁，{原始男子蛇形跑秒数：分数}
    age28_30 = {}
    age31_33 = {}
    age34_36 = {}
    age37_39 = {}

    age40_42 = {}  # 40岁以上，{原始男子蛇形跑秒数：分数}
    age43_45 = {}  # 43~45岁，{原始男子蛇形跑秒数：分数}
    age46_48 = {}
    age49_51 = {}
    age52_54 = {}
    age55_57 = {}
    age58_59 = {}

    rngs = ws_Serpentine_run_male.iter_rows(min_row=3,max_row=64,min_col=1,max_col=26)
    # 生成{原始男子蛇形跑秒数：分数}的字典
    for row in rngs:
        #print([c.value for c in row])
        age24[row[1].value] = row[0].value
        age25_27[row[3].value] = row[2].value
        age28_30[row[5].value] = row[4].value
        age31_33[row[7].value] = row[6].value
        age34_36[row[9].value] = row[8].value
        age37_39[row[11].value] = row[10].value

        age40_42[row[13].value] = row[12].value
        age43_45[row[15].value] = row[14].value
        age46_48[row[17].value] = row[16].value
        age49_51[row[19].value] = row[18].value
        age52_54[row[21].value] = row[20].value
        age55_57[row[23].value] = row[22].value
        age58_59[row[25].value] = row[24].value
    # print('-----age24-----') # 打印数据以便检查
    # for m in age24.items():
    #     print(m)
    # print('-----age25_27-----')
    # for m in age25_27.items():
    #     print(m)
    # print('-----age28_30-----')
    # for m in age28_30.items():
    #     print(m)
    # print('-----age31_33-----')
    # for m in age31_33.items():
    #     print(m)
    # print('-----age34_36-----')
    # for m in age34_36.items():
    #     print(m)
    # print('-----age37_39-----')
    # for m in age37_39.items():
    #     print(m)

    # print('-----age40_42-----')  # 打印数据以便检查
    # for m in age40_42.items():
    #     print(m)
    # print('-----age43_45-----')
    # for m in age43_45.items():
    #     print(m)
    # print('-----age46_48-----')
    # for m in age46_48.items():
    #     print(m)
    # print('-----age49_51-----')
    # for m in age49_51.items():
    #     print(m)
    # print('-----age52_54-----')
    # for m in age52_54.items():
    #     print(m)
    # print('-----age55_57-----')
    # for m in age55_57.items():
    #     print(m)
    # print('-----age58_59-----')
    # for m in age58_59.items():
    #     print(m)

    def serpentine_run_male_score_computing(self,age,original_amount):
        '''根据年龄，实际蛇形跑秒数，计算所得分数'''
        if type(original_amount) is float or int:  # 核查数据是否是整型
            if age <= 24:
                if original_amount > 21.2:
                    return 0
                elif original_amount < 18.1:
                    return 100+181 - original_amount*10  # 防止小数计算出现不准确的情况
                elif 18.1 <= original_amount <= 21.2:
                    return self.age24[original_amount]
            elif 25 <= age <= 27:
                if original_amount > 21.5:
                    return 0
                elif original_amount < 18.3:
                    return 100+183 - original_amount*10
                elif 18.3 <= original_amount <= 21.5:
                    return self.age25_27[original_amount]
            elif 28 <= age <= 30:
                if original_amount > 21.9:
                    return 0
                elif original_amount < 18.5:
                    return 100+185 - original_amount*10
                elif 18.5 <= original_amount <= 21.9:
                    return self.age28_30[original_amount]
            elif 31 <= age <= 33:
                if original_amount > 22.2:
                    return 0
                elif original_amount < 18.7:
                    return 100+187 - original_amount*10
                elif 18.7 <= original_amount <= 22.2:
                    return self.age31_33[original_amount]
            elif 34 <= age <= 36:
                if original_amount > 22.6:
                    return 0
                elif original_amount < 18.9:
                    return 100+189 - original_amount*10
                elif 18.9 <= original_amount <= 22.6:
                    return self.age34_36[original_amount]
            elif 37 <= age <= 39:
                if original_amount > 22.7:
                    return 0
                elif original_amount < 19.1:
                    return 100 + 191 - original_amount*10
                elif 19.1 <= original_amount <= 22.7:
                    return self.age37_39[original_amount]
            # 40~59
            elif 40 <= age <= 42:
                if original_amount > 22.9:
                    return 0
                elif original_amount < 19.3:
                    return 100 + 193 - original_amount*10
                elif 19.3 <= original_amount <= 22.9:
                    return self.age40_42[original_amount]
            elif 43 <= age <= 45:
                if original_amount > 23.3:
                    return 0
                elif original_amount < 19.5:
                    return 100 + 195 - original_amount*10
                elif 19.5 <= original_amount <= 23.3:
                    return self.age43_45[original_amount]
            elif 46 <= age <= 48:
                if original_amount > 23.9:
                    return 0
                elif original_amount < 19.7:
                    return 100 + 197 - original_amount*10
                elif 19.7 <= original_amount <= 23.9 :
                    return self.age46_48[original_amount]
            elif 49 <= age <= 51:
                if original_amount > 24:
                    return 0
                elif original_amount < 19.9:
                    return 100 + 199 - original_amount*10
                elif 19.9 <= original_amount <= 24 :
                    return self.age49_51[original_amount]
            elif 52 <= age <= 54:
                if original_amount > 24.5:
                    return 0
                elif original_amount < 20.6:
                    return 100 + 206 - original_amount*10
                elif 20.6 <= original_amount <= 24.5:
                    return self.age52_54[original_amount]
            elif 55 <= age <= 57:
                if original_amount > 24.8:
                    return 0
                elif original_amount < 21.1:
                    return 100 + 211 - original_amount*10
                elif 21.1 <= original_amount <= 24.8:
                    return self.age55_57[original_amount]
            elif 58 <= age <= 59:
                if original_amount > 25.4:
                    return 0
                elif original_amount < 21.3:
                    return 100 + 213 - original_amount*10
                elif 21.3 <= original_amount <= 25.4:
                    return self.age58_59[original_amount]
        else:
            print('ERROR：男子蛇形跑，原始成绩的数据类型须为整型(int)或者浮点型(float)类型')

if __name__ == "__main__":   # 测试本模块
    serpentine_run_male_std = Serpentine_run_male_standard_data()
    result = serpentine_run_male_std.serpentine_run_male_score_computing(35,18)
    print(result)

2.女子仰卧起坐的文件命名为serpentine_run_female.py，除了读取的工作簿不同，其它内容与男子大体相同。只是在参照标准编写程序时要特别细心，设置临界数值时不能出错，比如24岁以下女子大于22.6秒0分，20秒100分，但是25岁~27岁是大于22.9秒0分，20.3秒100分。这种变化也可以通过编程实现自动化计算，虽变化次数不多，但设计算法的复杂度会增加很多，这里有改进的空间，可以探索。一切相似重复的代码段都可以使用函数替代吧。

# 女子蛇形跑的成绩计算
# 从工作表“女子蛇形跑标准”中读取数据

import openpyxl
class Serpentine_run_female_standard_data():
    wb = openpyxl.load_workbook('通用训练课目考核成绩计算.xlsx')
    ws_Serpentine_run_female = wb['女子30米蛇形跑标准']

    age24 = {}     # 24岁以下，{原始女子蛇形跑秒数：分数}
    age25_27 = {}  # 25~27岁，{原始女子蛇形跑秒数：分数}
    age28_30 = {}
    age31_33 = {}
    age34_36 = {}
    age37_39 = {}

    age40_42 = {}  # 40岁以上，{原始女子蛇形跑秒数：分数}
    age43_45 = {}  # 43~45岁，{原始女子蛇形跑秒数：分数}
    age46_48 = {}
    age49_51 = {}
    age52_54 = {}
    age55_57 = {}
    age58_59 = {}

    rngs = ws_Serpentine_run_female.iter_rows(min_row=3,max_row=29,min_col=1,max_col=14)
    # 生成{原始女子蛇形跑秒数：分数}的字典
    for row in rngs:
        #print([c.value for c in row])
        age24[row[1].value] = row[0].value
        age25_27[row[2].value] = row[0].value
        age28_30[row[3].value] = row[0].value
        age31_33[row[4].value] = row[0].value
        age34_36[row[5].value] = row[0].value
        age37_39[row[6].value] = row[0].value

        age40_42[row[7].value] = row[0].value
        age43_45[row[8].value] = row[0].value
        age46_48[row[9].value] = row[0].value
        age49_51[row[10].value] = row[0].value
        age52_54[row[11].value] = row[0].value
        age55_57[row[12].value] = row[0].value
        age58_59[row[13].value] = row[0].value
    # print('-----age24-----') # 打印数据以便检查
    # for m in age24.items():
    #     print(m)
    # print('-----age25_27-----')
    # for m in age25_27.items():
    #     print(m)
    # print('-----age28_30-----')
    # for m in age28_30.items():
    #     print(m)
    # print('-----age31_33-----')
    # for m in age31_33.items():
    #     print(m)
    # print('-----age34_36-----')
    # for m in age34_36.items():
    #     print(m)
    # print('-----age37_39-----')
    # for m in age37_39.items():
    #     print(m)
    #
    # print('-----age40_42-----')  # 打印数据以便检查
    # for m in age40_42.items():
    #     print(m)
    # print('-----age43_45-----')
    # for m in age43_45.items():
    #     print(m)
    # print('-----age46_48-----')
    # for m in age46_48.items():
    #     print(m)
    # print('-----age49_51-----')
    # for m in age49_51.items():
    #     print(m)
    # print('-----age52_54-----')
    # for m in age52_54.items():
    #     print(m)
    # print('-----age55_57-----')
    # for m in age55_57.items():
    #     print(m)
    # print('-----age58_59-----')
    # for m in age58_59.items():
    #     print(m)

    def serpentine_run_female_score_computing(self,age,original_amount):
        '''根据年龄，实际蛇形跑秒数，计算所得分数'''
        if type(original_amount) is float or int:  # 核查数据是否是整型
            if age <= 24:
                if original_amount > 22.6:
                    return 0
                elif original_amount < 20:
                    return 100+200 - original_amount*10  # 防止小数计算出现不准确的情况
                elif 20 <= original_amount <= 22.6:
                    return self.age24[original_amount]
            elif 25 <= age <= 27:
                if original_amount > 22.9:
                    return 0
                elif original_amount < 20.3:
                    return 100+203 - original_amount*10
                elif 20.3 <= original_amount <= 22.9:
                    return self.age25_27[original_amount]
            elif 28 <= age <= 30:
                if original_amount > 23.2:
                    return 0
                elif original_amount < 20.6:
                    return 100+206 - original_amount*10
                elif 20.6 <= original_amount <= 23.2:
                    return self.age28_30[original_amount]
            elif 31 <= age <= 33:
                if original_amount > 23.5:
                    return 0
                elif original_amount < 20.9:
                    return 100+round((20.9 - original_amount),1)*10  # 防止小数计算出现不准确的情况
                elif 20.9 <= original_amount <= 23.5:
                    return self.age31_33[original_amount]
            elif 34 <= age <= 36:
                if original_amount > 23.8:
                    return 0
                elif original_amount < 21.2:
                    return 100+212 - original_amount*10
                elif 21.2 <= original_amount <= 23.8:
                    return self.age34_36[original_amount]
            elif 37 <= age <= 39:
                if original_amount > 24.1:
                    return 0
                elif original_amount < 21.5:
                    return 100+215 - original_amount*10
                elif 21.5 <= original_amount <= 24.1:
                    return self.age37_39[original_amount]
            # 40~59
            elif 40 <= age <= 42:
                if original_amount > 24.4:
                    return 0
                elif original_amount < 21.8:
                    return 100 + 218 - original_amount*10
                elif 21.8 <= original_amount <= 24.4:
                    return self.age40_42[original_amount]
            elif 43 <= age <= 45:
                if original_amount > 24.7:
                    return 0
                elif original_amount < 22.1:
                    return 100 + 221 - original_amount*10
                elif 22.1 <= original_amount <= 24.7:
                    return self.age43_45[original_amount]
            elif 46 <= age <= 48:
                if original_amount > 25:
                    return 0
                elif original_amount < 22.4:
                    return 100 + 224 - original_amount*10
                elif 22.4 <= original_amount <= 25:
                    return self.age46_48[original_amount]
            elif 49 <= age <= 51:
                if original_amount > 25.3:
                    return 0
                elif original_amount < 22.7:
                    return 100 + 227 - original_amount*10
                elif 22.7 <= original_amount <= 25.3:
                    return self.age49_51[original_amount]
            elif 52 <= age <= 54:
                if original_amount > 25.6:
                    return 0
                elif original_amount < 23:
                    return 100 + 230 - original_amount*10
                elif 23 <= original_amount <= 25.6:
                    return self.age52_54[original_amount]
            elif 55 <= age <= 57:
                if original_amount > 25.9:
                    return 0
                elif original_amount < 23.3:
                    return 100 + 233 - original_amount*10
                elif 23.3 <= original_amount <= 25.9:
                    return self.age55_57[original_amount]
            elif 58 <= age <= 59:
                if original_amount > 26.2:
                    return 0
                elif original_amount < 23.6:
                    return 100 + 236 - original_amount*10
                elif 23.6 <= original_amount <= 26.2:
                    return self.age58_59[original_amount]
        else:
            print('ERROR：女子蛇形跑，原始成绩的数据类型须为整型(int)或者浮点型(float)类型')

if __name__ == "__main__":   # 测试本模块
    serpentine_run_female_std = Serpentine_run_female_standard_data()
    result = serpentine_run_female_std.serpentine_run_female_score_computing(31,18.6)
    print(result)

3.对主程序training_performance.py进行修改，判断性别后，先计算单杠成绩，接着计算仰卧起坐成绩，再计算蛇形跑成绩，写入表中换算成绩的相应位置。

import openpyxl
import datetime as dt
from age_computing import age_computing                       # 导入年龄计算模块
from pullup_male import Pullup_standard_data                  # 导入男子引体向上成绩计算模块
from flex_arm_hang_female import Flex_arm_hang_standard_data  # 导入女子单杠曲臂悬垂的成绩计算模块
from situp_male import Situp_male_standard_data               # 导入男子仰卧起坐成绩计算模块
from situp_female import Situp_female_standard_data           # 导入女子仰卧起坐成绩计算模块
from serpentine_run_male import Serpentine_run_male_standard_data       # 导入男子蛇形跑成绩计算模块
from serpentine_run_female import Serpentine_run_female_standard_data   # 导入女子蛇形跑成绩计算模块

wb = openpyxl.load_workbook('通用训练课目考核成绩计算.xlsx')
ws_training_score = wb['体能考核成绩']

pullup_sd = Pullup_standard_data()             # 实例化男子引体向上成绩计算
flexarmhang_sd = Flex_arm_hang_standard_data() # 实例化女子单杠曲臂悬垂成绩计算
situp_male = Situp_male_standard_data()        # 实例化男子仰卧起坐成绩计算
situp_female = Situp_female_standard_data()    # 实例化男子仰卧起坐成绩计算
serpentine_run_male = Serpentine_run_male_standard_data()      # 实例化男子蛇形跑成绩计算
serpentine_run_female = Serpentine_run_female_standard_data()  # 实例化女子蛇形跑成绩计算

rngs = ws_training_score.iter_rows(min_row=6)
for row in rngs:
    gender = row[4].value          # 性别
    pullup = row[11].value         # 单杠原始数量
    situp =  row[7].value          # 仰卧起坐原始数量
    serpentine_run = row[9].value  # 蛇形跑原始数量
    if row[5].value:
        #print(row[5].value)
        age = age_computing(row[5].value)    #由出生日期计算年龄，精确到天
        row[6].value = age

    if gender == '男':
        if row[6].value != None:  # 年龄不能为空
            if pullup != None:    # 单杆原始数不能为空
                row[12].value = pullup_sd.pullup_score_computing(age, pullup) # 计算男子单杠或俯卧撑成绩
                #print(row[0].value,pullup,row[12].value) # 测试
            if situp != None:     # 仰卧起坐原始数不能为空
                row[8].value = situp_male.situp_male_score_computing(age, situp)  # 计算男子仰卧起坐成绩
                #print(row[0].value, situp, row[8].value)
            if serpentine_run != None:     # 蛇形跑原始数不能为空
                row[10].value = serpentine_run_male.serpentine_run_male_score_computing(age, serpentine_run)  # 计算男子蛇形跑成绩
                print(row[0].value, serpentine_run, row[10].value)

    elif gender == '女':
        if row[6].value != None:
            if pullup != None:  # 单杆原始数不能为空
                row[12].value = flexarmhang_sd.flex_arm_hang_score_computing(age, pullup) # 女子曲臂悬垂或俯卧撑成绩计算
                #print(row[0].value,pullup, row[12].value)
            if situp != None:     # 仰卧起坐原始数不能为空
                row[8].value = situp_female.situp_female_score_computing(age, situp)  # 计算女子仰卧起坐成绩
                #print(row[0].value, situp, row[8].value)
            if serpentine_run != None:     # 蛇形跑原始数不能为空
                row[10].value = serpentine_run_female.serpentine_run_female_score_computing(age, serpentine_run)  # 计算女子蛇形跑成绩
                print(row[0].value, serpentine_run, row[10].value)
    else:    # 性别输入有误
        print('序号%d %s 的性别填写错误'%(row[0].value,row[1].value))

wb.save('计算结果.xlsx')

运行后，生成文件“计算结果.xlsx”如下：