notebook/03 - Mining Frequent Itemsets using FP-Growth Algorithm.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Assignment 03 - Mining Frequent Itemsets using FP-Growth Algorithm\n",
    "## 1. Preparation\n",
    "### 1.1 Requirements\n",
    "\n",
    "1. Operating System : Linux Mint 18.3 Sylvia\n",
    "2. Apache Spark 2.4.0 Binary (https://spark.apache.org/downloads.html)\n",
    "3. PySpark 2.4.0 (Apache Spark Python API)\n",
    "4. Findspark 1.3.0 (Python's library)\n",
    "5. Jupyter Notebook (https://jupyter.org/install)\n",
    "\n",
    "### 1.2 Description of Dataset\n",
    "* Dataset's name : [Brazilian E-Commerce Public Dataset by Olist](https://www.kaggle.com/olistbr/brazilian-ecommerce)\n",
    "* Description : a Brazilian ecommerce public dataset of orders made at Olist Store. The dataset has information of 100k orders from 2016 to 2018 made at multiple marketplaces in Brazil. Its features allows viewing an order from multiple dimensions: from order status, price, payment and freight performance to customer location, product attributes and finally reviews written by customers. \n",
    "* There are nine data sources in this dataset, but I'll use only two data sources that required to mining frequent itemsets, that is **olist_order_items_dataset.csv** and **olist_products_dataset.csv**\n",
    "    \n",
    "| Data Source | Number of Rows | Number of Columns |\n",
    "|---|---|---|\n",
    "| olist_customers_dataset.csv | 99.4k | 5 |\n",
    "| olist_geolocation_dataset.csv | 1.00m | 5 |\n",
    "| olist_order_items_dataset.csv | 113k | 7 |\n",
    "| olist_order_payments_dataset.csv | 104k | 5 |\n",
    "| olist_order_reviews_dataset.csv | 100.0k | 7 |\n",
    "| olist_orders_dataset.csv | 99.4k | 8 |\n",
    "| olist_products_dataset.csv | 33.0k | 9 |\n",
    "| olist_sellers_dataset.csv | 3095 | 4 |\n",
    "| product_category_name_translation.csv | 71 | 2 |\n",
    "\n",
    "![data scheme](img/olistdata.png)\n",
    "\n",
    "## 2. Spark Initialization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import findspark to make pyspark importable as a regular library\n",
    "import findspark\n",
    "findspark.init('/home/mocatfrio/spark') \n",
    "\n",
    "# /home/mocatfrio/spark has symbolic link to /bin/spark-2.4.0-bin-hadoop2.7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import SparkSession\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# Create Spark Session\n",
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .appName(\"Frequent Itemsets\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pyspark.sql.session.SparkSession object at 0x7fd420313828>\n"
     ]
    }
   ],
   "source": [
    "# Print spark object ID\n",
    "print(spark)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Load Dataset\n",
    "### 3.1 Order items Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the dataset\n",
    "df_order = spark.read.load(\"/home/mocatfrio/Documents/projects/big-data/datasets/brazilian_ecommerce/olist_order_items_dataset.csv\", \\\n",
    "                     format=\"csv\", sep=\",\", inferSchema=\"true\", header=\"true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-------------+--------------------+--------------------+-------------------+------+-------------+\n",
      "|            order_id|order_item_id|          product_id|           seller_id|shipping_limit_date| price|freight_value|\n",
      "+--------------------+-------------+--------------------+--------------------+-------------------+------+-------------+\n",
      "|00010242fe8c5a6d1...|            1|4244733e06e7ecb49...|48436dade18ac8b2b...|2017-09-19 09:45:35|  58.9|        13.29|\n",
      "|00018f77f2f0320c5...|            1|e5f2d52b802189ee6...|dd7ddc04e1b6c2c61...|2017-05-03 11:05:13| 239.9|        19.93|\n",
      "|000229ec398224ef6...|            1|c777355d18b72b67a...|5b51032eddd242adc...|2018-01-18 14:48:30| 199.0|        17.87|\n",
      "|00024acbcdf0a6daa...|            1|7634da152a4610f15...|9d7a1d34a50524090...|2018-08-15 10:10:18| 12.99|        12.79|\n",
      "|00042b26cf59d7ce6...|            1|ac6c3623068f30de0...|df560393f3a51e745...|2017-02-13 13:57:51| 199.9|        18.14|\n",
      "|00048cc3ae777c65d...|            1|ef92defde845ab845...|6426d21aca402a131...|2017-05-23 03:55:27|  21.9|        12.69|\n",
      "|00054e8431b9d7675...|            1|8d4f2bb7e93e6710a...|7040e82f899a04d1b...|2017-12-14 12:10:31|  19.9|        11.85|\n",
      "|000576fe39319847c...|            1|557d850972a7d6f79...|5996cddab893a4652...|2018-07-10 12:30:45| 810.0|        70.75|\n",
      "|0005a1a1728c9d785...|            1|310ae3c140ff94b03...|a416b6a846a117243...|2018-03-26 18:31:29|145.95|        11.65|\n",
      "|0005f50442cb953dc...|            1|4535b0e1091c278df...|ba143b05f0110f0dc...|2018-07-06 14:10:56| 53.99|         11.4|\n",
      "|00061f2a7bc09da83...|            1|d63c1011f49d98b97...|cc419e0650a3c5ba7...|2018-03-29 22:28:09| 59.99|         8.88|\n",
      "|00063b381e2406b52...|            1|f177554ea93259a5b...|8602a61d680a10a82...|2018-07-31 17:30:39|  45.0|        12.98|\n",
      "|0006ec9db01a64e59...|            1|99a4788cb24856965...|4a3ca9315b744ce9f...|2018-07-26 17:24:20|  74.0|        23.32|\n",
      "|0008288aa423d2a3f...|            1|368c6c730842d7801...|1f50f920176fa81da...|2018-02-21 02:55:52|  49.9|        13.37|\n",
      "|0008288aa423d2a3f...|            2|368c6c730842d7801...|1f50f920176fa81da...|2018-02-21 02:55:52|  49.9|        13.37|\n",
      "|0009792311464db53...|            1|8cab8abac59158715...|530ec6109d11eaaf8...|2018-08-17 12:15:10|  99.9|        27.65|\n",
      "|0009c9a17f916a706...|            1|3f27ac8e699df3d30...|fcb5ace8bcc92f757...|2018-05-02 09:31:53| 639.0|        11.34|\n",
      "|000aed2e25dbad2f9...|            1|4fa33915031a8cde0...|fe2032dab1a61af87...|2018-05-16 20:57:03| 144.0|         8.77|\n",
      "|000c3e6612759851c...|            1|b50c950aba0dcead2...|218d46b86c1881d02...|2017-08-21 03:33:13|  99.0|        13.71|\n",
      "|000e562887b1f2006...|            1|5ed9eaf534f6936b5...|8cbac7e12637ed9cf...|2018-02-28 12:08:37|  25.0|        16.11|\n",
      "+--------------------+-------------+--------------------+--------------------+-------------------+------+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Print top 20 rows data\n",
    "df_order.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "112650"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count data rows\n",
    "df_order.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- order_id: string (nullable = true)\n",
      " |-- order_item_id: integer (nullable = true)\n",
      " |-- product_id: string (nullable = true)\n",
      " |-- seller_id: string (nullable = true)\n",
      " |-- shipping_limit_date: timestamp (nullable = true)\n",
      " |-- price: double (nullable = true)\n",
      " |-- freight_value: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# inferSchema is used to inference the actual datatype of columns, especially for dates and timestamp\n",
    "df_order.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Register the dataframe as a SQL temporary view\n",
    "df_order.createOrReplaceTempView(\"order\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1 Products Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the dataset\n",
    "df_product = spark.read.load(\"/home/mocatfrio/Documents/projects/big-data/datasets/brazilian_ecommerce/olist_products_dataset.csv\", \\\n",
    "                     format=\"csv\", sep=\",\", inferSchema=\"true\", header=\"true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---------------------+-------------------+--------------------------+------------------+----------------+-----------------+-----------------+----------------+\n",
      "|          product_id|product_category_name|product_name_lenght|product_description_lenght|product_photos_qty|product_weight_g|product_length_cm|product_height_cm|product_width_cm|\n",
      "+--------------------+---------------------+-------------------+--------------------------+------------------+----------------+-----------------+-----------------+----------------+\n",
      "|1e9e8ef04dbcff454...|           perfumaria|                 40|                       287|                 1|             225|               16|               10|              14|\n",
      "|3aa071139cb16b67c...|                artes|                 44|                       276|                 1|            1000|               30|               18|              20|\n",
      "|96bd76ec8810374ed...|        esporte_lazer|                 46|                       250|                 1|             154|               18|                9|              15|\n",
      "|cef67bcfe19066a93...|                bebes|                 27|                       261|                 1|             371|               26|                4|              26|\n",
      "|9dc1a7de274444849...| utilidades_domest...|                 37|                       402|                 4|             625|               20|               17|              13|\n",
      "|41d3672d4792049fa...| instrumentos_musi...|                 60|                       745|                 1|             200|               38|                5|              11|\n",
      "|732bd381ad09e530f...|           cool_stuff|                 56|                      1272|                 4|           18350|               70|               24|              44|\n",
      "|2548af3e6e77a690c...|     moveis_decoracao|                 56|                       184|                 2|             900|               40|                8|              40|\n",
      "|37cc742be07708b53...|     eletrodomesticos|                 57|                       163|                 1|             400|               27|               13|              17|\n",
      "|8c92109888e8cdf9d...|           brinquedos|                 36|                      1156|                 1|             600|               17|               10|              12|\n",
      "|14aa47b7fe5c25522...|      cama_mesa_banho|                 54|                       630|                 1|            1100|               16|               10|              16|\n",
      "|03b63c5fc16691530...|                bebes|                 49|                       728|                 4|            7150|               50|               19|              45|\n",
      "|cf55509ea8edaaac1...| instrumentos_musi...|                 43|                      1827|                 3|             250|               17|                7|              17|\n",
      "|7bb6f29c2be577161...|     moveis_decoracao|                 51|                      2083|                 2|             600|               68|               11|              13|\n",
      "|eb31436580a610f20...| construcao_ferram...|                 59|                      1602|                 4|             200|               17|                7|              17|\n",
      "|3bb7f144022e67327...|        esporte_lazer|                 22|                      3021|                 1|             800|               16|                2|              11|\n",
      "|6a2fb4dd53d2cdb88...|           perfumaria|                 39|                       346|                 2|             400|               27|                5|              20|\n",
      "|a1b71017a84f92fd8...| informatica_acess...|                 59|                       636|                 1|             900|               40|               15|              20|\n",
      "|a0736b92e52f6cead...|     moveis_decoracao|                 56|                       296|                 2|            1700|              100|                7|              15|\n",
      "|f53103a77d9cf245e...|      cama_mesa_banho|                 52|                       206|                 1|             500|               16|               10|              16|\n",
      "+--------------------+---------------------+-------------------+--------------------------+------------------+----------------+-----------------+-----------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_product.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32951"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_product.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- product_id: string (nullable = true)\n",
      " |-- product_category_name: string (nullable = true)\n",
      " |-- product_name_lenght: integer (nullable = true)\n",
      " |-- product_description_lenght: integer (nullable = true)\n",
      " |-- product_photos_qty: integer (nullable = true)\n",
      " |-- product_weight_g: integer (nullable = true)\n",
      " |-- product_length_cm: integer (nullable = true)\n",
      " |-- product_height_cm: integer (nullable = true)\n",
      " |-- product_width_cm: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_product.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_product.createOrReplaceTempView(\"product\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Frequent Pattern Mining using FP-Growth Algorithm\n",
    "Apache Spark has provides a library to mining frequent itemsets using FP-Growth algorithm. First step of FP-growth is to calculate item frequencies and identify frequent items. Then, FP-Growth uses a suffix tree (FP-Tree) structure to encode transactions without generating candidate sets explicitly, which are usually expensive to generate. After that, the frequent itemsets can be extracted from the FP-Tree. For further explanation about FP-Growth, please visit http://hareenlaks.blogspot.com/2011/06/fp-tree-example-how-to-identify.html\n",
    "\n",
    "\n",
    "### 4.1 Preprocess Data\n",
    "First, we need to preprocess the original data because FP-Growth algorithm from Spark can only process 2-column data. i.e. id and items, for example:\n",
    "\n",
    "| id|       items|\n",
    "|---|------------|\n",
    "|  0|   [1, 2, 5]|\n",
    "|  1|[1, 2, 3, 5]|\n",
    "|  2|      [1, 2]|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------------------+-------+\n",
      "|product_id                      |item_id|\n",
      "+--------------------------------+-------+\n",
      "|00066f42aeeb9f3007548bb9d3f33c38|1      |\n",
      "|00088930e925c41fd95ebfe695fd2655|2      |\n",
      "|0009406fd7479715e4bef61dd91f2462|3      |\n",
      "|000b8f95fcb9e0096488278317764d19|4      |\n",
      "|000d9be29b5207b54e86aa1b1ac54872|5      |\n",
      "|0011c512eb256aa0dbbb544d8dffcf6e|6      |\n",
      "|00126f27c813603687e6ce486d909d01|7      |\n",
      "|001795ec6f1b187d37335e1c4704762e|8      |\n",
      "|001b237c0e9bb435f2e54071129237e9|9      |\n",
      "|001b72dfd63e9833e8c02742adf472e3|10     |\n",
      "|001c5d71ac6ad696d22315953758fa04|11     |\n",
      "|00210e41887c2a8ef9f791ebc780cc36|12     |\n",
      "|002159fe700ed3521f46cfcf6e941c76|13     |\n",
      "|0021a87d4997a48b6cef1665602be0f5|14     |\n",
      "|00250175f79f584c14ab5cecd80553cd|15     |\n",
      "|002552c0663708129c0019cc97552d7d|16     |\n",
      "|002959d7a0b0990fe2d69988affcbc80|17     |\n",
      "|002af88741ba70c7b5cf4e4a0ad7ef85|18     |\n",
      "|002c6dab60557c48cfd6c2222ef7fd76|19     |\n",
      "|002d4ea7c04739c130bb74d7e7cd1694|20     |\n",
      "+--------------------------------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Make new id for each products named \"item_id\" using row_number()\n",
    "df_prep1 = spark.sql(\"SELECT `product_id`, ROW_NUMBER() OVER (ORDER BY `product_id`) AS `item_id` \\\n",
    "                    FROM product\")\n",
    "df_prep1.show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Register the dataframe as a SQL temporary view\n",
    "df_prep1.createOrReplaceTempView(\"product_prep\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------------------+---+------------+\n",
      "|order_id                        |id |num_of_items|\n",
      "+--------------------------------+---+------------+\n",
      "|00010242fe8c5a6d1ba2dd792cb16214|1  |1           |\n",
      "|00018f77f2f0320c557190d7a144bdd3|2  |1           |\n",
      "|000229ec398224ef6ca0657da4fc703e|3  |1           |\n",
      "|00024acbcdf0a6daa1e931b038114c75|4  |1           |\n",
      "|00042b26cf59d7ce69dfabb4e55b4fd9|5  |1           |\n",
      "|00048cc3ae777c65dbb7d2a0634bc1ea|6  |1           |\n",
      "|00054e8431b9d7675808bcb819fb4a32|7  |1           |\n",
      "|000576fe39319847cbb9d288c5617fa6|8  |1           |\n",
      "|0005a1a1728c9d785b8e2b08b904576c|9  |1           |\n",
      "|0005f50442cb953dcd1d21e1fb923495|10 |1           |\n",
      "|00061f2a7bc09da83e415a52dc8a4af1|11 |1           |\n",
      "|00063b381e2406b52ad429470734ebd5|12 |1           |\n",
      "|0006ec9db01a64e59a68b2c340bf65a7|13 |1           |\n",
      "|0008288aa423d2a3f00fcb17cd7d8719|14 |2           |\n",
      "|0009792311464db532ff765bf7b182ae|15 |1           |\n",
      "|0009c9a17f916a706d71784483a5d643|16 |1           |\n",
      "|000aed2e25dbad2f9ddb70584c5a2ded|17 |1           |\n",
      "|000c3e6612759851cc3cbb4b83257986|18 |1           |\n",
      "|000e562887b1f2006d75e0be9558292e|19 |1           |\n",
      "|000e63d38ae8c00bbcb5a30573b99628|20 |1           |\n",
      "+--------------------------------+---+------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Make new id for each order named \"id\" using row_number()\n",
    "df_prep2 = spark.sql(\"SELECT `order_id`, ROW_NUMBER() OVER (ORDER BY `order_id`) AS `id`, COUNT(`order_id`) AS `num_of_items` \\\n",
    "                    FROM order \\\n",
    "                    GROUP BY `order_id`\")\n",
    "\n",
    "df_prep2.show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Register the dataframe as a SQL temporary view\n",
    "df_prep2.createOrReplaceTempView(\"order_prep\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join the order and products dataframe by product_id, so its product_id converted to item_id \n",
    "# Then, group by \"id\" and \"item_id\" column, so the \"items\" will contain unique item_id\n",
    "df_prep3 = spark.sql(\"SELECT op.id, p.item_id \\\n",
    "                    FROM order AS o \\\n",
    "                    JOIN product_prep AS p \\\n",
    "                        ON o.product_id = p.product_id \\\n",
    "                    JOIN order_prep AS op \\\n",
    "                        ON o.order_id = op.order_id \\\n",
    "                    GROUP BY op.id, p.item_id \\\n",
    "                    ORDER BY op.id ASC\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: integer (nullable = true)\n",
      " |-- item_id: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Check the datatype\n",
    "df_prep3.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-------+\n",
      "| id|item_id|\n",
      "+---+-------+\n",
      "|  1|   8629|\n",
      "|  2|  29598|\n",
      "|  3|  25668|\n",
      "|  4|  15323|\n",
      "|  5|  22080|\n",
      "|  6|  30848|\n",
      "|  7|  18182|\n",
      "|  8|  11123|\n",
      "|  9|   6385|\n",
      "| 10|   9013|\n",
      "| 11|  27628|\n",
      "| 12|  31102|\n",
      "| 13|  19743|\n",
      "| 14|   7080|\n",
      "| 15|  18083|\n",
      "| 16|   8238|\n",
      "| 17|  10354|\n",
      "| 18|  23217|\n",
      "| 19|  12287|\n",
      "| 20|  11081|\n",
      "+---+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Import StringType\n",
    "from pyspark.sql.types import StringType\n",
    "\n",
    "# Convert the datatypes from int to string\n",
    "df_prep3 = df_prep3.withColumn('id', df_prep3.id.cast(StringType())).withColumn('item_id', df_prep3.item_id.cast(StringType()))\n",
    "    \n",
    "df_prep3.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- item_id: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# After converting the datatype\n",
    "df_prep3.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+--------------+\n",
      "|   id|         items|\n",
      "+-----+--------------+\n",
      "|10096|       [21884]|\n",
      "|10351|        [9274]|\n",
      "|10436|       [15473]|\n",
      "| 1090|        [8965]|\n",
      "|11078|       [22549]|\n",
      "|11332|       [12206]|\n",
      "|11563|       [24173]|\n",
      "| 1159|       [14100]|\n",
      "|11722|       [14509]|\n",
      "|11888|        [6985]|\n",
      "|12394|        [8685]|\n",
      "|12529|       [17667]|\n",
      "|12847|       [28719]|\n",
      "|13192|       [10894]|\n",
      "|13282|       [15155]|\n",
      "|13442|        [2798]|\n",
      "|13610|       [18717]|\n",
      "|13772|       [16240]|\n",
      "|13865|       [10623]|\n",
      "|14157|       [17176]|\n",
      "|14204|       [14751]|\n",
      "| 1436|       [23754]|\n",
      "|14369|       [11169]|\n",
      "|14838|       [14200]|\n",
      "|14887|       [28550]|\n",
      "|14899|       [32266]|\n",
      "| 1512|       [17303]|\n",
      "|15269|        [2917]|\n",
      "|15271|       [19554]|\n",
      "|15555|       [13485]|\n",
      "|15574|        [8614]|\n",
      "|15634|       [24356]|\n",
      "| 1572|       [32073]|\n",
      "|16250|       [27001]|\n",
      "|16320|        [4076]|\n",
      "|16504|        [6682]|\n",
      "|16576|[20538, 25213]|\n",
      "|16974|        [2715]|\n",
      "|17401|        [3289]|\n",
      "|17427|       [26832]|\n",
      "|17506|       [12578]|\n",
      "|17686|       [11227]|\n",
      "|17714|        [7649]|\n",
      "|18130|       [20610]|\n",
      "|18306|       [29359]|\n",
      "|18314|       [30170]|\n",
      "|18333| [3130, 19301]|\n",
      "|18509|        [2621]|\n",
      "|18556|       [23074]|\n",
      "|18574|       [24046]|\n",
      "|18634|       [12299]|\n",
      "|18726|[17573, 26760]|\n",
      "|18947| [20529, 5742]|\n",
      "|18992|       [24546]|\n",
      "|19095|       [10751]|\n",
      "|19132|       [17709]|\n",
      "|19338|       [30605]|\n",
      "|20158|        [8709]|\n",
      "|20219|        [7080]|\n",
      "|20428|        [8204]|\n",
      "|20512|        [5824]|\n",
      "|20569|       [19089]|\n",
      "| 2069|       [10910]|\n",
      "|20868|       [21716]|\n",
      "| 2088|        [9233]|\n",
      "|21248|       [18949]|\n",
      "|21249|        [8946]|\n",
      "|21259|       [17887]|\n",
      "|21331|        [4360]|\n",
      "| 2136|        [6163]|\n",
      "|21452|       [12303]|\n",
      "| 2162|       [17810]|\n",
      "|21783|        [2944]|\n",
      "|21889|       [10377]|\n",
      "|21894|       [20987]|\n",
      "|22121|       [10108]|\n",
      "|22254|       [13031]|\n",
      "|22596|       [23410]|\n",
      "|22728|       [16671]|\n",
      "| 2294|        [5294]|\n",
      "|23097|       [23364]|\n",
      "|23318|       [12307]|\n",
      "|23459|       [18361]|\n",
      "|23843|        [9406]|\n",
      "|23918|       [13745]|\n",
      "|24114|       [29885]|\n",
      "|24269|       [31982]|\n",
      "|24504|        [3414]|\n",
      "|25032|       [21303]|\n",
      "|25555|        [7365]|\n",
      "|25894|         [567]|\n",
      "|25912|       [15682]|\n",
      "|25969|       [12187]|\n",
      "|26005|       [27040]|\n",
      "|26082|       [16087]|\n",
      "|26112|       [23612]|\n",
      "|26439|       [17485]|\n",
      "|27108|       [11308]|\n",
      "|27248|       [21065]|\n",
      "|27264|[19966, 21741]|\n",
      "+-----+--------------+\n",
      "only showing top 100 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Import functions sql\n",
    "from pyspark.sql import functions as F\n",
    "\n",
    "# Group by \"id\" and merge/combine the \"item_id\" column to array in \"items\" column\n",
    "df_prep = df_prep3.groupBy(\"id\").agg(F.collect_list(F.col(\"item_id\")).alias(\"items\"))\n",
    "\n",
    "df_prep.show(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "98666"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_prep.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 FP-Growth Algorithm\n",
    "Make at least 3 models with different minimum support and minimum confidence values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import FPGrowth \n",
    "from pyspark.ml.fpm import FPGrowth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Initial testing\n",
    "fpGrowth1 = FPGrowth(itemsCol=\"items\", minSupport=0.5, minConfidence=0.6)\n",
    "model1 = fpGrowth1.fit(df_prep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+----+\n",
      "|items|freq|\n",
      "+-----+----+\n",
      "+-----+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display frequent itemsets.\n",
    "model1.freqItemsets.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. Model 1 doesn't display any results, so I decrease the min support and min confidence\n",
    "fpGrowth2 = FPGrowth(itemsCol=\"items\", minSupport=0.1, minConfidence=0.2)\n",
    "model2 = fpGrowth2.fit(df_prep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+----+\n",
      "|items|freq|\n",
      "+-----+----+\n",
      "+-----+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display frequent itemsets.\n",
    "model2.freqItemsets.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. Model 2 doesn't display any results, so I decrease the min support and min confidence\n",
    "fpGrowth3 = FPGrowth(itemsCol=\"items\", minSupport=0.0005, minConfidence=0.0005)\n",
    "model3 = fpGrowth3.fit(df_prep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----+\n",
      "|  items|freq|\n",
      "+-------+----+\n",
      "|[19743]| 467|\n",
      "|[22113]| 431|\n",
      "| [8614]| 352|\n",
      "|[27040]| 323|\n",
      "| [7365]| 311|\n",
      "|[10868]| 306|\n",
      "| [7080]| 291|\n",
      "|[10841]| 287|\n",
      "| [2795]| 269|\n",
      "| [5693]| 259|\n",
      "| [8052]| 255|\n",
      "|[16087]| 225|\n",
      "|[11747]| 194|\n",
      "|[28971]| 194|\n",
      "|[24087]| 187|\n",
      "|[21303]| 172|\n",
      "| [8685]| 160|\n",
      "|[21672]| 158|\n",
      "|[29514]| 156|\n",
      "| [6980]| 156|\n",
      "+-------+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display frequent itemsets.\n",
    "model3.freqItemsets.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----------+----------+----+\n",
      "|antecedent|consequent|confidence|lift|\n",
      "+----------+----------+----------+----+\n",
      "+----------+----------+----------+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display generated association rules.\n",
    "model3.associationRules.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4. Model 3 display results but doesn't display generated association rules\n",
    "fpGrowth4 = FPGrowth(itemsCol=\"items\", minSupport=0.00005, minConfidence=0.00005)\n",
    "model4 = fpGrowth4.fit(df_prep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----+\n",
      "|  items|freq|\n",
      "+-------+----+\n",
      "|[19907]|  42|\n",
      "|[17443]|   5|\n",
      "|[25857]|  26|\n",
      "| [7413]|   8|\n",
      "| [1420]|  20|\n",
      "|[16023]|   8|\n",
      "| [2247]|   7|\n",
      "|[24026]|   5|\n",
      "|[16695]|   5|\n",
      "| [2966]|  16|\n",
      "|[15686]|  14|\n",
      "|[31869]|  12|\n",
      "|[30822]|   7|\n",
      "| [5242]|   6|\n",
      "| [3804]|   6|\n",
      "|[18514]|  11|\n",
      "|[19409]|  10|\n",
      "| [7728]|   6|\n",
      "| [2915]|   9|\n",
      "|[12862]|   5|\n",
      "+-------+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display frequent itemsets.\n",
    "model4.freqItemsets.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----------+--------------------+------------------+\n",
      "|antecedent|consequent|          confidence|              lift|\n",
      "+----------+----------+--------------------+------------------+\n",
      "|   [29569]|   [11885]| 0.17647058823529413| 829.1260504201681|\n",
      "|    [8228]|    [7132]| 0.14814814814814814|131.68635301968632|\n",
      "|    [3200]|   [28328]|  0.4117647058823529| 625.0334841628959|\n",
      "|   [19743]|    [6980]| 0.06209850107066381| 39.27570965793664|\n",
      "|   [19743]|   [31285]| 0.01284796573875803|45.273478127867854|\n",
      "|   [12405]|   [12115]|  0.3157894736842105|1947.3552631578946|\n",
      "|    [8614]|    [7365]|             0.03125|  9.91418810289389|\n",
      "|    [8614]|   [10841]|0.019886363636363636| 6.836613082039912|\n",
      "|    [8614]|    [1518]|0.017045454545454544| 16.81806818181818|\n",
      "|   [10377]|   [31536]| 0.19101123595505617| 336.5413322632424|\n",
      "|    [6980]|   [19743]|  0.1858974358974359| 39.27570965793664|\n",
      "|    [7365]|    [8614]| 0.03536977491961415|  9.91418810289389|\n",
      "|    [7365]|    [7080]| 0.01607717041800643| 5.451099987845438|\n",
      "|    [7365]|   [10841]|0.028938906752411574|  9.94873231231164|\n",
      "|   [23594]|    [7931]|                 0.5|          6166.625|\n",
      "|   [28829]|   [21166]| 0.14705882352941177| 174.8157335223246|\n",
      "|    [1746]|   [30685]| 0.09523809523809523|408.55486542443066|\n",
      "|   [19637]|     [761]| 0.15789473684210525| 623.1536842105263|\n",
      "|   [12115]|   [12405]|               0.375|1947.3552631578946|\n",
      "|   [22185]|   [19045]|                 0.5| 3288.866666666667|\n",
      "+----------+----------+--------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display generated association rules.\n",
    "model4.associationRules.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+--------------+-------------------+\n",
      "|id   |items         |prediction         |\n",
      "+-----+--------------+-------------------+\n",
      "|10096|[21884]       |[]                 |\n",
      "|10351|[9274]        |[]                 |\n",
      "|10436|[15473]       |[]                 |\n",
      "|1090 |[8965]        |[]                 |\n",
      "|11078|[22549]       |[]                 |\n",
      "|11332|[12206]       |[]                 |\n",
      "|11563|[24173]       |[]                 |\n",
      "|1159 |[14100]       |[]                 |\n",
      "|11722|[14509]       |[]                 |\n",
      "|11888|[6985]        |[]                 |\n",
      "|12394|[8685]        |[]                 |\n",
      "|12529|[17667]       |[]                 |\n",
      "|12847|[28719]       |[]                 |\n",
      "|13192|[10894]       |[]                 |\n",
      "|13282|[15155]       |[]                 |\n",
      "|13442|[2798]        |[]                 |\n",
      "|13610|[18717]       |[]                 |\n",
      "|13772|[16240]       |[]                 |\n",
      "|13865|[10623]       |[]                 |\n",
      "|14157|[17176]       |[]                 |\n",
      "|14204|[14751]       |[]                 |\n",
      "|1436 |[23754]       |[]                 |\n",
      "|14369|[11169]       |[]                 |\n",
      "|14838|[14200]       |[]                 |\n",
      "|14887|[28550]       |[]                 |\n",
      "|14899|[32266]       |[]                 |\n",
      "|1512 |[17303]       |[]                 |\n",
      "|15269|[2917]        |[]                 |\n",
      "|15271|[19554]       |[]                 |\n",
      "|15555|[13485]       |[]                 |\n",
      "|15574|[8614]        |[7365, 10841, 1518]|\n",
      "|15634|[24356]       |[]                 |\n",
      "|1572 |[32073]       |[]                 |\n",
      "|16250|[27001]       |[]                 |\n",
      "|16320|[4076]        |[]                 |\n",
      "|16504|[6682]        |[]                 |\n",
      "|16576|[20538, 25213]|[]                 |\n",
      "|16974|[2715]        |[]                 |\n",
      "|17401|[3289]        |[]                 |\n",
      "|17427|[26832]       |[]                 |\n",
      "|17506|[12578]       |[]                 |\n",
      "|17686|[11227]       |[]                 |\n",
      "|17714|[7649]        |[]                 |\n",
      "|18130|[20610]       |[]                 |\n",
      "|18306|[29359]       |[]                 |\n",
      "|18314|[30170]       |[]                 |\n",
      "|18333|[3130, 19301] |[]                 |\n",
      "|18509|[2621]        |[]                 |\n",
      "|18556|[23074]       |[]                 |\n",
      "|18574|[24046]       |[]                 |\n",
      "+-----+--------------+-------------------+\n",
      "only showing top 50 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Transform examines the input items against all the association rules and summarize the\n",
    "# Consequents as prediction\n",
    "model4.transform(df_prep).show(50, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 5. I'll try once more by decreasing min support and min confidence more\n",
    "fpGrowth5 = FPGrowth(itemsCol=\"items\", minSupport=0.000005, minConfidence=0.000005)\n",
    "model5 = fpGrowth5.fit(df_prep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+----+\n",
      "|         items|freq|\n",
      "+--------------+----+\n",
      "|       [16017]|   1|\n",
      "|       [19907]|  42|\n",
      "| [19907, 8113]|   1|\n",
      "|[19907, 19743]|   1|\n",
      "|       [15497]|   1|\n",
      "|       [12694]|   2|\n",
      "|       [20648]|   1|\n",
      "|         [661]|   2|\n",
      "|        [7036]|   2|\n",
      "|        [3803]|   1|\n",
      "|       [17443]|   5|\n",
      "|       [27285]|   1|\n",
      "|       [18575]|   4|\n",
      "|        [5144]|   2|\n",
      "|       [12085]|   1|\n",
      "|        [5979]|   1|\n",
      "|       [12028]|   1|\n",
      "|       [32186]|   1|\n",
      "|        [2706]|   1|\n",
      "|        [2035]|   1|\n",
      "+--------------+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display frequent itemsets.\n",
    "model5.freqItemsets.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+----------+------------------+------------------+\n",
      "|          antecedent|consequent|        confidence|              lift|\n",
      "+--------------------+----------+------------------+------------------+\n",
      "|[1810, 6329, 1214...|    [4493]|               1.0|           49333.0|\n",
      "|[1810, 6329, 1214...|   [13390]|               1.0|           49333.0|\n",
      "|[1810, 6329, 1214...|    [2150]|               1.0|32888.666666666664|\n",
      "|[1810, 6329, 1214...|    [1686]|               1.0|           49333.0|\n",
      "|               [706]|   [11366]|               1.0|           98666.0|\n",
      "|[2791, 28204, 19052]|    [1858]|               1.0|           98666.0|\n",
      "|[2791, 28204, 19052]|    [9545]|               1.0|           49333.0|\n",
      "|[2791, 28204, 19052]|   [18100]|               1.0|           49333.0|\n",
      "|[2791, 28204, 19052]|   [12083]|               1.0|           98666.0|\n",
      "|             [25940]|   [31413]|0.3333333333333333|32888.666666666664|\n",
      "|  [1858, 2791, 9545]|   [19052]|               1.0|32888.666666666664|\n",
      "|  [1858, 2791, 9545]|   [18100]|               1.0|           49333.0|\n",
      "|  [1858, 2791, 9545]|   [28204]|               1.0|           49333.0|\n",
      "|  [1858, 2791, 9545]|   [12083]|               1.0|           98666.0|\n",
      "|[25454, 32097, 31...|   [24169]|               1.0|           49333.0|\n",
      "|       [7121, 11510]|   [22620]|               1.0|16444.333333333332|\n",
      "|[4611, 29713, 20214]|   [19419]|               1.0|           98666.0|\n",
      "|[4611, 29713, 20214]|   [26134]|               1.0|           49333.0|\n",
      "|[4611, 29713, 20214]|    [2871]|               1.0|           98666.0|\n",
      "|       [4726, 32412]|   [19045]|               1.0| 6577.733333333334|\n",
      "+--------------------+----------+------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display generated association rules.\n",
    "model5.associationRules.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+--------------+----------------------------------------------------------------------------------------------+\n",
      "|id   |items         |prediction                                                                                    |\n",
      "+-----+--------------+----------------------------------------------------------------------------------------------+\n",
      "|10096|[21884]       |[28967, 1307, 18103]                                                                          |\n",
      "|10351|[9274]        |[]                                                                                            |\n",
      "|10436|[15473]       |[]                                                                                            |\n",
      "|1090 |[8965]        |[12118, 26832, 21009]                                                                         |\n",
      "|11078|[22549]       |[9796]                                                                                        |\n",
      "|11332|[12206]       |[]                                                                                            |\n",
      "|11563|[24173]       |[]                                                                                            |\n",
      "|1159 |[14100]       |[]                                                                                            |\n",
      "|11722|[14509]       |[]                                                                                            |\n",
      "|11888|[6985]        |[]                                                                                            |\n",
      "|12394|[8685]        |[32732, 6507]                                                                                 |\n",
      "|12529|[17667]       |[]                                                                                            |\n",
      "|12847|[28719]       |[]                                                                                            |\n",
      "|13192|[10894]       |[27129]                                                                                       |\n",
      "|13282|[15155]       |[]                                                                                            |\n",
      "|13442|[2798]        |[]                                                                                            |\n",
      "|13610|[18717]       |[]                                                                                            |\n",
      "|13772|[16240]       |[]                                                                                            |\n",
      "|13865|[10623]       |[]                                                                                            |\n",
      "|14157|[17176]       |[7440, 1706, 3231, 13043, 19910, 26309, 8113]                                                 |\n",
      "|14204|[14751]       |[]                                                                                            |\n",
      "|1436 |[23754]       |[]                                                                                            |\n",
      "|14369|[11169]       |[]                                                                                            |\n",
      "|14838|[14200]       |[]                                                                                            |\n",
      "|14887|[28550]       |[12077, 5817, 17533, 163, 2741]                                                               |\n",
      "|14899|[32266]       |[]                                                                                            |\n",
      "|1512 |[17303]       |[]                                                                                            |\n",
      "|15269|[2917]        |[]                                                                                            |\n",
      "|15271|[19554]       |[]                                                                                            |\n",
      "|15555|[13485]       |[24915]                                                                                       |\n",
      "|15574|[8614]        |[7365, 7080, 10841, 29001, 30305, 10152, 1518, 22670, 23979, 27248, 9879, 17622, 21370, 31467]|\n",
      "|15634|[24356]       |[]                                                                                            |\n",
      "|1572 |[32073]       |[274]                                                                                         |\n",
      "|16250|[27001]       |[]                                                                                            |\n",
      "|16320|[4076]        |[]                                                                                            |\n",
      "|16504|[6682]        |[17014, 5440]                                                                                 |\n",
      "|16576|[20538, 25213]|[10597, 25792, 18406, 14708, 6980, 26719, 22725]                                              |\n",
      "|16974|[2715]        |[4427]                                                                                        |\n",
      "|17401|[3289]        |[]                                                                                            |\n",
      "|17427|[26832]       |[1296, 9253, 8965, 25340, 32878, 26947]                                                       |\n",
      "|17506|[12578]       |[4516]                                                                                        |\n",
      "|17686|[11227]       |[12760, 10514, 9278]                                                                          |\n",
      "|17714|[7649]        |[]                                                                                            |\n",
      "|18130|[20610]       |[]                                                                                            |\n",
      "|18306|[29359]       |[]                                                                                            |\n",
      "|18314|[30170]       |[]                                                                                            |\n",
      "|18333|[3130, 19301] |[]                                                                                            |\n",
      "|18509|[2621]        |[]                                                                                            |\n",
      "|18556|[23074]       |[20205]                                                                                       |\n",
      "|18574|[24046]       |[]                                                                                            |\n",
      "+-----+--------------+----------------------------------------------------------------------------------------------+\n",
      "only showing top 50 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Transform examines the input items against all the association rules and summarize the\n",
    "# Consequents as prediction\n",
    "model5.transform(df_prep).show(50, truncate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 Example of I/O Product and Recomendation\n",
    "This Brazilian ecommerce public dataset of orders made at Olist Store doesn't has any product name data, so I just give the product ID as a result of recommendation output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a new dataframe as an input\n",
    "df_input = spark.createDataFrame([\n",
    "                ('0',['8614']),\n",
    "                ('1',['25668']),\n",
    "                ('2',['19743']),\n",
    "                ('3',['2791','28204','19052']),\n",
    "                ('4',['20538','25213'])\n",
    "            ],['id','items'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'll use two models to get the prediction as recommendation ouput and let's see the difference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+--------------------+-------------------+\n",
      "|id |items               |prediction         |\n",
      "+---+--------------------+-------------------+\n",
      "|0  |[8614]              |[7365, 10841, 1518]|\n",
      "|1  |[25668]             |[]                 |\n",
      "|2  |[19743]             |[6980, 31285]      |\n",
      "|3  |[2791, 28204, 19052]|[]                 |\n",
      "|4  |[20538, 25213]      |[]                 |\n",
      "+---+--------------------+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 1. Get the prediction from Model 4 (minSupport=0.00005, minConfidence=0.00005)\n",
    "model4.transform(df_input).show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "|id |items               |prediction                                                                                                                                            |\n",
      "+---+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "|0  |[8614]              |[7365, 7080, 10841, 29001, 30305, 10152, 1518, 22670, 23979, 27248, 9879, 17622, 21370, 31467]                                                        |\n",
      "|1  |[25668]             |[18836]                                                                                                                                               |\n",
      "|2  |[19743]             |[19907, 7573, 15938, 6980, 5441, 658, 30741, 30950, 7651, 31270, 10339, 1291, 32645, 7206, 20029, 14953, 6816, 30377, 31285, 22637, 8684, 8113, 19622]|\n",
      "|3  |[2791, 28204, 19052]|[1858, 9545, 18100, 12083, 16727]                                                                                                                     |\n",
      "|4  |[20538, 25213]      |[10597, 25792, 18406, 14708, 6980, 26719, 22725]                                                                                                      |\n",
      "+---+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 2. Get the prediction from Model 5 (minSupport=0.000005, minConfidence=0.000005)\n",
    "model5.transform(df_input).show(truncate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Conclusion\n",
    "Based on the test results above, we can conclude that the higher minimum support and minimum confidence of a model, the more accurate the prediction results are."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. References\n",
    "\n",
    "* https://spark.apache.org/docs/2.3.0/ml-frequent-pattern-mining.html\n",
    "* https://www.qubole.com/resources/pyspark-cheatsheet/\n",
    "* https://stackoverflow.com/"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}