UseCases/Text_Term_Frequency/Text_Term_Frequency_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Text Term Frequency Analysis (Python-SQL)\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This demo will analyze the text in rows of the table to find the TF-IDF or Term Frequency-Inverse Document Frequency is an indicator of a term's importance in a specific document based on the entire corpus of documents.    \n",
    "This is a demonstration of Vantage capabilities for functional demos e.g.\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> NGramSplitter Function - tokenizes (splits) an input stream of text and outputs n multigrams (called n-grams) based on the specified Reset, Punctuation, and Delimiter syntax elements.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> This notebook demonstrate how the function is used in Python kernel, there is a similar notebook which shows the same features in sql kernel. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Steps</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Connect to Vantage and read the dataset. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Use NGramSplitter SQL to create a table of grams of n-size. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Express SQL to calculate TF-IDF and store the output in a table. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Retrieve the data as a local dataframe. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Basic visualization to show top 30 important terms. </li>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Import python packages, connect to Vantage and explore the dataset</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass\n",
    "import warnings\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# from teradataml.dataframe.dataframe import DataFrame\n",
    "# from teradataml.analytics.sqle import NGramSplitter\n",
    "# from teradataml.dataframe.dataframe import in_schema\n",
    "# from teradataml.context.context import create_context, remove_context, get_context\n",
    "# from teradataml.dataframe.copy_to import copy_to_sql\n",
    "# from teradataml.options.display import display\n",
    "from teradataml import *\n",
    "from teradatasqlalchemy.types import *\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "warnings.filterwarnings('ignore')\n",
    "display.max_rows = 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, then use down arrow to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below command will make a connection to the Vantage environment.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Text_Term_Frequency_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. In this demo since we are using Temporal table we will be creating databases and tables in local storage and use them in the notebook. Please execute the procedure in the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_Retail_cloud');\"\n",
    " # takes about 25 seconds, estimated space: 0 MB\n",
    "#%run -i ../run_procedure.py \"call get_data('DEMO_Retail_local');\" \n",
    "# takes about 50 seconds, estimated space: 23 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Get the data from Vantage in the DataFrame.</p>   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_reviews = DataFrame('\"DEMO_Retail\".\"Web_Comment\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us see how the data in the table looks like. We have taken one comment.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT *\n",
    "FROM \"DEMO_Retail\".\"Web_Comment\" where comment_id = 30\n",
    "'''\n",
    "tdf_res = DataFrame.from_query(qry)\n",
    "tdf_res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Use the NGram Splitter SQL Function</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>NGram function will split the corpus of documents into \"terms\" (grams) of selected size.  Specifically, this example will create a table called \"tbl_grams\" that is the result of splitting each \"document\" (review) into two-word chunks (grams).  Each row in this table includes;</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>The two-word chunk (ngram).</li>\n",
    "    <li>The source review id (row_id).</li>\n",
    "     <li>Chunk length (n).</li>\n",
    "     <li>The count of this chunk in the review (frequency).</li>\n",
    "     <li>The count of this chunk in all the reviews (totalcnt)</li>\n",
    "</ol>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The splitting algorithm can be controlled with delimeters, punctuation indicators, etc.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "qry = 'DROP TABLE tbl_grams;'\n",
    "\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except Exception as e:\n",
    "    if str(e.args).find('3807') >= 1:\n",
    "        pass\n",
    "    else:\n",
    "        raise\n",
    "\n",
    "#how many grams should we split the docs into?\n",
    "grams = 2\n",
    "\n",
    "#Create ngram table\n",
    "qry = f'''\n",
    "CREATE MULTISET TABLE tbl_grams AS (\n",
    "    SELECT * FROM NGramSplitter ( \n",
    "        ON ( SELECT * FROM \"DEMO_Retail\".\"Web_Comment\" )   \n",
    "        USING \n",
    "            TextColumn('comment_text') \n",
    "            Accumulate('comment_id') \n",
    "            Grams('{grams}') \n",
    "            OverLapping('TRUE') \n",
    "            ConvertToLowerCase('TRUE') \n",
    "            Delimiter(' ') \n",
    "            Punctuation('[`~#^&*()-]') \n",
    "            OutputTotalGramCount('TRUE') \n",
    "            NGramColName('ngram') \n",
    "            GramLengthColName('n') \n",
    "            FrequencyColName('frequency') \n",
    "            TotalCountColName('totalcnt') \n",
    "    ) as ngram_out\n",
    "    )\n",
    "WITH DATA\n",
    "PRIMARY INDEX (comment_id);\n",
    "'''\n",
    "\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_grams = DataFrame('tbl_grams')\n",
    "tdf_grams"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the table created above we can see the NGram function applied to the web comment column. We can see the frequency and the total number of times the ngram appear in the column.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us check how the comment id we saw earlier looks after converting to ngrams</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT *\n",
    "FROM tbl_grams where comment_id = 30 \n",
    "'''\n",
    "tdf_res = DataFrame.from_query(qry)\n",
    "tdf_res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Create the TF-IDF Table</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TF-IDF</b> or <b>Term Frequency-Inverse Document Frequency</b> is an indicator of a term's <b>importance</b> in a specific document based on the entire corpus of documents.  This value is calculated by taking the Product of:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Term Frequency = (Number of Terms in the Document)/(Number of Terms in the Corpus)</li>\n",
    "    <li>Inverse Document Frequency = Natural Log((Total Number of Documents)/(Number of Documents with the Term))</li>\n",
    " </ul>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This can be accomplished in SQL using the results table created using NGgram Splitter function in above step:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# Drop the table if it already exists\n",
    "qry = 'DROP TABLE tbl_tf_idf;'\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except Exception as e:\n",
    "    if str(e.args).find('3807') >= 1:\n",
    "        pass\n",
    "    else:\n",
    "        raise\n",
    "\n",
    "# Create the table\n",
    "qry = '''\n",
    "CREATE MULTISET TABLE tbl_tf_idf AS (\n",
    "    SELECT gr.comment_id AS comment_id,\n",
    "    gr.\"ngram\" AS term,\n",
    "    CAST(CAST(gr.frequency AS FLOAT) / CAST(gr.totalcnt AS FLOAT) AS DECIMAL(10,6)) AS tf,\n",
    "    CAST(LN(sel_docs.tot_docs / sel_docs.tot_term) AS DECIMAL(10,6)) AS idf,\n",
    "    CAST(idf * tf AS DECIMAL(10,6)) AS tf_idf\n",
    "    FROM tbl_grams AS gr\n",
    "    -- get the number of docs where each term exists\n",
    "    LEFT JOIN (\n",
    "        SELECT \"ngram\", tot_term , tot_docs FROM (\n",
    "            (SELECT \"ngram\", COUNT(*) AS tot_term\n",
    "            FROM tbl_grams\n",
    "            GROUP BY \"ngram\") terms\n",
    "            -- get the total doc count and join it to the table\n",
    "            CROSS JOIN (SELECT COUNT(DISTINCT comment_id) AS tot_docs FROM tbl_grams ) AS sum_docs\n",
    "        )\n",
    "    ) sel_docs\n",
    "    ON gr.\"ngram\" = sel_docs.\"ngram\"\n",
    "    WHERE tf_idf > .5\n",
    ")\n",
    "WITH DATA\n",
    "PRIMARY INDEX (comment_id);\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the data from the created table\n",
    "tdf_tf_idf = DataFrame('tbl_tf_idf')\n",
    "tdf_tf_idf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us check the term frequency and inverse document frequency calculated for the comment we saw before</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT *\n",
    "FROM tbl_tf_idf where comment_id = 30\n",
    "'''\n",
    "tdf_res = DataFrame.from_query(qry)\n",
    "tdf_res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let us check the mostly used terms in our data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tf_idf = tdf_tf_idf.to_pandas(all_rows = True)\n",
    "df_tf_idf['tf_idf'] = df_tf_idf['tf_idf'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tf_idf.sort_values(by = 'tf_idf', ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Visualize the Results</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Let's use Pandas and Matplotlib to do visualizations of the data:</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df_tf_idf.sort_values(by = 'tf_idf', ascending = False).head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#plot it:\n",
    "df1.sort_values(by = 'tf_idf', ascending = True).set_index('term')[['tf_idf']].plot(kind = 'barh', legend = True, figsize = (12, 9));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "In this plot we are plotting the top 30 terms which are used in the reviews. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5.  Cleanup \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Worktables </b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['tbl_tf_idf', 'tbl_grams']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "        try:\n",
    "            db_drop_table(table_name=table, schema_name='demo_user')\n",
    "        except:\n",
    "            pass\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Database and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Retail');\" \n",
    "#Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Reference Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Teradata Python Package User Guide: <a href = 'https://docs.teradata.com/reader/eteIDCTX4O4IMvazRMypxQ/uDjppX7PJInABCckgu~KFg'>https://docs.teradata.com/reader/eteIDCTX4O4IMvazRMypxQ/uDjppX7PJInABCckgu~KFg</a></li>\n",
    "    <li>Teradataml Python Reference: <a href = 'https://docs.teradata.com/reader/GsM0pYRZl5Plqjdf9ixmdA/MzdO1q_t80M47qY5lyImOA'>https://docs.teradata.com/reader/GsM0pYRZl5Plqjdf9ixmdA/MzdO1q_t80M47qY5lyImOA</a></li>\n",
    "    <li>Teradata NGramSplitter Function Reference: <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Text-Analytic-Functions/NGramSplitter'>https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Text-Analytic-Functions/NGramSplitter</a></li>\n",
    "  \n",
    "</ul>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}