03-Spark/002.02-Analyze-MovieLens-using-PySpark.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "582c56e9-64ed-4e75-b701-8e456d4b0073",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Data Analysis using __PySpark__  \n",
    "*Fun with the __MovieLens__ dataset*  \n",
    "\n",
    "**Part 2: Data Analysis basics using tags.csv from the MovieLens dataset**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d66c3ea4-b2f7-4fcb-bd16-de39c475fe45",
   "metadata": {},
   "source": [
    "<font color='green'>__Support for Google Colab__  </font>\n",
    "\n",
    "open this notebook in Colab using the following button:  \n",
    "  \n",
    "<a href=\"https://colab.research.google.com/github/shauryashaurya/learn-data-munging/blob/main/03-Spark/002.02-Analyze-MovieLens-using-PySpark.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>  \n",
    "\n",
    "<font color='green'>uncomment and execute the cell below to setup and run this Spark notebook on Google Colab.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "da7fdc50-355c-4550-b9d2-f5d51bdfe4b5",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# # SETUP FOR COLAB: select all the lines below and uncomment (CTRL+/ on windows)\n",
    "\n",
    "# # grab spark\n",
    "# # as of Jan 2025, the *working* version is 3.5.4, get the link from Apache Spark's website\n",
    "# ! wget -q https://dlcdn.apache.org/spark/spark-3.5.4/spark-3.5.4-bin-hadoop3.tgz\n",
    "# # unzip spark\n",
    "# !tar xf spark-3.5.4-bin-hadoop3.tgz\n",
    "# # install findspark package\n",
    "# !pip install -q findspark\n",
    "# # Let's download and unzip the MovieLens 25M Dataset as well.\n",
    "# ! mkdir ./../data\n",
    "# ! wget -q https://files.grouplens.org/datasets/movielens/ml-25m.zip\n",
    "# ! unzip ./ml-25m.zip -d ./../data/\n",
    "\n",
    "# # got to provide JAVA_HOME and SPARK_HOME vairables\n",
    "# import os\n",
    "# # IMPORTANT - check the version of java, use 11 or 17, code not tested on 21 yet\n",
    "# os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
    "# # IMPORTANT - UPDATE THE SPARK_HOME PATH BASED ON THE PACKAGE YOU DOWNLOAD\n",
    "# os.environ[\"SPARK_HOME\"] = \"/content/spark-3.5.4-bin-hadoop3\"\n",
    "# ! echo \"DONE\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75c9e7e5-5209-4df6-be1f-51a08cfb141d",
   "metadata": {},
   "source": [
    "## Start the local/colab Spark Cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "45890b3f-cf5c-4421-9533-767d60a2d4ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 1: initialize findspark\n",
    "import findspark\n",
    "\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6b0f2108-9e49-49a0-8f2d-fdf33a6194c4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.5.4\n"
     ]
    }
   ],
   "source": [
    "# Step 2: import pyspark\n",
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "print(pyspark.__version__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ee23a7b8-45ab-4642-8086-dacdb8769121",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 3: Create a spark session\n",
    "#     using local[*] to use as many logical cores as available, use 1 when in doubt\n",
    "#     'local[1]' indicates spark on 1 core on the local machine or specify the number of cores needed\n",
    "#     use .config(\"spark.some.config.option\", \"some-value\") for additional configuration\n",
    "\n",
    "spark = (\n",
    "    SparkSession.builder.master(\"local[*]\")\n",
    "    .appName(\"Analyzing Movielens Data\")\n",
    "    .getOrCreate()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6592b964-b4dd-468f-bfd9-5bb7031f4bdb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://The-Wonder-Of-U:4043\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.5.4</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>Analyzing Movielens Data</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x22ef1bc8c90>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e2cd306-d816-4cad-afe0-51bc62341c75",
   "metadata": {},
   "source": [
    "# Problem Set 1  - ```tags.csv```\n",
    "\n",
    "1. __Cleanup data__ Some tags may have wrapping quotation marks or similar special characters, also leading/trailing spaces etc.\n",
    "    * for example, the tag ```\"A Christmas Carol\"``` becomes ```A Christmas Carol``` etc.\n",
    "  \n",
    "1. List all unique tags found in ```tags.csv```  \n",
    "    * sort all tags lexically \n",
    "    * Also print the execution plan  \n",
    "    * *[think]* If there are multiple ways of doing this, compare the execution plans  \n",
    "    \n",
    "1. Which movies have the most number of tags? \n",
    "    * List movieIds in order of # of tags associated  \n",
    "    * Can we find out which movies have most # of *distinct* tags?\n",
    "    * Does the ```timestamp``` column have any impact on your report?\n",
    "  \n",
    "1. Which users have added the most number of tags?\n",
    "    * List userIds in order of # of tags created  \n",
    "    * Can we find out which users have created most # of *distinct* tags?\n",
    "    * Does the ```timestamp``` column have any impact on your report?\n",
    "  \n",
    "1. Which users have tagged the most number of movies?\n",
    "    * Like before, can we find out which users have tagged the most # of *distinct* movies? Does this question make sense in the context of users?\n",
    "    * Does the ```timestamp``` column have any impact on your report?\n",
    "  \n",
    "1. *[optional, skip on first attept]* We want to find out if there were days of higher activity during the tagging exercise or if the tagging output was more-or-less consistent. \n",
    "    * Convert time-stamps to Day-Month-Year. \n",
    "    * Find the date range (min-date, max-date) during which the tagging activity took place.\n",
    "    * Plot number of movies tagged per day during the date range\n",
    "\n",
    "1. *[optional, skip on first attept]* We want to find out how many users were active every day of the tagging activity. \n",
    "    * Plot number of users who tagged at least one movie during the tagging activity date range"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed96e1c2-0a1c-4331-9529-93baab52939c",
   "metadata": {},
   "source": [
    "# Load Tags data from the MovieLens dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "47cd64c2-9b31-4665-96cb-7fd1ec57f0e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.types import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d4e7db1f-9846-4a0c-87b8-5c8a2954e499",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "datalocation = \"../data/ml-25m/\"\n",
    "file_path_tags = datalocation + \"tags.csv\"\n",
    "#\n",
    "schema_tags = StructType(\n",
    "    [\n",
    "        StructField(\"userId\", StringType(), False),\n",
    "        StructField(\"movieId\", StringType(), False),\n",
    "        StructField(\"tag\", StringType(), True),\n",
    "        StructField(\"timestamp\", StringType(), True),\n",
    "    ]\n",
    ")\n",
    "#\n",
    "tags_raw = (\n",
    "    spark.read.format(\"csv\")\n",
    "    .option(\"encoding\", \"UTF-8\")\n",
    "    .option(\"header\", True)\n",
    "    .option(\"sep\", \",\")\n",
    "    .option(\"escape\", '\"')\n",
    "    .schema(schema_tags)\n",
    "    .load(file_path_tags)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6b78fc8b-faae-4718-a4e8-8e79305ddb3c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-------+-----------------------+----------+\n",
      "|userId|movieId|tag                    |timestamp |\n",
      "+------+-------+-----------------------+----------+\n",
      "|3     |260    |classic                |1439472355|\n",
      "|3     |260    |sci-fi                 |1439472256|\n",
      "|4     |1732   |dark comedy            |1573943598|\n",
      "|4     |1732   |great dialogue         |1573943604|\n",
      "|4     |7569   |so bad it's good       |1573943455|\n",
      "|4     |44665  |unreliable narrators   |1573943619|\n",
      "|4     |115569 |tense                  |1573943077|\n",
      "|4     |115713 |artificial intelligence|1573942979|\n",
      "|4     |115713 |philosophical          |1573943033|\n",
      "|4     |115713 |tense                  |1573943042|\n",
      "+------+-------+-----------------------+----------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tags_raw.show(10, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4b8effc-d4ea-40a3-bddf-255178a0aa6d",
   "metadata": {},
   "source": [
    "# Solutions to Problem Set 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e17dedd8-4778-4d88-a8d7-75693dc66589",
   "metadata": {},
   "source": [
    "### Cleanup  \n",
    "Some tags may have wrapping quotation marks or similar special characters, also leading/trailing spaces etc. Cleanup tags  \n",
    "\n",
    "* for example, the tag \"A Christmas Carol\" becomes A Christmas Carol etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d5a0b587-b858-43a5-90ac-460f4d931527",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove wrapping quotation marks from tags\n",
    "\n",
    "# if there's cleanup needed,\n",
    "# we do that first before finding out unique records,\n",
    "# chances are uniqueness will be affected if we don't follow this order.\n",
    "\n",
    "# code clean up update - importing * from pyspark.sql.functions at the top of the notebook\n",
    "# from pyspark.sql.functions import col, lower, regexp_replace, trim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "38a4a5ff-4057-41a7-9877-d14da244d7a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# define a regex pattern to use.\n",
    "# ^ indicates start of string, $ indicates end of string\n",
    "# double quote or single quote or ampersand or bang just after start of string\n",
    "pattern_start = \"^\\\"|^'|^&|^#\"\n",
    "# double quote or single quote or ampersand or bang just before end of string\n",
    "pattern_end = \"\\\"$|'$|&$|#$\"\n",
    "pattern = pattern_start + \"|\" + pattern_end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "10fa3f01-2f6a-43a6-92f7-ba140dcdccb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new tags dataframe with cleaned up data\n",
    "tags = tags_raw.withColumn(\"tag\", regexp_replace(trim(col(\"tag\")), pattern, \"\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "8df83907-e043-4672-b77f-3d8cf84dd40f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-------+-------------------+----------+\n",
      "|userId|movieId|                tag| timestamp|\n",
      "+------+-------+-------------------+----------+\n",
      "| 63844|   4023|\"A Christmas Carol\"|1457037403|\n",
      "+------+-------+-------------------+----------+\n",
      "\n",
      "+------+-------+-----------------+----------+\n",
      "|userId|movieId|              tag| timestamp|\n",
      "+------+-------+-----------------+----------+\n",
      "| 63844|   4023|A Christmas Carol|1457037403|\n",
      "+------+-------+-----------------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# quotes stripped and other chars too...\n",
    "tags_raw.filter(col(\"tag\").contains(\"Christmas Carol\")).show()\n",
    "tags.filter(col(\"tag\").contains(\"Christmas Carol\")).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "166e88b5-5ac1-4f8b-901d-f873750fd89c",
   "metadata": {},
   "source": [
    "### List all unique tags found in ```tags.csv```  \n",
    "    * sort all tags lexically \n",
    "    * Also print the execution plan  \n",
    "    * *[think]* If there are multiple ways of doing this, compare the execution plans  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "be8c300d-8300-4eda-ab5c-988e6359eb44",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-------+----------------+----------------+----------+\n",
      "|userId|movieId|tag             |lower tag       |timestamp |\n",
      "+------+-------+----------------+----------------+----------+\n",
      "|3     |260    |classic         |classic         |1439472355|\n",
      "|3     |260    |sci-fi          |sci-fi          |1439472256|\n",
      "|4     |1732   |dark comedy     |dark comedy     |1573943598|\n",
      "|4     |1732   |great dialogue  |great dialogue  |1573943604|\n",
      "|4     |7569   |so bad it's good|so bad it's good|1573943455|\n",
      "+------+-------+----------------+----------------+----------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# unique tags found in tags.csv - prep\n",
    "# careful - comparision should be case-insensitive\n",
    "# because 'Christmas Special' and 'Christmas special' are really the same thing\n",
    "# create a new column in tags\n",
    "tags = tags.withColumn(\"lower tag\", lower(col(\"tag\")))\n",
    "# reorder columns for easy readability\n",
    "tags = tags.select(\"userId\", \"movieId\", \"tag\", \"lower tag\", \"timestamp\")\n",
    "tags.show(5, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a93af9b0-9a58-4da0-b584-6b2fc82e64aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+\n",
      "|lower tag    |\n",
      "+-------------+\n",
      "|anime        |\n",
      "|art          |\n",
      "|traveling    |\n",
      "|travel       |\n",
      "|loss of child|\n",
      "+-------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# unique tags found in tags.csv - method 1, using distinct()\n",
    "distinct_tags1 = tags.select(\"lower tag\").distinct()\n",
    "#\n",
    "# let's do the explaining later, so it's easy to compare methods\n",
    "# distinct_tags.explain(True)\n",
    "# show 5 rows, do not truncate\n",
    "distinct_tags1.show(5, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c0b63172-4976-4526-819d-76bd4edba583",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+-----+\n",
      "|lower tag    |count|\n",
      "+-------------+-----+\n",
      "|anime        |1585 |\n",
      "|art          |340  |\n",
      "|traveling    |14   |\n",
      "|travel       |256  |\n",
      "|loss of child|21   |\n",
      "+-------------+-----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# unique tags found in tags.csv - method 2, using groupBy()\n",
    "# an aggregator like count() results in a dataframe\n",
    "# distinct_tags2 = tags.select('lower tag').groupBy('lower tag').count()\n",
    "distinct_tags2 = tags.select(\"lower tag\").groupBy(\"lower tag\").count()\n",
    "# let's do the explaining later, so it's easy to compare methods\n",
    "# distinct_tags2.explain(True)\n",
    "# show 5 rows, no truncate\n",
    "distinct_tags2.show(5, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d5554dd-6d9f-4907-8fc9-8fbafe8f20ab",
   "metadata": {},
   "source": [
    "Compare execution plan explanations for distinct() and groupBy()...  \n",
    "(uncomment the lines below)\n",
    "also [reference documentation](https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrame.explain.html#pyspark.sql.DataFrame.explain)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "eaa1ee7c-9448-4a03-a4c9-aa988842b43b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# extended set as True\n",
    "# to see something specific choose a mode\n",
    "# distinct_tags1.explain(True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "08531a49-5b1a-4be1-b171-7a7eb56027cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# distinct_tags2.explain(True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0a244a0-c16b-4e6a-8327-0e95fedd0503",
   "metadata": {},
   "source": [
    "Compare the costs in the two execution plans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "1bc3f169-c443-42e6-9469-99c0c9b6db6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Optimized Logical Plan ==\n",
      "Aggregate [lower tag#77], [lower tag#77], Statistics(sizeInBytes=11.8 MiB)\n",
      "+- Project [lower(regexp_replace(trim(tag#2, None), ^\"|^'|^&|^#|\"$|'$|&$|#$, , 1)) AS lower tag#77], Statistics(sizeInBytes=11.8 MiB)\n",
      "   +- Relation [userId#0,movieId#1,tag#2,timestamp#3] csv, Statistics(sizeInBytes=37.0 MiB)\n",
      "\n",
      "== Physical Plan ==\n",
      "AdaptiveSparkPlan isFinalPlan=false\n",
      "+- HashAggregate(keys=[lower tag#77], functions=[], output=[lower tag#77])\n",
      "   +- Exchange hashpartitioning(lower tag#77, 200), ENSURE_REQUIREMENTS, [plan_id=176]\n",
      "      +- HashAggregate(keys=[lower tag#77], functions=[], output=[lower tag#77])\n",
      "         +- Project [lower(regexp_replace(trim(tag#2, None), ^\"|^'|^&|^#|\"$|'$|&$|#$, , 1)) AS lower tag#77]\n",
      "            +- FileScan csv [tag#2] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex(1 paths)[file:/C:/shaurya/lab/learn-data-munging/data/ml-25m/tags.csv], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<tag:string>\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "distinct_tags1.explain(mode=\"cost\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "367cc8cb-374b-40f7-b152-34c179546b55",
   "metadata": {},
   "outputs": [],
   "source": [
    "# distinct_tags2.explain(mode='cost')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a03e154-c41d-4cbf-9e46-f1d3d5ea8f18",
   "metadata": {},
   "source": [
    "The two plans look identical:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10286ed2-eb91-4677-95a1-065491f7691b",
   "metadata": {},
   "source": [
    "**distinct()**  \n",
    "  \n",
    "```\n",
    "== Physical Plan ==\n",
    "AdaptiveSparkPlan isFinalPlan=false\n",
    "+- Sort [lower tag#130 ASC NULLS FIRST], true, 0\n",
    "   +- Exchange rangepartitioning(lower tag#130 ASC NULLS FIRST, 200), ENSURE_REQUIREMENTS, [id=#1415]\n",
    "      +- HashAggregate(keys=[lower tag#130], functions=[], output=[lower tag#130])\n",
    "         +- Exchange hashpartitioning(lower tag#130, 200), ENSURE_REQUIREMENTS, [id=#1412]\n",
    "            +- HashAggregate(keys=[lower tag#130], functions=[], output=[lower tag#130])\n",
    "               +- Project [lower(regexp_replace(trim(tag#55, None), ^\"|^'|^&|^#|\"$|'$|&$|#$, , 1)) AS lower tag#130]\n",
    "                  +- FileScan csv [tag#55] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex(1 paths)[file:/.../tags.csv], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<tag:string>\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38b0bcea-f13f-452c-85b6-49420d5d7576",
   "metadata": {},
   "source": [
    "**groupBy()**  \n",
    "  \n",
    "```\n",
    "== Physical Plan ==\n",
    "AdaptiveSparkPlan isFinalPlan=false\n",
    "+- Sort [lower tag#130 ASC NULLS FIRST], true, 0\n",
    "   +- Exchange rangepartitioning(lower tag#130 ASC NULLS FIRST, 200), ENSURE_REQUIREMENTS, [id=#1437]\n",
    "      +- HashAggregate(keys=[lower tag#130], functions=[count(1)], output=[lower tag#130, count#176L])\n",
    "         +- Exchange hashpartitioning(lower tag#130, 200), ENSURE_REQUIREMENTS, [id=#1434]\n",
    "            +- HashAggregate(keys=[lower tag#130], functions=[partial_count(1)], output=[lower tag#130, count#186L])\n",
    "               +- Project [lower(regexp_replace(trim(tag#55, None), ^\"|^'|^&|^#|\"$|'$|&$|#$, , 1)) AS lower tag#130]\n",
    "                  +- FileScan csv [tag#55] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex(1 paths)[file:/.../tags.csv], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<tag:string>\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d485911f-6fa9-4858-adf6-2d5f7a2cef2e",
   "metadata": {},
   "source": [
    "Spark optimizes both to identical plans / Inner workings for both seem identical in the optimized execution plan.  \n",
    "\n",
    "So we tend to prefer the one that is easier to read when debugging the code.  \n",
    "\n",
    "There may be a need for both distinct() and groupBy() in the same piece of code - for readability. We'll see that a few sections later.  \n",
    "\n",
    "*In some cases, I saw (and I am probably wrong here) that the groupBy() method seems marginally more expensive in terms of memory as compared to distinct(). Log a comment/ticket/PR if you have evidence, insights, ideas to the contrary/in-support.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1b00207-fa2a-44a5-9812-d0e5149d6875",
   "metadata": {},
   "source": [
    "#### Sort lexically"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "818e3cab-e8be-4c05-bd66-214d8aa07b88",
   "metadata": {},
   "outputs": [],
   "source": [
    "# sort unique tags lexically\n",
    "# ignore case when sorting values\n",
    "distinct_tags1 = distinct_tags1.orderBy(col(\"lower tag\").asc())\n",
    "distinct_tags2 = distinct_tags2.orderBy(col(\"lower tag\").asc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6278f106-0258-4036-aaf2-43c8f5913741",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------------------------------------------+\n",
      "|lower tag                                                  |\n",
      "+-----------------------------------------------------------+\n",
      "|a christmas carol                                          |\n",
      "|anti christmas                                             |\n",
      "|best christmas movie                                       |\n",
      "|christmas                                                  |\n",
      "|christmas bonus                                            |\n",
      "|christmas card                                             |\n",
      "|christmas carol                                            |\n",
      "|christmas england history                                  |\n",
      "|christmas eve                                              |\n",
      "|christmas gift                                             |\n",
      "|christmas holiday                                          |\n",
      "|christmas horror                                           |\n",
      "|christmas lights                                           |\n",
      "|christmas miracle                                          |\n",
      "|christmas morning                                          |\n",
      "|christmas movie                                            |\n",
      "|christmas music                                            |\n",
      "|christmas parade                                           |\n",
      "|christmas party                                            |\n",
      "|christmas present                                          |\n",
      "|christmas slasher                                          |\n",
      "|christmas songs                                            |\n",
      "|christmas special                                          |\n",
      "|christmas spirit                                           |\n",
      "|christmas story                                            |\n",
      "|christmas time                                             |\n",
      "|christmas tree                                             |\n",
      "|christmas tree dealer                                      |\n",
      "|christmas, dicken's                                        |\n",
      "|christmastime                                              |\n",
      "|classic christmas tale                                     |\n",
      "|father christmas                                           |\n",
      "|https://movielens.org/explore?tag=christmas&sortby=tagscore|\n",
      "|non-traditional christmas special                          |\n",
      "|saving christmas                                           |\n",
      "|see: merry christmas (joyeux noël)                         |\n",
      "+-----------------------------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "distinct_tags1.filter(col(\"lower tag\").contains(\"christmas\")).show(50, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "fac8759f-d0b7-4a2f-b28c-b4e1d04a7cfa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------------------------------------------+-----+\n",
      "|lower tag                                                  |count|\n",
      "+-----------------------------------------------------------+-----+\n",
      "|a christmas carol                                          |1    |\n",
      "|anti christmas                                             |1    |\n",
      "|best christmas movie                                       |7    |\n",
      "|christmas                                                  |1162 |\n",
      "|christmas bonus                                            |1    |\n",
      "|christmas card                                             |2    |\n",
      "|christmas carol                                            |11   |\n",
      "|christmas england history                                  |1    |\n",
      "|christmas eve                                              |36   |\n",
      "|christmas gift                                             |5    |\n",
      "|christmas holiday                                          |4    |\n",
      "|christmas horror                                           |12   |\n",
      "|christmas lights                                           |4    |\n",
      "|christmas miracle                                          |1    |\n",
      "|christmas morning                                          |1    |\n",
      "|christmas movie                                            |18   |\n",
      "|christmas music                                            |1    |\n",
      "|christmas parade                                           |1    |\n",
      "|christmas party                                            |25   |\n",
      "|christmas present                                          |2    |\n",
      "|christmas slasher                                          |1    |\n",
      "|christmas songs                                            |1    |\n",
      "|christmas special                                          |6    |\n",
      "|christmas spirit                                           |5    |\n",
      "|christmas story                                            |1    |\n",
      "|christmas time                                             |4    |\n",
      "|christmas tree                                             |22   |\n",
      "|christmas tree dealer                                      |1    |\n",
      "|christmas, dicken's                                        |1    |\n",
      "|christmastime                                              |2    |\n",
      "|classic christmas tale                                     |5    |\n",
      "|father christmas                                           |3    |\n",
      "|https://movielens.org/explore?tag=christmas&sortby=tagscore|1    |\n",
      "|non-traditional christmas special                          |1    |\n",
      "|saving christmas                                           |3    |\n",
      "|see: merry christmas (joyeux noël)                         |1    |\n",
      "+-----------------------------------------------------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "distinct_tags2.filter(col(\"lower tag\").contains(\"christmas\")).show(50, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf07c11f-703d-4d86-a440-22345c8bb247",
   "metadata": {},
   "source": [
    "### Which movies have the most number of tags?  \n",
    "* List movieIds in order of # of tags associated  \n",
    "* Can we find out which movies have most # of *distinct* tags?  \n",
    "* *[optional]* Does the ```timestamp``` column have any impact on your report?  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "b8bf136f-09dd-4d94-a98c-371ec4eb0ab8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----+\n",
      "|movieId|count|\n",
      "+-------+-----+\n",
      "|    260| 6180|\n",
      "|    296| 4767|\n",
      "|  79132| 4767|\n",
      "| 109487| 3616|\n",
      "|   2959| 3612|\n",
      "|    318| 3597|\n",
      "|   2571| 3573|\n",
      "|    356| 2701|\n",
      "|   4226| 2601|\n",
      "|   7361| 2533|\n",
      "|    593| 2482|\n",
      "|   4878| 2417|\n",
      "|   4973| 2226|\n",
      "|  48780| 2126|\n",
      "| 115713| 2115|\n",
      "|  72998| 2105|\n",
      "|    541| 2081|\n",
      "|  58559| 1956|\n",
      "|     47| 1947|\n",
      "|   2858| 1904|\n",
      "+-------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# movie IDs with most number of tags\n",
    "movies_by_tag_count = tags.groupBy(\"movieId\").count()\n",
    "movies_by_tag_count.orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6112f6b-7112-4d75-b698-6e5e029aceab",
   "metadata": {},
   "source": [
    "These movies have thousands of tags??? that makes no sense... let's see what some of those are:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "057e6d8d-fb25-4f21-a9d6-da49315b4fc2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+-----+\n",
      "|lowercase tag  |count|\n",
      "+---------------+-----+\n",
      "|sci-fi         |658  |\n",
      "|space          |302  |\n",
      "|classic        |273  |\n",
      "|science fiction|270  |\n",
      "|space adventure|263  |\n",
      "|classic sci-fi |232  |\n",
      "|action         |178  |\n",
      "|adventure      |160  |\n",
      "|fantasy        |150  |\n",
      "|star wars      |143  |\n",
      "|space opera    |139  |\n",
      "|harrison ford  |117  |\n",
      "|good vs evil   |115  |\n",
      "|epic           |112  |\n",
      "|space action   |104  |\n",
      "|space epic     |84   |\n",
      "|cult classic   |76   |\n",
      "|george lucas   |71   |\n",
      "|epic adventure |55   |\n",
      "|jedi           |55   |\n",
      "|scifi          |54   |\n",
      "|aliens         |53   |\n",
      "|futuristic     |51   |\n",
      "|scifi cult     |46   |\n",
      "|hero's journey |45   |\n",
      "+---------------+-----+\n",
      "only showing top 25 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tags.filter(col(\"movieId\") == 260).groupBy(\n",
    "    lower(col(\"tag\")).alias(\"lowercase tag\")\n",
    ").count().orderBy(col(\"count\").desc()).show(25, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5372ad7d-9c57-460f-870c-b41d40fe3d32",
   "metadata": {},
   "source": [
    "Any guesses what this movie could be?\n",
    "Let's quickly load the movies data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "399d8630-5e1e-438f-8dc5-cc6e597ec21d",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path_movies = datalocation + \"movies.csv\"\n",
    "#\n",
    "schema_movies = StructType(\n",
    "    [\n",
    "        StructField(\"movieId\", StringType(), False),\n",
    "        StructField(\"title\", StringType(), False),\n",
    "        StructField(\"genres\", StringType(), True),\n",
    "    ]\n",
    ")\n",
    "#\n",
    "movies_raw = (\n",
    "    spark.read.format(\"csv\")\n",
    "    .option(\"encoding\", \"UTF-8\")\n",
    "    .option(\"header\", True)\n",
    "    .option(\"sep\", \",\")\n",
    "    .option(\"escape\", '\"')\n",
    "    .schema(schema_movies)\n",
    "    .load(file_path_movies)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e5f2c628-92c5-4390-85c6-c3e65a099183",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------------------------------------+-----------------------+\n",
      "|movieId|title                                    |genres                 |\n",
      "+-------+-----------------------------------------+-----------------------+\n",
      "|260    |Star Wars: Episode IV - A New Hope (1977)|Action|Adventure|Sci-Fi|\n",
      "+-------+-----------------------------------------+-----------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "movies_raw.filter(col(\"movieId\") == 260).show(1, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "560412c6-3ea9-49c7-aa24-6dd7df442912",
   "metadata": {},
   "source": [
    "No surprises there!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "590aa69c-f021-4afa-ae37-b07dec217cfe",
   "metadata": {},
   "source": [
    "### Which users have added the most number of tags?\n",
    "\n",
    "* List ```userIds``` in order of # of tags created\n",
    "* Can we find out which users have created most # of distinct tags?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "7538cdfb-d7c1-4020-b825-c009ad4a295e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+------+\n",
      "|userId| count|\n",
      "+------+------+\n",
      "|  6550|183356|\n",
      "| 21096| 20317|\n",
      "| 62199| 13700|\n",
      "|160540| 12076|\n",
      "|155146| 11445|\n",
      "| 70092| 10582|\n",
      "|131347| 10195|\n",
      "| 14116| 10167|\n",
      "| 31047|  8463|\n",
      "|141263|  7114|\n",
      "| 64333|  6944|\n",
      "| 47969|  6599|\n",
      "| 15204|  6426|\n",
      "| 84824|  6209|\n",
      "|123527|  6005|\n",
      "|148457|  5960|\n",
      "| 19346|  5919|\n",
      "|  6285|  5837|\n",
      "| 96795|  5334|\n",
      "| 44444|  4941|\n",
      "+------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# users who have created most tags\n",
    "users_by_count_of_tag_entries = tags.groupBy(\"userId\").count()\n",
    "users_by_count_of_tag_entries.orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd276cb6-5a69-4d18-a7f4-202c7a73a321",
   "metadata": {},
   "source": [
    "Can we find out which users have created most # of distinct tags?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "68f07de1-dc97-4b09-b38f-b6f02ed03715",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----+\n",
      "|userId|count|\n",
      "+------+-----+\n",
      "|  6550|20765|\n",
      "| 31047| 5574|\n",
      "|155146| 5252|\n",
      "| 62199| 4959|\n",
      "|131347| 4834|\n",
      "|160540| 4783|\n",
      "|148457| 3550|\n",
      "| 47969| 3042|\n",
      "| 14116| 2913|\n",
      "|141263| 2837|\n",
      "| 19346| 2808|\n",
      "| 96795| 2304|\n",
      "| 64333| 2268|\n",
      "| 15204| 2190|\n",
      "| 44444| 2156|\n",
      "|105728| 2150|\n",
      "|123527| 2131|\n",
      "| 34047| 2103|\n",
      "| 56238| 2018|\n",
      "| 83426| 1942|\n",
      "+------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# isolate userId and tags and identify distinct pairs\n",
    "distinct_tags_by_user = tags.select(\"userId\", \"tag\").distinct()\n",
    "# count distinct tags by user\n",
    "count_distinct_tags_by_user = distinct_tags_by_user.groupBy(\"userId\").count()\n",
    "# list count of distinct tags by user\n",
    "count_distinct_tags_by_user.orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40146568-20c3-4ba8-b961-0098a6ed8e1c",
   "metadata": {},
   "source": [
    "*Holy Sith!* \n",
    "\n",
    "*183356* tags, *20765* unique tags - UserId ```6550``` is either a beast or probably a bot!  \n",
    "\n",
    "In most cases we may want to treat this one as an outlier, further analysis may be needed before taking that decision, but it's good to know 6550 exists.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "c9eedc69-b0ab-4c58-8eb7-8e8b98bb290e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+---------------+\n",
      "|userId|tag            |\n",
      "+------+---------------+\n",
      "|6550  |007            |\n",
      "|6550  |077            |\n",
      "|6550  |10th century   |\n",
      "|6550  |11th century   |\n",
      "|6550  |12th century   |\n",
      "|6550  |13th century   |\n",
      "|6550  |14th century   |\n",
      "|6550  |15th century   |\n",
      "|6550  |16th century   |\n",
      "|6550  |17th century   |\n",
      "|6550  |1860s          |\n",
      "|6550  |18th century   |\n",
      "|6550  |1910s          |\n",
      "|6550  |1920s          |\n",
      "|6550  |1930s          |\n",
      "|6550  |1940s          |\n",
      "|6550  |1950s          |\n",
      "|6550  |1960s          |\n",
      "|6550  |1970s          |\n",
      "|6550  |1980s          |\n",
      "|6550  |1990s          |\n",
      "|6550  |19th century   |\n",
      "|6550  |1st century    |\n",
      "|6550  |2040s          |\n",
      "|6550  |20th century   |\n",
      "|6550  |21st century   |\n",
      "|6550  |25th century   |\n",
      "|6550  |2nd century    |\n",
      "|6550  |3 dimensional  |\n",
      "|6550  |360 video      |\n",
      "|6550  |3d             |\n",
      "|6550  |47 ronin       |\n",
      "|6550  |80's           |\n",
      "|6550  |80's style     |\n",
      "|6550  |Afghanistan    |\n",
      "|6550  |Africa         |\n",
      "|6550  |Agent Coulson  |\n",
      "|6550  |Area 51        |\n",
      "|6550  |Australian     |\n",
      "|6550  |B Movie        |\n",
      "|6550  |B-movie        |\n",
      "|6550  |Bane           |\n",
      "|6550  |Batgirl        |\n",
      "|6550  |Batman         |\n",
      "|6550  |Black and white|\n",
      "|6550  |Catwoman       |\n",
      "|6550  |Chucky         |\n",
      "|6550  |Criterion      |\n",
      "|6550  |DC Universe    |\n",
      "|6550  |Dario Argento  |\n",
      "+------+---------------+\n",
      "only showing top 50 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "distinct_tags_by_user.filter(col(\"userId\") == \"6550\").orderBy(col(\"tag\")).show(\n",
    "    50, False\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21d190c2-34a8-4372-a718-ae110a8cde6d",
   "metadata": {},
   "source": [
    "Yeah! I'd say 6550 is some automated tagging mechanism. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df22ed59-5b7a-4671-a860-06d8e57dfe5c",
   "metadata": {},
   "source": [
    "### Which users have tagged the most number of movies?\n",
    "* Like before, can we find out which users have tagged the most # of *distinct* movies? Does this question make sense in the context of users?\n",
    "* *[optional]* Does the ```timestamp``` column have any impact on your report?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "8153c9cf-c8e2-4e45-b8aa-24f5754c2018",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----+\n",
      "|userId|count|\n",
      "+------+-----+\n",
      "|  6550|37913|\n",
      "| 62199|10840|\n",
      "| 21096| 7547|\n",
      "| 70092| 7171|\n",
      "| 64333| 2615|\n",
      "| 14116| 2361|\n",
      "| 72360| 2073|\n",
      "|125054| 1712|\n",
      "|131347| 1602|\n",
      "| 34164| 1526|\n",
      "+------+-----+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# which users have tagged the most number of movies?\n",
    "\n",
    "# approach:\n",
    "# create a df that has only users and movies\n",
    "# this df may have multiple rows for\n",
    "# same userId and movieId as each user has given many tags to each movie\n",
    "# so only pick distinct records.\n",
    "# then use groupBy and count as ususal\n",
    "\n",
    "users_and_movies_in_tags = tags.select(\"userId\", \"movieId\").distinct()\n",
    "\n",
    "users_and_movies_in_tags_count = (\n",
    "    users_and_movies_in_tags.groupBy(\"userId\").count().orderBy(col(\"count\").desc())\n",
    ")\n",
    "\n",
    "users_and_movies_in_tags_count.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b999d152-38cf-4ce5-b99b-30b53869754c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----+\n",
      "|movieId|count|\n",
      "+-------+-----+\n",
      "|   2069|    1|\n",
      "|   3959|    1|\n",
      "|  26112|    1|\n",
      "|  60336|    1|\n",
      "|   1436|    1|\n",
      "|   3210|    1|\n",
      "|   2904|    1|\n",
      "|  35640|    1|\n",
      "|   7252|    1|\n",
      "|   2136|    1|\n",
      "+-------+-----+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# check:\n",
    "# filter for any one userId, check that all movies occur only once\n",
    "users_and_movies_in_tags.filter(col(\"userId\") == 6550).groupBy(\n",
    "    \"movieId\"\n",
    ").count().orderBy(col(\"count\").desc()).show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cccb047f-0b4b-4bff-a4a9-58b4dba2ba41",
   "metadata": {},
   "source": [
    "### Tagging activity related analysis\n",
    "\n",
    "* Convert time-stamps to Day-Month-Year.\n",
    "* Find the date range (min-date, max-date) during which the tagging activity took place.\n",
    "* Plot number of movies tagged per day during the date range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "96e10998-d951-489c-854c-00476f0b0d47",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-------+-----------------------+-----------------------+----------+-------------------+------------+\n",
      "|userId|movieId|tag                    |lower tag              |timestamp |datetime           |tagging date|\n",
      "+------+-------+-----------------------+-----------------------+----------+-------------------+------------+\n",
      "|3     |260    |classic                |classic                |1439472355|2015-08-13 18:55:55|2015-08-13  |\n",
      "|3     |260    |sci-fi                 |sci-fi                 |1439472256|2015-08-13 18:54:16|2015-08-13  |\n",
      "|4     |1732   |dark comedy            |dark comedy            |1573943598|2019-11-17 04:03:18|2019-11-17  |\n",
      "|4     |1732   |great dialogue         |great dialogue         |1573943604|2019-11-17 04:03:24|2019-11-17  |\n",
      "|4     |7569   |so bad it's good       |so bad it's good       |1573943455|2019-11-17 04:00:55|2019-11-17  |\n",
      "|4     |44665  |unreliable narrators   |unreliable narrators   |1573943619|2019-11-17 04:03:39|2019-11-17  |\n",
      "|4     |115569 |tense                  |tense                  |1573943077|2019-11-17 03:54:37|2019-11-17  |\n",
      "|4     |115713 |artificial intelligence|artificial intelligence|1573942979|2019-11-17 03:52:59|2019-11-17  |\n",
      "|4     |115713 |philosophical          |philosophical          |1573943033|2019-11-17 03:53:53|2019-11-17  |\n",
      "|4     |115713 |tense                  |tense                  |1573943042|2019-11-17 03:54:02|2019-11-17  |\n",
      "+------+-------+-----------------------+-----------------------+----------+-------------------+------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# code cleanup update - all sql.functions imported at the top of this notebook\n",
    "# from pyspark.sql.functions import from_unixtime, to_date\n",
    "\n",
    "# convert unix epoch timestamp to date-time style timestamp\n",
    "tags = tags.withColumn(\"datetime\", from_unixtime(col(\"timestamp\")))\n",
    "# extract date from date-time timestamp\n",
    "tags = tags.withColumn(\"tagging date\", to_date(col(\"datetime\")))\n",
    "tags.show(10, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "f021706e-0fd1-4887-bfe2-9c1f25913b6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-----+\n",
      "|tagging date|count|\n",
      "+------------+-----+\n",
      "|2018-06-10  |26871|\n",
      "|2018-05-27  |26644|\n",
      "|2018-05-28  |21775|\n",
      "|2018-05-18  |20808|\n",
      "|2018-05-25  |18488|\n",
      "+------------+-----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tagging_activity_by_date = tags.groupBy(col(\"tagging date\")).count()\n",
    "#\n",
    "tagging_activity_by_date.orderBy(col(\"count\").desc()).show(5, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86d1a71a-5290-4465-8807-25cfe1e93e96",
   "metadata": {},
   "source": [
    "#### date range during which the tagging activity took place\n",
    "\n",
    "* (min-date, max-date) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "ed2fdb7e-6036-473c-8fd4-36a3575cb0c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "|min(tagging date)|\n",
      "+-----------------+\n",
      "|       2005-12-24|\n",
      "+-----------------+\n",
      "\n",
      "+-----------------+\n",
      "|max(tagging date)|\n",
      "+-----------------+\n",
      "|       2019-11-21|\n",
      "+-----------------+\n",
      "\n",
      "+-------+-----------------+\n",
      "|summary|            count|\n",
      "+-------+-----------------+\n",
      "|  count|             5056|\n",
      "|   mean|           216.25|\n",
      "| stddev|848.8262672935349|\n",
      "|    min|                1|\n",
      "|    25%|               61|\n",
      "|    50%|              120|\n",
      "|    75%|              224|\n",
      "|    max|            26871|\n",
      "+-------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# code cleanup update - all sql.functions imported at the top of this notebook\n",
    "# from pyspark.sql.functions import avg, max, min\n",
    "\n",
    "#\n",
    "tagging_min_date = tagging_activity_by_date.select(min(col(\"tagging date\")))\n",
    "tagging_max_date = tagging_activity_by_date.select(max(col(\"tagging date\")))\n",
    "#\n",
    "tagging_min_date.show()\n",
    "tagging_max_date.show()\n",
    "# tagging_activity_by_date.select(min(col('count'))).show()\n",
    "# tagging_activity_by_date.select(max(col('count'))).show()\n",
    "# tagging_activity_by_date.select(avg(col('count'))).show()\n",
    "tagging_activity_by_date.summary().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "d452af75-7e93-46c0-abe6-594d2f78d32b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# setup matplotlib before starting plotting\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# jupyter mpl magic\n",
    "%matplotlib inline\n",
    "\n",
    "# note: in case you want to use the widget or notebook magic:\n",
    "# ensure that ipympl is also installed.\n",
    "# there may be other steps involved as well\n",
    "# for e.g. widget works based on nodeJS, so you'll have to config/enable that too"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "ce39b905-f5ad-4a4c-88ee-91deb654609f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # determine the X and Y lists for plotting.\n",
    "# # converting a spark dataframe column to list:\n",
    "# # spark.catalog.clearCache()\n",
    "# tagging_activity_x = (\n",
    "#     tagging_activity_by_date.select(col(\"tagging date\"))\n",
    "#     .rdd.flatMap(lambda x: x)\n",
    "#     .collect()\n",
    "# )\n",
    "\n",
    "# tagging_activity_y = (\n",
    "#     tagging_activity_by_date.select(col(\"count\")).rdd.flatMap(lambda x: x).collect()\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "ec4e014c-b42b-408b-93bb-d57feffc6930",
   "metadata": {},
   "outputs": [],
   "source": [
    "# plt.figure(figsize=(18, 6))\n",
    "# plt.bar(tagging_activity_x, tagging_activity_y)\n",
    "# # Don't need to skew the graph with some really high counts\n",
    "# # limit to 1500\n",
    "# ax = plt.gca()\n",
    "# ax.set_ylim([0, 1500])\n",
    "# plt.title(\"count of tags by date\")\n",
    "# plt.xlabel(\"date\")\n",
    "# plt.ylabel(\"count\")\n",
    "# plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c5d9a41-8ae9-4ef6-8ba2-cc2e05dfb9f6",
   "metadata": {},
   "source": [
    "Seems like the activity peaked extraordinarily around May/June 2018"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bf6e5fa-deb0-4898-ab38-88ecc94d9a0b",
   "metadata": {},
   "source": [
    "### Daily active users during the tagging activity.\n",
    "\n",
    "* Plot number of users who tagged at least one movie during the tagging activity date range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "f80d60bf-0ee8-456b-810c-21098f909ba1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-----+\n",
      "|tagging date|count|\n",
      "+------------+-----+\n",
      "|  2015-08-17|  180|\n",
      "|  2018-05-06|   87|\n",
      "|  2018-05-05|   78|\n",
      "|  2018-05-07|   71|\n",
      "|  2015-07-27|   64|\n",
      "+------------+-----+\n",
      "only showing top 5 rows\n",
      "\n",
      "+-------+-----------------+\n",
      "|summary|            count|\n",
      "+-------+-----------------+\n",
      "|  count|             5056|\n",
      "|   mean|16.48378164556962|\n",
      "| stddev|9.770346849818784|\n",
      "|    min|                1|\n",
      "|    25%|               10|\n",
      "|    50%|               15|\n",
      "|    75%|               21|\n",
      "|    max|              180|\n",
      "+-------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "distinct_users_doing_tagging_by_date = (\n",
    "    tags.select(\"userId\", \"tagging date\").distinct().groupBy(\"tagging date\").count()\n",
    ")\n",
    "#\n",
    "distinct_users_doing_tagging_by_date.orderBy(col(\"count\").desc()).show(5)\n",
    "distinct_users_doing_tagging_by_date.summary().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "c2a30e22-b94f-42c8-ba40-f87f2c4b5314",
   "metadata": {},
   "outputs": [],
   "source": [
    "# like before, we need to determine X and Y lists.\n",
    "\n",
    "distinct_users_doing_tagging_by_date_x = (\n",
    "    distinct_users_doing_tagging_by_date.select(col(\"tagging date\"))\n",
    "    .rdd.flatMap(lambda x: x)\n",
    "    .collect()\n",
    ")\n",
    "\n",
    "distinct_users_doing_tagging_by_date_y = (\n",
    "    distinct_users_doing_tagging_by_date.select(col(\"count\"))\n",
    "    .rdd.flatMap(lambda x: x)\n",
    "    .collect()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "4e438bb5-82b9-4420-a5cb-854bfa63f65e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAABbYAAAHUCAYAAADm7H6jAAAAOnRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjEwLjAsIGh0dHBzOi8vbWF0cGxvdGxpYi5vcmcvlHJYcgAAAAlwSFlzAAAPYQAAD2EBqD+naQAARZpJREFUeJzt3XucVXW9P/73RmEcBMkLzDCCDip5OXgpNQRT0JLCS/nwVCZmXstrhVQ0Zsl4NFBPmSWpWYqUx+imZo6inKOgqRQaqGGaFSUpI5bcBAWR9fujL/vHMFzmsvdee+15Ph+P/dBZa+213p/LWrPXy+2aXJIkSQAAAAAAQEZ0S7sAAAAAAABoD8E2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAl993vfjdyuVwMGTKkw/t45ZVXorGxMebNm9dqXWNjY+RyuU5U2LX97W9/i1wuF9/85jdTOf5tt90WuVwu/va3v7X7vffdd180NjYWvCYAAMqLYBsAgJK79dZbIyJi/vz58dvf/rZD+3jllVfi8ssv32Swfc4558QTTzzRmRLJqPvuuy8uv/zytMsAAKDIBNsAAJTUk08+GU8//XQcd9xxERFxyy23FPwYAwYMiMMOO6zg+82KJEnizTffTLsMAAAoGsE2AAAltT7Ivuqqq2L48OExbdq0WLVqVavtXn755fjsZz8bAwcOjB49ekRdXV187GMfi1dffTVmzpwZhx56aEREnHnmmZHL5SKXy+UfQbHxo0hOPPHE2H333WPdunWtjjN06NB473vfm/85SZK44YYb4qCDDorq6urYcccd42Mf+1j89a9/3WrbzjjjjKivr2+1fFOPRvn5z38eQ4cOjT59+kTPnj1jjz32iLPOOqvFNsuXL48vfelLMWjQoOjRo0fsuuuuMXbs2Fi5cmWL7XK5XFx00UVx0003xb777htVVVUxderUiIi48cYb48ADD4xevXpF7969Y5999omvfvWrW21LRMS6deviG9/4Ruy2226x3XbbxSGHHBL/93//l1//6KOPRi6Xi5/85Cet3vujH/0ocrlczJkzZ4vHmD17dhx++OGx3XbbRV1dXVxyySXx9ttvt9rupz/9aYwaNSr69+8f1dXVse+++0ZDQ0OLvjjjjDPie9/7Xr5P1r/WP9KkM2MLAEB5EWwDAFAyb775ZvzkJz+JQw89NIYMGRJnnXVWrFixIn7+85+32O7ll1+OQw89NO66664YN25c3H///XHddddFnz59YsmSJfHe9743pkyZEhERX/va1+KJJ56IJ554Is4555xNHvess86Kl156KR566KEWy59//vn43e9+F2eeeWZ+2bnnnhtjx46ND37wg3H33XfHDTfcEPPnz4/hw4fHq6++WpB+eOKJJ+Lkk0+OPfbYI6ZNmxZNTU1x2WWXxdq1a/PbrFq1KkaMGBFTp06Nz3/+83H//ffHV77ylbjtttviIx/5SCRJ0mKfd999d9x4441x2WWXxQMPPBBHHHFETJs2LS644IIYMWJE3HXXXXH33XfHxRdf3CoY35zJkyfH9OnT47rrrovbb789unXrFqNHj84/5uWII46I97znPfkweeP3Hnroofn/ALEpzz33XHzgAx+IpUuXxm233RY33XRTzJ07N6688spW27744otx7LHHxi233BLTp0+PsWPHxs9+9rM44YQT8tt8/etfj4997GP5Pl7/6t+/f0SUZmwBACiRBAAASuRHP/pREhHJTTfdlCRJkqxYsSLp1atXcsQRR7TY7qyzzkq6d++ePPfcc5vd15w5c5KISKZMmdJq3YQJE5INP+q+/fbbSU1NTTJmzJgW240fPz7p0aNH8s9//jNJkiR54oknkohIvvWtb7XYbuHChUl1dXUyfvz4Lbbv9NNPT3bfffet1vPNb34ziYhk6dKlm93XpEmTkm7duiVz5sxpsfwXv/hFEhHJfffdl18WEUmfPn2S119/vcW2F110UfKud71rizVvyoIFC5KISOrq6pI333wzv3z58uXJTjvtlHzwgx/ML5syZUoSEcncuXPzy373u98lEZFMnTp1i8c5+eSTk+rq6qS5uTm/bO3atck+++yTRESyYMGCTb5v3bp1ydtvv53MmjUriYjk6aefzq+78MILk03d5nR2bAEAKC++sQ0AQMnccsstUV1dHZ/85CcjIqJXr17x8Y9/PB599NF48cUX89vdf//9cdRRR8W+++5bkONuu+228alPfSruvPPOWLZsWUREvPPOO/HjH/84PvrRj8bOO+8cERH33ntv5HK5+NSnPhVr167Nv2pra+PAAw+MmTNnFqSe9d9i/sQnPhE/+9nP4uWXX261zb333htDhgyJgw46qEUtH/rQhyKXy7Wq5eijj44dd9yxxbL3ve99sXTp0jjllFPiV7/6Vfzzn/9sV50nnXRSbLfddvmfe/fuHSeccEI88sgj8c4770RExCmnnBL9+vVr8a3t66+/Pvr27Rsnn3zyFvf/8MMPxwc+8IGoqanJL9tmm202+b6//vWvMWbMmKitrY1tttkmunfvHiNGjIiIiD/+8Y9bbUupxhYAgNIQbAMAUBJ//vOf45FHHonjjjsukiSJpUuXxtKlS/OPjrj11lvz27722msxYMCAgh7/rLPOirfeeiumTZsWEREPPPBALFq0qMVjSF599dVIkiRqamqie/fuLV6zZ89udzC8OUceeWTcfffdsXbt2vj0pz8dAwYMiCFDhrR4VvWrr74azzzzTKs6evfuHUmStKpl/eM2NnTaaafFrbfeGn//+9/jP//zP6Nfv34xdOjQmDFjRpvqrK2t3eSyNWvWxBtvvBEREVVVVXHuuefGHXfcEUuXLo3XXnstfvazn8U555wTVVVVW9z/v/71r80eY0NvvPFGHHHEEfHb3/42rrzyypg5c2bMmTMn7rzzzoiINv2hzFKNLQAApbFt2gUAANA13HrrrZEkSfziF7+IX/ziF63WT506Na688srYZpttom/fvvGPf/yjoMffb7/94n3ve19MmTIlzj333JgyZUrU1dXFqFGj8tvssssukcvl4tFHH91kKLu1oHa77baL1atXt1q+qdD0ox/9aHz0ox+N1atXx+zZs2PSpEkxZsyYqK+vj2HDhsUuu+wS1dXVLQL/De2yyy4tft74j1Oud+aZZ8aZZ54ZK1eujEceeSQmTJgQxx9/fPzpT3+K3XfffYvtaW5u3uSyHj16RK9evfLLzj///Ljqqqvi1ltvjbfeeivWrl0b55133hb3HRGx8847b/YYG3rooYfilVdeiZkzZ+a/pR0RsXTp0q0eY73Oji0AAOVFsA0AQNG98847MXXq1Nhzzz3jhz/8Yav19957b3zrW9+K+++/P44//vgYPXp0/PjHP44XXngh9t57703uc30Q2ZZv66535plnxvnnnx+/+c1v4te//nWMGzcuttlmm/z6448/Pq666qp4+eWX4xOf+EQ7WxlRX18fixcvjldffTX/eI01a9bEAw88sNn3VFVVxYgRI+Jd73pXPPDAAzF37twYNmxYHH/88TFx4sTYeeedY9CgQe2uZWPbb799jB49OtasWRMnnnhizJ8/f6vB9p133hn//d//nX8cyYoVK+LXv/51HHHEES36rX///vHxj388brjhhlizZk2ccMIJsdtuu221pqOOOiruueeeFv31zjvvxE9/+tMW260P7TcOn7///e+32ueG86K6ujq/vLNjCwBAeRFsAwBQdPfff3+88sorcfXVV8fIkSNbrR8yZEhMnjw5brnlljj++OPjv/7rv+L++++PI488Mr761a/G/vvvH0uXLo3p06fHuHHjYp999ok999wzqqur43/+539i3333jV69ekVdXV3U1dVtto5TTjklxo0bF6ecckqsXr06zjjjjBbrDz/88PjsZz8bZ555Zjz55JNx5JFHxvbbbx+LFi2K3/zmN7H//vvH+eefv9n9n3zyyXHZZZfFJz/5yfjyl78cb731Vnz3u9/NP496vcsuuyz+8Y9/xAc+8IEYMGBALF26NL7zne+0eG702LFj45e//GUceeSRcfHFF8cBBxwQ69ati5deeikefPDB+OIXvxhDhw7dYr9/5jOfierq6jj88MOjf//+0dzcHJMmTYo+ffrkn/O9Jdtss00cc8wxMW7cuFi3bl1cffXVsXz58rj88stbbfuFL3whX8+UKVO2uu+IiK997Wtxzz33xNFHHx2XXXZZ9OzZM773ve/FypUrW2w3fPjw2HHHHeO8886LCRMmRPfu3eN//ud/4umnn261z/333z8iIq6++uoYPXp0bLPNNnHAAQd0emwBACgzqf7pSgAAuoQTTzwx6dGjR7J48eLNbvPJT34y2XbbbZPm5uYkSZJk4cKFyVlnnZXU1tYm3bt3T+rq6pJPfOITyauvvpp/z09+8pNkn332Sbp3755ERDJhwoQkSZJkwoQJyeY+6o4ZMyaJiOTwww/fbC233nprMnTo0GT77bdPqqurkz333DP59Kc/nTz55JNbbet9992XHHTQQUl1dXWyxx57JJMnT25Vz7333puMHj062XXXXZMePXok/fr1S4499tjk0UcfbbGvN954I/na176W7L333kmPHj2SPn36JPvvv39y8cUX5/spSZIkIpILL7ywVS1Tp05NjjrqqKSmpibp0aNHvg+feeaZLbZhwYIFSUQkV199dXL55ZcnAwYMSHr06JG85z3vSR544IHNvq++vj7Zd999t9pHG3rssceSww47LKmqqkpqa2uTL3/5y8nNN9+cRESyYMGC/HaPP/54MmzYsKRnz55J3759k3POOSf5/e9/n0REMmXKlPx2q1evTs4555ykb9++SS6Xa7WfzowtAADlI5ckSZJaqg4AAFSEZ555Jg488MD43ve+FxdccEHa5QAAUOEE2wAAQIf95S9/ib///e/x1a9+NV566aX485//HD179ky7LAAAKly3tAsAAACy64orrohjjjkm3njjjfj5z38u1AYAoCR8YxsAAAAAgExJ9RvbjY2NkcvlWrxqa2vz65MkicbGxqirq4vq6uoYOXJkzJ8/P8WKAQAAAABIW+qPIvmP//iPWLRoUf717LPP5tddc801ce2118bkyZNjzpw5UVtbG8ccc0ysWLEixYoBAAAAAEhT6sH2tttuG7W1tflX3759I+Lf39a+7rrr4tJLL42TTjophgwZElOnTo1Vq1bFHXfckXLVAAAAAACkZdu0C3jxxRejrq4uqqqqYujQoTFx4sTYY489YsGCBdHc3ByjRo3Kb1tVVRUjRoyIxx9/PM4999xN7m/16tWxevXq/M/r1q2L119/PXbeeefI5XJFbw8AAAAAAO2XJEmsWLEi6urqolu3LX8nO9Vge+jQofGjH/0o3v3ud8err74aV155ZQwfPjzmz58fzc3NERFRU1PT4j01NTXx97//fbP7nDRpUlx++eVFrRsAAAAAgOJYuHBhDBgwYIvb5JIkSUpUz1atXLky9txzzxg/fnwcdthhcfjhh8crr7wS/fv3z2/zmc98JhYuXBjTp0/f5D42/sb2smXLYrfddouFCxfGDjvsUPQ2VJIhEx6IP1z+obTLYDOMD1lWjvO3HGuCrNra+eR8g86phHOoEtoAheBcaK2r9klXbTdsbPny5TFw4MBYunRp9OnTZ4vbpv4okg1tv/32sf/++8eLL74YJ554YkRENDc3twi2Fy9e3Opb3BuqqqqKqqqqVst32GEHwXY7davqqc/KmPEhy8px/pZjTZBVWzufnG/QOZVwDlVCG6AQnAutddU+6arths1pyyOlU//jkRtavXp1/PGPf4z+/fvHoEGDora2NmbMmJFfv2bNmpg1a1YMHz48xSoBAAAAAEhTqt/Y/tKXvhQnnHBC7LbbbrF48eK48sorY/ny5XH66adHLpeLsWPHxsSJE2Pw4MExePDgmDhxYvTs2TPGjBmTZtkAAAAAAKQo1WD7H//4R5xyyinxz3/+M/r27RuHHXZYzJ49O3bfffeIiBg/fny8+eabccEFF8SSJUti6NCh8eCDD0bv3r3TLBsAAAAAgBSlGmxPmzZti+tzuVw0NjZGY2NjaQoCAAAAAKDsldUztgGgXNQ3NKVdAhlhrgAAAJSeYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAABAZtU3NKVdAgApEGwDAAAAAJApgm0AAAAAADJFsA0AAAAAQKYItgEAAAAAyBTBNgAAAAAAmSLYBgAAAAAgUwTbAAAAAABkimAbAOgy6hua0i4BAACAAhBsAwAAAACQKYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAABAR9Q1NaZcAALSRYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsA0IXVNzSlXQJQYM5rAIB/87mosgm2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAW1Df0JR2CQAAwEYE2wAAAAAAZIpgGwAAAACATBFsAwAAAACQKYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAB1W39CUdgkVoVz6sVzqAACArRFsAwAAAACQKYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAAAAAZIpgGwAAaKW+oSntEgAAYLME2wAAAAAAZIpgGwAAAACATBFsAwAAAACQKWUTbE+aNClyuVyMHTs2vyxJkmhsbIy6urqorq6OkSNHxvz589MrEgAAAACA1JVFsD1nzpy4+eab44ADDmix/Jprrolrr702Jk+eHHPmzIna2to45phjYsWKFSlVCgAAAABA2lIPtt9444049dRT4wc/+EHsuOOO+eVJksR1110Xl156aZx00kkxZMiQmDp1aqxatSruuOOOFCsGAAAAACBNqQfbF154YRx33HHxwQ9+sMXyBQsWRHNzc4waNSq/rKqqKkaMGBGPP/74Zve3evXqWL58eYsXAAAAAACVI9Vge9q0afH73/8+Jk2a1Gpdc3NzRETU1NS0WF5TU5NftymTJk2KPn365F8DBw4sbNEAdBn1DU1plwBtYq5WLmMLgN8FAJuWWrC9cOHC+MIXvhC33357bLfddpvdLpfLtfg5SZJWyzZ0ySWXxLJly/KvhQsXFqxmAAAAAADSt21aB37qqadi8eLFcfDBB+eXvfPOO/HII4/E5MmT44UXXoiIf39zu3///vltFi9e3Opb3BuqqqqKqqqq4hUOAAAAAECqUvvG9gc+8IF49tlnY968efnXIYccEqeeemrMmzcv9thjj6itrY0ZM2bk37NmzZqYNWtWDB8+PK2yAQAAAABIWWrf2O7du3cMGTKkxbLtt98+dt555/zysWPHxsSJE2Pw4MExePDgmDhxYvTs2TPGjBmTRskAAAAAAJSB1ILtthg/fny8+eabccEFF8SSJUti6NCh8eCDD0bv3r3TLg0AAAAAgJSUVbA9c+bMFj/ncrlobGyMxsbGVOoBAAAAAKD8pPaMbbKvvqEp7RIAANgMn9WArsZ1D6BrEWwDAAAAAJApgm0AAAAAADJFsA0AAAAAQKYItgEAAAAAyBTBNgAAAAAAmSLYBgAAAAAgUwTbAAAAAABkimC7i6lvaEq7hM0q59q6MuNCWsw9KA/ORQrJfAKgknX133Ndvf2UnmAbAAAAAIBMEWwDAAAAAJApgm0AAAAAADJFsA0AAAAAQKYItgEAAAAAyBTBNgAAAAAAmSLYBgAAAAAgUwTbABRMfUNT2iXQQcYOAGDrfGYCKB+CbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbAOwVfUNTWmXQAEZTyg95x3FUGnzqtLaA3RNrmVQOoJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAAAAAZIpgG7qw+oamtEsAYCtcqwEAts5nJuh6BNsAAAAAAGSKYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItjuguobmtIuAQAKxu81YEtcI6BwnE/lzxiVP2MEhSPYBgAAAAAgUwTbAAAAAABkimAbAAAAAIBMEWwDAAAAAJApgm0AAAAAADJFsA0AAAAAQKYItgEAAAAAyBTBNnQh9Q1NqbyXzdOvAHQFft8BHVHJ145KbhvQOa4PbSfYBgAAAAAgUwTbAAAAAABkimAbAAAAAIBMEWwDAAAAAJApgm0AAAAAADJFsA0AAAAAQKYItgEAUlbf0JR2CdAp5nDx6WNozXlBOTM/ofgE2wAAAAAAZIpgGwAAAACATBFsAwAAAACQKYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AaANqpvaIr6hqa0y4A2M187rzN9qP/Juq3N4Q3Xm+9UOnMcsq8Y57FrQ7oE2wAAAAAAZIpgGwAAAACATBFsAwAAAACQKakG2zfeeGMccMABscMOO8QOO+wQw4YNi/vvvz+/PkmSaGxsjLq6uqiuro6RI0fG/PnzU6wYAAAAAIC0pRpsDxgwIK666qp48skn48knn4yjjz46PvrRj+bD62uuuSauvfbamDx5csyZMydqa2vjmGOOiRUrVqRZNgAAAAAAKUo12D7hhBPi2GOPjXe/+93x7ne/O77xjW9Er169Yvbs2ZEkSVx33XVx6aWXxkknnRRDhgyJqVOnxqpVq+KOO+5Is2wAAAAAAFJUNs/Yfuedd2LatGmxcuXKGDZsWCxYsCCam5tj1KhR+W2qqqpixIgR8fjjj292P6tXr47ly5e3eAEAAAAAUDlSD7afffbZ6NWrV1RVVcV5550Xd911V+y3337R3NwcERE1NTUttq+pqcmv25RJkyZFnz598q+BAwcWtX7KW31DU9ollC19s3Ub9lFn+ktfp8OYUSzmB5XOHM8eYwZQOK6pW6Z/KCepB9t77713zJs3L2bPnh3nn39+nH766fHcc8/l1+dyuRbbJ0nSatmGLrnkkli2bFn+tXDhwqLVDgAAAABA6W2bdgE9evSIvfbaKyIiDjnkkJgzZ0585zvfia985SsREdHc3Bz9+/fPb7948eJW3+LeUFVVVVRVVRW3aAAAAAAAUpP6N7Y3liRJrF69OgYNGhS1tbUxY8aM/Lo1a9bErFmzYvjw4SlWCAAAAABAmlL9xvZXv/rVGD16dAwcODBWrFgR06ZNi5kzZ8b06dMjl8vF2LFjY+LEiTF48OAYPHhwTJw4MXr27BljxoxJs2wAAAAAAFKUarD96quvxmmnnRaLFi2KPn36xAEHHBDTp0+PY445JiIixo8fH2+++WZccMEFsWTJkhg6dGg8+OCD0bt37zTLBgAAAAAgRakG27fccssW1+dyuWhsbIzGxsbSFAQAAAAAQNkru2dsk031DU1pl0CZysrcKJc6y6UOWjIusHXrzxPnC+1lzkBpOefKk3GhHJmXlDvBNgAAAAAAmSLYBgAAAAAgUwTbAAAAAABkimAbAAAAAIBMEWwDAAAAAJApgm0AAAAAADJFsA0AAAAAQKYItunS6hua0i4BoGRc87qmzY27+ZCurt7/Xb39tI/5Ulzl1L/lVAsA5U+wDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGRKh4Lto48+OpYuXdpq+fLly+Poo4/ubE0AAAAAALBZHQq2Z86cGWvWrGm1/K233opHH32000UBAAAAAMDmtCvYfuaZZ+KZZ56JiIjnnnsu//MzzzwTc+fOjVtuuSV23XXXohQKkCX1DU0F2QaKpSvOv67YZoqnPfOplHOv3Ob5puopRY0bH6Pc+qUc6BPoOOcPdA3O9fK3bXs2PuiggyKXy0Uul9vkI0eqq6vj+uuvL1hxAAAAAACwsXYF2wsWLIgkSWKPPfaI3/3ud9G3b9/8uh49ekS/fv1im222KXiRAAAAAACwXruC7d133z0iItatW1eUYgAAAAAAYGvaFWxv6E9/+lPMnDkzFi9e3CrovuyyyzpdGAAAAAAAbEqHgu0f/OAHcf7558cuu+wStbW1kcvl8utyuZxgGwAAAACAoulQsH3llVfGN77xjfjKV75S6HoAAAAAAGCLunXkTUuWLImPf/zjha4FAAAAAAC2qkPB9sc//vF48MEHC10LFE19Q1PaJWRKMfprS/ss5fiYC11XW8feHIHWnBeFU+rfsZA15nP56uhnqbTGtKsdF8w9uqIOPYpkr732iq9//esxe/bs2H///aN79+4t1n/+858vSHEAAAAAALCxDgXbN998c/Tq1StmzZoVs2bNarEul8sJtgEAAAAAKJoOBdsLFiwodB0AAAAAANAmHXrGNgAAAAAApKVD39g+66yztrj+1ltv7VAxAAAAAACwNR0KtpcsWdLi57fffjv+8Ic/xNKlS+Poo48uSGEAAAAAALApHXoUyV133dXide+998Zf//rX+OQnPxmHHXZYoWukAtU3NKVdQkXTv5ANxT5XXQsAgLSk+Tmkkj4DVVJb2iKN9rbnmF1tPOg4c6U0CvaM7W7dusXFF18c3/72twu1SwAAAAAAaKWgfzzyL3/5S6xdu7aQuwQAAAAAgBY69IztcePGtfg5SZJYtGhRNDU1xemnn16QwgAAAAAAYFM6FGzPnTu3xc/dunWLvn37xre+9a0466yzClIYAAAAAABsSoeC7YcffrjQdQAAAAAAQJt0KNhe77XXXosXXnghcrlcvPvd746+ffsWqi4AAAAAANikDv3xyJUrV8ZZZ50V/fv3jyOPPDKOOOKIqKuri7PPPjtWrVpV6BoBAAAAACCvQ8H2uHHjYtasWfHrX/86li5dGkuXLo1f/epXMWvWrPjiF79Y6BqhlfqGpi3+XKrjdmVdqS/a29au1DcdoX/Spf+3Th+Vhn6mrcyVtulsP5VTP5dTLZ1RKe1oi3Jv6/r6il1nR/df7v0HlcT5Vlk69CiSX/7yl/GLX/wiRo4cmV927LHHRnV1dXziE5+IG2+8sVD1AQAAAABACx36xvaqVauipqam1fJ+/fp5FAkAAAAAAEXVoWB72LBhMWHChHjrrbfyy9588824/PLLY9iwYQUrDgAAAAAANtahR5Fcd911MXr06BgwYEAceOCBkcvlYt68eVFVVRUPPvhgoWsEAAAAAIC8DgXb+++/f7z44otx++23x/PPPx9JksQnP/nJOPXUU6O6urrQNQIAAAAAQF6Hgu1JkyZFTU1NfOYzn2mx/NZbb43XXnstvvKVrxSkOAAAAAAA2FiHnrH9/e9/P/bZZ59Wy//jP/4jbrrppk4XBQAAAAAAm9OhYLu5uTn69+/fannfvn1j0aJFnS4KoBDqG5oyvX/+f/q6NLLQz1mocVNKWbc+Yks21c/6ni0pxPzwmaw8tLefKr1f6xuaKrqN5da2zdVTbnVmXTH60xi1nz4rnQ4F2wMHDozHHnus1fLHHnss6urqOl0UAAAAAABsToeesX3OOefE2LFj4+23346jjz46IiL+7//+L8aPHx9f/OIXC1ogAAAAAABsqEPB9vjx4+P111+PCy64INasWRMREdttt1185StfiUsuuaSgBQIAAAAAwIY6FGzncrm4+uqr4+tf/3r88Y9/jOrq6hg8eHBUVVUVuj4AAAAAAGihQ8H2er169YpDDz20ULUAAAAAAMBWdeiPRwIAAAAAQFoE22RGfUNTQbfLsg3b2Jb2VmqfbK5dldrerOnoOJRi/ApxjEqeZ5XUtva0pb6hqaLaXo4qpX/Xt6NS2lMMpeqbSh+Dzrav0vunUDrTT1nr4/bWW27tS6uerPdbV1TO9yKUH+OeXYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAAAAAZIpgGwAAAACATBFss0n1DU1lsY80jplG3VnW0f7qzPvKYYzKoYauKgt9n4UasyatPu2KY9kV25wVnR2bQo+tuVIc+pW0mYP/ph/KR3vGoquNW1drL2xMsA0AAAAAQKYItgEAAAAAyBTBNgAAAAAAmZJqsD1p0qQ49NBDo3fv3tGvX7848cQT44UXXmixTZIk0djYGHV1dVFdXR0jR46M+fPnp1QxAAAAAABpSzXYnjVrVlx44YUxe/bsmDFjRqxduzZGjRoVK1euzG9zzTXXxLXXXhuTJ0+OOXPmRG1tbRxzzDGxYsWKFCsHAAAAACAt26Z58OnTp7f4ecqUKdGvX7946qmn4sgjj4wkSeK6666LSy+9NE466aSIiJg6dWrU1NTEHXfcEeeee24aZQMAAAAAkKKyesb2smXLIiJip512ioiIBQsWRHNzc4waNSq/TVVVVYwYMSIef/zxTe5j9erVsXz58hYvAAAAAAAqR9kE20mSxLhx4+L9739/DBkyJCIimpubIyKipqamxbY1NTX5dRubNGlS9OnTJ/8aOHBgcQuvcPUNTZnYJ6VRzLHrzL5LNae2dJyuOq831+6O9EdX7cOItrW9K/ZP1trcnnqz1rbO2Fpbu1JfpKVQfVyuY+XcS1dbz/Es9X2Waq0EldDfhWxDJfTHxrKcK6Q1HoU+biXOq7Yo13aXa11ZVDbB9kUXXRTPPPNM/OQnP2m1LpfLtfg5SZJWy9a75JJLYtmyZfnXwoULi1IvAAAAAADpSPUZ2+t97nOfi3vuuSceeeSRGDBgQH55bW1tRPz7m9v9+/fPL1+8eHGrb3GvV1VVFVVVVcUtGAAAAACA1KT6je0kSeKiiy6KO++8Mx566KEYNGhQi/WDBg2K2tramDFjRn7ZmjVrYtasWTF8+PBSlwsAAAAAQBlI9RvbF154Ydxxxx3xq1/9Knr37p1/bnafPn2iuro6crlcjB07NiZOnBiDBw+OwYMHx8SJE6Nnz54xZsyYNEsHAAAAACAlqQbbN954Y0REjBw5ssXyKVOmxBlnnBEREePHj48333wzLrjggliyZEkMHTo0Hnzwwejdu3eJqwUAAAAAoBykGmwnSbLVbXK5XDQ2NkZjY2PxCwIAAAAAoOyl+oxtoPLUNzS1a/3Wts+ySm7bxjrS1vqGporpo/a2pTPtrpQ+64w0+yBL/Z+lWrOgq/VnJba3XNu0ubo2XF6s2je13y0dK40+LPYx27r/cp0/hVTqNhbqeFkcG/Ou44rZJ225HleiSm9fMRSyz/R/5wi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwTURE1Dc0dWp9Z7fvjGIca/0+N/5npai09mxNqcazq/UrpdHV51Ultr+jbarEvih3nRkr49U2xeynSh+DQrWv1P2U5fOjI/dMWW1rRxS6/V2p72ifSpsbxfps2FWvWZXQBtpOsA0AAAAAQKYItgEAAAAAyBTBNgAAAAAAmSLYBgAAAAAgUwTbAAAAAABkimAbAAAAAIBMEWwDAAAAAJApgu0urL6hqcPv29x7O7rPLR2r2Meg/LRnjM2HytDecSzkuG94TSvGfOrI/rd0naU8pTVe5km2VNJ4lUNbyqGGrOpI36Xd32kff70s9l056Ozn+yz0YRZqBFrb2rlbzHO7mPehXYVgGwAAAACATBFsAwAAAACQKYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2xRcfUPTJv89C7JWb1fTnvEp1raFUGnzrFTt2dpx0ujXLR2zo/W0d+5W2nwqpbb2XWf6uBTzIA0dqa/c29RZxeiTYvbZ+n1X+rhsTldqdzHa2tbfP4U+drHvMwp1vd/cfspt3pVbPcWW9uc2ykMljNmm2lCMOVzO99RpKlRbu1KfFZNgGwAAAACATBFsAwAAAACQKYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2K5w9Q1NaZfQaR1pw4bv6WgfVELftUV9Q1On+2tr7ylVX7b3OIWqq6vMlUJoa19lqU+zVGuWlLpfC3m8ze1r4+ttMXXkXCuXa3kWdaZvNn5vW+ZJGudHVsa/LXWWa1vKta72KOS5UEyFOFYljFdEtvo9jT4vxjEr8fPw5pRrGzr7e60Y98yl0pnfk+1dXs42VXMW24FgGwAAAACAjBFsAwAAAACQKYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME22RafUNT2iXw/9Q3NGVyPDauuS1t2NI2heyD9fvKYr9uSkfbseH7Nv73zo5XOSjEOHdkHpN97R3nNOdFV56TxW57MX7vZFXW6y+EUn1GKUfFbJ/fs5tW6H7I0u+19lr/uXVrNReqTeXaN52tq9TtauvxyrW/u7pSjkshzu1Ku/8vFcE2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2abP6hqZU3ltIhaijXNpSiTbs22L3c1v3b7zLX1ccoy21uVD9UU792pZa2lNvqdrW3rrrG5o6dW0qdLu2tr+OHq+c5tbmqHHzxyn0+bil91TyHMuacu7TSvy9V266Ut905PNEV+qfQirW75ON35/la0Rnfg8Wa36W8p69kMqh1vaOSTnUnAWCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYLuLqm9oatOyUknj2Gm2t1QqpY31DU0laUta/ZXFcWpvzR1tYxb7ptxUah+Wul2bO157l3fmWIWwft8bHmNT19hitKvcZbFtW6q5VNfpUh+jkHWW85gXq7audG5XYpvKVTH62vi1VshrPv+WpT4tdD1b21+5tb8cFKLP9GvhCbYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAAAAAZIpgGwAAAACATBFsAwAAAACQKYJtAAAAAAAyRbBNauobmlJ9P1u2uf4tdr93lXFtazvLqT+2Vsum1tc3NGWqDeUu6/WnpaP9Vq79vXFd7a2zlL9/O3LdIFvaMoaFmKNbm/dZn0vr689COwp5Xm+p3Vv6LNrW/irVNSgL49ZeW2pTltrbnrnVln2k3fYsXSvaI81soFh9ueFYdeazU6nGutLm1Ka05zPLxuNWafcTlUSwDQAAAABApgi2AQAAAADIFME2AAAAAACZkmqw/cgjj8QJJ5wQdXV1kcvl4u67726xPkmSaGxsjLq6uqiuro6RI0fG/Pnz0ykWAAAAAICykGqwvXLlyjjwwANj8uTJm1x/zTXXxLXXXhuTJ0+OOXPmRG1tbRxzzDGxYsWKElcKAAAAAEC52DbNg48ePTpGjx69yXVJksR1110Xl156aZx00kkRETF16tSoqamJO+64I84999xSlgoAAAAAQJko22dsL1iwIJqbm2PUqFH5ZVVVVTFixIh4/PHHN/u+1atXx/Lly1u8AAAAAACoHGUbbDc3N0dERE1NTYvlNTU1+XWbMmnSpOjTp0/+NXDgwKLW2RXUNzSlXULelmoppzrT1NF+aM/7unJfF6vtWerTLNW6KVmvvyO0ubz7oBS1FfsY5dy/WVDM/uvqY1Np7Xcub9nW6i/l9TarfVmousu9/ZuqL4uf87PweaccayqEzvZ9pdx/l3NtxeD3cPkp22B7vVwu1+LnJElaLdvQJZdcEsuWLcu/Fi5cWOwSAQAAAAAooVSfsb0ltbW1EfHvb273798/v3zx4sWtvsW9oaqqqqiqqip6fQAAAAAApKNsv7E9aNCgqK2tjRkzZuSXrVmzJmbNmhXDhw9PsTIAAAAAANKU6je233jjjfjzn/+c/3nBggUxb9682GmnnWK33XaLsWPHxsSJE2Pw4MExePDgmDhxYvTs2TPGjBmTYtUAAAAAAKQp1WD7ySefjKOOOir/87hx4yIi4vTTT4/bbrstxo8fH2+++WZccMEFsWTJkhg6dGg8+OCD0bt377RKBgAAAAAgZakG2yNHjowkSTa7PpfLRWNjYzQ2NpauKAAAAAAAylrZPmOb7KhvaGrxz0Lts5D7q3RZ6a9i1ljo+VduyrGmrqatY1Do7dq7bXtsar9Znmsb196WtmS5vZvS2fZU+rV0a7ZWc6Ha1N79FOL3fKnGo7PXto0/V2ZxHlFe1p8/5tSWleM1opj7qESV0C+daUMp2l+q+9nNHSfNMa7Ez9XFqDdrfVAJBNsAAAAAAGSKYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItju4uobmsrq+PUNTQWraUv7SbvdxVKsdhV6v23dXyHmQ0ffX6w2V+rcK1fF6nfjWB5K8fsibWlfO8q5b4qlvW0up9+97fn92hmbe3+x50va50MxFKItm/o8nbZyqKHc6JOW9EfnVeI1sb3S/p1dyPyiPcfc8J8b/zt0NYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAAAAAZIpgGwAAAACATBFsU3T1DU0t/rm59W3ZRyHq2NTyUtVQaIWuqRzbmHUb9mlb+7ecx6HY50pH37vx+8rlnO7I+LN5aY9roY+/tW3NmXSl0f+lvn6aY5WrmJ+d27ptqWvY8D2FvF5X6nnS3nYVqx+2dq+YhnKqZVPKvb6t2dw52pHP81s6Rrkq59oqSZrXeGNcWoJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAAAAAZIpgGwAAAACATNk27QIoP/UNTWmXUFSV3r6NdbX2bk7W+iFr9WZVufVze+rZ1LZben+5tbWYulJby42+75qMe2l1tr/b8v5SjmmW50+Way+WYvRJez/zwMbKeb6kUVs590cl0t/F5RvbAAAAAABkimAbAAAAAIBMEWwDAAAAAJApgm0AAAAAADJFsA0AAAAAQKYItgEAAAAAyBTBNiVV39CUdgntlsWa09bWPitG3xov0pwDhT52Z/bnXOgc/cfmmBttp68KoxD9aCzar76hSb8VSbH6taP73dT70j7vusrca0s7129T7D5Je8yLyX13+1Ry2yqRYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNu0UN/Q1Kn1lI/NjVWljGG5tKOQdZRLm9qqUPWWex8Wep9ZG+dyl1Z/GkcoPOdVYZVjf5ZjTXSOMQUgTYJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAAAAAZIpgGwAAAACATBFsw/9T39CUdglF1ZH2daZP6huaKr5PoVI4Vzumrf1WyP41VuWtFONjDlQOYwnF4dzqWrI03hvXWurat3a8LPVlOdBf5UGwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbBP1DU1pl0BGmTtAVrl+FYZ+bE2fAF1Blq515VxrOdcGbJ1zOH2CbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbANABahvaEq7BAAANpD257O0jw9QbIJtAAAAAAAyRbANAAAAAECmCLYBAAAAAMiUTATbN9xwQwwaNCi22267OPjgg+PRRx9NuyQAAAAAAFJS9sH2T3/60xg7dmxceumlMXfu3DjiiCNi9OjR8dJLL6VdGgAAAAAAKSj7YPvaa6+Ns88+O84555zYd99947rrrouBAwfGjTfemHZpAAAAAACkYNu0C9iSNWvWxFNPPRUNDQ0tlo8aNSoef/zxTb5n9erVsXr16vzPy5Yti4iI5cuXF6/QMrZu9aoWbV+3elVB9798+fKC7zML0p5PXbHPSXfemXNdkzlHqaz/PGHOUWrmHKVmzlFq5hylJi8pnLT7Mi3r250kyVa3zSVt2Solr7zySuy6667x2GOPxfDhw/PLJ06cGFOnTo0XXnih1XsaGxvj8ssvL2WZAAAAAAAUyMKFC2PAgAFb3Kasv7G9Xi6Xa/FzkiStlq13ySWXxLhx4/I/r1u3Ll5//fXYeeedN/uermD58uUxcODAWLhwYeywww5plwMFYV5TicxrKpF5TaUyt6lE5jWVytymElXivE6SJFasWBF1dXVb3basg+1ddtklttlmm2hubm6xfPHixVFTU7PJ91RVVUVVVVWLZe9617uKVWLm7LDDDhUz0WE985pKZF5TicxrKpW5TSUyr6lU5jaVqNLmdZ8+fdq0XVn/8cgePXrEwQcfHDNmzGixfMaMGS0eTQIAAAAAQNdR1t/YjogYN25cnHbaaXHIIYfEsGHD4uabb46XXnopzjvvvLRLAwAAAAAgBWUfbJ988snxr3/9K/7rv/4rFi1aFEOGDIn77rsvdt9997RLy5SqqqqYMGFCq8e0QJaZ11Qi85pKZF5TqcxtKpF5TaUyt6lEXX1e55IkSdIuAgAAAAAA2qqsn7ENAAAAAAAbE2wDAAAAAJApgm0AAAAAADJFsA0AAAAAQKYItjNi0qRJceihh0bv3r2jX79+ceKJJ8YLL7zQYpskSaKxsTHq6uqiuro6Ro4cGfPnz2+xzerVq+Nzn/tc7LLLLrH99tvHRz7ykfjHP/7R6nhNTU0xdOjQqK6ujl122SVOOumkoraPrqmU8/pPf/pTfPSjH41ddtkldthhhzj88MPj4YcfLnob6XoKNa9vvvnmGDlyZOywww6Ry+Vi6dKlrY61ZMmSOO2006JPnz7Rp0+fOO200za5HRRCqeb23/72tzj77LNj0KBBUV1dHXvuuWdMmDAh1qxZU+wm0gWV8pq93urVq+Oggw6KXC4X8+bNK0Kr6OpKPa/dO1IqpZzb7h8plULM69dffz0+97nPxd577x09e/aM3XbbLT7/+c/HsmXLWuynEu8fBdsZMWvWrLjwwgtj9uzZMWPGjFi7dm2MGjUqVq5cmd/mmmuuiWuvvTYmT54cc+bMidra2jjmmGNixYoV+W3Gjh0bd911V0ybNi1+85vfxBtvvBHHH398vPPOO/ltfvnLX8Zpp50WZ555Zjz99NPx2GOPxZgxY0raXrqGUs7r4447LtauXRsPPfRQPPXUU3HQQQfF8ccfH83NzSVtM5WvUPN61apV8eEPfzi++tWvbvZYY8aMiXnz5sX06dNj+vTpMW/evDjttNOK2j66rlLN7eeffz7WrVsX3//+92P+/Pnx7W9/O2666aYtngvQUaW8Zq83fvz4qKurK0p7IKK089q9I6VUyrnt/pFSKcS8fuWVV+KVV16Jb37zm/Hss8/GbbfdFtOnT4+zzz67xbEq8v4xIZMWL16cREQya9asJEmSZN26dUltbW1y1VVX5bd56623kj59+iQ33XRTkiRJsnTp0qR79+7JtGnT8tu8/PLLSbdu3ZLp06cnSZIkb7/9drLrrrsmP/zhD0vYGvi3Ys3r1157LYmI5JFHHslvs3z58iQikv/93/8tRdPowjoyrzf08MMPJxGRLFmypMXy5557LomIZPbs2fllTzzxRBIRyfPPP1+cxsAGijW3N+Waa65JBg0aVLDaYXOKPa/vu+++ZJ999knmz5+fREQyd+7cYjQDWijWvHbvSNqKNbfdP5Kmzs7r9X72s58lPXr0SN5+++0kSSr3/tE3tjNq/f9OsNNOO0VExIIFC6K5uTlGjRqV36aqqipGjBgRjz/+eEREPPXUU/H222+32Kauri6GDBmS3+b3v/99vPzyy9GtW7d4z3veE/3794/Ro0e3+l93oBiKNa933nnn2HfffeNHP/pRrFy5MtauXRvf//73o6amJg4++OBSNY8uqiPzui2eeOKJ6NOnTwwdOjS/7LDDDos+ffq0az/QUcWa25s71vrjQDEVc16/+uqr8ZnPfCZ+/OMfR8+ePQtXNGxFsea1e0fSVqy57f6RNBVqXi9btix22GGH2HbbbSOicu8fBdsZlCRJjBs3Lt7//vfHkCFDIiLy/ztMTU1Ni21ramry65qbm6NHjx6x4447bnabv/71rxER0djYGF/72tfi3nvvjR133DFGjBgRr7/+elHbRddWzHmdy+VixowZMXfu3Ojdu3dst9128e1vfzumT58e73rXu4rcMrqyjs7rtmhubo5+/fq1Wt6vXz//iyRFV8y5vbG//OUvcf3118d5553X8YKhDYo5r5MkiTPOOCPOO++8OOSQQwpXNGxFMee1e0fSVMy57f6RtBRqXv/rX/+KK664Is4999z8skq9f9w27QJov4suuiieeeaZ+M1vftNqXS6Xa/FzkiStlm1sw23WrVsXERGXXnpp/Od//mdEREyZMiUGDBgQP//5z1ucFFBIxZzXSZLEBRdcEP369YtHH300qqur44c//GEcf/zxMWfOnOjfv3/hGgIbKPS83to+OrofaK9iz+31Xnnllfjwhz8cH//4x+Occ87p0D6grYo5r6+//vpYvnx5XHLJJZ2uE9qjmPPavSNpKubcdv9IWgoxr5cvXx7HHXdc7LfffjFhwoQt7mNL+8kK39jOmM997nNxzz33xMMPPxwDBgzIL6+trY2IaPVfWRYvXpz/rzq1tbWxZs2aWLJkyWa3WX+B3m+//fLrq6qqYo899oiXXnqp8A2CKP68fuihh+Lee++NadOmxeGHHx7vfe9744Ybbojq6uqYOnVqMZtGF9aZed0WtbW18eqrr7Za/tprr7VrP9BexZ7b673yyitx1FFHxbBhw+Lmm2/uXNGwFcWe1w899FDMnj07qqqqYtttt4299torIiIOOeSQOP300wvQAmit2PPavSNpKcU12/0jpVaIeb1ixYr48Ic/HL169Yq77rorunfv3mI/lXj/KNjOiCRJ4qKLLoo777wzHnrooRg0aFCL9YMGDYra2tqYMWNGftmaNWti1qxZMXz48IiIOPjgg6N79+4ttlm0aFH84Q9/aLFNVVVVvPDCC/lt3n777fjb3/4Wu+++ezGbSBdUqnm9atWqiIjo1q3lJa9bt275b5pAoRRiXrfFsGHDYtmyZfG73/0uv+y3v/1tLFu2rF37gbYq1dyOiHj55Zdj5MiR8d73vjemTJnS6voNhVKqef3d7343nn766Zg3b17Mmzcv7rvvvoiI+OlPfxrf+MY3CtMY+H9KNa/dO1JqpZrb7h8ppULN6+XLl8eoUaOiR48ecc8998R2223XYj8Ve/9Ygj9QSQGcf/75SZ8+fZKZM2cmixYtyr9WrVqV3+aqq65K+vTpk9x5553Js88+m5xyyilJ//79k+XLl+e3Oe+885IBAwYk//u//5v8/ve/T44++ujkwAMPTNauXZvf5gtf+EKy6667Jg888EDy/PPPJ2effXbSr1+/5PXXXy9pm6l8pZrXr732WrLzzjsnJ510UjJv3rzkhRdeSL70pS8l3bt3T+bNm1fydlPZCjWvFy1alMydOzf5wQ9+kP+r7HPnzk3+9a9/5bf58Ic/nBxwwAHJE088kTzxxBPJ/vvvnxx//PElbS9dR6nm9ssvv5zstddeydFHH5384x//aHEsKLRSXrM3tGDBgiQikrlz5xa7iXRBpZzX7h0ppVLNbfePlFIh5vXy5cuToUOHJvvvv3/y5z//ucV+Nsz7KvH+UbCdERGxydeUKVPy26xbty6ZMGFCUltbm1RVVSVHHnlk8uyzz7bYz5tvvplcdNFFyU477ZRUV1cnxx9/fPLSSy+12GbNmjXJF7/4xaRfv35J7969kw9+8IPJH/7wh1I0ky6mlPN6zpw5yahRo5Kddtop6d27d3LYYYcl9913XymaSRdTqHk9YcKEre7nX//6V3LqqacmvXv3Tnr37p2ceuqpyZIlS0rTULqcUs3tKVOmbPZYUGilvGZvSLBNMZVyXrt3pJRKObfdP1IqhZjXDz/88Gb3s2DBgvx2lXj/mEuSJGnbd7sBAAAAACB9HlgIAAAAAECmCLYBAAAAAMgUwTYAAAAAAJki2AYAAAAAIFME2wAAAAAAZIpgGwAAAACATBFsAwAAAACQKYJtAAAAAAAyRbANAABlaOTIkTF27Ni0ywAAgLIk2AYAgIybOXNm5HK5WLp0adqlAABASQi2AQAAAADIFME2AACkbOXKlfHpT386evXqFf37949vfetbLdbffvvtccghh0Tv3r2jtrY2xowZE4sXL46IiL/97W9x1FFHRUTEjjvuGLlcLs4444yIiEiSJK655prYY489orq6Og488MD4xS9+UdK2AQBAMQi2AQAgZV/+8pfj4YcfjrvuuisefPDBmDlzZjz11FP59WvWrIkrrrginn766bj77rtjwYIF+fB64MCB8ctf/jIiIl544YVYtGhRfOc734mIiK997WsxZcqUuPHGG2P+/Plx8cUXx6c+9amYNWtWydsIAACFlEuSJEm7CAAA6KreeOON2HnnneNHP/pRnHzyyRER8frrr8eAAQPis5/9bFx33XWt3jNnzpx43/veFytWrIhevXrFzJkz46ijjoolS5bEu971roj497fAd9lll3jooYdi2LBh+feec845sWrVqrjjjjtK0TwAACiKbdMuAAAAurK//OUvsWbNmhbh80477RR77713/ue5c+dGY2NjzJs3L15//fVYt25dRES89NJLsd9++21yv88991y89dZbccwxx7RYvmbNmnjPe95ThJYAAEDpCLYBACBFW/sfKFeuXBmjRo2KUaNGxe233x59+/aNl156KT70oQ/FmjVrNvu+9eF3U1NT7Lrrri3WVVVVdb5wAABIkWAbAABStNdee0X37t1j9uzZsdtuu0VExJIlS+JPf/pTjBgxIp5//vn45z//GVdddVUMHDgwIiKefPLJFvvo0aNHRES88847+WX77bdfVFVVxUsvvRQjRowoUWsAAKA0BNsAAJCiXr16xdlnnx1f/vKXY+edd46ampq49NJLo1u3f/+d99122y169OgR119/fZx33nnxhz/8Ia644ooW+9h9990jl8vFvffeG8cee2xUV1dH796940tf+lJcfPHFsW7dunj/+98fy5cvj8cffzx69eoVp59+ehrNBQCAguiWdgEAANDV/fd//3cceeSR8ZGPfCQ++MEPxvvf//44+OCDIyKib9++cdttt8XPf/7z2G+//eKqq66Kb37zmy3ev+uuu8bll18eDQ0NUVNTExdddFFERFxxxRVx2WWXxaRJk2LfffeND33oQ/HrX/86Bg0aVPI2AgBAIeWSrT3UDwAAAAAAyohvbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApgi2AQAAAADIFME2AAAAAACZItgGAAAAACBTBNsAAAAAAGSKYBsAAAAAgEwRbAMAAAAAkCmCbQAAAAAAMkWwDQAAAABApvx/YM6t6qcofrkAAAAASUVORK5CYII=",
      "text/plain": [
       "<Figure size 1800x500 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.figure(figsize=(18, 5))\n",
    "plt.bar(distinct_users_doing_tagging_by_date_x, distinct_users_doing_tagging_by_date_y)\n",
    "# limit the y axis, some observations may go beyond the ceiling\n",
    "ax = plt.gca()\n",
    "ax.set_ylim([0, 50])\n",
    "plt.title(\"Active users by date\")\n",
    "plt.xlabel(\"date\")\n",
    "plt.ylabel(\"count\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e7759fb-b3f8-4ab9-8266-6da2ecf60fd1",
   "metadata": {},
   "source": [
    "Seems like while there were peaks and troughs, but visually, we don't see too much variation in the daily active users"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16ab200a-15e3-4c50-8f4e-273a5a1cec16",
   "metadata": {},
   "source": [
    "# Clear cache and stop the spark cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "af5974e7-f1a1-4cde-a3e8-67be94fa1c08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# clear cache\n",
    "spark.catalog.clearCache()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "31a5e020-a4ad-450a-9be4-8ef38daf08f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stop spark\n",
    "spark.stop()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4716527d-c142-4bb8-a548-ce91c5847297",
   "metadata": {},
   "source": [
    "# Insights"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0429a8dc-13dc-404e-9d59-fdcc8e05bcf8",
   "metadata": {},
   "source": [
    "What have we learned from working on Problem Set 1?\n",
    "\n",
    "1. Try to get a specific schema\n",
    "1. Clean up data before analysis or aggregation\n",
    "1. Clearly document weather you need case sensitive or case insensitive comparisions\n",
    "1. Use ```df.withColumn``` to create new columns\n",
    "1. ```col().contains()``` is like the ```LIKE``` clause in SQL, partial matches etc.\n",
    "1. Spark Columns: Only use literal name where it's trivial, otherwise prefer ```col('name')```\n",
    "1. ```distinct()``` vs ```groupBy()```\n",
    "1. To convert a column in a spark data frame to a list (for plotting data or other uses) you isolate the column, convert to rdd, flatmap, collect.\n",
    "  \n",
    "1. and another thing... use ```spark.catalog.clearCache()``` to clear the cache"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b32d2cb1-3cb6-44ed-92ab-1799c58d7912",
   "metadata": {},
   "source": [
    "# Next"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84836b3c-241f-4267-a6d5-f73cd1e9b8a2",
   "metadata": {},
   "source": [
    "We continue our data analysis exercises with the movies.csv data."
   ]
  }
 ],
 "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.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}