03_FeatureExtractionPart02-CompleteSolution-Template.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6e71f2dc",
   "metadata": {},
   "source": [
    "## Initial Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7a40ad05",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "64616eb05ca747b2a4e72ac919ebb0e9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Spark application\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr><th>ID</th><th>YARN Application ID</th><th>Kind</th><th>State</th><th>Spark UI</th><th>Driver log</th><th>Current session?</th></tr><tr><td>1</td><td>application_1661789328664_0002</td><td>pyspark</td><td>idle</td><td><a target=\"_blank\" href=\"http://ip-172-31-75-153.ec2.internal:20888/proxy/application_1661789328664_0002/\" class=\"emr-proxy-link\" emr-resource=\"j-3MCWIXT6K71TN\n",
       "\" application-id=\"application_1661789328664_0002\">Link</a></td><td><a target=\"_blank\" href=\"http://ip-172-31-77-76.ec2.internal:8042/node/containerlogs/container_1661789328664_0002_01_000001/livy\" >Link</a></td><td>✔</td></tr></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SparkSession available as 'spark'.\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2e6d1f73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5b6fd761302443a08aba2ab613ac04ee",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.appName(\"Basics\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b16e5a14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5044db98ffc1485bb06ab69d4a060ee9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pyspark.sql.session.SparkSession object at 0x7fbd92720290>"
     ]
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ccbec7ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4df9b231cd7f4c7d9696f3f1fcc21281",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting plotly==5.5.0\n",
      "  Using cached https://files.pythonhosted.org/packages/b3/f4/23d4a698db9fe772f7fdf40ac17b743c4b0d80274732c59db5bd45acb3be/plotly-5.5.0-py2.py3-none-any.whl\n",
      "Requirement already satisfied: six in /usr/local/lib/python3.7/site-packages (from plotly==5.5.0)\n",
      "Collecting tenacity>=6.2.0 (from plotly==5.5.0)\n",
      "  Using cached https://files.pythonhosted.org/packages/f2/a5/f86bc8d67c979020438c8559cc70cfe3a1643fd160d35e09c9cca6a09189/tenacity-8.0.1-py3-none-any.whl\n",
      "Installing collected packages: tenacity, plotly\n",
      "Successfully installed plotly-5.5.0 tenacity-8.0.1\n",
      "\n",
      "Collecting pandas==0.25.1\n",
      "  Using cached https://files.pythonhosted.org/packages/7e/ab/ea76361f9d3e732e114adcd801d2820d5319c23d0ac5482fa3b412db217e/pandas-0.25.1-cp37-cp37m-manylinux1_x86_64.whl\n",
      "Requirement already satisfied: pytz>=2017.2 in /usr/local/lib/python3.7/site-packages (from pandas==0.25.1)\n",
      "Requirement already satisfied: numpy>=1.13.3 in /usr/local/lib64/python3.7/site-packages (from pandas==0.25.1)\n",
      "Collecting python-dateutil>=2.6.1 (from pandas==0.25.1)\n",
      "  Using cached https://files.pythonhosted.org/packages/36/7a/87837f39d0296e723bb9b62bbb257d0355c7f6128853c78955f57342a56d/python_dateutil-2.8.2-py2.py3-none-any.whl\n",
      "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/site-packages (from python-dateutil>=2.6.1->pandas==0.25.1)\n",
      "Installing collected packages: python-dateutil, pandas\n",
      "Successfully installed pandas-0.25.1 python-dateutil-2.8.2\n",
      "\n",
      "Collecting numpy==1.14.5\n",
      "  Using cached https://files.pythonhosted.org/packages/3f/e7/7f24ef402a5766c677683e313c5595137d754cb9eb1c99627803280e79d5/numpy-1.14.5-cp37-cp37m-manylinux1_x86_64.whl\n",
      "Installing collected packages: numpy\n",
      "  Found existing installation: numpy 1.16.5\n",
      "    Not uninstalling numpy at /usr/local/lib64/python3.7/site-packages, outside environment /tmp/1661797994524-0\n",
      "Successfully installed numpy-1.14.5\n",
      "\n",
      "Collecting matplotlib==3.1.1\n",
      "  Using cached https://files.pythonhosted.org/packages/19/7a/60bd79c5d79559150f8bba866dd7d434f0a170312e4d15e8aefa5faba294/matplotlib-3.1.1-cp37-cp37m-manylinux1_x86_64.whl\n",
      "Requirement already satisfied: python-dateutil>=2.1 in /mnt/tmp/1661797994524-0/lib/python3.7/site-packages (from matplotlib==3.1.1)\n",
      "Collecting pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 (from matplotlib==3.1.1)\n",
      "  Using cached https://files.pythonhosted.org/packages/6c/10/a7d0fa5baea8fe7b50f448ab742f26f52b80bfca85ac2be9d35cdd9a3246/pyparsing-3.0.9-py3-none-any.whl\n",
      "Collecting cycler>=0.10 (from matplotlib==3.1.1)\n",
      "  Using cached https://files.pythonhosted.org/packages/5c/f9/695d6bedebd747e5eb0fe8fad57b72fdf25411273a39791cde838d5a8f51/cycler-0.11.0-py3-none-any.whl\n",
      "Requirement already satisfied: numpy>=1.11 in /mnt/tmp/1661797994524-0/lib/python3.7/site-packages (from matplotlib==3.1.1)\n",
      "Collecting kiwisolver>=1.0.1 (from matplotlib==3.1.1)\n",
      "  Using cached https://files.pythonhosted.org/packages/ab/8f/8dbe2d4efc4c0b08ec67d6efb7cc31fbfd688c80afad85f65980633b0d37/kiwisolver-1.4.4-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.whl\n",
      "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/site-packages (from python-dateutil>=2.1->matplotlib==3.1.1)\n",
      "Collecting typing-extensions; python_version < \"3.8\" (from kiwisolver>=1.0.1->matplotlib==3.1.1)\n",
      "  Using cached https://files.pythonhosted.org/packages/ed/d6/2afc375a8d55b8be879d6b4986d4f69f01115e795e36827fd3a40166028b/typing_extensions-4.3.0-py3-none-any.whl\n",
      "Installing collected packages: pyparsing, cycler, typing-extensions, kiwisolver, matplotlib\n",
      "Successfully installed cycler-0.11.0 kiwisolver-1.4.4 matplotlib-3.1.1 pyparsing-3.0.9 typing-extensions-4.3.0\n",
      "\n",
      "You are using pip version 9.0.1, however version 22.2.2 is available.\n",
      "You should consider upgrading via the 'pip install --upgrade pip' command.\n",
      "\n",
      "You are using pip version 9.0.1, however version 22.2.2 is available.\n",
      "You should consider upgrading via the 'pip install --upgrade pip' command.\n",
      "\n",
      "You are using pip version 9.0.1, however version 22.2.2 is available.\n",
      "You should consider upgrading via the 'pip install --upgrade pip' command.\n",
      "\n",
      "You are using pip version 9.0.1, however version 22.2.2 is available.\n",
      "You should consider upgrading via the 'pip install --upgrade pip' command."
     ]
    }
   ],
   "source": [
    "# Run this everytime you create a new spark instance. \n",
    "\n",
    "spark.sparkContext.install_pypi_package(\"plotly==5.5.0\")\n",
    "spark.sparkContext.install_pypi_package(\"pandas==0.25.1\")\n",
    "spark.sparkContext.install_pypi_package(\"numpy==1.14.5\")\n",
    "spark.sparkContext.install_pypi_package(\"matplotlib==3.1.1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cea1e671",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2d9bcfcdfee2438db57aae80912fe8d9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from pyspark.sql import functions as F\n",
    "from pyspark.ml.feature import Bucketizer\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from pyspark.sql.window import Window\n",
    "\n",
    "# Import for typecasting columns\n",
    "from pyspark.sql.types import IntegerType,BooleanType,DateType,FloatType,StringType\n",
    "from pyspark.sql.types import ArrayType"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37b75577",
   "metadata": {},
   "source": [
    "## Defining Custom Functions "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "515ff4f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "853c3d8b7cb74a0e85a9d64a961c5a9a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def get_quantiles(df, col_name, quantiles_list = [0.01, 0.25, 0.5, 0.75, 0.99]):\n",
    "    \"\"\"\n",
    "    Takes a numerical column and returns column values at requested quantiles\n",
    "\n",
    "    Inputs \n",
    "    Argument 1: Dataframe\n",
    "    Argument 2: Name of the column\n",
    "    Argument 3: A list of quantiles you want to find. Default value [0.01, 0.25, 0.5, 0.75, 0.99]\n",
    "\n",
    "    Output \n",
    "    Returns a dictionary with quantiles as keys and column quantile values as values \n",
    "    \"\"\"\n",
    "    # Get min, max and quantile values for given column\n",
    "    min_val = df.agg(F.min(col_name)).first()[0]\n",
    "    max_val = df.agg(F.max(col_name)).first()[0]\n",
    "    quantiles_vals = df.approxQuantile(col_name,\n",
    "                                       quantiles_list,\n",
    "                                       0)\n",
    "  \n",
    "    # Store min, quantiles and max in output dict, sequentially\n",
    "    quantiles_dict = {0.0:min_val}\n",
    "    quantiles_dict.update(dict(zip(quantiles_list, quantiles_vals)))\n",
    "    quantiles_dict.update({1.0:max_val})\n",
    "    return(quantiles_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ca215059",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "635cb5ccbe414781a41ba12a5943f1d8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def plot_bucketwise_statistics (summary, bucketizer):\n",
    "    \"\"\"\n",
    "    Takes in a dataframe and a bucketizer object and plots the summary statistics for each bucket in the dataframe. \n",
    "  \n",
    "    Inputs\n",
    "    Argument 1: Pandas dataframe obtained from bucket_col_print_summary function \n",
    "    Argument 2: Bucketizer object obtained from bucket_col_print_summary function\n",
    "  \n",
    "    Output\n",
    "    Displays a plot of bucketwise average ratings nunber of ratings of a parameter.   \n",
    "    \"\"\"\n",
    "    # Creating bucket labels from splits\n",
    "    classlist = bucketizer.getSplits()\n",
    "    number_of_classes = len(classlist) - 1\n",
    "\n",
    "    class_labels = []\n",
    "    hover_labels = []\n",
    "    for i in range (number_of_classes):\n",
    "        hover_labels.append(str(classlist[i])+\"-\"+str(classlist[i+1]) +\" (Bucket name: \"+ str(int(i)) +\")\"  )\n",
    "        class_labels.append(str(classlist[i])+\"-\"+str(classlist[i+1]) )\n",
    "  \n",
    "    summary[\"Scaled_number\"] = (summary[\"n_ratings\"]-summary[\"n_ratings\"].min())/(summary[\"n_ratings\"].max()-summary[\"n_ratings\"].min()) + 1.5\n",
    "    summary['Bucket_Names'] = class_labels\n",
    "  \n",
    "    # making plot\n",
    "    x = summary[\"Bucket_Names\"]\n",
    "    y1 = summary[\"avg_rating\"]\n",
    "    y2 = summary[\"n_ratings\"]\n",
    "    err = summary[\"stddev_rating\"]  \n",
    "\n",
    "    # Plot scatter here\n",
    "    plt.rcParams[\"figure.figsize\"] = [summary.shape[0]+2, 6.0]\n",
    "    plt.rcParams[\"figure.autolayout\"] = True\n",
    "    fig, ax1 = plt.subplots()\n",
    "\n",
    "    bar = ax1.bar(x, y1, color = \"#262261\")\n",
    "    ax1.errorbar(x, y1, yerr=err, fmt=\"o\", color=\"#EE4036\")\n",
    "    ax1.set(ylim=(0, 7))\n",
    "  \n",
    "    #ax1.bar_label(bar , fmt='%.2f', label_type='edge')  \n",
    "    def barlabel(x_list,y_list):\n",
    "        for i in range(len(x_list)):\n",
    "            ax1.text(i,y_list[i] + 0.2,y_list[i], ha = 'center',\n",
    "  \t\t\t         fontdict=dict(size=10),\n",
    "  \t\t\t         bbox=dict(facecolor='#262261', alpha=0.2)         \n",
    "  \t\t\t        )\n",
    "    barlabel(summary[\"Bucket_Names\"].tolist() ,summary[\"avg_rating\"].round(2).tolist())\n",
    "  \n",
    "    ax2 = ax1.twinx()\n",
    "    ax2.scatter(x, y2, s=summary[\"Scaled_number\"]*500, c = '#FAAF40')  \n",
    "    ax2.set(ylim=(0, summary[\"n_ratings\"].max()*1.15))\n",
    "    def scatterlabel(x_list,y_list):\n",
    "  \t    for i in range(len(x_list)):\n",
    "  \t\t    ax2.text(i,y_list[i] + 15000,y_list[i], ha = 'center',\n",
    "  \t\t\t\t\t fontdict=dict(size=10),\n",
    "                     bbox=dict(facecolor='#FAAF40', alpha=0.5)\n",
    "  \t\t\t\t\t)\n",
    "    scatterlabel(summary[\"Bucket_Names\"].tolist() ,summary[\"n_ratings\"].tolist())\n",
    "  \n",
    "    # giving labels to the axises\n",
    "    ax1.set_xlabel(bucketizer.getOutputCol(), fontdict=dict(size=14)) \n",
    "    ax1.set_ylabel(\"Average Ratings\",fontdict=dict(size=14))\n",
    "  \n",
    "    # secondary y-axis label\n",
    "    ax2.set_ylabel('Number of Ratings',fontdict=dict(size=14))\n",
    "  \n",
    "    #plot Title\n",
    "    plt.title('Bucketwise average ratings and number of ratings for \\n'+bucketizer.getInputCol(), \n",
    "              fontdict=dict(size=14)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2da73721",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a8f768bb6a944324ab14450812cee3bc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def bucket_col_print_summary(df, splits, inputCol, outputCol):\n",
    "    \"\"\"\n",
    "    Given a numerical column in a data frame, adds a bucketized version of the column to the data frame, according to splits provided.\n",
    "    Also prints a summary of ratings seen in each bucket made.\n",
    "\n",
    "    Inputs \n",
    "    Argument 1: Data Frame \n",
    "    Argument 2: Values at which the column will be split\n",
    "    Argument 3: Name of the input column (numerical column)\n",
    "    Argument 4: Name of the output column (bucketized numerical column)\n",
    "\n",
    "    Output: \n",
    "    1) New dataframe with the output column added\n",
    "    2) Bucketizer object trained from the input column \n",
    "    3) Pandas dataframe with summary statistics for ratings seen in buckets of the output column\n",
    "    Also plots summary statistics for ratings seen in buckets of the output column\n",
    "    \"\"\"\n",
    "\n",
    "    # Dropping bucket if it already exists\n",
    "    if outputCol in df.columns:\n",
    "        df = df.drop(outputCol)\n",
    "\n",
    "    # Training bucketizer\n",
    "    bucketizer = Bucketizer(splits = splits,\n",
    "                            inputCol  = inputCol,\n",
    "                            outputCol = outputCol)\n",
    "    \n",
    "    df = bucketizer.setHandleInvalid(\"keep\").transform(df)\n",
    "\n",
    "    # Printing meta information on buckets created\n",
    "    print(\"Added bucketized column {}\".format(outputCol))\n",
    "    print(\"\")\n",
    "    print(\"Bucketing done for split definition: {}\".format(splits))\n",
    "    print(\"\")  \n",
    "    print(\"Printing summary statistics for ratings in buckets below:\")\n",
    "\n",
    "    # Creating a summary statistics dataframe and passing it to the plotting function\n",
    "    summary =  (df\n",
    "                .groupBy(outputCol)\n",
    "                .agg(F.avg('rating').alias('avg_rating'),\n",
    "                     F.stddev('rating').alias('stddev_rating'),\n",
    "                     F.count('rating').alias('n_ratings'))\n",
    "                .sort(outputCol)\n",
    "                .toPandas())\n",
    "  \n",
    "    plot_bucketwise_statistics(summary,bucketizer)\n",
    "  \n",
    "    return df, bucketizer, summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "71bd221c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4af761802c0d4b8f93160101f779c113",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def get_column_distribution_summary(df, col_name):\n",
    "    \"\"\"\n",
    "    Takes a column in a data frame and prints the summary statistics (average, standard deviation, count and distinct count) for all unique values in that column.\n",
    "  \n",
    "    Inputs \n",
    "    Argument 1: Dataframe \n",
    "    Argument 2: Name of the column\n",
    "  \n",
    "    Output\n",
    "    Returns nothing \n",
    "    Prints a Dataframe with summary statistics\n",
    "    \"\"\"\n",
    "    print(df\n",
    "          .groupBy(col_name)\n",
    "          .agg(F.avg('rating').alias('avg_rating'),\n",
    "               F.stddev('rating').alias('stddev_rating'),\n",
    "               F.count('rating').alias('n_ratings'),\n",
    "               F.countDistinct('id').alias('n_recipes'))\n",
    "          .sort(F.col(col_name).asc())\n",
    "          .show(50))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "79011c8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a9c4ea0967ee4e2783e613c4688d2e2e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def get_n_items_satisfying_condition (df, condition, aggregation_level = \"recipe\"):\n",
    "    \"\"\"\n",
    "    Given a condition, find the number of recipes / reviews that match the condition.\n",
    "    Also calculates the percentage of such recipes / reviews as a percentage of all recipes / reviews.\n",
    "  \n",
    "    Inputs \n",
    "    Argument 1: Dataframe \n",
    "    Argument 2: Logical expression describing a condition, string type. eg: \"minutes == 0\"\n",
    "    Argument 3: Aggregation level for determining \"items\", either  \"recipe\" or \"review\". Default value == \"recipe\"\n",
    "  \n",
    "    Output: Returns no object.\n",
    "    Prints the following:\n",
    "    1) Number of recipes / reviews that satisfy the condition\n",
    "    2) Total number of recipes / reviews in the dataframe\n",
    "    3) Percentage of recipes / reviews that satisfy the condition\n",
    "    \"\"\"\n",
    "    # Find out num rows satisfying the condition\n",
    "    if aggregation_level == \"recipe\": \n",
    "        number_of_rows_satisfying_condition = (df\n",
    "                                             .filter(condition)\n",
    "                                             .agg(F.countDistinct(\"id\"))).first()[0]\n",
    "      \n",
    "        n_rows_total = (df.agg(F.countDistinct(\"id\"))).first()[0]\n",
    "    if aggregation_level == \"review\":\n",
    "        number_of_rows_satisfying_condition = (df\n",
    "                                             .filter(condition)\n",
    "                                             .agg(F.countDistinct(\"id\",\"user_id\"))).first()[0]\n",
    "        n_rows_total = (df.agg(F.countDistinct(\"id\",\"user_id\"))).first()[0]\n",
    "  \n",
    "    # Find out % rows satisfying the conditon and print a properly formatted output\n",
    "    perc_rows = round(number_of_rows_satisfying_condition * 100/ n_rows_total, 2)\n",
    "    print('Condition String                   : \"{}\"'.format(condition))\n",
    "    print(\"Num {}s Satisfying Condition   : {} [{}%]\".format(aggregation_level.title(), number_of_rows_satisfying_condition, perc_rows))\n",
    "    print(\"Total Num {}s                  : {}\".format(aggregation_level.title(), n_rows_total))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9ec89e88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f2b4c8bb50f249ae88b62d1d024de62a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def add_OHE_columns (df, n_name_list):\n",
    "    \"\"\"\n",
    "    Given a list of tags, creates one hot encoded columns for each tag. \n",
    "  \n",
    "    Input\n",
    "    Argument 1: Dataframe in which the function will add the new columns\n",
    "    Argument 2: list of tags\n",
    "  \n",
    "    Output\n",
    "    Prints the names of columns that have been added \n",
    "    Returns the modified dataframe \n",
    "    \"\"\"\n",
    "    for name in n_name_list:\n",
    "        df = (df.withColumn(\"has_tag_\"+name, F.when(F.array_contains(df.tags, name), 1).otherwise(0)))\n",
    "        print (\"added column: has_tag_\"+name)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f974fc0",
   "metadata": {},
   "source": [
    "## Read the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6716e7a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "48dfc3a1690f4301bd2246385d6ae133",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "interaction_level_df = spark.read.parquet(\"s3a://upgradfoodrecsysdir/interaction_level_df_postEDA.parquet\", header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48ba87c0",
   "metadata": {},
   "source": [
    "## Adding user level average features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "54e70465",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "dc9fd61da3d5473f8f4b4b440995c67b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "partition = Window.partitionBy(\"user_id\")\n",
    "\n",
    "interaction_level_df = (interaction_level_df\n",
    "                        .withColumn(\"user_avg_rating\",\n",
    "                                    F.avg(F.col(\"rating\")).over(partition))\n",
    "                        .withColumn(\"user_n_ratings\",\n",
    "                                    F.count(F.col(\"rating\")).over(partition))\n",
    "                        .withColumn(\"user_avg_years_betwn_review_and_submission\",\n",
    "                                    F.avg(F.col(\"years_since_submission_on_review_date\")).over(partition))\n",
    "                        .withColumn(\"user_avg_prep_time_recipes_reviewed\",\n",
    "                                    F.avg(F.col(\"minutes\")).over(partition))\n",
    "                        .withColumn(\"user_avg_n_steps_recipes_reviewed\",\n",
    "                                    F.avg(F.col(\"n_steps\")).over(partition))\n",
    "                        .withColumn(\"user_avg_n_ingredients_recipes_reviewed\",\n",
    "                                    F.avg(F.col(\"n_ingredients\")).over(partition)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1ab88a49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0f47488852644ad2a1fd7d1c99d599fb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nutrition_cols = ['calories',\n",
    "                  'total_fat_per_100_cal',\n",
    "                  'sugar_per_100_cal',\n",
    "                  'sodium_per_100_cal',\n",
    "                  'protein_per_100_cal',\n",
    "                  'saturated_fat_per_100_cal',\n",
    "                  'carbohydrates_per_100_cal']\n",
    "\n",
    "for nutri_col in nutrition_cols:\n",
    "    interaction_level_df = (interaction_level_df\n",
    "                            .withColumn(\"user_avg_{}_recipes_reviewed\".format(nutri_col),\n",
    "                                        F.avg(F.col(nutri_col)).over(partition)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "12589059",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d438e76afecb44a090dd7c23408a9261",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Code check cell\n",
    "# Do not edit cells with assert commands\n",
    "# If an error is shown after running this cell, please recheck your code. \n",
    "\n",
    "assert(round(interaction_level_df.filter('user_id == 601529').select('user_avg_rating').first()[0], 2) == 4.22)\n",
    "assert(interaction_level_df.filter('user_id == 601529').select('user_n_ratings').first()[0] == 27)\n",
    "assert(round(interaction_level_df.filter('user_id == 601529').select('user_avg_years_betwn_review_and_submission').first()[0], 2) == 3.51)\n",
    "assert(interaction_level_df.filter('user_id == 233044').select('user_avg_prep_time_recipes_reviewed').first()[0] == 50.3)\n",
    "assert(interaction_level_df.filter('user_id == 233044').select('user_avg_n_steps_recipes_reviewed').first()[0] == 8.8)\n",
    "assert(interaction_level_df.filter('user_id == 233044').select('user_avg_n_ingredients_recipes_reviewed').first()[0] == 8.2)\n",
    "assert(round(interaction_level_df.filter('user_id == 233044').select('user_avg_total_fat_per_100_cal_recipes_reviewed').first()[0]) == 6)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7513b134",
   "metadata": {},
   "source": [
    "**More Features:**\n",
    "\n",
    "high_ratings = 5 rating\n",
    "\n",
    "- `user_avg_years_betwn_review_and_submission_high_ratings`\n",
    "- `user_avg_prep_time_recipes_reviewed_high_ratings`\n",
    "- `user_avg_n_steps_recipes_reviewed_high_ratings`\n",
    "- `user_avg_n_ingredients_recipes_reviewed_high_ratings`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "fa15a2fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "22541434523d41e5bf90bdc6b4aea80f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "interaction_level_df = (interaction_level_df\n",
    "                        .withColumn(\"ind_5_rating\",\n",
    "                                    F.when(interaction_level_df[\"rating\"] != 5, None)\n",
    "                                     .otherwise(1))\n",
    "                        .withColumn(\"years_since_submission_on_review_date_5_ratings\",\n",
    "                                    F.when(interaction_level_df[\"rating\"] != 5, None)\n",
    "                                     .otherwise(F.col(\"years_since_submission_on_review_date\")))\n",
    "                        .withColumn(\"minutes_5_ratings\",\n",
    "                                    F.when(interaction_level_df[\"rating\"] != 5, None)\n",
    "                                     .otherwise(F.col(\"minutes\")))\n",
    "                        .withColumn(\"n_steps_5_ratings\",\n",
    "                                    F.when(interaction_level_df[\"rating\"] != 5, None)\n",
    "                                     .otherwise(F.col(\"n_steps\")))\n",
    "                        .withColumn(\"n_ingredients_5_ratings\",\n",
    "                                    F.when(interaction_level_df[\"rating\"] != 5, None)\n",
    "                                     .otherwise(F.col(\"n_ingredients\"))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "4daa41ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "584e2fd0c72149c9b228724f62b6deda",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "partition = Window.partitionBy(\"user_id\")\n",
    "\n",
    "interaction_level_df = (interaction_level_df\n",
    "                        .withColumn(\"user_n_5_ratings\",\n",
    "                                    F.sum(F.col(\"ind_5_rating\")).over(partition))\n",
    "                        .withColumn(\"user_avg_years_betwn_review_and_submission_5_ratings\",\n",
    "                                    F.avg(F.col(\"years_since_submission_on_review_date_5_ratings\")).over(partition))\n",
    "                        .withColumn(\"user_avg_prep_time_recipes_reviewed_5_ratings\",\n",
    "                                    F.avg(F.col(\"minutes_5_ratings\")).over(partition))\n",
    "                        .withColumn(\"user_avg_n_steps_recipes_reviewed_5_ratings\",\n",
    "                                    F.avg(F.col(\"n_steps_5_ratings\")).over(partition))\n",
    "                        .withColumn(\"user_avg_n_ingredients_recipes_reviewed_5_ratings\",\n",
    "                                    F.avg(F.col(\"n_ingredients_5_ratings\")).over(partition)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "49be153d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f03203a2a036402da77c9710d9b80723",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for nutri_col in nutrition_cols:\n",
    "    interaction_level_df = (interaction_level_df\n",
    "                            .withColumn(\"{}_5_ratings\".format(nutri_col),\n",
    "                                        F.when(interaction_level_df[\"rating\"] != 5, None)\n",
    "                                         .otherwise(F.col(nutri_col))))\n",
    "    interaction_level_df = (interaction_level_df\n",
    "                            .withColumn(\"user_avg_{}_recipes_reviewed_5_ratings\".format(nutri_col),\n",
    "                                        F.avg(F.col(\"{}_5_ratings\".format(nutri_col))).over(partition)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "27ec5f9f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "958b4b407d3445219399e09a9d51cf67",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Check - All rows with ratings should have non-null values in corresponding user_avg_5_ratings columns\n",
    "\n",
    "assert(interaction_level_df\n",
    "       .filter(\"rating == 5\")\n",
    "       .filter(interaction_level_df.user_n_5_ratings.isNull() |\n",
    "               interaction_level_df.user_avg_years_betwn_review_and_submission_5_ratings.isNull() |\n",
    "               interaction_level_df.user_avg_prep_time_recipes_reviewed_5_ratings.isNull() |\n",
    "               interaction_level_df.user_avg_n_steps_recipes_reviewed_5_ratings.isNull() |\n",
    "               interaction_level_df.user_avg_n_ingredients_recipes_reviewed_5_ratings.isNull())\n",
    "       .count() == 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "34bcdbfa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7980198228684e859b4325d137dbb7c4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Check values for a given user id\n",
    "\n",
    "assert(interaction_level_df.filter('user_id == 233044').select('user_n_5_ratings').first()[0] == 7)\n",
    "assert(round(interaction_level_df.filter('user_id == 233044').select('user_avg_years_betwn_review_and_submission_5_ratings').first()[0], 2) == 2.24)\n",
    "assert(round(interaction_level_df.filter('user_id == 233044').select('user_avg_prep_time_recipes_reviewed_5_ratings').first()[0]) == 46)\n",
    "assert(round(interaction_level_df.filter('user_id == 233044').select('user_avg_n_steps_recipes_reviewed_5_ratings').first()[0], 2) == 7.29)\n",
    "assert(round(interaction_level_df.filter('user_id == 233044').select('user_avg_n_ingredients_recipes_reviewed_5_ratings').first()[0], 2) == 6.86)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "1b122a38",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "fe40160a72c94d96bbb775d3ba0ca537",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- user_id: integer (nullable = true)\n",
      " |-- recipe_id: integer (nullable = true)\n",
      " |-- rating: integer (nullable = true)\n",
      " |-- review: string (nullable = true)\n",
      " |-- review_date: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- id: integer (nullable = true)\n",
      " |-- minutes: integer (nullable = true)\n",
      " |-- contributor_id: integer (nullable = true)\n",
      " |-- submitted: date (nullable = true)\n",
      " |-- tags: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- nutrition: string (nullable = true)\n",
      " |-- n_steps: integer (nullable = true)\n",
      " |-- steps: string (nullable = true)\n",
      " |-- description: string (nullable = true)\n",
      " |-- ingredients: string (nullable = true)\n",
      " |-- n_ingredients: integer (nullable = true)\n",
      " |-- calories: float (nullable = true)\n",
      " |-- total_fat_PDV: float (nullable = true)\n",
      " |-- sugar_PDV: float (nullable = true)\n",
      " |-- sodium_PDV: float (nullable = true)\n",
      " |-- protein_PDV: float (nullable = true)\n",
      " |-- saturated_fat_PDV: float (nullable = true)\n",
      " |-- carbohydrates_PDV: float (nullable = true)\n",
      " |-- total_fat_per_100_cal: double (nullable = true)\n",
      " |-- sugar_per_100_cal: double (nullable = true)\n",
      " |-- sodium_per_100_cal: double (nullable = true)\n",
      " |-- protein_per_100_cal: double (nullable = true)\n",
      " |-- saturated_fat_per_100_cal: double (nullable = true)\n",
      " |-- carbohydrates_per_100_cal: double (nullable = true)\n",
      " |-- days_since_submission_on_review_date: integer (nullable = true)\n",
      " |-- months_since_submission_on_review_date: double (nullable = true)\n",
      " |-- years_since_submission_on_review_date: double (nullable = true)\n",
      " |-- years_since_submission_on_review_date_bucket: double (nullable = true)\n",
      " |-- prep_time_bucket: double (nullable = true)\n",
      " |-- n_steps_bucket: double (nullable = true)\n",
      " |-- n_ingredients_bucket: double (nullable = true)\n",
      " |-- calories_bucket: double (nullable = true)\n",
      " |-- total_fat_PDV_bucket: double (nullable = true)\n",
      " |-- sugar_PDV_bucket: double (nullable = true)\n",
      " |-- sodium_PDV_bucket: double (nullable = true)\n",
      " |-- protein_PDV_bucket: double (nullable = true)\n",
      " |-- saturated_fat_PDV_bucket: double (nullable = true)\n",
      " |-- carbohydrates_PDV_bucket: double (nullable = true)\n",
      " |-- total_fat_per_100_cal_bucket: double (nullable = true)\n",
      " |-- sugar_per_100_cal_bucket: double (nullable = true)\n",
      " |-- sodium_per_100_cal_bucket: double (nullable = true)\n",
      " |-- protein_per_100_cal_bucket: double (nullable = true)\n",
      " |-- saturated_fat_per_100_cal_bucket: double (nullable = true)\n",
      " |-- carbohydrates_per_100_cal_bucket: double (nullable = true)\n",
      " |-- user_avg_rating: double (nullable = true)\n",
      " |-- user_n_ratings: long (nullable = false)\n",
      " |-- user_avg_years_betwn_review_and_submission: double (nullable = true)\n",
      " |-- user_avg_prep_time_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_n_steps_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_n_ingredients_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_calories_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_total_fat_per_100_cal_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_sugar_per_100_cal_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_sodium_per_100_cal_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_protein_per_100_cal_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_saturated_fat_per_100_cal_recipes_reviewed: double (nullable = true)\n",
      " |-- user_avg_carbohydrates_per_100_cal_recipes_reviewed: double (nullable = true)\n",
      " |-- ind_5_rating: integer (nullable = true)\n",
      " |-- years_since_submission_on_review_date_5_ratings: double (nullable = true)\n",
      " |-- minutes_5_ratings: integer (nullable = true)\n",
      " |-- n_steps_5_ratings: integer (nullable = true)\n",
      " |-- n_ingredients_5_ratings: integer (nullable = true)\n",
      " |-- user_n_5_ratings: long (nullable = true)\n",
      " |-- user_avg_years_betwn_review_and_submission_5_ratings: double (nullable = true)\n",
      " |-- user_avg_prep_time_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- user_avg_n_steps_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- user_avg_n_ingredients_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- calories_5_ratings: float (nullable = true)\n",
      " |-- user_avg_calories_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- total_fat_per_100_cal_5_ratings: double (nullable = true)\n",
      " |-- user_avg_total_fat_per_100_cal_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- sugar_per_100_cal_5_ratings: double (nullable = true)\n",
      " |-- user_avg_sugar_per_100_cal_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- sodium_per_100_cal_5_ratings: double (nullable = true)\n",
      " |-- user_avg_sodium_per_100_cal_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- protein_per_100_cal_5_ratings: double (nullable = true)\n",
      " |-- user_avg_protein_per_100_cal_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- saturated_fat_per_100_cal_5_ratings: double (nullable = true)\n",
      " |-- user_avg_saturated_fat_per_100_cal_recipes_reviewed_5_ratings: double (nullable = true)\n",
      " |-- carbohydrates_per_100_cal_5_ratings: double (nullable = true)\n",
      " |-- user_avg_carbohydrates_per_100_cal_recipes_reviewed_5_ratings: double (nullable = true)"
     ]
    }
   ],
   "source": [
    "interaction_level_df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d947f240",
   "metadata": {},
   "source": [
    "## Tags level EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c0537107",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e02c901eedae49aab28992249229f966",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "interaction_tag_level_df = interaction_level_df.withColumn('individual_tag',F.explode('tags'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "a15d9938",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c4a62744b349418698e63d241753f542",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "tags_ratings_summary = (interaction_tag_level_df\n",
    "                        .groupBy('individual_tag').agg(F.avg('rating').alias('avg_user_rating'),\n",
    "#                                                      F.max('rating').alias('max_user_rating'),\n",
    "#                                                      F.min('rating').alias('min_user_rating'),\n",
    "                                                       F.count('rating').alias('n_user_ratings'),\n",
    "                                                       F.countDistinct('id').alias('n_recipes')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "1aea4dfa",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "741b2054d57f476791986869e2700ce7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "interactions, recipes  =  interaction_level_df.count(), interaction_level_df.agg(F.countDistinct('id')).first()[0]\n",
    "\n",
    "tags_ratings_summary = (tags_ratings_summary.withColumn(\"in_percent_recipies\", F.col (\"n_recipes\")/F.lit(recipes))\n",
    "                                            .withColumn(\"in_percent_interactions\", F.col (\"n_user_ratings\")/F.lit(interactions)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b73f1b2",
   "metadata": {},
   "source": [
    "#### 1. Top ```n``` most rated tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "8969bb9a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b965bcd14e214a4784dc44ddd020a5a8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+------------------+--------------+---------+-------------------+-----------------------+\n",
      "|    individual_tag|   avg_user_rating|n_user_ratings|n_recipes|in_percent_recipies|in_percent_interactions|\n",
      "+------------------+------------------+--------------+---------+-------------------+-----------------------+\n",
      "|       preparation|4.4119124813277715|       1123326|   229318| 0.9952779007491125|     0.9970859455232471|\n",
      "|      time-to-make| 4.414416558383976|       1105132|   224098| 0.9726222407402585|       0.98093659823417|\n",
      "|            course| 4.412402044928726|       1071920|   217130| 0.9423799727437654|     0.9514569828574067|\n",
      "|           dietary| 4.412032038984685|        901277|   163918| 0.7114311259255401|     0.7999909462821618|\n",
      "|   main-ingredient| 4.424040070642098|        864074|   169549| 0.7358705936477349|     0.7669688418963456|\n",
      "|              easy|4.4183637556952755|        630786|   125789| 0.5459449840715953|     0.5598978882646952|\n",
      "|          occasion|4.4144829634028655|        619666|   113433| 0.4923179083878024|     0.5500275605822428|\n",
      "|         equipment| 4.415547752950291|        496985|    69892| 0.3033427948924941|     0.4411335254733452|\n",
      "|           cuisine| 4.416942151349161|        478853|    90639|0.39338819301580685|    0.42503921058681404|\n",
      "|  low-in-something| 4.414730950603082|        445959|    85258|0.37003376648177566|    0.39584185817794815|\n",
      "|         main-dish| 4.395996656937766|        384079|    71531|  0.310456324922094|    0.34091596995940915|\n",
      "|60-minutes-or-less| 4.405568569863525|        343212|    69929|0.30350338098834234|    0.30464162810700074|\n",
      "|number-of-servings| 4.407139294746751|        338857|    58410| 0.2535090232025208|     0.3007760456378389|\n",
      "|              meat| 4.408259712746521|        319091|    55769| 0.2420466480907615|    0.28323136065840054|\n",
      "|        taste-mood| 4.412428615527087|        310992|    52060| 0.2259489770231678|    0.27604253117097416|\n",
      "|    north-american| 4.413212293557913|        283433|    48182|0.20911781811237554|    0.25158062823925603|\n",
      "|30-minutes-or-less|4.4268528818028265|        267003|    55059|0.23896513111637718|    0.23699704156455345|\n",
      "|        vegetables| 4.454577657305231|        259718|    53562|0.23246790448165414|    0.23053073426539286|\n",
      "|              oven| 4.417805174050443|        249669|    30777| 0.1335772505924325|    0.22161104695595366|\n",
      "|   4-hours-or-less| 4.383299863701983|        247986|    49450|0.21462114701874083|    0.22011718351264725|\n",
      "+------------------+------------------+--------------+---------+-------------------+-----------------------+\n",
      "only showing top 20 rows"
     ]
    }
   ],
   "source": [
    "tags_ratings_summary.sort(F.col(\"n_user_ratings\").desc()).show(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44d3b60e",
   "metadata": {},
   "source": [
    "Drop tags present in majority of recipes. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "a8601fb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0a1288844efc4d7b920491c5a63ef317",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "tags_ratings_summary = tags_ratings_summary.filter(tags_ratings_summary.in_percent_interactions < 0.75)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "8d7c6535",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "af5585f9a4634944ad44de82424300d4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top_most_frequent_tags = tags_ratings_summary.sort(F.col(\"n_user_ratings\").desc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "a68f45de",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "93ba7e70ce79447e8cac9925b77ca684",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{0.0: 8.876193959039915e-07, 0.01: 8.876193959039915e-07, 0.25: 0.00035948585534111656, 0.5: 0.0034235480100016954, 0.75: 0.018886765506045133, 0.8: 0.02746826982564492, 0.85: 0.04430729738533955, 0.9: 0.08068815356525645, 0.95: 0.16414301678754564, 0.99: 0.34091596995940915, 1.0: 0.5598978882646952}"
     ]
    }
   ],
   "source": [
    "get_quantiles(df = top_most_frequent_tags , \n",
    "              col_name = 'in_percent_interactions', \n",
    "              quantiles_list = [0.01,0.25,0.5, 0.75,0.8,0.85,0.9,0.95, 0.99])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "4bc86bfb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "141be896183f447ea9255ba70ba0cd40",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29"
     ]
    }
   ],
   "source": [
    "# keep tags appearing in the top 5 percentile \n",
    "top_most_frequent_tags = top_most_frequent_tags.filter(\"in_percent_interactions > 0.16\")\n",
    "\n",
    "top_most_frequent_tags.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "a80d5598",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a70fce4298844c90a8eb69a88d052942",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top_frequent_tags_list = [data[0] for data in top_most_frequent_tags.select('individual_tag').collect()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "0323df86",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7b2990b55f4f4c5caebdb6dceb566390",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "added column: has_tag_easy\n",
      "added column: has_tag_occasion\n",
      "added column: has_tag_equipment\n",
      "added column: has_tag_cuisine\n",
      "added column: has_tag_low-in-something\n",
      "added column: has_tag_main-dish\n",
      "added column: has_tag_60-minutes-or-less\n",
      "added column: has_tag_number-of-servings\n",
      "added column: has_tag_meat\n",
      "added column: has_tag_taste-mood\n",
      "added column: has_tag_north-american\n",
      "added column: has_tag_30-minutes-or-less\n",
      "added column: has_tag_vegetables\n",
      "added column: has_tag_oven\n",
      "added column: has_tag_4-hours-or-less\n",
      "added column: has_tag_low-carb\n",
      "added column: has_tag_holiday-event\n",
      "added column: has_tag_desserts\n",
      "added column: has_tag_healthy\n",
      "added column: has_tag_dinner-party\n",
      "added column: has_tag_15-minutes-or-less\n",
      "added column: has_tag_low-sodium\n",
      "added column: has_tag_american\n",
      "added column: has_tag_beginner-cook\n",
      "added column: has_tag_low-cholesterol\n",
      "added column: has_tag_low-calorie\n",
      "added column: has_tag_inexpensive\n",
      "added column: has_tag_comfort-food\n",
      "added column: has_tag_kid-friendly"
     ]
    }
   ],
   "source": [
    "interaction_level_df = add_OHE_columns (interaction_level_df, top_frequent_tags_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33744553",
   "metadata": {},
   "source": [
    "#### 2.  Bottom ```n``` least rated tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "8b02c0de",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "efe7956aa3b34b1bb424e42f3adcd02b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---------------+--------------+---------+--------------------+-----------------------+\n",
      "|      individual_tag|avg_user_rating|n_user_ratings|n_recipes| in_percent_recipies|in_percent_interactions|\n",
      "+--------------------+---------------+--------------+---------+--------------------+-----------------------+\n",
      "|lamb-sheep-main-dish|            0.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "|     black-bean-soup|            3.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "|Throw the ultimat...|            5.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "|       chicken-stews|            4.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "|       desserts-easy|            5.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "+--------------------+---------------+--------------+---------+--------------------+-----------------------+\n",
      "only showing top 5 rows"
     ]
    }
   ],
   "source": [
    "tags_ratings_summary.sort(F.col(\"n_user_ratings\").asc()).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28edd839",
   "metadata": {},
   "source": [
    "The above tags are present in 1 recipe in over two hundred thousand. The features we create based on these tags will not teach the model new information. If these tags were one hot encoded, the entire column would be filled with zeros, and only a few rows will have 1s. One hot encoding of these tags is not a good idea. If you come up with an encoding that captures the rarity of these tags, only then can you add these tags to the analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3bacac2",
   "metadata": {},
   "source": [
    "#### 3. Top ```n``` rated tags "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "7f4391c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "66f895db486a458aa1397980ead92b8a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---------------+--------------+---------+--------------------+-----------------------+\n",
      "|      individual_tag|avg_user_rating|n_user_ratings|n_recipes| in_percent_recipies|in_percent_interactions|\n",
      "+--------------------+---------------+--------------+---------+--------------------+-----------------------+\n",
      "|  breakfast-potatoes|            5.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "|           pork-loin|            5.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "|middle-eastern-ma...|            5.0|             2|        1|4.340164752654011E-6|   1.775238791807983E-6|\n",
      "|Throw the ultimat...|            5.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "|       desserts-easy|            5.0|             1|        1|4.340164752654011E-6|   8.876193959039915E-7|\n",
      "+--------------------+---------------+--------------+---------+--------------------+-----------------------+\n",
      "only showing top 5 rows"
     ]
    }
   ],
   "source": [
    "tags_ratings_summary.sort(F.col(\"avg_user_rating\").desc()).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ba743fa",
   "metadata": {},
   "source": [
    "Top rated tags have low number of ratings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "50c62424",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e3168923c7a74e858cab5ddd0d82e3e5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{0.0: 104, 0.01: 114.0, 0.05: 180.0, 0.1: 330.0, 0.15: 522.0, 0.2: 806.0, 0.25: 1143.0, 0.5: 6312.0, 0.75: 27839.0, 0.99: 445959.0, 1.0: 630786}"
     ]
    }
   ],
   "source": [
    "get_quantiles (tags_ratings_summary, \"n_user_ratings\", quantiles_list = [0.01, 0.05, 0.1, 0.15, 0.2, 0.25, 0.5, 0.75, 0.99])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "eec928b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "78588aa0260a41198bf7c8870f671e25",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "tags_ratings_summary = tags_ratings_summary.filter(tags_ratings_summary.n_user_ratings > 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "f52df5f6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "84cc6b639b6a48288bc5c4a662b41b3c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top_rated_tags_df = tags_ratings_summary.sort(F.col(\"avg_user_rating\").desc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "558d9eae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a21771f18cec4e0d9c08d1778d20be93",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{0.0: 3.6742424242424243, 0.01: 3.962536023054755, 0.25: 4.344224006718102, 0.5: 4.403587357364895, 0.75: 4.451890903550206, 0.8: 4.461777662606893, 0.85: 4.48284199758207, 0.9: 4.503597122302159, 0.95: 4.536699941280094, 0.99: 4.609756097560975, 1.0: 4.822727272727272}"
     ]
    }
   ],
   "source": [
    "get_quantiles(df = top_rated_tags_df , \n",
    "              col_name = 'avg_user_rating', \n",
    "              quantiles_list = [0.01,0.25,0.5, 0.75,0.8,0.85,0.9,0.95, 0.99])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "62a0ca52",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d72873e970f341079af4fce347ed54ff",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26"
     ]
    }
   ],
   "source": [
    "# keep tags above 95 percentile\n",
    "top_rated_tags_df = top_rated_tags_df.filter(\"avg_user_rating > 4.53\")\n",
    "\n",
    "top_rated_tags_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "06ccccb4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "781dee35ad9b4b9e928cfe2e887f73eb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top_rated_tags_list = [data[0] for data in top_rated_tags_df.select('individual_tag').collect()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cfe9edf",
   "metadata": {},
   "source": [
    "Check if any of the current tags have been added earlier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "aaafc25c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c2e6b2a863524db290751de1926f0c8d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "set()"
     ]
    }
   ],
   "source": [
    "set(top_frequent_tags_list) & set(top_rated_tags_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "31c5a490",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "bc4e414485364d44be207520480311b3",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "all_added_columns_set = set(top_frequent_tags_list).union(set(top_rated_tags_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "f5693caa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a6c2dde53a3242d48859f147b2c0efe6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "added column: has_tag_ragu-recipe-contest\n",
      "added column: has_tag_simply-potatoes2\n",
      "added column: has_tag_non-alcoholic\n",
      "added column: has_tag_a1-sauce\n",
      "added column: has_tag_labor-day\n",
      "added column: has_tag_punch\n",
      "added column: has_tag_lettuces\n",
      "added column: has_tag_cocktails\n",
      "added column: has_tag_mashed-potatoes\n",
      "added column: has_tag_smoothies\n",
      "added column: has_tag_turkey-burgers\n",
      "added column: has_tag_avocado\n",
      "added column: has_tag_beverages\n",
      "added column: has_tag_mango\n",
      "added column: has_tag_asparagus\n",
      "added column: has_tag_memorial-day\n",
      "added column: has_tag_shakes\n",
      "added column: has_tag_strawberries\n",
      "added column: has_tag_omelets-and-frittatas\n",
      "added column: has_tag_salsas\n",
      "added column: has_tag_greek\n",
      "added column: has_tag_salads\n",
      "added column: has_tag_barbecue\n",
      "added column: has_tag_australian\n",
      "added column: has_tag_grilling\n",
      "added column: has_tag_polynesian"
     ]
    }
   ],
   "source": [
    "interaction_level_df = add_OHE_columns (interaction_level_df, top_rated_tags_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de06488a",
   "metadata": {},
   "source": [
    "#### 3. Bottom ```n``` rated tags "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "d5f3abff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "87095bfb3da64b4da88912f4b6f93a91",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bottom_rated_tags_df = tags_ratings_summary.sort(F.col(\"avg_user_rating\").asc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "d21ffd36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ceb852a243244d828fb9d5b7a8486f0a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{0.0: 3.6742424242424243, 0.01: 3.962536023054755, 0.02: 4.054406964091403, 0.03: 4.096385542168675, 0.04: 4.126760563380282, 0.05: 4.171974522292993, 0.1: 4.238095238095238, 0.15: 4.303225806451613, 0.2: 4.325726141078838, 0.25: 4.344224006718102, 0.5: 4.403587357364895, 0.75: 4.451890903550206, 0.99: 4.609756097560975, 1.0: 4.822727272727272}"
     ]
    }
   ],
   "source": [
    "get_quantiles (bottom_rated_tags_df, \"avg_user_rating\", quantiles_list = [0.01, 0.02, 0.03, 0.04, 0.05, 0.1, 0.15, 0.2, 0.25, 0.5, 0.75, 0.99])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "2166012b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3ab6a06d456e423791438b402e6ae2b1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6"
     ]
    }
   ],
   "source": [
    "bottom_rated_tags_df = bottom_rated_tags_df.filter(\"avg_user_rating < 4.00\")\n",
    "\n",
    "bottom_rated_tags_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "ad52943b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "77ea0908bce0418887b8f8e25321189f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bottom_rated_tags_list = [data[0] for data in bottom_rated_tags_df.select('individual_tag').collect()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "06b5caf9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b69159d03fa64144954153d3a508c183",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "set()"
     ]
    }
   ],
   "source": [
    "all_added_columns_set & set(bottom_rated_tags_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "b044ac62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "78fc8bf814a144e5a2fd0ba6b0cafed8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "added column: has_tag_pressure-canning\n",
      "added column: has_tag_honduran\n",
      "added column: has_tag_unprocessed-freezer\n",
      "added column: has_tag_birthday\n",
      "added column: has_tag_jellies\n",
      "added column: has_tag_water-bath"
     ]
    }
   ],
   "source": [
    "interaction_level_df =  add_OHE_columns(interaction_level_df, bottom_rated_tags_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fedadf63",
   "metadata": {},
   "source": [
    "## Final DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "5dffa017",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cb809497568449838746a463fc41fbd1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "148"
     ]
    }
   ],
   "source": [
    "len(interaction_level_df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c878b1df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "841c9922765d4450aa5e86d49fd9eb0f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b9c932de8ade48b2b783f039a7dee8db",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "interaction_level_df.write.mode('overwrite').parquet('s3a://upgradfoodrecsysdir/interaction_level_df_ModelReady.parquet')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PySpark",
   "language": "",
   "name": "pysparkkernel"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "python",
    "version": 3
   },
   "mimetype": "text/x-python",
   "name": "pyspark",
   "pygments_lexer": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}