dataSpiderAnalysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# -*- coding: utf8 -*-"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 加载疾病名称和描述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(23658, 7)\n",
      "      Code   Disease SecondCode SecondType FirstCode   FirstType WIKI\n",
      "1  A00.001   古典生物型霍乱    A00-A09      肠道传染病   A00-B99  某些传染病和寄生虫病  NaN\n",
      "2  A00.101  埃尔托生物型霍乱    A00-A09      肠道传染病   A00-B99  某些传染病和寄生虫病  NaN\n",
      "3  A00.901        霍乱    A00-A09      肠道传染病   A00-B99  某些传染病和寄生虫病  NaN\n",
      "4  A00.902      霍乱轻型    A00-A09      肠道传染病   A00-B99  某些传染病和寄生虫病  NaN\n",
      "5  A00.903      霍乱中型    A00-A09      肠道传染病   A00-B99  某些传染病和寄生虫病  NaN\n"
     ]
    }
   ],
   "source": [
    "pd_icd10 = pd.read_excel(\"datasets/ICD-10.xlsx\")\n",
    "print(pd_icd10.shape)\n",
    "print(pd_icd10.head(5))\n",
    "icd10disList = pd_icd10['Disease'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def loadFileDisDes(fileName,columnNameDis,columnNameDes ):\n",
    "    pd_file1 = pd.read_excel(os.path.join(filePath, fileName))\n",
    "    print(pd_file1.columns)\n",
    "    disList = pd_file1[columnNameDis].tolist()\n",
    "    desList = pd_file1[columnNameDes].tolist()\n",
    "    \n",
    "    disAliasNewList = list()\n",
    "    desAliasNewList = list()\n",
    "    \n",
    "    for i in range(len(disList)):\n",
    "        dis = disList[i]\n",
    "        des = desList[i]\n",
    "        disArr = dis.replace(\"、\",\",\").replace(\"，\",\",\").replace(\";\",\",\").split(',')\n",
    "        #print(dis, disArr)\n",
    "        for i in range(len(disArr)):\n",
    "            item = disArr[i]\n",
    "            if len(item.strip())>0:\n",
    "                disAliasNewList.append(item)\n",
    "                desAliasNewList.append(des)\n",
    "    print(len(disList), \"名称拆分后梳理：\",len(disAliasNewList))\n",
    "    return disAliasNewList, desAliasNewList"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 加载三份数据的疾病名称和描述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['序号', '字段2', '字段1_文本', '病因', '预防', '并发症', '症状', '检查', '诊断鉴别', '治疗',\n",
      "       '护理', '饮食保健', '疾病简介', '医保疾病', '患病比例', '易感人群', '传染方式', '并发症.1', '就诊科室',\n",
      "       '治疗方式', '治疗周期', '治愈率', '常用药品', '治疗费用', '温馨提示'],\n",
      "      dtype='object')\n",
      "4714 名称拆分后梳理： 4733\n",
      "Index(['disease_name', 'disease_url', '检查', '趋势', '病因及病理类型', '常见胎盘异常', '鉴别诊断',\n",
      "       '补钙方法', '辨证诊断', '母乳不足',\n",
      "       ...\n",
      "       '辨证要点', '辩证施治', '副银屑病分型', '术前准备', '遗传要点', '面瘫程度的分级', '常见病因', '支原体感染预防',\n",
      "       '脊柱骨折分类', '预防精神分裂症的复发'],\n",
      "      dtype='object', length=373)\n",
      "6462 名称拆分后梳理： 6480\n",
      "Index(['名称', '链接', '类别', '概述', '别名', '是否医保', '常用药物', '症状', '病因', '治疗 ', '并发症'], dtype='object')\n",
      "7795 名称拆分后梳理： 7831\n"
     ]
    }
   ],
   "source": [
    "def loadFiles():\n",
    "    disListALL = list()\n",
    "    desListALL = list()\n",
    "    \n",
    "    disList1, desList1 = loadFileDisDes('disease汇总全部数据.xlsx','字段1_文本', '症状' )\n",
    "    disListALL.extend(disList1)\n",
    "    desList1_ = list()\n",
    "    for des in desList1:\n",
    "        des_ = des.replace('介绍分享到','').replace('\\n','')\n",
    "        desList1_.append(des_)\n",
    "    desListALL.extend(desList1_)\n",
    "    #名医百科\n",
    "    disList2, desList2 = loadFileDisDes('baike疾病content.xlsx', 'disease_name', '检查')\n",
    "    disListALL.extend(disList2)\n",
    "    desListALL.extend(desList2)\n",
    "    #39健康网\n",
    "    disList3, desList3 = loadFileDisDes('jbk39jb.xlsx', '名称', '概述')\n",
    "    disListALL.extend(disList3)\n",
    "    desListALL.extend(desList3)\n",
    "    \n",
    "    return disListALL, desListALL\n",
    "disListALL, desListALL = loadFiles()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "疾病数量： 19044 19044\n"
     ]
    }
   ],
   "source": [
    "#18971\n",
    "print(\"疾病数量：\",len(disListALL), len(desListALL))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def doComputICD(disAliasList):\n",
    "    num=0\n",
    "    for item in disAliasList:\n",
    "        if item in icd10disList:\n",
    "            num +=1\n",
    "    print(num, len(disListALL))\n",
    "\n",
    "    numSet = 0\n",
    "    numList = list()\n",
    "    for item in list(set(disAliasList)):\n",
    "        if item in icd10disList:\n",
    "            numSet +=1\n",
    "            numList.append(item)\n",
    "    print(numSet, len(numList),len(numList)*1.0/23658)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统计当前icd10疾病覆盖 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4747 19044\n",
      "2400 2400 0.1014455997971088\n"
     ]
    }
   ],
   "source": [
    "doComputICD(disListALL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "des        18510\n",
       "disease    18971\n",
       "dtype: int64"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = {'disease': disListALL, 'des': desListALL}\n",
    "df = pd.DataFrame(data=d)\n",
    "df.count()\n",
    "#df.to_csv(\"datasets/diseaseAll.res\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 提取别名，作为疾病实体词典使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19044 19044\n",
      "Index(['名称', '链接', '类别', '概述', '别名', '是否医保', '常用药物', '症状', '病因', '治疗 ', '并发症'], dtype='object')\n",
      "新别名词典数量： 12439\n",
      "新别名在ICD10数量： 12439 12439\n"
     ]
    }
   ],
   "source": [
    "print(len(disListALL),len(desListALL))\n",
    "disAliasNewList = list()\n",
    "desAliasNewList = list()\n",
    "disAliasList_ = list()\n",
    "#39健康网，提取别名\n",
    "pd_file1 = pd.read_excel(os.path.join(filePath, 'jbk39jb.xlsx'))\n",
    "print(pd_file1.columns)\n",
    "disList = pd_file1['别名'].tolist()\n",
    "desList = pd_file1['概述'].tolist()\n",
    "for i in range(len(disList)):\n",
    "        dis = disList[i]\n",
    "        des = desList[i]\n",
    "        disArr = dis.replace(\"、\",\",\").replace(\"，\",\",\").replace(\";\",\",\").split(',')\n",
    "        #print(dis, disArr)\n",
    "        for item in disArr:\n",
    "            if len(item)>0:\n",
    "                disAliasList_.append(item)\n",
    "                disAliasNewList.append(item)\n",
    "                desAliasNewList.append(des)\n",
    "print(\"新别名词典数量：\", len(disAliasList_))\n",
    "print(\"新别名在ICD10数量：\", len(disAliasNewList), len(desAliasNewList))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12439 19044\n",
      "12439 19044\n"
     ]
    }
   ],
   "source": [
    "print(len(disAliasNewList), len(disListALL))\n",
    "print(len(desAliasNewList), len(desListALL))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 结果中的疾病和别名汇总,30432"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31483\n"
     ]
    }
   ],
   "source": [
    "disAliasNewList.extend(disListALL)\n",
    "print(len(disAliasNewList))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31483\n"
     ]
    }
   ],
   "source": [
    "desAliasNewList.extend(desListALL)\n",
    "print(len(desAliasNewList))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5382 19044\n",
      "2726 2726 0.11522529376954942\n"
     ]
    }
   ],
   "source": [
    "doComputICD(disAliasNewList)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 最终疾病，在icd10中的，获取疾病名称和描述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5382 5382\n"
     ]
    }
   ],
   "source": [
    "diNew = list()\n",
    "deNew = list()\n",
    "for i in range(len(disAliasNewList)):\n",
    "    di = disAliasNewList[i]\n",
    "    de = desAliasNewList[i]\n",
    "    \n",
    "    if di in icd10disList:\n",
    "        diNew.append(di)\n",
    "        deNew.append(de)\n",
    "print(len(diNew), len(deNew))\n",
    "d = {'symptom': diNew, 'symptomDes': deNew}\n",
    "df = pd.DataFrame(data=d)\n",
    "df.count()\n",
    "df.to_csv(\"datasets/diseaseMatch.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 补充疾病名称，从症状的相关疾病中，获取疾病名称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['名称', '链接', '类别', '概述', '病因', '相关疾病'], dtype='object')\n",
      "6753 名称拆分后梳理： 6834\n",
      "45142\n"
     ]
    }
   ],
   "source": [
    "aliasList, desList = loadFileDisDes('jbk39zz.xlsx','名称', '相关疾病' )\n",
    "mayDisList = list()\n",
    "for inStr in desList:\n",
    "    inArr = inStr.replace(\"、\",\",\").replace(\"，\",\",\").replace(\";\",\",\").replace(\" \",',').split(r',')\n",
    "    for item in inArr:\n",
    "        if(len(item.strip())>0):\n",
    "            mayDisList.append(item.replace(\"...\",\"\"))\n",
    "print(len(mayDisList))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10997 19044\n",
      "1673 1673 0.07071603685856792\n"
     ]
    }
   ],
   "source": [
    "#考虑相关疾病中，拿症状概述补充\n",
    "doComputICD(mayDisList)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2730 23658\n"
     ]
    }
   ],
   "source": [
    "print(len(set(numList)), len(icd10disList))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len source= 100283\n",
      "len source= 39850\n"
     ]
    }
   ],
   "source": [
    "disAliasNewList.extend(icd10disList)\n",
    "disAliasNewList.extend(mayDisList)\n",
    "print(\"len source=\", len(disAliasNewList))\n",
    "disAliasList__ = list(set(disAliasNewList))\n",
    "print(\"len source=\", len(disAliasList__))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(disAliasList__)\n",
    "df.to_csv(\"datasets/disease.dic\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 载入症状数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['序号', '字段1', '症状名称', '字段3_链接', '字段3_介绍', '字段3_病因', '字段3_预防', '字段3_检查',\n",
      "       '字段3_诊断', '介绍', '病因', '预防', '检查', '诊断'],\n",
      "      dtype='object')\n",
      "6844 名称拆分后梳理： 6962\n",
      "Index(['disease_name', 'disease_url', 'category', '概述', '病因及常见疾病', '病因',\n",
      "       '病因病机', '临床表现', '正常值参考', '检查',\n",
      "       ...\n",
      "       '腹痛的感觉', '临床表现.2', '主病', '日常调摄', '治疗方案', '宫缩压力', '病因和病理', '分型',\n",
      "       'FIGO正常和异常子宫出血相关术语、病因新分类系统', '分期'],\n",
      "      dtype='object', length=180)\n",
      "1341 名称拆分后梳理： 1344\n",
      "Index(['名称', '链接', '类别', '概述', '病因', '相关疾病'], dtype='object')\n",
      "6753 名称拆分后梳理： 6834\n"
     ]
    }
   ],
   "source": [
    "def loadSymptomFiles():\n",
    "    symptomListALL = list()\n",
    "    symptomdesListALL = list()\n",
    "    symList1, desList1 = loadFileDisDes('symptom症状全数据.xlsx','症状名称', '介绍' )\n",
    "    symptomListALL.extend(symList1)\n",
    "    desList1_ = list()\n",
    "    for des in desList1:\n",
    "        des_ = des.replace('介绍分享到','').replace('\\n','')\n",
    "        desList1_.append(des_)\n",
    "    symptomdesListALL.extend(desList1_)\n",
    "    \n",
    "    symList2, desList2 = loadFileDisDes('baike症状content.xls','disease_name', '概述' )\n",
    "    symptomListALL.extend(symList2)\n",
    "    symptomdesListALL.extend(desList2)  \n",
    "    \n",
    "    symList3, desList3 = loadFileDisDes('jbk39zz.xlsx','名称', '概述' )\n",
    "    symptomListALL.extend(symList3)\n",
    "    symptomdesListALL.extend(desList3) \n",
    "    \n",
    "    return symptomListALL, symptomdesListALL\n",
    "symptomListALL, symptomdesListALL = loadSymptomFiles()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 症状里面，多个症状的拆分开，规范化写，写入文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "all= 15140\n"
     ]
    }
   ],
   "source": [
    "symptomListALL_ = list()\n",
    "for inStr in symptomListALL:\n",
    "    inArr = inStr.replace(\"、\",\",\").replace(\"，\",\",\").replace(\";\",\",\").replace(\" \",',').split(r',')\n",
    "    for item in inArr:\n",
    "        if(len(item)>0):\n",
    "            symptomListALL_.append(item.replace(\"...\",\"\"))\n",
    "print(\"all=\",len(symptomListALL_))\n",
    "df_ = pd.DataFrame(symptomListALL_)\n",
    "df_.to_csv(\"datasets/symptom.dic\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "d = {'symptom': symptomListALL, 'symptomDes': symptomdesListALL}\n",
    "df = pd.DataFrame(data=d)\n",
    "df.count()\n",
    "df.to_csv(\"datasets/symptomAll.res\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6330 34184\n"
     ]
    }
   ],
   "source": [
    "num=0\n",
    "tmpList = list()\n",
    "tmpList.extend(symptomListALL)\n",
    "tmpList.extend(disListALL)\n",
    "for item in tmpList:\n",
    "    if item in icd10disList:\n",
    "        num +=1\n",
    "print(num, len(tmpList))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1583 19044\n",
      "937 937 0.03960605292078789\n"
     ]
    }
   ],
   "source": [
    "\n",
    "doComputICD(symptomListALL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1583 1583\n"
     ]
    }
   ],
   "source": [
    "syNew = list()\n",
    "desNew = list()\n",
    "for i in range(len(symptomListALL)):\n",
    "    sym = symptomListALL[i]\n",
    "    des = symptomdesListALL[i]\n",
    "    \n",
    "    if sym in icd10disList:\n",
    "        syNew.append(sym)\n",
    "        desNew.append(des)\n",
    "print(len(syNew), len(desNew))\n",
    "d = {'symptom': syNew, 'symptomDes': desNew}\n",
    "df = pd.DataFrame(data=d)\n",
    "df.count()\n",
    "df.to_csv(\"datasets/symptomMatch.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}