Basic Text Search (DEMO 1.2).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basic Phrase & Word Searches via Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL: Import data and create convert to Pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\fuzzywuzzy\\fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning\n",
      "  warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd  \n",
    "\n",
    "# PY-ODBC, an open source Python package that makes accessing ODBC databases simple\n",
    "import pyodbc\n",
    "\n",
    "# to create Object Lists \n",
    "from typing import List\n",
    "\n",
    "# SQL-Alchemy; a SQL toolkit that gives application developers the full power and flexibility of SQL\n",
    "import sqlalchemy\n",
    "\n",
    "# for Regular Expression (RegEx)\n",
    "import re\n",
    "\n",
    "# to tokenize text into words\n",
    "import nltk\n",
    "# nltk.download('punkt')\n",
    "from nltk.tokenize import word_tokenize\n",
    "from nltk.util import ngrams\n",
    "\n",
    "# for creating Lists\n",
    "from typing import List\n",
    "\n",
    "# for Fuzzy Matches\n",
    "from fuzzywuzzy import fuzz\n",
    "\n",
    "# to change dataframe output font colors\n",
    "from IPython.core.display import HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sets up an engine for an SQLAlchemy application, defining how to connect with a database\n",
    "# Takes the general form:  dialect+driver://username:password@host:port/database\n",
    "# DESKTOP-UBSKTQ6\\tshob\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('mssql+pyodbc://tshob:dbmaster@MSFT SQL Server')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format: display full column width\n",
    "pd.set_option('display.max_colwidth', -1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>id</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC123</td>\n",
       "      <td>This practice sentence contains test words and all test words are spelled correctly.\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABC456</td>\n",
       "      <td>This test sentence contains test word and some test words are spelled incorrectly in this practice sentences for test words.\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABC789</td>\n",
       "      <td>Test words practice sentence ONE test word TWO TWO practice sentences TWO TWO test words TWO TWO practice sentence THREE THREE THREE test word THREE THREE THREE practice sentences \\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEF123</td>\n",
       "      <td>\"My money is my monies in my practice sentence account of test words with this test word \"\"my monies is my money account\"\".\"\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEF456</td>\n",
       "      <td>This concludes the practice sentences with my money monies account and test word practice sentences with dot dot test words from a practices sentence dot test word.\\r</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  \\\n",
       "0  ABC123   \n",
       "1  ABC456   \n",
       "2  ABC789   \n",
       "3  DEF123   \n",
       "4  DEF456   \n",
       "\n",
       "                                                                                                                                                                                     text  \n",
       "0  This practice sentence contains test words and all test words are spelled correctly.\\r                                                                                                  \n",
       "1  This test sentence contains test word and some test words are spelled incorrectly in this practice sentences for test words.\\r                                                          \n",
       "2  Test words practice sentence ONE test word TWO TWO practice sentences TWO TWO test words TWO TWO practice sentence THREE THREE THREE test word THREE THREE THREE practice sentences \\r  \n",
       "3  \"My money is my monies in my practice sentence account of test words with this test word \"\"my monies is my money account\"\".\"\\r                                                          \n",
       "4  This concludes the practice sentences with my money monies account and test word practice sentences with dot dot test words from a practices sentence dot test word.\\r                  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query from transcript database table\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT *\n",
    "FROM master.dbo.transcript_row_2\n",
    "ORDER BY id ASC\n",
    "\"\"\"\n",
    "\n",
    "Data = pd.read_sql(query, engine)\n",
    "Data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       id  \\\n",
      "0  ABC123   \n",
      "1  ABC456   \n",
      "2  ABC789   \n",
      "3  DEF123   \n",
      "4  DEF456   \n",
      "5  DEF789   \n",
      "\n",
      "                                                                                                                                                                                   text  \n",
      "0  this practice sentence contains test words and all test words are spelled correctly                                                                                                   \n",
      "1  this test sentence contains test word and some test words are spelled incorrectly in this practice sentences for test words                                                           \n",
      "2  test words practice sentence one test word two two practice sentences two two test words two two practice sentence three three three test word three three three practice sentences   \n",
      "3  my money is my monies in my practice sentence account of test words with this test word my monies is my money account                                                                 \n",
      "4  this concludes the practice sentences with my money monies account and test word practice sentences with dot dot test words from a practices sentence dot test word                   \n",
      "5  taylor has no more accounts of my money account practice sentence test words dot dot practice sentences test word                                                                     \n"
     ]
    }
   ],
   "source": [
    "# Convert to Pandas Dataframe\n",
    "\n",
    "pandasDF = pd.DataFrame(Data)\n",
    "\n",
    "# When using Spark session\n",
    "# pandasDF = data.toPandas()\n",
    "# print(pandasDF)\n",
    "\n",
    "# Clean data\n",
    "# Remove unwanted characters and convert to all lowercase\n",
    "\n",
    "pandasDF['text'] = pandasDF['text'].str.replace('\"', '')\n",
    "pandasDF['text'] = pandasDF['text'].str.replace(',', ' ')\n",
    "pandasDF['text'] = pandasDF['text'].str.replace('.', '')\n",
    "pandasDF['text'] = pandasDF['text'].str.replace('?', '')\n",
    "pandasDF['text'] = pandasDF['text'].str.replace('\\r', '')\n",
    "pandasDF['text'] = pandasDF['text'].str.lower()\n",
    "\n",
    "print(pandasDF)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create working dataframe, independent of original dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "working_DF = pandasDF.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Confirm data types of Dataframe fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id      object\n",
      "text    object\n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(working_DF.dtypes)\n",
    "type(working_DF['text'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Word position and count, per record - (words only, not phrases - does not recognize spaces)\n",
    "### Exact word match"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Count all instances of \"money\" and show position locations, per row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>color_text</th>\n",
       "      <th>word</th>\n",
       "      <th>position</th>\n",
       "      <th>word_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC123</td>\n",
       "      <td>this practice sentence contains <span style='color: red; font-weight:bold;'>test</span> words and all <span style='color: red; font-weight:bold;'>test</span> words are spelled correctly</td>\n",
       "      <td>test</td>\n",
       "      <td>[4, 8]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABC456</td>\n",
       "      <td>this <span style='color: red; font-weight:bold;'>test</span> sentence contains <span style='color: red; font-weight:bold;'>test</span> word and some <span style='color: red; font-weight:bold;'>test</span> words are spelled incorrectly in this practice sentences for <span style='color: red; font-weight:bold;'>test</span> words</td>\n",
       "      <td>test</td>\n",
       "      <td>[1, 4, 8, 18]</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABC789</td>\n",
       "      <td><span style='color: red; font-weight:bold;'>test</span> words practice sentence one <span style='color: red; font-weight:bold;'>test</span> word two two practice sentences two two <span style='color: red; font-weight:bold;'>test</span> words two two practice sentence three three three <span style='color: red; font-weight:bold;'>test</span> word three three three practice sentences</td>\n",
       "      <td>test</td>\n",
       "      <td>[0, 5, 13, 22]</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEF123</td>\n",
       "      <td>my money is my monies in my practice sentence account of <span style='color: red; font-weight:bold;'>test</span> words with this <span style='color: red; font-weight:bold;'>test</span> word my monies is my money account</td>\n",
       "      <td>test</td>\n",
       "      <td>[11, 15]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEF456</td>\n",
       "      <td>this concludes the practice sentences with my money monies account and <span style='color: red; font-weight:bold;'>test</span> word practice sentences with dot dot <span style='color: red; font-weight:bold;'>test</span> words from a practices sentence dot <span style='color: red; font-weight:bold;'>test</span> word</td>\n",
       "      <td>test</td>\n",
       "      <td>[11, 18, 25]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DEF789</td>\n",
       "      <td>taylor has no more accounts of my money account practice sentence <span style='color: red; font-weight:bold;'>test</span> words dot dot practice sentences <span style='color: red; font-weight:bold;'>test</span> word</td>\n",
       "      <td>test</td>\n",
       "      <td>[11, 17]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# for Position Locations\n",
    "def indices(string, search_word):   \n",
    "    string = string.split()\n",
    "    return [i for i, x in enumerate(string) if x == search_word]\n",
    "\n",
    "# for Aggregate Counts\n",
    "def check_occurences(string, word = 'test'):\n",
    "    string_list = string.split(' ')\n",
    "    indices_word = [i for i, x in enumerate(string_list) if x == word]\n",
    "    result = 0\n",
    "    for i in indices_word:\n",
    "        result += 1\n",
    "    return result\n",
    "\n",
    "working_DF.insert(2, 'word', 'test')\n",
    "\n",
    "working_DF['position'] = working_DF['text'].apply(indices, search_word = 'test')\n",
    "\n",
    "working_DF['word_count'] = working_DF['text'].apply(lambda x: check_occurences(x))\n",
    "\n",
    "def highlight(text):\n",
    "    text = re.sub(r'\\b(test)\\b', r\"<span style='color: red; font-weight:bold;'>\\1</span>\", text)    \n",
    "    return text\n",
    "\n",
    "working_DF['color_text'] = working_DF['text'].apply(lambda x: highlight(x))\n",
    "\n",
    "display(HTML(working_DF[['id', 'color_text', 'word', 'position', 'word_count']].to_html(escape=False)))\n",
    "\n",
    "working_DF.drop(['color_text'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Phrase count - Exact matches of single string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Count all instances of \"test word\", per string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>color_text</th>\n",
       "      <th>phrase_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABC456</td>\n",
       "      <td>this test sentence contains <span style='color: red; font-weight:bold;'>test word</span> and some <span style='color: blue; font-weight:bold;'>test words</span> are spelled incorrectly in this practice sentences for <span style='color: blue; font-weight:bold;'>test words</span></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABC789</td>\n",
       "      <td><span style='color: blue; font-weight:bold;'>test words</span> practice sentence one <span style='color: red; font-weight:bold;'>test word</span> two two practice sentences two two <span style='color: blue; font-weight:bold;'>test words</span> two two practice sentence three three three <span style='color: red; font-weight:bold;'>test word</span> three three three practice sentences</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEF123</td>\n",
       "      <td>my money is my monies in my practice sentence account of <span style='color: blue; font-weight:bold;'>test words</span> with this <span style='color: red; font-weight:bold;'>test word</span> my monies is my money account</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEF456</td>\n",
       "      <td>this concludes the practice sentences with my money monies account and <span style='color: red; font-weight:bold;'>test word</span> practice sentences with dot dot <span style='color: blue; font-weight:bold;'>test words</span> from a practices sentence dot <span style='color: red; font-weight:bold;'>test word</span></td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DEF789</td>\n",
       "      <td>taylor has no more accounts of my money account practice sentence <span style='color: blue; font-weight:bold;'>test words</span> dot dot practice sentences <span style='color: red; font-weight:bold;'>test word</span></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 6\n"
     ]
    }
   ],
   "source": [
    "working_DF['phrase_count'] = working_DF['text'].str.count(r'\\b' + 'test word' + r'\\b')\n",
    "\n",
    "def highlight(text):\n",
    "    text = re.sub(r'\\b(test word)\\b', r\"<span style='color: red; font-weight:bold;'>\\1</span>\", text)\n",
    "    text = re.sub(r'\\b(test words)\\b', r\"<span style='color: blue; font-weight:bold;'>\\1</span>\", text)\n",
    "    return text\n",
    "\n",
    "working_DF['color_text'] = working_DF['text'].apply(lambda x: highlight(x))\n",
    "\n",
    "# Filter the rows where phrase_count is greater than zero\n",
    "filtered_data = working_DF[working_DF['phrase_count'] > 0]\n",
    "\n",
    "# Display the filtered data in HTML format\n",
    "display(HTML(filtered_data[['id', 'color_text', 'phrase_count']].to_html(escape=False)))\n",
    "\n",
    "# Row count\n",
    "print(f\"{len(working_DF.index): ,}\")\n",
    "\n",
    "# Remove the color_text column\n",
    "working_DF.drop(['color_text'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Phrase count - Exact matches for multiple strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>color_text</th>\n",
       "      <th>p1_multi_string_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC123</td>\n",
       "      <td>this practice sentence contains test words and all test words are spelled correctly</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABC456</td>\n",
       "      <td>this test sentence contains <span style='color: red; font-weight:bold;'>test word</span> and some test words are spelled incorrectly in this <span style='color: blue; font-weight:bold;'>practice sentences</span> for test words</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABC789</td>\n",
       "      <td>test words practice sentence one <span style='color: red; font-weight:bold;'>test word</span> two two <span style='color: blue; font-weight:bold;'>practice sentences</span> two two test words two two practice sentence three three three <span style='color: red; font-weight:bold;'>test word</span> three three three <span style='color: blue; font-weight:bold;'>practice sentences</span></td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEF123</td>\n",
       "      <td>my money is my monies in my practice sentence account of test words with this <span style='color: red; font-weight:bold;'>test word</span> my monies is my money account</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEF456</td>\n",
       "      <td>this concludes the <span style='color: blue; font-weight:bold;'>practice sentences</span> with my money monies account and <span style='color: red; font-weight:bold;'>test word</span> <span style='color: blue; font-weight:bold;'>practice sentences</span> with dot dot test words from a practices sentence dot <span style='color: red; font-weight:bold;'>test word</span></td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DEF789</td>\n",
       "      <td>taylor has no more accounts of my money account practice sentence test words dot dot <span style='color: blue; font-weight:bold;'>practice sentences</span> <span style='color: red; font-weight:bold;'>test word</span></td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def phrase_finder(text: str, strings: List[str]) -> int:\n",
    "    pattern = r'\\b(?:{})\\b'.format('|'.join(strings))\n",
    "    results = 0\n",
    "    \n",
    "    for match in re.finditer(pattern, text):\n",
    "        results += 1\n",
    "        \n",
    "    return results\n",
    "\n",
    "# SET INPUT VARIABLES HERE!\n",
    "strings = ['test word', 'practice sentences']\n",
    "\n",
    "# apply function to each row of the text column\n",
    "working_DF['p1_multi_string_count'] = working_DF['text'].apply(phrase_finder, args=(strings,))\n",
    "\n",
    "def highlight(text):   \n",
    "    text = re.sub(r'\\b(test word)\\b', r\"<span style='color: red; font-weight:bold;'>\\1</span>\", text)\n",
    "    text = re.sub(r'\\b(practice sentences)\\b', r\"<span style='color: blue; font-weight:bold;'>\\1</span>\", text)\n",
    "    return text\n",
    "\n",
    "working_DF['color_text'] = working_DF['text'].apply(lambda x: highlight(x))\n",
    "\n",
    "display(HTML(working_DF[['id', 'color_text', 'p1_multi_string_count']].to_html(escape=False)))\n",
    "\n",
    "working_DF.drop(['color_text'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Phrase count - FUZZY Match for single phrase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       id  \\\n",
      "0  ABC123   \n",
      "1  ABC456   \n",
      "2  ABC789   \n",
      "3  DEF123   \n",
      "4  DEF456   \n",
      "5  DEF789   \n",
      "\n",
      "                                                                                                                                                                                   text  \\\n",
      "0  this practice sentence contains test words and all test words are spelled correctly                                                                                                    \n",
      "1  this test sentence contains test word and some test words are spelled incorrectly in this practice sentences for test words                                                            \n",
      "2  test words practice sentence one test word two two practice sentences two two test words two two practice sentence three three three test word three three three practice sentences    \n",
      "3  my money is my monies in my practice sentence account of test words with this test word my monies is my money account                                                                  \n",
      "4  this concludes the practice sentences with my money monies account and test word practice sentences with dot dot test words from a practices sentence dot test word                    \n",
      "5  taylor has no more accounts of my money account practice sentence test words dot dot practice sentences test word                                                                      \n",
      "\n",
      "   fuzzy_phrase_count  \n",
      "0  2                   \n",
      "1  5                   \n",
      "2  6                   \n",
      "3  3                   \n",
      "4  5                   \n",
      "5  3                   \n"
     ]
    }
   ],
   "source": [
    "# The \"threshold\" value is used to set a minimum level of similarity between the string you are searching for and the text you are searching in. \n",
    "# In other words, it sets a limit for how \"fuzzy\" the match can be. \n",
    "# For example, if you set the threshold to 0.8, it will only return matches that have at least 80% similarity. \n",
    "# This can be useful if you want to find similar words, but not exactly the same word. \n",
    "# The threshold value should be a float between 0 and 1, where 1 is an exact match. \n",
    "# The smaller the threshold value, the more similar the string must be.\n",
    "\n",
    "# The threshold parameter in the fuzz.token_set_ratio function determines the minimum similarity score required for a match to be considered a match. \n",
    "# The function compares the two strings being passed to it and returns a score between 0 and 100, with 100 being an exact match and 0 being no match at all. \n",
    "# The score is determined by comparing the total number of matching characters between the two strings, taking into account the order of the characters and the number of characters in each string. \n",
    "# If the score is greater than or equal to the threshold, the function considers it a match.\n",
    "\n",
    "def fuzzy_match(text: str, search_string: str, threshold: int) -> int:\n",
    "    results = 0\n",
    "    for word in text.split():\n",
    "        if fuzz.token_set_ratio(word, search_string) >= threshold:\n",
    "            results += 1\n",
    "    return results\n",
    "\n",
    "# SET INPUT VARIABLES HERE!\n",
    "search_string = 'test word'\n",
    "threshold = 80\n",
    "\n",
    "# apply function to each row of the text column\n",
    "working_DF['fuzzy_phrase_count'] = working_DF['text'].apply(fuzzy_match, args = (search_string, threshold))\n",
    "\n",
    "print(working_DF[['id', 'text', 'fuzzy_phrase_count']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Count Phrase 1 NEAR Phrase 2, including variations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count ALL instances of \"test word\" NEAR \"practice sentence\", within 4 words of each other"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Don't stop searching at the first match found for both directions (back and forward), count all matches found within D \"Distance\" specified\n",
    "#### So, max possible matches when D = All words in entire record = (N# of times Phrase 1 occurrs within D) x (N# of times Phrase 2 occurs within D) = N1 x N2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>color_text</th>\n",
       "      <th>p1_NEAR_p2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC123</td>\n",
       "      <td>this <span style='color: blue; font-weight:bold; '>practice sentence</span> contains <span style='color: red; font-weight:bold;'>test word</span>s and all <span style='color: red; font-weight:bold;'>test word</span>s are spelled correctly</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABC456</td>\n",
       "      <td>this test sentence contains <span style='color: red; font-weight:bold;'>test word</span> and some <span style='color: red; font-weight:bold;'>test word</span>s are spelled incorrectly in this <span style='color: blue; font-weight:bold; '>practice sentence</span>s for <span style='color: red; font-weight:bold;'>test word</span>s</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABC789</td>\n",
       "      <td><span style='color: red; font-weight:bold;'>test word</span>s <span style='color: blue; font-weight:bold; '>practice sentence</span> one <span style='color: red; font-weight:bold;'>test word</span> two two <span style='color: blue; font-weight:bold; '>practice sentence</span>s two two <span style='color: red; font-weight:bold;'>test word</span>s two two <span style='color: blue; font-weight:bold; '>practice sentence</span> three three three <span style='color: red; font-weight:bold;'>test word</span> three three three <span style='color: blue; font-weight:bold; '>practice sentence</span>s</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEF123</td>\n",
       "      <td>my money is my monies in my <span style='color: blue; font-weight:bold; '>practice sentence</span> account of <span style='color: red; font-weight:bold;'>test word</span>s with this <span style='color: red; font-weight:bold;'>test word</span> my monies is my money account</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEF456</td>\n",
       "      <td>this concludes the <span style='color: blue; font-weight:bold; '>practice sentence</span>s with my money monies account and <span style='color: red; font-weight:bold;'>test word</span> <span style='color: blue; font-weight:bold; '>practice sentence</span>s with dot dot <span style='color: red; font-weight:bold;'>test word</span>s from a practices sentence dot <span style='color: red; font-weight:bold;'>test word</span></td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DEF789</td>\n",
       "      <td>taylor has no more accounts of my money account <span style='color: blue; font-weight:bold; '>practice sentence</span> <span style='color: red; font-weight:bold;'>test word</span>s dot dot <span style='color: blue; font-weight:bold; '>practice sentence</span>s <span style='color: red; font-weight:bold;'>test word</span></td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Don't stop searching at the first match found for both directions (back and forward), count all matches found within D \"Distance\" specified\n",
    "# So, max possible matches when D = All words in entire record =\n",
    "# = (N# of times Phrase 1 occurrs within D) x (N# of times Phrase 2 occurs within D) = N1 x N2\n",
    "\n",
    "def phrase_finder(text: str, strings1: List[str], strings2: List[str], distance: int, direction: str = 'both') -> int:\n",
    "    # direction: str = 'both' because you have to set a default direction ?\n",
    "    \n",
    "    pattern1 = r'\\b(?:{})\\b'.format('|'.join(strings1))\n",
    "    pattern2 = r'\\b(?:{})\\b'.format('|'.join(strings2))\n",
    "    count = 0\n",
    "    \n",
    "    for match1 in re.finditer(pattern1, text):\n",
    "        for match2 in re.finditer(pattern2, text):\n",
    "            \n",
    "            # Search Both directions, Forward and Backwards\n",
    "            if direction == 'both':\n",
    "                if match1.end() < match2.start():\n",
    "                    between_matches = text[match1.end():match2.start()]\n",
    "                    if len(re.findall(r'\\w+', between_matches)) <= distance:\n",
    "                        count += 1\n",
    "                elif match2.end() < match1.start():\n",
    "                    between_matches = text[match2.end():match1.start()]\n",
    "                    if len(re.findall(r'\\w+', between_matches)) <= distance:\n",
    "                        count += 1\n",
    "                else:\n",
    "                    pass\n",
    "                \n",
    "            # Search Forward or Backwards only, not Both\n",
    "            elif direction == 'forward':\n",
    "                if match1.end() < match2.start():\n",
    "                    between_matches = text[match1.end():match2.start()]\n",
    "                    if len(re.findall(r'\\w+', between_matches)) <= distance:\n",
    "                        count += 1\n",
    "                        \n",
    "            elif direction == 'backward':\n",
    "                if match2.end() < match1.start():\n",
    "                    between_matches = text[match2.end():match1.start()]\n",
    "                    if len(re.findall(r'\\w+', between_matches)) <= distance:\n",
    "                        count += 1\n",
    "    return count\n",
    "\n",
    "\n",
    "# SET INPUT VARIABLES HERE!\n",
    "strings1 = ['test word', 'test words']\n",
    "strings2 = ['practice sentence', 'practice sentences']\n",
    "distance = 3\n",
    "direction = 'both'\n",
    "\n",
    "# Apply function to each row of the text column\n",
    "working_DF['p1_NEAR_p2'] = working_DF['text'].apply(phrase_finder, args=(strings1, strings2, distance, direction))\n",
    "\n",
    "\n",
    "# Create function to edit font\n",
    "def highlight(text):\n",
    "    text = text.replace(\"test word\", \"<span style='color: red; font-weight:bold;'>test word</span>\")  \n",
    "    text = text.replace(\"practice sentence\", \"<span style='color: blue; font-weight:bold; '>practice sentence</span>\") \n",
    "    return text\n",
    "\n",
    "working_DF['color_text'] = working_DF['text'].apply(lambda x: highlight(x))\n",
    "\n",
    "display(HTML(working_DF[['id', 'color_text', 'p1_NEAR_p2']].to_html(escape = False)))\n",
    "\n",
    "working_DF.drop(['color_text'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Count Phrase 1 NOT NEAR Phrase 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Count all instances of \"test word\" NOT NEAR \"practice sentence\", within 3 words of each other"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>color_text</th>\n",
       "      <th>p1_NOT_NEAR_p2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC123</td>\n",
       "      <td>this <span style='color: blue; font-weight:bold; '>practice sentence</span> contains <span style='color: red; font-weight:bold;'>test word</span>s and all <span style='color: red; font-weight:bold;'>test word</span>s are spelled correctly</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABC456</td>\n",
       "      <td>this test sentence contains <span style='color: red; font-weight:bold;'>test word</span> and some <span style='color: red; font-weight:bold;'>test word</span>s are spelled incorrectly in this <span style='color: blue; font-weight:bold; '>practice sentence</span>s for <span style='color: red; font-weight:bold;'>test word</span>s</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABC789</td>\n",
       "      <td><span style='color: red; font-weight:bold;'>test word</span>s <span style='color: blue; font-weight:bold; '>practice sentence</span> one <span style='color: red; font-weight:bold;'>test word</span> two two <span style='color: blue; font-weight:bold; '>practice sentence</span>s two two <span style='color: red; font-weight:bold;'>test word</span>s two two <span style='color: blue; font-weight:bold; '>practice sentence</span> three three three <span style='color: red; font-weight:bold;'>test word</span> three three three <span style='color: blue; font-weight:bold; '>practice sentence</span>s</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEF123</td>\n",
       "      <td>my money is my monies in my <span style='color: blue; font-weight:bold; '>practice sentence</span> account of <span style='color: red; font-weight:bold;'>test word</span>s with this <span style='color: red; font-weight:bold;'>test word</span> my monies is my money account</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEF456</td>\n",
       "      <td>this concludes the <span style='color: blue; font-weight:bold; '>practice sentence</span>s with my money monies account and <span style='color: red; font-weight:bold;'>test word</span> <span style='color: blue; font-weight:bold; '>practice sentence</span>s with dot dot <span style='color: red; font-weight:bold;'>test word</span>s from a practices sentence dot <span style='color: red; font-weight:bold;'>test word</span></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DEF789</td>\n",
       "      <td>taylor has no more accounts of my money account <span style='color: blue; font-weight:bold; '>practice sentence</span> <span style='color: red; font-weight:bold;'>test word</span>s dot dot <span style='color: blue; font-weight:bold; '>practice sentence</span>s <span style='color: red; font-weight:bold;'>test word</span></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def phrase_finder(text: str, target_strings: List[str], avoid_strings: List[str], distance: int) -> int:\n",
    "    pattern1 = r'\\b(?:{})\\b'.format('|'.join(target_strings))\n",
    "    pattern2 = r'\\b(?:{})\\b'.format('|'.join(avoid_strings))\n",
    "    count = 0\n",
    "      \n",
    "    for match1 in re.finditer(pattern1, text):\n",
    "        found_within_distance = False\n",
    "        for match2 in re.finditer(pattern2, text):\n",
    "            if match1.end() < match2.start():\n",
    "                between_matches = text[match1.end():match2.start()]\n",
    "                if len(re.findall(r'\\w+', between_matches)) <= distance:\n",
    "                    found_within_distance = True\n",
    "                    break\n",
    "                    \n",
    "            elif match2.end() < match1.start():\n",
    "                between_matches = text[match2.end():match1.start()]\n",
    "                if len(re.findall(r'\\w+', between_matches)) <= distance:\n",
    "                    found_within_distance = True\n",
    "                    break\n",
    "                    \n",
    "        if not found_within_distance:\n",
    "            count += 1\n",
    "            \n",
    "    return count\n",
    "\n",
    "\n",
    "# SET INPUT VARIABLES HERE!\n",
    "target_strings = ['test word', 'test words']\n",
    "avoid_strings = ['practice sentence', 'practice sentences']\n",
    "distance = 3\n",
    "\n",
    "# Apply the phrase_finder function to the 'text' column of working_DF using the strings1, strings2, and distance arguments\n",
    "working_DF['p1_NOT_NEAR_p2'] = working_DF['text'].apply(lambda x: phrase_finder(x, target_strings, avoid_strings, distance))\n",
    "\n",
    "\n",
    "# Create function to edit font\n",
    "def highlight(text):\n",
    "    text = text.replace(\"test word\", \"<span style='color: red; font-weight:bold;'>test word</span>\")  \n",
    "    text = text.replace(\"practice sentence\", \"<span style='color: blue; font-weight:bold; '>practice sentence</span>\") \n",
    "    return text\n",
    "\n",
    "working_DF['color_text'] = working_DF['text'].apply(lambda x: highlight(x))\n",
    "\n",
    "display(HTML(working_DF[['id', 'color_text', 'p1_NOT_NEAR_p2']].to_html(escape = False)))\n",
    "\n",
    "working_DF.drop(['color_text'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Post-Dataframe clean-up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame: __, Shape: (5, 2), Columns: Index(['id', 'text'], dtype='object')\n",
      "DataFrame: Data, Shape: (6, 6), Columns: Index(['id', 'text', 'word', 'position', 'word_count', 'color_text'], dtype='object')\n",
      "DataFrame: _4, Shape: (5, 2), Columns: Index(['id', 'text'], dtype='object')\n",
      "DataFrame: pandasDF, Shape: (6, 10), Columns: Index(['id', 'text', 'word', 'position', 'word_count', 'phrase_count',\n",
      "       'p1_multi_string_count', 'fuzzy_phrase_count', 'p1_NEAR_p2',\n",
      "       'p1_NOT_NEAR_p2'],\n",
      "      dtype='object')\n",
      "DataFrame: _40, Shape: (5, 2), Columns: Index(['id', 'text'], dtype='object')\n",
      "DataFrame: _48, Shape: (5, 2), Columns: Index(['id', 'text'], dtype='object')\n",
      "DataFrame: _63, Shape: (5, 2), Columns: Index(['id', 'text'], dtype='object')\n",
      "DataFrame: _68, Shape: (5, 2), Columns: Index(['id', 'text'], dtype='object')\n",
      "DataFrame: _73, Shape: (5, 2), Columns: Index(['id', 'text'], dtype='object')\n",
      "DataFrame: _89, Shape: (5, 2), Columns: Index(['id', 'text'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def list_dataframes():\n",
    "    for var_name in globals():\n",
    "        var = globals()[var_name]\n",
    "        if isinstance(var, pd.DataFrame):\n",
    "            print(f'DataFrame: {var_name}, Shape: {var.shape}, Columns: {var.columns}')\n",
    "\n",
    "# Call the function\n",
    "list_dataframes()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                       object\n",
      "text                     object\n",
      "word                     object\n",
      "position                 object\n",
      "word_count               int64 \n",
      "phrase_count             int64 \n",
      "p1_multi_string_count    int64 \n",
      "fuzzy_phrase_count       int64 \n",
      "p1_NEAR_p2               int64 \n",
      "p1_NOT_NEAR_p2           int64 \n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(working_DF.dtypes)\n",
    "type(working_DF['text'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 6\n",
      "       id  word  word_count  phrase_count  p1_multi_string_count  \\\n",
      "0  ABC123  test  2           0             0                       \n",
      "1  ABC456  test  4           1             2                       \n",
      "2  ABC789  test  4           2             4                       \n",
      "3  DEF123  test  2           1             1                       \n",
      "4  DEF456  test  3           2             4                       \n",
      "5  DEF789  test  2           1             2                       \n",
      "\n",
      "   fuzzy_phrase_count  p1_NOT_NEAR_p2  p1_NEAR_p2  \n",
      "0  2                   1               1           \n",
      "1  5                   2               1           \n",
      "2  6                   0               7           \n",
      "3  3                   1               1           \n",
      "4  5                   1               2           \n",
      "5  3                   0               3           \n",
      " 6\n"
     ]
    }
   ],
   "source": [
    "# Remove 'text' and 'position' columns\n",
    "working_DF.drop(columns = ['text', 'position'], axis = 1, inplace = True) \n",
    "\n",
    "# Remove records with no results (zero hits)\n",
    "# Use this when datatype = Integer\n",
    "working_DF = working_DF[working_DF['word_count'] + working_DF['phrase_count'] \\\n",
    "            + working_DF['p1_multi_string_count'] + working_DF['fuzzy_phrase_count'] \\\n",
    "            + working_DF['p1_NEAR_p2'] + working_DF['p1_NOT_NEAR_p2'] != 0]\n",
    "\n",
    "print(f\"{len(working_DF.index): ,}\")\n",
    "\n",
    "print(working_DF)\n",
    "\n",
    "print(f\"{len(working_DF.index): ,}\")"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}