Python+Excel数据分析实战：军事体能考核成绩评定(五)男女仰卧起坐计算
原创Python+Excel数据分析实战：军事体能考核成绩评定(五)男女仰卧起坐计算
原创
张国平
修改于 2021-02-16 11:24:27
修改于 2021-02-16 11:24:27
这一章我们实现男子和女子仰卧起坐成绩的计算，单位是数值，多少个，全年龄段考核内容都是仰卧起坐，相对简单一点吧。
一、基本情况
通过分析男女仰卧起坐“成绩计算标准表”，发现标准表也只是参照标准表，不是连续的全覆盖。比如标准中24岁以下，只规定了男子仰卧起坐87个的成绩为100分，82个的成绩为95分，那么中间的情况多少分呢？
男子仰卧起坐成绩计算标准表
女子仰卧起坐成绩计算标准表
还是只能是我们根据公平原则去补充，在87个、82个的成绩之间去取平均分，数据量比较小，NO！数据量不算小，也不大，看下图就知道！
男子仰卧起坐补充标准表
女子仰卧起坐补充标准表
这时我也想到3公里测试的原始成绩跨度更大，使用这种手工添加补充的办法已经落后了，不过新算法再酝酿当中，也不复杂。本次我也选择手工计算，补充到成绩计算标准表里，精确到小数点后一位。
同样，通过Python的openpyxl模块读取补充成绩计算标准表中的数据，制成 {原始仰卧起坐成绩：分数} 格式的字典，以供主程序查询出换算成绩，再写入成绩表的对应位置中。
二、代码实现
男女仰卧起坐原始成绩统计表
1.我把计算男子和女子仰卧起坐的文件分开，分别放入单独的模块中，男子的文件命名为situp_male.py。因为男女仰卧起坐读取不同的工作簿中的标准表，分开做也方便。计算函数的原理与前面的文章相同，增加了一项判定，若原始数据类型不是整型，则不执行计算，打印错误。
# 男性仰卧起坐的成绩计算
# 从工作表“男子仰卧起坐标准”中读取数据
import openpyxl
class Situp_male_standard_data():
wb=openpyxl.load_workbook('通用训练课目考核成绩计算.xlsx')
ws_situp_male = wb['男子仰卧起坐标准']
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_situp_male.iter_rows(min_row=3,max_row=49,min_col=1,max_col=24)
# 生成{原始仰卧起坐个数：分数}的字典
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[4].value]=row[2].value
age31_33[row[5].value]=row[2].value
age34_36[row[7].value]=row[6].value
age37_39[row[9].value] = row[8].value
age40_42[row[11].value] = row[10].value
age43_45[row[13].value] = row[12].value
age46_48[row[15].value] = row[14].value
age49_51[row[17].value] = row[16].value
age52_54[row[19].value] = row[18].value
age55_57[row[21].value] = row[20].value
age58_59[row[23].value] = row[22].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 situp_male_score_computing(self,age,original_amount):
'''根据年龄，实际仰卧起坐个数，计算所得分数'''
if type(original_amount) is int: # 核查数据是否是整型
if age <= 24:
if original_amount < 46:
return 0
elif original_amount > 87:
return 100+(original_amount-87)*0.5
elif 46 <= original_amount <= 87 :
return self.age24[original_amount]
elif 25 <= age <= 27:
if original_amount < 43:
return 0
elif original_amount > 82:
return 100+(original_amount-82)*0.5
elif 43 <= original_amount <= 82:
return self.age25_27[original_amount]
elif 28 <= age <= 30:
if original_amount < 41:
return 0
elif original_amount > 80:
return 100+(original_amount-80)*0.5
elif 41 <= original_amount <= 80:
return self.age28_30[original_amount]
elif 31 <= age <= 33:
if original_amount < 39:
return 0
elif original_amount > 78:
return 100+(original_amount-78)*0.5
elif 39 <= original_amount <= 78:
return self.age31_33[original_amount]
elif 34 <= age <= 36:
if original_amount < 35:
return 0
elif original_amount > 75:
return 100+(original_amount-75)*0.5
elif 35 <= original_amount <= 75:
return self.age34_36[original_amount]
elif 37 <= age <= 39:
if original_amount < 30:
return 0
elif original_amount > 74:
return 100+(original_amount-74)*0.5
elif 30 <= original_amount <= 74:
return self.age37_39[original_amount]
# 40~59
elif 40 <= age <= 42:
if original_amount < 28:
return 0
elif original_amount > 71:
return 100 + (original_amount-71)*0.5
elif 28 <= original_amount <= 71:
return self.age40_42[original_amount]
elif 43 <= age <= 45:
if original_amount < 25:
return 0
elif original_amount > 69:
return 100 + (original_amount-69)*0.5
elif 25 <= original_amount <= 69:
return self.age43_45[original_amount]
elif 46 <= age <= 48:
if original_amount < 23:
return 0
elif original_amount > 66:
return 100 + (original_amount-66)*0.5
elif 23 <= original_amount <= 66 :
return self.age46_48[original_amount]
elif 49 <= age <= 51:
if original_amount < 21:
return 0
elif original_amount > 63:
return 100 + (original_amount-63)*0.5
elif 21 <= original_amount <= 63 :
return self.age49_51[original_amount]
elif 52 <= age <= 54:
if original_amount < 19:
return 0
elif original_amount > 61:
return 100 + (original_amount-61)*0.5
elif 19 <= original_amount <= 61:
return self.age52_54[original_amount]
elif 55 <= age <= 57:
if original_amount < 17:
return 0
elif original_amount > 59:
return 100 + (original_amount-59)*0.5
elif 17 <= original_amount <= 59:
return self.age55_57[original_amount]
elif 58 <= age <= 59:
if original_amount < 15:
return 0
elif original_amount > 57:
return 100 + (original_amount-57)*0.5
elif 15 <= original_amount <= 57:
return self.age58_59[original_amount]
else:
print('ERROR：男子仰卧起坐，原始成绩的数据类型须为整型(Int)类型')
if __name__ == "__main__": # 测试本模块
situp_male_std_data = Situp_male_standard_data()
result = situp_male_std_data.situp_male_score_computing(45,80)
print(result)2.女子仰卧起坐的文件命名为situp_female.py，除了读取的工作簿不同，其它内容与男子大体相同。只是在参照标准编写程序时要特别细心，设置临界数值时不能出错，比如24岁以下女子少于41个0分，71个100分，但是25岁~27岁是少于38个0分，69个100分。这种变化也可以通过编程实现自动化计算，虽变化次数不多，但设计算法的复杂度会增加很多，这里有改进的空间，可以探索。一切相似重复的代码段都可以使用函数替代吧。
# 女性仰卧起坐的成绩计算
# 从工作表“女子仰卧起坐标准”中读取数据
import openpyxl
class Situp_female_standard_data():
wb=openpyxl.load_workbook('通用训练课目考核成绩计算.xlsx')
ws_situp_female = wb['女子仰卧起坐标准']
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_situp_female.iter_rows(min_row=3,max_row=56,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 situp_female_score_computing(self,age,original_amount):
'''根据年龄，实际仰卧起坐个数，计算所得分数'''
if type(original_amount) is int: # 核查数据是否是整型
if age <= 24:
if original_amount < 41:
return 0
elif original_amount > 71:
return 100+(original_amount-87)*0.5
elif 41 <= original_amount <= 71:
return self.age24[original_amount]
elif 25 <= age <= 27:
if original_amount < 38:
return 0
elif original_amount > 69:
return 100+(original_amount-69)*0.5
elif 38 <= original_amount <= 69:
return self.age25_27[original_amount]
elif 28 <= age <= 30:
if original_amount < 36:
return 0
elif original_amount > 67:
return 100+(original_amount-67)*0.5
elif 36 <= original_amount <= 67:
return self.age28_30[original_amount]
elif 31 <= age <= 33:
if original_amount < 34:
return 0
elif original_amount > 66:
return 100+(original_amount-66)*0.5
elif 34 <= original_amount <= 66:
return self.age31_33[original_amount]
elif 34 <= age <= 36:
if original_amount < 32:
return 0
elif original_amount > 64:
return 100+(original_amount-64)*0.5
elif 32 <= original_amount <= 64:
return self.age34_36[original_amount]
elif 37 <= age <= 39:
if original_amount < 29:
return 0
elif original_amount > 63:
return 100+(original_amount-63)*0.5
elif 29 <= original_amount <= 63:
return self.age37_39[original_amount]
# 40~59
elif 40 <= age <= 42:
if original_amount < 27:
return 0
elif original_amount > 62:
return 100 + (original_amount-62)*0.5
elif 27 <= original_amount <= 62:
return self.age40_42[original_amount]
elif 43 <= age <= 45:
if original_amount < 24:
return 0
elif original_amount > 61:
return 100 + (original_amount-61)*0.5
elif 24 <= original_amount <= 61:
return self.age43_45[original_amount]
elif 46 <= age <= 48:
if original_amount < 22:
return 0
elif original_amount > 60:
return 100 + (original_amount-60)*0.5
elif 22 <= original_amount <= 60 :
return self.age46_48[original_amount]
elif 49 <= age <= 51:
if original_amount < 19:
return 0
elif original_amount > 59:
return 100 + (original_amount-59)*0.5
elif 19 <= original_amount <= 59 :
return self.age49_51[original_amount]
elif 52 <= age <= 54:
if original_amount < 17:
return 0
elif original_amount > 57:
return 100 + (original_amount-57)*0.5
elif 17 <= original_amount <= 57:
return self.age52_54[original_amount]
elif 55 <= age <= 57:
if original_amount < 15:
return 0
elif original_amount > 56:
return 100 + (original_amount-56)*0.5
elif 15 <= original_amount <= 56:
return self.age55_57[original_amount]
elif 58 <= age <= 59:
if original_amount < 13:
return 0
elif original_amount > 55:
return 100 + (original_amount-55)*0.5
elif 13 <= original_amount <= 55:
return self.age58_59[original_amount]
else:
print('ERROR：女子仰卧起坐，原始成绩的数据类型须为整型(Int)类型')
if __name__ == "__main__": # 测试本模块
situp_female_std_data = Situp_female_standard_data()
result = situp_female_std_data.situp_female_score_computing(59,55)
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 # 导入女子仰卧起坐成绩计算模块
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() #实例化女子仰卧起坐成绩计算
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 # 仰卧起坐原始数量
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)
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)
else: # 性别输入有误
print('序号%d %s 的性别填写错误'%(row[0].value,row[1].value))
wb.save('计算结果.xlsx')运行后，生成文件“计算结果.xlsx”如下：
男女仰卧起坐计算结果
到此，我们成功实现了男女仰卧起坐成绩计算的功能。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录