UseCases/Text_Term_Frequency/Text_Term_Frequency_Python.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)\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 using  <a href = 'https://www.docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-Package-for-Python-User-Guide-17.20/Introduction-to-Teradata-Package-for-Python'>TeradataML Package</a>,there is a similar notebook which shows the same features in sql kernel.More information on the functions can be found in <a href = 'https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20'>TeradataML Python Reference</a> </p>\n",
    "<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": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys."
   ]
  },
  {
   "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 sys\n",
    "import warnings\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# from teradataml import func\n",
    "# from teradataml.analytics.sqle import NGramSplitter\n",
    "# from teradataml.context.context import create_context, get_context, remove_context\n",
    "# from teradataml.dataframe.dataframe import DataFrame, in_schema\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",
    "from sqlalchemy import func\n",
    "%matplotlib inline\n",
    "\n",
    "# Set message level\n",
    "display.print_sqlmr_query = False\n",
    "display.max_rows = 5\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "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 and set (optional) QueryBand for database session.</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_Python.ipynb;' UPDATE FOR SESSION;\")"
   ]
  },
  {
   "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. You have the option of either running the demo using foreign tables to access the data without using any storage on your environment or downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage. There are two statements in the following cell, and one is commented out. You may switch which mode you choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "%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 object <i>\"DEMO_Retail\".\"Web_Comment\"</i> 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:18px;font-family:Arial;color:#00233C'> <b>Getting familiar with the dataset</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_reviews.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Object tdf_reviews has {} records and uses {} bytes in memory.\"\n",
    "      .format(tdf_reviews.size, sys.getsizeof(tdf_reviews)));"
   ]
  },
  {
   "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": [
    "# check an example comment\n",
    "tdf_reviews[(tdf_reviews.comment_id == 30)]"
   ]
  },
  {
   "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'>The splitting algorithm <a href = 'https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/teradataml-Analytic-Database-SQL-Engine-Analytic-Functions/Supported-on-Database-Versions-16.20.xx-17.00.xx-17.05.xx/NGramSplitter'>NGramSplitter </a>can be controlled with delimeters, punctuation indicators, etc.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "tdf_grams = NGramSplitter(\n",
    "               data             = tdf_reviews\n",
    "              ,text_column      = 'comment_text'\n",
    "              ,accumulate       = 'comment_id'\n",
    "              ,grams            = \"2\"\n",
    "              ,overlapping      = True\n",
    "              ,to_lower_case    = True\n",
    "              ,delimiter        = \" \"\n",
    "              ,punctuation      = '[`~#^&*()-]'\n",
    "              ,total_gram_count = True\n",
    "            ).result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the dataframe 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.  \n",
    "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": [
    "# check an example comment\n",
    "tdf_grams[(tdf_grams.comment_id == 30)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check count of distinct CommentID (distinct_Comment should be 22641)\n",
    "tdf_grams.assign(drop_columns=True\n",
    "                ,distinct_Comment=tdf_grams.comment_id.distinct().count())"
   ]
  },
  {
   "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'><a href = 'https://en.wikipedia.org/wiki/Tf%E2%80%93idf'<b>TF-IDF</b> or <b>Term Frequency-Inverse Document Frequency</b> </a> 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:\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 teradatml.dataframe using the results table created using NGgram Splitter function in above step.\n",
    "<br>\n",
    "    \n",
    "    tdf_tf_idf.tf     = tdf_grams.frequency / tdf_grams.totalcnt\n",
    "    tdf_tf_idf.idf    = ln( (Total distinct tdf_grams.comment_id) / (Total distinct tdf_grams.ngram) )\n",
    "    tdf_tf_idf.tf_idf = idf * tf\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get Total distinct tdf_grams.comment_id --> iDistinctComment (SQL column: tot_docs)\n",
    "iDistinctComment = tdf_grams.assign(drop_columns=True\n",
    "                                         ,distinct_Comment = tdf_grams.comment_id.distinct().count()\n",
    "                                   ).get_values()[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get Total distinct tdf_grams.ngram --> td_ngram_count (SQL column: tot_term)\n",
    "td_ngram_count  = tdf_grams.groupby([\"ngram\"]).count().select([\"ngram\", \"count_comment_id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first draft of tdf_tf_idf by selecting column to keep & adding iDistinctComment\n",
    "tdf_tf_idf = tdf_grams.assign(drop_columns = True\n",
    "                                   ,comment_id = tdf_grams.comment_id\n",
    "                                   ,term      = tdf_grams.ngram\n",
    "                                   ,tf         = tdf_grams.frequency.cast(type_=FLOAT) / tdf_grams.totalcnt \n",
    "                                   ,tot_docs   = (int(iDistinctComment))\n",
    "                                   )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# left-join tdf_tf_idf to td_ngram_count\n",
    "tdf_tf_idf = tdf_tf_idf.join(other   = td_ngram_count\n",
    "                            ,on      = [\"term=ngram\"]\n",
    "                            ,how     = \"left\" \n",
    "                            ,lsuffix = \"t1\", rsuffix = \"t2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add inner division of idf-LN\n",
    "tdf_tf_idf = tdf_tf_idf.assign(idf_    = (tdf_tf_idf.tot_docs.cast(type_=FLOAT) / tdf_tf_idf.count_comment_id) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add idf\n",
    "tdf_tf_idf = tdf_tf_idf.assign(idf     = func.ln(tdf_tf_idf.idf_.expression))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add tf_idf\n",
    "tdf_tf_idf = tdf_tf_idf.assign(tf_idf  = tdf_tf_idf.idf * tdf_tf_idf.tf )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check whether all columns are now pulled together\n",
    "tdf_tf_idf.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pull it local to pandas-dataframe\n",
    "# again.. - Lazy Evaluation & this step does all the work, taking some time\n",
    "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": [
    "# check mem sizes of teradataml.dataframe <-> pandas.dataframe\n",
    "print(\"teradataml.dataframe tdf_tf_idf has {:>10} bytes in memory.\".format(sys.getsizeof(tdf_tf_idf)));\n",
    "print(\"pandas.dataframe     df_tf_idf  has {:>10} bytes in memory.\".format(sys.getsizeof(df_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": [
    "# check an example comment\n",
    "df_tf_idf[(df_tf_idf.comment_id == 30)]"
   ]
  },
  {
   "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.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'>Let's use Pandas and Matplotlib to do visualizations of the data:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get Top30 by tf_idf\n",
    "df_top30 = df_tf_idf.sort_values(by = 'tf_idf', ascending = False).head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot it using build in plot\n",
    "df_top30.sort_values(by = 'tf_idf', ascending = True)\\\n",
    "        .set_index('term')[['tf_idf']]\\\n",
    "        .plot(kind = 'barh', legend = True, figsize = (12, 9));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this plot you see the top 30 terms which are used in the reviews. The plot is made up with pandas.dataframe build in <a href = 'https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.plot.htmlhttps://pandas.pydata.org/docs/reference/api/pandas.DataFrame.plot.html'>plot function </a> build on <a href = 'https://matplotlib.org/stable/api/indexhttps://matplotlib.org/stable/api/index'> matplolib </a>.</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 </b></p>\n",
    "<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><a href = 'https://docs.teradata.com/reader/eteIDCTX4O4IMvazRMypxQ/uDjppX7PJInABCckgu~KFg'>Teradata Python Package User Guide</a></li>\n",
    "    <li><a href = 'https://docs.teradata.com/reader/GsM0pYRZl5Plqjdf9ixmdA/MzdO1q_t80M47qY5lyImOA'>Teradataml Python Reference</a></li>\n",
    "    <li><a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Text-Analytic-Functions/NGramSplitter'>Teradata NGramSplitter Function Reference</a></li>\n",
    "  \n",
    "</ul>"
   ]
  },
  {
   "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"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": true
 },
 "nbformat": 4,
 "nbformat_minor": 4
}