Step_6_Term_Harmonize_Impute_Term_Hierarchy (TL4).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Term Harmonize - STEP 6: Impute Hierarchy (TL4)\n",
    "#### Author: Ryan Urbanowicz (ryanurb@upenn.edu) \n",
    "#### Institution: University of Pennsylvania - Perleman School of Medicine\n",
    "#### Project: CMREF Data Harmonization \n",
    "#### Date: 9/1/21\n",
    "\n",
    "#### Project Overview:\n",
    "See the first notebook in this series ('Step_1_Term_Harmonize_Data_Preparation.ipynb') for an overview of this project, these notebooks, the target application, data availability, code dependencies, and our strategy for generalizing the code in these notebooks. \n",
    "\n",
    "#### Notebook Summary:\n",
    "This notebook loads the working mapping file (following completion of PT and HLT mapping (which we assume has been completed fully). Next it performs the next level of term hierarchy imputation.  In the current target application this involves imputing the HLGTs from HLTs. However HLGT imputation includes possible branching, therefore further manual, subjective annotation will be required after running this notebook. Whenever the data includes relevant term data, we will apply fuzzy matching to assist in the selection of the most appropriate of available HGLT branches. When this relevant term data is not available we pick the first of the possible branches by default.  Branch 'quality' is tracked much like term mapping quality. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "## Load Python packages required in this notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Load necessary packages.\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from fuzzywuzzy import fuzz\n",
    "from fuzzywuzzy import process\n",
    "\n",
    "# Jupyter Notebook Hack: This code ensures that the results of multiple commands within a given cell are all displayed, rather than just the last. \n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "#Import Progress bar\n",
    "from tqdm import tnrange, tqdm_notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "## Load Working Map File and Relevent Ontology Files\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create general variable names for any target application specific values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input filename for 'target dataset' (excel file loaded in this application)\n",
    "target_study_data = 'Combined_MEDHX_TERMS_20studies.xlsx' \n",
    "\n",
    "ont_DL1_data = 'LLT.xlsx' # Input filename for ontology file defining all DL1 terms and their codes. \n",
    "ont_DL1_name_col = 'llt_name' # column label for DL1 term name\n",
    "ont_DL1_code_col ='llt_code' # column label for DL1 term code\n",
    "ont_DL1_cur_col = 'llt_currency' # column label for term currency\n",
    "ont_DL2_data = 'PT.xlsx' # Input filename for ontology file defining all DL2 terms and their codes. \n",
    "ont_DL2_name_col = 'pt_name' # column label for DL2 term name\n",
    "ont_DL2_code_col = 'pt_code' # column label for DL2 term code\n",
    "ont_DL3_DL2_data = 'HLT_PT.xlsx' # Input filename for ontology file defining connections between DL2 and DL3 term codes. \n",
    "ont_DL3_data = 'HLT.xlsx' # Input filename for ontology file defining all DL3 terms and their codes.\n",
    "ont_DL3_name_col = 'hlt_name' # column label for DL3 term name\n",
    "ont_DL3_code_col = 'hlt_code' # column label for DL3 term code\n",
    "ont_DL4_name_col = 'hlgt_name' # column label for DL4 term name\n",
    "ont_DL4_code_col = 'hlgt_code' # column label for DL4 term code\n",
    "\n",
    "ont_DL4_DL3_data = 'HLGT_HLT.xlsx' # Input filename for ontology file defining connections between DL3 and DL4 term codes. \n",
    "ont_DL4_data = 'HLGT.xlsx' # Input filename for ontology file defining all DL4 terms and their codes.\n",
    "ont_DL5_DL4_data = 'SOC_HLGT.xlsx' # Input filename for ontology file defining connections between DL4 and DL5 term codes. \n",
    "ont_DL5_data = 'SOC.xlsx' # Input filename for ontology file defining all DL5 terms and their codes.\n",
    "\n",
    "DL1_FT1 = 'MHTERM' # focus term 1: This term is available over all studies. \n",
    "DL1_FT2 = 'LLT_NAME' # focus term 3: an alternative term available for a subset of studies. This one supposedly conforms to the MedDRA standard so we expect it to yield more exact matches. May offer a better match for the lowest level of the standardized terminology.\n",
    "DL1_FT3 = 'MHMODIFY' # focus term 2: an alternative term available for a subset of studies. May offer a better match for the lowest level of the standardized terminology.\n",
    "\n",
    "DL2 = 'PT_NAME' # Secondary level terms (i.e. more general than DL1 terms)\n",
    "DL3 = 'HLT_NAME' # Tertiary level terms (i.e. more general than DL2 terms)\n",
    "DL4 = 'HLGT_NAME' # Quarternary level terms (i.e. more general than DL3 terms)\n",
    "DL5 = 'SOC_NAME' # Quinary Level terms (i.e. more general than DL4 terms)\n",
    "\n",
    "TL1_qual_code_header = 'LLT_map_code' # column name for lowest term level mapping quality code (added to mapping file)\n",
    "TL1_name_header = 'T_LLT' # column name for the 'mapped' TL1 - term name (added to mapping file)\n",
    "TL1_code_header = 'T_LLT_CODE' # column name for the 'mapped' TL1 - term code (added to mapping file)\n",
    "TL2_name_header = 'T_PT'\n",
    "TL2_code_header = 'T_PT_CODE'\n",
    "TL3_name_header = 'T_HLT'\n",
    "TL3_code_header = 'T_HLT_CODE'\n",
    "TL4_name_header = 'T_HLGT'\n",
    "TL4_code_header = 'T_HLGT_CODE'\n",
    "TL5_name_header = 'T_SOC'\n",
    "TL5_code_header = 'T_SOC_CODE'\n",
    "\n",
    "FZ1_FT1 = 'FZMatch_1_'+DL1_FT1 # column name for best FT1 fuzzy match (temporarily added to mapping file)\n",
    "FZ2_FT1 = 'FZMatch_2_'+DL1_FT1 # column name for second best FT1 fuzzy match (temporarily added to mapping file)\n",
    "FZ3_FT1 = 'FZMatch_3_'+DL1_FT1 # column name for third best FT1 fuzzy match (temporarily added to mapping file)\n",
    "FZ4_FT1 = 'FZMatch_4_'+DL1_FT1 # column name for fourth best FT1 fuzzy match (temporarily added to mapping file)\n",
    "FZ5_FT1 = 'FZMatch_5_'+DL1_FT1 # column name for fifth best FT1 fuzzy match (temporarily added to mapping file)\n",
    "\n",
    "FZMC = 'FZMatch_Choice_ID_'+DL1_FT1 #column name for the column where manual annotator will enter the number (1-5) indicating the FT1 fuzzy matched term that offers the best match (if a good one is identified)\n",
    "FZCT = 'FZMatch_Copied_Term' #column name for the column where manual annotator can alternatively manually copy in the MedDRA LLT term that best matches the term information in this row (can come from FT2 or FT3 if term was not identified in FT1)\n",
    "\n",
    "FZ1_FT2 = 'FZMatch_1_'+DL1_FT2 # column name for best FT2 fuzzy match (temporarily added to mapping file)\n",
    "FZ2_FT2 = 'FZMatch_2_'+DL1_FT2 # column name for second best FT2 fuzzy match (temporarily added to mapping file)\n",
    "FZ3_FT2 = 'FZMatch_3_'+DL1_FT2 # column name for third best FT2 fuzzy match (temporarily added to mapping file)\n",
    "FZ4_FT2 = 'FZMatch_4_'+DL1_FT2 # column name for forth best FT2 fuzzy match (temporarily added to mapping file)\n",
    "FZ5_FT2 = 'FZMatch_5_'+DL1_FT2 # column name for fifth best FT2 fuzzy match (temporarily added to mapping file)\n",
    "\n",
    "FZ1_FT3 = 'FZMatch_1_'+DL1_FT3 # column name for best FT3 fuzzy match (temporarily added to mapping file)\n",
    "FZ2_FT3 = 'FZMatch_2_'+DL1_FT3 # column name for second best FT3 fuzzy match (temporarily added to mapping file)\n",
    "FZ3_FT3 = 'FZMatch_3_'+DL1_FT3 # column name for third best FT3 fuzzy match (temporarily added to mapping file)\n",
    "FZ4_FT3 = 'FZMatch_4_'+DL1_FT3 # column name for forth best FT3 fuzzy match (temporarily added to mapping file)\n",
    "FZ5_FT3 = 'FZMatch_5_'+DL1_FT3 # column name for fifth best FT3 fuzzy match (temporarily added to mapping file)\n",
    "\n",
    "TL3_B = 'HLT_branches'\n",
    "TL3_BQ = 'HLT_branch_quality'\n",
    "TL3_FZT = 'HLT_Fuzzy_Terms'\n",
    "TL3_FZS = 'HLT_Fuzzy_Scores'\n",
    "\n",
    "TL4_B = 'HLGT_branches'\n",
    "TL4_BQ = 'HLGT_branch_quality'\n",
    "TL4_FZT = 'HLGT_Fuzzy_Terms'\n",
    "TL4_FZS = 'HLGT_Fuzzy_Scores'\n",
    "\n",
    "TL5_B = 'SOC_branches'\n",
    "TL5_BQ = 'SOC_branch_quality'\n",
    "TL5_FZT = 'SOC_Fuzzy_Terms'\n",
    "TL5_FZS = 'SOC_Fuzzy_Scores'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load map File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(28720, 21)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Load target (tab-delimited) file into a pandas data frame\n",
    "target_map_file = 'MH_harmonization_map_15_TL3.csv' #Input filename (excel file loaded in this application)\n",
    "td = pd.read_csv(target_map_file, na_values=' ') #Data loaded so that blank excell cells are 'NA'\n",
    "td.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load 4th Level Terminology Standard File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(337, 9)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tl4 = pd.read_excel(ont_DL4_data, sep='\\t',na_values=' ')\n",
    "tl4.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load 3th to 4th Level Terminology Connection File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1755, 2)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tl4_tl3 = pd.read_excel(ont_DL4_DL3_data, sep='\\t',na_values=' ')\n",
    "tl4_tl3.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "## Insert New Columns for Hierarchy Imputation\n",
    "Insert column into the mapping file needed for the fourth level of the hierarchy imputation (i.e. HLGT). For level 4 (i.e. HLGT) we will again add columns to handle the branch possibilities, the quality of our branch selection, and results from any fuzzy branch matching. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*To adapt this code to other tasks, users may need to specify different column indexes below. We place these new columns after the original data columns.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(28720, 27)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.insert(loc=21,column=TL4_name_header,value='NA') \n",
    "td.insert(loc=22,column=TL4_code_header,value='NA') \n",
    "\n",
    "td.insert(loc=23,column=TL4_B,value='NA') \n",
    "td.insert(loc=24,column=TL4_BQ,value='NA') \n",
    "td.insert(loc=25,column=TL4_FZT,value='NA') \n",
    "td.insert(loc=26,column=TL4_FZS,value='NA') \n",
    "td.shape\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define a method that takes a pandas column and turns it into a list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def listify(inList):\n",
    "    nameList = []\n",
    "    scoreList = []\n",
    "    for each in inList:\n",
    "        nameList.append(each[0])\n",
    "        scoreList.append(each[1])\n",
    "    return nameList, scoreList"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "## Impute TL4 from TL3 (Branches Possible)\n",
    "In this application we impute HLGTs (i.e. TL4) from previously imputed HLTs (TL3). \n",
    "\n",
    "Before completing this task we lay out a coding scheme to describe the quality/confidence of any branch selection for this entire imputation proceedure. These codes for TL4 will be entered into the column with the 'TL4_BQ' label. We have developed a custom coding scheme to suit the needs of our target application:\n",
    "\n",
    "* 0 = No branching: Only one possible term available for imputation - best quality implied. \n",
    "* 1 = Branching: Branch selected based on an exact match with DL3 available term. \n",
    "* 2 = Branching: DL3 term available, fuzzy matching applied, top scoring fuzzy match chosen/confirmed. \n",
    "* 3 = Branching: DL3 term available, fuzzy matching applied, non-top scoring fuzzy match chosen/confirmed.\n",
    "* 4 = Branching: no DL3 term available - No fuzzy matching available. Picked first branch by default. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ec69809340ed45e596c2a235e3a86df4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='1st loop', max=28720), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:32: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:33: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:34: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Directly Imputed: 28324\n",
      "With Branches: 366\n"
     ]
    }
   ],
   "source": [
    "data_count = 0\n",
    "total_branched = 0\n",
    "total_direct = 0\n",
    "\n",
    "for each in tqdm_notebook(td[TL3_code_header], desc='1st loop'): #for each row\n",
    "    if not pd.isna(each): #Check for missing value\n",
    "\n",
    "        #Idenfity the TL3 code in the MedDRA HLGT_HLT file\n",
    "        tempList = tl4_tl3[ont_DL3_code_col].tolist() # the column name reference is specific to the loaded MedDRA file\n",
    "        indexList = [i for i,val in enumerate(tempList) if val==int(float(each))]\n",
    " \n",
    "        #Put all connected TL4 codes in TL4_B separated by an underscore\n",
    "        TL4_list = []\n",
    "        TL4_str = ''\n",
    "        for i in indexList:\n",
    "            TL4_list.append(tl4_tl3[ont_DL4_code_col][i]) # the column name reference is specific to the loaded MedDRA file\n",
    "            TL4_str += str(tl4_tl3[ont_DL4_code_col][i])+'_' # the column name reference is specific to the loaded MedDRA file\n",
    "        \n",
    "        #Branch Reporting\n",
    "        if len(TL4_list) > 1: # is there more than one branch \n",
    "            total_branched += 1\n",
    "            td[TL4_B][data_count] = TL4_str\n",
    "\n",
    "        else: #only one branch found\n",
    "            total_direct += 1\n",
    "            #Identify term for single code\n",
    "            tempList = tl4[ont_DL4_code_col].tolist() # the column name reference is specific to the loaded MedDRA file\n",
    "\n",
    "            tempIndex = tempList.index(TL4_list[0]) #find index/location of code in tl4 set of terms\n",
    "            term = tl4[ont_DL4_name_col][tempIndex] # the column name reference is specific to the loaded MedDRA file\n",
    "\n",
    "            td[TL4_name_header][data_count] = term\n",
    "            td[TL4_code_header][data_count] = TL4_list[0]\n",
    "            td[TL4_BQ][data_count] = 0 # Top Branch Quality\n",
    "\n",
    "    data_count +=1 \n",
    "\n",
    "print(\"Directly Imputed: \" +str(total_direct))\n",
    "print(\"With Branches: \" +str(total_branched))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save file in progress"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "td.to_csv(\"MH_harmonization_map_16_TL4.csv\", header=True, index=False)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exact Matching of TL4 Branches\n",
    "This process will focus on TL4's with branches, as well as available DL4 terms to allow for exact matching. For any row without DL4 data, but branching we will pick the first branch by default and enter at TL4_BQ of 4 (lowest quality branch resolution)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(28720, 27)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5224f6c8512646e2820a96dc89afc6aa",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='1st loop', max=28720), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:46: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:47: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:35: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:36: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:37: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Branches Resolved with Exact Match: 237\n",
      "Branches with Data but No Exact Match: 3\n",
      "Branches with no Data: 126\n"
     ]
    }
   ],
   "source": [
    "td = pd.read_csv('MH_harmonization_map_16_TL4.csv', na_values=' ')\n",
    "td.shape\n",
    "\n",
    "data_count = 0\n",
    "total_exact_matched = 0\n",
    "total_data_no_match = 0\n",
    "total_no_data = 0\n",
    "\n",
    "for each in tqdm_notebook(td[TL4_B], desc='1st loop'): #for each row\n",
    "    if not pd.isna(td[TL1_name_header][data_count]): #Check for missing value\n",
    "        if td[TL4_BQ][data_count] != 0: # Focus on terms with multiple branches\n",
    "\n",
    "            #Create list of branch codes\n",
    "            codeList = each.strip('_').split('_')\n",
    "            #Get MedDRA terms for each code in branch list - put in a list of terms\n",
    "            textList = [] #text for corresponding branch terms\n",
    "            tempList = tl4[ont_DL4_code_col].tolist() # the column name reference is specific to the loaded MedDRA file\n",
    "\n",
    "            for code in codeList:\n",
    "                #Idenfity the TL4 code in the MedDRA HLGT file\n",
    "                tempIndex = tempList.index(int(code))\n",
    "\n",
    "                #Put the corresponding TL5 term into the list\n",
    "                term = tl4[ont_DL4_name_col][tempIndex] # the column name reference is specific to the loaded MedDRA file\n",
    "                textList.append(str(term))\n",
    "\n",
    "            #Exact Matching\n",
    "            if not pd.isna(td[DL4][data_count]): #See if row has original DL4 data available.\n",
    "                #Check for branch exact match\n",
    "                term_count = 0\n",
    "                matched = False\n",
    "                for term in textList: #each branch term\n",
    "                    if str(term).casefold() == str(td[DL4][data_count]).casefold(): \n",
    "                        td[TL4_name_header][data_count] = term # Map matching term\n",
    "                        td[TL4_code_header][data_count] = codeList[term_count] # Map matching term code, the column name reference is specific to the loaded MedDRA LLT file\n",
    "                        td[TL4_BQ][data_count] = 1 # Exact Match Branch Quality\n",
    "                        total_exact_matched += 1\n",
    "                        matched = True\n",
    "                    term_count +=1\n",
    "                if not matched:\n",
    "                    total_data_no_match += 1\n",
    "\n",
    "            else: # no DL4 data\n",
    "                #Pick first branch by default.\n",
    "                td[TL4_name_header][data_count] = textList[0] # Map matching term\n",
    "                td[TL4_code_header][data_count] = codeList[0] # Map matching term code, the column name reference is specific to the loaded MedDRA LLT file\n",
    "                td[TL4_BQ][data_count] = 4 # First branch picked by default - lowest quality branch resolution code. \n",
    "                total_no_data += 1\n",
    "        \n",
    "    data_count +=1 \n",
    "\n",
    "print(\"Branches Resolved with Exact Match: \" +str(total_exact_matched))\n",
    "print(\"Branches with Data but No Exact Match: \" +str(total_data_no_match))  \n",
    "print(\"Branches with no Data: \" +str(total_no_data))  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save file in progress"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "td.to_csv(\"MH_harmonization_map_17_TL4.csv\", header=True, index=False)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fuzzy Matching of TL4 Branches\n",
    "This process will focus on TL4's with branches, unresolved by exact matching, but have DL4 terms to allow for fuzzy matching."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(28720, 27)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d83706068c2d4f218522c0261813301d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='1st loop', max=28720), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:32: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "C:\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:189: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self._setitem_with_indexer(indexer, value)\n",
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:33: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Branches with Applied Fuzzy Matching: 3\n",
      "Branches with no Data: 28687\n"
     ]
    }
   ],
   "source": [
    "td = pd.read_csv('MH_harmonization_map_17_TL4.csv', na_values=' ')\n",
    "td.shape\n",
    "\n",
    "data_count = 0\n",
    "total_fuzzy_matched = 0\n",
    "total_no_data = 0\n",
    "\n",
    "for each in tqdm_notebook(td[TL4_B], desc='1st loop'): #for each row\n",
    "    if not pd.isna(td[TL1_name_header][data_count]): #Check for missing value\n",
    "        if pd.isna(td[TL4_BQ][data_count]): #haven't previously assigned branch quality a code (i.e. of 0, 1, or 4) (i.e. multiple branches, no exact match, and DL3 available)\n",
    "\n",
    "            #Create list of branch codes\n",
    "            codeList = each.strip('_').split('_')\n",
    "            #Get MedDRA terms for each code in branch list - put in a list of terms\n",
    "            textList = [] #text for corresponding branch terms\n",
    "            tempList = tl4[ont_DL4_code_col].tolist() # the column name reference is specific to the loaded MedDRA file\n",
    "\n",
    "            for code in codeList:\n",
    "                #Idenfity the TL4 code in the MedDRA HLGT file\n",
    "                tempIndex = tempList.index(int(code))\n",
    "\n",
    "                #Put the corresponding TL4 term into the list\n",
    "                term = tl4[ont_DL4_name_col][tempIndex] # the column name reference is specific to the loaded MedDRA file\n",
    "                textList.append(str(term))\n",
    "\n",
    "            #Perform fuzzy matching between row's HLT and term list\n",
    "            bestFound = process.extract(td[DL4][data_count],textList)\n",
    "            nameList,scoreList = listify(bestFound)\n",
    "\n",
    "            # Add results of fuzzy matching\n",
    "            td[TL4_FZT][data_count] = str(nameList)\n",
    "            td[TL4_FZS][data_count] = str(scoreList)  \n",
    "\n",
    "            total_fuzzy_matched += 1\n",
    "        else:\n",
    "            total_no_data += 1\n",
    "            \n",
    "    data_count += 1\n",
    "print(\"Branches with Applied Fuzzy Matching: \" +str(total_fuzzy_matched))\n",
    "print(\"Branches with no Data: \" +str(total_no_data))  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "## Save working map file\n",
    "Before moving on to the next step of the harmonization pipeline we will save our mapping file 'in progress'. Since a row index was previously added, we will set index to 'False' below.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "td.to_csv(\"MH_harmonization_map_18_TL4.csv\", header=True, index=False)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add codes for branch terms resolved with fuzzy matching\n",
    "We examined the data and only 3 terms were resolved here so we simply confirmed and entered the best branch terms and looked up the codes in TL4 of MedDRA and manually entered their codes, saving this file as \"MH_harmonization_map_18_TL4_Fuzzy.csv\". This process could be automated like it was in the previous notebook as needed for a different application. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notebook conclusion\n",
    "In this notebook we have mapped all TL4 names and codes, this time resolving any possible term branches using available term information in the dataset.  This was completed with exact and fuzzy matching similar to the first phase of TL1 mapping. \n",
    "\n",
    "In the next notebook we replicate the same process we just completed for TL4, but for TL5. "
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}