512song/jupyter

0 Fork 0

Files

T

512song 906fe2f1bc 20240411

2025-04-11 09:52:34 +08:00

95 KiB

Raw Permalink Blame History

体测数据处理

体测人员导入

In [1]:

import openpyxl
import json
from datetime import date

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, 3).value
    dict1['sex'] = sheet.cell(n, 4).value
    dict1['unit'] = sheet.cell(n, 2).value
    dict1['birth'] = str(sheet.cell(n, 5).value).replace('/','-').split(' ')[0] 
    dict1['phone'] = str(sheet.cell(n,6).value)
    person[code] = dict1
filename = 'data/青海.json'
with open(filename, 'w') as fl:
    json.dump(person, fl, ensure_ascii=False)
print('ok')

ok

In [ ]:

import openpyxl
import json
from datetime import date

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, 4).value
    dict1['sex'] = sheet.cell(n, 5).value
    dict1['unit'] = sheet.cell(n, 2).value
    dict1['birth'] = str(sheet.cell(n, 8).value).replace('/','-').split(' ')[0] 
    dict1['phone'] = str(sheet.cell(n,10).value)
    person[code] = dict1
filename = 'data/青海2024.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':v['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/青海2024.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/places_result_20240311.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']
        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_青海20240515.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/青海2024.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/places_result_20240311.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('/','-'))
        birth = date.fromisoformat(dict1[user]['birth'])
        #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_青海all.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_青海all.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_青海all.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_青海20240515.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

filename = 'data/青海2024.json'
with open(filename,'r') as fl:
    dict2 = json.load(fl)
    
list1 = []
for k, v in dict1.items():
    if k in dict2.keys():
        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/青海20240515.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
sheet.append(title)
for row in list1:
    sheet.append(row)
    
wb.save(filename)

导入问卷信息

In [ ]:

import json
import csv
import openpyxl
import time
from datetime import date

filename = 'data/result_青海all.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/青海2024.json'
with open(filename,'r') as fl:
    dict3 = json.load(fl)

phone =  {}
for k,v in dict3.items():
    if 'phone' in v.keys():
        phone[v['phone']] = k
list1 = []
filename = 'data/Survey_20240612.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        list1.append(line)
#print(list1)
dict2 = {}
psy =[]
tcm = []
spine = []
for i in range(0,45):
    psy.append(0)
psy[44] = []
for i in range(0,60):
    tcm.append(0)
for i in range(0,26):
    spine.append(0)

for item in list1:
    if item[2] in phone.keys():
        psy =[]
        tcm = []
        spine = []
        for i in range(0,45):
            psy.append(0)
        psy[44] = []
        for i in range(0,60):
            tcm.append(0)
        for i in range(0,26):
            spine.append(0)
       
        content = json.loads(item[5])
        if phone[item[2]] not in dict1.keys():
            dict1[phone[item[2]]] = dict3[phone[item[2]]]
            #print(item[2])
        rq = date.fromisoformat(item[6].replace('/','-').split(' ')[0])
            #dict1[phone[item[2]]]['rq'] = date.fromisoformat(item[6].replace('/','-').split(' ')[0])
        for k, v in content.items():
            if 'psy' in k:
                i = int(k[3:])
                psy[i-1] = int(v)
            if 'tcm' in k:
                i = int(k[3:])
                tcm[i-1] = int(v)
            if 'spine' in k:
                i = int(k[5:])
                spine[i-1] = int(v)
        if 'psy' in item[5]:
            #for i in range(5,26):
            #    new_valve = 5-psy[i]
            #    psy[i] = new_valve
            for i in range(26,40):
                new_valve = 1+psy[i]
                psy[i] = new_valve
            
            dict1[phone[item[2]]]['psy'] = psy
        if 'tcm' in item[5]:
            dict1[phone[item[2]]]['tcm'] = tcm
        if 'spine' in item[5]:
            dict1[phone[item[2]]]['spine'] = spine
        birth = date.fromisoformat(dict3[phone[item[2]]]['birth'].replace('/','-'))
        #rq =date.fromisoformat('2023-12-08')
        days =  (rq-birth).days          
        dict1[phone[item[2]]]['age'] = int(days/365)
        dict1[phone[item[2]]]['month'] = int(days/365*12)
    else:
        print(item[2])
filename = 'data/result_青海all_2.json'

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

生成报告

In [ ]:

import requests
import json
import openpyxl


headers = {
    "Content-Type": "application/json; charset=UTF-8"
    }
filename = 'data/result_青海all_2.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = []
file_path ='./青海1/'
list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp','bmi']
i=0
list2 = []
for  k, v in dict1.items():
    list1 = []
    mydata = {}
    
    id = str(k).rjust(4,"0")
    mydata['path'] = file_path+id+'-'+ v['name']+'.pdf'
    mydata['title'] = '青海油田勘探事业部'    
    mydata['subtitle'] = v['unit']
    mydata['id'] = id
    mydata['name'] = v['name']
    if v['sex'] == '男':
        mydata['gender'] = 'male'
    else:
        mydata['gender'] = 'female'
    
    mydata['month'] = v['month']
    mydata['fits'] = {}
    survey_list = ['tcm','psy','spine']
    for item in survey_list:
        if item in  v.keys():
            mydata.setdefault('surveys',{})
            mydata['surveys'][item] = v[item]
            
    
    #mydata['fits'] = {}
    for item in list_item:
        if item in v.keys():
            mydata.setdefault('fits',{})
            if item in ['lung','pushup','step','situp']:
                mark = v[item]['成绩'].split()[0].split('.')[0]
            else:
                mark = v[item]['成绩'].split()[0]
            mydata['fits'][item] = {'mark':mark,'score':v[item]['score']}
    #if len(mydata['fits']) >2 or len(mydata['surveys']) >0:
    #if len(mydata['fits']) >2 :    
    list1.append(mydata)
    list2.append([k,v['name']])
    i+=1
    x = requests.post('http://localhost:3003', data = json.dumps(list1), headers=headers)
    #print(id,v['name'],x.text)
    #print(mydata)
    #x.close()
print(i)

统计人员情况

In [ ]:

import json
import csv
import openpyxl
import time
from datetime import date
wj = []
tc = []
all = []
filename = 'data/result_青海20240515.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

for k, v in dict1.items():
    tc.append(k)
print(len(tc))
    
filename = 'data/result_青海all_2.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
for k, v in dict1.items():
    if 'psy' in v.keys():
        wj.append(k)
print(len(wj))
for item in wj:
    if item in tc:
        all.append(item)
print(len(all))

统计问卷人员情况表

In [ ]:

import json
import csv
import openpyxl
import time
from datetime import date


filename = 'data/青海2024.json'
with open(filename,'r') as fl:
    dict3 = json.load(fl)

phone =  {}
for k,v in dict3.items():
    if 'phone' in v.keys():
        phone[v['phone']] = k
list1 = []
filename = 'data/Survey_20240603.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        if line[2] in phone.keys() and  line[2] not in list1:
            list1.append(line[2])
print(list1) 
for item in list1:
    print(phone[item])

统计报告发送情况表

In [ ]:

import json
import openpyxl

filename = 'data/result_青海all_1.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = []
for k,v in dict1.items():
    list2 = []
    list2 = [k,v['name'],v['sex'],v['unit'],v['phone']]
    list1.append(list2)

filename = 'data/青海体测报告发送明细.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active

for row in list1:
    sheet.append(row)
    
wb.save(filename)

筛选家属人员

In [ ]:

import json

dict3 = {}
filename = 'data/青海.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
filename = 'data/青海2024.json'
with open(filename,'r') as fl:
    dict2 = json.load(fl)

for k, v in dict1.items():
    if k not in dict2.keys():
        dict3[k] = v
filename = 'data/青海_家属.json'
with open(filename, 'w') as fl:
    json.dump(dict3, fl, ensure_ascii=False)
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_20240311.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']
        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_20240311.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('/','-'))
        birth = date.fromisoformat(dict1[user]['birth'])
        #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_青海家属.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_青海家属.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_青海家属.json'
with open(filename,'w') as fl:
    json.dump(dict2,fl , ensure_ascii=False) 
print('ok!')

In [ ]:

import json
import csv
import openpyxl
import time
from datetime import date

filename = 'data/result_青海家属.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 =  {}
for k,v in dict3.items():
    if 'phone' in v.keys():
        phone[v['phone']] = k
list1 = []
filename = 'data/Survey_20240612.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        list1.append(line)
#print(list1)
dict2 = {}
psy =[]
tcm = []
spine = []
for i in range(0,45):
    psy.append(0)
psy[44] = []
for i in range(0,60):
    tcm.append(0)
for i in range(0,26):
    spine.append(0)

for item in list1:
    if item[2] in phone.keys():
        psy =[]
        tcm = []
        spine = []
        for i in range(0,45):
            psy.append(0)
        psy[44] = []
        for i in range(0,60):
            tcm.append(0)
        for i in range(0,26):
            spine.append(0)
       
        content = json.loads(item[5])
        if phone[item[2]] not in dict1.keys():
            dict1[phone[item[2]]] = dict3[phone[item[2]]]
            #print(item[2])
        rq = date.fromisoformat(item[6].replace('/','-').split(' ')[0])
            #dict1[phone[item[2]]]['rq'] = date.fromisoformat(item[6].replace('/','-').split(' ')[0])
        for k, v in content.items():
            if 'psy' in k:
                i = int(k[3:])
                psy[i-1] = int(v)
            if 'tcm' in k:
                i = int(k[3:])
                tcm[i-1] = int(v)
            if 'spine' in k:
                i = int(k[5:])
                spine[i-1] = int(v)
        if 'psy' in item[5]:
            #for i in range(5,26):
            #    new_valve = 5-psy[i]
            #    psy[i] = new_valve
            for i in range(26,40):
                new_valve = 1+psy[i]
                psy[i] = new_valve
            
            dict1[phone[item[2]]]['psy'] = psy
        if 'tcm' in item[5]:
            dict1[phone[item[2]]]['tcm'] = tcm
        if 'spine' in item[5]:
            dict1[phone[item[2]]]['spine'] = spine
        birth = date.fromisoformat(dict3[phone[item[2]]]['birth'].replace('/','-'))
        #rq =date.fromisoformat('2023-12-08')
        days =  (rq-birth).days          
        dict1[phone[item[2]]]['age'] = int(days/365)
        dict1[phone[item[2]]]['month'] = int(days/365*12)
    else:
        print(item[2])
filename = 'data/result_青海家属.json'

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

In [ ]:

import requests
import json
import openpyxl


headers = {
    "Content-Type": "application/json; charset=UTF-8"
    }
filename = 'data/result_青海家属.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = []
file_path ='./青海家属/'
list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp','bmi']
i=0
list2 = []
for  k, v in dict1.items():
    list1 = []
    mydata = {}
    
    id = str(k).rjust(4,"0")
    mydata['path'] = file_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']
    mydata['fits'] = {}
    survey_list = ['tcm','psy','spine']
    for item in survey_list:
        if item in  v.keys():
            mydata.setdefault('surveys',{})
            mydata['surveys'][item] = v[item]
            
    
    #mydata['fits'] = {}
    for item in list_item:
        if item in v.keys():
            mydata.setdefault('fits',{})
            if item in ['lung','pushup','step','situp']:
                mark = v[item]['成绩'].split()[0].split('.')[0]
            else:
                mark = v[item]['成绩'].split()[0]
            mydata['fits'][item] = {'mark':mark,'score':v[item]['score']}
    #if len(mydata['fits']) >2 or len(mydata['surveys']) >0:
    #if len(mydata['fits']) >2 :    
    list1.append(mydata)
    list2.append([k,v['name']])
    i+=1
    x = requests.post('http://localhost:3003', data = json.dumps(list1), headers=headers)
    #print(id,v['name'],x.text)
    #print(mydata)
    #x.close()
print(i)

体测数据处理（2025年）

体测人员导入

In [23]:

import openpyxl
import json


wb = openpyxl.load_workbook('data/青海2025.xlsx')
sheet = wb.active
# sheets = wb.sheetnames
person = {}

for n in range(2, sheet.max_row+1):
    if sheet.cell(n, 1).value is not None:
    #print(sheet.cell(n, 1).value)
        code = int(sheet.cell(n, 1).value)
        person.setdefault(code, {})
        dict1 = {}
        dict1['name'] = sheet.cell(n, 3).value
        dict1['sex'] = sheet.cell(n, 4).value
        dict1['unit'] = sheet.cell(n, 2).value
        dict1['phone'] = int(sheet.cell(n, 6).value)
        dict1['birth'] = str(sheet.cell(n, 5).value).replace('/','-').split(' ')[0]
        person[code] = dict1

filename = 'data/青海人员2025.json'
with open(filename, 'w') as fl:
    json.dump(person, fl, ensure_ascii=False)
print(len(person),'ok')

81 ok

获取人员测试成绩

In [16]:

import json
import datetime
import csv
from datetime import date


re_ta = {}
list1 = []
filename = 'data/青海人员2025.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/marks_20250118.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)

for result in list1:
    user = str(result[2])
    rq = date.fromisoformat(result[5].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 'phone' in dict1[user].keys():
            re_ta[user]['phone'] = dict1[user]['phone']
        if dict1[user]['sex'] == '男':
            l_xm = ['bmi','lung','grip','flexion','jump','pushup','balance','reaction','step']
        else:
            l_xm = ['bmi','lung','grip','flexion','jump','balance','reaction','step','situp']
        re_ta[user]['unit'] = dict1[user]['unit']
        birth = date.fromisoformat(dict1[user]['birth'].replace('/','-'))
        item_name = result[3]     
        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[5]
            re_ta[user].setdefault(item_name,{})  
            score = result[4] 
            re_ta[user][item_name]['成绩'] = score

filename = 'data/result_青海2025.json'
with open(filename,'w') as fl:
    json.dump(re_ta, fl, ensure_ascii=False) 
print(len(re_ta))

生成测试得分

In [1]:

import json
import time
import my_module as My

#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_青海2025.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 'bmi' in v.keys():
        #bmi_data = v['height']['成绩'].split()[0]+','+ v['weight']['成绩'].split()[0]
        bmi_data = v['bmi']['成绩']
        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'] = My.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])}
            #print(k,v['name'])
            dict2[k][item_en]['score'] = My.cal_score(data1)
            #print(k,v[item_en]['成绩'],cal_score(data1))

filename = f'data/result_青海2025.json'
with open(filename,'w') as fl:
    json.dump(dict2,fl , ensure_ascii=False) 
print('ok!')

ok!

导出测试人员信息

In [2]:

import json
import openpyxl

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

filename = 'data/result_青海2025.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

filename = 'data/青海人员2025.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'])
    if 'bmi' in v.keys():
        height = v['bmi']['成绩'].split(',')[0]
        weight = v['bmi']['成绩'].split(',')[1]
        list2.append(height)
        list2.append(weight)
    else:
        list2.append('')
        list2.append('')
    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/青海体测情况表2025.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
sheet.append(title)
for row in list1:
    sheet.append(row)
    
wb.save(filename)

导入问卷信息

In [16]:

import json
import csv
import openpyxl
import time
from datetime import date
#import my_module as My

dict1 = {}
filename = 'data/result_青海2025.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
filename = 'data/青海人员2025.json'
with open(filename,'r') as fl:
    dict3 = json.load(fl)

phone =  {}
for k,v in dict3.items():
    if 'phone' in v.keys():
        phone[str(v['phone'])] = k

list1 = []
filename = 'data/Survey_20250118.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        list1.append(line)
#print(list1)
dict2 = {}
psy =[]
tcm = []
spine = []
for i in range(0,45):
    psy.append(0)
psy[44] = []
for i in range(0,60):
    tcm.append(0)
for i in range(0,26):
    spine.append(0)

for item in list1:
    if item[2] in phone.keys():
        psy =[]
        tcm = []
        spine = []
        for i in range(0,45):
            psy.append(0)
        psy[44] = []
        for i in range(0,60):
            tcm.append(0)
        for i in range(0,26):
            spine.append(0)
       
        content = json.loads(item[5])
        if phone[item[2]] not in dict1.keys():
            dict1[phone[item[2]]] = dict3[phone[item[2]]]
            rq = date.fromisoformat(item[6].replace('/','-').split(' ')[0])
            #dict1[phone[item[2]]]['rq'] = date.fromisoformat(item[6].replace('/','-').split(' ')[0])
        else:
            rq = date.fromisoformat('2025-01-18')
        for k, v in content.items():
            if 'psy' in k:
                i = int(k[3:])
                psy[i-1] = int(v)
            if 'tcm' in k:
                i = int(k[3:])
                tcm[i-1] = int(v)
            if 'spine' in k:
                i = int(k[5:])
                spine[i-1] = int(v)
        if 'psy' in item[5]:
            #for i in range(5,26):
            #    new_valve = 5-psy[i]
            #    psy[i] = new_valve
            
            
            dict1[phone[item[2]]]['psy'] = psy
        if 'tcm' in item[5]:
            dict1[phone[item[2]]]['tcm'] = tcm
        if 'spine' in item[5]:
            dict1[phone[item[2]]]['spine'] = spine
        birth = date.fromisoformat(dict3[phone[item[2]]]['birth'].replace('/','-'))
        
        days =  (rq-birth).days          
        dict1[phone[item[2]]]['age'] = int(days/365)
        dict1[phone[item[2]]]['month'] = int(days/365*12)
        

filename = 'data/result_青海2025-1.json'
with open(filename,'w') as fl:
    json.dump(dict1, fl, ensure_ascii=False)
print(data1[1])

统计参加问卷人员

In [25]:

import json
import csv
import openpyxl
import time
from datetime import date


filename = 'data/青海人员2025.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

phone1 =  set()
phone2 = set()
for k,v in dict1.items():
    phone1.add(v['phone'])
list1 = []
filename = 'data/Survey_20250118.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        list1.append(line)
print(len(phone1))
i =1
list2 = []
for item in list1:
    code = int(item[2])
    for k, v in dict1.items():
        list3 = []
        if v['phone'] == code:            
            list3.append(k)
            list3.append(v['name'])
            list3.append(v['sex'])
            list3.append(v['unit'])
            list3.append(code)
            list2.append(list3)
filename = 'data/青海问卷情况表2025.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in list2:
    sheet.append(row)
    
wb.save(filename)

统计问卷人员手机号码未登记

In [24]:

import json
import csv
import openpyxl
import time
from datetime import date


filename = 'data/青海人员2025.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

phone1 =  set()
phone2 = set()
for k,v in dict1.items():
    phone1.add(v['phone'])
list1 = []
filename = 'data/Survey_20250118.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        list1.append(line)
print(len(phone1))
i =1
list2 = []
for item in list1:
    code = int(item[2])
    if code not in phone1:
        content = json.loads(item[5])
        if 'name' in content.keys():
            print(code,content['name'])

统计未参加问卷人员

In [6]:

import json
import csv
import openpyxl
import time
from datetime import date


filename = 'data/青海人员2025.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

phone1 =  set()
phone2 = set()
for k,v in dict1.items():
    phone1.add(v['phone'])
list1 = []
filename = 'data/Survey_20250118.csv'
with open(filename,'r',newline='') as csv_file:
    fl = csv.reader(csv_file,delimiter=',')
    header = next(fl)    
    for line in fl:
        phone2.add(int(line[2]))
print(len(phone2))
i =1

for k, v in dict1.items():
    if v['phone'] not in phone2:
        list2 = []
        list2 = [k,v['name'],v['sex'],v['phone']]
        list1.append(list2)
filename = 'data/青海未参加问卷人员2025.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in list1:
    sheet.append(row)
    
wb.save(filename)

生成报告

In [1]:

import requests
import json
import openpyxl


headers = {
    "Content-Type": "application/json; charset=UTF-8"
    }
filename = 'data/result_青海2025-1.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = []
file_path ='./青海2025-1/'
list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp','bmi']
i=0
list2 = []
for  k, v in dict1.items():
    list1 = []
    mydata = {}
    
    id = str(k).rjust(4,"0")
    mydata['path'] = file_path+id+'-'+ v['name']+'.pdf'
    mydata['title'] = '青海油田勘探事业部'
    mydata['subtitle'] = v['unit']
    mydata['id'] = id
    mydata['name'] = v['name']
    if v['sex'] == '男':
        mydata['gender'] = 'male'
    else:
        mydata['gender'] = 'female'
    
    mydata['month'] = v['month']
    mydata['fits'] = {}
    survey_list = ['tcm','psy','spine']
    for item in survey_list:
        if item in  v.keys():
            mydata.setdefault('surveys',{})
            mydata['surveys'][item] = v[item]
            
    
    #mydata['fits'] = {}
    for item in list_item:
        if item in v.keys():
            mydata.setdefault('fits',{})
            if item in ['lung','pushup','step','situp']:
                mark = v[item]['成绩'].split()[0].split('.')[0]
            else:
                mark = v[item]['成绩'].split()[0]
            mydata['fits'][item] = {'mark':mark,'score':v[item]['score']}
    if len(mydata['fits']) >2 or len(mydata['surveys']) >0:
    #if len(mydata['fits']) >2 :    
        list1.append(mydata)
        list2.append([k,v['name']])
        i+=1
        x = requests.post('http://localhost:3003', data = json.dumps(list1), headers=headers)
        #print(id,v['name'],x.text)
        #print(mydata)
        #x.close()
print(i)

体测数据分析

常用参数及自定义函数

In [1]:

import json

questions = [
  [1],
  [-1, 2],
  [-1, 2],
  [-1, 8],
  [-1, 3],
  [1],
  [-1],
  [-1, 7],
  [2],
  [2],
  [2],
  [2, 3],
  [2],
  [2],
  [3],
  [3],
  [3],
  [3],
  [3],
  [4],
  [4],
  [4],
  [4],
  [4],
  [4],
  [4],
  [4],
  [5],
  [5],
  [5],
  [5],
  [5],
  [5],
  [5],
  [5],
  [6],
  [6],
  [6],
  [6],
  [6],
  [6],
  [7],
  [7],
  [7],
  [7],
  [7],
  [7],
  [8],
  [8],
  [8],
  [8],
  [8],
  [8],
  [9],
  [9],
  [9],
  [9],
  [9],
  [9],
  [9]
]

kinds = [
  '平和',
  '气虚',
  '阳虚',
  '阴虚',
  '痰湿',
  '湿热',
  '血瘀',
  '气郁',
  '特禀'
]

def tcm_calc(arr):
  qa = [8, 8, 7, 8, 8, 6, 7, 7, 7]
  # 成绩数组
  s = [0] * 9
  # 遍历五进制
  for i in range(len(questions)):
    m = arr[i] - 1
    for v in questions[i]:
      if v < 0:
        s[-v - 1] += 4 - m
      else:
        s[v - 1] += m
  return [int((v / qa[i]) * 25) for i, v in enumerate(s)]

def tcm_kind(score):
  kind = 0
  near = False
  max_kind = 0
  max_score = 0
  for i in range(1, 9):
    if score[i] > max_score:
      max_kind = i
      max_score = score[i]
  if score[0] >= 60 and max_score < 40:
    if max_score >= 30:
      near = True
      kind = max_kind
  else:
    kind = max_kind
  return {
    "kind": kind,
    "near": near
  }
list2 = ['成就感','愉快心境','放松程度','压力应对','体力充沛','情感充沛度']
list3 = [[5,7],[7,4],[7,4],[7,4],[8,1],[6,1]] 
list4 = ['颈椎','胸椎','腰椎','骶尾椎']
list5 = [[0,10,10],[10,17,7],[17,24,6],[24,26,2]]

计算中医体质

In [2]:


filename = 'data/result_青海2025-1.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

for k, v in dict1.items():
    if 'tcm' in v.keys():
        list1 = []
        tcm =v['tcm']
        for item in tcm:
            list1.append(item)
        score = tcm_calc(list1)

        result = tcm_kind(score)
        kind = result['kind']
        near = result['near']
        print(k,kinds[kind], near, score)

5 特禀 False [65, 37, 32, 56, 53, 50, 35, 35, 57]
99 平和 False [84, 3, 14, 12, 0, 8, 3, 0, 3]
101 湿热 False [50, 40, 17, 37, 53, 54, 35, 28, 21]
100 阴虚 False [71, 21, 14, 40, 12, 20, 32, 21, 14]
102 平和 False [81, 18, 3, 9, 28, 20, 14, 14, 3]
105 湿热 False [37, 81, 21, 50, 50, 83, 57, 50, 42]
104 阳虚 False [59, 34, 42, 40, 28, 37, 32, 35, 32]
103 阳虚 False [56, 43, 53, 53, 53, 45, 42, 32, 53]
18 血瘀 False [25, 81, 71, 68, 81, 66, 89, 71, 57]
28 气虚 True [65, 37, 21, 34, 28, 12, 7, 14, 17]
33 湿热 False [87, 31, 10, 25, 34, 41, 10, 10, 35]
107 痰湿 False [65, 34, 10, 46, 53, 45, 32, 17, 10]
39 气虚 False [78, 50, 10, 25, 40, 16, 10, 3, 21]
15 痰湿 False [50, 43, 50, 50, 56, 54, 50, 53, 53]
108 湿热 False [62, 40, 42, 31, 53, 58, 28, 28, 32]
36 气郁 False [62, 18, 35, 34, 28, 45, 35, 50, 17]
43 湿热 True [62, 34, 21, 15, 25, 37, 35, 32, 21]
24 平和 False [71, 25, 3, 0, 3, 0, 17, 14, 3]
61 阳虚 False [56, 37, 46, 31, 46, 41, 42, 32, 39]
40 阴虚 False [65, 37, 42, 43, 34, 29, 25, 28, 25]
113 阴虚 False [71, 40, 28, 46, 31, 33, 17, 0, 14]
114 阴虚 False [56, 28, 7, 43, 28, 33, 25, 21, 25]
55 平和 False [90, 9, 3, 15, 18, 20, 7, 3, 17]
57 痰湿 False [56, 37, 10, 46, 50, 37, 28, 35, 21]
69 阴虚 False [56, 37, 17, 53, 34, 41, 21, 21, 14]
50 阳虚 False [56, 46, 50, 43, 46, 45, 35, 32, 25]
118 痰湿 False [34, 62, 53, 40, 65, 62, 57, 57, 21]
20 气虚 False [56, 50, 50, 9, 34, 25, 17, 35, 3]
35 阴虚 True [87, 25, 3, 34, 28, 12, 14, 3, 3]
44 平和 False [71, 21, 28, 25, 18, 29, 21, 28, 3]
41 痰湿 False [40, 50, 42, 50, 71, 37, 53, 57, 32]
131 痰湿 False [56, 43, 21, 34, 50, 45, 32, 32, 32]
9 平和 False [84, 9, 17, 18, 15, 20, 17, 7, 3]
132 湿热 True [100, 6, 0, 31, 21, 33, 25, 7, 21]
134 阳虚 False [53, 37, 64, 25, 34, 37, 39, 50, 42]
133 阴虚 False [75, 31, 10, 59, 21, 8, 7, 14, 14]
3 痰湿 False [78, 31, 46, 34, 59, 41, 21, 14, 35]
71 阳虚 True [78, 21, 32, 21, 25, 20, 14, 7, 7]
6 阳虚 False [68, 28, 46, 43, 28, 25, 25, 7, 10]
135 湿热 False [53, 43, 39, 37, 56, 58, 57, 25, 39]
48 气虚 False [59, 43, 14, 43, 37, 41, 14, 39, 35]
53 痰湿 False [46, 68, 53, 59, 84, 79, 64, 57, 78]
138 湿热 False [43, 25, 42, 25, 28, 50, 25, 39, 10]
139 阳虚 False [53, 34, 46, 37, 40, 41, 42, 42, 25]

计算心理

In [3]:

list2 = ['成就感','愉快心境','放松程度','压力应对','体力充沛','情感充沛度']
list3 = [[5,7],[7,4],[7,4],[7,4],[8,1],[6,1]]
qb = [
      1, 1, 1, 1, 1,
      4, 3, 2, 3, 2, 4, 3,
      4, 3, 2, 4, 4, 2, 4,
      3, 2, 2, 4, 3, 3, 2,
      5, 5, 5, 5, 5, 5, 5, 5,
      6, 6, 6, 6, 6, 6
    ]
filename = 'data/result_青海2025-1.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for k, v in dict1.items():
    if 'psy' in v.keys():
        psy=v['psy']
        for i in range(26,40):
            new_valve = psy[i]-1
            psy[i] = new_valve
        dict3 = {}
        
        i = 0
        for item in qb:
            dict3.setdefault(item,0)
            dict3[item] +=psy[i]
            i+=1
        print(dict3)
        print(k)
        for i in range(0,6):
            score = int(dict3[i+1]/list3[i][0]/list3[i][1]*100)
            print(list2[i],score)

{1: 30, 4: 14, 3: 12, 2: 8, 5: 1, 6: 1}
5
成就感 85
愉快心境 28
放松程度 42
压力应对 50
体力充沛 12
情感充沛度 16
{1: 20, 4: 8, 3: 7, 2: 7, 5: 6, 6: 6}
99
成就感 57
愉快心境 25
放松程度 25
压力应对 28
体力充沛 75
情感充沛度 100
{1: 20, 4: 17, 3: 10, 2: 8, 5: 2, 6: 0}
101
成就感 57
愉快心境 28
放松程度 35
压力应对 60
体力充沛 25
情感充沛度 0
{1: 35, 4: 9, 3: 10, 2: 9, 5: 2, 6: 1}
100
成就感 100
愉快心境 32
放松程度 35
压力应对 32
体力充沛 25
情感充沛度 16
{1: 17, 4: 9, 3: 8, 2: 10, 5: 3, 6: 5}
102
成就感 48
愉快心境 35
放松程度 28
压力应对 32
体力充沛 37
情感充沛度 83
{1: 12, 4: 25, 3: 28, 2: 28, 5: 0, 6: 2}
105
成就感 34
愉快心境 100
放松程度 100
压力应对 89
体力充沛 0
情感充沛度 33
{1: 20, 4: 13, 3: 12, 2: 11, 5: 3, 6: 1}
104
成就感 57
愉快心境 39
放松程度 42
压力应对 46
体力充沛 37
情感充沛度 16
{1: 35, 4: 14, 3: 11, 2: 8, 5: 2, 6: 3}
103
成就感 100
愉快心境 28
放松程度 39
压力应对 50
体力充沛 25
情感充沛度 50
{1: 32, 4: 17, 3: 15, 2: 12, 5: 3, 6: 2}
18
成就感 91
愉快心境 42
放松程度 53
压力应对 60
体力充沛 37
情感充沛度 33
{1: 27, 4: 8, 3: 9, 2: 10, 5: 5, 6: 4}
28
成就感 77
愉快心境 35
放松程度 32
压力应对 28
体力充沛 62
情感充沛度 66
{1: 35, 4: 11, 3: 11, 2: 7, 5: 6, 6: 6}
33
成就感 100
愉快心境 25
放松程度 39
压力应对 39
体力充沛 75
情感充沛度 100
{1: 32, 4: 14, 3: 11, 2: 12, 5: 4, 6: 5}
107
成就感 91
愉快心境 42
放松程度 39
压力应对 50
体力充沛 50
情感充沛度 83
{1: 31, 4: 14, 3: 9, 2: 9, 5: 3, 6: 5}
39
成就感 88
愉快心境 32
放松程度 32
压力应对 50
体力充沛 37
情感充沛度 83
{1: 25, 4: 14, 3: 13, 2: 13, 5: 3, 6: 3}
15
成就感 71
愉快心境 46
放松程度 46
压力应对 50
体力充沛 37
情感充沛度 50
{1: 26, 4: 19, 3: 16, 2: 14, 5: 0, 6: 5}
108
成就感 74
愉快心境 50
放松程度 57
压力应对 67
体力充沛 0
情感充沛度 83
{1: 33, 4: 12, 3: 9, 2: 9, 5: 4, 6: 1}
36
成就感 94
愉快心境 32
放松程度 32
压力应对 42
体力充沛 50
情感充沛度 16
{1: 34, 4: 11, 3: 9, 2: 8, 5: 8, 6: 5}
43
成就感 97
愉快心境 28
放松程度 32
压力应对 39
体力充沛 100
情感充沛度 83
{1: 29, 4: 7, 3: 7, 2: 7, 5: 8, 6: 5}
24
成就感 82
愉快心境 25
放松程度 25
压力应对 25
体力充沛 100
情感充沛度 83
{1: 20, 4: 9, 3: 11, 2: 9, 5: 3, 6: 4}
61
成就感 57
愉快心境 32
放松程度 39
压力应对 32
体力充沛 37
情感充沛度 66
{1: 27, 4: 13, 3: 11, 2: 13, 5: 3, 6: 4}
40
成就感 77
愉快心境 46
放松程度 39
压力应对 46
体力充沛 37
情感充沛度 66
{1: 35, 4: 13, 3: 14, 2: 10, 5: 3, 6: 2}
113
成就感 100
愉快心境 35
放松程度 50
压力应对 46
体力充沛 37
情感充沛度 33
{1: 33, 4: 14, 3: 10, 2: 10, 5: 6, 6: 5}
114
成就感 94
愉快心境 35
放松程度 35
压力应对 50
体力充沛 75
情感充沛度 83
{1: 24, 4: 10, 3: 8, 2: 9, 5: 8, 6: 6}
55
成就感 68
愉快心境 32
放松程度 28
压力应对 35
体力充沛 100
情感充沛度 100
{1: 26, 4: 15, 3: 14, 2: 11, 5: 2, 6: 0}
57
成就感 74
愉快心境 39
放松程度 50
压力应对 53
体力充沛 25
情感充沛度 0
{1: 29, 4: 11, 3: 9, 2: 10, 5: 6, 6: 2}
69
成就感 82
愉快心境 35
放松程度 32
压力应对 39
体力充沛 75
情感充沛度 33
{1: 20, 4: 12, 3: 11, 2: 10, 5: 6, 6: 2}
50
成就感 57
愉快心境 35
放松程度 39
压力应对 42
体力充沛 75
情感充沛度 33
{1: 27, 4: 16, 3: 11, 2: 14, 5: 3, 6: 4}
118
成就感 77
愉快心境 50
放松程度 39
压力应对 57
体力充沛 37
情感充沛度 66
{1: 35, 4: 16, 3: 7, 2: 9, 5: 5, 6: 5}
20
成就感 100
愉快心境 32
放松程度 25
压力应对 57
体力充沛 62
情感充沛度 83
{1: 35, 4: 9, 3: 8, 2: 7, 5: 4, 6: 5}
35
成就感 100
愉快心境 25
放松程度 28
压力应对 32
体力充沛 50
情感充沛度 83
{1: 24, 4: 14, 3: 9, 2: 9, 5: 8, 6: 5}
44
成就感 68
愉快心境 32
放松程度 32
压力应对 50
体力充沛 100
情感充沛度 83
{1: 21, 4: 14, 3: 11, 2: 10, 5: 1, 6: 4}
41
成就感 60
愉快心境 35
放松程度 39
压力应对 50
体力充沛 12
情感充沛度 66
{1: 29, 4: 12, 3: 14, 2: 11, 5: 1, 6: 3}
131
成就感 82
愉快心境 39
放松程度 50
压力应对 42
体力充沛 12
情感充沛度 50
{1: 35, 4: 8, 3: 8, 2: 7, 5: 8, 6: 5}
9
成就感 100
愉快心境 25
放松程度 28
压力应对 28
体力充沛 100
情感充沛度 83
{1: 35, 4: 7, 3: 8, 2: 7, 5: 8, 6: 6}
132
成就感 100
愉快心境 25
放松程度 28
压力应对 25
体力充沛 100
情感充沛度 100
{1: 35, 4: 12, 3: 9, 2: 8, 5: 6, 6: 5}
134
成就感 100
愉快心境 28
放松程度 32
压力应对 42
体力充沛 75
情感充沛度 83
{1: 20, 4: 9, 3: 10, 2: 8, 5: 6, 6: 3}
133
成就感 57
愉快心境 28
放松程度 35
压力应对 32
体力充沛 75
情感充沛度 50
{1: 32, 4: 11, 3: 11, 2: 8, 5: 7, 6: 5}
3
成就感 91
愉快心境 28
放松程度 39
压力应对 39
体力充沛 87
情感充沛度 83
{1: 27, 4: 7, 3: 8, 2: 7, 5: 6, 6: 5}
71
成就感 77
愉快心境 25
放松程度 28
压力应对 25
体力充沛 75
情感充沛度 83
{1: 23, 4: 9, 3: 9, 2: 8, 5: 7, 6: 5}
6
成就感 65
愉快心境 28
放松程度 32
压力应对 32
体力充沛 87
情感充沛度 83
{1: 24, 4: 22, 3: 12, 2: 10, 5: 4, 6: 5}
135
成就感 68
愉快心境 35
放松程度 42
压力应对 78
体力充沛 50
情感充沛度 83
{1: 23, 4: 14, 3: 12, 2: 10, 5: 2, 6: 1}
48
成就感 65
愉快心境 35
放松程度 42
压力应对 50
体力充沛 25
情感充沛度 16
{1: 25, 4: 14, 3: 14, 2: 13, 5: 0, 6: 1}
53
成就感 71
愉快心境 46
放松程度 50
压力应对 50
体力充沛 0
情感充沛度 16
{1: 20, 4: 14, 3: 9, 2: 16, 5: 3, 6: 3}
138
成就感 57
愉快心境 57
放松程度 32
压力应对 50
体力充沛 37
情感充沛度 50
{1: 32, 4: 13, 3: 12, 2: 11, 5: 4, 6: 2}
139
成就感 91
愉快心境 39
放松程度 42
压力应对 46
体力充沛 50
情感充沛度 33

计算脊柱

In [4]:

list4 = ['颈椎','胸椎','腰椎','骶尾椎']
list5 = [[0,10,10],[10,17,7],[17,24,6],[24,26,2]]
filename = 'data/result_青海2025-1.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for k, v in dict1.items():
    if 'spine' in v.keys():
        spine = v['spine']
        for i in range(0,4):
            score = 0
            for ii in range(list5[i][0],list5[i][1]):
                score+= spine[ii]
            print(k,list4[i],int(score/list5[i][2]*100)-100)

5 颈椎 20
5 胸椎 28
5 腰椎 66
5 骶尾椎 100
99 颈椎 80
99 胸椎 100
99 腰椎 83
99 骶尾椎 50
101 颈椎 30
101 胸椎 28
101 腰椎 83
101 骶尾椎 100
100 颈椎 60
100 胸椎 100
100 腰椎 83
100 骶尾椎 100
102 颈椎 80
102 胸椎 100
102 腰椎 100
102 骶尾椎 100
105 颈椎 60
105 胸椎 42
105 腰椎 83
105 骶尾椎 100
104 颈椎 90
104 胸椎 57
104 腰椎 100
104 骶尾椎 100
103 颈椎 60
103 胸椎 71
103 腰椎 100
103 骶尾椎 100
18 颈椎 10
18 胸椎 57
18 腰椎 16
18 骶尾椎 50
28 颈椎 70
28 胸椎 100
28 腰椎 100
28 骶尾椎 100
33 颈椎 90
33 胸椎 71
33 腰椎 100
33 骶尾椎 100
107 颈椎 40
107 胸椎 100
107 腰椎 100
107 骶尾椎 100
39 颈椎 60
39 胸椎 85
39 腰椎 83
39 骶尾椎 100
15 颈椎 100
15 胸椎 100
15 腰椎 100
15 骶尾椎 100
108 颈椎 60
108 胸椎 71
108 腰椎 100
108 骶尾椎 100
36 颈椎 70
36 胸椎 100
36 腰椎 83
36 骶尾椎 100
43 颈椎 60
43 胸椎 57
43 腰椎 66
43 骶尾椎 100
24 颈椎 90
24 胸椎 100
24 腰椎 83
24 骶尾椎 100
61 颈椎 60
61 胸椎 28
61 腰椎 83
61 骶尾椎 100
40 颈椎 70
40 胸椎 100
40 腰椎 50
40 骶尾椎 100
113 颈椎 90
113 胸椎 71
113 腰椎 100
113 骶尾椎 100
114 颈椎 70
114 胸椎 85
114 腰椎 100
114 骶尾椎 100
55 颈椎 100
55 胸椎 100
55 腰椎 100
55 骶尾椎 100
57 颈椎 50
57 胸椎 42
57 腰椎 83
57 骶尾椎 100
69 颈椎 90
69 胸椎 85
69 腰椎 100
69 骶尾椎 100
50 颈椎 60
50 胸椎 85
50 腰椎 100
50 骶尾椎 100
118 颈椎 50
118 胸椎 42
118 腰椎 50
118 骶尾椎 100
20 颈椎 50
20 胸椎 85
20 腰椎 66
20 骶尾椎 100
35 颈椎 80
35 胸椎 100
35 腰椎 83
35 骶尾椎 100
44 颈椎 90
44 胸椎 100
44 腰椎 83
44 骶尾椎 100
41 颈椎 20
41 胸椎 28
41 腰椎 33
41 骶尾椎 100
131 颈椎 70
131 胸椎 57
131 腰椎 100
131 骶尾椎 100
9 颈椎 100
9 胸椎 100
9 腰椎 83
9 骶尾椎 100
132 颈椎 90
132 胸椎 100
132 腰椎 100
132 骶尾椎 100
134 颈椎 60
134 胸椎 100
134 腰椎 66
134 骶尾椎 100
133 颈椎 90
133 胸椎 100
133 腰椎 100
133 骶尾椎 100
3 颈椎 60
3 胸椎 57
3 腰椎 83
3 骶尾椎 100
71 颈椎 100
71 胸椎 100
71 腰椎 100
71 骶尾椎 100
6 颈椎 80
6 胸椎 71
6 腰椎 83
6 骶尾椎 100
135 颈椎 70
135 胸椎 85
135 腰椎 66
135 骶尾椎 100
48 颈椎 60
48 胸椎 57
48 腰椎 100
48 骶尾椎 100
53 颈椎 20
53 胸椎 71
53 腰椎 50
53 骶尾椎 100
138 颈椎 80
138 胸椎 100
138 腰椎 83
138 骶尾椎 100
139 颈椎 80
139 胸椎 100
139 腰椎 66
139 骶尾椎 100

In [5]:

import openpyxl

list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp']
filename = 'data/result_青海2025-1.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
data_list = []
for k, v in dict1.items():
    list6 = []
    list6.append(str(k).rjust(4,'0'))
    list6.append(v['name'])
    list6.append(v['sex'])
    list6.append(v['unit'])
    list6.append(v['age'])
    if 'bmi' in v.keys():
        bmi = v['bmi']['成绩']
        list6.append(bmi.split(',')[0]+' 厘米')
        list6.append(bmi.split(',')[1]+' 千克')
        list6.append(v['bmi']['score'])
    else:
        list6.append('')
        list6.append('')
        list6.append('')
    for xm in list_item:
        if xm in v.keys():
            list6.append(v[xm]['成绩'])
            list6.append(v[xm]['score'])
        else:
            list6.append('')
            list6.append('')
            
    if 'tcm' in v.keys():
        list1 = []
        tcm =v['tcm']
        for item in tcm:
            list1.append(item)
        score = tcm_calc(list1)

        result = tcm_kind(score)
        kind = result['kind']
        near = result['near']
        #print(k,kinds[kind], near, score)
        list6.append(kinds[kind])
        if near:
            list6.append('是')
        else:
            list6.append('')
        for item in score:
            list6.append(item)
    else:
        for i in range(0,11):
            list6.append('')
            i+=1
    if 'psy' in v.keys():
        psy=v['psy']
        for i in range(26,40):
            new_valve = psy[i]-1
            psy[i] = new_valve
        dict3 = {}
        
        i = 0
        for item in qb:
            dict3.setdefault(item,0)
            dict3[item] +=psy[i]
            i+=1
        
        for i in range(0,6):
            score = int(dict3[i+1]/list3[i][0]/list3[i][1]*100)
            list6.append(score)
    else:
        for i in range(0,6):
            list6.append('')
            i+=1
    if 'spine' in v.keys():
        spine = v['spine']
        for i in range(0,4):
            score = 0
            for ii in range(list5[i][0],list5[i][1]):
                score+= spine[ii]
            list6.append(int(score/list5[i][2]*100)-100)
    else:
        for i in range(0,4):
            list6.append('')
            i+=1
    data_list.append(list6)
filename = 'data/青海测试情况表2025.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in data_list:
    sheet.append(row)
    
wb.save(filename)

In [113]:

import openpyxl

filename = 'data/result_青海all_2.json'
list4 = ['颈椎','胸椎','腰椎','骶尾椎']
list5 = [[0,10,10],[10,17,7],[17,24,6],[24,26,2]]
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
data_list = []
for k, v in dict1.items():
    
    if 'spine' in v.keys():
        list6 = []
        list6.append(str(k).rjust(4,'0'))
        list6.append(v['name'])
        list6.append(v['sex'])
        list6.append(v['unit'])
        list6.append(v['age'])    
        spine = v['spine']
        for i in range(0,4):
            score = 0
            for ii in range(list5[i][0],list5[i][1]):
                score+= spine[ii]
            list6.append(int(score/list5[i][2]*100)-100)
        for item in spine:
            list6.append(item)
        data_list.append(list6)
 
filename = 'data/青海脊椎情况表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in data_list:
    sheet.append(row)
    
wb.save(filename)

In [ ]:

95 KiB Raw Permalink Blame History

体测数据处理

体测人员导入

生成读卡系统文件

获取人员测试成绩

转换报告格式

生成完善得分

导出测试人员信息

导入问卷信息

生成报告

统计人员情况

统计问卷人员情况表

统计报告发送情况表

筛选家属人员

体测数据处理（2025年）

体测人员导入

获取人员测试成绩

生成测试得分

导出测试人员信息

导入问卷信息

统计参加问卷人员

统计问卷人员手机号码未登记

统计未参加问卷人员

生成报告

体测数据分析

常用参数及自定义函数

计算中医体质

计算心理

计算脊柱

95 KiB

Raw Permalink Blame History