etl.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Processes\n",
    "Use this notebook to develop the ETL process for each of your tables before completing the `etl.py` file to load the whole datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "from sql_queries import *\n",
    "\n",
    "# To avoid `psycopg2.ProgrammingError: can't adapt type 'numpy.int64'` when\n",
    "# executing the INSERT command:\n",
    "from numpy import int64\n",
    "psycopg2.extensions.register_adapter(int64, psycopg2._psycopg.AsIs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"host=127.0.0.1 dbname=sparkifydb user=student password=student\")\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_files(filepath):\n",
    "    all_files = []\n",
    "    for root, dirs, files in os.walk(filepath):\n",
    "        files = glob.glob(os.path.join(root,'*.json'))\n",
    "        for f in files :\n",
    "            all_files.append(os.path.abspath(f))\n",
    "    \n",
    "    return all_files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process `song_data`\n",
    "In this first part, you'll perform ETL on the first dataset, `song_data`, to create the `songs` and `artists` dimensional tables.\n",
    "\n",
    "Let's perform ETL on a single song file and load a single record into each table to start.\n",
    "- Use the `get_files` function provided above to get a list of all song JSON files in `data/song_data`\n",
    "- Select the first song in this list\n",
    "- Read the song file and view the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>num_songs</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_latitude</th>\n",
       "      <th>artist_longitude</th>\n",
       "      <th>artist_location</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>song_id</th>\n",
       "      <th>title</th>\n",
       "      <th>duration</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ARMJAGH1187FB546F3</td>\n",
       "      <td>35.14968</td>\n",
       "      <td>-90.04892</td>\n",
       "      <td>Memphis, TN</td>\n",
       "      <td>The Box Tops</td>\n",
       "      <td>SOCIWDW12A8C13D406</td>\n",
       "      <td>Soul Deep</td>\n",
       "      <td>148.03546</td>\n",
       "      <td>1969</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_songs           artist_id  artist_latitude  artist_longitude  \\\n",
       "0          1  ARMJAGH1187FB546F3         35.14968         -90.04892   \n",
       "\n",
       "  artist_location   artist_name             song_id      title   duration  \\\n",
       "0     Memphis, TN  The Box Tops  SOCIWDW12A8C13D406  Soul Deep  148.03546   \n",
       "\n",
       "   year  \n",
       "0  1969  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_files = get_files('data/song_data/')\n",
    "filepath = song_files[0]\n",
    "\n",
    "df = pd.read_json(filepath, lines=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "num_songs             int64\n",
       "artist_id            object\n",
       "artist_latitude     float64\n",
       "artist_longitude    float64\n",
       "artist_location      object\n",
       "artist_name          object\n",
       "song_id              object\n",
       "title                object\n",
       "duration            float64\n",
       "year                  int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #1: `songs` Table\n",
    "#### Extract Data for Songs Table\n",
    "- Select columns for song ID, title, artist ID, year, and duration\n",
    "- Use `df.values` to select just the values from the dataframe\n",
    "- Index to select the first (only) record in the dataframe\n",
    "- Convert the array to a list and set it to `song_data`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SOCIWDW12A8C13D406', 'Soul Deep', 'ARMJAGH1187FB546F3', 1969, 148.03546]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_data = df.loc[0, ['song_id', 'title', 'artist_id', 'year', 'duration']].values.tolist()\n",
    "song_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert Record into Song Table\n",
    "Implement the `song_table_insert` query in `sql_queries.py` and run the cell below to insert a record for this song into the `songs` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `songs` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(SONG_TABLE_INSERT, song_data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added a record to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #2: `artists` Table\n",
    "#### Extract Data for Artists Table\n",
    "- Select columns for artist ID, name, location, latitude, and longitude\n",
    "- Use `df.values` to select just the values from the dataframe\n",
    "- Index to select the first (only) record in the dataframe\n",
    "- Convert the array to a list and set it to `artist_data`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ARMJAGH1187FB546F3', 'The Box Tops', 'Memphis, TN', 35.14968, -90.04892]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artist_data = df.loc[0, ['artist_id', 'artist_name', 'artist_location',\n",
    "                         'artist_latitude', 'artist_longitude']].values.tolist()\n",
    "artist_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert Record into Artist Table\n",
    "Implement the `artist_table_insert` query in `sql_queries.py` and run the cell below to insert a record for this song's artist into the `artists` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `artists` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(ARTIST_TABLE_INSERT, artist_data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added a record to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process `log_data`\n",
    "In this part, you'll perform ETL on the second dataset, `log_data`, to create the `time` and `users` dimensional tables, as well as the `songplays` fact table.\n",
    "\n",
    "Let's perform ETL on a single log file and load a single record into each table.\n",
    "- Use the `get_files` function provided above to get a list of all log JSON files in `data/log_data`\n",
    "- Select the first log file in this list\n",
    "- Read the log file and view the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>None</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Walter</td>\n",
       "      <td>M</td>\n",
       "      <td>0</td>\n",
       "      <td>Frye</td>\n",
       "      <td>NaN</td>\n",
       "      <td>free</td>\n",
       "      <td>San Francisco-Oakland-Hayward, CA</td>\n",
       "      <td>GET</td>\n",
       "      <td>Home</td>\n",
       "      <td>1540919166796</td>\n",
       "      <td>38</td>\n",
       "      <td>None</td>\n",
       "      <td>200</td>\n",
       "      <td>1541105830796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>0</td>\n",
       "      <td>Summers</td>\n",
       "      <td>NaN</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>GET</td>\n",
       "      <td>Home</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>None</td>\n",
       "      <td>200</td>\n",
       "      <td>1541106106796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Des'ree</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Summers</td>\n",
       "      <td>246.30812</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>You Gotta Be</td>\n",
       "      <td>200</td>\n",
       "      <td>1541106106796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>2</td>\n",
       "      <td>Summers</td>\n",
       "      <td>NaN</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>GET</td>\n",
       "      <td>Upgrade</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>None</td>\n",
       "      <td>200</td>\n",
       "      <td>1541106132796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mr Oizo</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>3</td>\n",
       "      <td>Summers</td>\n",
       "      <td>144.03873</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>Flat 55</td>\n",
       "      <td>200</td>\n",
       "      <td>1541106352796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    artist       auth firstName gender  itemInSession lastName     length  \\\n",
       "0     None  Logged In    Walter      M              0     Frye        NaN   \n",
       "1     None  Logged In    Kaylee      F              0  Summers        NaN   \n",
       "2  Des'ree  Logged In    Kaylee      F              1  Summers  246.30812   \n",
       "3     None  Logged In    Kaylee      F              2  Summers        NaN   \n",
       "4  Mr Oizo  Logged In    Kaylee      F              3  Summers  144.03873   \n",
       "\n",
       "  level                           location method      page   registration  \\\n",
       "0  free  San Francisco-Oakland-Hayward, CA    GET      Home  1540919166796   \n",
       "1  free        Phoenix-Mesa-Scottsdale, AZ    GET      Home  1540344794796   \n",
       "2  free        Phoenix-Mesa-Scottsdale, AZ    PUT  NextSong  1540344794796   \n",
       "3  free        Phoenix-Mesa-Scottsdale, AZ    GET   Upgrade  1540344794796   \n",
       "4  free        Phoenix-Mesa-Scottsdale, AZ    PUT  NextSong  1540344794796   \n",
       "\n",
       "   sessionId          song  status             ts  \\\n",
       "0         38          None     200  1541105830796   \n",
       "1        139          None     200  1541106106796   \n",
       "2        139  You Gotta Be     200  1541106106796   \n",
       "3        139          None     200  1541106132796   \n",
       "4        139       Flat 55     200  1541106352796   \n",
       "\n",
       "                                           userAgent  userId  \n",
       "0  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...      39  \n",
       "1  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  \n",
       "2  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  \n",
       "3  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  \n",
       "4  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log_files = get_files('data/log_data/')\n",
    "filepath = log_files[0]\n",
    "\n",
    "df = pd.read_json(filepath, lines=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "artist            object\n",
       "auth              object\n",
       "firstName         object\n",
       "gender            object\n",
       "itemInSession      int64\n",
       "lastName          object\n",
       "length           float64\n",
       "level             object\n",
       "location          object\n",
       "method            object\n",
       "page              object\n",
       "registration       int64\n",
       "sessionId          int64\n",
       "song              object\n",
       "status             int64\n",
       "ts                 int64\n",
       "userAgent         object\n",
       "userId             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F    10\n",
      "M     5\n",
      "Name: gender, dtype: int64\n",
      "--------------------------------------------------\n",
      "free    15\n",
      "Name: level, dtype: int64\n",
      "--------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for c in ('gender', 'level'):\n",
    "    print(df[c].value_counts())\n",
    "    print(50*'-')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #3: `time` Table\n",
    "#### Extract Data for Time Table\n",
    "- Filter records by `NextSong` action\n",
    "- Convert the `ts` timestamp column to datetime\n",
    "  - Hint: the current timestamp is in milliseconds\n",
    "- Extract the timestamp, hour, day, week of year, month, year, and weekday from the `ts` column and set `time_data` to a list containing these values in order\n",
    "  - Hint: use pandas' [`dt` attribute](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.dt.html) to access easily datetimelike properties.\n",
    "- Specify labels for these columns and set to `column_labels`\n",
    "- Create a dataframe, `time_df,` containing the time data for this file by combining `column_labels` and `time_data` into a dictionary and converting this into a dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>2</th>\n",
       "      <td>Des'ree</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Summers</td>\n",
       "      <td>246.30812</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>You Gotta Be</td>\n",
       "      <td>200</td>\n",
       "      <td>1541106106796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mr Oizo</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>3</td>\n",
       "      <td>Summers</td>\n",
       "      <td>144.03873</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>Flat 55</td>\n",
       "      <td>200</td>\n",
       "      <td>1541106352796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Tamba Trio</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>4</td>\n",
       "      <td>Summers</td>\n",
       "      <td>177.18812</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>Quem Quiser Encontrar O Amor</td>\n",
       "      <td>200</td>\n",
       "      <td>1541106496796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>The Mars Volta</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>5</td>\n",
       "      <td>Summers</td>\n",
       "      <td>380.42077</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>Eriatarka</td>\n",
       "      <td>200</td>\n",
       "      <td>1541106673796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Infected Mushroom</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kaylee</td>\n",
       "      <td>F</td>\n",
       "      <td>6</td>\n",
       "      <td>Summers</td>\n",
       "      <td>440.26730</td>\n",
       "      <td>free</td>\n",
       "      <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1540344794796</td>\n",
       "      <td>139</td>\n",
       "      <td>Becoming Insane</td>\n",
       "      <td>200</td>\n",
       "      <td>1541107053796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              artist       auth firstName gender  itemInSession lastName  \\\n",
       "2            Des'ree  Logged In    Kaylee      F              1  Summers   \n",
       "4            Mr Oizo  Logged In    Kaylee      F              3  Summers   \n",
       "5         Tamba Trio  Logged In    Kaylee      F              4  Summers   \n",
       "6     The Mars Volta  Logged In    Kaylee      F              5  Summers   \n",
       "7  Infected Mushroom  Logged In    Kaylee      F              6  Summers   \n",
       "\n",
       "      length level                     location method      page  \\\n",
       "2  246.30812  free  Phoenix-Mesa-Scottsdale, AZ    PUT  NextSong   \n",
       "4  144.03873  free  Phoenix-Mesa-Scottsdale, AZ    PUT  NextSong   \n",
       "5  177.18812  free  Phoenix-Mesa-Scottsdale, AZ    PUT  NextSong   \n",
       "6  380.42077  free  Phoenix-Mesa-Scottsdale, AZ    PUT  NextSong   \n",
       "7  440.26730  free  Phoenix-Mesa-Scottsdale, AZ    PUT  NextSong   \n",
       "\n",
       "    registration  sessionId                          song  status  \\\n",
       "2  1540344794796        139                  You Gotta Be     200   \n",
       "4  1540344794796        139                       Flat 55     200   \n",
       "5  1540344794796        139  Quem Quiser Encontrar O Amor     200   \n",
       "6  1540344794796        139                     Eriatarka     200   \n",
       "7  1540344794796        139               Becoming Insane     200   \n",
       "\n",
       "              ts                                          userAgent  userId  \n",
       "2  1541106106796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  \n",
       "4  1541106352796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  \n",
       "5  1541106496796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  \n",
       "6  1541106673796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  \n",
       "7  1541107053796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...       8  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.loc[df['page'] == 'NextSong', :]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Logged In    11\n",
      "Name: auth, dtype: int64\n",
      "--------------------------------------------------\n",
      "1    2\n",
      "0    2\n",
      "8    1\n",
      "7    1\n",
      "6    1\n",
      "5    1\n",
      "4    1\n",
      "3    1\n",
      "2    1\n",
      "Name: itemInSession, dtype: int64\n",
      "--------------------------------------------------\n",
      "PUT    11\n",
      "Name: method, dtype: int64\n",
      "--------------------------------------------------\n",
      "1540344794796    7\n",
      "1541016707796    2\n",
      "1540266185796    1\n",
      "1541033612796    1\n",
      "Name: registration, dtype: int64\n",
      "--------------------------------------------------\n",
      "200    11\n",
      "Name: status, dtype: int64\n",
      "--------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for c in ('auth', 'itemInSession', 'method', 'registration', 'status'):\n",
    "    print(df[c].value_counts())\n",
    "    print(50*'-')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2   2018-11-01 21:01:46.796000+00:00\n",
       "4   2018-11-01 21:05:52.796000+00:00\n",
       "5   2018-11-01 21:08:16.796000+00:00\n",
       "6   2018-11-01 21:11:13.796000+00:00\n",
       "7   2018-11-01 21:17:33.796000+00:00\n",
       "Name: ts, dtype: datetime64[ns, UTC]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = pd.to_datetime(df['ts'], utc=True, unit='ms')\n",
    "t.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>start_time</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>hour</th>\n",
       "      <th>day</th>\n",
       "      <th>week</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1541106106796</td>\n",
       "      <td>2018-11-01 21:01:46.796000+00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1541106352796</td>\n",
       "      <td>2018-11-01 21:05:52.796000+00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1541106496796</td>\n",
       "      <td>2018-11-01 21:08:16.796000+00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1541106673796</td>\n",
       "      <td>2018-11-01 21:11:13.796000+00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1541107053796</td>\n",
       "      <td>2018-11-01 21:17:33.796000+00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      start_time                        timestamp  hour  day  week  month  \\\n",
       "2  1541106106796 2018-11-01 21:01:46.796000+00:00    21    1    44     11   \n",
       "4  1541106352796 2018-11-01 21:05:52.796000+00:00    21    1    44     11   \n",
       "5  1541106496796 2018-11-01 21:08:16.796000+00:00    21    1    44     11   \n",
       "6  1541106673796 2018-11-01 21:11:13.796000+00:00    21    1    44     11   \n",
       "7  1541107053796 2018-11-01 21:17:33.796000+00:00    21    1    44     11   \n",
       "\n",
       "   year  weekday  \n",
       "2  2018        3  \n",
       "4  2018        3  \n",
       "5  2018        3  \n",
       "6  2018        3  \n",
       "7  2018        3  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_data = (df['ts'], t, t.dt.hour, t.dt.day, t.dt.weekofyear, t.dt.month,\n",
    "                 t.dt.year, t.dt.weekday)\n",
    "column_labels = ('start_time', 'timestamp', 'hour', 'day', 'week', 'month',\n",
    "                 'year', 'weekday')\n",
    "time_df = pd.DataFrame(dict(zip(column_labels, time_data)))\n",
    "time_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert Records into Time Table\n",
    "Implement the `time_table_insert` query in `sql_queries.py` and run the cell below to insert records for the timestamps in this log file into the `time` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `time` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "for _, row in time_df.iterrows():\n",
    "    cur.execute(TIME_TABLE_INSERT, row)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #4: `users` Table\n",
    "#### Extract Data for Users Table\n",
    "- Select columns for user ID, first name, last name, gender and level and set to `user_df`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_df = df.loc[:, ['userId', 'firstName', 'lastName', 'gender', 'level']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert Records into Users Table\n",
    "Implement the `user_table_insert` query in `sql_queries.py` and run the cell below to insert records for the users in this log file into the `users` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `users` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "for _, row in user_df.iterrows():\n",
    "    cur.execute(USER_TABLE_INSERT, row)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #5: `songplays` Table\n",
    "#### Extract Data and Songplays Table\n",
    "This one is a little more complicated since information from the songs table, artists table, and original log file are all needed for the `songplays` table. Since the log file does not specify an ID for either the song or the artist, you'll need to get the song ID and artist ID by querying the songs and artists tables to find matches based on song title, artist name, and song duration time.\n",
    "- Implement the `song_select` query in `sql_queries.py` to find the song ID and artist ID based on the title, artist name, and duration of a song.\n",
    "- Select the timestamp, user ID, level, song ID, artist ID, session ID, location, and user agent and set to `songplay_data`\n",
    "\n",
    "#### Insert Records into Songplays Table\n",
    "- Implement the `songplay_table_insert` query and run the cell below to insert records for the songplay actions in this log file into the `songplays` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `songplays` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in df.iterrows():\n",
    "\n",
    "    # Get song_id and artist_id from song and artist tables\n",
    "    cur.execute(SONG_SELECT, (row.song, row.artist, row.length))\n",
    "    results = cur.fetchone()\n",
    "\n",
    "    if results:\n",
    "        songid, artistid = results\n",
    "    else:\n",
    "        songid, artistid = None, None\n",
    "\n",
    "    # Insert songplay record\n",
    "    songplay_data = ([df.loc[index, 'ts'], songid, artistid] +\n",
    "                     row[['userId', 'level', 'sessionId', 'location',\n",
    "                          'userAgent']].values.tolist())\n",
    "    cur.execute(SONGPLAY_TABLE_INSERT, songplay_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Close Connection to Sparkify Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Implement `etl.py`\n",
    "Use what you've completed in this notebook to implement `etl.py`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}