Spark_process_2_TFIDF_NMF.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/keisukeirie/anaconda/lib/python2.7/site-packages/sklearn/cross_validation.py:44: DeprecationWarning: This module was deprecated in version 0.18 in favor of the model_selection module into which all the refactored classes and functions are moved. Also note that the interface of the new CV iterators are different from that of this module. This module will be removed in 0.20.\n",
      "  \"This module will be removed in 0.20.\", DeprecationWarning)\n",
      "/Users/keisukeirie/anaconda/lib/python2.7/site-packages/sklearn/grid_search.py:43: DeprecationWarning: This module was deprecated in version 0.18 in favor of the model_selection module into which all the refactored classes and functions are moved. This module will be removed in 0.20.\n",
      "  DeprecationWarning)\n"
     ]
    }
   ],
   "source": [
    "import pyspark as ps\n",
    "from pyspark import SparkConf, SparkContext\n",
    "from __future__ import unicode_literals\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import time\n",
    "import json\n",
    "import gzip\n",
    "import spacy\n",
    "%matplotlib inline\n",
    "np.random.seed(32113)\n",
    "import pickle\n",
    "from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer\n",
    "from sklearn.feature_extraction.stop_words import ENGLISH_STOP_WORDS\n",
    "from sklearn.decomposition import NMF\n",
    "from nltk.corpus import stopwords\n",
    "from spacy.en import English\n",
    "from sklearn.grid_search import GridSearchCV\n",
    "import string\n",
    "import data_prep_for_test_run as dp \n",
    "parser = English()\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql.functions import monotonically_increasing_id\n",
    "from pyspark.sql.functions import isnan, when, count, col\n",
    "import pyspark.sql.functions as pys_fun\n",
    "from pyspark.mllib.recommendation import ALS,Rating\n",
    "import math"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "hc = ps.HiveContext(sc)\n",
    "sql = ps.SQLContext(sc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = spark.read.csv('sparkprocess1.csv', header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4234, 111)\n"
     ]
    }
   ],
   "source": [
    "print((df.count(), len(df.columns)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### NOTES:\n",
    "I notice something. I realize that there are 25 samples where reviewText is null(!?).  \n",
    "I was not able to run TFIDF for this reason (or collect tokenized data)...  \n",
    "This happens whenever I ran data cleaning and store my data as csv file.  \n",
    "There seems to be some issue whenever I save or load my data.  \n",
    "Will investigate with other data format and see this issue persist.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing Null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4209"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(df.reviewText.isNotNull()).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(df.reviewText.isNull()).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+-------------------+--------------------+------------------+-----------+----+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+---+--------+-----------+-------+-------+--------+--------+-------------+\n",
      "|reviewText|overall|helpful_total_votes|num_of_helpful_votes|helpful_percentage|Text_length|asin|rank_values|num_category|Screen Protectors|PlayStation 4|PlayStation 3|PlayStation 2|Game Boy Advance|Joysticks|GameCube|Commodore Amiga|3DO|Cases & Protectors|More Systems|Sony PSP|LIVE|Sega Master System|Fitness Accessories|Subscription Cards|Points & Currency Cards|Atari 2600|Controllers|Light Guns|Nintendo 64|Nintendo 3DS|Linux Games|Accessories|Skins|Steering Wheels|Racing Wheels|PlayStation|Networking|Atari 7800|Xbox 360|Chargers|Kids & Family|ColecoVision|Gamepads|Digital Games & DLC| PC|Consoles|Game Boy Color|Nintendo NES|Drums|MMO & Free-to-Play Games|Xbox|Sega CD|Faceplates|Game Boy|Adapters|Batteries|Fire TV|Sega Genesis|Speakers|Batteries & Chargers|Mac Game Downloads|Sega Game Gear|PDAs|Sega Dreamcast|Remotes|Xbox One|Dance Mats|Atari 5200|Keyboards|Downloadable Content|Cables & Adapters|Guitars|Commodore 64|Microphones|Wii U|Headsets|Atari Lynx|Accessory Kits|Casual Games|Interactive Gaming Figures|Memory|Intellivision|NEOGEO Pocket|Nintendo DS|TurboGrafx 16|PlayStation Vita|Video Games|Digital Games|PC Game Downloads|Mac|Sensor Bars|Atari Jaguar|Flight Controls|Currency Cards|Games|Super Nintendo|Wii|Hardware|Gaming Mice|Sega Saturn|Cables|Gaming Keyboards| id|software|Video_Games|below20|below50|below100|below300|price_unknown|\n",
      "+----------+-------+-------------------+--------------------+------------------+-----------+----+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+---+--------+-----------+-------+-------+--------+--------+-------------+\n",
      "|        25|      0|                  0|                   0|                 0|          0|   0|          0|           0|                0|            0|            0|            0|               0|        0|       0|              0|  0|                 0|           0|       0|   0|                 0|                  0|                 0|                      0|         0|          0|         0|          0|           0|          0|          0|    0|              0|            0|          0|         0|         0|       0|       0|            0|           0|       0|                  0|  0|       0|             0|           0|    0|                       0|   0|      0|         0|       0|       0|        0|      0|           0|       0|                   0|                 0|             0|   0|             0|      0|       0|         0|         0|        0|                   0|                0|      0|           0|          0|    0|       0|         0|             0|           0|                         0|     0|            0|            0|          0|            0|               0|          0|            0|                0|  0|          0|           0|              0|             0|    0|             0|  0|       0|          0|          0|     0|               0|  0|       0|          0|      0|      0|       0|       0|            0|\n",
      "+----------+-------+-------------------+--------------------+------------------+-----------+----+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+---+--------+-----------+-------+-------+--------+--------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df.columns]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.na.drop(subset=[\"reviewText\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(df.reviewText.isNull()).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Columns that only exist in the pyspark ver. of my data\n",
    "For some reason, I have more category features that were not in my original model.  \n",
    "I double checked the code but I was not able to find what caused this.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['reviewText',\n",
       " 'overall',\n",
       " 'helpful_total_votes',\n",
       " 'num_of_helpful_votes',\n",
       " 'helpful_percentage',\n",
       " 'Text_length',\n",
       " 'asin',\n",
       " 'rank_values',\n",
       " 'num_category',\n",
       " 'Screen Protectors',\n",
       " 'PlayStation 4',\n",
       " 'PlayStation 3',\n",
       " 'PlayStation 2',\n",
       " 'Game Boy Advance',\n",
       " 'Joysticks',\n",
       " 'GameCube',\n",
       " 'Commodore Amiga',\n",
       " '3DO',\n",
       " 'Cases & Protectors',\n",
       " 'More Systems',\n",
       " 'Sony PSP',\n",
       " 'LIVE',\n",
       " 'Sega Master System',\n",
       " 'Fitness Accessories',\n",
       " 'Subscription Cards',\n",
       " 'Points & Currency Cards',\n",
       " 'Atari 2600',\n",
       " 'Controllers',\n",
       " 'Light Guns',\n",
       " 'Nintendo 64',\n",
       " 'Nintendo 3DS',\n",
       " 'Linux Games',\n",
       " 'Accessories',\n",
       " 'Skins',\n",
       " 'Steering Wheels',\n",
       " 'Racing Wheels',\n",
       " 'PlayStation',\n",
       " 'Networking',\n",
       " 'Atari 7800',\n",
       " 'Xbox 360',\n",
       " 'Chargers',\n",
       " 'Kids & Family',\n",
       " 'ColecoVision',\n",
       " 'Gamepads',\n",
       " 'Digital Games & DLC',\n",
       " 'PC',\n",
       " 'Consoles',\n",
       " 'Game Boy Color',\n",
       " 'Nintendo NES',\n",
       " 'Drums',\n",
       " 'MMO & Free-to-Play Games',\n",
       " 'Xbox',\n",
       " 'Sega CD',\n",
       " 'Faceplates',\n",
       " 'Game Boy',\n",
       " 'Adapters',\n",
       " 'Batteries',\n",
       " 'Fire TV',\n",
       " 'Sega Genesis',\n",
       " 'Speakers',\n",
       " 'Batteries & Chargers',\n",
       " 'Mac Game Downloads',\n",
       " 'Sega Game Gear',\n",
       " 'PDAs',\n",
       " 'Sega Dreamcast',\n",
       " 'Remotes',\n",
       " 'Xbox One',\n",
       " 'Dance Mats',\n",
       " 'Atari 5200',\n",
       " 'Keyboards',\n",
       " 'Downloadable Content',\n",
       " 'Cables & Adapters',\n",
       " 'Guitars',\n",
       " 'Commodore 64',\n",
       " 'Microphones',\n",
       " 'Wii U',\n",
       " 'Headsets',\n",
       " 'Atari Lynx',\n",
       " 'Accessory Kits',\n",
       " 'Casual Games',\n",
       " 'Interactive Gaming Figures',\n",
       " 'Memory',\n",
       " 'Intellivision',\n",
       " 'NEOGEO Pocket',\n",
       " 'Nintendo DS',\n",
       " 'TurboGrafx 16',\n",
       " 'PlayStation Vita',\n",
       " 'Video Games',\n",
       " 'Digital Games',\n",
       " 'PC Game Downloads',\n",
       " 'Mac',\n",
       " 'Sensor Bars',\n",
       " 'Atari Jaguar',\n",
       " 'Flight Controls',\n",
       " 'Currency Cards',\n",
       " 'Games',\n",
       " 'Super Nintendo',\n",
       " 'Wii',\n",
       " 'Hardware',\n",
       " 'Gaming Mice',\n",
       " 'Sega Saturn',\n",
       " 'Cables',\n",
       " 'Gaming Keyboards',\n",
       " 'id',\n",
       " 'software',\n",
       " 'Video_Games',\n",
       " 'below20',\n",
       " 'below50',\n",
       " 'below100',\n",
       " 'below300',\n",
       " 'price_unknown']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.schema.names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns_from_orig_ver = [u'reviewerID', u'asin', u'reviewerName', u'reviewText', u'overall',\n",
    "       u'summary', u'helpful_total_review', u'num_of_helpful_review',\n",
    "       u'helpful_percent', u'text_length', u'price', u'rank_values',\n",
    "       u'num category', u'Sony PSP', u'PlayStation', u'LIVE', u'PlayStation 3',\n",
    "       u'PlayStation 2', u'Game Boy Advance', u'Xbox 360', u'Joysticks',\n",
    "       u'GameCube', u'PC Game Downloads', u'Chargers', u'Kids & Family',\n",
    "       u'Remotes', u'Memory', u'Gamepads', u'Networking',\n",
    "       u'Digital Games & DLC', u'Nintendo DS', u'Cases & Protectors',\n",
    "       u'More Systems', u'PlayStation Vita', u'Adapters', u'Digital Games',\n",
    "       u'Mac', u'PC', u'Consoles', u'Game Boy Color', u'Dance Mats',\n",
    "       u'Nintendo NES', u'Drums', u'Interactive Gaming Figures', u'Xbox One',\n",
    "       u'Screen Protectors', u'Mac Game Downloads', u'Downloadable Content',\n",
    "       u'Fitness Accessories', u'MMO & Free-to-Play Games',\n",
    "       u'Subscription Cards', u'Points & Currency Cards', u'Flight Controls',\n",
    "       u'Currency Cards', u'Xbox', u'Controllers', u'Cables & Adapters',\n",
    "       u'Games', u'Batteries & Chargers', u'Game Boy', u'Light Guns',\n",
    "       u'Nintendo 64', u'PlayStation 4', u'Super Nintendo', u'Guitars',\n",
    "       u'Wii U', u'Nintendo 3DS', u'Steering Wheels', u'Headsets',\n",
    "       u'Accessories', u'Wii', u'Skins', u'Hardware', u'Linux Games',\n",
    "       u'Batteries', u'Gaming Mice', u'Sega Genesis', u'Accessory Kits',\n",
    "       u'Fire TV', u'Cables', u'Gaming Keyboards', u'Casual Games',\n",
    "       u'Racing Wheels', u'Software', u'Video Games']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['helpful_total_votes',\n",
       " 'num_of_helpful_votes',\n",
       " 'helpful_percentage',\n",
       " 'Text_length',\n",
       " 'num_category',\n",
       " 'Commodore Amiga',\n",
       " '3DO',\n",
       " 'Sega Master System',\n",
       " 'Atari 2600',\n",
       " 'Atari 7800',\n",
       " 'ColecoVision',\n",
       " 'Sega CD',\n",
       " 'Faceplates',\n",
       " 'Speakers',\n",
       " 'Sega Game Gear',\n",
       " 'PDAs',\n",
       " 'Sega Dreamcast',\n",
       " 'Atari 5200',\n",
       " 'Keyboards',\n",
       " 'Commodore 64',\n",
       " 'Microphones',\n",
       " 'Atari Lynx',\n",
       " 'Intellivision',\n",
       " 'NEOGEO Pocket',\n",
       " 'TurboGrafx 16',\n",
       " 'Sensor Bars',\n",
       " 'Atari Jaguar',\n",
       " 'Sega Saturn',\n",
       " 'id',\n",
       " 'software',\n",
       " 'Video_Games',\n",
       " 'below20',\n",
       " 'below50',\n",
       " 'below100',\n",
       " 'below300',\n",
       " 'price_unknown']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#columns that only exist in the pyspark data after data cleaning.\n",
    "[co for co in df.schema.names if co not in columns_from_orig_ver]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tokenizer and Stopword in Spark\n",
    "Here, I am applying Tokenizer and stopword to my review contents.  \n",
    "I realize that Spark tokenizer does not lemmatize/tokenize word accurately unlike SpaCy that I used previously.  \n",
    "So I played around with it and decided to do following process:\n",
    "1. tokenize review text.  \n",
    "2. use stopwordremover to remove custom stopword I created with NLTK, SKlearn Stopwords.  \n",
    "3. run stopwordremover 1 more time. This time, run it with the spark stopword.  \n",
    "  \n",
    "I also realize that running tokenizer does not take good care of special symbols (!, ., ?, etc) so I repeated the same procedure with RegexTokenizer with Pattern set as \\\\W+  \n",
    "this should get rid of all special character that is in review contents.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pyspark.ml.feature import Tokenizer,RegexTokenizer\n",
    "from pyspark.ml.feature import StopWordsRemover"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#ran both tokenizer and regextokenizer\n",
    "tokenizer = Tokenizer(inputCol=\"reviewText\", outputCol=\"words\")\n",
    "tokenized = tokenizer.transform(df)\n",
    "\n",
    "#the regextokenizer here takes out any non-word character from a token.\n",
    "#for instance, Spoiler! --> spoiler\n",
    "regtokenizer = RegexTokenizer(inputCol=\"reviewText\", outputCol=\"words\", pattern=\"\\\\W+\")\n",
    "reg = regtokenizer.transform(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|               words|\n",
      "+--------------------+\n",
      "|[this, does, not,...|\n",
      "|[my, son, has, be...|\n",
      "|[unfortunately, i...|\n",
      "|[animal, crossing...|\n",
      "|[got, this, game,...|\n",
      "|[i'm, going, to, ...|\n",
      "|[the, all, new, s...|\n",
      "|[the, starter, bu...|\n",
      "|[atgames, is, a, ...|\n",
      "|[i've, had, grand...|\n",
      "|[**update:, many,...|\n",
      "|[no, spoilers!, r...|\n",
      "|[as, you'll, prob...|\n",
      "|[i've, been, an, ...|\n",
      "|[when, call, of, ...|\n",
      "|[it's, amazing, w...|\n",
      "|[imagine, you, ha...|\n",
      "|[in, 2005,, f.e.a...|\n",
      "|[while, the, past...|\n",
      "|[we, got, our, ha...|\n",
      "+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tokenized.select('words').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#my STOPLIST from the original project. mix NLTK and SKlearn english stopwords.\n",
    "STOPLIST = list(set(stopwords.words('english') + [\"n't\", \"'s\", \"'m\", \"ca\"] + \\\n",
    "                list(ENGLISH_STOP_WORDS))) +\\\n",
    "                 \" \".join(string.punctuation).split(\" \") + \\\n",
    "                 [\"-----\", \"---\", \"...\", \"..\", \"....\", \"\", \" \", \"\\n\", \"\\n\\n\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stopwordremove = StopWordsRemover(inputCol=\"words\", outputCol=\"tokenized_filt\" ,stopWords=STOPLIST)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#applying customized stopwordlist to tokenized words\n",
    "tokenized2 = stopwordremove.transform(tokenized)\n",
    "reg2 = stopwordremove.transform(reg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|      tokenized_filt|\n",
      "+--------------------+\n",
      "|[come, usb, hook,...|\n",
      "|[son, eagerly, an...|\n",
      "|[unfortunately, c...|\n",
      "|[animal, crossing...|\n",
      "|[got, game, day, ...|\n",
      "|[i'm, going, pref...|\n",
      "|[new, slimmer, ps...|\n",
      "|[starter, bundle,...|\n",
      "|[atgames, chinese...|\n",
      "|[i've, grand, sla...|\n",
      "|[**update:, revie...|\n",
      "|[spoilers!, read,...|\n",
      "|[you'll, probably...|\n",
      "|[i've, xbox, 360,...|\n",
      "|[duty, arrived, p...|\n",
      "|[it's, amazing, s...|\n",
      "|[imagine, wonderf...|\n",
      "|[2005,, f.e.a.r.,...|\n",
      "|[past, year,, we'...|\n",
      "|[got, hands, xbox...|\n",
      "+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tokenized2.select(\"tokenized_filt\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "you see that custom stopword is not working for words like i'm,it's,i've.  \n",
    "So I decided to run another stopwordremover.  \n",
    "This time, I am applying spark stopword by not specifying any custom stopwordlist.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "remove = StopWordsRemover(inputCol=\"tokenized_filt\", outputCol=\"tokenized\")\n",
    "tokenized3 = remove.transform(tokenized2)\n",
    "reg3 = remove.transform(reg2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|           tokenized|\n",
      "+--------------------+\n",
      "|[come, usb, hook,...|\n",
      "|[son, eagerly, an...|\n",
      "|[unfortunately, r...|\n",
      "|[animal, crossing...|\n",
      "|[got, game, day, ...|\n",
      "|[going, preface, ...|\n",
      "|[new, slimmer, ps...|\n",
      "|[starter, bundle,...|\n",
      "|[atgames, chinese...|\n",
      "|[grand, slam, ten...|\n",
      "|[**update:, revie...|\n",
      "|[spoilers!, read,...|\n",
      "|[probably, know,,...|\n",
      "|[xbox, 360, owner...|\n",
      "|[duty, arrived, p...|\n",
      "|[amazing, sony, t...|\n",
      "|[imagine, wonderf...|\n",
      "|[2005,, f.e.a.r.,...|\n",
      "|[past, year,, rea...|\n",
      "|[got, hands, xbox...|\n",
      "+--------------------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+--------------------+\n",
      "|           tokenized|\n",
      "+--------------------+\n",
      "|[come, usb, hook,...|\n",
      "|[son, eagerly, an...|\n",
      "|[unfortunately, r...|\n",
      "|[animal, crossing...|\n",
      "|[got, game, day, ...|\n",
      "|[going, preface, ...|\n",
      "|[new, slimmer, ps...|\n",
      "|[starter, bundle,...|\n",
      "|[atgames, chinese...|\n",
      "|[grand, slam, ten...|\n",
      "|[update, reviewer...|\n",
      "|[spoilers, read, ...|\n",
      "|[probably, know, ...|\n",
      "|[xbox, 360, owner...|\n",
      "|[duty, arrived, p...|\n",
      "|[amazing, sony, t...|\n",
      "|[imagine, wonderf...|\n",
      "|[2005, f, e, r, i...|\n",
      "|[past, year, real...|\n",
      "|[got, hands, xbox...|\n",
      "+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Comparison between normal tokenizer and regex tokenizer results.\n",
    "tokenized3.select('tokenized').show()\n",
    "reg3.select('tokenized').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "reg3=reg3.drop('words','tokenized_filt')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tokenizer result\n",
    "I would say that Regextokenizer is doing it's job the way I wanted.  \n",
    "Although the process of making tokenizer is super fast and easy, I wish lemmatizer and tokenizer were smarter like the one in SpaCy.  \n",
    "But again, I can not complain about how simply it is to run tokenizer process :-)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TFIDF MATRIX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pyspark.ml.feature import HashingTF, IDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#run TF first.\n",
    "hashingTF = HashingTF(inputCol=\"tokenized\", outputCol=\"TTT\", numFeatures=10)\n",
    "featurizedData = hashingTF.transform(reg3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(TTT=SparseVector(10, {0: 8.0, 1: 13.0, 2: 7.0, 3: 6.0, 4: 8.0, 5: 5.0, 6: 6.0, 7: 17.0, 8: 2.0, 9: 4.0}))]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "featurizedData.select('TTT').take(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#now apply IDF to the TF feature created above.\n",
    "idf = IDF(minDocFreq=2, inputCol=\"TTT\", outputCol=\"TFIDF_features\")\n",
    "idfModel = idf.fit(featurizedData)\n",
    "TFIDF_model = idfModel.transform(featurizedData)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TFIDF_model is the dataframe which contains TFIDF terms.  \n",
    "However, unlike, TFIDF matrix from SKlearn, the spark TFIDF terms are stored in a sparse vector.  \n",
    "So the TFIDF_features are consists of many sparse vectors (each sample has a vectors).   \n",
    "I will use this list of sparse vectors to run NMF.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NMF\n",
    "The hardest part about running NMF in Spark is that, It requires specific data format to run NMF.  \n",
    "The NMF hidden layers can be calculated from this function called ALS from mllib.recommendation class.  \n",
    "the input data format needs to be Rating matrix and so I first need to convert my sparse vectors to Rating matrix.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# I am cheating here. Since creating an id column is such a pain in my butt, I'm making my cust_id in pandas dataframe.\n",
    "# once I finish adding my custom id column in pandas, I convert that dataframe into spark/hc.dataframe.  \n",
    "t2 = TFIDF_model.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "features = t2\n",
    "features['new_id']=features.index\n",
    "features['cust_id'] = features['new_id']\n",
    "features = features.drop(['new_id','tokenized','TTT'],axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>reviewText</th>\n",
       "      <th>overall</th>\n",
       "      <th>helpful_total_votes</th>\n",
       "      <th>num_of_helpful_votes</th>\n",
       "      <th>helpful_percentage</th>\n",
       "      <th>Text_length</th>\n",
       "      <th>asin</th>\n",
       "      <th>rank_values</th>\n",
       "      <th>num_category</th>\n",
       "      <th>Screen Protectors</th>\n",
       "      <th>...</th>\n",
       "      <th>id</th>\n",
       "      <th>software</th>\n",
       "      <th>Video_Games</th>\n",
       "      <th>below20</th>\n",
       "      <th>below50</th>\n",
       "      <th>below100</th>\n",
       "      <th>below300</th>\n",
       "      <th>price_unknown</th>\n",
       "      <th>TFIDF_features</th>\n",
       "      <th>cust_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>This does not come with a USB hook up.  I'm pe...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>274</td>\n",
       "      <td>265</td>\n",
       "      <td>0.97</td>\n",
       "      <td>1053</td>\n",
       "      <td>B001FBHT34</td>\n",
       "      <td>131442</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>25769803779</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>(0.290237392645, 0.740153459094, 0.34419198738...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>My son has been eagerly anticipating this game...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>133</td>\n",
       "      <td>127</td>\n",
       "      <td>0.95</td>\n",
       "      <td>2678</td>\n",
       "      <td>B000EXW52O</td>\n",
       "      <td>21032</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>154618822659</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>(0.435356088968, 1.36643715525, 1.671789653, 0...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Unfortunately I can't review the product too m...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>799</td>\n",
       "      <td>497</td>\n",
       "      <td>0.62</td>\n",
       "      <td>4713</td>\n",
       "      <td>B00BGA9WK2</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>274877907016</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>(1.05211054834, 1.65111156259, 1.671789653, 0....</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Animal Crossing was always a sleeper hit on th...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>716</td>\n",
       "      <td>683</td>\n",
       "      <td>0.95</td>\n",
       "      <td>4694</td>\n",
       "      <td>B0009Z3MQK</td>\n",
       "      <td>1769</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>292057776131</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>(1.56002598547, 1.19563251084, 1.37676794953, ...</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Got this game the first day it came out and ha...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>205</td>\n",
       "      <td>16</td>\n",
       "      <td>0.08</td>\n",
       "      <td>93</td>\n",
       "      <td>B000FKBCX4</td>\n",
       "      <td>368</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>326417514596</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>(0.108839022242, 0.0, 0.0, 0.0, 0.0, 0.0311197...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 113 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          reviewText  overall  \\\n",
       "0  This does not come with a USB hook up.  I'm pe...      5.0   \n",
       "1  My son has been eagerly anticipating this game...      2.0   \n",
       "2  Unfortunately I can't review the product too m...      1.0   \n",
       "3  Animal Crossing was always a sleeper hit on th...      5.0   \n",
       "4  Got this game the first day it came out and ha...      5.0   \n",
       "\n",
       "   helpful_total_votes  num_of_helpful_votes  helpful_percentage  Text_length  \\\n",
       "0                  274                   265                0.97         1053   \n",
       "1                  133                   127                0.95         2678   \n",
       "2                  799                   497                0.62         4713   \n",
       "3                  716                   683                0.95         4694   \n",
       "4                  205                    16                0.08           93   \n",
       "\n",
       "         asin  rank_values  num_category  Screen Protectors   ...     \\\n",
       "0  B001FBHT34       131442             1                0.0   ...      \n",
       "1  B000EXW52O        21032             1                0.0   ...      \n",
       "2  B00BGA9WK2           13             4                0.0   ...      \n",
       "3  B0009Z3MQK         1769             2                0.0   ...      \n",
       "4  B000FKBCX4          368             2                0.0   ...      \n",
       "\n",
       "             id  software  Video_Games  below20  below50  below100  below300  \\\n",
       "0   25769803779         0            1        1        0         0         0   \n",
       "1  154618822659         0            1        0        0         1         0   \n",
       "2  274877907016         0            1        0        1         0         0   \n",
       "3  292057776131         0            1        0        1         0         0   \n",
       "4  326417514596         0            1        0        1         0         0   \n",
       "\n",
       "   price_unknown                                     TFIDF_features  cust_id  \n",
       "0              0  (0.290237392645, 0.740153459094, 0.34419198738...        0  \n",
       "1              0  (0.435356088968, 1.36643715525, 1.671789653, 0...        1  \n",
       "2              0  (1.05211054834, 1.65111156259, 1.671789653, 0....        2  \n",
       "3              0  (1.56002598547, 1.19563251084, 1.37676794953, ...        3  \n",
       "4              0  (0.108839022242, 0.0, 0.0, 0.0, 0.0, 0.0311197...        4  \n",
       "\n",
       "[5 rows x 113 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "id_df = hc.createDataFrame(features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-------+-------------------+--------------------+------------------+-----------+----------+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+-------------+--------+-----------+-------+-------+--------+--------+-------------+--------------------+-------+\n",
      "|          reviewText|overall|helpful_total_votes|num_of_helpful_votes|helpful_percentage|Text_length|      asin|rank_values|num_category|Screen Protectors|PlayStation 4|PlayStation 3|PlayStation 2|Game Boy Advance|Joysticks|GameCube|Commodore Amiga|3DO|Cases & Protectors|More Systems|Sony PSP|LIVE|Sega Master System|Fitness Accessories|Subscription Cards|Points & Currency Cards|Atari 2600|Controllers|Light Guns|Nintendo 64|Nintendo 3DS|Linux Games|Accessories|Skins|Steering Wheels|Racing Wheels|PlayStation|Networking|Atari 7800|Xbox 360|Chargers|Kids & Family|ColecoVision|Gamepads|Digital Games & DLC| PC|Consoles|Game Boy Color|Nintendo NES|Drums|MMO & Free-to-Play Games|Xbox|Sega CD|Faceplates|Game Boy|Adapters|Batteries|Fire TV|Sega Genesis|Speakers|Batteries & Chargers|Mac Game Downloads|Sega Game Gear|PDAs|Sega Dreamcast|Remotes|Xbox One|Dance Mats|Atari 5200|Keyboards|Downloadable Content|Cables & Adapters|Guitars|Commodore 64|Microphones|Wii U|Headsets|Atari Lynx|Accessory Kits|Casual Games|Interactive Gaming Figures|Memory|Intellivision|NEOGEO Pocket|Nintendo DS|TurboGrafx 16|PlayStation Vita|Video Games|Digital Games|PC Game Downloads|Mac|Sensor Bars|Atari Jaguar|Flight Controls|Currency Cards|Games|Super Nintendo|Wii|Hardware|Gaming Mice|Sega Saturn|Cables|Gaming Keyboards|           id|software|Video_Games|below20|below50|below100|below300|price_unknown|      TFIDF_features|cust_id|\n",
      "+--------------------+-------+-------------------+--------------------+------------------+-----------+----------+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+-------------+--------+-----------+-------+-------+--------+--------+-------------+--------------------+-------+\n",
      "|This does not com...|    5.0|                274|                 265|              0.97|       1053|B001FBHT34|     131442|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        1.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     1.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0|  25769803779|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|      0|\n",
      "|My son has been e...|    2.0|                133|                 127|              0.95|       2678|B000EXW52O|      21032|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 154618822659|       0|          1|      0|      0|       1|       0|            0|(10,[0,1,2,3,4,5,...|      1|\n",
      "|Unfortunately I c...|    1.0|                799|                 497|              0.62|       4713|B00BGA9WK2|         13|           4|              0.0|          1.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     1.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 274877907016|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|      2|\n",
      "|Animal Crossing w...|    5.0|                716|                 683|              0.95|       4694|B0009Z3MQK|       1769|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        1.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 292057776131|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|      3|\n",
      "|Got this game the...|    5.0|                205|                  16|              0.08|         93|B000FKBCX4|        368|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|1.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 326417514596|       0|          1|      0|      1|       0|       0|            0|(10,[0,5,7],[0.10...|      4|\n",
      "|I'm going to pref...|    5.0|                238|                 229|              0.96|       2912|B00D6NPFDK|        199|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|1.0|     0.0|        0.0|        0.0|   0.0|             0.0| 446676598802|       0|          1|      0|      0|       1|       0|            0|(10,[0,1,2,3,4,5,...|      5|\n",
      "|The all new slimm...|    5.0|                411|                 388|              0.94|       2642|B001KMRN0M|        231|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     1.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     1.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 472446402575|       0|          1|      0|      0|       0|       1|            0|(10,[0,1,2,3,4,5,...|      6|\n",
      "|The Starter Bundl...|    5.0|                497|                 479|              0.96|       6857|B002I0J4NE|       1749|           2|              0.0|          0.0|          1.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 481036337164|       0|          1|      0|      0|       1|       0|            0|(10,[0,1,2,3,4,5,...|      7|\n",
      "|AtGames is a Chin...|    2.0|                266|                 252|              0.95|       5461|B0094H8H7I|       4840|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         1.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     1.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         1.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     1.0|        0.0|        0.0|   0.0|             0.0| 592705486860|       0|          1|      0|      0|       0|       1|            0|(10,[0,1,2,3,4,5,...|      8|\n",
      "|I've had Grand Sl...|    4.0|                238|                 229|              0.96|       3653|B001SET1KW|      14464|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|1.0|     0.0|        0.0|        0.0|   0.0|             0.0| 627065225225|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|      9|\n",
      "|**UPDATE: MANY RE...|    5.0|                243|                 234|              0.96|       6742|B001UU1WRE|       8087|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|1.0|     0.0|        0.0|        0.0|   0.0|             0.0| 730144440325|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     10|\n",
      "|No spoilers! Read...|    5.0|                267|                 248|              0.93|       5287|B0002A6CQ4|       3005|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     1.0|            0.0|0.0|               0.0|         1.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 738734374913|       0|          1|      0|      0|       0|       1|            0|(10,[0,1,2,3,4,5,...|     11|\n",
      "|As you'll probabl...|    1.0|                109|                   5|              0.05|        692|B00005JC8D|      22418|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 816043786244|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     12|\n",
      "|I've been an Xbox...|    5.0|                132|                 121|              0.92|       1955|B00FATRKLI|       1223|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     1.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 824633720837|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|     13|\n",
      "|When Call of Duty...|    1.0|                283|                 251|              0.89|       3592|B00269QLJ2|       3518|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 841813590032|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     14|\n",
      "|It's amazing what...|    2.0|                269|                  11|              0.04|        773|B002I0J4VQ|       1846|           1|              0.0|          0.0|          1.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 927712935944|       0|          1|      0|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     15|\n",
      "|Imagine you have ...|    1.0|                376|                 283|              0.75|        599|B004FYEZMQ|        868|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     1.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 927712935972|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|     16|\n",
      "|In 2005, F.E.A.R....|    2.0|                127|                 109|              0.86|       5107|B0016N4A7E|      15551|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 953482739720|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     17|\n",
      "|While the past ye...|    4.0|                139|                 111|               0.8|       2609|B001DO3NEW|       3201|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|1.0|     0.0|        0.0|        0.0|   0.0|             0.0|1013612281875|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|     18|\n",
      "|We got our hands ...|    5.0|               2749|                2392|              0.87|       6736|B0009VXAM0|       2952|           1|              0.0|          0.0|          1.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0|1099511627788|       0|          1|      0|      0|       0|       1|            0|(10,[0,1,2,3,4,5,...|     19|\n",
      "+--------------------+-------+-------------------+--------------------+------------------+-----------+----------+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+-------------+--------+-----------+-------+-------+--------+--------+-------------+--------------------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "id_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NMF problem:\n",
    "Making a list of sparse vector into a rating matrix is not difficult.  \n",
    "However, once you convert it, the rating matrix could contain many values that can not be stored in a laptop CPU.  \n",
    "Since I am having problem with memory allocation, I only used 10 features for TFIDF and only take first 1000 samples from my model to run NMF.  \n",
    "  \n",
    "Although this will produce different results than the original version, I am going to use this subset of data and keep going (so that I can at least code my later process).  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "TFIDF_rdd = id_df.select('TFIDF_features','cust_id').take(1000)\n",
    "TFIDF_model2 = hc.createDataFrame(TFIDF_rdd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rdd_id = TFIDF_model2.rdd.map(lambda x: [x.cust_id]*len(x.TFIDF_features.indices)).flatMap(lambda x: x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rdd_indices = TFIDF_model2.rdd.map(lambda x: x.TFIDF_features.indices).flatMap(lambda x: x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rdd_tfidf = TFIDF_model2.rdd.map(lambda x: x.TFIDF_features.values).flatMap(lambda x: x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rd_zip1 = rdd_id.zip(rdd_indices)\n",
    "rdd_zipped = rd_zip1.zip(rdd_tfidf).map(lambda x: (str(x[0][0]),int(x[0][1]),float(x[1])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('0', 0, 0.29023739264518944), ('0', 1, 0.740153459093575)]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdd_zipped.take(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_for_NMF = hc.createDataFrame(rdd_zipped)\n",
    "df_for_NMF = df_for_NMF.select(col('_1').alias(\"USER_ID\").cast(IntegerType()), col('_2').alias(\"tfidf_index\").cast(IntegerType()), col('_3').alias(\"tfidf_term\"))\n",
    "df_for_NMF = df_for_NMF.withColumn(\"tfidf_ROUND\", pys_fun.format_number(df_for_NMF.tfidf_term, 3).cast(FloatType()))\n",
    "df_for_NMF = df_for_NMF.drop('tfidf_term')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- USER_ID: integer (nullable = true)\n",
      " |-- tfidf_index: integer (nullable = true)\n",
      " |-- tfidf_ROUND: float (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_for_NMF.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MY dataframe is now ready to run ALS.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9704\n",
      "9704\n",
      "9704\n"
     ]
    }
   ],
   "source": [
    "print(rdd_id.count())\n",
    "print(rdd_indices.count())\n",
    "print(rdd_tfidf.count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So let me summarize what is happening with my data:\n",
    "1. has the first 1000 data samples from the TFIDF_model.  \n",
    "2. in df_for_NMF, it has 3 features, USER_ID,tfidf_index,tfidf_ROUND.    \n",
    "    - USER_ID = data sample id (integer)  \n",
    "    - tfidf_index = names of each tfidf terms (integer)  \n",
    "    - tifdf_ROUND = tfidf values of the tfidf_index for that USER_ID. rounded to 2 decimal digits.  (float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ratings = df_for_NMF.rdd.map(lambda l: Rating(int(l[0]), int(l[1]), float(l[2])))\n",
    "rank = 15\n",
    "numIterations = 15\n",
    "#als = ALS(rank=8, maxIter=5, userCol=\"USER_ID\", itemCol=\"tfidf_index\", ratingCol=\"tfidf_ROUND\", nonnegative=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#my model runs 10 iterations of ALS calculation and has 8 hidden features.  \n",
    "model = ALS.train(ratings,rank,numIterations,nonnegative=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "NMF_results = model.userFeatures()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0,\n",
       "  array('d', [0.16416122019290924, 0.0, 0.0, 0.0, 0.0, 0.0055698673240840435, 0.0, 0.4366821050643921, 0.47314947843551636, 0.0, 0.0, 0.0, 0.0, 0.2683221399784088, 0.0])),\n",
       " (4,\n",
       "  array('d', [0.0, 0.0, 0.0, 0.0, 0.06606338918209076, 0.013389339670538902, 0.011553904972970486, 0.004726927727460861, 0.0691143274307251, 0.00015500385779887438, 0.0, 0.0, 0.0, 0.04424767568707466, 0.0])),\n",
       " (8,\n",
       "  array('d', [0.7441716194152832, 0.0, 0.0, 0.0, 0.6991147398948669, 0.9363747835159302, 0.8712197542190552, 0.30249133706092834, 0.9294428825378418, 1.175255537033081, 0.8778303861618042, 0.0, 0.0, 0.6912622451782227, 0.0])),\n",
       " (12,\n",
       "  array('d', [0.11978136003017426, 0.0, 0.0, 0.0, 0.12147659808397293, 0.11968875676393509, 0.05484406650066376, 0.1616278737783432, 0.0, 0.2573058605194092, 0.0, 0.0, 0.0, 0.0, 0.0])),\n",
       " (16,\n",
       "  array('d', [0.02931974083185196, 0.0, 0.0, 0.0, 0.09359288960695267, 0.21137385070323944, 0.0, 0.1696062833070755, 0.0, 0.07691962271928787, 0.11201909929513931, 0.0, 0.0, 0.01738045923411846, 0.0]))]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NMF_results.take(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "YAY! I got something here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "NMF_res = NMF_results.values().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "np_NMF = np.array(NMF_res)\n",
    "pd_NMF = pd.DataFrame(np_NMF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Pd_NMF = pd_NMF.rename(columns ={0:\"NMF1\", 1:\"NMF2\", 2:\"NMF3\", 3:\"NMF4\", 4:\"NMF5\", 5:\"NMF6\", 6:\"NMF7\",\\\n",
    "                                 7:\"NMF8\", 8:\"NMF9\", 9:\"NMF10\", 10:\"NMF11\", 11:\"NMF12\", 12:\"NMF13\",\\\n",
    "                                 13:\"NMF14\", 14:\"NMF15\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Pd_NMF['cust_id'] = Pd_NMF.index\n",
    "#I'm repeating this step so that spark recognize cust_id as a feature and not as an index feature.  \n",
    "pd_NMF['cust_id2'] = Pd_NMF['cust_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NMF1</th>\n",
       "      <th>NMF2</th>\n",
       "      <th>NMF3</th>\n",
       "      <th>NMF4</th>\n",
       "      <th>NMF5</th>\n",
       "      <th>NMF6</th>\n",
       "      <th>NMF7</th>\n",
       "      <th>NMF8</th>\n",
       "      <th>NMF9</th>\n",
       "      <th>NMF10</th>\n",
       "      <th>NMF11</th>\n",
       "      <th>NMF12</th>\n",
       "      <th>NMF13</th>\n",
       "      <th>NMF14</th>\n",
       "      <th>NMF15</th>\n",
       "      <th>cust_id2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.164161</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.005570</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.436682</td>\n",
       "      <td>0.473149</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.268322</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.066063</td>\n",
       "      <td>0.013389</td>\n",
       "      <td>0.011554</td>\n",
       "      <td>0.004727</td>\n",
       "      <td>0.069114</td>\n",
       "      <td>0.000155</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.044248</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.744172</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.699115</td>\n",
       "      <td>0.936375</td>\n",
       "      <td>0.871220</td>\n",
       "      <td>0.302491</td>\n",
       "      <td>0.929443</td>\n",
       "      <td>1.175256</td>\n",
       "      <td>0.877830</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.691262</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.119781</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.121477</td>\n",
       "      <td>0.119689</td>\n",
       "      <td>0.054844</td>\n",
       "      <td>0.161628</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.257306</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.029320</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.093593</td>\n",
       "      <td>0.211374</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.169606</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.076920</td>\n",
       "      <td>0.112019</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.017380</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.384303</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.369571</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.659860</td>\n",
       "      <td>2.320368</td>\n",
       "      <td>0.584154</td>\n",
       "      <td>0.981200</td>\n",
       "      <td>1.414535</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.074128</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.071392</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.083100</td>\n",
       "      <td>0.365640</td>\n",
       "      <td>0.482205</td>\n",
       "      <td>0.184634</td>\n",
       "      <td>0.308133</td>\n",
       "      <td>0.046077</td>\n",
       "      <td>0.214599</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.132512</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4.625066</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.495781</td>\n",
       "      <td>3.440292</td>\n",
       "      <td>2.642541</td>\n",
       "      <td>3.441575</td>\n",
       "      <td>1.732401</td>\n",
       "      <td>0.694688</td>\n",
       "      <td>0.558181</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.489183</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.160408</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.043664</td>\n",
       "      <td>0.156508</td>\n",
       "      <td>0.161556</td>\n",
       "      <td>0.199418</td>\n",
       "      <td>0.073464</td>\n",
       "      <td>0.038703</td>\n",
       "      <td>0.031808</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.198606</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.772353</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.961693</td>\n",
       "      <td>1.878223</td>\n",
       "      <td>1.430986</td>\n",
       "      <td>0.927186</td>\n",
       "      <td>1.845948</td>\n",
       "      <td>0.786515</td>\n",
       "      <td>0.923379</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.553925</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       NMF1  NMF2  NMF3  NMF4      NMF5      NMF6      NMF7      NMF8  \\\n",
       "0  0.164161   0.0   0.0   0.0  0.000000  0.005570  0.000000  0.436682   \n",
       "1  0.000000   0.0   0.0   0.0  0.066063  0.013389  0.011554  0.004727   \n",
       "2  0.744172   0.0   0.0   0.0  0.699115  0.936375  0.871220  0.302491   \n",
       "3  0.119781   0.0   0.0   0.0  0.121477  0.119689  0.054844  0.161628   \n",
       "4  0.029320   0.0   0.0   0.0  0.093593  0.211374  0.000000  0.169606   \n",
       "5  0.384303   0.0   0.0   0.0  0.369571  0.000000  0.659860  2.320368   \n",
       "6  0.071392   0.0   0.0   0.0  0.083100  0.365640  0.482205  0.184634   \n",
       "7  4.625066   0.0   0.0   0.0  2.495781  3.440292  2.642541  3.441575   \n",
       "8  0.160408   0.0   0.0   0.0  0.043664  0.156508  0.161556  0.199418   \n",
       "9  0.772353   0.0   0.0   0.0  0.961693  1.878223  1.430986  0.927186   \n",
       "\n",
       "       NMF9     NMF10     NMF11  NMF12  NMF13     NMF14  NMF15  cust_id2  \n",
       "0  0.473149  0.000000  0.000000    0.0    0.0  0.268322    0.0         0  \n",
       "1  0.069114  0.000155  0.000000    0.0    0.0  0.044248    0.0         1  \n",
       "2  0.929443  1.175256  0.877830    0.0    0.0  0.691262    0.0         2  \n",
       "3  0.000000  0.257306  0.000000    0.0    0.0  0.000000    0.0         3  \n",
       "4  0.000000  0.076920  0.112019    0.0    0.0  0.017380    0.0         4  \n",
       "5  0.584154  0.981200  1.414535    0.0    0.0  1.074128    0.0         5  \n",
       "6  0.308133  0.046077  0.214599    0.0    0.0  0.132512    0.0         6  \n",
       "7  1.732401  0.694688  0.558181    0.0    0.0  1.489183    0.0         7  \n",
       "8  0.073464  0.038703  0.031808    0.0    0.0  0.198606    0.0         8  \n",
       "9  1.845948  0.786515  0.923379    0.0    0.0  1.553925    0.0         9  "
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_NMF.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#making a schema for sp_NMF\n",
    "schema = StructType([\n",
    "        StructField('NMF1', FloatType(), True),\n",
    "        StructField('NMF2', FloatType(), True),\n",
    "        StructField('NMF3', FloatType(), True),\n",
    "        StructField('NMF4', FloatType(), True),\n",
    "        StructField('NMF5', FloatType(), True),\n",
    "        StructField('NMF6', FloatType(), True),\n",
    "        StructField('NMF7', FloatType(), True),\n",
    "        StructField('NMF8', FloatType(), True),\n",
    "        StructField('NMF9', FloatType(), True),\n",
    "        StructField('NMF10', FloatType(), True),\n",
    "        StructField('NMF11', FloatType(), True),\n",
    "        StructField('NMF12', FloatType(), True),\n",
    "        StructField('NMF13', FloatType(), True),\n",
    "        StructField('NMF14', FloatType(), True),\n",
    "        StructField('NMF15', FloatType(), True),\n",
    "        StructField('cust_id', IntegerType(), True)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sp_NMF =hc.createDataFrame(pd_NMF,schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+----+----+----+-----------+------------+-----------+------------+-----------+------------+-----------+-----+-----+-----------+-----+-------+\n",
      "|       NMF1|NMF2|NMF3|NMF4|       NMF5|        NMF6|       NMF7|        NMF8|       NMF9|       NMF10|      NMF11|NMF12|NMF13|      NMF14|NMF15|cust_id|\n",
      "+-----------+----+----+----+-----------+------------+-----------+------------+-----------+------------+-----------+-----+-----+-----------+-----+-------+\n",
      "| 0.16416122| 0.0| 0.0| 0.0|        0.0|0.0055698673|        0.0|   0.4366821| 0.47314948|         0.0|        0.0|  0.0|  0.0| 0.26832214|  0.0|      0|\n",
      "|        0.0| 0.0| 0.0| 0.0| 0.06606339|  0.01338934|0.011553905|0.0047269277| 0.06911433|1.5500386E-4|        0.0|  0.0|  0.0|0.044247676|  0.0|      1|\n",
      "|  0.7441716| 0.0| 0.0| 0.0| 0.69911474|   0.9363748| 0.87121975|  0.30249134|  0.9294429|   1.1752555|  0.8778304|  0.0|  0.0| 0.69126225|  0.0|      2|\n",
      "| 0.11978136| 0.0| 0.0| 0.0|  0.1214766|  0.11968876|0.054844067|  0.16162787|        0.0|  0.25730586|        0.0|  0.0|  0.0|        0.0|  0.0|      3|\n",
      "| 0.02931974| 0.0| 0.0| 0.0| 0.09359289|  0.21137385|        0.0|  0.16960628|        0.0|  0.07691962|  0.1120191|  0.0|  0.0| 0.01738046|  0.0|      4|\n",
      "| 0.38430297| 0.0| 0.0| 0.0|  0.3695708|         0.0|    0.65986|    2.320368|  0.5841545|  0.98119986|  1.4145352|  0.0|  0.0|   1.074128|  0.0|      5|\n",
      "|0.071391806| 0.0| 0.0| 0.0| 0.08309974|  0.36564034|  0.4822047|   0.1846343| 0.30813292|  0.04607732| 0.21459903|  0.0|  0.0| 0.13251242|  0.0|      6|\n",
      "|  4.6250663| 0.0| 0.0| 0.0|  2.4957812|   3.4402924|  2.6425412|    3.441575|  1.7324015|   0.6946877|   0.558181|  0.0|  0.0|  1.4891833|  0.0|      7|\n",
      "| 0.16040781| 0.0| 0.0| 0.0|  0.0436635|  0.15650813| 0.16155614|  0.19941776| 0.07346431|  0.03870332|0.031808425|  0.0|  0.0| 0.19860625|  0.0|      8|\n",
      "| 0.77235293| 0.0| 0.0| 0.0|  0.9616929|   1.8782231|  1.4309864|  0.92718613|  1.8459483|  0.78651536| 0.92337936|  0.0|  0.0|  1.5539252|  0.0|      9|\n",
      "| 0.63690436| 0.0| 0.0| 0.0|  0.8198148|  0.94973725| 0.32539496|  0.62138736|  0.6380616|   1.0405147|   0.795335|  0.0|  0.0|  1.0606444|  0.0|     10|\n",
      "|  1.1787956| 0.0| 0.0| 0.0|  1.5121595|  0.90021986|  0.8580229|   1.6393564|  1.3239065|   0.9598198|   0.760794|  0.0|  0.0| 0.80935925|  0.0|     11|\n",
      "|0.009577243| 0.0| 0.0| 0.0|        0.0|  0.02380852| 0.09484388|  0.14698242| 0.07182374|         0.0| 0.14621499|  0.0|  0.0|0.049160425|  0.0|     12|\n",
      "| 0.06718137| 0.0| 0.0| 0.0| 0.36237255|  0.41385478|  0.5404332|  0.42741102|  0.5218714|   0.2109595| 0.13062264|  0.0|  0.0| 0.52365005|  0.0|     13|\n",
      "|0.037125457| 0.0| 0.0| 0.0|  0.5392861|   0.3286933| 0.16365814|  0.34408078| 0.10931545|  0.15365592|0.011859398|  0.0|  0.0|   0.434547|  0.0|     14|\n",
      "|0.043004032| 0.0| 0.0| 0.0|0.016382428| 0.038391713|0.007503236|  0.03510151|0.015553906|  0.00547511|        0.0|  0.0|  0.0|0.018766688|  0.0|     15|\n",
      "| 0.34074265| 0.0| 0.0| 0.0|  1.5456933|   0.4861389|   1.458343|   0.7632951|   1.449196|   0.4255285| 0.42571187|  0.0|  0.0|  0.8553687|  0.0|     16|\n",
      "|        0.0| 0.0| 0.0| 0.0| 0.22054143| 0.050099336|  0.4019892|  0.28575912| 0.00827426|  0.20459087|        0.0|  0.0|  0.0|0.060665894|  0.0|     17|\n",
      "|  2.0832205| 0.0| 0.0| 0.0|  1.3930095|  0.84946895|  1.1240748|   2.2621312|  1.5946895|    3.323226| 0.54479533|  0.0|  0.0|  2.7778084|  0.0|     18|\n",
      "| 0.28294432| 0.0| 0.0| 0.0| 0.05863857|  0.19409111| 0.17735375|   0.3365037|        0.0|   0.3108411|        0.0|  0.0|  0.0| 0.21418314|  0.0|     19|\n",
      "+-----------+----+----+----+-----------+------------+-----------+------------+-----------+------------+-----------+-----+-----+-----------+-----+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sp_NMF.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sp_NMF.registerTempTable('NMF')\n",
    "id_df.registerTempTable('TFIDF')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = '''SELECT TFIDF.*,NMF.NMF1,NMF.NMF2,NMF.NMF3,NMF.NMF4,NMF.NMF5,NMF.NMF6,NMF.NMF7,NMF.NMF8,\n",
    "            NMF.NMF9,NMF.NMF10,NMF.NMF11,NMF.NMF12,NMF.NMF13,NMF.NMF14,NMF.NMF15\n",
    "            FROM TFIDF JOIN NMF ON TFIDF.cust_id = NMF.cust_id ORDER BY TFIDF.cust_id'''\n",
    "df_for_RF = hc.sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-------+-------------------+--------------------+------------------+-----------+----------+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+-------------+--------+-----------+-------+-------+--------+--------+-------------+--------------------+-------+-----------+----+----+----+-----------+------------+-----------+------------+-----------+------------+-----------+-----+-----+-----------+-----+\n",
      "|          reviewText|overall|helpful_total_votes|num_of_helpful_votes|helpful_percentage|Text_length|      asin|rank_values|num_category|Screen Protectors|PlayStation 4|PlayStation 3|PlayStation 2|Game Boy Advance|Joysticks|GameCube|Commodore Amiga|3DO|Cases & Protectors|More Systems|Sony PSP|LIVE|Sega Master System|Fitness Accessories|Subscription Cards|Points & Currency Cards|Atari 2600|Controllers|Light Guns|Nintendo 64|Nintendo 3DS|Linux Games|Accessories|Skins|Steering Wheels|Racing Wheels|PlayStation|Networking|Atari 7800|Xbox 360|Chargers|Kids & Family|ColecoVision|Gamepads|Digital Games & DLC| PC|Consoles|Game Boy Color|Nintendo NES|Drums|MMO & Free-to-Play Games|Xbox|Sega CD|Faceplates|Game Boy|Adapters|Batteries|Fire TV|Sega Genesis|Speakers|Batteries & Chargers|Mac Game Downloads|Sega Game Gear|PDAs|Sega Dreamcast|Remotes|Xbox One|Dance Mats|Atari 5200|Keyboards|Downloadable Content|Cables & Adapters|Guitars|Commodore 64|Microphones|Wii U|Headsets|Atari Lynx|Accessory Kits|Casual Games|Interactive Gaming Figures|Memory|Intellivision|NEOGEO Pocket|Nintendo DS|TurboGrafx 16|PlayStation Vita|Video Games|Digital Games|PC Game Downloads|Mac|Sensor Bars|Atari Jaguar|Flight Controls|Currency Cards|Games|Super Nintendo|Wii|Hardware|Gaming Mice|Sega Saturn|Cables|Gaming Keyboards|           id|software|Video_Games|below20|below50|below100|below300|price_unknown|      TFIDF_features|cust_id|       NMF1|NMF2|NMF3|NMF4|       NMF5|        NMF6|       NMF7|        NMF8|       NMF9|       NMF10|      NMF11|NMF12|NMF13|      NMF14|NMF15|\n",
      "+--------------------+-------+-------------------+--------------------+------------------+-----------+----------+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+-------------+--------+-----------+-------+-------+--------+--------+-------------+--------------------+-------+-----------+----+----+----+-----------+------------+-----------+------------+-----------+------------+-----------+-----+-----+-----------+-----+\n",
      "|This does not com...|    5.0|                274|                 265|              0.97|       1053|B001FBHT34|     131442|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        1.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     1.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0|  25769803779|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|      0| 0.16416122| 0.0| 0.0| 0.0|        0.0|0.0055698673|        0.0|   0.4366821| 0.47314948|         0.0|        0.0|  0.0|  0.0| 0.26832214|  0.0|\n",
      "|My son has been e...|    2.0|                133|                 127|              0.95|       2678|B000EXW52O|      21032|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 154618822659|       0|          1|      0|      0|       1|       0|            0|(10,[0,1,2,3,4,5,...|      1|        0.0| 0.0| 0.0| 0.0| 0.06606339|  0.01338934|0.011553905|0.0047269277| 0.06911433|1.5500386E-4|        0.0|  0.0|  0.0|0.044247676|  0.0|\n",
      "|Unfortunately I c...|    1.0|                799|                 497|              0.62|       4713|B00BGA9WK2|         13|           4|              0.0|          1.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     1.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 274877907016|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|      2|  0.7441716| 0.0| 0.0| 0.0| 0.69911474|   0.9363748| 0.87121975|  0.30249134|  0.9294429|   1.1752555|  0.8778304|  0.0|  0.0| 0.69126225|  0.0|\n",
      "|Animal Crossing w...|    5.0|                716|                 683|              0.95|       4694|B0009Z3MQK|       1769|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        1.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 292057776131|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|      3| 0.11978136| 0.0| 0.0| 0.0|  0.1214766|  0.11968876|0.054844067|  0.16162787|        0.0|  0.25730586|        0.0|  0.0|  0.0|        0.0|  0.0|\n",
      "|Got this game the...|    5.0|                205|                  16|              0.08|         93|B000FKBCX4|        368|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|1.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 326417514596|       0|          1|      0|      1|       0|       0|            0|(10,[0,5,7],[0.10...|      4| 0.02931974| 0.0| 0.0| 0.0| 0.09359289|  0.21137385|        0.0|  0.16960628|        0.0|  0.07691962|  0.1120191|  0.0|  0.0| 0.01738046|  0.0|\n",
      "|I'm going to pref...|    5.0|                238|                 229|              0.96|       2912|B00D6NPFDK|        199|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|1.0|     0.0|        0.0|        0.0|   0.0|             0.0| 446676598802|       0|          1|      0|      0|       1|       0|            0|(10,[0,1,2,3,4,5,...|      5| 0.38430297| 0.0| 0.0| 0.0|  0.3695708|         0.0|    0.65986|    2.320368|  0.5841545|  0.98119986|  1.4145352|  0.0|  0.0|   1.074128|  0.0|\n",
      "|The all new slimm...|    5.0|                411|                 388|              0.94|       2642|B001KMRN0M|        231|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     1.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     1.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 472446402575|       0|          1|      0|      0|       0|       1|            0|(10,[0,1,2,3,4,5,...|      6|0.071391806| 0.0| 0.0| 0.0| 0.08309974|  0.36564034|  0.4822047|   0.1846343| 0.30813292|  0.04607732| 0.21459903|  0.0|  0.0| 0.13251242|  0.0|\n",
      "|The Starter Bundl...|    5.0|                497|                 479|              0.96|       6857|B002I0J4NE|       1749|           2|              0.0|          0.0|          1.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 481036337164|       0|          1|      0|      0|       1|       0|            0|(10,[0,1,2,3,4,5,...|      7|  4.6250663| 0.0| 0.0| 0.0|  2.4957812|   3.4402924|  2.6425412|    3.441575|  1.7324015|   0.6946877|   0.558181|  0.0|  0.0|  1.4891833|  0.0|\n",
      "|AtGames is a Chin...|    2.0|                266|                 252|              0.95|       5461|B0094H8H7I|       4840|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         1.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     1.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         1.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     1.0|        0.0|        0.0|   0.0|             0.0| 592705486860|       0|          1|      0|      0|       0|       1|            0|(10,[0,1,2,3,4,5,...|      8| 0.16040781| 0.0| 0.0| 0.0|  0.0436635|  0.15650813| 0.16155614|  0.19941776| 0.07346431|  0.03870332|0.031808425|  0.0|  0.0| 0.19860625|  0.0|\n",
      "|I've had Grand Sl...|    4.0|                238|                 229|              0.96|       3653|B001SET1KW|      14464|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|1.0|     0.0|        0.0|        0.0|   0.0|             0.0| 627065225225|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|      9| 0.77235293| 0.0| 0.0| 0.0|  0.9616929|   1.8782231|  1.4309864|  0.92718613|  1.8459483|  0.78651536| 0.92337936|  0.0|  0.0|  1.5539252|  0.0|\n",
      "|**UPDATE: MANY RE...|    5.0|                243|                 234|              0.96|       6742|B001UU1WRE|       8087|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|1.0|     0.0|        0.0|        0.0|   0.0|             0.0| 730144440325|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     10| 0.63690436| 0.0| 0.0| 0.0|  0.8198148|  0.94973725| 0.32539496|  0.62138736|  0.6380616|   1.0405147|   0.795335|  0.0|  0.0|  1.0606444|  0.0|\n",
      "|No spoilers! Read...|    5.0|                267|                 248|              0.93|       5287|B0002A6CQ4|       3005|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     1.0|            0.0|0.0|               0.0|         1.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 738734374913|       0|          1|      0|      0|       0|       1|            0|(10,[0,1,2,3,4,5,...|     11|  1.1787956| 0.0| 0.0| 0.0|  1.5121595|  0.90021986|  0.8580229|   1.6393564|  1.3239065|   0.9598198|   0.760794|  0.0|  0.0| 0.80935925|  0.0|\n",
      "|As you'll probabl...|    1.0|                109|                   5|              0.05|        692|B00005JC8D|      22418|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 816043786244|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     12|0.009577243| 0.0| 0.0| 0.0|        0.0|  0.02380852| 0.09484388|  0.14698242| 0.07182374|         0.0| 0.14621499|  0.0|  0.0|0.049160425|  0.0|\n",
      "|I've been an Xbox...|    5.0|                132|                 121|              0.92|       1955|B00FATRKLI|       1223|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     1.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 824633720837|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|     13| 0.06718137| 0.0| 0.0| 0.0| 0.36237255|  0.41385478|  0.5404332|  0.42741102|  0.5218714|   0.2109595| 0.13062264|  0.0|  0.0| 0.52365005|  0.0|\n",
      "|When Call of Duty...|    1.0|                283|                 251|              0.89|       3592|B00269QLJ2|       3518|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 841813590032|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     14|0.037125457| 0.0| 0.0| 0.0|  0.5392861|   0.3286933| 0.16365814|  0.34408078| 0.10931545|  0.15365592|0.011859398|  0.0|  0.0|   0.434547|  0.0|\n",
      "|It's amazing what...|    2.0|                269|                  11|              0.04|        773|B002I0J4VQ|       1846|           1|              0.0|          0.0|          1.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 927712935944|       0|          1|      0|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     15|0.043004032| 0.0| 0.0| 0.0|0.016382428| 0.038391713|0.007503236|  0.03510151|0.015553906|  0.00547511|        0.0|  0.0|  0.0|0.018766688|  0.0|\n",
      "|Imagine you have ...|    1.0|                376|                 283|              0.75|        599|B004FYEZMQ|        868|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     1.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 927712935972|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|     16| 0.34074265| 0.0| 0.0| 0.0|  1.5456933|   0.4861389|   1.458343|   0.7632951|   1.449196|   0.4255285| 0.42571187|  0.0|  0.0|  0.8553687|  0.0|\n",
      "|In 2005, F.E.A.R....|    2.0|                127|                 109|              0.86|       5107|B0016N4A7E|      15551|           1|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|1.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0| 953482739720|       0|          1|      1|      0|       0|       0|            0|(10,[0,1,2,3,4,5,...|     17|        0.0| 0.0| 0.0| 0.0| 0.22054143| 0.050099336|  0.4019892|  0.28575912| 0.00827426|  0.20459087|        0.0|  0.0|  0.0|0.060665894|  0.0|\n",
      "|While the past ye...|    4.0|                139|                 111|               0.8|       2609|B001DO3NEW|       3201|           2|              0.0|          0.0|          0.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          1.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  1.0|           0.0|1.0|     0.0|        0.0|        0.0|   0.0|             0.0|1013612281875|       0|          1|      0|      1|       0|       0|            0|(10,[0,1,2,3,4,5,...|     18|  2.0832205| 0.0| 0.0| 0.0|  1.3930095|  0.84946895|  1.1240748|   2.2621312|  1.5946895|    3.323226| 0.54479533|  0.0|  0.0|  2.7778084|  0.0|\n",
      "|We got our hands ...|    5.0|               2749|                2392|              0.87|       6736|B0009VXAM0|       2952|           1|              0.0|          0.0|          1.0|          0.0|             0.0|      0.0|     0.0|            0.0|0.0|               0.0|         0.0|     0.0| 0.0|               0.0|                0.0|               0.0|                    0.0|       0.0|        0.0|       0.0|        0.0|         0.0|        0.0|        0.0|  0.0|            0.0|          0.0|        0.0|       0.0|       0.0|     0.0|     0.0|          0.0|         0.0|     0.0|                0.0|0.0|     0.0|           0.0|         0.0|  0.0|                     0.0| 0.0|    0.0|       0.0|     0.0|     0.0|      0.0|    0.0|         0.0|     0.0|                 0.0|               0.0|           0.0| 0.0|           0.0|    0.0|     0.0|       0.0|       0.0|      0.0|                 0.0|              0.0|    0.0|         0.0|        0.0|  0.0|     0.0|       0.0|           0.0|         0.0|                       0.0|   0.0|          0.0|          0.0|        0.0|          0.0|             0.0|        1.0|          0.0|              0.0|0.0|        0.0|         0.0|            0.0|           0.0|  0.0|           0.0|0.0|     0.0|        0.0|        0.0|   0.0|             0.0|1099511627788|       0|          1|      0|      0|       0|       1|            0|(10,[0,1,2,3,4,5,...|     19| 0.28294432| 0.0| 0.0| 0.0| 0.05863857|  0.19409111| 0.17735375|   0.3365037|        0.0|   0.3108411|        0.0|  0.0|  0.0| 0.21418314|  0.0|\n",
      "+--------------------+-------+-------------------+--------------------+------------------+-----------+----------+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-----------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+-------------+--------+-----------+-------+-------+--------+--------+-------------+--------------------+-------+-----------+----+----+----+-----------+------------+-----------+------------+-----------+------------+-----------+-----+-----+-----------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_for_RF.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total number of datapoints: 1000\n",
      "total number of features: 128\n"
     ]
    }
   ],
   "source": [
    "print \"total number of datapoints: {}\".format(df_for_RF.count())\n",
    "print \"total number of features: {}\".format(len(df_for_RF.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#renaming columns. no space!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# removing Video Games feature. It's actually a duplicate column of Video_Games column\n",
    "df_for_RF = df_for_RF.drop('Video Games')\n",
    "df_for_RF = df_for_RF.toDF(*(c.replace(' ', '_') for c in df_for_RF.columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SAVING File\n",
    "Saving my file as a parquet file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_for_RF.write.save(\"RF_ready.parquet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Checking if I'm not losing data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test= spark.read.load(\"RF_ready.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total number of datapoints: 1000\n",
      "total number of features: 127\n"
     ]
    }
   ],
   "source": [
    "print \"total number of datapoints: {}\".format(test.count())\n",
    "print \"total number of features: {}\".format(len(test.columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Size of the df is correct. Now I just need to check if there's any nulls."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test=test.drop('TFIDF_features')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+-------------------+--------------------+------------------+-----------+----+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+---+--------+-----------+-------+-------+--------+--------+-------------+-------+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+\n",
      "|reviewText|overall|helpful_total_votes|num_of_helpful_votes|helpful_percentage|Text_length|asin|rank_values|num_category|Screen_Protectors|PlayStation_4|PlayStation_3|PlayStation_2|Game_Boy_Advance|Joysticks|GameCube|Commodore_Amiga|3DO|Cases_&_Protectors|More_Systems|Sony_PSP|LIVE|Sega_Master_System|Fitness_Accessories|Subscription_Cards|Points_&_Currency_Cards|Atari_2600|Controllers|Light_Guns|Nintendo_64|Nintendo_3DS|Linux_Games|Accessories|Skins|Steering_Wheels|Racing_Wheels|PlayStation|Networking|Atari_7800|Xbox_360|Chargers|Kids_&_Family|ColecoVision|Gamepads|Digital_Games_&_DLC| PC|Consoles|Game_Boy_Color|Nintendo_NES|Drums|MMO_&_Free-to-Play_Games|Xbox|Sega_CD|Faceplates|Game_Boy|Adapters|Batteries|Fire_TV|Sega_Genesis|Speakers|Batteries_&_Chargers|Mac_Game_Downloads|Sega_Game_Gear|PDAs|Sega_Dreamcast|Remotes|Xbox_One|Dance_Mats|Atari_5200|Keyboards|Downloadable_Content|Cables_&_Adapters|Guitars|Commodore_64|Microphones|Wii_U|Headsets|Atari_Lynx|Accessory_Kits|Casual_Games|Interactive_Gaming_Figures|Memory|Intellivision|NEOGEO_Pocket|Nintendo_DS|TurboGrafx_16|PlayStation_Vita|Digital_Games|PC_Game_Downloads|Mac|Sensor_Bars|Atari_Jaguar|Flight_Controls|Currency_Cards|Games|Super_Nintendo|Wii|Hardware|Gaming_Mice|Sega_Saturn|Cables|Gaming_Keyboards| id|software|Video_Games|below20|below50|below100|below300|price_unknown|cust_id|NMF1|NMF2|NMF3|NMF4|NMF5|NMF6|NMF7|NMF8|NMF9|NMF10|NMF11|NMF12|NMF13|NMF14|NMF15|\n",
      "+----------+-------+-------------------+--------------------+------------------+-----------+----+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+---+--------+-----------+-------+-------+--------+--------+-------------+-------+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+\n",
      "|         0|      0|                  0|                   0|                 0|          0|   0|          0|           0|                0|            0|            0|            0|               0|        0|       0|              0|  0|                 0|           0|       0|   0|                 0|                  0|                 0|                      0|         0|          0|         0|          0|           0|          0|          0|    0|              0|            0|          0|         0|         0|       0|       0|            0|           0|       0|                  0|  0|       0|             0|           0|    0|                       0|   0|      0|         0|       0|       0|        0|      0|           0|       0|                   0|                 0|             0|   0|             0|      0|       0|         0|         0|        0|                   0|                0|      0|           0|          0|    0|       0|         0|             0|           0|                         0|     0|            0|            0|          0|            0|               0|            0|                0|  0|          0|           0|              0|             0|    0|             0|  0|       0|          0|          0|     0|               0|  0|       0|          0|      0|      0|       0|       0|            0|      0|   0|   0|   0|   0|   0|   0|   0|   0|   0|    0|    0|    0|    0|    0|    0|\n",
      "+----------+-------+-------------------+--------------------+------------------+-----------+----+-----------+------------+-----------------+-------------+-------------+-------------+----------------+---------+--------+---------------+---+------------------+------------+--------+----+------------------+-------------------+------------------+-----------------------+----------+-----------+----------+-----------+------------+-----------+-----------+-----+---------------+-------------+-----------+----------+----------+--------+--------+-------------+------------+--------+-------------------+---+--------+--------------+------------+-----+------------------------+----+-------+----------+--------+--------+---------+-------+------------+--------+--------------------+------------------+--------------+----+--------------+-------+--------+----------+----------+---------+--------------------+-----------------+-------+------------+-----------+-----+--------+----------+--------------+------------+--------------------------+------+-------------+-------------+-----------+-------------+----------------+-------------+-----------------+---+-----------+------------+---------------+--------------+-----+--------------+---+--------+-----------+-----------+------+----------------+---+--------+-----------+-------+-------+--------+--------+-------------+-------+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "test.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in test.columns]).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: \n",
    "- TFIDF_features are sparse vector feature that cause error when I run the previous line.  \n",
    "- There is no null. I should use parquet file to export and import files."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------------"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}