jupyter/体测单位/东营杨庙202307.ipynb at master

512song/jupyter

0 Fork 0

Files

T

512song ac36b00339 20241122

2024-11-22 15:50:59 +08:00

50 KiB

Raw Permalink Blame History

杨庙人员信息导入

In [ ]:

import openpyxl
import json


wb = openpyxl.load_workbook('data/杨庙社区居民信息表20230801（系统导入版）.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:
        code = int(sheet.cell(n, 1).value)
        person.setdefault(code, {})
        dict1 = {}
        dict1['name'] = sheet.cell(n, 2).value
        dict1['sex'] = sheet.cell(n, 3).value
        birth = str(sheet.cell(n, 4).value).split()[0]
        dict1['birth'] = birth              
        person[code] = dict1

filename = 'data/杨庙社区人员信息2307.json'
with open(filename, 'w') as fl:
    json.dump(person, fl, ensure_ascii=False)
print('ok')

检测问卷编号重复

In [ ]:

import openpyxl
import json

filename = 'data/杨庙社区人员信息2307.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 = {}
sheet = wb.active
data1 =list(sheet.values)
list_bh = data1[0][1:]
list1 = []
for item in list_bh:
    if item not in list1:
        list1.append(item)
    else:
        print(item)

导入手工体测数据

In [ ]:

import openpyxl
import json

filename = 'data/杨庙社区人员信息2307.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 = item[0]
    if str(code) in dict2.keys():
        for i in range(1,8):
            if item[i] is not None:
                list1.append((141,code,dict3[i],item[i],'2023-08-03','2023-08-03 12:00:00'))
print(list1)

导出体测信息

In [ ]:

import json
import datetime
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
item['1']['en'] = 'lung'
item['2']['en'] = 'grip'
item['3']['en'] = 'flexion'
item['6']['en'] = 'balance'
item['7']['en'] = 'reaction'
item['10']['en'] = 'height'
item['11']['en'] = 'weight'
#print(item)
re_ta = {}
dict1 = {}
list1 = []
filename = 'data/杨庙社区人员信息2307.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/places_result_20230803.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]['birth'] = dict1[user]['birth']
        item_name = item[str(m_item)]['en']
        re_ta[user].setdefault(item_name,{})  
        performance = int(result[4])/item[m_item]['divisor']
        score = result[5]
        re_ta[user][item_name]['成绩'] = performance
        re_ta[user][item_name]['得分'] = score
list2 = ['grip','lung','balance','reaction','flexion']
list3 = []
for k, v in re_ta.items():
    dict2 = {}
    dict2['id'] = int(k)
    dict2['title'] = '杨庙社区'
    dict2['subtitle'] = ''
    dict2['name'] = v['name']
    if v['sex'] == '男':
        dict2['gender'] = 'male'
    else:
        dict2['gender'] = 'female'
    birth = v['birth'].split()[0].split('-')    
    nian = int(birth[0].strip())
    yue = int(birth[1].strip())
    ri = int(birth[2].strip())
    #print(k,nian,yue,ri)
    days = (datetime.date(2023, 7, 31)-datetime.date(nian,yue,ri)).days
    dict2['month'] = int(days/30)
    dict3 = {}    
    for xm in list2:
        if xm in v.keys():
            dict3[xm] = {'mark':str(v[xm]['成绩']),'score':int(v[xm]['得分'])}
        if 'height' in v.keys():
            dict3['bmi'] = {'mark':str(v['height']['成绩'])+','+str(v['weight']['成绩']),'score':int(v['weight']['得分'])}
    dict2['fit'] = dict3
    list3.append(dict2)
print(list3)

In [ ]:

import json
import datetime
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
item['1']['en'] = 'lung'
item['2']['en'] = 'grip'
item['3']['en'] = 'flexion'
item['6']['en'] = 'balance'
item['7']['en'] = 'reaction'
item['10']['en'] = 'height'
item['11']['en'] = 'weight'
#print(item)
re_ta = {}
dict1 = {}
list1 = []
filename = 'data/杨庙社区人员信息2307.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/places_result_20230803.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]['birth'] = dict1[user]['birth']
        item_name = item[str(m_item)]['en']
        re_ta[user].setdefault(item_name,{})  
        performance = int(result[4])/item[m_item]['divisor']
        score = result[5]
        re_ta[user][item_name]['成绩'] = performance
        re_ta[user][item_name]['得分'] = score
list2 = ['grip','lung','balance','reaction','flexion']
list3 = []
result = {}
for k, v in re_ta.items():
    dict2 = {}
    dict2['id'] = int(k)
    dict2['title'] = '杨庙社区'
    dict2['subtitle'] = ''
    dict2['name'] = v['name']
    if v['sex'] == '男':
        dict2['gender'] = 'male'
    else:
        dict2['gender'] = 'female'
    birth = v['birth'].split()[0].split('-')    
    nian = int(birth[0].strip())
    yue = int(birth[1].strip())
    ri = int(birth[2].strip())
    #print(k,nian,yue,ri)
    days = (datetime.date(2023, 7, 31)-datetime.date(nian,yue,ri)).days
    dict2['month'] = int(days/30)
    dict3 = {}    
    for xm in list2:
        if xm in v.keys():
            mark = v[xm]['成绩']
            if xm == 'lung':
                mark = int(mark)
                
            dict3[xm] = {'mark':str(mark),'score':int(v[xm]['得分'])}
        if 'height' in v.keys():
            dict3['bmi'] = {'mark':str(v['height']['成绩'])+','+str(v['weight']['成绩']),'score':int(v['weight']['得分'])}
    dict2['fit'] = dict3
    result[k] = dict2

filename = 'data/result_杨庙230801.json'
with open(filename,'w') as fl:
    json.dump(result, fl) 
print('ok')

规范身高体重

In [ ]:

import json
filename = 'data/result_杨庙2308.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for k, v in dict1.items():
    if 'bmi' in v['fit'].keys():
        old = v['fit']['bmi']['mark']
        height = old.split(',')[0]
        weight = old.split(',')[1]
        w = round(float(weight),1)
        v['fit']['bmi']['mark'] = height+','+str(w)
filename = 'data/result_杨庙230801.json'
with open(filename,'w') as fl:
    json.dump(dict1, fl, ensure_ascii=False) 
print('ok')

问卷信息导入

In [ ]:

import openpyxl
import json

filename = 'data/杨庙社区人员信息2307.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 = {}
sheet = wb.active
data1 =list(sheet.values)
list_bh = data1[0][1:]
#print(list_bh)
del data1[0]
for i in range(1,len(list_bh)+1):
    list2 = []
    
    for item in data1:
        list2.append(item[i])
    dict1[list_bh[i-1]] = list2
filename = 'data/result_杨庙2308.json'
with open(filename,'r') as fl:
    dict2 = json.load(fl)
for k,v in dict1.items():
    if str(k) in dict2.keys():
        if v[0] is not None:
            psy_yangmiao_old = v[:30]
            #dict2[str(k)]['psy_yangmiao_old'] = psy_yangmiao_old
        if v[30] is not None:
            tcm = v[30:90]
            #print(tcm,len(tcm))
        if v[90] is not None:
            spine = v[90:]
            xx22 =  spine[21]
            xx23 =  spine[22]
            spine[21] = xx23
            spine[22] = xx22
            
        dict2[str(k)]['psy_yangmiao_old'] = psy_yangmiao_old
        dict2[str(k)]['tcm'] = tcm
        dict2[str(k)]['spine'] = spine
filename = 'data/result_杨庙2308.json'
with open(filename,'w') as fl:
    json.dump(dict2, fl, ensure_ascii=False) 
print('ok')

单份报告生成

In [ ]:

import requests
import json

headers = {
    "Content-Type": "application/json; charset=UTF-8"
    }

mydata ={"path":"./于先生体测报告.pdf",
         "id":"00001",
         "title":"社区测试报告",
         "name":"于先生",
         "gender":"male",
         "month":725,
         "fit":{"grip":{"mark":"40.9","score":2},
                "bmi":{"mark":"181.2,81.3","score":5},
                "lung":{"mark":"4590","score":5},
                "balance":{"mark":"57.2","score":4},
                "reaction":{"mark":"0.466","score":4},
                "flexion":{"mark":"-11.6","score":0},
                "jump":{"mark":"26.3","score":2},
                "pushup":{"mark":"3","score":1},
                "step":{"mark":"43","score":1}
               },
         "surveys":{"tcm":[3,3,3,2,2,3,1,3,3,1,1,2,3,4,1,1,2,3,4,1,2,4,2,2,4,5,5,3,1,3,3,2,3,4,3,3,2,3,3,3,3,2,4,3,5,2,3,3,2,1,3,4,3,3,3,3,1,1,2,3],"psy_yangmiao_old":[3,3,2,3,3,3,4,4,4,1,4,4,4,4,5,5,5,3,4,3,3,4,4,3,4,4,3,4,3,3],"spine":[1,2,1,2,1,2,2,1,2,2,1,2,2,2,1,1,1,1,1,2,2,2,2,2,1,2]}}
print(type(json.dumps(mydata)))
x = requests.post('http://192.168.31.163:3003', data = json.dumps(mydata), headers=headers)
print(x.text)

In [ ]:

import requests
import json

headers = {
    "Content-Type": "application/json; charset=UTF-8"
    }

mydata ={"path":"./00343-殷焕章.pdf",
         "id":"00343",
         "title":"东营羊庙社区",
         "name":"殷焕章",
         "gender":"male",
         "month":992,
         "fit":{"grip":{"mark":"20.6","score":0},
                "bmi":{"mark":"160.4,60.0","score":5},
                "lung":{"mark":"1703","score":1},
                "balance":{"mark":"3.9","score":2},
                "reaction":{"mark":"1.519","score":0},
                "flexion":{"mark":"8.9","score":4}
               }
         }
print(type(json.dumps(mydata)))
x = requests.post('http://192.168.31.163:3003', data = json.dumps(mydata), headers=headers)
print(x.text)

报告生成

In [ ]:

import requests
import json

headers = {
    "Content-Type": "application/json; charset=UTF-8"
    }
filename = 'data/result_杨庙230801.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

fiie_path ='./141/'
dict2 = {}
list1 = []
for  k, v in dict1.items():
    mydata = {}
    
    id = str(k).rjust(5,"0")
    mydata['path'] = fiie_path+id+'-'+ v['name']+'.pdf'
    mydata['title'] = '东营杨庙社区'
    mydata['id'] = id
    mydata['name'] = v['name']
    mydata['gender'] = v['gender'] 
    mydata['month'] = v['month']
    if 'fit' in v.keys():
        dict2 = {}
        for k1, v1 in v['fit'].items():
            dict2[k1] = v1
        mydata['fit'] = dict2
    #data['surveys'] = {}
    for item in ('tcm','psy_yangmiao_old','spine'):
        if item in v.keys():
            mydata.setdefault('surveys',{})
            dict2 = {}            
            mydata['surveys'][item] = v[item]
    list1.append(mydata)        
    #x = requests.post('http://192.168.31.163:3003', data = json.dumps(mydata), headers=headers)
    #print(id,v['name'],x.text)
    #x.close()
    #if k =='218':
    #    print(mydata)
json_data = json.dumps(list1,ensure_ascii=False, indent=4)  

# 将 json 数据写入文件
with open("data1.json", "w",encoding = 'utf-8') as file:
    file.write(json_data)

In [ ]:

import requests
import json
import time
from pathlib import Path

headers = {
    "Content-Type": "application/json; charset=UTF-8"
    }
filename = 'data/result_杨庙230801.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

file_path ='/home/songyi/pdf-typescript-master/141/'
dict2 = {}
list1 = []
for  k, v in dict1.items():
    id = str(k).rjust(5,"0")
    fn = file_path+id+'-'+ v['name']+'.pdf'
    
    mydata = {}
    
    id = str(k).rjust(5,"0")
    mydata['path'] = file_path+id+'-'+ v['name']+'.pdf'
    mydata['title'] = '东营杨庙社区'
    mydata['id'] = id
    mydata['name'] = v['name']
    mydata['gender'] = v['gender'] 
    mydata['month'] = v['month']
    if 'fit' in v.keys():
        dict2 = {}
        for k1, v1 in v['fit'].items():
            dict2[k1] = v1
        mydata['fit'] = dict2
    #data['surveys'] = {}
    for item in ('tcm','psy_yangmiao_old','spine'):
        if item in v.keys():
            mydata.setdefault('surveys',{})
            dict2 = {}            
            mydata['surveys'][item] = v[item]
           
    x = requests.post('http://10.11.12.4:3003', data = json.dumps(mydata), headers=headers)
    print(id,v['name'],x.text)
    x.close()
    time.sleep(0.05)
        #if k =='218':
        #    print(mydata)

生成报告人员信息

In [47]:

import json
import openpyxl

filename = 'data/result_杨庙230801.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
filename = 'data/杨庙社区人员信息2307.json'
with open(filename,'r') as fl:
    dict2 = json.load(fl)

list1 = []
for  k, v in dict1.items():
    id = str(k).rjust(5,"0")
    
    name = v['name']
    sex = dict2[k]['sex']
    birth = dict2[k]['birth']
    list1.append([id,name,sex,birth])
filename = 'data/杨庙测试2023年8月人员明细表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active

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

In [ ]:

统计报告页数

In [ ]:

import pdfplumber
import glob

file_path ='/home/songyi/pdf-typescript-master/141/'
fls=glob.glob('/home/songyi/pdf-typescript-master/141/*.pdf')
n = 0
for fl in fls:
    pdf = pdfplumber.open(fl)
    n= n+len(pdf.pages)
    print(fl,len(pdf.pages))
print(n)

导入202303月体测信息

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/杨庙体测明确人员信息（20230328）.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 

filename = 'data/places_result_20230812.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]['部门'] = 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"]}'
        re_ta[user][item_name]['得分'] = result[5]
print(len(re_ta))
filename = 'data/result_杨庙社区2303.json'
with open(filename,'w') as fl:
    json.dump(re_ta, fl) 
print('ok')

对应两次测试信息

In [ ]:

import json
import openpyxl

dict4 = {
    'lung':'肺活量',
    'grip':'握力',
    'flexion' : '坐位体前屈',
    'balance' : '单脚站立',
    'reaction' : '选择反应时'
}

wb = openpyxl.load_workbook('data/副本杨庙测试2023年8月报告明细表.xlsx')
sheet = wb.active
items = ['bmi','lung','grip','flexion','balance','reaction']
dict1 = {}
for n in range(2, sheet.max_row+1):
    if sheet.cell(n,1).value is not None:
        code = str(sheet.cell(n, 1).value)    
        dict1[code] = str(sheet.cell(n, 3).value)    
filename = 'data/result_杨庙230801.json'
with open(filename,'r') as fl:
    dict2 = json.load(fl) 
filename = 'data/result_杨庙社区2303.json'
with open(filename,'r') as fl:
    dict3 = json.load(fl) 
list1 = []
for k, v in dict1.items():
    list2 = []
    list2.append(k)
    list2.append(dict2[k]['name'])
    list2.append(dict2[k]['gender'])
    list2.append(dict3[dict1[k]]['部门'])
    for item in items:
        if item == 'bmi' and item in dict2[k]['fit'].keys():
            list2.append(dict2[k]['fit'][item]['mark'].split(',')[0])
            if '身高' in dict3[dict1[k]].keys():
                list2.append(dict3[dict1[k]]['身高']['成绩'])
            else:
                list2.append('')                
            list2.append(dict2[k]['fit'][item]['mark'].split(',')[1])
            if '体重' in dict3[dict1[k]].keys():
                list2.append(dict3[dict1[k]]['体重']['成绩'])
            else:
                list2.append('')
        if item != 'bmi' and item in dict2[k]['fit'].keys():
            list2.append(dict2[k]['fit'][item]['mark'])
            if dict4[item] in dict3[dict1[k]].keys():
                list2.append(dict3[dict1[k]][dict4[item]]['成绩'])
            else:
                list2.append('') 
    list1.append(list2)
print(list1)
filename = 'data/杨庙测试人员测试对比信息表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active

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

分析三月数据

In [26]:

import json
import openpyxl

list1 = []

wb = openpyxl.load_workbook('data/杨庙体测报告明细表.xlsx')
sheet = wb.active
items = ['bmi','lung','grip','flexion','balance','reaction']
dict1 = {}
for n in range(2, sheet.max_row+1):
    if sheet.cell(n,1).value is not None:
        code = int(sheet.cell(n, 1).value)    
        list1.append(str(code))   


items = ['体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
filename = 'data/result_杨庙社区2303.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 


for item in items:
    m = 0
    f = 0
    score = 0
    t_score = 0
    for k,v in dict1.items():
        if v['sex'] == 1 and item in v.keys() and k in list1:
            #print(k)
            
            if v[item]['得分']!="":
                m = m +1
                score = score+int(v[item]['得分'])
    if m>0:
        print(f'{item}平均成绩：{round(score/m,4)}分，男性：{m}人')
    t_score = t_score + score
    score = 0
    for k,v in dict1.items():
        if v['sex'] == 2 and item in v.keys() and k in list1:
            
            if v[item]['得分']!='':
                f = f +1
                score = score+int(v[item]['得分'])
    if f >0:
        print(f'{item}平均成绩：{round(score/f,4)}分，女性：{f}人')
    t_score = t_score + score
    if (f+m)>0:
        print(f'{item}平均成绩：{round(t_score/(f+m),4)}分，总体：{f+m}人')

体重平均成绩：3.6196分，男性：184人
体重平均成绩：3.6765分，女性：204人
体重平均成绩：3.6495分，总体：388人
肺活量平均成绩：1.8865分，男性：185人
肺活量平均成绩：1.7122分，女性：205人
肺活量平均成绩：1.7949分，总体：390人
握力平均成绩：1.7838分，男性：185人
握力平均成绩：1.774分，女性：208人
握力平均成绩：1.7786分，总体：393人
坐位体前屈平均成绩：2.8764分，男性：178人
坐位体前屈平均成绩：2.9424分，女性：191人
坐位体前屈平均成绩：2.9106分，总体：369人
纵跳平均成绩：0.8421分，男性：57人
纵跳平均成绩：0.5513分，女性：78人
纵跳平均成绩：0.6741分，总体：135人
俯卧撑平均成绩：3.0333分，男性：30人
俯卧撑平均成绩：3.0333分，总体：30人
一分钟仰卧起坐平均成绩：3.2927分，女性：41人
一分钟仰卧起坐平均成绩：3.2927分，总体：41人
单脚站立平均成绩：1.6932分，男性：176人
单脚站立平均成绩：1.7135分，女性：192人
单脚站立平均成绩：1.7038分，总体：368人
选择反应时平均成绩：2.4725分，男性：182人
选择反应时平均成绩：2.2927分，女性：205人
选择反应时平均成绩：2.3773分，总体：387人
台阶指数平均成绩：3.475分，男性：40人
台阶指数平均成绩：3.6866分，女性：67人
台阶指数平均成绩：3.6075分，总体：107人

In [37]:

import json
import openpyxl

list1 = []

wb = openpyxl.load_workbook('data/杨庙体测报告明细表.xlsx')
sheet = wb.active
items = ['bmi','lung','grip','flexion','balance','reaction']
dict1 = {}
for n in range(2, sheet.max_row+1):
    if sheet.cell(n,1).value is not None:
        code = int(sheet.cell(n, 1).value)    
        list1.append(str(code))   


items = ['体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
filename = 'data/result_杨庙社区2303.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 


m =0
f =0
for k,v in dict1.items():
    if v['sex'] == 1 and  k in list1:
        m = m +1
print(f'男性：{m}人')

for k,v in dict1.items():
    if v['sex'] == 2 and  k in list1:
        f = f +1
print(f'女性：{f}人')

print(f'总体：{f+m}人')

男性：187人
女性：208人
总体：395人

In [28]:

import json
import openpyxl

list1 = []

wb = openpyxl.load_workbook('data/杨庙体测报告明细表.xlsx')
sheet = wb.active
items = ['bmi','lung','grip','flexion','balance','reaction']
dict1 = {}
for n in range(2, sheet.max_row+1):
    if sheet.cell(n,1).value is not None:
        code = int(sheet.cell(n, 1).value)    
        list1.append(str(code))   


items = ['体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
filename = 'data/result_杨庙社区2303.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for item in list1:
    if item not in dict1.keys():
        print(item)

分析八月数据

In [46]:

import json
import openpyxl

list1 = []

wb = openpyxl.load_workbook('data/杨庙测试2023年8月报告明细表.xlsx')
sheet = wb.active
items = ['bmi','lung','grip','flexion','balance','reaction']
dict1 = {}
for n in range(2, sheet.max_row+1):
    if sheet.cell(n,1).value is not None:
        code = int(sheet.cell(n, 1).value)    
        list1.append(str(code))   
filename = 'data/result_杨庙2308.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
        
for item in items:    
    m = 0
    f = 0
    score = 0
    t_score = 0
    for k,v in dict1.items():
        if v['gender'] == 'male' and item in v['fit'].keys() and k in list1:
            #print(k)
            
            if v['fit'][item]['score']!="":
                m = m +1
                score = score+int(v['fit'][item]['score'])
    if m>0:
        print(f'{item}平均成绩：{round(score/m,4)}分，男性：{m}人')
    t_score = t_score + score
    score = 0
    for k,v in dict1.items():
        if v['gender'] == 'female' and item in v['fit'].keys() and k in list1:
            
            if v['fit'][item]['score']!="":
                f = f +1
                score = score+int(v['fit'][item]['score'])
    if f >0:
        print(f'{item}平均成绩：{round(score/f,4)}分，女性：{f}人')
    t_score = t_score + score
    if (f+m)>0:
        print(f'{item}平均成绩：{round(t_score/(f+m),4)}分，总体：{f+m}人')

bmi平均成绩：3.7597分，男性：258人
bmi平均成绩：3.7911分，女性：316人
bmi平均成绩：3.777分，总体：574人
lung平均成绩：1.8803分，男性：259人
lung平均成绩：1.652分，女性：319人
lung平均成绩：1.7543分，总体：578人
grip平均成绩：1.5328分，男性：259人
grip平均成绩：1.5063分，女性：318人
grip平均成绩：1.5182分，总体：577人
flexion平均成绩：2.2923分，男性：260人
flexion平均成绩：2.511分，女性：319人
flexion平均成绩：2.4128分，总体：579人
balance平均成绩：2.082分，男性：244人
balance平均成绩：1.9628分，女性：296人
balance平均成绩：2.0167分，总体：540人
reaction平均成绩：1.9648分，男性：256人
reaction平均成绩：1.7453分，女性：318人
reaction平均成绩：1.8432分，总体：574人

In [45]:

import json
import openpyxl

list1 = []

wb = openpyxl.load_workbook('data/杨庙测试2023年8月报告明细表.xlsx')
sheet = wb.active
items = ['bmi','lung','grip','flexion','balance','reaction']

for n in range(2, sheet.max_row+1):
    if sheet.cell(n,1).value is not None:
        code = int(sheet.cell(n, 1).value)    
        list1.append(str(code))   


items = ['体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
filename = 'data/杨庙社区人员信息2307.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
print(len(list1))

m =0
f =0
for k,v in dict1.items():
    if v['sex'] == '男'  and k in list1:
        m = m +1
print(f'男性：{m}人')

for k,v in dict1.items():
    if v['sex'] == '女'  and k in list1:
        f = f +1
print(f'女性：{f}人')

print(f'总体：{f+m}人')

579
男性：260人
女性：319人
总体：579人

In [43]:

import json
import openpyxl

list1 = []

wb = openpyxl.load_workbook('data/杨庙测试2023年8月报告明细表.xlsx')
sheet = wb.active
items = ['bmi','lung','grip','flexion','balance','reaction']
dict1 = {}
for n in range(2, sheet.max_row+1):
    if sheet.cell(n,1).value is not None:
        code = int(sheet.cell(n, 1).value)    
        list1.append(str(code))   


items = ['体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
filename = 'data/result_杨庙2308.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for item in list1:
    if item not in dict1.keys():
        print(item)

In [44]:

import json
import openpyxl

list1 = []

wb = openpyxl.load_workbook('data/杨庙测试2023年8月报告明细表.xlsx')
sheet = wb.active
items = ['bmi','lung','grip','flexion','balance','reaction']
dict1 = {}
for n in range(2, sheet.max_row+1):
    if sheet.cell(n,1).value is not None:
        code = int(sheet.cell(n, 1).value) 
        if str(code) not in list1:
            list1.append(str(code))
        else:
            print(code)


items = ['体重','肺活量','握力','坐位体前屈','纵跳','俯卧撑','一分钟仰卧起坐','单脚站立','选择反应时','台阶指数']
filename = 'data/result_杨庙2308.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
for item in list1:
    if item not in dict1.keys():
        print(item)

生成体测成绩表

In [6]:

import json
import openpyxl

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

filename = 'data/result_杨庙2308.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)

list1 = []
for k, v in dict1.items():    #print(k,dict2[str(k)]['name'])
    list2 = []
    list2.append(str(k).rjust(5,'0'))
    list2.append(dict1[k]['name'])    
    list2.append(dict1[k]['sex'])
    list2.append(dict1[k]['部门'])
    i = 0
    
    for item in items:
        if item in dict1[k].keys():
            list2.append(dict1[k][item]['成绩'])
            list2.append(dict1[k][item]['得分']) 
            i+=1
        elif item =='name':
            list2.append(dict1[k][item])
        else:
            list2.append('') 
            list2.append('')
    list1.append(list2)
filename = 'data/杨庙202303体测明细表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
sheet.append(title)
for row in list1:
    sheet.append(row)
    
wb.save(filename)
print('ok')

[0;31m---------------------------------------------------------------------------[0m
[0;31mKeyError[0m                                  Traceback (most recent call last)
Cell [0;32mIn[6], line 17[0m
[1;32m     15[0m list2[38;5;241m.[39mappend([38;5;28mstr[39m(k)[38;5;241m.[39mrjust([38;5;241m5[39m,[38;5;124m'[39m[38;5;124m0[39m[38;5;124m'[39m))
[1;32m     16[0m list2[38;5;241m.[39mappend(dict1[k][[38;5;124m'[39m[38;5;124mname[39m[38;5;124m'[39m])    
[0;32m---> 17[0m list2[38;5;241m.[39mappend([43mdict1[49m[43m[[49m[43mk[49m[43m][49m[43m[[49m[38;5;124;43m'[39;49m[38;5;124;43msex[39;49m[38;5;124;43m'[39;49m[43m][49m)
[1;32m     18[0m list2[38;5;241m.[39mappend(dict1[k][[38;5;124m'[39m[38;5;124m部门[39m[38;5;124m'[39m])
[1;32m     19[0m i [38;5;241m=[39m [38;5;241m0[39m

[0;31mKeyError[0m: 'sex'

In [9]:

import json
import openpyxl

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

filename = 'data/result_杨庙2308.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = []
for k, v in dict1.items():    #print(k,dict2[str(k)]['name'])
    list2 = []
    list2.append(str(k).rjust(5,'0'))
    list2.append(dict1[k]['name'])    
    list2.append(dict1[k]['gender'])
    list2.append(dict1[k]['title'])
    i = 0
    if 'bmi' in v['fit'].keys():
        m_bmi = v['fit']['bmi']['mark']
        list2.append(m_bmi.split(',')[0])
        list2.append(m_bmi.split(',')[1])
        list2.append(v['fit']['bmi']['score'])
        i+=1
    else:
        list2.append('') 
        list2.append('') 
        list2.append('') 
           
        
    for item in items:
        if item in v['fit'].keys():
            list2.append(v['fit'][item]['mark'])
            list2.append(v['fit'][item]['score']) 
            i+=1
        
        else:
            list2.append('') 
            list2.append('') 
    
    list1.append(list2)
filename = 'data/杨庙202308体测明细表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
sheet.append(title)
for row in list1:
    sheet.append(row)
    
wb.save(filename)
print('ok')

ok

In [ ]:

50 KiB Raw Permalink Blame History

杨庙人员信息导入

检测问卷编号重复

导入手工体测数据

导出体测信息

规范身高体重

问卷信息导入

单份报告生成

报告生成

生成报告人员信息

统计报告页数

导入202303月体测信息

对应两次测试信息

分析三月数据

分析八月数据

生成体测成绩表

50 KiB

Raw Permalink Blame History