NLP_Query.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO:tensorflow:Saver not created because there are no variables in the graph to restore\n"
     ]
    }
   ],
   "source": [
    "#Create a tensor-flow session for USE(Universal Setence Encoder)\n",
    "\n",
    "import tensorflow as tf\n",
    "import tensorflow_hub as hub\n",
    "\n",
    "\n",
    "g = tf.Graph()\n",
    "with g.as_default():\n",
    "  text_input = tf.placeholder(dtype=tf.string, shape=[None])\n",
    "  \n",
    "  \n",
    "  #Please download USE(Universal Sentence Encoder) from below url and unzip and place it locally\n",
    "  #https://tfhub.dev/google/universal-sentence-encoder-large/3?tf-hub-format=compressed\n",
    "\n",
    "  embed = hub.Module(\"C:\\\\Users\\\\user_name\\\\Downloads\\\\3\")#Enter your location\n",
    "    \n",
    "    \n",
    "  embedded_text = embed(text_input)\n",
    "  init_op = tf.group([tf.global_variables_initializer(), tf.tables_initializer()])\n",
    "g.finalize()\n",
    "\n",
    "session = tf.Session(graph=g)\n",
    "session.run(init_op)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "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>applicant_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>country_code</th>\n",
       "      <th>country_name</th>\n",
       "      <th>department</th>\n",
       "      <th>favourite_subject</th>\n",
       "      <th>class_head</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>nikhil</td>\n",
       "      <td>kumar</td>\n",
       "      <td>01-FEB-1991</td>\n",
       "      <td>IND</td>\n",
       "      <td>India</td>\n",
       "      <td>AID</td>\n",
       "      <td>AI</td>\n",
       "      <td>Prakash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10002</td>\n",
       "      <td>janu</td>\n",
       "      <td>anjali</td>\n",
       "      <td>01-MAR-1995</td>\n",
       "      <td>USA</td>\n",
       "      <td>USA</td>\n",
       "      <td>ECE</td>\n",
       "      <td>LA</td>\n",
       "      <td>BRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10003</td>\n",
       "      <td>prathul</td>\n",
       "      <td>nair</td>\n",
       "      <td>04-JAN-1993</td>\n",
       "      <td>THA</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>CSE</td>\n",
       "      <td>AI</td>\n",
       "      <td>BRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10004</td>\n",
       "      <td>dev</td>\n",
       "      <td>nair</td>\n",
       "      <td>01-JAN-1997</td>\n",
       "      <td>USA</td>\n",
       "      <td>USA</td>\n",
       "      <td>AID</td>\n",
       "      <td>LA</td>\n",
       "      <td>BRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10005</td>\n",
       "      <td>anand</td>\n",
       "      <td>krishnan</td>\n",
       "      <td>07-DEC-1995</td>\n",
       "      <td>IND</td>\n",
       "      <td>India</td>\n",
       "      <td>CSE</td>\n",
       "      <td>FOD</td>\n",
       "      <td>Ganesh</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  applicant_id first_name last_name date_of_birth country_code country_name  \\\n",
       "0        10001     nikhil     kumar   01-FEB-1991          IND        India   \n",
       "1        10002       janu    anjali   01-MAR-1995          USA          USA   \n",
       "2        10003    prathul      nair   04-JAN-1993          THA     Thailand   \n",
       "3        10004        dev      nair   01-JAN-1997          USA          USA   \n",
       "4        10005      anand  krishnan   07-DEC-1995          IND        India   \n",
       "\n",
       "  department favourite_subject class_head  \n",
       "0        AID                AI    Prakash  \n",
       "1        ECE                LA        BRT  \n",
       "2        CSE                AI        BRT  \n",
       "3        AID                LA        BRT  \n",
       "4        CSE               FOD     Ganesh  "
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "#class_data.csv can be found in my GITHUB\n",
    "df=pd.read_csv(\"class_data.csv\")\n",
    "\n",
    "df['applicant_id']=df['applicant_id'].astype(str)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['applicant_id',\n",
       " 'first_name',\n",
       " 'last_name',\n",
       " 'date_of_birth',\n",
       " 'country_code',\n",
       " 'country_name',\n",
       " 'department',\n",
       " 'favourite_subject',\n",
       " 'class_head']"
      ]
     },
     "execution_count": 282,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns=list(df.columns)\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['applicant id',\n",
       " 'first name',\n",
       " 'last name',\n",
       " 'date of birth',\n",
       " 'country code',\n",
       " 'country name',\n",
       " 'department',\n",
       " 'favourite subject',\n",
       " 'class head']"
      ]
     },
     "execution_count": 283,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Replace _ with space for later comparision\n",
    "dictionary=[]\n",
    "for d in columns:\n",
    "        dictionary.append(d.replace(\"_\",\" \"))\n",
    "dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Generate all n-grams, here we create uni,bi,tri grams only\n",
    "from nltk import ngrams\n",
    "\n",
    "#text=input(\"Enter your query in plain English?\")\n",
    "text=\"what is the name and department of applicant 10003?\"\n",
    "\n",
    "text=text.replace(\"?\",\" \")\n",
    "text=text.replace(\",\",\" \")\n",
    "text=text.replace(\".\",\" \")\n",
    "\n",
    "unigram=list(ngrams(text.lower().split(),1))\n",
    "bigram=list(ngrams(text.lower().split(),2))\n",
    "trigram=list(ngrams(text.lower().split(),3))\n",
    "fourgram=list(ngrams(text.lower().split(),4))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['what is the',\n",
       " 'is the name',\n",
       " 'the name and',\n",
       " 'name and department',\n",
       " 'and department of',\n",
       " 'department of applicant',\n",
       " 'of applicant 10003',\n",
       " 'what is',\n",
       " 'is the',\n",
       " 'the name',\n",
       " 'name and',\n",
       " 'and department',\n",
       " 'department of',\n",
       " 'of applicant',\n",
       " 'applicant 10003',\n",
       " 'what',\n",
       " 'is',\n",
       " 'the',\n",
       " 'name',\n",
       " 'and',\n",
       " 'department',\n",
       " 'of',\n",
       " 'applicant',\n",
       " '10003']"
      ]
     },
     "execution_count": 285,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#append all generated grams to a list\n",
    "all_grams=[]\n",
    "for line in trigram[0:100]:\n",
    "    new_line=\"\"\n",
    "    for word in line:\n",
    "        new_line=new_line+word+\" \"\n",
    "    new_line=new_line.rstrip(\" \")\n",
    "    all_grams.append(new_line)\n",
    "for line in bigram[0:100]:\n",
    "    new_line=\"\"\n",
    "    for word in line:\n",
    "        new_line=new_line+word+\" \"\n",
    "    new_line=new_line.rstrip(\" \")\n",
    "    all_grams.append(new_line)\n",
    "for line in unigram[0:100]:\n",
    "    new_line=\"\"\n",
    "    for word in line:\n",
    "        new_line=new_line+word+\" \"\n",
    "    new_line=new_line.rstrip(\" \")\n",
    "    all_grams.append(new_line)\n",
    "    \n",
    "all_grams"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(24, 512)\n",
      "(9, 512)\n"
     ]
    }
   ],
   "source": [
    "#Encode all grams to USE(Universal Sentence Encoder) vector\n",
    "#Encode all column names to USE(Universal Sentence Encoder) vector\n",
    "query_v = session.run(embedded_text, feed_dict={text_input: all_grams})\n",
    "dict_v=session.run(embedded_text, feed_dict={text_input: dictionary})\n",
    "print(query_v.shape)\n",
    "print(dict_v.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[0.18597080647945405, 0.2527309137582779, 0.21155848145484923, 0.16174248576164246, 0.2525496330857277, 0.40213613033294676, 0.2727108496427536, 0.29220599234104155, 0.3084959614276886], [0.2740073096752167, 0.6109123706817627, 0.6153407442569733, 0.29992903232574464, 0.3096180260181427, 0.5244617807865143, 0.3501835548877716, 0.2054035869240761, 0.27221714973449707], [0.4077827489376068, 0.7252013611793519, 0.6719449388980866, 0.34115159153938296, 0.3990750449895859, 0.5641072726249695, 0.37919980108737944, 0.23688327133655548, 0.4247922170162201], [0.5035235971212387, 0.5184328722953796, 0.5343469047546386, 0.3410661721229553, 0.4172597140073776, 0.5247188901901245, 0.7480796599388122, 0.37803934335708617, 0.2644855070114136], [0.383610999584198, 0.400233678817749, 0.39072604179382325, 0.197769872546196, 0.29475352764129636, 0.36940900325775144, 0.7636963963508606, 0.43474850356578826, 0.30067142128944396], [0.7659382152557374, 0.4354214322566986, 0.46668302357196806, 0.396395246386528, 0.39701321482658386, 0.2848152673244476, 0.7267091202735901, 0.3030637121200562, 0.4256829500198364], [0.7873755097389221, 0.37901665091514586, 0.38459274530410764, 0.3362384855747223, 0.3638675630092621, 0.24816419720649718, 0.4663385188579559, 0.18753349542617798, 0.4764120215177536], [0.1586079317331314, 0.23852997809648513, 0.18576947525143622, 0.17537119045853614, 0.18516676098108292, 0.3112399816513062, 0.2608325582742691, 0.2723878401517868, 0.27814519345760347], [0.24737490445375443, 0.4039520561695099, 0.41802568346261976, 0.1994125020503998, 0.32614543199539187, 0.3827516001462936, 0.4872643345594406, 0.36295851588249206, 0.24904207229614259], [0.3735688275098801, 0.6744426715373993, 0.6191287112236022, 0.3429765820503235, 0.38602793246507644, 0.4436242213845253, 0.3755525305867195, 0.20661379903554916, 0.5235909366607666], [0.4225024777650833, 0.7178555572032929, 0.6815127611160279, 0.356684008538723, 0.3639261215925217, 0.6161103689670563, 0.38724215030670167, 0.25694278091192246, 0.30744185984134675], [0.3459611818194389, 0.3293366104364395, 0.31638021767139435, 0.1403098413348198, 0.24185702949762344, 0.18547878116369249, 0.7536132025718689, 0.239290192425251, 0.33262997955083845], [0.40421031713485717, 0.43807859390974047, 0.4382855036854744, 0.2509766666591167, 0.359741330742836, 0.46131106406450273, 0.8543556880950928, 0.4835905572772026, 0.33047581821680067], [0.843899490237236, 0.439056042432785, 0.4569931995868683, 0.3879525184631348, 0.3651848578453064, 0.24682530224323274, 0.5316215282678605, 0.22038643792271614, 0.44813779830932615], [0.7611645966768265, 0.37520592510700224, 0.374903958439827, 0.31716987013816833, 0.3757276543974876, 0.2625079134106636, 0.41498368233442307, 0.17369642227888107, 0.45264881670475005], [0.1412755846977234, 0.25362250208854675, 0.22635215520858765, 0.1235051080584526, 0.2393307238817215, 0.27433282136917114, 0.22171267867088318, 0.08681172132492065, 0.34221187233924866], [0.18318384885787964, 0.3872876763343811, 0.3872736096382141, 0.1883503794670105, 0.23968756198883057, 0.3034077286720276, 0.467502236366272, 0.3081667721271515, 0.22801151871681213], [0.2255750298500061, 0.4072600305080414, 0.40658384561538696, 0.15146979689598083, 0.30876582860946655, 0.35674965381622314, 0.4888594448566437, 0.31523260474205017, 0.23897427320480347], [0.3328342139720917, 0.6511850357055664, 0.5984483957290649, 0.3244597613811493, 0.3358411192893982, 0.6393309831619263, 0.4240046739578247, 0.30592143535614014, 0.2865605652332306], [0.16893254220485687, 0.2743944525718689, 0.2488018125295639, 0.1127396821975708, 0.14575409889221191, 0.2253260314464569, 0.29255783557891846, 0.09402970969676971, 0.32213664054870605], [0.42257511615753174, 0.4185689687728882, 0.4218866229057312, 0.2308243215084076, 0.3447688817977905, 0.33152955770492554, 1.0, 0.3653297424316406, 0.39554327726364136], [0.18619436025619507, 0.40163567662239075, 0.3724559545516968, 0.15407714247703552, 0.23856180906295776, 0.3575546145439148, 0.5017089247703552, 0.38720011711120605, 0.2337675839662552], [0.809361457824707, 0.4482078552246094, 0.4533078372478485, 0.38664931058883667, 0.4028434753417969, 0.2834489643573761, 0.5462090969085693, 0.25429031252861023, 0.5011126399040222], [0.3021528720855713, 0.353861927986145, 0.3266756534576416, 0.1741504669189453, 0.30792760848999023, 0.24877575039863586, 0.3539530038833618, 0.1418619155883789, 0.5709357261657715]]\n"
     ]
    }
   ],
   "source": [
    "#Generate cosine similarit matrix between above two vectors\n",
    "from sklearn.metrics.pairwise import cosine_similarity\n",
    "\n",
    "result=[]\n",
    "r=[]\n",
    "\n",
    "for i,ii in enumerate(query_v):\n",
    "    r=[]\n",
    "    weight=all_grams[i].count(\" \")\n",
    "    for j,jj in enumerate(dict_v):\n",
    "        similarity=cosine_similarity(query_v[i].reshape(1,512), dict_v[j].reshape(1,512))[0][0]\n",
    "        #adding a small weight based on length of gram\n",
    "        r.append(similarity+0.01*weight*similarity)\n",
    "    result.append(r)\n",
    "    \n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "n-gram value ==> actual column name == > similarity\n",
      "---------------------------------------\n",
      "department ==> department ==> 1.0\n",
      "of applicant ==> applicant id ==> 0.843899490237236\n",
      "the name and ==> first name ==> 0.7252013611793519\n",
      "name and ==> last name ==> 0.6815127611160279\n",
      "\n",
      "-------------------\n",
      "Text without identified n-grams:  what is    10003 \n"
     ]
    }
   ],
   "source": [
    "#Order the similarity matrix in descending\n",
    "import bisect \n",
    "ordered_arr=[]\n",
    "ordered_index=[]\n",
    "\n",
    "for i,ii in enumerate(result):\n",
    "    for j,jj in enumerate(ii):\n",
    "        #matched greater than 65% is filtered out, you may change this as per your data\n",
    "        if(jj>=0.65):\n",
    "            bisect.insort(ordered_arr, jj)\n",
    "            loc=ordered_arr.index(jj)\n",
    "            ordered_index.insert(loc,[i,j,jj])\n",
    "\n",
    "\n",
    "ordered_arr.sort(reverse=True)\n",
    "ordered_index.reverse()\n",
    "\n",
    "\n",
    "text_remain=text\n",
    "list_dum=[]\n",
    "list_map=[]\n",
    "\n",
    "print(\"n-gram value ==> actual column name == > similarity\")\n",
    "print(\"---------------------------------------\")\n",
    "#remove identified n-grams from actual text and put in text_remain\n",
    "#this will help easily identify value in the query like (India,10001,prathul etc)\n",
    "for i,j in enumerate(ordered_index):\n",
    "   if(dictionary[ordered_index[i][1]] not in list_dum ):\n",
    "    print(all_grams[ordered_index[i][0]]+\" ==> \"+dictionary[ordered_index[i][1]]+\" ==> \"+str(ordered_index[i][2]))\n",
    "    list_map.append([all_grams[ordered_index[i][0]],dictionary[ordered_index[i][1]].replace(\" \",\"_\"),str(ordered_index[i][2])])\n",
    "    list_dum.append(dictionary[ordered_index[i][1]])\n",
    "    text_remain=text_remain.replace(all_grams[ordered_index[i][0]],\"\")\n",
    "    \n",
    "print(\"\\n-------------------\")\n",
    "print(\"Text without identified n-grams:  \"+text_remain)\n",
    "col_list=[]\n",
    "for e in list_dum:\n",
    "    e=e.replace(\" \",\"_\")\n",
    "    col_list.append(e)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['   ', '10003']"
      ]
     },
     "execution_count": 289,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Remove stop words(the,is,of etc)\n",
    "from spacy.lang.en import English\n",
    "from spacy.lang.en.stop_words import STOP_WORDS\n",
    "\n",
    "nlp = English()\n",
    "\n",
    "my_doc = nlp(text_remain)\n",
    "\n",
    "token_list = []\n",
    "for token in my_doc:\n",
    "    token_list.append(token.text)\n",
    "\n",
    "filtered_sentence =[] \n",
    "\n",
    "for word in token_list:\n",
    "    lexeme = nlp.vocab[word]\n",
    "    if lexeme.is_stop == False:\n",
    "        if word!=\"\":\n",
    "            filtered_sentence.append(word)\n",
    "\n",
    "filtered_sentence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "what is the name and department of applicant 10003 \n",
      "For applicant_id 10003\n",
      "  department first_name last_name\n",
      "2        CSE    prathul      nair\n"
     ]
    }
   ],
   "source": [
    "#find the values in the filtered sentence is matching with any features\n",
    "q_str=\"\"\n",
    "qry_col=col_list.copy()\n",
    "q=\"\"\n",
    "answer=\"\"\n",
    "df_ans=df\n",
    "for e in filtered_sentence:\n",
    "    for col in col_list:\n",
    "            if(len(df_ans.loc[(df_ans[col]==e)].values)>0):\n",
    "                \n",
    "                df_ans=df_ans.loc[df_ans[col]==e]\n",
    "                if answer==\"\":\n",
    "                    answer=\"For \"+col+\" \"+e\n",
    "                else :\n",
    "                    answer=answer +\" and \"+col+\" \"+e\n",
    "                for i,temp in enumerate(list_map):\n",
    "                    if(list_map[i][1]==col):\n",
    "                        for j,temp1 in enumerate(list_map):\n",
    "                            if(list_map[i][0]==list_map[j][0]):\n",
    "                                qry_col.remove(list_map[j][1])\n",
    "df_ans=df_ans[qry_col]\n",
    "print(text)\n",
    "print(answer)\n",
    "print(df_ans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Important: This function is all the above code wrapped inside\n",
    "#It is the same code\n",
    "#This is done so we can call this function and expose via api as done in next cell\n",
    "def nlp_query(txt):\n",
    "    from nltk import ngrams\n",
    "    text=txt\n",
    "\n",
    "    text=text.replace(\"?\",\" \")\n",
    "    text=text.replace(\",\",\" \")\n",
    "    text=text.replace(\".\",\" \")\n",
    "\n",
    "    unigram=list(ngrams(text.lower().split(),1))\n",
    "    bigram=list(ngrams(text.lower().split(),2))\n",
    "    trigram=list(ngrams(text.lower().split(),3))\n",
    "    fourgram=list(ngrams(text.lower().split(),4))\n",
    "    \n",
    "    all_grams=[]\n",
    "    for line in trigram[0:100]:\n",
    "        new_line=\"\"\n",
    "        for word in line:\n",
    "            new_line=new_line+word+\" \"\n",
    "        new_line=new_line.rstrip(\" \")\n",
    "        all_grams.append(new_line)\n",
    "    for line in bigram[0:100]:\n",
    "        new_line=\"\"\n",
    "        for word in line:\n",
    "            new_line=new_line+word+\" \"\n",
    "        new_line=new_line.rstrip(\" \")\n",
    "        all_grams.append(new_line)\n",
    "    for line in unigram[0:100]:\n",
    "        new_line=\"\"\n",
    "        for word in line:\n",
    "            new_line=new_line+word+\" \"\n",
    "        new_line=new_line.rstrip(\" \")\n",
    "        all_grams.append(new_line)\n",
    "        \n",
    "    print(all_grams)\n",
    "    \n",
    "    query_v = session.run(embedded_text, feed_dict={text_input: all_grams})\n",
    "    dict_v=session.run(embedded_text, feed_dict={text_input: dictionary})\n",
    "    print(query_v.shape)\n",
    "    print(dict_v.shape)\n",
    "    \n",
    "    from sklearn.metrics.pairwise import cosine_similarity\n",
    "\n",
    "    result=[]\n",
    "    r=[]\n",
    "\n",
    "    for i,ii in enumerate(query_v):\n",
    "        r=[]\n",
    "        weight=all_grams[i].count(\" \")\n",
    "        for j,jj in enumerate(dict_v):\n",
    "            similarity=cosine_similarity(query_v[i].reshape(1,512), dict_v[j].reshape(1,512))[0][0]\n",
    "            r.append(similarity+0.01*weight*similarity)\n",
    "        result.append(r)\n",
    "\n",
    "    print(result)\n",
    "    \n",
    "    import bisect \n",
    "    ordered_arr=[]\n",
    "    ordered_index=[]\n",
    "\n",
    "    for i,ii in enumerate(result):\n",
    "        for j,jj in enumerate(ii):\n",
    "            if(jj>=0.65):\n",
    "                bisect.insort(ordered_arr, jj)\n",
    "                loc=ordered_arr.index(jj)\n",
    "                ordered_index.insert(loc,[i,j,jj])\n",
    "\n",
    "\n",
    "    ordered_arr.sort(reverse=True)\n",
    "    ordered_index.reverse()\n",
    "\n",
    "\n",
    "    text_remain=text\n",
    "    list_dum=[]\n",
    "    list_map=[]\n",
    "    for i,j in enumerate(ordered_index):\n",
    "       if(dictionary[ordered_index[i][1]] not in list_dum ):\n",
    "        print(all_grams[ordered_index[i][0]]+\" ==> \"+dictionary[ordered_index[i][1]]+\" ==> \"+str(ordered_index[i][2]))\n",
    "        list_map.append([all_grams[ordered_index[i][0]],dictionary[ordered_index[i][1]].replace(\" \",\"_\"),str(ordered_index[i][2])])\n",
    "        list_dum.append(dictionary[ordered_index[i][1]])\n",
    "        text_remain=text_remain.replace(all_grams[ordered_index[i][0]],\"\")\n",
    "\n",
    "    print((list_dum)) \n",
    "    print(\"Text without identified n-grams:  \"+text_remain)\n",
    "    col_list=[]\n",
    "    for e in list_dum:\n",
    "        e=e.replace(\" \",\"_\")\n",
    "        col_list.append(e)\n",
    "\n",
    "    col_list\n",
    "\n",
    "    from spacy.lang.en import English\n",
    "    from spacy.lang.en.stop_words import STOP_WORDS\n",
    "\n",
    "    nlp = English()\n",
    "\n",
    "    my_doc = nlp(text_remain)\n",
    "\n",
    "    token_list = []\n",
    "    for token in my_doc:\n",
    "        token_list.append(token.text)\n",
    "\n",
    "    filtered_sentence =[] \n",
    "\n",
    "    for word in token_list:\n",
    "        lexeme = nlp.vocab[word]\n",
    "        if lexeme.is_stop == False:\n",
    "            if word!=\"\":\n",
    "                filtered_sentence.append(word)\n",
    "\n",
    "    filtered_sentence\n",
    "    q_str=\"\"\n",
    "    qry_col=col_list.copy()\n",
    "    q=\"\"\n",
    "    answer=\"\"\n",
    "    df_ans=df\n",
    "    for e in filtered_sentence:\n",
    "        for col in col_list:\n",
    "                if(len(df_ans.loc[(df_ans[col]==e)].values)>0):\n",
    "\n",
    "                    df_ans=df_ans.loc[df_ans[col]==e]\n",
    "                    if answer==\"\":\n",
    "                        answer=\"For \"+col+\" \"+e\n",
    "                    else :\n",
    "                        answer=answer +\" and \"+col+\" \"+e\n",
    "                    for i,temp in enumerate(list_map):\n",
    "                        if(list_map[i][1]==col):\n",
    "                            for j,temp1 in enumerate(list_map):\n",
    "                                if(list_map[i][0]==list_map[j][0]):\n",
    "                                    qry_col.remove(list_map[j][1])\n",
    "    df_ans=df_ans[qry_col]\n",
    "    print(answer)\n",
    "    print(df_ans)\n",
    "    \n",
    "    html_txt= \"<br>\"+\"User Question:<br>&nbsp <b> \" + txt+\"</b><br><br>Computer response:<br>&nbsp<b>\"+answer+\"</b>  we have <br>\"\n",
    "    html_txt=html_txt+\"<br>\"+df_ans.to_html()\n",
    "    html_txt=html_txt+'<a href=\"http://127.0.0.1:5000/\"> \\\n",
    "    <button>Go Back</button>\\\n",
    "    </a> <br><br><br>---------------- by himeshph    '\n",
    "    return html_txt\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * Serving Flask app \"__main__\" (lazy loading)\n",
      " * Environment: development\n",
      " * Debug mode: off\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)\n",
      "127.0.0.1 - - [03/Dec/2019 13:40:53] \"GET / HTTP/1.1\" 200 -\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['which applicant born', 'applicant born on', 'born on 04-jan-1993', 'on 04-jan-1993 and', '04-jan-1993 and what', 'and what is', 'what is his', 'is his name', 'which applicant', 'applicant born', 'born on', 'on 04-jan-1993', '04-jan-1993 and', 'and what', 'what is', 'is his', 'his name', 'which', 'applicant', 'born', 'on', '04-jan-1993', 'and', 'what', 'is', 'his', 'name']\n",
      "(27, 512)\n",
      "(9, 512)\n",
      "[[0.7085240256786346, 0.43819023728370665, 0.4609634184837341, 0.4845591187477112, 0.3371748769283295, 0.4191902232170105, 0.2935005068778992, 0.29032056391239164, 0.13521308451890945], [0.7909287142753602, 0.4826393401622772, 0.5124936997890472, 0.6579589533805847, 0.46951703310012816, 0.41183910727500916, 0.3584689950942993, 0.24009853720664978, 0.3110657787322998], [0.3546977126598358, 0.44052936136722565, 0.4363837230205536, 0.5751469874382019, 0.2779542946815491, 0.33157501101493836, 0.13831096708774568, 0.22964753240346908, 0.31066847205162046], [0.26333590149879454, 0.23632337898015976, 0.2104082527756691, 0.1308540040254593, 0.3100810843706131, 0.2589568340778351, 0.3377215003967285, 0.12417083904147148, 0.4549876463413238], [0.22964724361896516, 0.29171265721321105, 0.24427668660879134, 0.12942734777927398, 0.23674816578626634, 0.2980195587873459, 0.30003840565681456, 0.2076713812351227, 0.4455443930625915], [0.13681047320365905, 0.19271725326776504, 0.13776916176080703, 0.12471081286668778, 0.13890472322702407, 0.2883578324317932, 0.2083990877866745, 0.2598396027088165, 0.28055025458335875], [0.13626734554767608, 0.302925369143486, 0.2812593877315521, 0.16172193646430968, 0.10635190799832345, 0.3690395414829254, 0.25227338790893555, 0.341928026676178, 0.23793523728847504], [0.25070609360933305, 0.6298270869255066, 0.6519687724113464, 0.27518734395503996, 0.23835783541202546, 0.44287459552288055, 0.34649994134902956, 0.20628703951835634, 0.2876357191801071], [0.6927431398630142, 0.3677813136577606, 0.3795519334077835, 0.2901488199830055, 0.301783085167408, 0.3511164379119873, 0.41850325554609297, 0.31884296774864196, 0.22254145711660386], [0.7520018708705902, 0.43800731629133227, 0.4424684885144234, 0.5777388095855713, 0.40001107811927794, 0.33340277582406996, 0.36331767320632935, 0.19904312565922738, 0.3220041060447693], [0.35084035754203796, 0.4272108942270279, 0.41973535299301146, 0.574908173084259, 0.3779307287931442, 0.4455804127454758, 0.3213068014383316, 0.3217820256948471, 0.37791435420513153], [0.27628466099500654, 0.24151605278253555, 0.21578904658555983, 0.15156788229942322, 0.2647310045361519, 0.2065086133778095, 0.31872377038002014, 0.13002816528081895, 0.5286520689725875], [0.3423367992043495, 0.36981964886188506, 0.35754280149936674, 0.18963452517986298, 0.39889856934547424, 0.3683674275875092, 0.3849701762199402, 0.1698593807220459, 0.426915639936924], [0.17076863691210747, 0.23107716262340547, 0.17544097810983658, 0.10737068697810173, 0.1526816402375698, 0.22386870175600052, 0.21808192536234855, 0.11724183194339276, 0.38357984125614164], [0.1586079317331314, 0.23852997809648513, 0.18576947525143622, 0.17537119045853614, 0.18516676098108292, 0.3112399816513062, 0.2608325582742691, 0.2723878401517868, 0.27814519345760347], [0.17142508029937745, 0.40547155171632765, 0.44796893537044524, 0.2182128769159317, 0.2003240007162094, 0.28892069578170776, 0.3907958573102951, 0.20765156865119935, 0.2696084946393967], [0.3698022508621216, 0.830092762708664, 0.8069987541437149, 0.36378061652183535, 0.35442566990852353, 0.4299108350276947, 0.38858685314655306, 0.23823863685131072, 0.47086741209030153], [0.18050576746463776, 0.32841768860816956, 0.26884526014328003, 0.15743835270404816, 0.19275379180908203, 0.364524245262146, 0.3660823702812195, 0.28265202045440674, 0.3063202500343323], [0.809361457824707, 0.4482078552246094, 0.4533078372478485, 0.38664931058883667, 0.4028434753417969, 0.2834489643573761, 0.5462090969085693, 0.25429031252861023, 0.5011126399040222], [0.23618385195732117, 0.2862446904182434, 0.2783857583999634, 0.5100551843643188, 0.23723764717578888, 0.2900814414024353, 0.2283623218536377, 0.19628843665122986, 0.07019209861755371], [0.22459201514720917, 0.3596966862678528, 0.32285988330841064, 0.14408430457115173, 0.23229600489139557, 0.32914525270462036, 0.5058797597885132, 0.3314152956008911, 0.29241541028022766], [0.31241774559020996, 0.35800617933273315, 0.328233003616333, 0.1812063753604889, 0.3034650683403015, 0.2456386387348175, 0.3618064224720001, 0.14195884764194489, 0.571861743927002], [0.16893254220485687, 0.2743944525718689, 0.2488018125295639, 0.1127396821975708, 0.14575409889221191, 0.2253260314464569, 0.29255783557891846, 0.09402970969676971, 0.32213664054870605], [0.1412755846977234, 0.25362250208854675, 0.22635215520858765, 0.1235051080584526, 0.2393307238817215, 0.27433282136917114, 0.22171267867088318, 0.08681172132492065, 0.34221187233924866], [0.18318384885787964, 0.3872876763343811, 0.3872736096382141, 0.1883503794670105, 0.23968757688999176, 0.30340775847435, 0.467502236366272, 0.3081667721271515, 0.22801153361797333], [0.1644582748413086, 0.4264659881591797, 0.42126357555389404, 0.1189938634634018, 0.19750574231147766, 0.275343120098114, 0.4340352416038513, 0.22385457158088684, 0.2776011824607849], [0.33283427357673645, 0.651185154914856, 0.5984485149383545, 0.32445982098579407, 0.33584120869636536, 0.6393311023712158, 0.42400479316711426, 0.3059214949607849, 0.286560595035553]]\n",
      "his name ==> first name ==> 0.830092762708664\n",
      "applicant ==> applicant id ==> 0.809361457824707\n",
      "his name ==> last name ==> 0.8069987541437149\n",
      "applicant born on ==> date of birth ==> 0.6579589533805847\n",
      "['first name', 'applicant id', 'last name', 'date of birth']\n",
      "Text without identified n-grams:  which  born on 04-JAN-1993 and what is  \n",
      "For date_of_birth 04-JAN-1993\n",
      "  first_name applicant_id last_name\n",
      "2    prathul        10003      nair\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "127.0.0.1 - - [03/Dec/2019 13:41:11] \"POST /submit HTTP/1.1\" 200 -\n"
     ]
    }
   ],
   "source": [
    "#Create a flask app to call the above function\n",
    "#By default the flask app run on http://127.0.0.1:5000/\n",
    "#Once cell is run go to this url http://127.0.0.1:5000/\n",
    "\n",
    "from flask import Flask,request\n",
    "app = Flask(__name__)\n",
    "\n",
    "@app.route(\"/\")\n",
    "def hello():\n",
    "    html=\"<b><center><font size='50'>Welcome to magic query</font></center></b><br><br>\"\n",
    "    html=html+df.to_html()\n",
    "    html=html+'<br><form method=\"POST\" action=\"http://127.0.0.1:5000/submit\">\\\n",
    "    Please enter your query here: <input type=\"text\" size=\"45\" name=\"txt\" id=\"txt\"/>\\\n",
    "    <br><br><input type=\"submit\" name=\"submit\"/> \\\n",
    "    <br><br>Sample questions:\\\n",
    "    <br> 1. what is the name of applicant 10002?\\\n",
    "    <br> 2. hey computer, tell me favorite subject of name anand?\\\n",
    "    <br> 3. list me names of applicant in department AID?\\\n",
    "    <br> 4. tell me the birth date, country, and department of name anjali? \\\n",
    "    <br> 5. which applicant born on 04-JAN-1993 and what is his name? \\\n",
    "    <br> 6. applicants from country USA and their names and department?\\\n",
    "    <br><br><br>---------------- by himeshph   \\\n",
    "    </form>'\n",
    "    return html\n",
    "\n",
    "@app.route(\"/submit\",methods=['POST'])\n",
    "def submit():\n",
    "    txt=request.form['txt']\n",
    "    html=\"<b><center><font size='50'>Welcome to magic query</font></center></b><br><br>\"\n",
    "    html=html+df.to_html()\n",
    "    html_txt=nlp_query(txt)\n",
    "    html=html+html_txt\n",
    "    return html\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    app.run(debug=False)"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}