Previous chapter Python+Excel data analysis actual combat: military physical fitness assessment performance evaluation (III) men's pull-up calculation The function of men's pull-up calculation is successfully realized. In this chapter, we realize the calculation of women's horizontal bar examination results. Women's horizontal bar examination is different from men's pull-up, which is the timing of curved arm suspension.
1, Basic information
By analyzing the "performance calculation standard table" of the women's horizontal bar, it is found that the standard table is only a reference to the standard table, not a continuous full coverage. For example, under the age of 24 in the standard, it only stipulates that the score of 1 minute and 10 seconds of curved arm suspension is 100 points, and the score of 1 minute and 6 seconds is 95 points. What is the score in the middle?
Or can we only supplement according to the principle of fairness, and take the average score between 1 minute, 6 seconds and 1 minute, 10 seconds. The amount of data is relatively small. I also choose to calculate it manually and add it to the score calculation standard table, accurate to one decimal place.
Of course, according to the standard, the examination of push ups over the age of 40 is organized to replace the examination of curved arm suspension.
Similarly, the data in the supplementary score calculation standard table is read through the openpyxl module of Python, and a dictionary in the format of {original horizontal bar score: score} is made for the main program to query the conversion score, and then write it into the corresponding position of the score table.
2, Code implementation
Here, the item "gender" is added to the Original Score statistical table, and the corresponding serial numbers of other items need to be changed in the main program. And the original score of curved arm suspension is in time format. Enter a format similar to 0:01:20, so that the size can be compared in the program.
1. Calculate age as a function of high reuse frequency. I put it into a separate module, and the file is named calculate_age.py.
# Calculate the age according to the date of birth, accurate to days
# The born parameter is datetime Datetime type
import datetime as dt
def calculate_age(born):
'''Age is calculated from the date of birth to the nearest day'''
today =dt.datetime.today()
# today = today.replace(year=2020)
# print(born)
try:
birthday = born.replace(year = today.year)
except ValueError:
# The date of birth is February 29, but this year is not a run year. One day will be reduced from 29 to 28 days
birthday = born.replace(year=today.year, day=born.day-1)
# print(birthday)
if birthday > today:
return today.year - born.year - 1
else:
return today.year - born.year
2. The module for calculating women's curved arm suspension is named flex_arm_hang.py, first read out the standard data and make a dictionary in the format of {original curved arm suspension time: fraction}. Each dictionary is divided by age. The dictionary "age24" refers to the data dictionary under the age of 24, and "age25_27" refers to the data dictionary between the ages of 25 and 27.
There are some commented out statements in the following code for intermediate testing, and most of them have been deleted, because in the actual programming process, every small piece of code needs to be tested, and the next step can be continued only after it is successful, just like the building is built layer by layer.
# The number of push ups for women aged 40 and above is calculated
# Read the data from the worksheet "women's single lever curved arm suspension standard"
import openpyxl
import datetime as dt
class Flex_arm_hang_standard_data():
wb=openpyxl.load_workbook('Calculation of examination results of general training courses.xlsx')
ws_flex_arm_hang = wb['Women's single pole curved arm suspension standard']
age24={} # Under 24 years old, {original drape time: score}
age25_27={} # 25-27 years old, {original drape time: score}
age28_30={}
age31_33={}
age34_36={}
age37_39={}
rngs1 = ws_flex_arm_hang.iter_rows(min_row=3,max_row=43,min_col=1,max_col=8)
# Generate a dictionary of {original drape time: fraction}
for row in rngs1:
#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[6].value]=row[2].value
age37_39[row[7].value] = row[2].value
# print('-----age24-----') # Print data for inspection
# 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)
age40_42 = {} # Over 40 years old, {original number of push ups: score}
age43_45 = {} # 43 ~ 45 years old, {original number of push ups: score}
age46_48 = {}
age49_51 = {}
age52_54 = {}
age55_57 = {}
age58_59 = {}
rngs2 = ws_flex_arm_hang.iter_rows(min_row=49,max_row=69,min_col=1,max_col=14)
# Generate a dictionary of {original number of push ups: score}
for row in rngs2:
#print([c.value for c in row])
age40_42[row[1].value]=row[0].value
age43_45[row[3].value]=row[2].value
age46_48[row[5].value]=row[4].value
age49_51[row[7].value]=row[6].value
age52_54[row[9].value]=row[8].value
age55_57[row[11].value] = row[10].value
age58_59[row[13].value] = row[12].value
# print('-----age40_42-----') # Print data for inspection
# 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)
3. Design the calculation function flex of curved arm suspension_ arm_ hang_ performance_ Computing(), first determine the age with the IF statement; Then determine whether the bending arm suspension time or the number of push ups is less than 55 points. IF yes, 0 point will be returned; Then determine whether the bending arm suspension time or the number of push ups greater than 100 points. IF yes, it shall be calculated according to the method of exceeding 100 points; For the last 55 ~ 100 points, query the dictionary and return the corresponding score. For class I personnel passing 65 points and class II personnel passing 60 points, it will be considered in the main procedure in the later stage.
def flex_arm_hang_performance_computing(self,age,original_amount):
'''The score is calculated according to the age and the actual suspension time of the curved arm of the horizontal bar'''
if age <= 24:
# Determine whether the bending arm suspension time is less than 55 minutes. If yes, return to 0 minutes
if original_amount < dt.time(0,0,30):
return 0
# Determine whether the suspension duration of the curved arm is greater than 100 minutes. If yes, it shall be calculated according to the pull-up exceeding 100 minutes, and one minute shall be added every 5 seconds
elif original_amount > dt.time(0,1,10):
return 100+((original_amount.hour-0)*3600+(original_amount.minute-1)*60+original_amount.second-10)/5
elif dt.time(0,0,30) <= original_amount <= dt.time(0,1,10) :
return self.age24[original_amount]
elif 25 <= age <= 27:
if original_amount < dt.time(0,0,29):
return 0
elif original_amount > dt.time(0,1,5):
return 100+((original_amount.hour-0)*3600+(original_amount.minute-1)*60+original_amount.second-5)/5
elif dt.time(0,0,29) <= original_amount <= dt.time(0,1,5) :
return self.age25_27[original_amount]
elif 28 <= age <= 30:
if original_amount < dt.time(0,0,27):
return 0
elif original_amount > dt.time(0,1,3):
return 100+((original_amount.hour-0)*3600+(original_amount.minute-1)*60+original_amount.second-3)/5
elif dt.time(0,0,27) <= original_amount <= dt.time(0,1,3) :
return self.age28_30[original_amount]
elif 31 <= age <= 33:
if original_amount < dt.time(0,0,24):
return 0
elif original_amount > dt.time(0,1,0):
return 100+((original_amount.hour-0)*3600+(original_amount.minute-1)*60+original_amount.second-0)/5
elif dt.time(0,0,24) <= original_amount <= dt.time(0,1,00) :
return self.age31_33[original_amount]
elif 34 <= age <= 36:
if original_amount < dt.time(0,0,21):
return 0
elif original_amount > dt.time(0,0,57):
return 100+((original_amount.hour-0)*3600+(original_amount.minute-0)*60+original_amount.second-57)/5
elif dt.time(0,0,21) <= original_amount <= dt.time(0,0,57) :
return self.age34_36[original_amount]
elif 37 <= age <= 39:
if original_amount < dt.time(0,0,18):
return 0
elif original_amount > dt.time(0,0,54):
return 100+((original_amount.hour-0)*3600+(original_amount.minute-0)*60+original_amount.second-54)/5
elif dt.time(0,0,18) <= original_amount <= dt.time(0,0,54) :
return self.age37_39[original_amount]
# Push ups are calculated over the age of 40
elif 40 <= age <= 42:
if original_amount < 13:
return 0
elif original_amount > 30:
return 100 + (original_amount-30)*0.5
elif 13 <= original_amount <= 30 :
return self.age40_42[original_amount]
elif 43 <= age <= 45:
if original_amount < 11:
return 0
elif original_amount > 27:
return 100 + (original_amount-27)*0.5
elif 11 <= original_amount <= 27 :
return self.age43_45[original_amount]
elif 46 <= age <= 48:
if original_amount < 11:
return 0
elif original_amount > 24:
return 100 + (original_amount-24)*0.5
elif 11 <= original_amount <= 24 :
return self.age46_48[original_amount]
elif 49 <= age <= 51:
if original_amount < 9:
return 0
elif original_amount > 21:
return 100 + (original_amount-21)*0.5
elif 9 <= original_amount <= 21 :
return self.age49_51[original_amount]
elif 52 <= age <= 54:
if original_amount < 8:
return 0
elif original_amount > 19:
return 100 + (original_amount-19)*0.5
elif 8 <= original_amount <= 19 :
return self.age52_54[original_amount]
elif 55 <= age <= 57:
if original_amount < 7:
return 0
elif original_amount > 17:
return 100 + (original_amount-17)*0.5
elif 7 <= original_amount <= 17 :
return self.age55_57[original_amount]
elif 58 <= age <= 59:
if original_amount < 3:
return 0
elif original_amount > 16:
return 100 + (original_amount-16)*0.5
elif 3 <= original_amount <= 16 :
return self.age58_59[original_amount]
if __name__ == "__main__": #This module tests
flex_arm_hang_std_data = Flex_arm_hang_standard_data()
result = flex_arm_hang_std_data.flex_arm_hang_performance_computing(59,15)
#print(type(dt.time(0, 1, 10).second))
print(result)
4. Training the main program_ performance. Py. If the original score is not empty, calculate the score, and then write the score into the corresponding position of the converted score in the table. The birth date format anomaly detection and gender judgment are added here. If the gender is "male", the results of men's pull-up or push ups shall be calculated; If the gender is "female", calculate the results of women's curved arm suspension or push ups
import openpyxl
import datetime as dt
from calculate_age import calculate_age # Import age calculation module
from pullup_standard_data import Pullup_standard_data # Import men's pull-up score calculation module
from flex_arm_hang import Flex_arm_hang_standard_data # Introduce the result calculation module of curved arm suspension of women's horizontal bar
wb=openpyxl.load_workbook('Calculation of examination results of general training courses.xlsx')
ws_training_performance = wb['Physical examination results']
pullup_sd = Pullup_standard_data() #Calculation of men's pull-up performance
flexarmhang_sd = Flex_arm_hang_standard_data() #Calculation of curve arm suspension performance of women's horizontal bar
rngs = ws_training_performance.iter_rows(min_row=6)
for row in rngs:
gender = row[4].value # Gender
pullup = row[11].value # Original quantity of horizontal bar
if row[5].value:
if not type(row[5].value) is dt.datetime: # Verification date format
print('Serial number%d %s Your birth date is not in the correct format'%(row[0].value,row[1].value))
# print(row[5].value)
else:
age = calculate_age(row[5].value) # Age is calculated from the date of birth to the nearest day
row[6].value = age
if gender == 'male' : # Calculation of men's pull up or push ups
if row[6].value != None:
row[12].value = pullup_sd.pullup_performance_computing(age, pullup)
print(row[0].value,pullup,row[12].value)
elif gender == 'female': # Calculation of results of women's curved arm suspension or push ups
if row[6].value != None:
row[12].value = flexarmhang_sd.flex_arm_hang_performance_computing(age, pullup)
print(row[0].value,pullup, row[12].value)
else: #Verification of gender
print('Serial number%d %s Wrong gender'%(row[0].value,row[1].value))
wb.save('Calculation results.xlsx')
After running, the generated file "calculation result. xlsx" is as follows:
So far, we have successfully realized the function of calculating the evaluation results of women's horizontal bar.
Original is not easy, paid download, please support more:
Military physical examination score evaluation system download
