{ "cells": [ { "cell_type": "markdown", "id": "4c74893b-fca2-45f8-8254-2060886909ec", "metadata": {}, "source": [ "## 人员信息导入" ] }, { "cell_type": "code", "execution_count": null, "id": "abde40cd-d8e3-434d-ade2-207cf80cab49", "metadata": { "tags": [] }, "outputs": [], "source": [ "import json\n", "\n", "filename = 'data/data_世纪花园.json'\n", "with open(filename,'r') as fl:\n", " list1 = json.load(fl)\n", "\n", "dict1 = {}\n", "for item in list1:\n", " id = int(item['id'])\n", " dict1.setdefault(str(id),{})\n", " dict1[str(id)]['name'] = item['name']\n", " dict1[str(id)]['sex'] = item['gender']\n", " dict1[str(id)]['birth'] = item['birth']\n", " dict1[str(id)]['phone'] = item['phone']\n", " if item['unit'] == '':\n", " dict1[str(id)]['unit'] = '未确定'\n", " else:\n", " dict1[str(id)]['unit'] = item['unit']\n", "filename = 'data/世纪花园人员信息.json'\n", "with open(filename, 'w') as fl:\n", " json.dump(dict1, fl, ensure_ascii=False)\n", "print('ok')" ] }, { "cell_type": "code", "execution_count": 4, "id": "1a261b8f-6fa8-41d6-8449-9987ca55133f", "metadata": { "execution": { "iopub.execute_input": "2023-11-10T02:31:09.764304Z", "iopub.status.busy": "2023-11-10T02:31:09.763832Z", "iopub.status.idle": "2023-11-10T02:31:09.786184Z", "shell.execute_reply": "2023-11-10T02:31:09.785822Z", "shell.execute_reply.started": "2023-11-10T02:31:09.764268Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "ok\n" ] } ], "source": [ "import openpyxl\n", "import json\n", "\n", "\n", "wb = openpyxl.load_workbook('data/世纪花园人员信息.xlsx')\n", "sheet = wb.active\n", "# sheets = wb.sheetnames\n", "person = {}\n", "\n", "for n in range(1, sheet.max_row+1):\n", " code = int(sheet.cell(n, 1).value)\n", " person.setdefault(code, {})\n", " dict1 = {}\n", " dict1['name'] = sheet.cell(n, 2).value\n", " dict1['unit'] = sheet.cell(n, 6).value\n", " dict1['sex'] = sheet.cell(n, 3).value\n", " dict1['birth'] = str(sheet.cell(n,4).value).split(' ')[0]\n", " if sheet.cell(n,5).value is not None:\n", " dict1['phone'] = str(sheet.cell(n,5).value)\n", " \n", " person[code] = dict1\n", "filename = 'data/世纪花园人员信息.json'\n", "with open(filename, 'w') as fl:\n", " json.dump(person, fl, ensure_ascii=False)\n", "print('ok')" ] }, { "cell_type": "markdown", "id": "691cbb33-47df-4235-8e20-78fb1b5aafc0", "metadata": {}, "source": [ "## 转换人员信息" ] }, { "cell_type": "code", "execution_count": 20, "id": "be91abd1-4f95-4d82-9adb-4a38464a0f0e", "metadata": { "execution": { "iopub.execute_input": "2024-08-11T10:46:44.484824Z", "iopub.status.busy": "2024-08-11T10:46:44.484083Z", "iopub.status.idle": "2024-08-11T10:46:44.496331Z", "shell.execute_reply": "2024-08-11T10:46:44.495225Z", "shell.execute_reply.started": "2024-08-11T10:46:44.484756Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "12\n" ] } ], "source": [ "import json\n", "\n", "filename = 'data/世纪花园基础数据240808.json'\n", "with open(filename,'r') as fl:\n", " dict1 = json.load(fl)\n", "\n", "dict2 = {}\n", "for k, v in dict1.items():\n", " dict1[k]['phone'] = str(k)\n", "\n", "with open(filename,'w') as fl:\n", " json.dump(dict1, fl, ensure_ascii=False) \n", "print(len(dict1))" ] }, { "cell_type": "markdown", "id": "0dc90cf8-0a51-49ab-8f93-81acfd359e63", "metadata": {}, "source": [ "## 获取人员测试成绩" ] }, { "cell_type": "code", "execution_count": null, "id": "693820f4-f79d-4e6b-9901-0aae57bc6c92", "metadata": { "tags": [] }, "outputs": [], "source": [ "import json\n", "import datetime\n", "import csv\n", "from datetime import date\n", "\n", "filename = '../item.json'\n", "item = {}\n", "unit = {}\n", "with open(filename,'r') as fl:\n", " dict1 = json.load(fl) \n", "for k,v in dict1.items():\n", " item[k] = v\n", "item['1']['en'] = 'lung'\n", "item['2']['en'] = 'grip'\n", "item['3']['en'] = 'flexion'\n", "item['4']['en'] = 'jump'\n", "item['5']['en'] = 'pushup'\n", "item['6']['en'] = 'balance'\n", "item['7']['en'] = 'reaction'\n", "item['8']['en'] = 'step'\n", "item['9']['en'] = 'situp'\n", "item['10']['en'] = 'height'\n", "item['11']['en'] = 'weight'\n", "\n", "\n", "re_ta = {}\n", "dict1 = {}\n", "list1 = []\n", "filename = 'data/世纪花园人员信息.json'\n", "with open(filename,'r') as fl:\n", " dict1 = json.load(fl) \n", "\n", "filename = 'data/places_result_20231027.csv'\n", "with open(filename,'r',newline='') as csv_file:\n", " fl = csv.reader(csv_file,delimiter=',')\n", " header = next(fl) \n", " for line in fl:\n", " #line = re.sub('[\\r\\n\\f ]{1,}', '', line)\n", " list1.append(line)\n", "#f_item = ['lung','grip','flexion','jump','balance','reaction','step','situp']\n", "#m_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step']\n", "for result in list1:\n", " user = str(result[2])\n", " rq = date.fromisoformat(result[6].replace('/','-'))\n", " if user in dict1.keys():\n", " l_xm = []\n", " m_item = str(result[3]) \n", " re_ta.setdefault(user,{}) \n", " re_ta[user]['name'] = dict1[user]['name']\n", " re_ta[user]['sex'] = dict1[user]['sex']\n", " if dict1[user]['sex'] == '男':\n", " l_xm = ['weight','height','lung','grip','flexion','jump','pushup','balance','reaction','step']\n", " else:\n", " l_xm = ['weight','height','lung','grip','flexion','jump','balance','reaction','step','situp']\n", " re_ta[user]['unit'] = dict1[user]['unit']\n", " birth = date.fromisoformat(dict1[user]['birth'].replace('/','-'))\n", " #nian = int(birth[0].strip())\n", " #yue = int(birth[1].strip())\n", " #ri = int(birth[2].strip())\n", " #print(k,nian,yue,ri)\n", " item_name = item[m_item]['en'] \n", " if item_name in l_xm: \n", " days = (rq-birth).days \n", " re_ta[user]['age'] = int(days/365)\n", " re_ta[user]['month'] = int(days/365*12)\n", " re_ta[user]['rq'] = result[6]\n", "\n", "\n", " re_ta[user].setdefault(item_name,{}) \n", " score = int(result[4])/item[m_item]['divisor'] \n", " re_ta[user][item_name]['成绩'] = f'{score} {item[m_item][\"unit\"]}'\n", "print(len(re_ta))\n", "filename = 'data/result_世纪花园.json'\n", "\n", "with open(filename,'w') as fl:\n", " json.dump(re_ta, fl, ensure_ascii=False) \n", "print(len(re_ta))" ] }, { "cell_type": "markdown", "id": "2d417d9a-ca85-4c9f-8673-11f5f1033d1f", "metadata": {}, "source": [ "## 计算项目成绩" ] }, { "cell_type": "code", "execution_count": null, "id": "9230c78a-40d1-47ed-9bfc-94525b44f8f9", "metadata": { "tags": [] }, "outputs": [], "source": [ "import json\n", "import time\n", "\n", "filename = '../item.json'\n", "item = {}\n", "unit = {}\n", "with open(filename,'r') as fl:\n", " dict3 = json.load(fl) \n", "for k,v in dict3.items():\n", " item[k] = v\n", "item['1']['en'] = 'lung'\n", "item['2']['en'] = 'grip'\n", "item['3']['en'] = 'flexion'\n", "item['4']['en'] = 'jump'\n", "item['5']['en'] = 'pushup'\n", "item['6']['en'] = 'balance'\n", "item['7']['en'] = 'reaction'\n", "item['8']['en'] = 'step'\n", "item['9']['en'] = 'situp'\n", "item['10']['en'] = 'height'\n", "item['11']['en'] = 'weight'\n", "\n", "filename = 'data/体质检测标准 (1).json' \n", "with open(filename,'r') as fl:\n", " dict1 = json.load(fl)\n", "\n", "\n", "\n", "def cal_score(data1):\n", " #data = {'name':'张三','sex':'M','age':37,'item':'StepExperiment','result':46} \n", " person = dict1['person']\n", " criteria = dict1['criteria']\n", " if data1['age'] >59:\n", " data1['age'] = 59\n", " if data1['age'] <20:\n", " data1['age'] = 20\n", " info = data1['sex']+str(data1['age'])\n", " bz = person[info]\n", " mx = criteria[bz][data1['item']]\n", " result = data1['result'] \n", " if data1['item'] == 'reaction':\n", " for bz1 in mx:\n", " if result > bz1:\n", " #print(bz1)\n", " score = mx.index(bz1,0)\n", " break\n", " else:\n", " score = 5\n", " else:\n", " for bz1 in mx:\n", " if result < bz1:\n", " #print(bz1)\n", " score = mx.index(bz1,0)\n", " break\n", " else:\n", " score = 5\n", " return(score)\n", "filename = 'data/体质检测标准_BMI.json'\n", "with open(filename,'r') as fl:\n", " dict4 = json.load(fl) \n", " \n", "def cal_bmi(data1):\n", " # data = {'name':'张三','sex':'M','age':37,'item':'HeightWeight','result':'177.7,97.0'}\n", " person = dict4['person']\n", " criteria = dict4['criteria']\n", " if data1['age'] > 59:\n", " data1['age'] = 59\n", " if data1['age'] <20:\n", " data1['age'] = 20\n", " info = data1['sex']+str(data1['age'])\n", " bz = person[info]\n", " #print(bz)\n", " result = data1['result']\n", " #print(data1['code'],result)\n", " height = int(float(result.split(',')[0]))\n", " weight = float(result.split(',')[1])\n", " if str(height) not in criteria[bz]:\n", " score = 1\n", " else: \n", " mx = criteria[bz][str(height)]\n", " if weight < mx[0]:\n", " score = 1\n", " elif weight < mx[1]:\n", " score = 3\n", " elif weight < mx[2]:\n", " score = 5 \n", " elif weight <= mx[3]:\n", " score = 3 \n", " elif weight > mx[3]:\n", " score = 1\n", " return score\n", " \n", " \n", "\n", "#list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp','height','weight']\n", "list_item = ['lung','grip','flexion','jump','pushup','balance','reaction','step','situp']\n", "filename = 'data/result_世纪花园.json'\n", "with open(filename,'r') as fl:\n", " dict2 = json.load(fl) \n", "for k, v in dict2.items():\n", " #print(k)\n", " if v['sex'] == '男':\n", " sex = 'M'\n", " else:\n", " sex = 'F' \n", " if 'height' in v.keys() and 'weight' in v.keys():\n", " bmi_data = v['height']['成绩'].split()[0]+','+ v['weight']['成绩'].split()[0]\n", " data1 = {'code':k,'sex':sex,'age':v['age'],'item':'HeightWeight','result':bmi_data}\n", " dict2[k]['bmi'] = {}\n", " dict2[k]['bmi']['成绩'] = bmi_data\n", " dict2[k]['bmi']['score'] = cal_bmi(data1)\n", " for item_en in list_item:\n", " if item_en in v.keys(): \n", " data1 = {'code':k,'sex':sex,'age':v['age'],'item':item_en,'result':float(v[item_en]['成绩'].split()[0])}\n", " dict2[k][item_en]['score'] = cal_score(data1)\n", " #print(k,v[item_en]['成绩'],cal_score(data1))\n", "\n", "filename = f'data/result_世纪花园.json'\n", "with open(filename,'w') as fl:\n", " json.dump(dict2,fl , ensure_ascii=False) \n", "print('ok!') " ] }, { "cell_type": "code", "execution_count": null, "id": "4365fd0d-1e79-48a3-b580-3041a1340815", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "id": "e86acc03-06e2-4eaf-8207-e1ba834869d3", "metadata": {}, "source": [ "## 问卷信息导入" ] }, { "cell_type": "code", "execution_count": null, "id": "c42bb7f4-ae16-4950-8fc9-97e7084e4615", "metadata": { "tags": [] }, "outputs": [], "source": [ "import openpyxl\n", "import json\n", "\n", "filename = 'data/世纪花园人员信息.json'\n", "with open(filename,'r') as fl:\n", " dict2 = json.load(fl)\n", "wb = openpyxl.load_workbook('data/世纪花园问卷.xlsx')\n", "sheet = wb.active\n", "# sheets = wb.sheetnames\n", "dict1 = {}\n", "dict3 = {}\n", "sheet = wb.active\n", "data1 =list(sheet.values)\n", "list_bh = data1[0][1:]\n", "#print(list_bh)\n", "del data1[0]\n", "for i in range(1,len(list_bh)+1):\n", " list2 = []\n", " \n", " for item in data1:\n", " list2.append(item[i])\n", " dict1[list_bh[i-1]] = list2\n", "filename = 'data/result_世纪花园.json'\n", "with open(filename,'r') as fl:\n", " dict2 = json.load(fl)\n", "for k,v in dict1.items():\n", " if str(k) in dict2.keys():\n", " if v[0] is not None:\n", " psy_yangmiao_old = v[:30]\n", " #dict2[str(k)]['psy_yangmiao_old'] = psy_yangmiao_old\n", " if v[30] is not None:\n", " tcm = v[30:90]\n", " #print(tcm,len(tcm))\n", " if v[90] is not None:\n", " spine = v[90:]\n", " xx22 = spine[21]\n", " xx23 = spine[22]\n", " spine[21] = xx23\n", " spine[22] = xx22\n", " \n", " dict2[str(k)]['psy'] = psy_yangmiao_old\n", " dict2[str(k)]['tcm'] = tcm\n", " dict2[str(k)]['spine'] = spine\n", "filename = 'data/result_世纪花园.json'\n", "with open(filename,'w') as fl:\n", " json.dump(dict2, fl, ensure_ascii=False) \n", "print('ok') " ] }, { "cell_type": "markdown", "id": "8ff5f29b-f2cd-4b2f-9716-8ec6bd77db1f", "metadata": {}, "source": [ "## 生成报告人员信息" ] }, { "cell_type": "code", "execution_count": null, "id": "6038af14-e3cf-4e79-9a02-a5d70cb2ed65", "metadata": { "tags": [] }, "outputs": [], "source": [ "import json\n", "import openpyxl\n", "\n", "filename = 'data/世纪花园人员信息.json'\n", "with open(filename,'r') as fl:\n", " dict1 = json.load(fl)\n", "\n", "\n", "list1 = []\n", "for k, v in dict1.items():\n", " id = str(k).rjust(5,\"0\")\n", " \n", " name = v['name']\n", " list1.append([id,name,v['sex'],v['sex'],v['birth'],v['phone'],v['unit']])\n", "filename = 'data/世纪花园人员信息.xlsx'\n", "wb = openpyxl.Workbook()\n", "sheet = wb.active\n", "\n", "for row in list1:\n", " sheet.append(row)\n", "wb.save(filename)" ] }, { "cell_type": "markdown", "id": "e790c586-9099-4a63-a460-a6d53a1e8f99", "metadata": {}, "source": [ "### 导入问卷人员信息（有姓名）" ] }, { "cell_type": "code", "execution_count": null, "id": "096672ef-c1f1-4b31-a490-efd408b95aec", "metadata": { "tags": [] }, "outputs": [], "source": [ "import json\n", "import datetime\n", "import csv\n", "\n", "list1 = []\n", "filename = 'data/Survey_20231027.csv'\n", "with open(filename,'r',newline='') as csv_file:\n", " fl = csv.reader(csv_file,delimiter=',')\n", " header = next(fl) \n", " for line in fl:\n", " #line = re.sub('[\\r\\n\\f ]{1,}', '', line)\n", " list1.append(line)\n", "#print(list1)\n", "list3 = []\n", "for item in list1:\n", " phone = item[2]\n", " content = json.loads(item[5])\n", " name = content['name']\n", " sex = content['gender']\n", " birth = content['birth']\n", " unit = content['unit']\n", " list2 = [phone,name,sex,birth,unit]\n", " list3.append(list2)\n", "filename = 'data/世纪花园人员信息1.xlsx'\n", "wb = openpyxl.Workbook()\n", "sheet = wb.active\n", "\n", "for row in list3:\n", " sheet.append(row)\n", "wb.save(filename) \n", " " ] }, { "cell_type": "code", "execution_count": 16, "id": "563d553e-1629-458b-ad03-4abfeb9fbc7f", "metadata": { "execution": { "iopub.execute_input": "2024-08-07T23:49:52.126604Z", "iopub.status.busy": "2024-08-07T23:49:52.125976Z", "iopub.status.idle": "2024-08-07T23:49:52.148956Z", "shell.execute_reply": "2024-08-07T23:49:52.148516Z", "shell.execute_reply.started": "2024-08-07T23:49:52.126549Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "12\n" ] } ], "source": [ "import json\n", "import datetime\n", "import csv\n", "\n", "list1 = []\n", "filename = 'data/Survey_20240808.csv'\n", "with open(filename,'r',newline='') as csv_file:\n", " fl = csv.reader(csv_file,delimiter=',')\n", " header = next(fl) \n", " for line in fl:\n", " #line = re.sub('[\\r\\n\\f ]{1,}', '', line)\n", " list1.append(line)\n", "#print(list1)\n", "list3 = []\n", "dict1 = {}\n", "bh = 1\n", "for item in list1:\n", " phone = item[1]\n", " content = json.loads(item[5])\n", " \n", " name = content['name']\n", " sex = content['gender']\n", " birth = content['birth']\n", " unit = content['unit']\n", " del content['name']\n", " del content['gender']\n", " del content['birth']\n", " del content['unit'] \n", " for k, v in content.items(): \n", " if 'O' in k:\n", " age = 'O'\n", " psy =[]\n", " for i in range(1,31):\n", " m_key = 'q'+str(i)+'O'\n", " psy.append(content[m_key])\n", " if 'M' in k:\n", " psy =[]\n", " age = 'M'\n", " for i in range(1,48):\n", " m_key = 'q'+str(i)+'M'\n", " psy.append(content[m_key])\n", " if 'qv' in k:\n", " spine =[]\n", " for i in range(1,27):\n", " m_key = 'qv'+str(i)\n", " if m_key in content.keys():\n", " spine.append(content[m_key])\n", " else:\n", " spine.append(0)\n", " if 'v' in k:\n", " tcm = []\n", " for i in range(1,61):\n", " m_key = 'q'+str(i)\n", " if m_key in content.keys():\n", " tcm.append(content[m_key])\n", " else:\n", " tcm.append(0)\n", " dict1.setdefault(phone,{})\n", " \n", " dict1[phone]['name'] = name\n", " dict1[phone]['sex'] = sex\n", " dict1[phone]['birth'] = birth\n", " dict1[phone]['age'] = age\n", " dict1[phone]['unit'] = unit\n", " dict1[phone]['psy'] = psy\n", " dict1[phone]['spine'] = spine\n", " dict1[phone]['tcm'] = tcm\n", " \n", " \n", " #list3.append(list2)\n", "filename = 'data/世纪花园基础数据240808.json'\n", "\n", "with open(filename,'w') as fl:\n", " json.dump(dict1, fl, ensure_ascii=False) \n", "print(len(dict1))" ] }, { "cell_type": "markdown", "id": "5ab7d644-c90a-4877-9abf-6d4bd88ef3ef", "metadata": {}, "source": [ "### 导入问卷人员信息（无姓名）" ] }, { "cell_type": "code", "execution_count": null, "id": "8f801647-a326-4bf1-a651-4b52e0296998", "metadata": { "tags": [] }, "outputs": [], "source": [ "import openpyxl\n", "import json\n", "\n", "filename = 'data/世纪花园人员信息.json'\n", "with open(filename,'r') as fl:\n", " dict1 = json.load(fl)\n", " \n", "list1 = []\n", "list2 = []\n", "for k, v in dict1.items():\n", " list2.append(v['phone'])\n", "print(list2)\n", "\n", "filename = 'data/Survey_20231027.csv'\n", "with open(filename,'r',newline='') as csv_file:\n", " fl = csv.reader(csv_file,delimiter=',')\n", " header = next(fl) \n", " for line in fl:\n", " #line = re.sub('[\\r\\n\\f ]{1,}', '', line)\n", " phone = str(line[2])\n", " if phone in list2:\n", " print(phone)\n" ] }, { "cell_type": "markdown", "id": "3e5b5d43-c883-4e41-80b6-dd80bdbfcbfa", "metadata": {}, "source": [ "## 问卷导出" ] }, { "cell_type": "code", "execution_count": null, "id": "fbe793e4-04cf-4e7d-8fb8-1776ed474ab9", "metadata": { "tags": [] }, "outputs": [], "source": [ "import openpyxl\n", "import json\n", "\n", "filename = 'data/世纪花园人员信息.json'\n", "with open(filename,'r') as fl:\n", " dict3 = json.load(fl)\n", "wb = openpyxl.load_workbook('data/世纪花园问卷.xlsx')\n", "sheet = wb.active\n", "# sheets = wb.sheetnames\n", "dict1 = {}\n", "zy = [2,7,9,11,12,21,24]\n", "no_xinli = [286]\n", "sheet = wb.active\n", "data1 =list(sheet.values)\n", "list_bh = data1[0][1:]\n", "#print(list_bh)\n", "del data1[0]\n", "\n", "for i in range(1,len(list_bh)+1):\n", " list2 = []\n", " \n", " for item in data1:\n", " list2.append(item[i])\n", " dict1[list_bh[i-1]] = list2\n", "#print(dict1)\n", "s = ''\n", "for k,v in dict1.items():\n", " if str(k) in dict3.keys(): \n", " if dict3[str(k)]['sex'] =='男':\n", " sex = \"m\"\n", " else:\n", " sex = \"f\"\n", " dict2 = {}\n", " list_mx = []\n", " list_mx.append(f'\"Gender\":\"{sex}\"')\n", " list_mx.append(f'\"Age\":\"O\"')\n", " dict2['surveyId'] = \"merge1\"\n", " dict2['name'] = dict3[str(k)]['name'] \n", " dict2['code'] = str(k) \n", " name = dict3[str(k)]['phone'] \n", " if k not in no_xinli:\n", " for i in range(0,30):\n", " list_mx.append(f'\"q{i+1}O\":{v[i]}') \n", " \n", " for i in range(1,61):\n", " if i not in zy:\n", " list_mx.append(f'\"q{i}\":{v[29+i]}')\n", " for i in range(1,27):\n", " list_mx.append(f'\"qv{i}\":{v[89+i]}')\n", " ss = ','.join(list_mx)\n", " ss ='{'+ss+'}'\n", " dict2['data'] = ss\n", " sj = '2023-10-27 15:00:00'\n", " s= s+f'(\\'ptv_0533_sjhy\\',\\'{name}\\',\\'{ss}\\',\\'{sj}\\'),'\n", " #print(dict2)\n", "print(s)" ] }, { "cell_type": "code", "execution_count": null, "id": "9a634c3b-6954-4eff-bc1c-3f5e229bc458", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.3" } }, "nbformat": 4, "nbformat_minor": 5 }