jupyter/体测单位/政法委.ipynb at master

512song/jupyter

0 Fork 0

Files

T

512song a00eae7487 20231027

2023-10-27 03:05:33 +00:00

43 KiB

Raw Permalink Blame History

体测人员导入

In [ ]:

import openpyxl
import json


wb = openpyxl.load_workbook('data/政法委名单.xlsx')
sheet = wb.active
# sheets = wb.sheetnames
person = {}

for n in range(2, sheet.max_row+1):
    code = int(sheet.cell(n, 1).value)
    person.setdefault(code, {})
    dict1 = {}
    dict1['name'] = sheet.cell(n, 2).value
    dict1['unit'] = '政法委'
    dict1['sex'] = sheet.cell(n, 3).value
    dict1['birth'] = str(sheet.cell(n,4).value).split(' ')[0]
    if sheet.cell(n,5).value is not None:
        dict1['phone'] = str(sheet.cell(n,5).value)
        
    person[code] = dict1
filename = 'data/政法委.json'
with open(filename, 'w') as fl:
    json.dump(person, fl, ensure_ascii=False)
print('ok')

生成读卡系统文件

In [ ]:

import json

filename = 'data/政法委.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = []
for k, v in dict1.items():
    dict2 = {}
    #if dict1['sex'] =='男':
    #    sex = 1
    
    dict2 = {'id':k,'name':v['name'],'gender':v['sex'],'birth':v['birth'],'unit':'政法委'}
    list1.append(dict2)
json_data = json.dumps(list1,ensure_ascii=False, indent=4)  

# 将 json 数据写入文件
with open("data/data_政法委.json", "w",encoding = 'utf-8') as file:
    file.write(json_data)   
print('ok')

获取人员测试成绩

In [ ]:

import json
import time
import csv

filename = '../item.json'
item = {}
unit = {}
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for k,v in dict1.items():
    item[k] = v
re_ta = {}
dict1 = {}
list1 = []
filename = 'data/政法委.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/places_result_20231020.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        #line = re.sub('[\r\n\f ]{1,}', '', line)
        list1.append(line)
#print(list1)
for result in list1:
    user = str(result[2])
    if user in dict1.keys():        
        m_item = str(result[3])    
        re_ta.setdefault(user,{})    
        re_ta[user]['name'] = dict1[user]['name']
        re_ta[user]['sex'] = dict1[user]['sex'] 
        re_ta[user]['unit'] = dict1[user]['unit']
        re_ta[user]['phone'] = dict1[user]['phone']
        item_name = item[m_item]['name']
        re_ta[user].setdefault(item_name,{})  
        score = int(result[4])/item[m_item]['divisor']    
        re_ta[user][item_name]['成绩'] = f'{score} {item[m_item]["unit"]}'
print(len(re_ta))
filename = 'data/result_政法委.json'

with open(filename,'w') as fl:
    json.dump(re_ta, fl, ensure_ascii=False) 
print(len(re_ta))

In [ ]:

import json
import datetime
import csv
from datetime import date

filename = '../item.json'
item = {}
unit = {}
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for k,v in dict1.items():
    item[k] = v
item['1']['en'] = 'lung'
item['2']['en'] = 'grip'
item['3']['en'] = 'flexion'
item['4']['en'] = 'jump'
item['5']['en'] = 'pushup'
item['6']['en'] = 'balance'
item['7']['en'] = 'reaction'
item['8']['en'] = 'step'
item['9']['en'] = 'situp'
item['10']['en'] = 'height'
item['11']['en'] = 'weight'


re_ta = {}
dict1 = {}
list1 = []
filename = 'data/政法委.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/places_result_20231020.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        #line = re.sub('[\r\n\f ]{1,}', '', line)
        list1.append(line)
#f_item = ['lung','grip','flexion','jump','balance','reaction','step','situp']
#m_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step']
for result in list1:
    user = str(result[2])
    rq = date.fromisoformat(result[6].replace('/','-'))
    if user in dict1.keys():
        l_xm = []
        m_item = str(result[3])    
        re_ta.setdefault(user,{})    
        re_ta[user]['name'] = dict1[user]['name']
        re_ta[user]['sex'] = dict1[user]['sex']
        if dict1[user]['sex'] == '男':
            l_xm = ['weight','height','lung','grip','flexion','jump','pushup','balance','reaction','step']
        else:
            l_xm = ['weight','height','lung','grip','flexion','jump','balance','reaction','step','situp']
        re_ta[user]['unit'] = dict1[user]['unit']
        birth = date.fromisoformat(dict1[user]['birth'].replace('/','-'))
        #nian = int(birth[0].strip())
        #yue = int(birth[1].strip())
        #ri = int(birth[2].strip())
        #print(k,nian,yue,ri)
        item_name = item[m_item]['en']        
        if item_name in l_xm:            
            days = (rq-birth).days        
            re_ta[user]['age'] = int(days/365)
            re_ta[user]['month'] = int(days/365*12)
            re_ta[user]['rq'] = result[6]


            re_ta[user].setdefault(item_name,{})  
            score = int(result[4])/item[m_item]['divisor']    
            re_ta[user][item_name]['成绩'] = f'{score} {item[m_item]["unit"]}'
print(len(re_ta))
filename = 'data/result_政法委new.json'

with open(filename,'w') as fl:
    json.dump(re_ta, fl, ensure_ascii=False) 
print(len(re_ta))

计算项目成绩

In [ ]:

import json
import time

filename = '../item.json'
item = {}
unit = {}
with open(filename,'r') as fl:
    dict3 = json.load(fl) 
for k,v in dict3.items():
    item[k] = v
item['1']['en'] = 'lung'
item['2']['en'] = 'grip'
item['3']['en'] = 'flexion'
item['4']['en'] = 'jump'
item['5']['en'] = 'pushup'
item['6']['en'] = 'balance'
item['7']['en'] = 'reaction'
item['8']['en'] = 'step'
item['9']['en'] = 'situp'
item['10']['en'] = 'height'
item['11']['en'] = 'weight'

filename = 'data/体质检测标准 (1).json'    
with open(filename,'r') as fl:
    dict1 = json.load(fl)



def cal_score(data1):
    #data = {'name':'张三','sex':'M','age':37,'item':'StepExperiment','result':46}     
    person = dict1['person']
    criteria = dict1['criteria']
    if data1['age'] >59:
        data1['age'] = 59
    if data1['age'] <20:
        data1['age'] = 20
    info = data1['sex']+str(data1['age'])
    bz = person[info]
    mx = criteria[bz][data1['item']]
    result = data1['result']    
    if data1['item'] == 'reaction':
        for bz1 in mx:
            if result > bz1:
                #print(bz1)
                score = mx.index(bz1,0)
                break
            else:
                score = 5
    else:
        for bz1 in mx:
            if result < bz1:
                #print(bz1)
                score = mx.index(bz1,0)
                break
            else:
                score = 5
    return(score)
filename = 'data/体质检测标准_BMI.json'
with open(filename,'r') as fl:
    dict4 = json.load(fl) 
    
def cal_bmi(data1):
   # data = {'name':'张三','sex':'M','age':37,'item':'HeightWeight','result':'177.7,97.0'}
    person = dict4['person']
    criteria = dict4['criteria']
    if data1['age'] > 59:
        data1['age'] = 59
    if data1['age'] <20:
        data1['age'] = 20
    info = data1['sex']+str(data1['age'])
    bz = person[info]
    #print(bz)
    result = data1['result']
    #print(data1['code'],result)
    height = int(float(result.split(',')[0]))
    weight = float(result.split(',')[1])
    if str(height) not in criteria[bz]:
        score = 1
    else:   
        mx = criteria[bz][str(height)]
        if weight < mx[0]:
            score = 1
        elif weight < mx[1]:
            score = 3
        elif weight < mx[2]:
            score = 5    
        elif weight <= mx[3]:
            score = 3  
        elif weight > mx[3]:
            score = 1
    return score
    
    

#list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp','height','weight']
list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp']
filename = 'data/result_政法委new.json'
with open(filename,'r') as fl:
    dict2 = json.load(fl) 
for k, v in dict2.items():
    #print(k)
    if v['sex'] == '男':
        sex = 'M'
    else:
        sex = 'F'            
    if 'height' in v.keys() and 'weight' in v.keys():
        bmi_data = v['height']['成绩'].split()[0]+','+ v['weight']['成绩'].split()[0]
        data1 = {'code':k,'sex':sex,'age':v['age'],'item':'HeightWeight','result':bmi_data}
        dict2[k]['bmi'] = {}
        dict2[k]['bmi']['成绩'] = bmi_data
        dict2[k]['bmi']['score'] = cal_bmi(data1)
    for item_en in list_item:
        if item_en in v.keys():            
            data1 = {'code':k,'sex':sex,'age':v['age'],'item':item_en,'result':float(v[item_en]['成绩'].split()[0])}
            dict2[k][item_en]['score'] = cal_score(data1)
            #print(k,v[item_en]['成绩'],cal_score(data1))

filename = f'data/result_政法委new.json'
with open(filename,'w') as fl:
    json.dump(dict2,fl , ensure_ascii=False) 
print('ok!')

导出测试人员信息

In [ ]:

import json
import openpyxl

items = ['身高','体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
title = ['编号','姓名','性别','单位','身高','体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']

filename = 'data/result_政法委.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

filename = 'data/政法委.json'
with open(filename,'r') as fl:
    dict2 = json.load(fl)
    
list1 = []
for k, v in dict1.items():
    list2 = []
    list2.append(str(k).rjust(5,'0'))
    list2.append(v['name'])    
    list2.append(dict2[k]['sex'])
    list2.append(dict2[k]['unit'])
    for item in items:
        if item in v.keys():
            list2.append(v[item]['成绩']) 
        elif item =='name':
            list2.append(v[item])
        else:
            list2.append('') 
    list1.append(list2)
filename = 'data/政法委.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
sheet.append(title)
for row in list1:
    sheet.append(row)
    
wb.save(filename)

手工数据导入数据库

In [ ]:

import openpyxl
import json

filename = 'data/政法委.json'
with open(filename,'r') as fl:
    dict2 = json.load(fl)
wb = openpyxl.load_workbook('data/政法委手工数据.xlsx')
sheet = wb.active
# sheets = wb.sheetnames
dict1 = {}
dict3 = {1:10,2:11,3:7,4:2,5:6,6:3,7:1}
sheet = wb.active
data1 =list(sheet.values)
del data1[0]
list1 = []
for item in data1:
    code = int(item[0])
    list1.append((496535,code,item[2],item[1],'2023-10-20','2023-10-20 17:00:00'))
print(list1)

导入问卷信息

In [ ]:

import json
import csv
import openpyxl
import time

filename = 'data/result_政法委new.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/政法委.json'
with open(filename,'r') as fl:
    dict3 = json.load(fl)

phone =  set()
phone1 = set()
for k,v in dict3.items():
    if 'phone' in v.keys():
        phone.add(v['phone'])
list1 = []
filename = 'data/Survey_20231026.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        #line = re.sub('[\r\n\f ]{1,}', '', line)
        list1.append(line)
dict2 = {}
m_rq = '2023-10-20'
rq= date.fromisoformat(m_rq.replace('/','-'))
for item in list1:
    if item[2] in phone:        
        content = json.loads(item[5])
        list2 = []
        for i in range(0,60):
            list2.append(0)
        for k, v in content.items():
            i = int(k[1:])
            list2[i-1] = v
        dict2[item[2]] = list2
print(dict2)        
for k, v in dict3.items():
    if 'phone' in v.keys() and v['phone'] in dict2.keys():
        if k not in dict1.keys():
            dict1.setdefault(k,{})
            dict1[k] = dict3[k]
            days = (rq-birth).days        
            dict1[k]['age'] = int(days/365)
            dict1[k]['month'] = int(days/365*12)
        
        dict1[k]['tcm'] = dict2[str(v['phone'])]
      
filename = 'data/result_政法委new.json'

with open(filename,'w') as fl:
    json.dump(dict1, fl, ensure_ascii=False)

统计未参加问卷人员信息

In [ ]:

import json
import csv
import openpyxl

filename = 'data/政法委.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

phone =  set()
phone1 = set()
for k,v in dict1.items():
    if 'phone' in v.keys():
        phone.add(v['phone'])
list1 = []
filename = 'data/Survey_20231023-1698026051370.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        #line = re.sub('[\r\n\f ]{1,}', '', line)
        list1.append(line)
#print(list1)
for result in list1:
    phone1.add(result[2])

for k, v in dict1.items():
    if 'phone' in v.keys():
        item = str(v['phone'])
        if item not in phone1:
            print(k,v['name'])

In [ ]:

import json
import csv
import openpyxl

filename = 'data/政法委.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

phone =  set()
phone1 = set()
for k,v in dict1.items():
    if 'phone' in v.keys():
        phone.add(v['phone'])
list1 = []
filename = 'data/Survey_20231023-1698026051370.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        #line = re.sub('[\r\n\f ]{1,}', '', line)
        list1.append(line)
#print(list1)
for result in list1:
    phone1.add(result[2])

for item in phone1:
    if item in phone:
        print(item)

生成报告

In [ ]:

import requests
import json



headers = {
    "Content-Type": "application/json; charset=UTF-8"
    }
filename = 'data/result_政法委new.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = []
fiie_path ='./政法委1/'
list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp','bmi']
i = 27
list2 = []
for k,v in dict1.items():    
    list1 = []
    mydata = {}    
    id = k.rjust(3,"0")
    mydata['path'] = fiie_path+id+'-'+ v['name']+'.pdf'
    mydata['title'] = '延庆区委政法委'
    #mydata['subtitle'] = ''
    mydata['id'] = id
    mydata['name'] = v['name']
    if v['sex'] == '男':
        mydata['gender'] = 'male'
    else:
        mydata['gender'] = 'female'    
    mydata['month'] = v['month']
    
    if 'tcm' in v.keys():
        mydata.setdefault('surveys',{})
        mydata['surveys']['tcm'] = v['tcm']
        
    mydata['fits'] = {}
    #mydata.setdefault('fits',{})
    for item in list_item:
        if item in dict1[k].keys():
            if item in ['lung','pushup','step','situp']:
                mark = dict1[k][item]['成绩'].split()[0].split('.')[0]
            else:
                mark = dict1[k][item]['成绩'].split()[0]
            mydata['fits'][item] = {'mark':mark,'score':dict1[k][item]['score']}
    #print(mydata)        
           
    list1.append(mydata)
    #list2.append([k,dict1[k]['name']])

    x = requests.post('http://localhost:3003', data = json.dumps(list1), headers=headers)
    print(id,dict1[k]['name'],x.text)
    x.close()
    #print(mydata)

数据分析

清理报告数据

In [95]:

import json

filename = 'data/result_政法委new.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

    
#items = ['身高','体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
items = {}
items['lung'] = '肺活量'
items['grip'] ='握力'
items['flexion'] ='坐位体前屈'
items['jump'] ='纵跳'
items['pushup'] ='俯卧撑'
items['balance'] ='单脚站立'
items['reaction'] ='选择反应时'
items['step'] ='台阶指数'
items['situp'] ='一分钟仰卧起坐'
items['bmi'] ='BMI'


list1 = []

list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp','bmi']
i=1
list2 = []
dict2 = {}
for  k, v in dict1.items():
    list1 = []
    mydata = {}
    
    id = str(k).rjust(3,"0")
    mydata['unit'] = v['unit']
    mydata['name'] = v['name']
    mydata['sex'] = v['sex']
    mydata['month'] = v['month']
    age = int(v['month']/12)
    if age <20:
        mydata['age'] = 20
    else:
        mydata['age'] = int(v['month']/12)
    
    mydata['fits'] = {}
    score = 0
    for item in list_item:
        if item in v.keys():
            if item in ['lung','pushup','step','situp']:
                mark = v[item]['成绩'].split()[0].split('.')[0]
                n+=1
            else:
                mark = v[item]['成绩'].split()[0]
            mydata['fits'][items[item]] = {'mark':mark,'score':v[item]['score']}
            score = score + v[item]['score']
    
    #mydata['score'] = round(score/len(mydata['fits']),2)
    if len(mydata['fits']) >2:
        mydata['score'] = round(score/len(mydata['fits']),2)
        dict2[str(k)] = mydata
filename = f'data/data_政法委.json'
with open(filename,'w') as fl:
    json.dump(dict2,fl , ensure_ascii=False) 
print('ok!')

ok!

计算测试等级

In [96]:

import json

items = ['体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']

filename = 'data/data_政法委.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
dict2 = {}
dict2['不合格'] = [0,255]
dict2['合格'] = [256,332]
dict2['良好'] = [333,367]
dict2['优秀'] = [368,500]

for k1, v1 in dict2.items():
    di = v1[0]
    gao = v1[1]
    i = 0    
    m = 0
    f = 0
    for k,v in dict1.items():
        if int(v['score']*100) in range(di,gao+1):
            dict1[k]['level'] = k1
            i+=1
            if v['sex'] == '男':
                m = m +1
            else:
                f = f+1
    print(f'{di}~{gao}分人数：{i}人,男性：{m}人,女性:{f}人')
print(len(dict1))

0~255分人数：4人,男性：4人,女性:0人
256~332分人数：13人,男性：5人,女性:8人
333~367分人数：5人,男性：1人,女性:4人
368~500分人数：3人,男性：1人,女性:2人
25

根据年龄汇总人员信息及成绩

In [118]:

nld = [[20,24],[25,29],[30,34],[35,39],[40,44],[45,49],[50,54],[55,69]]

with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for item in nld:
    di = item[0]
    gao = item[1]
    i = 0
    score = 0
    m = 0
    f = 0
    for k,v in dict1.items():
        if v['age'] in range(di,gao+1):
            score = score+v['score']
            i+=1
            if v['sex'] == '男':
                m = m +1
    if i ==0:
        print(f'{di}~{gao}岁平均成绩：0分，人数：0人,男性：{m}人')
    else:
        print(f'{di}~{gao}岁平均成绩：{round(score/i,2)}分，人数：{i}人,男性：{m}人')

20~24岁平均成绩：0分，人数：0人,男性：0人
25~29岁平均成绩：2.22分，人数：1人,男性：1人
30~34岁平均成绩：3.0分，人数：5人,男性：3人
35~39岁平均成绩：2.99分，人数：6人,男性：2人
40~44岁平均成绩：2.96分，人数：6人,男性：2人
45~49岁平均成绩：3.5分，人数：4人,男性：1人
50~54岁平均成绩：3.18分，人数：3人,男性：2人
55~69岁平均成绩：0分，人数：0人,男性：0人

根据年龄汇总人员信息及成绩（男）

In [114]:

import json

nld = [[20,24],[25,29],[30,34],[35,39],[40,44],[45,49],[50,54],[55,80]]

with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for item in nld:
    di = item[0]
    gao = item[1]
    i = 0
    score = 0
    m = 0
    f = 0
    for k,v in dict1.items():
        if v['age'] in range(di,gao+1) and v['sex'] == '男':
            score = score+v['score']
            i+=1
    if i ==0:
        print(f'{di}~{gao}岁平均成绩：0分，人数：{i}人') 
    else:
        print(f'{di}~{gao}岁平均成绩：{round(score/i,2)}分，人数：{i}人')

20~24岁平均成绩：0分，人数：0人
25~29岁平均成绩：2.22分，人数：1人
30~34岁平均成绩：2.96分，人数：3人
35~39岁平均成绩：2.54分，人数：2人
40~44岁平均成绩：2.82分，人数：2人
45~49岁平均成绩：2.89分，人数：1人
50~54岁平均成绩：3.17分，人数：2人
55~80岁平均成绩：0分，人数：0人

根据年龄汇总人员信息及成绩（女）

In [116]:

nld = [[20,24],[25,29],[30,34],[35,39],[40,44],[45,49],[50,54],[55,80]]

with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for item in nld:
    di = item[0]
    gao = item[1]
    i = 0
    score = 0
    m = 0
    f = 0
    for k,v in dict1.items():
        if v['age'] in range(di,gao+1) and v['sex'] == '女':
            score = score+v['score']
            i+=1
    if i ==0:
        print(f'{di}~{gao}岁平均成绩：0分，人数：{i}人') 
    else:
        print(f'{di}~{gao}岁平均成绩：{round(score/i,2)}分，人数：{i}人')

20~24岁平均成绩：0分，人数：0人
25~29岁平均成绩：0分，人数：0人
30~34岁平均成绩：3.06分，人数：2人
35~39岁平均成绩：3.21分，人数：4人
40~44岁平均成绩：3.03分，人数：4人
45~49岁平均成绩：3.7分，人数：3人
50~54岁平均成绩：3.22分，人数：1人
55~80岁平均成绩：0分，人数：0人

计算平均成绩

In [100]:

nld = [[20,24],[25,29],[30,34],[35,39],[40,44],[45,49],[50,54],[55,80]]

with open(filename,'r') as fl:
    dict1 = json.load(fl) 
i = 1
m = 0
f = 0
score = 0
t_score = 0
for k,v in dict1.items():
    if v['sex'] == '男':
        m = m +1
        score = score+v['score']
print(f'平均成绩：{round(score/m,4)}分，男性：{m}人')
t_score = t_score + score
score = 0
for k,v in dict1.items():
    if v['sex'] == '女':
        f = f +1
        score = score+v['score']
print(f'平均成绩：{round(score/f,4)}分，女性：{f}人')
t_score = t_score + score
print(f'平均成绩：{round(t_score/25,4)}分，总体：25人')

平均成绩：2.8227分，男性：11人
平均成绩：3.2429分，女性：14人
平均成绩：3.058分，总体：25人

计算测试等级

In [101]:

with open(filename,'r') as fl:
    dict1 = json.load(fl) 
dict2 = {}
dict2['不合格'] = [0,255]
dict2['合格'] = [256,332]
dict2['良好'] = [333,367]
dict2['优秀'] = [368,500]

for k1, v1 in dict2.items():
    di = v1[0]
    gao = v1[1]
    i = 0    
    m = 0
    f = 0
    for k,v in dict1.items():
        if int(v['score']*100) in range(di,gao+1):
            dict1[k]['level'] = k1
            i+=1
            if v['sex'] == '男':
                m = m +1
            else:
                f = f+1
    print(f'{di}~{gao}分人数：{i}人,男性：{m}人,女性:{f}人')
#filename = 'data/result_石家庄.json'
with open(filename,'w') as fl:
    json.dump(dict1, fl) 
print('ok')

0~255分人数：4人,男性：4人,女性:0人
256~332分人数：13人,男性：5人,女性:8人
333~367分人数：5人,男性：1人,女性:4人
368~500分人数：3人,男性：1人,女性:2人
ok

计算各年龄段测试等级(女）

In [119]:

nld = [[20,24],[25,29],[30,34],[35,39],[40,44],[45,49],[50,54],[55,80]]

with open(filename,'r') as fl:
    dict1 = json.load(fl) 
dict2 = {}
dict2['不合格'] = [0,255]
dict2['合格'] = [256,332]
dict2['良好'] = [333,367]
dict2['优秀'] = [368,500]
dict3 = {}
for item in nld:
    di = item[0]
    gao = item[1]
    age = f'{di}-{gao}'
    dict3.setdefault(age,{})
    i = 0    
    m = 0
    f = 0
    for k,v in dict1.items():
        if v['age'] in range(di,gao+1):            
            dict3[age].setdefault(v['level'],0)
            if v['sex'] == '女':
                dict3[age][v['level']] = dict3[age][v['level']]+1
            
for k, v in dict3.items():
    print(k,v)

20-24 {}
25-29 {'不合格': 0}
30-34 {'良好': 1, '不合格': 0, '合格': 1}
35-39 {'合格': 2, '良好': 2, '不合格': 0}
40-44 {'合格': 3, '良好': 1}
45-49 {'优秀': 2, '合格': 1}
50-54 {'不合格': 0, '优秀': 0, '合格': 1}
55-80 {}

计算各年龄段测试等级(男）

In [103]:

nld = [[20,24],[25,29],[30,34],[35,39],[40,44],[45,49],[50,54],[55,80]]

with open(filename,'r') as fl:
    dict1 = json.load(fl) 
dict2 = {}
dict2['不合格'] = [0,255]
dict2['合格'] = [256,332]
dict2['良好'] = [333,367]
dict2['优秀'] = [368,500]
dict3 = {}
for item in nld:
    di = item[0]
    gao = item[1]
    age = f'{di}-{gao}'
    dict3.setdefault(age,{})
    i = 0    
    m = 0
    f = 0
    for k,v in dict1.items():
        if v['age'] in range(di,gao+1):            
            dict3[age].setdefault(v['level'],0)
            if v['sex'] == '男':
                dict3[age][v['level']] = dict3[age][v['level']]+1
            
for k, v in dict3.items():
    print(k,v)

20-24 {}
25-29 {'不合格': 1}
30-34 {'良好': 1, '不合格': 1, '合格': 1}
35-39 {'合格': 1, '良好': 0, '不合格': 1}
40-44 {'合格': 2, '良好': 0}
45-49 {'优秀': 0, '合格': 1}
50-54 {'不合格': 1, '优秀': 1, '合格': 0}
55-80 {}

计算各项目成绩

In [117]:

with open(filename,'r') as fl:
    dict1 = json.load(fl) 
items = ['BMI','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
for item in items:
    score = 0
    n = 0
    for k, v in dict1.items():        
        if item in v['fits'].keys():
            n = n + 1
            score =score + int(v['fits'][item]['score'])
    print(item,round(score/n,2),n)

BMI 3.8 25
肺活量 3.68 25
握力 2.56 25
坐位体前屈 2.88 25
纵跳 2.44 25
俯卧撑 3.91 11
一分钟仰卧起坐 3.86 14
单脚站立 2.32 25
选择反应时 3.72 25
台阶指数 2.18 22

In [ ]:

43 KiB Raw Permalink Blame History

体测人员导入

生成读卡系统文件

获取人员测试成绩

计算项目成绩

导出测试人员信息

手工数据导入数据库

导入问卷信息

统计未参加问卷人员信息

生成报告

数据分析

清理报告数据

计算测试等级

根据年龄汇总人员信息及成绩

根据年龄汇总人员信息及成绩（男）

根据年龄汇总人员信息及成绩（女）

计算平均成绩

计算测试等级

计算各年龄段测试等级(女）

计算各年龄段测试等级(男）

计算各项目成绩

43 KiB

Raw Permalink Blame History