AMRparser.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import Bio\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the AMRresults table\n",
    "\n",
    "Original table dropping unwanted columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>accession</th>\n",
       "      <th>gene</th>\n",
       "      <th>protein-name</th>\n",
       "      <th>Target length</th>\n",
       "      <th>RefSeq length</th>\n",
       "      <th>%Cov</th>\n",
       "      <th>%Iden</th>\n",
       "      <th>Align Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>gb|MW116081|</td>\n",
       "      <td>sul1</td>\n",
       "      <td>sulfonamide-resistant dihydropteroate synthase...</td>\n",
       "      <td>145</td>\n",
       "      <td>275</td>\n",
       "      <td>52.73</td>\n",
       "      <td>100.0</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gb|MW207171|</td>\n",
       "      <td>aac(6')-Ib-cr</td>\n",
       "      <td>fluoroquinolone-acetylating aminoglycoside 6'-...</td>\n",
       "      <td>160</td>\n",
       "      <td>184</td>\n",
       "      <td>86.96</td>\n",
       "      <td>100.0</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>gb|MZ254707|</td>\n",
       "      <td>blaCMY-6</td>\n",
       "      <td>class C beta-lactamase CMY-6</td>\n",
       "      <td>381</td>\n",
       "      <td>381</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>gb|MZ254708|</td>\n",
       "      <td>qnrS</td>\n",
       "      <td>QnrS family quinolone resistance pentapeptide ...</td>\n",
       "      <td>139</td>\n",
       "      <td>218</td>\n",
       "      <td>63.76</td>\n",
       "      <td>100.0</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gb|MZ254709|</td>\n",
       "      <td>tet(B)</td>\n",
       "      <td>tetracycline efflux MFS transporter Tet(B)</td>\n",
       "      <td>219</td>\n",
       "      <td>401</td>\n",
       "      <td>54.61</td>\n",
       "      <td>100.0</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>gb|MZ254710|</td>\n",
       "      <td>blaSHV</td>\n",
       "      <td>SHV family class A beta-lactamase</td>\n",
       "      <td>265</td>\n",
       "      <td>286</td>\n",
       "      <td>92.66</td>\n",
       "      <td>100.0</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>gb|MZ254711|</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>gb|MZ254712|</td>\n",
       "      <td>blaNDM</td>\n",
       "      <td>NDM family subclass B1 metallo-beta-lactamase</td>\n",
       "      <td>158</td>\n",
       "      <td>270</td>\n",
       "      <td>58.52</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>gb|MZ254713|</td>\n",
       "      <td>blaTEM</td>\n",
       "      <td>TEM family class A beta-lactamase</td>\n",
       "      <td>274</td>\n",
       "      <td>286</td>\n",
       "      <td>94.41</td>\n",
       "      <td>100.0</td>\n",
       "      <td>270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>gb|MZ275235|</td>\n",
       "      <td>aac(6')-Ib-cr</td>\n",
       "      <td>fluoroquinolone-acetylating aminoglycoside 6'-...</td>\n",
       "      <td>159</td>\n",
       "      <td>184</td>\n",
       "      <td>86.41</td>\n",
       "      <td>100.0</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>gb|MZ275236|</td>\n",
       "      <td>qnrB</td>\n",
       "      <td>QnrB family quinolone resistance pentapeptide ...</td>\n",
       "      <td>158</td>\n",
       "      <td>214</td>\n",
       "      <td>73.83</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>gb|MZ275237|</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>gb|MZ275238|</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>gb|MZ275239|</td>\n",
       "      <td>sul1</td>\n",
       "      <td>sulfonamide-resistant dihydropteroate synthase...</td>\n",
       "      <td>145</td>\n",
       "      <td>275</td>\n",
       "      <td>52.73</td>\n",
       "      <td>100.0</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>gb|MZ275240|</td>\n",
       "      <td>blaNDM</td>\n",
       "      <td>NDM family subclass B1 metallo-beta-lactamase</td>\n",
       "      <td>158</td>\n",
       "      <td>270</td>\n",
       "      <td>58.52</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       accession           gene  \\\n",
       "0   gb|MW116081|           sul1   \n",
       "1   gb|MW207171|  aac(6')-Ib-cr   \n",
       "2   gb|MZ254707|       blaCMY-6   \n",
       "3   gb|MZ254708|           qnrS   \n",
       "4   gb|MZ254709|         tet(B)   \n",
       "5   gb|MZ254710|         blaSHV   \n",
       "6   gb|MZ254711|       blaCTX-M   \n",
       "7   gb|MZ254712|         blaNDM   \n",
       "8   gb|MZ254713|         blaTEM   \n",
       "9   gb|MZ275235|  aac(6')-Ib-cr   \n",
       "10  gb|MZ275236|           qnrB   \n",
       "11  gb|MZ275237|       blaCTX-M   \n",
       "12  gb|MZ275238|       blaCTX-M   \n",
       "13  gb|MZ275239|           sul1   \n",
       "14  gb|MZ275240|         blaNDM   \n",
       "\n",
       "                                         protein-name  Target length  \\\n",
       "0   sulfonamide-resistant dihydropteroate synthase...            145   \n",
       "1   fluoroquinolone-acetylating aminoglycoside 6'-...            160   \n",
       "2                        class C beta-lactamase CMY-6            381   \n",
       "3   QnrS family quinolone resistance pentapeptide ...            139   \n",
       "4          tetracycline efflux MFS transporter Tet(B)            219   \n",
       "5                   SHV family class A beta-lactamase            265   \n",
       "6   CTX-M family class A extended-spectrum beta-la...            180   \n",
       "7       NDM family subclass B1 metallo-beta-lactamase            158   \n",
       "8                   TEM family class A beta-lactamase            274   \n",
       "9   fluoroquinolone-acetylating aminoglycoside 6'-...            159   \n",
       "10  QnrB family quinolone resistance pentapeptide ...            158   \n",
       "11  CTX-M family class A extended-spectrum beta-la...            180   \n",
       "12  CTX-M family class A extended-spectrum beta-la...            180   \n",
       "13  sulfonamide-resistant dihydropteroate synthase...            145   \n",
       "14      NDM family subclass B1 metallo-beta-lactamase            158   \n",
       "\n",
       "    RefSeq length    %Cov  %Iden  Align Length  \n",
       "0             275   52.73  100.0           145  \n",
       "1             184   86.96  100.0           160  \n",
       "2             381  100.00  100.0           381  \n",
       "3             218   63.76  100.0           139  \n",
       "4             401   54.61  100.0           219  \n",
       "5             286   92.66  100.0           265  \n",
       "6             291   61.86  100.0           180  \n",
       "7             270   58.52  100.0           158  \n",
       "8             286   94.41  100.0           270  \n",
       "9             184   86.41  100.0           159  \n",
       "10            214   73.83  100.0           158  \n",
       "11            291   61.86  100.0           180  \n",
       "12            291   61.86  100.0           180  \n",
       "13            275   52.73  100.0           145  \n",
       "14            270   58.52  100.0           158  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_name_string = (r'C:\\Users\\mcveigh\\Documents\\PythonPC\\AMRresults.txt')\n",
    "AMRresults = pd.read_csv(file_name_string,\n",
    "                        sep='\\t',\n",
    "                        index_col=None,\n",
    "                        low_memory=False,\n",
    "                        usecols=[0,1,2,9,10,11,12,13],\n",
    "                        header=0,\n",
    "                        names=[\"accession\", \"gene\", \"protein-name\", \"Target length\",\n",
    "                               \"RefSeq length\", \"%Cov\", \"%Iden\", \"Align Length\"])\n",
    "AMRresults"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check for seqids missing in the AMRresults table but present in the fasta file\n",
    "\n",
    "Outputs a list of seqids that were not found in the AMRresults table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gb|MZ424297|\n",
      "gb|MW183828|\n",
      "gb|MW183829|\n",
      "gb|MW183830|\n",
      "gb|MW183831|\n",
      "gb|MW183832|\n",
      "gb|MW183833|\n",
      "gb|MW183834|\n",
      "gb|MW183835|\n",
      "gb|MW183836|\n",
      "gb|MW183837|\n",
      "gb|MW183838|\n",
      "gb|MW183839|\n",
      "gb|MW183840|\n",
      "gb|MW183841|\n",
      "gb|MW183842|\n",
      "gb|MW183843|\n",
      "gb|MW183844|\n",
      "gb|MW183845|\n",
      "gb|MW183846|\n",
      "gb|MW183847|\n",
      "gb|MW183848|\n",
      "gb|MW183849|\n",
      "gb|MW183850|\n",
      "gb|MW183851|\n",
      "gb|MW183852|\n",
      "gb|MW183853|\n",
      "gb|MW183854|\n",
      "gb|MW183855|\n",
      "gb|MW183856|\n",
      "gb|MW183857|\n",
      "gb|MW183858|\n",
      "gb|MW183859|\n",
      "gb|MW183860|\n",
      "gb|MW183861|\n",
      "gb|MW183862|\n",
      "gb|MW183863|\n",
      "gb|MW183864|\n",
      "gb|MW183865|\n",
      "gb|MW183866|\n",
      "gb|MW183867|\n",
      "gb|MW183868|\n",
      "gb|MW183869|\n",
      "gb|MW183870|\n",
      "gb|MW183871|\n",
      "gb|MW183872|\n",
      "gb|MW183873|\n",
      "gb|MW183874|\n",
      "gb|MW183875|\n",
      "gb|MW183876|\n",
      "gb|MW183877|\n",
      "gb|MW183878|\n",
      "gb|MW183879|\n",
      "gb|MW183880|\n",
      "gb|MW183881|\n",
      "gb|MW183882|\n",
      "gb|MW183883|\n",
      "gb|MW183884|\n",
      "gb|MW183885|\n",
      "gb|MW183886|\n",
      "gb|MW183887|\n",
      "gb|MW183888|\n",
      "gb|MW183889|\n",
      "gb|MW183890|\n",
      "gb|MW183891|\n",
      "gb|MW183892|\n",
      "gb|MW183893|\n",
      "gb|MW183894|\n",
      "gb|MW183895|\n",
      "gb|MW183896|\n",
      "gb|MW183897|\n",
      "gb|MW183898|\n",
      "gb|MW183899|\n",
      "gb|MW183900|\n",
      "gb|MW183901|\n",
      "gb|MW183902|\n",
      "gb|MW183903|\n",
      "gb|MW183904|\n",
      "gb|MW183905|\n",
      "gb|MW183906|\n",
      "gb|MW183907|\n",
      "gb|MW183908|\n",
      "gb|MW183909|\n",
      "gb|MW183910|\n",
      "gb|MW183911|\n",
      "gb|MW183912|\n",
      "gb|MW183913|\n",
      "gb|MW183914|\n",
      "gb|MW183915|\n",
      "gb|MW183916|\n",
      "gb|MW183917|\n",
      "gb|MW183918|\n",
      "gb|MW183919|\n",
      "gb|MT680405|\n",
      "gb|MT680406|\n",
      "gb|MT680407|\n",
      "gb|MZ424298|\n",
      "gb|MZ424299|\n",
      "gb|MZ424300|\n",
      "gb|MZ424301|\n",
      "gb|MZ424302|\n",
      "gb|MZ424303|\n"
     ]
    }
   ],
   "source": [
    "from Bio import SeqIO\n",
    "for seq_record in SeqIO.parse(\"AMRprotein.fa.txt\", \"fasta\"):    \n",
    "    if seq_record.id not in AMRresults['accession'].tolist():\n",
    "        print(seq_record.id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>accession</th>\n",
       "      <th>gene</th>\n",
       "      <th>allele</th>\n",
       "      <th>protein-name</th>\n",
       "      <th>Target length</th>\n",
       "      <th>RefSeq length</th>\n",
       "      <th>%Cov</th>\n",
       "      <th>%Iden</th>\n",
       "      <th>Align Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MW116081</td>\n",
       "      <td>sul1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sulfonamide-resistant dihydropteroate synthase...</td>\n",
       "      <td>145</td>\n",
       "      <td>275</td>\n",
       "      <td>52.73</td>\n",
       "      <td>100.0</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MW207171</td>\n",
       "      <td>aac(6')-Ib-cr</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fluoroquinolone-acetylating aminoglycoside 6'-...</td>\n",
       "      <td>160</td>\n",
       "      <td>184</td>\n",
       "      <td>86.96</td>\n",
       "      <td>100.0</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MZ254707</td>\n",
       "      <td>blaCMY-6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>class C beta-lactamase CMY-6</td>\n",
       "      <td>381</td>\n",
       "      <td>381</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MZ254708</td>\n",
       "      <td>qnrS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QnrS family quinolone resistance pentapeptide ...</td>\n",
       "      <td>139</td>\n",
       "      <td>218</td>\n",
       "      <td>63.76</td>\n",
       "      <td>100.0</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MZ254709</td>\n",
       "      <td>tet(B)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tetracycline efflux MFS transporter Tet(B)</td>\n",
       "      <td>219</td>\n",
       "      <td>401</td>\n",
       "      <td>54.61</td>\n",
       "      <td>100.0</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MZ254710</td>\n",
       "      <td>blaSHV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SHV family class A beta-lactamase</td>\n",
       "      <td>265</td>\n",
       "      <td>286</td>\n",
       "      <td>92.66</td>\n",
       "      <td>100.0</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MZ254711</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MZ254712</td>\n",
       "      <td>blaNDM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NDM family subclass B1 metallo-beta-lactamase</td>\n",
       "      <td>158</td>\n",
       "      <td>270</td>\n",
       "      <td>58.52</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MZ254713</td>\n",
       "      <td>blaTEM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEM family class A beta-lactamase</td>\n",
       "      <td>274</td>\n",
       "      <td>286</td>\n",
       "      <td>94.41</td>\n",
       "      <td>100.0</td>\n",
       "      <td>270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MZ275235</td>\n",
       "      <td>aac(6')-Ib-cr</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fluoroquinolone-acetylating aminoglycoside 6'-...</td>\n",
       "      <td>159</td>\n",
       "      <td>184</td>\n",
       "      <td>86.41</td>\n",
       "      <td>100.0</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>MZ275236</td>\n",
       "      <td>qnrB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QnrB family quinolone resistance pentapeptide ...</td>\n",
       "      <td>158</td>\n",
       "      <td>214</td>\n",
       "      <td>73.83</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MZ275237</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>MZ275238</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MZ275239</td>\n",
       "      <td>sul1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sulfonamide-resistant dihydropteroate synthase...</td>\n",
       "      <td>145</td>\n",
       "      <td>275</td>\n",
       "      <td>52.73</td>\n",
       "      <td>100.0</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>MZ275240</td>\n",
       "      <td>blaNDM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NDM family subclass B1 metallo-beta-lactamase</td>\n",
       "      <td>158</td>\n",
       "      <td>270</td>\n",
       "      <td>58.52</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   accession           gene  allele  \\\n",
       "0   MW116081           sul1     NaN   \n",
       "1   MW207171  aac(6')-Ib-cr     NaN   \n",
       "2   MZ254707       blaCMY-6     NaN   \n",
       "3   MZ254708           qnrS     NaN   \n",
       "4   MZ254709         tet(B)     NaN   \n",
       "5   MZ254710         blaSHV     NaN   \n",
       "6   MZ254711       blaCTX-M     NaN   \n",
       "7   MZ254712         blaNDM     NaN   \n",
       "8   MZ254713         blaTEM     NaN   \n",
       "9   MZ275235  aac(6')-Ib-cr     NaN   \n",
       "10  MZ275236           qnrB     NaN   \n",
       "11  MZ275237       blaCTX-M     NaN   \n",
       "12  MZ275238       blaCTX-M     NaN   \n",
       "13  MZ275239           sul1     NaN   \n",
       "14  MZ275240         blaNDM     NaN   \n",
       "\n",
       "                                         protein-name  Target length  \\\n",
       "0   sulfonamide-resistant dihydropteroate synthase...            145   \n",
       "1   fluoroquinolone-acetylating aminoglycoside 6'-...            160   \n",
       "2                        class C beta-lactamase CMY-6            381   \n",
       "3   QnrS family quinolone resistance pentapeptide ...            139   \n",
       "4          tetracycline efflux MFS transporter Tet(B)            219   \n",
       "5                   SHV family class A beta-lactamase            265   \n",
       "6   CTX-M family class A extended-spectrum beta-la...            180   \n",
       "7       NDM family subclass B1 metallo-beta-lactamase            158   \n",
       "8                   TEM family class A beta-lactamase            274   \n",
       "9   fluoroquinolone-acetylating aminoglycoside 6'-...            159   \n",
       "10  QnrB family quinolone resistance pentapeptide ...            158   \n",
       "11  CTX-M family class A extended-spectrum beta-la...            180   \n",
       "12  CTX-M family class A extended-spectrum beta-la...            180   \n",
       "13  sulfonamide-resistant dihydropteroate synthase...            145   \n",
       "14      NDM family subclass B1 metallo-beta-lactamase            158   \n",
       "\n",
       "    RefSeq length    %Cov  %Iden  Align Length  \n",
       "0             275   52.73  100.0           145  \n",
       "1             184   86.96  100.0           160  \n",
       "2             381  100.00  100.0           381  \n",
       "3             218   63.76  100.0           139  \n",
       "4             401   54.61  100.0           219  \n",
       "5             286   92.66  100.0           265  \n",
       "6             291   61.86  100.0           180  \n",
       "7             270   58.52  100.0           158  \n",
       "8             286   94.41  100.0           270  \n",
       "9             184   86.41  100.0           159  \n",
       "10            214   73.83  100.0           158  \n",
       "11            291   61.86  100.0           180  \n",
       "12            291   61.86  100.0           180  \n",
       "13            275   52.73  100.0           145  \n",
       "14            270   58.52  100.0           158  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AMRresults['accession']=AMRresults['accession'].str.replace('gb','')\n",
    "AMRresults['accession']=AMRresults['accession'].str.replace('lcl|','')\n",
    "AMRresults['accession']=AMRresults['accession'].str.replace('|','')\n",
    "AMRresults.insert(2, \"allele\", np.nan)\n",
    "AMRresults"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read in CuratedAMRFamilies.txt file.\n",
    "\n",
    "The file can be editted prior to running the script but be sure to save it with the same file name and in the same directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "GeneList = (r'CuratedAMRFamilies.txt') \n",
    "genelist_df = pd.read_csv(GeneList, sep='\\t', index_col=None, low_memory=False, header=None, names=[\"genesymbol\"])\n",
    "genelist_df = set(genelist_df['genesymbol'].tolist())\n",
    "pattern = '|'.join(genelist_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "def pattern_searcher(search_str:str, search_list:str):\n",
    "\n",
    "    search_obj = re.search(search_list, search_str)\n",
    "    if search_obj :\n",
    "        return_str = search_str[search_obj.start(): search_obj.end()]\n",
    "    else:\n",
    "        return_str = np.nan\n",
    "    return return_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "AMRresults['allele'] = AMRresults.gene.apply(lambda x: pattern_searcher(search_str=x, search_list=pattern))\n",
    "#AMRresults"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "AMRresults.loc[AMRresults.gene == AMRresults.allele, \"allele\"] = np.nan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Final table to save and use with gbench to update your records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": false
   },
   "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>accession</th>\n",
       "      <th>gene</th>\n",
       "      <th>allele</th>\n",
       "      <th>protein-name</th>\n",
       "      <th>Target length</th>\n",
       "      <th>RefSeq length</th>\n",
       "      <th>%Cov</th>\n",
       "      <th>%Iden</th>\n",
       "      <th>Align Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MW116081</td>\n",
       "      <td>sul1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sulfonamide-resistant dihydropteroate synthase...</td>\n",
       "      <td>145</td>\n",
       "      <td>275</td>\n",
       "      <td>52.73</td>\n",
       "      <td>100.0</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MW207171</td>\n",
       "      <td>aac(6')-Ib-cr</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fluoroquinolone-acetylating aminoglycoside 6'-...</td>\n",
       "      <td>160</td>\n",
       "      <td>184</td>\n",
       "      <td>86.96</td>\n",
       "      <td>100.0</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MZ254707</td>\n",
       "      <td>blaCMY</td>\n",
       "      <td>blaCMY-6</td>\n",
       "      <td>class C beta-lactamase CMY-6</td>\n",
       "      <td>381</td>\n",
       "      <td>381</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MZ254708</td>\n",
       "      <td>qnrS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QnrS family quinolone resistance pentapeptide ...</td>\n",
       "      <td>139</td>\n",
       "      <td>218</td>\n",
       "      <td>63.76</td>\n",
       "      <td>100.0</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MZ254709</td>\n",
       "      <td>tet(B)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tetracycline efflux MFS transporter Tet(B)</td>\n",
       "      <td>219</td>\n",
       "      <td>401</td>\n",
       "      <td>54.61</td>\n",
       "      <td>100.0</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MZ254710</td>\n",
       "      <td>blaSHV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SHV family class A beta-lactamase</td>\n",
       "      <td>265</td>\n",
       "      <td>286</td>\n",
       "      <td>92.66</td>\n",
       "      <td>100.0</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MZ254711</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MZ254712</td>\n",
       "      <td>blaNDM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NDM family subclass B1 metallo-beta-lactamase</td>\n",
       "      <td>158</td>\n",
       "      <td>270</td>\n",
       "      <td>58.52</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MZ254713</td>\n",
       "      <td>blaTEM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEM family class A beta-lactamase</td>\n",
       "      <td>274</td>\n",
       "      <td>286</td>\n",
       "      <td>94.41</td>\n",
       "      <td>100.0</td>\n",
       "      <td>270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MZ275235</td>\n",
       "      <td>aac(6')-Ib-cr</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fluoroquinolone-acetylating aminoglycoside 6'-...</td>\n",
       "      <td>159</td>\n",
       "      <td>184</td>\n",
       "      <td>86.41</td>\n",
       "      <td>100.0</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>MZ275236</td>\n",
       "      <td>qnrB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QnrB family quinolone resistance pentapeptide ...</td>\n",
       "      <td>158</td>\n",
       "      <td>214</td>\n",
       "      <td>73.83</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MZ275237</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>MZ275238</td>\n",
       "      <td>blaCTX-M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CTX-M family class A extended-spectrum beta-la...</td>\n",
       "      <td>180</td>\n",
       "      <td>291</td>\n",
       "      <td>61.86</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MZ275239</td>\n",
       "      <td>sul1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sulfonamide-resistant dihydropteroate synthase...</td>\n",
       "      <td>145</td>\n",
       "      <td>275</td>\n",
       "      <td>52.73</td>\n",
       "      <td>100.0</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>MZ275240</td>\n",
       "      <td>blaNDM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NDM family subclass B1 metallo-beta-lactamase</td>\n",
       "      <td>158</td>\n",
       "      <td>270</td>\n",
       "      <td>58.52</td>\n",
       "      <td>100.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   accession           gene    allele  \\\n",
       "0   MW116081           sul1       NaN   \n",
       "1   MW207171  aac(6')-Ib-cr       NaN   \n",
       "2   MZ254707         blaCMY  blaCMY-6   \n",
       "3   MZ254708           qnrS       NaN   \n",
       "4   MZ254709         tet(B)       NaN   \n",
       "5   MZ254710         blaSHV       NaN   \n",
       "6   MZ254711       blaCTX-M       NaN   \n",
       "7   MZ254712         blaNDM       NaN   \n",
       "8   MZ254713         blaTEM       NaN   \n",
       "9   MZ275235  aac(6')-Ib-cr       NaN   \n",
       "10  MZ275236           qnrB       NaN   \n",
       "11  MZ275237       blaCTX-M       NaN   \n",
       "12  MZ275238       blaCTX-M       NaN   \n",
       "13  MZ275239           sul1       NaN   \n",
       "14  MZ275240         blaNDM       NaN   \n",
       "\n",
       "                                         protein-name  Target length  \\\n",
       "0   sulfonamide-resistant dihydropteroate synthase...            145   \n",
       "1   fluoroquinolone-acetylating aminoglycoside 6'-...            160   \n",
       "2                        class C beta-lactamase CMY-6            381   \n",
       "3   QnrS family quinolone resistance pentapeptide ...            139   \n",
       "4          tetracycline efflux MFS transporter Tet(B)            219   \n",
       "5                   SHV family class A beta-lactamase            265   \n",
       "6   CTX-M family class A extended-spectrum beta-la...            180   \n",
       "7       NDM family subclass B1 metallo-beta-lactamase            158   \n",
       "8                   TEM family class A beta-lactamase            274   \n",
       "9   fluoroquinolone-acetylating aminoglycoside 6'-...            159   \n",
       "10  QnrB family quinolone resistance pentapeptide ...            158   \n",
       "11  CTX-M family class A extended-spectrum beta-la...            180   \n",
       "12  CTX-M family class A extended-spectrum beta-la...            180   \n",
       "13  sulfonamide-resistant dihydropteroate synthase...            145   \n",
       "14      NDM family subclass B1 metallo-beta-lactamase            158   \n",
       "\n",
       "    RefSeq length    %Cov  %Iden  Align Length  \n",
       "0             275   52.73  100.0           145  \n",
       "1             184   86.96  100.0           160  \n",
       "2             381  100.00  100.0           381  \n",
       "3             218   63.76  100.0           139  \n",
       "4             401   54.61  100.0           219  \n",
       "5             286   92.66  100.0           265  \n",
       "6             291   61.86  100.0           180  \n",
       "7             270   58.52  100.0           158  \n",
       "8             286   94.41  100.0           270  \n",
       "9             184   86.41  100.0           159  \n",
       "10            214   73.83  100.0           158  \n",
       "11            291   61.86  100.0           180  \n",
       "12            291   61.86  100.0           180  \n",
       "13            275   52.73  100.0           145  \n",
       "14            270   58.52  100.0           158  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AMRresults['gene'],AMRresults['allele'] = np.where(~pd.isnull(AMRresults['allele']), (AMRresults['allele'],AMRresults['gene']),(AMRresults['gene'],AMRresults['allele']))\n",
    "AMRresults"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check for table rows where the Targe Length = RefSeq length AND %Cov = %Iden\n",
    "Empty dataframe returns indicates none are found"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "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>accession</th>\n",
       "      <th>gene</th>\n",
       "      <th>allele</th>\n",
       "      <th>protein-name</th>\n",
       "      <th>Target length</th>\n",
       "      <th>RefSeq length</th>\n",
       "      <th>%Cov</th>\n",
       "      <th>%Iden</th>\n",
       "      <th>Align Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MZ254707</td>\n",
       "      <td>blaCMY</td>\n",
       "      <td>blaCMY-6</td>\n",
       "      <td>class C beta-lactamase CMY-6</td>\n",
       "      <td>381</td>\n",
       "      <td>381</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>381</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  accession    gene    allele                  protein-name  Target length  \\\n",
       "2  MZ254707  blaCMY  blaCMY-6  class C beta-lactamase CMY-6            381   \n",
       "\n",
       "   RefSeq length   %Cov  %Iden  Align Length  \n",
       "2            381  100.0  100.0           381  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ExactMatch = AMRresults.loc[(AMRresults['Target length'] == AMRresults['RefSeq length']) & (AMRresults['%Cov'] == AMRresults['%Iden'])]\n",
    "ExactMatch"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check for partial match scenario where Target Length = RefSeq Length but %Cov not equal to %Iden of vice-versa\n",
    "\n",
    "Empty dataframe returns indicates none are found"
   ]
  },
  {
   "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",
       "      <th>accession</th>\n",
       "      <th>gene</th>\n",
       "      <th>allele</th>\n",
       "      <th>protein-name</th>\n",
       "      <th>Target length</th>\n",
       "      <th>RefSeq length</th>\n",
       "      <th>%Cov</th>\n",
       "      <th>%Iden</th>\n",
       "      <th>Align Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [accession, gene, allele, protein-name, Target length, RefSeq length, %Cov, %Iden, Align Length]\n",
       "Index: []"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "LengthMatch = AMRresults.loc[(AMRresults['Target length'] == AMRresults['RefSeq length']) & (AMRresults['%Cov'] != AMRresults['%Iden'])]\n",
    "IdentMatch = AMRresults.loc[(AMRresults['Target length'] != AMRresults['RefSeq length']) & (AMRresults['%Cov'] == AMRresults['%Iden'])]\n",
    "NotMatch = [LengthMatch, IdentMatch]\n",
    "NotExactMatch = pd.concat(NotMatch)\n",
    "NotExactMatch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save final AMRtable file to your directory in csv format that can be read by excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "AMRresults.to_csv(r'AMRtable', sep ='\\t', index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}