annotate clsi_profile_type2_linux.ipynb @ 0:c1a77856070c draft

"planemo upload for repository https://github.com/rakesh4osdd/asist/tree/master commit f5b374bef15145c893ffdd3a7d2f2978d8052184-dirty"

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "9aa0a6f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ASIST module2 | map AST result to the CLSI breakporints with combination antibiotics\n",
    "# By [e-mail], 06-Jun-2021\n",
    "import pandas as pd\n",
    "import re\n",
    "import sys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9af8387e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.2.4 2.2.1\n"
     ]
    }
   ],
   "source": [
    "#print(pd.__version__, re.__version__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "73d0783c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# compare two MIC value strings\n",
    "def check_mic(mic1,mic2,mic_type):\n",
    "    #print(mic1,mic2,mic_type)\n",
    "    try:\n",
    "        if '/' in mic1:\n",
    "            m1a = mic1.split('/')[0]\n",
    "            m1b = mic1.split('/')[1]\n",
    "            if float(m1a)==0 or float(m1b)==0:\n",
    "                strain_type='Strain could not be classified'\n",
    "                return(strain_type)          \n",
    "        elif '/' in mic2:\n",
    "            m1a = mic1\n",
    "            if float(m1a)==0:\n",
    "                strain_type='Strain could not be classified'\n",
    "                return(strain_type)            \n",
    "            m1b = '1'\n",
    "        elif float(mic1)==0:\n",
    "            strain_type='Strain could not be classified'\n",
    "            return(strain_type)\n",
    "        else:\n",
    "            m1a = mic1\n",
    "            \n",
    "        if '-' in mic2:\n",
    "            m2a = mic2.split('-')[0]\n",
    "            m2b = mic2.split('-')[1]           \n",
    "         \n",
    "    except ValueError:\n",
    "        strain_type='Strain could not be classified' \n",
    "        return(strain_type)\n",
    "    try:\n",
    "        if '-' in mic2 and mic_type == 'i':   # for intermediate only\n",
    "            if '/' in mic2:\n",
    "                m2a = mic2.split('-')[0].split('/')[0]\n",
    "                m2b = mic2.split('-')[0].split('/')[1]\n",
    "                m2aa = mic2.split('-')[1].split('/')[0]\n",
    "                m2bb = mic2.split('-')[1].split('/')[1]\n",
    "                if (float(m2aa)>=float(m1a)>=float(m2a) and float(m2bb)>=float(m1b)>=float(m2b)):\n",
    "                    #print('intermediate')\n",
    "                    m_type='Intermediate'\n",
    "                else:\n",
    "                    #print('not define')\n",
    "                    m_type='Strain could not be classified'\n",
    "            else:\n",
    "                m2a = mic2.split('-')[0]\n",
    "                m2b = mic2.split('-')[1] \n",
    "                if (float(m2b)>=float(m1a)>=float(m2a)):\n",
    "                    #print('intermediate')\n",
    "                    m_type='Intermediate'\n",
    "                else:\n",
    "                    #print('not define')\n",
    "                    m_type='Strain could not be classified'                \n",
    "            #print (m1a,m1b,m2a,m2b,m2aa,m2bb)\n",
    "        elif '/' in mic2:\n",
    "            m2a = mic2.split('/')[0]\n",
    "            m2b = mic2.split('/')[1]\n",
    "            #print(m1a,m1b,m2a,m2b,mic_type)\n",
    "            if (mic_type=='s' and (float(m1a)<=float(m2a) and float(m1b)<=float(m2b))):\n",
    "                m_type='Susceptible'\n",
    "            elif (mic_type=='r' and (float(m1a)>=float(m2a) and float(m1b)>=float(m2b))):\n",
    "                m_type='Resistant'\n",
    "            elif (mic_type=='i' and (float(m1a)==float(m2a) and float(m1b)==float(m2b))):\n",
    "                m_type='Intermediate'\n",
    "            else:\n",
    "                m_type='Strain could not be classified'\n",
    "        elif '-' in mic2:\n",
    "                m_type='Strain could not be classified'\n",
    "        else:\n",
    "            m2a=mic2\n",
    "            if (mic_type=='s' and (float(m1a)<=float(m2a))):\n",
    "                m_type='Susceptible'\n",
    "            elif (mic_type=='r' and (float(m1a)>=float(m2a))):\n",
    "                m_type='Resistant'\n",
    "            elif (mic_type=='i' and (float(m1a)==float(m2a))):\n",
    "                m_type='Intermediate'\n",
    "            else:\n",
    "                m_type='Strain could not be classified-1'        \n",
    "    except IndexError:\n",
    "        strain_type='Strain could not be classified-2' \n",
    "        return(strain_type)\n",
    "    \n",
    "    return(m_type)\n",
    "\n",
    "#check_mic('65','32-64','i')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "4d2ab1b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# compare MIC value in pandas list\n",
    "def sus_res_int(mic):\n",
    "    #print(mic)\n",
    "    o_mic = mic[0].replace(' ', '')\n",
    "    s_mic = mic[1].replace(' ', '')\n",
    "    r_mic = mic[2].replace(' ', '')\n",
    "    i_mic = mic[3].replace(' ', '')\n",
    "    try:\n",
    "        if check_mic(o_mic,s_mic,'s')=='Susceptible':\n",
    "            strain_type='Susceptible'\n",
    "        elif check_mic(o_mic,r_mic,'r')=='Resistant':\n",
    "            strain_type='Resistant'\n",
    "        elif check_mic(o_mic,i_mic,'i')=='Intermediate':\n",
    "            strain_type='Intermediate'                    \n",
    "        else:\n",
    "            strain_type='Strain could not be classified'\n",
    "    except ValueError:\n",
    "        strain_type='Strain could not be classified'            \n",
    "    return(strain_type)\n",
    "\n",
    "#mic=['128','16/4','128/4','32/4-64/4']\n",
    "#sus_res_int(mic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "0e22ef0d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'# for input argument\\ninput_user = sys.argv[1]\\ninput_clsi = sys.argv[2]\\noutput_table = sys.argv[3]'"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# for input argument\n",
    "input_user = sys.argv[1]\n",
    "input_clsi = sys.argv[2]\n",
    "output_table = sys.argv[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "21d5fe63",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''input_user='input2.csv'\n",
    "input_clsi='clsi.csv'\n",
    "output_profile=input_user+'_profile.csv'\n",
    "output_table=input_user+'_table.csv''''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "1e64b025",
   "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>Strain name</th>\n",
       "      <th>Antibiotics</th>\n",
       "      <th>MIC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Acinetobacter baumannii A85</td>\n",
       "      <td>Colistin</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Acinetobacter baumannii A85</td>\n",
       "      <td>Netilmicin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>Ampicillin</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>Ampicillin/sulbactam</td>\n",
       "      <td>mg/L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>piperacillin/tazobactam</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>Cefazolin</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>Ceftriaxone</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>Cefepime</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>Meropenem</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>Imipenem</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Acinetobacter baumannii AB307-0294</td>\n",
       "      <td>Tigecycline</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Acinetobacter baumannii AB5075-UW</td>\n",
       "      <td>Ceftazidime</td>\n",
       "      <td>&gt;128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Acinetobacter baumannii AB5075-UW</td>\n",
       "      <td>Cefepime</td>\n",
       "      <td>&gt;256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Acinetobacter baumannii AB5075-UW</td>\n",
       "      <td>Ciprofloxacin</td>\n",
       "      <td>&gt;32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Acinetobacter baumannii DU202</td>\n",
       "      <td>colistin</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Acinetobacter baumannii DU202</td>\n",
       "      <td>Ampicillin/sulbactam</td>\n",
       "      <td>&gt;=0.3 mg/L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Acinetobacter baumannii DU202</td>\n",
       "      <td>ticarcillin/clavulanic acid</td>\n",
       "      <td>&gt;=128/2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Acinetobacter baumannii DU202</td>\n",
       "      <td>piperacillin/tazobactam</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Acinetobacter baumannii DU202</td>\n",
       "      <td>trimethoprim/sulfamethoxazole</td>\n",
       "      <td>8/152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Acinetobacter baumannii ORAB01</td>\n",
       "      <td>piperacillin/tazobactam</td>\n",
       "      <td>&gt;64/4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Acinetobacter baumannii strain MRSN7100</td>\n",
       "      <td>amoxicillin/clavulanic acid</td>\n",
       "      <td>16/8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Acinetobacter baumannii strain MRSN7168</td>\n",
       "      <td>amoxicillin/clavulanic acid</td>\n",
       "      <td>16/8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Acinetobacter baumannii strain MRSN7336</td>\n",
       "      <td>amoxicillin/clavulanic acid</td>\n",
       "      <td>16/8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Acinetobacter baumannii strain MRSN7834</td>\n",
       "      <td>amoxicillin/clavulanic acid</td>\n",
       "      <td>16/8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Acinetobacter baumannii strain FDA-CDC-AR_0305</td>\n",
       "      <td>polymyxin B</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Acinetobacter baumannii strain FDA-CDC-AR_0306</td>\n",
       "      <td>polymyxin B</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Acinetobacter baumannii strain FDA-CDC-AR_0307</td>\n",
       "      <td>polymyxin B</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Acinetobacter baumannii strain FDA-CDC-AR_0308</td>\n",
       "      <td>polymyxin B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Strain name  \\\n",
       "0                      Acinetobacter baumannii A85   \n",
       "1                      Acinetobacter baumannii A85   \n",
       "2               Acinetobacter baumannii AB307-0294   \n",
       "3               Acinetobacter baumannii AB307-0294   \n",
       "4               Acinetobacter baumannii AB307-0294   \n",
       "5               Acinetobacter baumannii AB307-0294   \n",
       "6               Acinetobacter baumannii AB307-0294   \n",
       "7               Acinetobacter baumannii AB307-0294   \n",
       "8               Acinetobacter baumannii AB307-0294   \n",
       "9               Acinetobacter baumannii AB307-0294   \n",
       "10              Acinetobacter baumannii AB307-0294   \n",
       "11               Acinetobacter baumannii AB5075-UW   \n",
       "12               Acinetobacter baumannii AB5075-UW   \n",
       "13               Acinetobacter baumannii AB5075-UW   \n",
       "14                   Acinetobacter baumannii DU202   \n",
       "15                   Acinetobacter baumannii DU202   \n",
       "16                   Acinetobacter baumannii DU202   \n",
       "17                   Acinetobacter baumannii DU202   \n",
       "18                   Acinetobacter baumannii DU202   \n",
       "19                  Acinetobacter baumannii ORAB01   \n",
       "20         Acinetobacter baumannii strain MRSN7100   \n",
       "21         Acinetobacter baumannii strain MRSN7168   \n",
       "22         Acinetobacter baumannii strain MRSN7336   \n",
       "23         Acinetobacter baumannii strain MRSN7834   \n",
       "24  Acinetobacter baumannii strain FDA-CDC-AR_0305   \n",
       "25  Acinetobacter baumannii strain FDA-CDC-AR_0306   \n",
       "26  Acinetobacter baumannii strain FDA-CDC-AR_0307   \n",
       "27  Acinetobacter baumannii strain FDA-CDC-AR_0308   \n",
       "\n",
       "                      Antibiotics         MIC  \n",
       "0                        Colistin         0.1  \n",
       "1                      Netilmicin           1  \n",
       "2                      Ampicillin           0  \n",
       "3            Ampicillin/sulbactam        mg/L  \n",
       "4         piperacillin/tazobactam           0  \n",
       "5                       Cefazolin          64  \n",
       "6                     Ceftriaxone          16  \n",
       "7                        Cefepime           8  \n",
       "8                       Meropenem        0.25  \n",
       "9                        Imipenem           1  \n",
       "10                    Tigecycline         0.5  \n",
       "11                    Ceftazidime        >128  \n",
       "12                       Cefepime        >256  \n",
       "13                  Ciprofloxacin         >32  \n",
       "14                      colistin               \n",
       "15           Ampicillin/sulbactam  >=0.3 mg/L  \n",
       "16    ticarcillin/clavulanic acid     >=128/2  \n",
       "17       piperacillin/tazobactam           NA  \n",
       "18  trimethoprim/sulfamethoxazole       8/152  \n",
       "19        piperacillin/tazobactam       >64/4  \n",
       "20    amoxicillin/clavulanic acid        16/8  \n",
       "21    amoxicillin/clavulanic acid        16/8  \n",
       "22    amoxicillin/clavulanic acid        16/8  \n",
       "23    amoxicillin/clavulanic acid        16/8  \n",
       "24                    polymyxin B           1  \n",
       "25                    polymyxin B           1  \n",
       "26                    polymyxin B           8  \n",
       "27                    polymyxin B           4  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read user AST data with selected 3 columns\n",
    "strain_mic=pd.read_csv(input_user, sep=',', usecols =['Strain name', 'Antibiotics', 'MIC'],na_filter=False)\n",
    "#strain_mic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0d30ddc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "clsi_bp=pd.read_csv(input_clsi,sep=',')\n",
    "\n",
    "#clsi_bp[clsi_bp[['Antibiotics', 'Susceptible']].duplicated()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "a818676d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#clsi_bp\n",
    "#strain_mic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "c6b4c59b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert MIC to numbers sMIC, rMIC\n",
    "clsi_bp['s_mic'] =clsi_bp[['Susceptible']].applymap(lambda x: (re.sub(r'[^0-9.\\/-]', '', x)))\n",
    "clsi_bp['r_mic'] =clsi_bp[['Resistant']].applymap(lambda x: (re.sub(r'[^0-9.\\/-]', '', x)))\n",
    "clsi_bp['i_mic'] = clsi_bp[['Intermediate']].applymap(lambda x: (re.sub(r'[^0-9.\\/-]', '', x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "d0171f94",
   "metadata": {},
   "outputs": [],
   "source": [
    "#clsi_bp['i_mic'] = clsi_bp[['Intermediate']].applymap(lambda x: (re.sub(r'[^0-9.\\/-]', '', x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "fe45b2dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read only numbers in MIC values\n",
    "#try:\n",
    "strain_mic['o_mic']=strain_mic[['MIC']].applymap(lambda x: (re.sub(r'[^0-9.\\/]','', x)))\n",
    "#except TypeError:\n",
    "#    print('Waring: Error in MIC value')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "ddbbe4d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#strain_mic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "640508f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# capitalize each Antibiotic Name for comparision with removing whitespace\n",
    "strain_mic['Strain name']=strain_mic['Strain name'].str.capitalize().str.replace(\" \",\"\")\n",
    "strain_mic['Antibiotics']=strain_mic['Antibiotics'].str.capitalize().str.replace(\" \",\"\")\n",
    "\n",
    "clsi_bp['Antibiotics']=clsi_bp['Antibiotics'].str.capitalize().str.replace(\" \",\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "b87426f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#find duplicate values in input files\n",
    "dups=strain_mic[strain_mic[['Strain name', 'Antibiotics']].duplicated(keep=False)]\n",
    "if dups.shape[0] != 0:\n",
    "    print ('Please provide a single MIC value in input file for given duplicates combination of \\'Strain name and Antibiotics\\' to use the tool:-\\n',dups)\n",
    "    #exit()\n",
    "else:\n",
    "    #compare CLSI Antibiotics only\n",
    "    #result=pd.merge(strain_mic, clsi_bp, on='Antibiotics',how='inner',  indicator=True)[['Strain name','Antibiotics', 'MIC', 'o_mic', 's_mic', 'r_mic','_merge']]\n",
    "    try:\n",
    "        result=pd.merge(strain_mic, clsi_bp, on='Antibiotics',how='inner')[['Strain name','Antibiotics', 'MIC', 'o_mic', 's_mic', 'r_mic','i_mic']]\n",
    "    except KeyError:\n",
    "        print('Waring: Error in input Values')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "91bfc94d",
   "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>Strain name</th>\n",
       "      <th>Antibiotics</th>\n",
       "      <th>MIC</th>\n",
       "      <th>o_mic</th>\n",
       "      <th>s_mic</th>\n",
       "      <th>r_mic</th>\n",
       "      <th>i_mic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Acinetobacterbaumanniia85</td>\n",
       "      <td>Colistin</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>-</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Acinetobacterbaumanniidu202</td>\n",
       "      <td>Colistin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Acinetobacterbaumanniia85</td>\n",
       "      <td>Netilmicin</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Acinetobacterbaumanniiab307-0294</td>\n",
       "      <td>Ampicillin/sulbactam</td>\n",
       "      <td>mg/L</td>\n",
       "      <td>/</td>\n",
       "      <td>8/4</td>\n",
       "      <td>32/16</td>\n",
       "      <td>16/8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Acinetobacterbaumanniidu202</td>\n",
       "      <td>Ampicillin/sulbactam</td>\n",
       "      <td>&gt;=0.3 mg/L</td>\n",
       "      <td>0.3/</td>\n",
       "      <td>8/4</td>\n",
       "      <td>32/16</td>\n",
       "      <td>16/8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Acinetobacterbaumanniiab307-0294</td>\n",
       "      <td>Piperacillin/tazobactam</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16/4</td>\n",
       "      <td>128/4</td>\n",
       "      <td>32/4-64/4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Acinetobacterbaumanniidu202</td>\n",
       "      <td>Piperacillin/tazobactam</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>16/4</td>\n",
       "      <td>128/4</td>\n",
       "      <td>32/4-64/4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Acinetobacterbaumanniiorab01</td>\n",
       "      <td>Piperacillin/tazobactam</td>\n",
       "      <td>&gt;64/4</td>\n",
       "      <td>64/4</td>\n",
       "      <td>16/4</td>\n",
       "      <td>128/4</td>\n",
       "      <td>32/4-64/4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Acinetobacterbaumanniiab307-0294</td>\n",
       "      <td>Ceftriaxone</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>16-32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Acinetobacterbaumanniiab307-0294</td>\n",
       "      <td>Cefepime</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Acinetobacterbaumanniiab5075-uw</td>\n",
       "      <td>Cefepime</td>\n",
       "      <td>&gt;256</td>\n",
       "      <td>256</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Acinetobacterbaumanniiab307-0294</td>\n",
       "      <td>Meropenem</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Acinetobacterbaumanniiab307-0294</td>\n",
       "      <td>Imipenem</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Acinetobacterbaumanniiab5075-uw</td>\n",
       "      <td>Ceftazidime</td>\n",
       "      <td>&gt;128</td>\n",
       "      <td>128</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Acinetobacterbaumanniiab5075-uw</td>\n",
       "      <td>Ciprofloxacin</td>\n",
       "      <td>&gt;32</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Acinetobacterbaumanniidu202</td>\n",
       "      <td>Ticarcillin/clavulanicacid</td>\n",
       "      <td>&gt;=128/2</td>\n",
       "      <td>128/2</td>\n",
       "      <td>16/2</td>\n",
       "      <td>128/2</td>\n",
       "      <td>32/2-64/2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Acinetobacterbaumanniidu202</td>\n",
       "      <td>Trimethoprim/sulfamethoxazole</td>\n",
       "      <td>8/152</td>\n",
       "      <td>8/152</td>\n",
       "      <td>2/38</td>\n",
       "      <td>4/76</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Acinetobacterbaumanniistrainfda-cdc-ar_0305</td>\n",
       "      <td>Polymyxinb</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Acinetobacterbaumanniistrainfda-cdc-ar_0306</td>\n",
       "      <td>Polymyxinb</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Acinetobacterbaumanniistrainfda-cdc-ar_0307</td>\n",
       "      <td>Polymyxinb</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>-</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Acinetobacterbaumanniistrainfda-cdc-ar_0308</td>\n",
       "      <td>Polymyxinb</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>-</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Strain name  \\\n",
       "0                     Acinetobacterbaumanniia85   \n",
       "1                   Acinetobacterbaumanniidu202   \n",
       "2                     Acinetobacterbaumanniia85   \n",
       "3              Acinetobacterbaumanniiab307-0294   \n",
       "4                   Acinetobacterbaumanniidu202   \n",
       "5              Acinetobacterbaumanniiab307-0294   \n",
       "6                   Acinetobacterbaumanniidu202   \n",
       "7                  Acinetobacterbaumanniiorab01   \n",
       "8              Acinetobacterbaumanniiab307-0294   \n",
       "9              Acinetobacterbaumanniiab307-0294   \n",
       "10              Acinetobacterbaumanniiab5075-uw   \n",
       "11             Acinetobacterbaumanniiab307-0294   \n",
       "12             Acinetobacterbaumanniiab307-0294   \n",
       "13              Acinetobacterbaumanniiab5075-uw   \n",
       "14              Acinetobacterbaumanniiab5075-uw   \n",
       "15                  Acinetobacterbaumanniidu202   \n",
       "16                  Acinetobacterbaumanniidu202   \n",
       "17  Acinetobacterbaumanniistrainfda-cdc-ar_0305   \n",
       "18  Acinetobacterbaumanniistrainfda-cdc-ar_0306   \n",
       "19  Acinetobacterbaumanniistrainfda-cdc-ar_0307   \n",
       "20  Acinetobacterbaumanniistrainfda-cdc-ar_0308   \n",
       "\n",
       "                      Antibiotics         MIC  o_mic s_mic  r_mic      i_mic  \n",
       "0                        Colistin         0.1    0.1     -      4          2  \n",
       "1                        Colistin                        -      4          2  \n",
       "2                      Netilmicin           1      1     8     32         16  \n",
       "3            Ampicillin/sulbactam        mg/L      /   8/4  32/16       16/8  \n",
       "4            Ampicillin/sulbactam  >=0.3 mg/L   0.3/   8/4  32/16       16/8  \n",
       "5         Piperacillin/tazobactam           0      0  16/4  128/4  32/4-64/4  \n",
       "6         Piperacillin/tazobactam          NA         16/4  128/4  32/4-64/4  \n",
       "7         Piperacillin/tazobactam       >64/4   64/4  16/4  128/4  32/4-64/4  \n",
       "8                     Ceftriaxone          16     16     8     64      16-32  \n",
       "9                        Cefepime           8      8     8     32         16  \n",
       "10                       Cefepime        >256    256     8     32         16  \n",
       "11                      Meropenem        0.25   0.25     2      8          4  \n",
       "12                       Imipenem           1      1     2      8          4  \n",
       "13                    Ceftazidime        >128    128     8     32         16  \n",
       "14                  Ciprofloxacin         >32     32     1      4          2  \n",
       "15     Ticarcillin/clavulanicacid     >=128/2  128/2  16/2  128/2  32/2-64/2  \n",
       "16  Trimethoprim/sulfamethoxazole       8/152  8/152  2/38   4/76          -  \n",
       "17                     Polymyxinb           1      1     -      4          2  \n",
       "18                     Polymyxinb           1      1     -      4          2  \n",
       "19                     Polymyxinb           8      8     -      4          2  \n",
       "20                     Polymyxinb           4      4     -      4          2  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "b171f205",
   "metadata": {},
   "outputs": [],
   "source": [
    "#compare MIC values and assign Susceptible and Resistant to Strain\n",
    "#try:\n",
    "result[['CLSI_profile']] = result[['o_mic','s_mic','r_mic','i_mic']].apply(sus_res_int,axis = 1)\n",
    "#except ValueError:\n",
    "#    print('Waring: Error in input MIC value')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "3336fd92",
   "metadata": {},
   "outputs": [],
   "source": [
    "#result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "f0dacfd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#result[['Strain name', 'Antibiotics', 'MIC','s_mic','r_mic','CLSI_profile']].to_csv(output_profile,sep=',', index=False, encoding='utf-8-sig')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "3d8d03f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a pivot table for ASIST\n",
    "table=result[['Strain name', 'Antibiotics','CLSI_profile']].drop_duplicates()\n",
    "result_table=pd.pivot_table(table, values ='CLSI_profile', index =['Strain name'],columns =['Antibiotics'], aggfunc = lambda x: ' '.join(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "7d7223a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#result_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "8a41b2ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "#result_table.to_csv(output_table,na_rep='NA')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "8c9e5f87",
   "metadata": {},
   "outputs": [],
   "source": [
    "# reorder the Antibiotics for ASIST\n",
    "clsi_ab=['Amikacin','Tobramycin','Gentamycin','Netilmicin','Imipenem','Meropenem','Doripenem','Ciprofloxacin','Levofloxacin',\n",
    "         'Piperacillin/tazobactam','Ticarcillin/clavulanicacid','Cefotaxime','Ceftriaxone','Ceftazidime','Cefepime',\n",
    "         'Trimethoprim/sulfamethoxazole','Ampicillin/sulbactam','Colistin','Polymyxinb','Tetracycline','Doxicycline ',\n",
    "         'Minocycline']\n",
    "result_selected=result_table.filter(clsi_ab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "cdf43afb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(result_selected.shape, result_table.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "c4c4df30",
   "metadata": {},
   "outputs": [],
   "source": [
    "#result_selected.insert(0,'Resistance_phenotype','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "9adb2703",
   "metadata": {},
   "outputs": [],
   "source": [
    "#rename headers\n",
    "result_selected=result_selected.rename(columns = {'Ticarcillin/clavulanicacid':'Ticarcillin/clavulanic acid','Piperacillin/tazobactam':'Piperacillin/ tazobactam','Trimethoprim/sulfamethoxazole': 'Trimethoprim/ sulfamethoxazole','Ampicillin/sulbactam':'Ampicillin/ sulbactam', 'Polymyxinb': 'Polymyxin B'} )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "50e6cf5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#result_selected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "2833671c",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_selected.to_csv(output_table,na_rep='NA')"
   ]
  }
 ],
 "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}