Amazon_Comprehend_Medical_Job_JSON_to_CSV_GitHub.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Convert Comprehend Medical JSON Outputs to CSV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This project converts outputs from Comprehend Medical Analysis Job into Pandas dataframes. There are four types of AWS Comprehend Medical APIs/types of analyses:\n",
    "1. Named entity and relationship\n",
    "2. Protected Health Information\n",
    "3. ICD-10-CM identification and linking\n",
    "4. RxNorm identification and linking\n",
    "\n",
    "To create and run an analysis project in Amazon Comprehend Medical, refer to the guides below.\n",
    "\n",
    "**Reference**\n",
    "\n",
    "General: https://aws.amazon.com/comprehend/medical/\n",
    "\n",
    "Setting up account: https://docs.aws.amazon.com/comprehend/latest/dg/getting-started-med.html\n",
    "\n",
    "Pricing: https://aws.amazon.com/comprehend/pricing/\n",
    "\n",
    "Data Guidelines and Quotas: https://docs.aws.amazon.com/comprehend/latest/dg/guidelines-and-limits-med.html\n",
    "\n",
    "Data protection: https://docs.aws.amazon.com/comprehend/latest/dg/data-protection.html\n",
    "\n",
    "Access Management and permissions: https://docs.aws.amazon.com/comprehend/latest/dg/access-control-managing-permissions-med.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Processing - Entities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load output file from the local server\n",
    "\n",
    "local_cm_folder = 'Comprehend/outputs/medical/' # local Comprehend Medical folder\n",
    "project_folder = 'medical_entities_test/' # local project resutls folder\n",
    "results_filename = 'A Case of 2019 Novel Coronavirus.txt.out' # Output filename\n",
    "\n",
    "local_results_filename = local_cm_folder+project_folder+results_filename\n",
    "\n",
    "# Alternatively, load the results file from GitHub\n",
    "# local_results_filename = 'insert github link here'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records in the output: 98\n"
     ]
    }
   ],
   "source": [
    "# Load one results file\n",
    "\n",
    "results = [json.loads(line) for line in open(local_results_filename, 'r', encoding = 'utf-8')]\n",
    "print('Number of records in the output:',len(results[0]['Entities']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Code that processes JSON into Dataframes**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_traits(results):\n",
    "    traits_df = pd.DataFrame()\n",
    "    for record in results[0]['Entities']:\n",
    "        try: \n",
    "            if len(record['Traits'])>0:\n",
    "                temp_pd = pd.DataFrame(record['Traits'])\n",
    "                temp_pd['EntityId'] = record['Id']\n",
    "                traits_df = traits_df.append(temp_pd)\n",
    "        except:\n",
    "            pass\n",
    "    try: \n",
    "        traits_df = traits_df.set_index('EntityId')\n",
    "    except: \n",
    "        pass\n",
    "    return traits_df  \n",
    "\n",
    "def extract_attributes(results):\n",
    "    attributes_df = pd.DataFrame()\n",
    "    for record in results[0]['Entities']:\n",
    "        try:\n",
    "            if len(record['Attributes'])>0:\n",
    "                temp_pd = pd.DataFrame(record['Attributes'])\n",
    "                temp_pd['EntityId'] = record['Id']\n",
    "                attributes_df = attributes_df.append(temp_pd)\n",
    "        except:\n",
    "            pass\n",
    "    try: \n",
    "        attributes_df = attributes_df.set_index('EntityId')\n",
    "    except: \n",
    "        pass\n",
    "  \n",
    "    return attributes_df\n",
    "\n",
    "def extract_rxnorm(results):\n",
    "    rxnorm_df = pd.DataFrame()\n",
    "    for record in results[0]['Entities']:\n",
    "        try:\n",
    "            if len(record['RxNormConcepts'])>0:\n",
    "                temp_pd = pd.DataFrame(record['RxNormConcepts'])\n",
    "                temp_pd['EntityId'] = record['Id']\n",
    "                rxnorm_df = rxnorm_df.append(temp_pd)\n",
    "        except:\n",
    "            pass\n",
    "        \n",
    "    try: \n",
    "        rxnorm_df = rxnorm_df.set_index('EntityId')\n",
    "    except: \n",
    "        pass    \n",
    "    return rxnorm_df\n",
    "\n",
    "def extract_icd10(results):\n",
    "    icd10_df = pd.DataFrame()\n",
    "    for record in results[0]['Entities']:\n",
    "        try:\n",
    "            if len(record['ICD10CMConcepts'])>0:\n",
    "                temp_pd = pd.DataFrame(record['ICD10CMConcepts'])\n",
    "                temp_pd['EntityId'] = record['Id']\n",
    "                icd10_df = icd10_df.append(temp_pd)\n",
    "        except:\n",
    "            pass\n",
    "    \n",
    "    try: \n",
    "        icd10_df = icd10_df.set_index('EntityId')\n",
    "    except: \n",
    "        pass    \n",
    "    return icd10_df\n",
    "\n",
    "def extract_entities(results):\n",
    "    entities_df = pd.DataFrame()\n",
    "    for entity in results[0]['Entities']:\n",
    "        temp = entity.copy()\n",
    "        temp.pop('Traits', None)\n",
    "        temp.pop('Attributes', None)\n",
    "        temp.pop('RxNormConcepts', None)\n",
    "        temp.pop('ICD10CMConcepts', None)\n",
    "        entities_df = entities_df.append(pd.DataFrame(temp, index = [0]))\n",
    "\n",
    "    try: \n",
    "        entities_df = entities_df.rename(columns = {'Id':'EntityId'}).set_index('EntityId')\n",
    "    except: \n",
    "        pass     \n",
    "    \n",
    "    return entities_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BeginOffset</th>\n",
       "      <th>EndOffset</th>\n",
       "      <th>Score</th>\n",
       "      <th>Text</th>\n",
       "      <th>Category</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>0.974785</td>\n",
       "      <td>2 February 2020</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>22</td>\n",
       "      <td>24</td>\n",
       "      <td>0.972128</td>\n",
       "      <td>28</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>AGE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>59</td>\n",
       "      <td>67</td>\n",
       "      <td>0.990530</td>\n",
       "      <td>pregnant</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>84</td>\n",
       "      <td>89</td>\n",
       "      <td>0.758245</td>\n",
       "      <td>fever</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>84</td>\n",
       "      <td>96</td>\n",
       "      <td>0.150570</td>\n",
       "      <td>fever clinic</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>4308</td>\n",
       "      <td>4314</td>\n",
       "      <td>0.604536</td>\n",
       "      <td>12, 15</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>4365</td>\n",
       "      <td>4376</td>\n",
       "      <td>0.684804</td>\n",
       "      <td>infiltrates</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>4385</td>\n",
       "      <td>4389</td>\n",
       "      <td>0.431774</td>\n",
       "      <td>lung</td>\n",
       "      <td>ANATOMY</td>\n",
       "      <td>SYSTEM_ORGAN_SITE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>4512</td>\n",
       "      <td>4524</td>\n",
       "      <td>0.809839</td>\n",
       "      <td>asymptomatic</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>4571</td>\n",
       "      <td>4581</td>\n",
       "      <td>0.349055</td>\n",
       "      <td>postpartum</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          BeginOffset  EndOffset     Score             Text  \\\n",
       "EntityId                                                      \n",
       "61                  3         18  0.974785  2 February 2020   \n",
       "62                 22         24  0.972128               28   \n",
       "76                 59         67  0.990530         pregnant   \n",
       "77                 84         89  0.758245            fever   \n",
       "63                 84         96  0.150570     fever clinic   \n",
       "...               ...        ...       ...              ...   \n",
       "155              4308       4314  0.604536           12, 15   \n",
       "156              4365       4376  0.684804      infiltrates   \n",
       "162              4385       4389  0.431774             lung   \n",
       "157              4512       4524  0.809839     asymptomatic   \n",
       "158              4571       4581  0.349055       postpartum   \n",
       "\n",
       "                              Category               Type  \n",
       "EntityId                                                   \n",
       "61        PROTECTED_HEALTH_INFORMATION               DATE  \n",
       "62        PROTECTED_HEALTH_INFORMATION                AGE  \n",
       "76                   MEDICAL_CONDITION            DX_NAME  \n",
       "77                   MEDICAL_CONDITION            DX_NAME  \n",
       "63        PROTECTED_HEALTH_INFORMATION            ADDRESS  \n",
       "...                                ...                ...  \n",
       "155       PROTECTED_HEALTH_INFORMATION               DATE  \n",
       "156                  MEDICAL_CONDITION            DX_NAME  \n",
       "162                            ANATOMY  SYSTEM_ORGAN_SITE  \n",
       "157                  MEDICAL_CONDITION            DX_NAME  \n",
       "158                  MEDICAL_CONDITION            DX_NAME  \n",
       "\n",
       "[98 rows x 6 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_entities(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.960524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>SYMPTOM</td>\n",
       "      <td>0.541633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.443185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.704319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.863402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.865270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.791274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.923236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.975147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>DIAGNOSIS</td>\n",
       "      <td>0.583656</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Name     Score\n",
       "EntityId                     \n",
       "76        DIAGNOSIS  0.960524\n",
       "79          SYMPTOM  0.541633\n",
       "79        DIAGNOSIS  0.443185\n",
       "81        DIAGNOSIS  0.704319\n",
       "82        DIAGNOSIS  0.863402\n",
       "83        DIAGNOSIS  0.865270\n",
       "136       DIAGNOSIS  0.791274\n",
       "137       DIAGNOSIS  0.923236\n",
       "138       DIAGNOSIS  0.975147\n",
       "156       DIAGNOSIS  0.583656"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_traits(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save DataFrame to Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export into Excel parameters\n",
    "\n",
    "local_cm_folder = 'Comprehend/outputs/medical/'\n",
    "project_folder = 'medical_entities_test/' #local resutls folder\n",
    "results_filename = 'A Case of 2019 Novel Coronavirus.txt.out'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def json_to_excel(folder, filename):\n",
    "    # Load the results\n",
    "    results = [json.loads(line) for line in open(folder+filename, 'r', encoding = 'utf-8')]\n",
    "    # name and create the processed results folder\n",
    "    processed_folder = 'processed/'\n",
    "    # create output folder if it does not exist\n",
    "    try: \n",
    "        os.mkdir(folder+processed_folder) \n",
    "    except OSError as error: \n",
    "        pass # do nothing\n",
    "\n",
    "    output_filename = folder+processed_folder+filename[:-8]+'.xlsx' # -8 deletes 2 extensions from filename\n",
    "    \n",
    "    # Create a multisheet Excel writer\n",
    "    writer = pd.ExcelWriter(output_filename, engine='xlsxwriter')\n",
    "    \n",
    "    # Save and Excel file with Entities, Traits, and other outputs as tabs\n",
    "    if len(results[0]['Entities'])>0:\n",
    "        extract_entities(results).to_excel(writer, sheet_name = 'Entities', encoding = 'utf-8')\n",
    "        extract_traits(results).to_excel(writer, sheet_name = 'Traits', encoding = 'utf-8')\n",
    "        # These attriputes might not be present in the results\n",
    "        attributes = extract_attributes(results) \n",
    "        rxnorm = extract_rxnorm(results)\n",
    "        icd10 = extract_icd10(results)\n",
    "        if len(rxnorm)>0:\n",
    "            rxnorm.to_excel(writer, sheet_name = 'RxNorm', encoding = 'utf-8')\n",
    "        if len(icd10)>0:\n",
    "            icd10.to_excel(writer, sheet_name = 'ICD10-CM', encoding = 'utf-8')\n",
    "        if len(attributes)>0:\n",
    "            attributes.to_excel(writer, sheet_name = 'Attributes', encoding = 'utf-8')\n",
    "        \n",
    "    writer.save()\n",
    "    \n",
    "    print(len(results[0]['Entities']),'records saved to \\n', output_filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "98 records saved to \n",
      " Comprehend/outputs/medical/medical_entities_test/processed/A Case of 2019 Novel Coronavirus.xlsx\n"
     ]
    }
   ],
   "source": [
    "json_to_excel(local_cm_folder+project_folder,results_filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save DataFrame to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def json_to_csv(folder, filename):\n",
    "    # Load the results\n",
    "    results = [json.loads(line) for line in open(folder+filename, 'r', encoding = 'utf-8')]\n",
    "    # name and create the processed results folder\n",
    "    processed_folder = 'processed_csv/'\n",
    "    # create output folder if it does not exist\n",
    "    try: \n",
    "        os.mkdir(folder+processed_folder) \n",
    "    except OSError as error: \n",
    "        pass # do nothing\n",
    "\n",
    "    output_filename_entities = folder+processed_folder+filename[:-8]+'_entities.csv' # -8 deletes 2 extensions\n",
    "    output_filename_attributes = folder+processed_folder+filename[:-8]+'_attributes.csv'\n",
    "    output_filename_traits = folder+processed_folder+filename[:-8]+'_traits.csv'\n",
    "    output_filename_rxnorm = folder+processed_folder+filename[:-8]+'_RxNorm.csv'\n",
    "    output_filename_icd10 = folder+processed_folder+filename[:-8]+'_ICD-10.csv'\n",
    "\n",
    "    # Create a multisheet Excel writer\n",
    "    # writer = pd.ExcelWriter(output_filename, engine='xlsxwriter')\n",
    "    \n",
    "    # Save and Excel file with Entities, Traits, and other outputs as tabs\n",
    "    if len(results[0]['Entities'])>0:\n",
    "        extract_entities(results).to_csv(output_filename_entities, encoding = 'utf-8')\n",
    "        extract_traits(results).to_csv(output_filename_traits, encoding = 'utf-8')\n",
    "        # These attriputes might not be present in the results\n",
    "        attributes = extract_attributes(results) \n",
    "        rxnorm = extract_rxnorm(results)\n",
    "        icd10 = extract_icd10(results)\n",
    "        if len(rxnorm)>0:\n",
    "            rxnorm.to_csv(output_filename_rxnorm, encoding = 'utf-8')\n",
    "        if len(icd10)>0:\n",
    "            icd10.to_csv(output_filename_icd10, encoding = 'utf-8')\n",
    "        if len(attributes)>0:\n",
    "            attributes.to_csv(output_filename_attributes, encoding = 'utf-8')\n",
    " \n",
    "    print(len(results[0]['Entities']),'records saved to \\n', folder+processed_folder)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "98 records saved to \n",
      " Comprehend/outputs/medical/medical_entities_test/processed_csv/\n"
     ]
    }
   ],
   "source": [
    "json_to_csv(local_cm_folder+project_folder,results_filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run all files in the folder "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/A Case of 2019 Novel Coronavirus.xlsx\n",
      "8 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/A Case Report of an Elderly Woman With Mucous Membrane Pemphigoid.xlsx\n",
      "12 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/Case 17-2007 A 25-Year-Old Woman 0.xlsx\n",
      "26 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/Case 17-2007 A 25-Year-Old Woman 1.xlsx\n",
      "6 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/Case 23-2004 A 50-Year-Old Woman 0.xlsx\n",
      "1 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/Case 23-2004 A 50-Year-Old Woman 1.txt.xlsx\n",
      "1 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/Case 23-2004 A 50-Year-Old Woman 1.xlsx\n",
      "9 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/Case 23-2004 A 50-Year-Old Woman 2txt.xlsx\n"
     ]
    }
   ],
   "source": [
    "local_cm_folder = 'Comprehend/outputs/medical/'\n",
    "project_folder = 'rxnorm_test/' #local resutls folder\n",
    "\n",
    "for file in os.listdir(local_cm_folder+project_folder):\n",
    "    if file[-4:] == '.out': # is a valid AWS output file\n",
    "        json_to_excel(local_cm_folder+project_folder, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### RxNorm Concepts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "project_folder = 'rxnorm_test/' #local analysis resutls folder\n",
    "local_results_filename = local_cm_folder+project_folder+results_filename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records in the output: 9\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Text</th>\n",
       "      <th>Category</th>\n",
       "      <th>Type</th>\n",
       "      <th>Score</th>\n",
       "      <th>BeginOffset</th>\n",
       "      <th>EndOffset</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Arbidol</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>BRAND_NAME</td>\n",
       "      <td>0.655011</td>\n",
       "      <td>2358</td>\n",
       "      <td>2365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Suzhou Pharmaceutical Factory</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>BRAND_NAME</td>\n",
       "      <td>0.223883</td>\n",
       "      <td>2375</td>\n",
       "      <td>2404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>lopinavir</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>GENERIC_NAME</td>\n",
       "      <td>0.974537</td>\n",
       "      <td>2450</td>\n",
       "      <td>2459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ritonavir</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>GENERIC_NAME</td>\n",
       "      <td>0.936402</td>\n",
       "      <td>2464</td>\n",
       "      <td>2473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>cefoperazone sodium</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>GENERIC_NAME</td>\n",
       "      <td>0.995457</td>\n",
       "      <td>2531</td>\n",
       "      <td>2550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>sulbactam sodium</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>GENERIC_NAME</td>\n",
       "      <td>0.984302</td>\n",
       "      <td>2555</td>\n",
       "      <td>2571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>human serum albumin</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>GENERIC_NAME</td>\n",
       "      <td>0.425588</td>\n",
       "      <td>2627</td>\n",
       "      <td>2646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>dexamethasone</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>GENERIC_NAME</td>\n",
       "      <td>0.999287</td>\n",
       "      <td>2708</td>\n",
       "      <td>2721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>magnesium sulfate</td>\n",
       "      <td>MEDICATION</td>\n",
       "      <td>GENERIC_NAME</td>\n",
       "      <td>0.997411</td>\n",
       "      <td>2726</td>\n",
       "      <td>2743</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Text    Category          Type     Score  \\\n",
       "EntityId                                                                      \n",
       "0                               Arbidol  MEDICATION    BRAND_NAME  0.655011   \n",
       "2         Suzhou Pharmaceutical Factory  MEDICATION    BRAND_NAME  0.223883   \n",
       "6                             lopinavir  MEDICATION  GENERIC_NAME  0.974537   \n",
       "7                             ritonavir  MEDICATION  GENERIC_NAME  0.936402   \n",
       "12                  cefoperazone sodium  MEDICATION  GENERIC_NAME  0.995457   \n",
       "13                     sulbactam sodium  MEDICATION  GENERIC_NAME  0.984302   \n",
       "17                  human serum albumin  MEDICATION  GENERIC_NAME  0.425588   \n",
       "21                        dexamethasone  MEDICATION  GENERIC_NAME  0.999287   \n",
       "22                    magnesium sulfate  MEDICATION  GENERIC_NAME  0.997411   \n",
       "\n",
       "          BeginOffset  EndOffset  \n",
       "EntityId                          \n",
       "0                2358       2365  \n",
       "2                2375       2404  \n",
       "6                2450       2459  \n",
       "7                2464       2473  \n",
       "12               2531       2550  \n",
       "13               2555       2571  \n",
       "17               2627       2646  \n",
       "21               2708       2721  \n",
       "22               2726       2743  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load one results file\n",
    "\n",
    "results = [json.loads(line) for line in open(local_results_filename, 'r', encoding = 'utf-8')]\n",
    "print('Number of records in the output:',len(results[0]['Entities']))\n",
    "\n",
    "# Show Entities\n",
    "extract_entities(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Type</th>\n",
       "      <th>Score</th>\n",
       "      <th>RelationshipScore</th>\n",
       "      <th>Id</th>\n",
       "      <th>BeginOffset</th>\n",
       "      <th>EndOffset</th>\n",
       "      <th>Text</th>\n",
       "      <th>Traits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ROUTE_OR_MODE</td>\n",
       "      <td>0.991703</td>\n",
       "      <td>0.644543</td>\n",
       "      <td>4</td>\n",
       "      <td>2427</td>\n",
       "      <td>2433</td>\n",
       "      <td>orally</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>FORM</td>\n",
       "      <td>0.992166</td>\n",
       "      <td>0.896451</td>\n",
       "      <td>8</td>\n",
       "      <td>2474</td>\n",
       "      <td>2481</td>\n",
       "      <td>tablets</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DOSAGE</td>\n",
       "      <td>0.856208</td>\n",
       "      <td>0.989020</td>\n",
       "      <td>9</td>\n",
       "      <td>2483</td>\n",
       "      <td>2494</td>\n",
       "      <td>400/100  mg</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ROUTE_OR_MODE</td>\n",
       "      <td>0.998038</td>\n",
       "      <td>0.992903</td>\n",
       "      <td>10</td>\n",
       "      <td>2508</td>\n",
       "      <td>2514</td>\n",
       "      <td>orally</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>FREQUENCY</td>\n",
       "      <td>0.999190</td>\n",
       "      <td>0.953145</td>\n",
       "      <td>11</td>\n",
       "      <td>2515</td>\n",
       "      <td>2528</td>\n",
       "      <td>every 8 hours</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Type     Score  RelationshipScore  Id  BeginOffset  \\\n",
       "EntityId                                                                \n",
       "6         ROUTE_OR_MODE  0.991703           0.644543   4         2427   \n",
       "6                  FORM  0.992166           0.896451   8         2474   \n",
       "6                DOSAGE  0.856208           0.989020   9         2483   \n",
       "6         ROUTE_OR_MODE  0.998038           0.992903  10         2508   \n",
       "6             FREQUENCY  0.999190           0.953145  11         2515   \n",
       "\n",
       "          EndOffset           Text Traits  \n",
       "EntityId                                   \n",
       "6              2433         orally     []  \n",
       "6              2481        tablets     []  \n",
       "6              2494    400/100  mg     []  \n",
       "6              2514         orally     []  \n",
       "6              2528  every 8 hours     []  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_attributes(results).loc[6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Description</th>\n",
       "      <th>Code</th>\n",
       "      <th>Score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>lopinavir</td>\n",
       "      <td>195088</td>\n",
       "      <td>0.614041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>lopinavir 200 MG / Ritonavir 50 MG Oral Tablet</td>\n",
       "      <td>597730</td>\n",
       "      <td>0.381698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>lopinavir 100 MG / Ritonavir 25 MG Oral Tablet</td>\n",
       "      <td>746645</td>\n",
       "      <td>0.218608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>lopinavir 200 MG / Ritonavir 50 MG Oral Tablet...</td>\n",
       "      <td>847741</td>\n",
       "      <td>0.078047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>lopinavir 100 MG / Ritonavir 25 MG Oral Tablet...</td>\n",
       "      <td>847745</td>\n",
       "      <td>0.046521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Description    Code     Score\n",
       "EntityId                                                                     \n",
       "6                                                 lopinavir  195088  0.614041\n",
       "6            lopinavir 200 MG / Ritonavir 50 MG Oral Tablet  597730  0.381698\n",
       "6            lopinavir 100 MG / Ritonavir 25 MG Oral Tablet  746645  0.218608\n",
       "6         lopinavir 200 MG / Ritonavir 50 MG Oral Tablet...  847741  0.078047\n",
       "6         lopinavir 100 MG / Ritonavir 25 MG Oral Tablet...  847745  0.046521"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_rxnorm(results).loc[6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9 records saved to \n",
      " Comprehend/outputs/medical/rxnorm_test/processed/A Case of 2019 Novel Coronavirus.xlsx\n"
     ]
    }
   ],
   "source": [
    "json_to_excel(local_cm_folder+project_folder, results_filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ICD-10 Concepts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "project_folder = 'icd-10_test/' #local analysis resutls folder\n",
    "local_results_filename = local_cm_folder+project_folder+results_filename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records in the output: 12\n"
     ]
    }
   ],
   "source": [
    "results = [json.loads(line) for line in open(local_results_filename, 'r', encoding = 'utf-8')]\n",
    "print('Number of records in the output:',len(results[0]['Entities']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Text</th>\n",
       "      <th>Category</th>\n",
       "      <th>Type</th>\n",
       "      <th>Score</th>\n",
       "      <th>BeginOffset</th>\n",
       "      <th>EndOffset</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pregnant</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.990530</td>\n",
       "      <td>59</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fever</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.758245</td>\n",
       "      <td>84</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fever</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.993965</td>\n",
       "      <td>144</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>fever</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.904484</td>\n",
       "      <td>322</td>\n",
       "      <td>327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>subpleural patchy consolidation</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.416429</td>\n",
       "      <td>650</td>\n",
       "      <td>681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ground-glass opacities</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.440463</td>\n",
       "      <td>698</td>\n",
       "      <td>720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>preterm birth</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.627147</td>\n",
       "      <td>2285</td>\n",
       "      <td>2298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ground-glass opacities</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.417119</td>\n",
       "      <td>2924</td>\n",
       "      <td>2946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>atypical pneumonia</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.513483</td>\n",
       "      <td>2993</td>\n",
       "      <td>3011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>infiltrates</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.684804</td>\n",
       "      <td>4365</td>\n",
       "      <td>4376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>asymptomatic</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.809839</td>\n",
       "      <td>4512</td>\n",
       "      <td>4524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>postpartum</td>\n",
       "      <td>MEDICAL_CONDITION</td>\n",
       "      <td>DX_NAME</td>\n",
       "      <td>0.349055</td>\n",
       "      <td>4571</td>\n",
       "      <td>4581</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Text           Category     Type  \\\n",
       "EntityId                                                                \n",
       "0                                pregnant  MEDICAL_CONDITION  DX_NAME   \n",
       "1                                   fever  MEDICAL_CONDITION  DX_NAME   \n",
       "3                                   fever  MEDICAL_CONDITION  DX_NAME   \n",
       "5                                   fever  MEDICAL_CONDITION  DX_NAME   \n",
       "9         subpleural patchy consolidation  MEDICAL_CONDITION  DX_NAME   \n",
       "11                 ground-glass opacities  MEDICAL_CONDITION  DX_NAME   \n",
       "12                          preterm birth  MEDICAL_CONDITION  DX_NAME   \n",
       "15                 ground-glass opacities  MEDICAL_CONDITION  DX_NAME   \n",
       "16                     atypical pneumonia  MEDICAL_CONDITION  DX_NAME   \n",
       "26                            infiltrates  MEDICAL_CONDITION  DX_NAME   \n",
       "28                           asymptomatic  MEDICAL_CONDITION  DX_NAME   \n",
       "29                             postpartum  MEDICAL_CONDITION  DX_NAME   \n",
       "\n",
       "             Score  BeginOffset  EndOffset  \n",
       "EntityId                                    \n",
       "0         0.990530           59         67  \n",
       "1         0.758245           84         89  \n",
       "3         0.993965          144        149  \n",
       "5         0.904484          322        327  \n",
       "9         0.416429          650        681  \n",
       "11        0.440463          698        720  \n",
       "12        0.627147         2285       2298  \n",
       "15        0.417119         2924       2946  \n",
       "16        0.513483         2993       3011  \n",
       "26        0.684804         4365       4376  \n",
       "28        0.809839         4512       4524  \n",
       "29        0.349055         4571       4581  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_entities(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Type</th>\n",
       "      <th>Score</th>\n",
       "      <th>RelationshipScore</th>\n",
       "      <th>Id</th>\n",
       "      <th>BeginOffset</th>\n",
       "      <th>EndOffset</th>\n",
       "      <th>Text</th>\n",
       "      <th>Traits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ACUITY</td>\n",
       "      <td>0.728252</td>\n",
       "      <td>0.999872</td>\n",
       "      <td>2</td>\n",
       "      <td>131</td>\n",
       "      <td>143</td>\n",
       "      <td>intermittent</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ACUITY</td>\n",
       "      <td>0.412754</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>154</td>\n",
       "      <td>160</td>\n",
       "      <td>1 week</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SYSTEM_ORGAN_SITE</td>\n",
       "      <td>0.470504</td>\n",
       "      <td>0.405845</td>\n",
       "      <td>7</td>\n",
       "      <td>576</td>\n",
       "      <td>581</td>\n",
       "      <td>Chest</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DIRECTION</td>\n",
       "      <td>0.909073</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>8</td>\n",
       "      <td>639</td>\n",
       "      <td>649</td>\n",
       "      <td>left-sided</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>DIRECTION</td>\n",
       "      <td>0.938604</td>\n",
       "      <td>0.999727</td>\n",
       "      <td>10</td>\n",
       "      <td>686</td>\n",
       "      <td>697</td>\n",
       "      <td>right-sided</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>DIRECTION</td>\n",
       "      <td>0.979477</td>\n",
       "      <td>0.859434</td>\n",
       "      <td>14</td>\n",
       "      <td>2905</td>\n",
       "      <td>2914</td>\n",
       "      <td>bilateral</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>SYSTEM_ORGAN_SITE</td>\n",
       "      <td>0.431774</td>\n",
       "      <td>0.999517</td>\n",
       "      <td>27</td>\n",
       "      <td>4385</td>\n",
       "      <td>4389</td>\n",
       "      <td>lung</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Type     Score  RelationshipScore  Id  BeginOffset  \\\n",
       "EntityId                                                                    \n",
       "3                    ACUITY  0.728252           0.999872   2          131   \n",
       "3                    ACUITY  0.412754           1.000000   4          154   \n",
       "9         SYSTEM_ORGAN_SITE  0.470504           0.405845   7          576   \n",
       "9                 DIRECTION  0.909073           1.000000   8          639   \n",
       "11                DIRECTION  0.938604           0.999727  10          686   \n",
       "15                DIRECTION  0.979477           0.859434  14         2905   \n",
       "26        SYSTEM_ORGAN_SITE  0.431774           0.999517  27         4385   \n",
       "\n",
       "          EndOffset          Text Traits  \n",
       "EntityId                                  \n",
       "3               143  intermittent     []  \n",
       "3               160        1 week     []  \n",
       "9               581         Chest     []  \n",
       "9               649    left-sided     []  \n",
       "11              697   right-sided     []  \n",
       "15             2914     bilateral     []  \n",
       "26             4389          lung     []  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_attributes(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Description</th>\n",
       "      <th>Code</th>\n",
       "      <th>Score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Asymptomatic menopausal state</td>\n",
       "      <td>Z78.0</td>\n",
       "      <td>0.131600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Asymptomatic premature menopause</td>\n",
       "      <td>E28.319</td>\n",
       "      <td>0.110610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Asymptomatic varicose veins of unspecified low...</td>\n",
       "      <td>I83.90</td>\n",
       "      <td>0.103053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Other forms of plague</td>\n",
       "      <td>A20.8</td>\n",
       "      <td>0.097035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Asymptomatic neurosyphilis</td>\n",
       "      <td>A52.2</td>\n",
       "      <td>0.090533</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Description     Code     Score\n",
       "EntityId                                                                      \n",
       "28                            Asymptomatic menopausal state    Z78.0  0.131600\n",
       "28                         Asymptomatic premature menopause  E28.319  0.110610\n",
       "28        Asymptomatic varicose veins of unspecified low...   I83.90  0.103053\n",
       "28                                    Other forms of plague    A20.8  0.097035\n",
       "28                               Asymptomatic neurosyphilis    A52.2  0.090533"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_icd10(results).loc[28]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Protected Health Information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "project_folder = 'phi_test/' #local analysis resutls folder\n",
    "local_results_filename = local_cm_folder+project_folder+results_filename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records in the output: 19\n"
     ]
    }
   ],
   "source": [
    "results = [json.loads(line) for line in open(local_cm_folder+project_folder+results_filename, 'r', encoding = 'utf-8')]\n",
    "entities = results[0][list(results[0].keys())[0]]\n",
    "print('Number of records in the output:',len(entities))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BeginOffset</th>\n",
       "      <th>EndOffset</th>\n",
       "      <th>Score</th>\n",
       "      <th>Text</th>\n",
       "      <th>Category</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EntityId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>0.974785</td>\n",
       "      <td>2 February 2020</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22</td>\n",
       "      <td>24</td>\n",
       "      <td>0.972128</td>\n",
       "      <td>28</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>AGE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>84</td>\n",
       "      <td>96</td>\n",
       "      <td>0.150570</td>\n",
       "      <td>fever clinic</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100</td>\n",
       "      <td>125</td>\n",
       "      <td>0.804315</td>\n",
       "      <td>Suzhou Municipal Hospital</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201</td>\n",
       "      <td>207</td>\n",
       "      <td>0.947584</td>\n",
       "      <td>Suzhou</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>214</td>\n",
       "      <td>221</td>\n",
       "      <td>0.132450</td>\n",
       "      <td>January</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>257</td>\n",
       "      <td>262</td>\n",
       "      <td>0.833486</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>410</td>\n",
       "      <td>424</td>\n",
       "      <td>0.152510</td>\n",
       "      <td>Chinese Center</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>477</td>\n",
       "      <td>501</td>\n",
       "      <td>0.430996</td>\n",
       "      <td>BioGerm, Shanghai, China</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>615</td>\n",
       "      <td>625</td>\n",
       "      <td>0.285187</td>\n",
       "      <td>4 February</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>792</td>\n",
       "      <td>802</td>\n",
       "      <td>0.688616</td>\n",
       "      <td>6 February</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>909</td>\n",
       "      <td>928</td>\n",
       "      <td>0.991988</td>\n",
       "      <td>intensive care unit</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>930</td>\n",
       "      <td>933</td>\n",
       "      <td>0.999127</td>\n",
       "      <td>ICU</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>980</td>\n",
       "      <td>1010</td>\n",
       "      <td>0.259535</td>\n",
       "      <td>Affiliated Infectious Hospital</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1014</td>\n",
       "      <td>1032</td>\n",
       "      <td>0.186861</td>\n",
       "      <td>Soochow University</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2860</td>\n",
       "      <td>2870</td>\n",
       "      <td>0.208116</td>\n",
       "      <td>8 February</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3707</td>\n",
       "      <td>3710</td>\n",
       "      <td>0.988703</td>\n",
       "      <td>ICU</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>ADDRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4087</td>\n",
       "      <td>4098</td>\n",
       "      <td>0.719764</td>\n",
       "      <td>11 February</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4308</td>\n",
       "      <td>4314</td>\n",
       "      <td>0.604536</td>\n",
       "      <td>12, 15</td>\n",
       "      <td>PROTECTED_HEALTH_INFORMATION</td>\n",
       "      <td>DATE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          BeginOffset  EndOffset     Score                            Text  \\\n",
       "EntityId                                                                     \n",
       "0                   3         18  0.974785                 2 February 2020   \n",
       "1                  22         24  0.972128                              28   \n",
       "2                  84         96  0.150570                    fever clinic   \n",
       "3                 100        125  0.804315       Suzhou Municipal Hospital   \n",
       "4                 201        207  0.947584                          Suzhou   \n",
       "5                 214        221  0.132450                         January   \n",
       "6                 257        262  0.833486                           Wuhan   \n",
       "7                 410        424  0.152510                  Chinese Center   \n",
       "8                 477        501  0.430996        BioGerm, Shanghai, China   \n",
       "9                 615        625  0.285187                      4 February   \n",
       "10                792        802  0.688616                      6 February   \n",
       "11                909        928  0.991988             intensive care unit   \n",
       "12                930        933  0.999127                             ICU   \n",
       "13                980       1010  0.259535  Affiliated Infectious Hospital   \n",
       "14               1014       1032  0.186861              Soochow University   \n",
       "15               2860       2870  0.208116                      8 February   \n",
       "16               3707       3710  0.988703                             ICU   \n",
       "17               4087       4098  0.719764                     11 February   \n",
       "18               4308       4314  0.604536                          12, 15   \n",
       "\n",
       "                              Category     Type  \n",
       "EntityId                                         \n",
       "0         PROTECTED_HEALTH_INFORMATION     DATE  \n",
       "1         PROTECTED_HEALTH_INFORMATION      AGE  \n",
       "2         PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "3         PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "4         PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "5         PROTECTED_HEALTH_INFORMATION     DATE  \n",
       "6         PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "7         PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "8         PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "9         PROTECTED_HEALTH_INFORMATION     DATE  \n",
       "10        PROTECTED_HEALTH_INFORMATION     DATE  \n",
       "11        PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "12        PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "13        PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "14        PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "15        PROTECTED_HEALTH_INFORMATION     DATE  \n",
       "16        PROTECTED_HEALTH_INFORMATION  ADDRESS  \n",
       "17        PROTECTED_HEALTH_INFORMATION     DATE  \n",
       "18        PROTECTED_HEALTH_INFORMATION     DATE  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_entities(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_attributes(results)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}