data_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "from pyspark.sql import SparkSession\n",
    "import pyspark.sql.functions as F\n",
    "import boto3\n",
    "import configparser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup Spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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://5402627a7270:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v2.4.5</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>udacity_dend_p4</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7f20e380bb10>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('dl.cfg')\n",
    "udacity_bucket_name = \"udacity-dend\"\n",
    "aws_region_name = config.get(\"AWS\", \"REGION\")\n",
    "aws_access_key_id = config.get(\"AWS\", \"ACCESS_KEY_ID\")\n",
    "aws_access_key_secret = config.get(\"AWS\", \"SECRET_ACCESS_KEY\")\n",
    "s3 = boto3.resource(\n",
    "    \"s3\", region_name=aws_region_name,\n",
    "    aws_access_key_id=aws_access_key_id,\n",
    "    aws_secret_access_key=aws_access_key_secret\n",
    ")\n",
    "bucket = s3.Bucket(udacity_bucket_name)\n",
    "spark = SparkSession.builder \\\n",
    "    .master(\"local\") \\\n",
    "    .appName(\"udacity_dend_p4\") \\\n",
    "    .config(\"spark.sql.repl.eagerEval.enabled\", True) \\\n",
    "    .config(\"mapreduce.input.fileinputformat.input.dir.recursive\", True) \\\n",
    "    .config(\"spark.jars.packages\", \"org.apache.hadoop:hadoop-aws:2.7.0\") \\\n",
    "    .config(\"spark.hadoop.fs.s3a.impl\", \"org.apache.hadoop.fs.s3a.S3AFileSystem\") \\\n",
    "    .config(\"spark.hadoop.com.amazonaws.services.s3.enableV4\", \"true\") \\\n",
    "    .config(\"spark.hadoop.fs.s3a.access.key\", aws_access_key_id) \\\n",
    "    .config(\"spark.hadoop.fs.s3a.secret.key\", aws_access_key_secret) \\\n",
    "    .config(\"spark.hadoop.fs.s3a.connection.maximum\", \"100000\") \\\n",
    "    .getOrCreate()\n",
    "\n",
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read Log Data\n",
    "\n",
    "Reading all log data for analysis the data is filter by page = \"NextSong\" since that is the only data that is used in the Data Warehouse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>artist</th><th>auth</th><th>firstName</th><th>gender</th><th>itemInSession</th><th>lastName</th><th>length</th><th>level</th><th>location</th><th>method</th><th>page</th><th>registration</th><th>sessionId</th><th>song</th><th>status</th><th>ts</th><th>userAgent</th><th>userId</th></tr>\n",
       "<tr><td>Harmonia</td><td>Logged In</td><td>Ryan</td><td>M</td><td>0</td><td>Smith</td><td>655.77751</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>Sehr kosmisch</td><td>200</td><td>1542241826796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>The Prodigy</td><td>Logged In</td><td>Ryan</td><td>M</td><td>1</td><td>Smith</td><td>260.07465</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>The Big Gundown</td><td>200</td><td>1542242481796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>Train</td><td>Logged In</td><td>Ryan</td><td>M</td><td>2</td><td>Smith</td><td>205.45261</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>Marry Me</td><td>200</td><td>1542242741796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>null</td><td>Logged In</td><td>Wyatt</td><td>M</td><td>0</td><td>Scott</td><td>null</td><td>free</td><td>Eureka-Arcata-For...</td><td>GET</td><td>Home</td><td>1.540872073796E12</td><td>563</td><td>null</td><td>200</td><td>1542247071796</td><td>Mozilla/5.0 (Wind...</td><td>9</td></tr>\n",
       "<tr><td>null</td><td>Logged In</td><td>Austin</td><td>M</td><td>0</td><td>Rosales</td><td>null</td><td>free</td><td>New York-Newark-J...</td><td>GET</td><td>Home</td><td>1.541059521796E12</td><td>521</td><td>null</td><td>200</td><td>1542252577796</td><td>Mozilla/5.0 (Wind...</td><td>12</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+-----------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+---------------+------+-------------+--------------------+------+\n",
       "|     artist|     auth|firstName|gender|itemInSession|lastName|   length|level|            location|method|    page|     registration|sessionId|           song|status|           ts|           userAgent|userId|\n",
       "+-----------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+---------------+------+-------------+--------------------+------+\n",
       "|   Harmonia|Logged In|     Ryan|     M|            0|   Smith|655.77751| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|  Sehr kosmisch|   200|1542241826796|\"Mozilla/5.0 (X11...|    26|\n",
       "|The Prodigy|Logged In|     Ryan|     M|            1|   Smith|260.07465| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|The Big Gundown|   200|1542242481796|\"Mozilla/5.0 (X11...|    26|\n",
       "|      Train|Logged In|     Ryan|     M|            2|   Smith|205.45261| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|       Marry Me|   200|1542242741796|\"Mozilla/5.0 (X11...|    26|\n",
       "|       null|Logged In|    Wyatt|     M|            0|   Scott|     null| free|Eureka-Arcata-For...|   GET|    Home|1.540872073796E12|      563|           null|   200|1542247071796|Mozilla/5.0 (Wind...|     9|\n",
       "|       null|Logged In|   Austin|     M|            0| Rosales|     null| free|New York-Newark-J...|   GET|    Home|1.541059521796E12|      521|           null|   200|1542252577796|Mozilla/5.0 (Wind...|    12|\n",
       "+-----------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+---------------+------+-------------+--------------------+------+"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_events = spark.read.json(\"data/input/log-data/*.json\")\n",
    "df_events.limit(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data Schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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: double (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": [
    "df_events.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for null data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null checking:\n"
     ]
    },
    {
     "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>1236</td>\n",
       "      <td>0</td>\n",
       "      <td>286</td>\n",
       "      <td>286</td>\n",
       "      <td>0</td>\n",
       "      <td>286</td>\n",
       "      <td>1236</td>\n",
       "      <td>0</td>\n",
       "      <td>286</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>286</td>\n",
       "      <td>0</td>\n",
       "      <td>1236</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>286</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   artist  auth  firstName  gender  itemInSession  lastName  length  level  \\\n",
       "0    1236     0        286     286              0       286    1236      0   \n",
       "\n",
       "   location  method  page  registration  sessionId  song  status  ts  \\\n",
       "0       286       0     0           286          0  1236       0   0   \n",
       "\n",
       "   userAgent  userId  \n",
       "0        286       0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nan checking:\n"
     ]
    },
    {
     "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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   artist  auth  firstName  gender  itemInSession  lastName  length  level  \\\n",
       "0       0     0          0       0              0         0       0      0   \n",
       "\n",
       "   location  method  page  registration  sessionId  song  status  ts  \\\n",
       "0         0       0     0             0          0     0       0   0   \n",
       "\n",
       "   userAgent  userId  \n",
       "0          0       0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(\"Null checking:\")\n",
    "display(df_events.select([F.count(F.when(F.isnull(c), c)).alias(c) for c in df_events.columns]).toPandas())\n",
    "print(\"Nan checking:\")\n",
    "display(df_events.select([F.count(F.when(F.isnan(c), c)).alias(c) for c in df_events.columns]).toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for nan data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find page that are related to playing songs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>page</th><th>count</th></tr>\n",
       "<tr><td>NextSong</td><td>6820</td></tr>\n",
       "<tr><td>Home</td><td>806</td></tr>\n",
       "<tr><td>Login</td><td>92</td></tr>\n",
       "<tr><td>Logout</td><td>90</td></tr>\n",
       "<tr><td>Downgrade</td><td>60</td></tr>\n",
       "<tr><td>Settings</td><td>56</td></tr>\n",
       "<tr><td>Help</td><td>47</td></tr>\n",
       "<tr><td>About</td><td>36</td></tr>\n",
       "<tr><td>Upgrade</td><td>21</td></tr>\n",
       "<tr><td>Save Settings</td><td>10</td></tr>\n",
       "<tr><td>Error</td><td>9</td></tr>\n",
       "<tr><td>Submit Upgrade</td><td>8</td></tr>\n",
       "<tr><td>Submit Downgrade</td><td>1</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+----------------+-----+\n",
       "|            page|count|\n",
       "+----------------+-----+\n",
       "|        NextSong| 6820|\n",
       "|            Home|  806|\n",
       "|           Login|   92|\n",
       "|          Logout|   90|\n",
       "|       Downgrade|   60|\n",
       "|        Settings|   56|\n",
       "|            Help|   47|\n",
       "|           About|   36|\n",
       "|         Upgrade|   21|\n",
       "|   Save Settings|   10|\n",
       "|           Error|    9|\n",
       "|  Submit Upgrade|    8|\n",
       "|Submit Downgrade|    1|\n",
       "+----------------+-----+"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_events.groupBy([\"page\"]).count().orderBy(F.col(\"count\").desc())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove all non song playing records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>artist</th><th>auth</th><th>firstName</th><th>gender</th><th>itemInSession</th><th>lastName</th><th>length</th><th>level</th><th>location</th><th>method</th><th>page</th><th>registration</th><th>sessionId</th><th>song</th><th>status</th><th>ts</th><th>userAgent</th><th>userId</th></tr>\n",
       "<tr><td>Harmonia</td><td>Logged In</td><td>Ryan</td><td>M</td><td>0</td><td>Smith</td><td>655.77751</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>Sehr kosmisch</td><td>200</td><td>1542241826796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>The Prodigy</td><td>Logged In</td><td>Ryan</td><td>M</td><td>1</td><td>Smith</td><td>260.07465</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>The Big Gundown</td><td>200</td><td>1542242481796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>Train</td><td>Logged In</td><td>Ryan</td><td>M</td><td>2</td><td>Smith</td><td>205.45261</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>Marry Me</td><td>200</td><td>1542242741796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>Sony Wonder</td><td>Logged In</td><td>Samuel</td><td>M</td><td>0</td><td>Gonzalez</td><td>218.06975</td><td>free</td><td>Houston-The Woodl...</td><td>PUT</td><td>NextSong</td><td>1.540492941796E12</td><td>597</td><td>Blackbird</td><td>200</td><td>1542253449796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>61</td></tr>\n",
       "<tr><td>Van Halen</td><td>Logged In</td><td>Tegan</td><td>F</td><td>2</td><td>Levine</td><td>289.38404</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>Best Of Both Worl...</td><td>200</td><td>1542260935796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+-----------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+--------------------+------+-------------+--------------------+------+\n",
       "|     artist|     auth|firstName|gender|itemInSession|lastName|   length|level|            location|method|    page|     registration|sessionId|                song|status|           ts|           userAgent|userId|\n",
       "+-----------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+--------------------+------+-------------+--------------------+------+\n",
       "|   Harmonia|Logged In|     Ryan|     M|            0|   Smith|655.77751| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|       Sehr kosmisch|   200|1542241826796|\"Mozilla/5.0 (X11...|    26|\n",
       "|The Prodigy|Logged In|     Ryan|     M|            1|   Smith|260.07465| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|     The Big Gundown|   200|1542242481796|\"Mozilla/5.0 (X11...|    26|\n",
       "|      Train|Logged In|     Ryan|     M|            2|   Smith|205.45261| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|            Marry Me|   200|1542242741796|\"Mozilla/5.0 (X11...|    26|\n",
       "|Sony Wonder|Logged In|   Samuel|     M|            0|Gonzalez|218.06975| free|Houston-The Woodl...|   PUT|NextSong|1.540492941796E12|      597|           Blackbird|   200|1542253449796|\"Mozilla/5.0 (Mac...|    61|\n",
       "|  Van Halen|Logged In|    Tegan|     F|            2|  Levine|289.38404| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|Best Of Both Worl...|   200|1542260935796|\"Mozilla/5.0 (Mac...|    80|\n",
       "+-----------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+--------------------+------+-------------+--------------------+------+"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_staging_events = df_events.where(df_events.page==\"NextSong\")\n",
    "df_staging_events.limit(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>artist</th><th>auth</th><th>firstName</th><th>gender</th><th>itemInSession</th><th>lastName</th><th>length</th><th>level</th><th>location</th><th>method</th><th>page</th><th>registration</th><th>sessionId</th><th>song</th><th>status</th><th>ts</th><th>userAgent</th><th>userId</th></tr>\n",
       "<tr><td>Harmonia</td><td>Logged In</td><td>Ryan</td><td>M</td><td>0</td><td>Smith</td><td>655.77751</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>Sehr kosmisch</td><td>200</td><td>1542241826796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>The Prodigy</td><td>Logged In</td><td>Ryan</td><td>M</td><td>1</td><td>Smith</td><td>260.07465</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>The Big Gundown</td><td>200</td><td>1542242481796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>Train</td><td>Logged In</td><td>Ryan</td><td>M</td><td>2</td><td>Smith</td><td>205.45261</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>583</td><td>Marry Me</td><td>200</td><td>1542242741796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>Sony Wonder</td><td>Logged In</td><td>Samuel</td><td>M</td><td>0</td><td>Gonzalez</td><td>218.06975</td><td>free</td><td>Houston-The Woodl...</td><td>PUT</td><td>NextSong</td><td>1.540492941796E12</td><td>597</td><td>Blackbird</td><td>200</td><td>1542253449796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>61</td></tr>\n",
       "<tr><td>Van Halen</td><td>Logged In</td><td>Tegan</td><td>F</td><td>2</td><td>Levine</td><td>289.38404</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>Best Of Both Worl...</td><td>200</td><td>1542260935796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "<tr><td>Magic Sam</td><td>Logged In</td><td>Tegan</td><td>F</td><td>3</td><td>Levine</td><td>132.04853</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>Call Me If You Ne...</td><td>200</td><td>1542261224796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "<tr><td>Edward Sharpe &amp; T...</td><td>Logged In</td><td>Tegan</td><td>F</td><td>4</td><td>Levine</td><td>306.31138</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>Home</td><td>200</td><td>1542261356796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "<tr><td>Usher featuring w...</td><td>Logged In</td><td>Tegan</td><td>F</td><td>5</td><td>Levine</td><td>395.72853</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>OMG</td><td>200</td><td>1542261662796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "<tr><td>Helen Reddy</td><td>Logged In</td><td>Tegan</td><td>F</td><td>7</td><td>Levine</td><td>176.50893</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>Candle On The Water</td><td>200</td><td>1542262057796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "<tr><td>Taylor Swift</td><td>Logged In</td><td>Tegan</td><td>F</td><td>8</td><td>Levine</td><td>201.06404</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>Our Song</td><td>200</td><td>1542262233796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "<tr><td>Sean Paul</td><td>Logged In</td><td>Tegan</td><td>F</td><td>9</td><td>Levine</td><td>245.34159</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>Baby Boy [feat. B...</td><td>200</td><td>1542262434796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "<tr><td>Soundgarden</td><td>Logged In</td><td>Lily</td><td>F</td><td>0</td><td>Koch</td><td>272.19546</td><td>paid</td><td>Chicago-Napervill...</td><td>PUT</td><td>NextSong</td><td>1.541048010796E12</td><td>582</td><td>Black Hole Sun</td><td>200</td><td>1542262456796</td><td>&quot;Mozilla/5.0 (X11...</td><td>15</td></tr>\n",
       "<tr><td>The Killers</td><td>Logged In</td><td>Tegan</td><td>F</td><td>10</td><td>Levine</td><td>360.75057</td><td>paid</td><td>Portland-South Po...</td><td>PUT</td><td>NextSong</td><td>1.540794356796E12</td><td>602</td><td>Human</td><td>200</td><td>1542262679796</td><td>&quot;Mozilla/5.0 (Mac...</td><td>80</td></tr>\n",
       "<tr><td>Amy Winehouse</td><td>Logged In</td><td>Lily</td><td>F</td><td>1</td><td>Koch</td><td>165.11955</td><td>paid</td><td>Chicago-Napervill...</td><td>PUT</td><td>NextSong</td><td>1.541048010796E12</td><td>582</td><td>Addicted</td><td>200</td><td>1542262728796</td><td>&quot;Mozilla/5.0 (X11...</td><td>15</td></tr>\n",
       "<tr><td>Steve Anderson</td><td>Logged In</td><td>Lily</td><td>F</td><td>2</td><td>Koch</td><td>265.06404</td><td>paid</td><td>Chicago-Napervill...</td><td>PUT</td><td>NextSong</td><td>1.541048010796E12</td><td>582</td><td>Air</td><td>200</td><td>1542262893796</td><td>&quot;Mozilla/5.0 (X11...</td><td>15</td></tr>\n",
       "<tr><td>Rob Zombie</td><td>Logged In</td><td>Lily</td><td>F</td><td>3</td><td>Koch</td><td>220.13342</td><td>paid</td><td>Chicago-Napervill...</td><td>PUT</td><td>NextSong</td><td>1.541048010796E12</td><td>582</td><td>Superbeast</td><td>200</td><td>1542263158796</td><td>&quot;Mozilla/5.0 (X11...</td><td>15</td></tr>\n",
       "<tr><td>Deadmau5 &amp; Kaskade</td><td>Logged In</td><td>Lily</td><td>F</td><td>4</td><td>Koch</td><td>595.56526</td><td>paid</td><td>Chicago-Napervill...</td><td>PUT</td><td>NextSong</td><td>1.541048010796E12</td><td>582</td><td>I Remember</td><td>200</td><td>1542263378796</td><td>&quot;Mozilla/5.0 (X11...</td><td>15</td></tr>\n",
       "<tr><td>Shania Twain</td><td>Logged In</td><td>Ryan</td><td>M</td><td>0</td><td>Smith</td><td>213.7073</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>607</td><td>Don&#x27;t Be Stupid (...</td><td>200</td><td>1542265716796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>Los Campesinos</td><td>Logged In</td><td>Ryan</td><td>M</td><td>1</td><td>Smith</td><td>138.10893</td><td>free</td><td>San Jose-Sunnyval...</td><td>PUT</td><td>NextSong</td><td>1.541016707796E12</td><td>607</td><td>We throw parties_...</td><td>200</td><td>1542265929796</td><td>&quot;Mozilla/5.0 (X11...</td><td>26</td></tr>\n",
       "<tr><td>Ill Nino</td><td>Logged In</td><td>Chloe</td><td>F</td><td>1</td><td>Cuevas</td><td>188.9171</td><td>paid</td><td>San Francisco-Oak...</td><td>PUT</td><td>NextSong</td><td>1.540940782796E12</td><td>606</td><td>How Can I Live (S...</td><td>200</td><td>1542266927796</td><td>Mozilla/5.0 (Wind...</td><td>49</td></tr>\n",
       "</table>\n",
       "only showing top 20 rows\n"
      ],
      "text/plain": [
       "+--------------------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+--------------------+------+-------------+--------------------+------+\n",
       "|              artist|     auth|firstName|gender|itemInSession|lastName|   length|level|            location|method|    page|     registration|sessionId|                song|status|           ts|           userAgent|userId|\n",
       "+--------------------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+--------------------+------+-------------+--------------------+------+\n",
       "|            Harmonia|Logged In|     Ryan|     M|            0|   Smith|655.77751| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|       Sehr kosmisch|   200|1542241826796|\"Mozilla/5.0 (X11...|    26|\n",
       "|         The Prodigy|Logged In|     Ryan|     M|            1|   Smith|260.07465| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|     The Big Gundown|   200|1542242481796|\"Mozilla/5.0 (X11...|    26|\n",
       "|               Train|Logged In|     Ryan|     M|            2|   Smith|205.45261| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|            Marry Me|   200|1542242741796|\"Mozilla/5.0 (X11...|    26|\n",
       "|         Sony Wonder|Logged In|   Samuel|     M|            0|Gonzalez|218.06975| free|Houston-The Woodl...|   PUT|NextSong|1.540492941796E12|      597|           Blackbird|   200|1542253449796|\"Mozilla/5.0 (Mac...|    61|\n",
       "|           Van Halen|Logged In|    Tegan|     F|            2|  Levine|289.38404| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|Best Of Both Worl...|   200|1542260935796|\"Mozilla/5.0 (Mac...|    80|\n",
       "|           Magic Sam|Logged In|    Tegan|     F|            3|  Levine|132.04853| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|Call Me If You Ne...|   200|1542261224796|\"Mozilla/5.0 (Mac...|    80|\n",
       "|Edward Sharpe & T...|Logged In|    Tegan|     F|            4|  Levine|306.31138| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|                Home|   200|1542261356796|\"Mozilla/5.0 (Mac...|    80|\n",
       "|Usher featuring w...|Logged In|    Tegan|     F|            5|  Levine|395.72853| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|                 OMG|   200|1542261662796|\"Mozilla/5.0 (Mac...|    80|\n",
       "|         Helen Reddy|Logged In|    Tegan|     F|            7|  Levine|176.50893| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602| Candle On The Water|   200|1542262057796|\"Mozilla/5.0 (Mac...|    80|\n",
       "|        Taylor Swift|Logged In|    Tegan|     F|            8|  Levine|201.06404| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|            Our Song|   200|1542262233796|\"Mozilla/5.0 (Mac...|    80|\n",
       "|           Sean Paul|Logged In|    Tegan|     F|            9|  Levine|245.34159| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|Baby Boy [feat. B...|   200|1542262434796|\"Mozilla/5.0 (Mac...|    80|\n",
       "|         Soundgarden|Logged In|     Lily|     F|            0|    Koch|272.19546| paid|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|      Black Hole Sun|   200|1542262456796|\"Mozilla/5.0 (X11...|    15|\n",
       "|         The Killers|Logged In|    Tegan|     F|           10|  Levine|360.75057| paid|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|               Human|   200|1542262679796|\"Mozilla/5.0 (Mac...|    80|\n",
       "|       Amy Winehouse|Logged In|     Lily|     F|            1|    Koch|165.11955| paid|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|            Addicted|   200|1542262728796|\"Mozilla/5.0 (X11...|    15|\n",
       "|      Steve Anderson|Logged In|     Lily|     F|            2|    Koch|265.06404| paid|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|                 Air|   200|1542262893796|\"Mozilla/5.0 (X11...|    15|\n",
       "|          Rob Zombie|Logged In|     Lily|     F|            3|    Koch|220.13342| paid|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|          Superbeast|   200|1542263158796|\"Mozilla/5.0 (X11...|    15|\n",
       "|  Deadmau5 & Kaskade|Logged In|     Lily|     F|            4|    Koch|595.56526| paid|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|          I Remember|   200|1542263378796|\"Mozilla/5.0 (X11...|    15|\n",
       "|        Shania Twain|Logged In|     Ryan|     M|            0|   Smith| 213.7073| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      607|Don't Be Stupid (...|   200|1542265716796|\"Mozilla/5.0 (X11...|    26|\n",
       "|      Los Campesinos|Logged In|     Ryan|     M|            1|   Smith|138.10893| free|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      607|We throw parties_...|   200|1542265929796|\"Mozilla/5.0 (X11...|    26|\n",
       "|            Ill Nino|Logged In|    Chloe|     F|            1|  Cuevas| 188.9171| paid|San Francisco-Oak...|   PUT|NextSong|1.540940782796E12|      606|How Can I Live (S...|   200|1542266927796|Mozilla/5.0 (Wind...|    49|\n",
       "+--------------------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-----------------+---------+--------------------+------+-------------+--------------------+------+\n",
       "only showing top 20 rows"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_staging_events.where(df_staging_events.userId == \"29\")\n",
    "df_staging_events.limit(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### users dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>userId</th><th>firstName</th><th>lastName</th><th>gender</th><th>level</th></tr>\n",
       "<tr><td>5</td><td>Elijah</td><td>Davis</td><td>M</td><td>free</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>49</td><td>Chloe</td><td>Cuevas</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>49</td><td>Chloe</td><td>Cuevas</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>49</td><td>Chloe</td><td>Cuevas</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>49</td><td>Chloe</td><td>Cuevas</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>49</td><td>Chloe</td><td>Cuevas</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>91</td><td>Jayden</td><td>Bell</td><td>M</td><td>free</td></tr>\n",
       "<tr><td>49</td><td>Chloe</td><td>Cuevas</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>91</td><td>Jayden</td><td>Bell</td><td>M</td><td>free</td></tr>\n",
       "<tr><td>16</td><td>Rylan</td><td>George</td><td>M</td><td>paid</td></tr>\n",
       "</table>\n",
       "only showing top 20 rows\n"
      ],
      "text/plain": [
       "+------+---------+--------+------+-----+\n",
       "|userId|firstName|lastName|gender|level|\n",
       "+------+---------+--------+------+-----+\n",
       "|     5|   Elijah|   Davis|     M| free|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    49|    Chloe|  Cuevas|     F| paid|\n",
       "|    49|    Chloe|  Cuevas|     F| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    49|    Chloe|  Cuevas|     F| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    49|    Chloe|  Cuevas|     F| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    49|    Chloe|  Cuevas|     F| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    91|   Jayden|    Bell|     M| free|\n",
       "|    49|    Chloe|  Cuevas|     F| paid|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "|    91|   Jayden|    Bell|     M| free|\n",
       "|    16|    Rylan|  George|     M| paid|\n",
       "+------+---------+--------+------+-----+\n",
       "only showing top 20 rows"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user = df_staging_events.select(\n",
    "    \"userId\", \"firstName\", \"lastName\", \"gender\", \"level\"\n",
    ").orderBy(F.col(\"ts\").desc())\n",
    "df_user.limit(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>userId</th><th>firstName</th><th>lastName</th><th>gender</th><th>level</th></tr>\n",
       "<tr><td>52</td><td>Theodore</td><td>Smith</td><td>M</td><td>free</td></tr>\n",
       "<tr><td>48</td><td>Marina</td><td>Sutton</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>47</td><td>Kimber</td><td>Norris</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>25</td><td>Jayden</td><td>Graves</td><td>M</td><td>paid</td></tr>\n",
       "<tr><td>87</td><td>Dustin</td><td>Lee</td><td>M</td><td>free</td></tr>\n",
       "<tr><td>65</td><td>Amiya</td><td>Davidson</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>10</td><td>Sylvie</td><td>Cruz</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>84</td><td>Shakira</td><td>Hunt</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>71</td><td>Ayleen</td><td>Wise</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>43</td><td>Jahiem</td><td>Miles</td><td>M</td><td>free</td></tr>\n",
       "<tr><td>82</td><td>Avery</td><td>Martinez</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>86</td><td>Aiden</td><td>Hess</td><td>M</td><td>free</td></tr>\n",
       "<tr><td>96</td><td>Cierra</td><td>Finley</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>58</td><td>Emily</td><td>Benson</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>101</td><td>Jayden</td><td>Fox</td><td>M</td><td>free</td></tr>\n",
       "<tr><td>95</td><td>Sara</td><td>Johnson</td><td>F</td><td>paid</td></tr>\n",
       "<tr><td>22</td><td>Sean</td><td>Wilson</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>69</td><td>Anabelle</td><td>Simpson</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>11</td><td>Christian</td><td>Porter</td><td>F</td><td>free</td></tr>\n",
       "<tr><td>45</td><td>Dominick</td><td>Norris</td><td>M</td><td>free</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+------+---------+--------+------+-----+\n",
       "|userId|firstName|lastName|gender|level|\n",
       "+------+---------+--------+------+-----+\n",
       "|    52| Theodore|   Smith|     M| free|\n",
       "|    48|   Marina|  Sutton|     F| free|\n",
       "|    47|   Kimber|  Norris|     F| free|\n",
       "|    25|   Jayden|  Graves|     M| paid|\n",
       "|    87|   Dustin|     Lee|     M| free|\n",
       "|    65|    Amiya|Davidson|     F| paid|\n",
       "|    10|   Sylvie|    Cruz|     F| free|\n",
       "|    84|  Shakira|    Hunt|     F| free|\n",
       "|    71|   Ayleen|    Wise|     F| free|\n",
       "|    43|   Jahiem|   Miles|     M| free|\n",
       "|    82|    Avery|Martinez|     F| paid|\n",
       "|    86|    Aiden|    Hess|     M| free|\n",
       "|    96|   Cierra|  Finley|     F| free|\n",
       "|    58|    Emily|  Benson|     F| paid|\n",
       "|   101|   Jayden|     Fox|     M| free|\n",
       "|    95|     Sara| Johnson|     F| paid|\n",
       "|    22|     Sean|  Wilson|     F| free|\n",
       "|    69| Anabelle| Simpson|     F| free|\n",
       "|    11|Christian|  Porter|     F| free|\n",
       "|    45| Dominick|  Norris|     M| free|\n",
       "+------+---------+--------+------+-----+"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user = df_user.dropDuplicates((\"userId\", \"firstName\", \"lastName\", \"gender\"))\n",
    "df_user.limit(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for null data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null checking:\n"
     ]
    },
    {
     "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>userId</th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>gender</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userId  firstName  lastName  gender  level\n",
       "0       0          0         0       0      0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nan checking:\n"
     ]
    },
    {
     "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>userId</th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>gender</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userId  firstName  lastName  gender  level\n",
       "0       0          0         0       0      0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(\"Null checking:\")\n",
    "display(df_user.select([F.count(F.when(F.isnull(c), c)).alias(c) for c in df_user.columns]).toPandas())\n",
    "print(\"Nan checking:\")\n",
    "display(df_user.select([F.count(F.when(F.isnan(c), c)).alias(c) for c in df_user.columns]).toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check Data Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- userId: string (nullable = true)\n",
      " |-- firstName: string (nullable = true)\n",
      " |-- lastName: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- level: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_user.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>userId</th><th>count</th></tr>\n",
       "<tr><td>51</td><td>1</td></tr>\n",
       "<tr><td>7</td><td>1</td></tr>\n",
       "<tr><td>15</td><td>1</td></tr>\n",
       "<tr><td>54</td><td>1</td></tr>\n",
       "<tr><td>101</td><td>1</td></tr>\n",
       "<tr><td>11</td><td>1</td></tr>\n",
       "<tr><td>69</td><td>1</td></tr>\n",
       "<tr><td>29</td><td>1</td></tr>\n",
       "<tr><td>42</td><td>1</td></tr>\n",
       "<tr><td>87</td><td>1</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+------+-----+\n",
       "|userId|count|\n",
       "+------+-----+\n",
       "|    51|    1|\n",
       "|     7|    1|\n",
       "|    15|    1|\n",
       "|    54|    1|\n",
       "|   101|    1|\n",
       "|    11|    1|\n",
       "|    69|    1|\n",
       "|    29|    1|\n",
       "|    42|    1|\n",
       "|    87|    1|\n",
       "+------+-----+"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user.groupBy(\"userId\").count().orderBy(F.col(\"count\").desc()).limit(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>userId</th><th>count</th></tr>\n",
       "<tr><td>51</td><td>1</td></tr>\n",
       "<tr><td>7</td><td>1</td></tr>\n",
       "<tr><td>15</td><td>1</td></tr>\n",
       "<tr><td>54</td><td>1</td></tr>\n",
       "<tr><td>101</td><td>1</td></tr>\n",
       "<tr><td>11</td><td>1</td></tr>\n",
       "<tr><td>69</td><td>1</td></tr>\n",
       "<tr><td>29</td><td>1</td></tr>\n",
       "<tr><td>42</td><td>1</td></tr>\n",
       "<tr><td>87</td><td>1</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+------+-----+\n",
       "|userId|count|\n",
       "+------+-----+\n",
       "|    51|    1|\n",
       "|     7|    1|\n",
       "|    15|    1|\n",
       "|    54|    1|\n",
       "|   101|    1|\n",
       "|    11|    1|\n",
       "|    69|    1|\n",
       "|    29|    1|\n",
       "|    42|    1|\n",
       "|    87|    1|\n",
       "+------+-----+"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user = df_user.dropDuplicates()\n",
    "df_user.groupBy(\"userId\").count().orderBy(F.col(\"count\").desc()).limit(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>userId</th><th>firstName</th><th>lastName</th><th>gender</th><th>level</th></tr>\n",
       "<tr><td>15</td><td>Lily</td><td>Koch</td><td>F</td><td>paid</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+------+---------+--------+------+-----+\n",
       "|userId|firstName|lastName|gender|level|\n",
       "+------+---------+--------+------+-----+\n",
       "|    15|     Lily|    Koch|     F| paid|\n",
       "+------+---------+--------+------+-----+"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user.where(df_user.userId == 15).limit(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### time dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>ts</th></tr>\n",
       "<tr><td>1542241826796</td></tr>\n",
       "<tr><td>1542242481796</td></tr>\n",
       "<tr><td>1542242741796</td></tr>\n",
       "<tr><td>1542253449796</td></tr>\n",
       "<tr><td>1542260935796</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+-------------+\n",
       "|           ts|\n",
       "+-------------+\n",
       "|1542241826796|\n",
       "|1542242481796|\n",
       "|1542242741796|\n",
       "|1542253449796|\n",
       "|1542260935796|\n",
       "+-------------+"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time = df_staging_events.select(\"ts\")\n",
    "df_time.limit(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for null data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null checking:\n"
     ]
    },
    {
     "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>ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ts\n",
       "0   0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nan checking:\n"
     ]
    },
    {
     "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>ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ts\n",
       "0   0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(\"Null checking:\")\n",
    "display(df_time.select([F.count(F.when(F.isnull(c), c)).alias(c) for c in df_time.columns]).toPandas())\n",
    "print(\"Nan checking:\")\n",
    "display(df_time.select([F.count(F.when(F.isnan(c), c)).alias(c) for c in df_time.columns]).toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>ts</th><th>count</th></tr>\n",
       "<tr><td>1542308104796</td><td>2</td></tr>\n",
       "<tr><td>1543339730796</td><td>2</td></tr>\n",
       "<tr><td>1542171216796</td><td>2</td></tr>\n",
       "<tr><td>1543435163796</td><td>2</td></tr>\n",
       "<tr><td>1543069787796</td><td>2</td></tr>\n",
       "<tr><td>1542984111796</td><td>2</td></tr>\n",
       "<tr><td>1543422975796</td><td>2</td></tr>\n",
       "<tr><td>1542795222796</td><td>1</td></tr>\n",
       "<tr><td>1542798065796</td><td>1</td></tr>\n",
       "<tr><td>1542825241796</td><td>1</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+-------------+-----+\n",
       "|           ts|count|\n",
       "+-------------+-----+\n",
       "|1542308104796|    2|\n",
       "|1543339730796|    2|\n",
       "|1542171216796|    2|\n",
       "|1543435163796|    2|\n",
       "|1543069787796|    2|\n",
       "|1542984111796|    2|\n",
       "|1543422975796|    2|\n",
       "|1542795222796|    1|\n",
       "|1542798065796|    1|\n",
       "|1542825241796|    1|\n",
       "+-------------+-----+"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time.groupBy(\"ts\").count().orderBy(F.col(\"count\").desc()).limit(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### songs staging table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>artist_id</th><th>artist_latitude</th><th>artist_location</th><th>artist_longitude</th><th>artist_name</th><th>duration</th><th>num_songs</th><th>song_id</th><th>title</th><th>year</th></tr>\n",
       "<tr><td>ARDR4AC1187FB371A1</td><td>null</td><td></td><td>null</td><td>Montserrat Caball...</td><td>511.16363</td><td>1</td><td>SOBAYLL12A8C138AF9</td><td>Sono andati? Fing...</td><td>0</td></tr>\n",
       "<tr><td>AREBBGV1187FB523D2</td><td>null</td><td>Houston, TX</td><td>null</td><td>Mike Jones (Featu...</td><td>173.66159</td><td>1</td><td>SOOLYAZ12A6701F4A6</td><td>Laws Patrolling (...</td><td>0</td></tr>\n",
       "<tr><td>ARMAC4T1187FB3FA4C</td><td>40.82624</td><td>Morris Plains, NJ</td><td>-74.47995</td><td>The Dillinger Esc...</td><td>207.77751</td><td>1</td><td>SOBBUGU12A8C13E95D</td><td>Setting Fire to S...</td><td>2004</td></tr>\n",
       "<tr><td>ARPBNLO1187FB3D52F</td><td>40.71455</td><td>New York, NY</td><td>-74.00712</td><td>Tiny Tim</td><td>43.36281</td><td>1</td><td>SOAOIBZ12AB01815BE</td><td>I Hold Your Hand ...</td><td>2000</td></tr>\n",
       "<tr><td>ARDNS031187B9924F0</td><td>32.67828</td><td>Georgia</td><td>-83.22295</td><td>Tim Wilson</td><td>186.48771</td><td>1</td><td>SONYPOM12A8C13B2D7</td><td>I Think My Wife I...</td><td>2005</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+------------------+---------------+-----------------+----------------+--------------------+---------+---------+------------------+--------------------+----+\n",
       "|         artist_id|artist_latitude|  artist_location|artist_longitude|         artist_name| duration|num_songs|           song_id|               title|year|\n",
       "+------------------+---------------+-----------------+----------------+--------------------+---------+---------+------------------+--------------------+----+\n",
       "|ARDR4AC1187FB371A1|           null|                 |            null|Montserrat Caball...|511.16363|        1|SOBAYLL12A8C138AF9|Sono andati? Fing...|   0|\n",
       "|AREBBGV1187FB523D2|           null|      Houston, TX|            null|Mike Jones (Featu...|173.66159|        1|SOOLYAZ12A6701F4A6|Laws Patrolling (...|   0|\n",
       "|ARMAC4T1187FB3FA4C|       40.82624|Morris Plains, NJ|       -74.47995|The Dillinger Esc...|207.77751|        1|SOBBUGU12A8C13E95D|Setting Fire to S...|2004|\n",
       "|ARPBNLO1187FB3D52F|       40.71455|     New York, NY|       -74.00712|            Tiny Tim| 43.36281|        1|SOAOIBZ12AB01815BE|I Hold Your Hand ...|2000|\n",
       "|ARDNS031187B9924F0|       32.67828|          Georgia|       -83.22295|          Tim Wilson|186.48771|        1|SONYPOM12A8C13B2D7|I Think My Wife I...|2005|\n",
       "+------------------+---------------+-----------------+----------------+--------------------+---------+---------+------------------+--------------------+----+"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_song_staging = spark.read.json(\"data/input/song_data/*/*/*\")\n",
    "df_song_staging.limit(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for null data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null checking:\n"
     ]
    },
    {
     "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_id</th>\n",
       "      <th>artist_latitude</th>\n",
       "      <th>artist_location</th>\n",
       "      <th>artist_longitude</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>num_songs</th>\n",
       "      <th>song_id</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   artist_id  artist_latitude  artist_location  artist_longitude  artist_name  \\\n",
       "0          0               40                0                40            0   \n",
       "\n",
       "   duration  num_songs  song_id  title  year  \n",
       "0         0          0        0      0     0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nan checking:\n"
     ]
    },
    {
     "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_id</th>\n",
       "      <th>artist_latitude</th>\n",
       "      <th>artist_location</th>\n",
       "      <th>artist_longitude</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>num_songs</th>\n",
       "      <th>song_id</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   artist_id  artist_latitude  artist_location  artist_longitude  artist_name  \\\n",
       "0          0                0                0                 0            0   \n",
       "\n",
       "   duration  num_songs  song_id  title  year  \n",
       "0         0          0        0      0     0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(\"Null checking:\")\n",
    "display(df_song_staging.select([F.count(F.when(F.isnull(c), c)).alias(c) for c in df_song_staging.columns]).toPandas())\n",
    "print(\"Nan checking:\")\n",
    "display(df_song_staging.select([F.count(F.when(F.isnan(c), c)).alias(c) for c in df_song_staging.columns]).toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check Data Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- artist_id: string (nullable = true)\n",
      " |-- artist_latitude: double (nullable = true)\n",
      " |-- artist_location: string (nullable = true)\n",
      " |-- artist_longitude: double (nullable = true)\n",
      " |-- artist_name: string (nullable = true)\n",
      " |-- duration: double (nullable = true)\n",
      " |-- num_songs: long (nullable = true)\n",
      " |-- song_id: string (nullable = true)\n",
      " |-- title: string (nullable = true)\n",
      " |-- year: long (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_song_staging.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### songs dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>song_id</th><th>title</th><th>artist_id</th><th>year</th><th>duration</th></tr>\n",
       "<tr><td>SOGOSOV12AF72A285E</td><td>¿Dónde va Chichi?</td><td>ARGUVEV1187B98BA17</td><td>1997</td><td>313.12934</td></tr>\n",
       "<tr><td>SOTTDKS12AB018D69B</td><td>It Wont Be Christmas</td><td>ARMBR4Y1187B9990EB</td><td>0</td><td>241.47546</td></tr>\n",
       "<tr><td>SOBBUGU12A8C13E95D</td><td>Setting Fire to S...</td><td>ARMAC4T1187FB3FA4C</td><td>2004</td><td>207.77751</td></tr>\n",
       "<tr><td>SOIAZJW12AB01853F1</td><td>Pink World</td><td>AR8ZCNI1187B9A069B</td><td>1984</td><td>269.81832</td></tr>\n",
       "<tr><td>SONYPOM12A8C13B2D7</td><td>I Think My Wife I...</td><td>ARDNS031187B9924F0</td><td>2005</td><td>186.48771</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+------------------+--------------------+------------------+----+---------+\n",
       "|           song_id|               title|         artist_id|year| duration|\n",
       "+------------------+--------------------+------------------+----+---------+\n",
       "|SOGOSOV12AF72A285E|   ¿Dónde va Chichi?|ARGUVEV1187B98BA17|1997|313.12934|\n",
       "|SOTTDKS12AB018D69B|It Wont Be Christmas|ARMBR4Y1187B9990EB|   0|241.47546|\n",
       "|SOBBUGU12A8C13E95D|Setting Fire to S...|ARMAC4T1187FB3FA4C|2004|207.77751|\n",
       "|SOIAZJW12AB01853F1|          Pink World|AR8ZCNI1187B9A069B|1984|269.81832|\n",
       "|SONYPOM12A8C13B2D7|I Think My Wife I...|ARDNS031187B9924F0|2005|186.48771|\n",
       "+------------------+--------------------+------------------+----+---------+"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_song = df_song_staging.select(\"song_id\", \"title\", \"artist_id\", \"year\", \"duration\").dropDuplicates()\n",
    "df_song.limit(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for null data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null checking:\n"
     ]
    },
    {
     "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>song_id</th>\n",
       "      <th>title</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>year</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   song_id  title  artist_id  year  duration\n",
       "0        0      0          0     0         0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nan checking:\n"
     ]
    },
    {
     "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>song_id</th>\n",
       "      <th>title</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>year</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   song_id  title  artist_id  year  duration\n",
       "0        0      0          0     0         0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(\"Null checking:\")\n",
    "display(df_song.select([F.count(F.when(F.isnull(c), c)).alias(c) for c in df_song.columns]).toPandas())\n",
    "print(\"Nan checking:\")\n",
    "display(df_song.select([F.count(F.when(F.isnan(c), c)).alias(c) for c in df_song.columns]).toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check Data Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- song_id: string (nullable = true)\n",
      " |-- title: string (nullable = true)\n",
      " |-- artist_id: string (nullable = true)\n",
      " |-- year: long (nullable = true)\n",
      " |-- duration: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_song.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>song_id</th><th>count</th></tr>\n",
       "<tr><td>SOGOSOV12AF72A285E</td><td>1</td></tr>\n",
       "<tr><td>SOMZWCG12A8C13C480</td><td>1</td></tr>\n",
       "<tr><td>SOUPIRU12A6D4FA1E1</td><td>1</td></tr>\n",
       "<tr><td>SOXVLOJ12AB0189215</td><td>1</td></tr>\n",
       "<tr><td>SOWTBJW12AC468AC6E</td><td>1</td></tr>\n",
       "<tr><td>SOBONFF12A6D4F84D8</td><td>1</td></tr>\n",
       "<tr><td>SOPVXLX12A8C1402D5</td><td>1</td></tr>\n",
       "<tr><td>SOAOIBZ12AB01815BE</td><td>1</td></tr>\n",
       "<tr><td>SOBKWDJ12A8C13B2F3</td><td>1</td></tr>\n",
       "<tr><td>SONSKXP12A8C13A2C9</td><td>1</td></tr>\n",
       "<tr><td>SOPSWQW12A6D4F8781</td><td>1</td></tr>\n",
       "<tr><td>SOOLYAZ12A6701F4A6</td><td>1</td></tr>\n",
       "<tr><td>SOUDSGM12AC9618304</td><td>1</td></tr>\n",
       "<tr><td>SOQVMXR12A81C21483</td><td>1</td></tr>\n",
       "<tr><td>SOTCKKY12AB018A141</td><td>1</td></tr>\n",
       "<tr><td>SOBCOSW12A8C13D398</td><td>1</td></tr>\n",
       "<tr><td>SOZCTXZ12AB0182364</td><td>1</td></tr>\n",
       "<tr><td>SOLLHMX12AB01846DC</td><td>1</td></tr>\n",
       "<tr><td>SOFFKZS12AB017F194</td><td>1</td></tr>\n",
       "<tr><td>SOTUKVB12AB0181477</td><td>1</td></tr>\n",
       "</table>\n",
       "only showing top 20 rows\n"
      ],
      "text/plain": [
       "+------------------+-----+\n",
       "|           song_id|count|\n",
       "+------------------+-----+\n",
       "|SOGOSOV12AF72A285E|    1|\n",
       "|SOMZWCG12A8C13C480|    1|\n",
       "|SOUPIRU12A6D4FA1E1|    1|\n",
       "|SOXVLOJ12AB0189215|    1|\n",
       "|SOWTBJW12AC468AC6E|    1|\n",
       "|SOBONFF12A6D4F84D8|    1|\n",
       "|SOPVXLX12A8C1402D5|    1|\n",
       "|SOAOIBZ12AB01815BE|    1|\n",
       "|SOBKWDJ12A8C13B2F3|    1|\n",
       "|SONSKXP12A8C13A2C9|    1|\n",
       "|SOPSWQW12A6D4F8781|    1|\n",
       "|SOOLYAZ12A6701F4A6|    1|\n",
       "|SOUDSGM12AC9618304|    1|\n",
       "|SOQVMXR12A81C21483|    1|\n",
       "|SOTCKKY12AB018A141|    1|\n",
       "|SOBCOSW12A8C13D398|    1|\n",
       "|SOZCTXZ12AB0182364|    1|\n",
       "|SOLLHMX12AB01846DC|    1|\n",
       "|SOFFKZS12AB017F194|    1|\n",
       "|SOTUKVB12AB0181477|    1|\n",
       "+------------------+-----+\n",
       "only showing top 20 rows"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_song.groupBy(\"song_id\").count().orderBy(F.col(\"count\").desc())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### artists dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>artist_id</th><th>name</th><th>location</th><th>latitude</th><th>longitude</th></tr>\n",
       "<tr><td>ARPBNLO1187FB3D52F</td><td>Tiny Tim</td><td>New York, NY</td><td>40.71455</td><td>-74.00712</td></tr>\n",
       "<tr><td>ARXR32B1187FB57099</td><td>Gob</td><td></td><td>null</td><td>null</td></tr>\n",
       "<tr><td>AROGWRA122988FEE45</td><td>Christos Dantis</td><td></td><td>null</td><td>null</td></tr>\n",
       "<tr><td>ARBGXIG122988F409D</td><td>Steel Rain</td><td>California - SF</td><td>37.77916</td><td>-122.42005</td></tr>\n",
       "<tr><td>AREVWGE1187B9B890A</td><td>Bitter End</td><td>Noci (BA)</td><td>-13.442</td><td>-41.9952</td></tr>\n",
       "</table>\n"
      ],
      "text/plain": [
       "+------------------+---------------+---------------+--------+----------+\n",
       "|         artist_id|           name|       location|latitude| longitude|\n",
       "+------------------+---------------+---------------+--------+----------+\n",
       "|ARPBNLO1187FB3D52F|       Tiny Tim|   New York, NY|40.71455| -74.00712|\n",
       "|ARXR32B1187FB57099|            Gob|               |    null|      null|\n",
       "|AROGWRA122988FEE45|Christos Dantis|               |    null|      null|\n",
       "|ARBGXIG122988F409D|     Steel Rain|California - SF|37.77916|-122.42005|\n",
       "|AREVWGE1187B9B890A|     Bitter End|      Noci (BA)| -13.442|  -41.9952|\n",
       "+------------------+---------------+---------------+--------+----------+"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_artist = df_song_staging.select(\n",
    "    df_song_staging.artist_id,\n",
    "    df_song_staging.artist_name.alias('name'),\n",
    "    df_song_staging.artist_location.alias('location'),\n",
    "    df_song_staging.artist_latitude.alias('latitude'),\n",
    "    df_song_staging.artist_longitude.alias('longitude')    \n",
    ").dropDuplicates()\n",
    "df_artist.limit(5)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for null data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null checking:\n"
     ]
    },
    {
     "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_id</th>\n",
       "      <th>name</th>\n",
       "      <th>location</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   artist_id  name  location  latitude  longitude\n",
       "0          0     0         0        38         38"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nan checking:\n"
     ]
    },
    {
     "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_id</th>\n",
       "      <th>name</th>\n",
       "      <th>location</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   artist_id  name  location  latitude  longitude\n",
       "0          0     0         0         0          0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(\"Null checking:\")\n",
    "display(df_artist.select([F.count(F.when(F.isnull(c), c)).alias(c) for c in df_artist.columns]).toPandas())\n",
    "print(\"Nan checking:\")\n",
    "display(df_artist.select([F.count(F.when(F.isnan(c), c)).alias(c) for c in df_artist.columns]).toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check Data Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- artist_id: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- latitude: double (nullable = true)\n",
      " |-- longitude: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_artist.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>artist_id</th><th>count</th></tr>\n",
       "<tr><td>AR9AWNF1187B9AB0B4</td><td>1</td></tr>\n",
       "<tr><td>AR0IAWL1187B9A96D0</td><td>1</td></tr>\n",
       "<tr><td>AREDL271187FB40F44</td><td>1</td></tr>\n",
       "<tr><td>AR0RCMP1187FB3F427</td><td>1</td></tr>\n",
       "<tr><td>ARI3BMM1187FB4255E</td><td>1</td></tr>\n",
       "<tr><td>AR7SMBG1187B9B9066</td><td>1</td></tr>\n",
       "<tr><td>ARMAC4T1187FB3FA4C</td><td>1</td></tr>\n",
       "<tr><td>ARNTLGG11E2835DDB9</td><td>1</td></tr>\n",
       "<tr><td>ARKRRTF1187B9984DA</td><td>1</td></tr>\n",
       "<tr><td>AR051KA1187B98B2FF</td><td>1</td></tr>\n",
       "<tr><td>AR7ZKHQ1187B98DD73</td><td>1</td></tr>\n",
       "<tr><td>AR10USD1187B99F3F1</td><td>1</td></tr>\n",
       "<tr><td>ARQ9BO41187FB5CF1F</td><td>1</td></tr>\n",
       "<tr><td>AR7G5I41187FB4CE6C</td><td>1</td></tr>\n",
       "<tr><td>ARGCY1Y1187B9A4FA5</td><td>1</td></tr>\n",
       "<tr><td>ARAJPHH1187FB5566A</td><td>1</td></tr>\n",
       "<tr><td>AR558FS1187FB45658</td><td>1</td></tr>\n",
       "<tr><td>ARKFYS91187B98E58F</td><td>1</td></tr>\n",
       "<tr><td>ARD842G1187B997376</td><td>1</td></tr>\n",
       "<tr><td>ARJIE2Y1187B994AB7</td><td>1</td></tr>\n",
       "</table>\n",
       "only showing top 20 rows\n"
      ],
      "text/plain": [
       "+------------------+-----+\n",
       "|         artist_id|count|\n",
       "+------------------+-----+\n",
       "|AR9AWNF1187B9AB0B4|    1|\n",
       "|AR0IAWL1187B9A96D0|    1|\n",
       "|AREDL271187FB40F44|    1|\n",
       "|AR0RCMP1187FB3F427|    1|\n",
       "|ARI3BMM1187FB4255E|    1|\n",
       "|AR7SMBG1187B9B9066|    1|\n",
       "|ARMAC4T1187FB3FA4C|    1|\n",
       "|ARNTLGG11E2835DDB9|    1|\n",
       "|ARKRRTF1187B9984DA|    1|\n",
       "|AR051KA1187B98B2FF|    1|\n",
       "|AR7ZKHQ1187B98DD73|    1|\n",
       "|AR10USD1187B99F3F1|    1|\n",
       "|ARQ9BO41187FB5CF1F|    1|\n",
       "|AR7G5I41187FB4CE6C|    1|\n",
       "|ARGCY1Y1187B9A4FA5|    1|\n",
       "|ARAJPHH1187FB5566A|    1|\n",
       "|AR558FS1187FB45658|    1|\n",
       "|ARKFYS91187B98E58F|    1|\n",
       "|ARD842G1187B997376|    1|\n",
       "|ARJIE2Y1187B994AB7|    1|\n",
       "+------------------+-----+\n",
       "only showing top 20 rows"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_artist.groupBy(\"artist_id\").count().orderBy(F.col(\"count\").desc())"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}