jupyter/体测单位/淄博高新区.ipynb

人员基本信息导入

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):
    if sheet.cell(n,2).value is None:
        break
    else:        
        code = int(sheet.cell(n, 1).value)
        person.setdefault(code, {})
        dict1 = {}
        dict1['name'] = sheet.cell(n, 2).value    
        sex = str(sheet.cell(n, 3).value)
        dict1['sex'] = sex
        dict1['birth'] = str(sheet.cell(n,8).value).split(' ')[0]
        if sheet.cell(n,5).value is not None:
            dict1['phone'] = 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 openpyxl
import json
import csv
filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
wb = openpyxl.load_workbook('data/高新区.xlsx')
sheet = wb.active
# sheets = wb.sheetnames
person = {}
for n in range(2, sheet.max_row+1):
    if sheet.cell(n,2).value is None:
        break
    else:        
        code = int(sheet.cell(n, 4).value)
        if str(code) in dict1.keys():
            dict1[str(code)]['unit'] = sheet.cell(n, 3).value
with open(filename, 'w') as fl:
    json.dump(dict1, fl, ensure_ascii=False)
print('ok')

统计测评人员

统计未参加心理测评人员

In [ ]:

import openpyxl
import json
import csv
filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
cjry = set()
filename = 'data/Survey_20230424-psy.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)
        cjry.add(line[2])
print(len(cjry))
list2 = []
for k, v in person.items():    
    #if 'phone' in v.keys() and v['phone'] in cjry:
    if 'phone' in v.keys() and v['phone'] not in cjry:
        list2.append([k,dict1[str(k)]['name'],dict1[str(k)]['phone']])
        
filename = 'data/高新未参加心理评估人员(20230424).xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in list2:
    sheet.append(row)
    
wb.save(filename)

生成心理测评人员明细

In [56]:

import openpyxl
import json
import csv
filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
cjry = set()
filename = 'data/Survey_20230424-psy.csv'
list2 = []
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)
        phone = str(line[2])
        result = json.loads(line[5])
        
        for k, v in dict1.items():
            if 'phone' in v.keys() and v['phone'] == phone:
                list1 = []
                list1.append(k)
                list1.append(phone)
                list1.append(v['name'])
                list1.append(v['sex'])
                if 'unit' in v.keys():
                    unit = v['unit']
                else:
                    unit = ' '
                list1.append(unit)
                #print(k,v['phone'],type(result))
                for k1, v1 in result.items():
                    list1.append(v1)
        list2.append(list1)
filename = 'data/高新心理评估明细表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in list2:
    sheet.append(row)
    
wb.save(filename)

统计中医测评人员

In [ ]:

import openpyxl
import json
import csv
filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
cjry = set()
filename = 'data/Survey_20230424-tcm1.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)
        cjry.add(line[2])
print(len(cjry))
list2 = []
for k, v in person.items():    
    #if 'phone' in v.keys() and v['phone'] in cjry:
    if 'phone' in v.keys() and v['phone'] not in cjry:
        list2.append([k,dict1[str(k)]['name'],dict1[str(k)]['phone']])
        
filename = 'data/高新未参加中医评估人员(20230424).xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in list2:
    sheet.append(row)
    
wb.save(filename)

生成中医测评人员明细

In [57]:

import openpyxl
import json
import csv
filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
cjry = set()
filename = 'data/Survey_20230424-tcm1.csv'
list2 = []
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)
        phone = str(line[2])
        result = json.loads(line[5])
        
        for k, v in dict1.items():
            if 'phone' in v.keys() and v['phone'] == phone:
                list1 = []
                list1.append(k)
                list1.append(phone)
                list1.append(v['name'])
                list1.append(v['sex'])
                if 'unit' in v.keys():
                    unit = v['unit']
                else:
                    unit = ' '
                list1.append(unit)
                #print(k,v['phone'],type(result))
                for k1, v1 in result.items():
                    list1.append(v1)
        list2.append(list1)
filename = 'data/高新中医明细表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in list2:
    sheet.append(row)
    
wb.save(filename)

统计信息缺少人员信息

In [ ]:

import openpyxl
import json
import csv
filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
cjry = set()
filename = 'data/Survey_20230424-gx.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)
        cjry.add(line[2])
print(len(cjry))
phones = set()
for k, v in person.items():    
    #if 'phone' in v.keys() and v['phone'] in cjry:
    if 'phone' in v.keys():
        phones.add(v['phone'])
list2 = []
for item in cjry:    
    if item  not in phones:
        list2.append(['','',item])
filename = 'data/高新缺失信息人员(20230424).xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in list2:
    sheet.append(row)
    
wb.save(filename)

统计生成报告人员情况表

In [ ]:

import json
import glob
from pathlib2 import Path
import openpyxl


filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
list1 = []
fi_path = 'file/130/20230424'
fl=glob.glob(f'{fi_path}/*.pdf')
print(len(fl))
for fn in fl:
    name = Path(fn).stem
    if name in dict1.keys():
        list1.append([name,dict1[name]['name']])
filename = 'data/高新报告明细表(20230424).xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
title = ['测试编号','姓名']
sheet.append(title)
for row in list1:
    sheet.append(row)
    
wb.save(filename)

报告分组重命名

In [ ]:

import os,sys,shutil
import json
import math
import openpyxl

filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
list1 = []
list_bh = []
depart = []
dict2 = {}
for k, v in dict1.items():
    dict2.setdefault(k,{})    
   
    if 'unit' in v.keys():
        m_depart = v['unit']
    else:
        m_depart = '未明确部门'
    dict2[k]['unit'] = m_depart
    if m_depart not in depart:
        depart.append(m_depart)
    dict2[k]['name'] = v['name']
    
old = []

        
fi_path = 'file/130/20230424'


# 创建部门办公室    
m_path = 'file/130/new'
for pn in depart:
    if not os.path.exists(m_path + '/' + pn):
        os.mkdir(m_path + '/' + pn)
fl=os.listdir(fi_path)
for fn in fl:
    if os.path.isfile(fi_path + '/' + fn):
        ofn = int(fn.split('.')[0])
        old.append(ofn) 
old.sort()
for n in old:    
    o_name = f'{fi_path}/{n}.pdf'
    unit = dict2[str(n)]['unit']
    name = dict2[str(n)]['name']
    n_name = f'{m_path}/{unit}/{str(n).rjust(5,"0")}-{name}.pdf'
    #print(o_name,n_name)
    if not os.path.exists(n_name):
        shutil.copyfile(o_name,n_name)
        print(n_name)

In [34]:

import os,sys,shutil
import json
import math
import openpyxl

filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
list1 = []
list_bh = []
depart = []
dict2 = {}
for k, v in dict1.items():
    dict2.setdefault(k,{})    
   
    if 'unit' in v.keys():
        m_depart = v['unit']
    else:
        m_depart = '未明确部门'
    dict2[k]['unit'] = m_depart
    if m_depart not in depart:
        depart.append(m_depart)
    dict2[k]['name'] = v['name']
    
old = []

        
fi_path = 'file/130/20230424'


# 创建部门办公室    
m_path = 'file/130/new'
for pn in depart:
    if not os.path.exists(m_path + '/' + pn):
        os.mkdir(m_path + '/' + pn)
fl=os.listdir(fi_path)
for fn in fl:
    if os.path.isfile(fi_path + '/' + fn):
        ofn = int(fn.split('.')[0])
        old.append(ofn) 
old.sort()

for n in old:    
    o_name = f'{fi_path}/{n}.pdf'
    unit = dict2[str(n)]['unit']
    name = dict2[str(n)]['name']
    code = str(n).rjust(5,"0")
    list1.append([unit,code,name])
filename = 'data/高新报告打印明细表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
title = ['部门','测试编号','姓名']
sheet.append(title)
for row in list1:
    sheet.append(row)
    
wb.save(filename)

心理测评计分分析

In [ ]:

import openpyxl
import json
import csv
filename = 'data/高新区.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl) 
cjry = set()
filename = 'data/Survey_20230424-psy.csv'
list2 = []
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)
        phone = str(line[2])
        result = json.loads(line[5])
        
        for k, v in dict1.items():
            if 'phone' in v.keys() and v['phone'] == phone:
                list1 = []
                list1.append(k)
                list1.append(phone)
                list1.append(v['name'])
                list1.append(v['sex'])
                if 'unit' in v.keys():
                    unit = v['unit']
                else:
                    unit = ' '
                list1.append(unit)
                #print(k,v['phone'],type(result))
                for k1, v1 in result.items():
                    list1.append(v1)
        list2.append(list1)
filename = 'data/高新心理评估明细表.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
#sheet.append(title)
for row in list2:
    sheet.append(row)
    
wb.save(filename)