1, Basic framework
Project tasks:
Each person's physical fitness test includes four items: horizontal bar, sit ups, 30m x2 snake run and 3000m run, plus whether the body shape is qualified (BMI body mass index or PBF body fat percentage). Each original test data is converted into a score of 100 points through the standards specified by different items. Finally, the individual evaluation results are summarized and can be calculated in batch.
! [example of input raw data]( https://img-
blog.csdnimg.cn/img_convert/23341b92d84fadaceeed04a951b3c6a0.png)
Algorithm idea:
1. General idea. By reading the gender, age and altitude data of the personnel in this row on the Excel table (required for 3000 meter running, not required for other items), and the original score, query the "score calculation standard table" of this item, get the corresponding score, and write the score into the "conversion score" column of this item. Cycle through all columns.
2. Prepare the score calculation standard table. Through the analysis of various "performance calculation standard tables", it is found that the standard table is only a reference to the standard table, not a continuous full coverage. Taking the calculation standard of men's pull-up performance as an example, the standard only stipulates 30 100 points and 27 95 points for a single shot under the age of 24. What are the scores for 28 / 29?
! [calculation standard table of men's pull-up performance]( https://img-
blog.csdnimg.cn/img_convert/42cb3d94d9ab6511df55ba111d3a36bb.png)
We can only supplement according to the principle of fairness and take the average score between 30 / 27, which can be automatically converted by code (3000 meter run) or calculated manually (pull-up, sit ups, etc.) and added to the score calculation standard table, accurate to one decimal place.
! [supplementary standard table for men's horizontal bar]( https://img-
blog.csdnimg.cn/img_convert/8f1101c48f18b1ccccbbce4df6ad8e2a.png)
3. Read the score calculation standard table. Read the data in the supplementary score calculation standard table through the openpyxl module of Python, and make a dictionary in the format of {number of original horizontal bars: score} for query.
2, Age calculation
I think there is a basic principle of programming, which is to do one step and verify one step. Otherwise, verify the correctness after accumulating a lot of code. Bugs may be very complex and difficult to eliminate.
Therefore, in order to verify the correctness of the calculation in time, several elements of the calculation should be included from the beginning. Gender, altitude and original scores can be read directly, but the age is dynamic. The age of personnel may be different when the assessment is organized at different times. The best way is to calculate the age of personnel at the time of assessment through the date of birth, It can be accurate to days.
! [calculation of assessment results of general training courses. Xlsx]( https://img-
blog.csdnimg.cn/img_convert/49f638eda38d2d86a7558b07fa9081a4.png)
Function calculate_age(born). The parameter born is the date of birth. The function returns the age value.
import openpyxl # Import openpyxl module
import datetime as dt
# Open Excel file 'general training course assessment score calculation xlsx'
wb=openpyxl.load_workbook('Calculation of examination results of general training courses.xlsx')
# Open the Workbook "physical assessment results" in the Excel file
ws_training_performance = wb['Physical examination results']
def calculate_age(born):
'''Age is calculated from the date of birth to the nearest day'''
today =dt.datetime.today() #The time when the program runs, that is, the current time
# today = today.replace(year=2020) #Used to test the situation in different years
# print(born)
try:
birthday = born.replace(year = today.year)
except ValueError:
# The date of birth is February 29, but if this year is not a run year, 29 will be reduced by one day to 28 days
birthday = born.replace(year=today.year, day=born.day-1)
# print(birthday)
if birthday > today:
return today.year - born.year - 1 #If the month day of your birthday is greater than today's month day, you will lose 1 if you are less than one year old today
else:
return today.year - born.year #If the month day of your birthday is less than or equal to today's month day, you have reached one year of age and do not need to subtract 1
#iter_ The table data intercepted by the rows method starts from row 6 and column 2 (column B) of the original table
rngs = ws_training_performance.iter_rows(min_row=6,min_col=2)
for row in rngs: #The intercepted table data circulates line by line
if row[3].value: #If the birthday data is not empty, the birthday data of this row will be processed. N in row[n] starts from 0
# print(row[3].value)
age = calculate_age(row[3].value) # Age is calculated from the date of birth to the nearest day
row[4].value = age #Write age values to the age table in the table
wb.save('Calculation results.xlsx')
Run the above code to generate an Excel file "calculation result. xlsx":
! [calculation result. Xlsx]( https://img-
blog.csdnimg.cn/img_convert/1b77206f3d70fff434de5e03255f7eb2.png)
The function of automatic age calculation is realized here, but the robustness of the code is insufficient. For example, if the format of birth date is wrong, an error will pop up, which will be gradually improved in the later work.
Original is not easy, paid download, please support more:
Military physical examination score evaluation system download
A complete set of Python source code download of military physical fitness assessment score evaluation system
