notebooks/01_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sparkify: Customers Churn prediction\n",
    "\n",
    "![Customers Churn](https://thinkml.ai/content/images/2019/11/image-10.png)\n",
    "\n",
    "## Table of Contents\n",
    "\n",
    "1. [Overview](#overview)\n",
    "2. [Load and Clean Dataset](#load_clean)\n",
    "    1. [Checking for missing values](#check)\n",
    "    2. [Cleaning the data](#clean)\n",
    "3. [Exploratory Data Analysis](./02_data_exploration.ipynb)\n",
    "    1. [Descriptive statistics](#stat)\n",
    "    2. [Defining churn indicator](#churn)\n",
    "    2. [Data Exploration](#explore)\n",
    "4. [Feature Engineering](./03_feature_engineering.ipynb)\n",
    "5. [Model training and evaluation](./04_model_training_evaluation.ipynb)\n",
    "6. [Conclusion](./05_conclusion.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Overview<a id='overview'></a>\n",
    "This notebooks is done in the context of the Capstone project for `Data Science Nanodegree` Program by Udacity.\n",
    "\n",
    "Sparkify is a music streaming service like Spotify and Pandora. The users can use the service either the `Premium`  or the `Free Tier`. The premium plan with the monthly fees payment enables the use of service without any advertisements between songs.\n",
    "\n",
    "The used data contains the user activity logs happening in the service. Those contain visited pages, service upgrade or downgrade events, events timestamps, demographic info, ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pyspark==3.0.0 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from -r requirements.txt (line 1)) (3.0.0)\n",
      "Requirement already satisfied: numpy==1.19.1 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from -r requirements.txt (line 2)) (1.19.1)\n",
      "Requirement already satisfied: pandas==1.1.0 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from -r requirements.txt (line 3)) (1.1.0)\n",
      "Requirement already satisfied: matplotlib==3.3.0 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from -r requirements.txt (line 4)) (3.3.0)\n",
      "Requirement already satisfied: seaborn==0.10.1 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from -r requirements.txt (line 5)) (0.10.1)\n",
      "Requirement already satisfied: py4j==0.10.9 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from pyspark==3.0.0->-r requirements.txt (line 1)) (0.10.9)\n",
      "Requirement already satisfied: pytz>=2017.2 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from pandas==1.1.0->-r requirements.txt (line 3)) (2020.1)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from pandas==1.1.0->-r requirements.txt (line 3)) (2.8.1)\n",
      "Requirement already satisfied: kiwisolver>=1.0.1 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from matplotlib==3.3.0->-r requirements.txt (line 4)) (1.2.0)\n",
      "Requirement already satisfied: cycler>=0.10 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from matplotlib==3.3.0->-r requirements.txt (line 4)) (0.10.0)\n",
      "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from matplotlib==3.3.0->-r requirements.txt (line 4)) (2.4.7)\n",
      "Requirement already satisfied: pillow>=6.2.0 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from matplotlib==3.3.0->-r requirements.txt (line 4)) (7.2.0)\n",
      "Requirement already satisfied: scipy>=1.0.1 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from seaborn==0.10.1->-r requirements.txt (line 5)) (1.5.2)\n",
      "Requirement already satisfied: six>=1.5 in /home/wajih/miniconda3/envs/spark/lib/python3.7/site-packages (from python-dateutil>=2.7.3->pandas==1.1.0->-r requirements.txt (line 3)) (1.15.0)\n"
     ]
    }
   ],
   "source": [
    "! pip install -r requirements.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import isnan, when, count, col, udf\n",
    "from pyspark.sql import functions as F\n",
    "from pyspark.sql.types import StringType, DoubleType, LongType, IntegerType, DateType, TimestampType\n",
    "from pyspark.ml.classification import LogisticRegression, DecisionTreeClassifier\n",
    "from pyspark.ml.classification import GBTClassifier, RandomForestClassifier\n",
    "from pyspark.ml.evaluation import MulticlassClassificationEvaluator\n",
    "from pyspark.ml.tuning import CrossValidator, ParamGridBuilder\n",
    "\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .appName(\"Sparkify\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'events_data_path' (str)\n"
     ]
    }
   ],
   "source": [
    "events_data_path = \"../data/mini_sparkify_event_data.json\"\n",
    "%store events_data_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "import utils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('spark.driver.host', '192.168.1.176'),\n",
       " ('spark.app.id', 'local-1596899599143'),\n",
       " ('spark.rdd.compress', 'True'),\n",
       " ('spark.app.name', 'Sparkify'),\n",
       " ('spark.serializer.objectStreamReset', '100'),\n",
       " ('spark.master', 'local[*]'),\n",
       " ('spark.submit.pyFiles', ''),\n",
       " ('spark.executor.id', 'driver'),\n",
       " ('spark.submit.deployMode', 'client'),\n",
       " ('spark.ui.showConsoleProgress', 'true'),\n",
       " ('spark.driver.port', '34375')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make the output more like pandas and less like command-line SQL\n",
    "spark.sparkContext.getConf().getAll()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark.conf.set('spark.sql.repl.eagerEval.enabled', True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://192.168.1.176:4041\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.0.0</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>Sparkify</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7fc7fc43ce90>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center> 2. Load and Clean Dataset <a id='load_clean'></a> </center>\n",
    "\n",
    "1. Load the data.\n",
    "2. Check for invalid or missing values e.g. records without userids or sessionids. \n",
    "3. Clean the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- artist: string (nullable = true)\n",
      " |-- auth: string (nullable = true)\n",
      " |-- firstName: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- itemInSession: long (nullable = true)\n",
      " |-- lastName: string (nullable = true)\n",
      " |-- length: double (nullable = true)\n",
      " |-- level: string (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- method: string (nullable = true)\n",
      " |-- page: string (nullable = true)\n",
      " |-- registration: long (nullable = true)\n",
      " |-- sessionId: long (nullable = true)\n",
      " |-- song: string (nullable = true)\n",
      " |-- status: long (nullable = true)\n",
      " |-- ts: long (nullable = true)\n",
      " |-- userAgent: string (nullable = true)\n",
      " |-- userId: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Load the Sparkify Events data from a JSON file into a Spark DataFrame\n",
    "events_df = spark.read.json(events_data_path)\n",
    "events_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows:  286500\n",
      "Number of columns:  18\n"
     ]
    }
   ],
   "source": [
    "nb_rows = events_df.count()\n",
    "print(\"Number of rows: \", nb_rows)\n",
    "print(\"Number of columns: \", len(events_df.columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking for missing data<a id='check'></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [0]\n",
       "Index: []"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dd = events_df.select([count(when(isnan(c), c)).alias(c) for c in events_df.columns]).toPandas().T\n",
    "dd[dd[0] > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data set has no NaNs. In PySpark NaN is not the same as Null. Both of these are also different than an empty string \"\". So we check also Null values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>artist</th>\n",
       "      <td>58392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>auth</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>firstName</th>\n",
       "      <td>8346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <td>8346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>itemInSession</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lastName</th>\n",
       "      <td>8346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>length</th>\n",
       "      <td>58392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>level</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location</th>\n",
       "      <td>8346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>method</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>registration</th>\n",
       "      <td>8346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sessionId</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>song</th>\n",
       "      <td>58392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>status</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ts</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>userAgent</th>\n",
       "      <td>8346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>userId</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   0\n",
       "artist         58392\n",
       "auth               0\n",
       "firstName       8346\n",
       "gender          8346\n",
       "itemInSession      0\n",
       "lastName        8346\n",
       "length         58392\n",
       "level              0\n",
       "location        8346\n",
       "method             0\n",
       "page               0\n",
       "registration    8346\n",
       "sessionId          0\n",
       "song           58392\n",
       "status             0\n",
       "ts                 0\n",
       "userAgent       8346\n",
       "userId             0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_counts = events_df.select([\n",
    "    count(when(col(column_name).isNull(), column_name)).alias(column_name)\n",
    "        for column_name in events_df.columns\n",
    "    ]).toPandas().T\n",
    "null_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It appears that there are some users with misssing registration field, firstName and lastName. This might be related to non registered users."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking rows with Null value in all columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "286500"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_df.na.drop(how=\"all\").count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It appears that there is no row with Null value in all columns "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check empty user IDs and sessionID\n",
    "\n",
    "Regardless of the reason (logged out, unregistered...), empty userIds and sessionIds are useless for the purpose of detecting potentially churning users, so we can drop the corresponding records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8346"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_df.filter((\n",
    "    events_df[\"userId\"] == \"\") | events_df[\"userId\"].isNull() | isnan(events_df[\"userId\"])).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "286500"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_df.filter((\n",
    "    events_df[\"sessionId\"] == \"\") | events_df[\"sessionId\"].isNull() | isnan(events_df[\"sessionId\"])).count()\n",
    "events_df.dropna(how = 'any', subset = ['userId', 'sessionId'])\n",
    "events_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking for empty sessionId values shows that there are no missing values for sessionId column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of distinct users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "225"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_df = events_df.filter(events_df.userId != '')\n",
    "events_df.select('userId').distinct().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check for Duplicated events logs\n",
    "Comparing events_df.count() to events_df.distinct().count() shows that there are no duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "278154"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_df.distinct().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's re-check some of the empty fields after the cleaning\n",
    "e.g. registration, gender, ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>artist</th>\n",
       "      <td>50046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>song</th>\n",
       "      <td>50046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>length</th>\n",
       "      <td>50046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>page</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>userAgent</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ts</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>status</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sessionId</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>registration</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>method</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>auth</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>level</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lastName</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>itemInSession</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>firstName</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>userId</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   0\n",
       "artist         50046\n",
       "song           50046\n",
       "length         50046\n",
       "page               0\n",
       "userAgent          0\n",
       "ts                 0\n",
       "status             0\n",
       "sessionId          0\n",
       "registration       0\n",
       "method             0\n",
       "auth               0\n",
       "location           0\n",
       "level              0\n",
       "lastName           0\n",
       "itemInSession      0\n",
       "gender             0\n",
       "firstName          0\n",
       "userId             0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_counts = events_df.select([count(when(col(column_name).isNull(), column_name)).alias(column_name) for column_name in \n",
    "           events_df.columns]).toPandas().T\n",
    "null_counts.sort_values(by=0, axis=0, ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-warning\">\n",
    "It appears that the rows with missing values where related to the log events with no userId\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artist</th>\n",
       "      <th>auth</th>\n",
       "      <th>firstName</th>\n",
       "      <th>gender</th>\n",
       "      <th>itemInSession</th>\n",
       "      <th>lastName</th>\n",
       "      <th>length</th>\n",
       "      <th>level</th>\n",
       "      <th>location</th>\n",
       "      <th>method</th>\n",
       "      <th>page</th>\n",
       "      <th>registration</th>\n",
       "      <th>sessionId</th>\n",
       "      <th>song</th>\n",
       "      <th>status</th>\n",
       "      <th>ts</th>\n",
       "      <th>userAgent</th>\n",
       "      <th>userId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Martha Tilston</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Colin</td>\n",
       "      <td>M</td>\n",
       "      <td>50</td>\n",
       "      <td>Freeman</td>\n",
       "      <td>277.89016</td>\n",
       "      <td>paid</td>\n",
       "      <td>Bakersfield, CA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1538173362000</td>\n",
       "      <td>29</td>\n",
       "      <td>Rockpools</td>\n",
       "      <td>200</td>\n",
       "      <td>1538352117000</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) G...</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Five Iron Frenzy</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Micah</td>\n",
       "      <td>M</td>\n",
       "      <td>79</td>\n",
       "      <td>Long</td>\n",
       "      <td>236.09424</td>\n",
       "      <td>free</td>\n",
       "      <td>Boston-Cambridge-Newton, MA-NH</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1538331630000</td>\n",
       "      <td>8</td>\n",
       "      <td>Canada</td>\n",
       "      <td>200</td>\n",
       "      <td>1538352180000</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Adam Lambert</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Colin</td>\n",
       "      <td>M</td>\n",
       "      <td>51</td>\n",
       "      <td>Freeman</td>\n",
       "      <td>282.82730</td>\n",
       "      <td>paid</td>\n",
       "      <td>Bakersfield, CA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1538173362000</td>\n",
       "      <td>29</td>\n",
       "      <td>Time For Miracles</td>\n",
       "      <td>200</td>\n",
       "      <td>1538352394000</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) G...</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             artist       auth firstName gender  itemInSession lastName  \\\n",
       "0    Martha Tilston  Logged In     Colin      M             50  Freeman   \n",
       "1  Five Iron Frenzy  Logged In     Micah      M             79     Long   \n",
       "2      Adam Lambert  Logged In     Colin      M             51  Freeman   \n",
       "\n",
       "      length level                        location method      page  \\\n",
       "0  277.89016  paid                 Bakersfield, CA    PUT  NextSong   \n",
       "1  236.09424  free  Boston-Cambridge-Newton, MA-NH    PUT  NextSong   \n",
       "2  282.82730  paid                 Bakersfield, CA    PUT  NextSong   \n",
       "\n",
       "    registration  sessionId               song  status             ts  \\\n",
       "0  1538173362000         29          Rockpools     200  1538352117000   \n",
       "1  1538331630000          8             Canada     200  1538352180000   \n",
       "2  1538173362000         29  Time For Miracles     200  1538352394000   \n",
       "\n",
       "                                           userAgent userId  \n",
       "0  Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) G...     30  \n",
       "1  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...      9  \n",
       "2  Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) G...     30  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(events_df.take(3), columns=events_df.columns).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "278154"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}