etl.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "from sql_queries import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true
   },
   "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": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "#conn.set_session(autocommit=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "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": {
    "editable": true
   },
   "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": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "filepath = 'data/song_data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "song_files = get_files(filepath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "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_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>AR8IEZO1187B99055E</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>Marc Shaiman</td>\n",
       "      <td>149.86404</td>\n",
       "      <td>1</td>\n",
       "      <td>SOINLJW12A8C13314C</td>\n",
       "      <td>City Slickers</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            artist_id  artist_latitude artist_location  artist_longitude  \\\n",
       "0  AR8IEZO1187B99055E              NaN                               NaN   \n",
       "\n",
       "    artist_name   duration  num_songs             song_id          title  year  \n",
       "0  Marc Shaiman  149.86404          1  SOINLJW12A8C13314C  City Slickers  2008  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(song_files[0], lines=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            artist_id  artist_latitude artist_location  artist_longitude  \\\n",
      "0  AR8IEZO1187B99055E              NaN                               NaN   \n",
      "\n",
      "    artist_name   duration  num_songs             song_id          title  year  \n",
      "0  Marc Shaiman  149.86404          1  SOINLJW12A8C13314C  City Slickers  2008  \n"
     ]
    }
   ],
   "source": [
    "#print(song_files[0])\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 9,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2008\n",
      "<class 'int'>\n"
     ]
    }
   ],
   "source": [
    "#df.values\n",
    "print(df.values[0][-1])\n",
    "print(type(df.values[0][-1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['SOINLJW12A8C13314C', 'City Slickers', 'AR8IEZO1187B99055E', 2008, 149.86404]\n",
      "<class 'list'>\n"
     ]
    }
   ],
   "source": [
    "#song_data = df.values[0].tolist()\n",
    "song_data = list(df.loc[:,['song_id', 'title', 'artist_id', 'year', 'duration']].values[0])\n",
    "\n",
    "print(song_data)\n",
    "print(type(song_data))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 11,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cur.execute(song_table_insert, song_data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Run `test.ipynb` to see if you've successfully added a record to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'str'>\n"
     ]
    }
   ],
   "source": [
    "artist_data = list(df.loc[:,['artist_id', 'artist_name', 'artist_location', 'artist_latitude', 'artist_longitude']].values[0])\n",
    "artist_data\n",
    "\n",
    "print(type(artist_data[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 13,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cur.execute(artist_table_insert, artist_data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Run `test.ipynb` to see if you've successfully added a record to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 14,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "filepath = 'data/log_data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "log_files = get_files(filepath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "editable": true
   },
   "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>Sydney Youngblood</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>M</td>\n",
       "      <td>53</td>\n",
       "      <td>Klein</td>\n",
       "      <td>238.07955</td>\n",
       "      <td>paid</td>\n",
       "      <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540558e+12</td>\n",
       "      <td>954</td>\n",
       "      <td>Ain't No Sunshine</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449657796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gang Starr</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Layla</td>\n",
       "      <td>F</td>\n",
       "      <td>88</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>151.92771</td>\n",
       "      <td>paid</td>\n",
       "      <td>Lake Havasu City-Kingman, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541057e+12</td>\n",
       "      <td>984</td>\n",
       "      <td>My Advice 2 You (Explicit)</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449690796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3OH!3</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Layla</td>\n",
       "      <td>F</td>\n",
       "      <td>89</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>192.52200</td>\n",
       "      <td>paid</td>\n",
       "      <td>Lake Havasu City-Kingman, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541057e+12</td>\n",
       "      <td>984</td>\n",
       "      <td>My First Kiss (Feat. Ke$ha) [Album Version]</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449841796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RÃÂ¶yksopp</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>M</td>\n",
       "      <td>54</td>\n",
       "      <td>Klein</td>\n",
       "      <td>369.81506</td>\n",
       "      <td>paid</td>\n",
       "      <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540558e+12</td>\n",
       "      <td>954</td>\n",
       "      <td>The Girl and The Robot</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449895796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kajagoogoo</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Layla</td>\n",
       "      <td>F</td>\n",
       "      <td>90</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>223.55546</td>\n",
       "      <td>paid</td>\n",
       "      <td>Lake Havasu City-Kingman, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541057e+12</td>\n",
       "      <td>984</td>\n",
       "      <td>Too Shy</td>\n",
       "      <td>200</td>\n",
       "      <td>1543450033796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              artist       auth firstName gender  itemInSession lastName  \\\n",
       "0  Sydney Youngblood  Logged In     Jacob      M             53    Klein   \n",
       "1         Gang Starr  Logged In     Layla      F             88  Griffin   \n",
       "2              3OH!3  Logged In     Layla      F             89  Griffin   \n",
       "3        RÃÂ¶yksopp  Logged In     Jacob      M             54    Klein   \n",
       "4         Kajagoogoo  Logged In     Layla      F             90  Griffin   \n",
       "\n",
       "      length level                             location method      page  \\\n",
       "0  238.07955  paid  Tampa-St. Petersburg-Clearwater, FL    PUT  NextSong   \n",
       "1  151.92771  paid         Lake Havasu City-Kingman, AZ    PUT  NextSong   \n",
       "2  192.52200  paid         Lake Havasu City-Kingman, AZ    PUT  NextSong   \n",
       "3  369.81506  paid  Tampa-St. Petersburg-Clearwater, FL    PUT  NextSong   \n",
       "4  223.55546  paid         Lake Havasu City-Kingman, AZ    PUT  NextSong   \n",
       "\n",
       "   registration  sessionId                                         song  \\\n",
       "0  1.540558e+12        954                            Ain't No Sunshine   \n",
       "1  1.541057e+12        984                   My Advice 2 You (Explicit)   \n",
       "2  1.541057e+12        984  My First Kiss (Feat. Ke$ha) [Album Version]   \n",
       "3  1.540558e+12        954                       The Girl and The Robot   \n",
       "4  1.541057e+12        984                                      Too Shy   \n",
       "\n",
       "   status             ts                                          userAgent  \\\n",
       "0     200  1543449657796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "1     200  1543449690796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "2     200  1543449841796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "3     200  1543449895796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "4     200  1543450033796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "\n",
       "  userId  \n",
       "0     73  \n",
       "1     24  \n",
       "2     24  \n",
       "3     73  \n",
       "4     24  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(log_files[0], lines=True)\n",
    "df.head()\n",
    "#df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 17,
   "metadata": {
    "editable": true
   },
   "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>Sydney Youngblood</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>M</td>\n",
       "      <td>53</td>\n",
       "      <td>Klein</td>\n",
       "      <td>238.07955</td>\n",
       "      <td>paid</td>\n",
       "      <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540558e+12</td>\n",
       "      <td>954</td>\n",
       "      <td>Ain't No Sunshine</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449657796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gang Starr</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Layla</td>\n",
       "      <td>F</td>\n",
       "      <td>88</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>151.92771</td>\n",
       "      <td>paid</td>\n",
       "      <td>Lake Havasu City-Kingman, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541057e+12</td>\n",
       "      <td>984</td>\n",
       "      <td>My Advice 2 You (Explicit)</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449690796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3OH!3</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Layla</td>\n",
       "      <td>F</td>\n",
       "      <td>89</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>192.52200</td>\n",
       "      <td>paid</td>\n",
       "      <td>Lake Havasu City-Kingman, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541057e+12</td>\n",
       "      <td>984</td>\n",
       "      <td>My First Kiss (Feat. Ke$ha) [Album Version]</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449841796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RÃÂ¶yksopp</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>M</td>\n",
       "      <td>54</td>\n",
       "      <td>Klein</td>\n",
       "      <td>369.81506</td>\n",
       "      <td>paid</td>\n",
       "      <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540558e+12</td>\n",
       "      <td>954</td>\n",
       "      <td>The Girl and The Robot</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449895796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kajagoogoo</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Layla</td>\n",
       "      <td>F</td>\n",
       "      <td>90</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>223.55546</td>\n",
       "      <td>paid</td>\n",
       "      <td>Lake Havasu City-Kingman, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541057e+12</td>\n",
       "      <td>984</td>\n",
       "      <td>Too Shy</td>\n",
       "      <td>200</td>\n",
       "      <td>1543450033796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              artist       auth firstName gender  itemInSession lastName  \\\n",
       "0  Sydney Youngblood  Logged In     Jacob      M             53    Klein   \n",
       "1         Gang Starr  Logged In     Layla      F             88  Griffin   \n",
       "2              3OH!3  Logged In     Layla      F             89  Griffin   \n",
       "3        RÃÂ¶yksopp  Logged In     Jacob      M             54    Klein   \n",
       "4         Kajagoogoo  Logged In     Layla      F             90  Griffin   \n",
       "\n",
       "      length level                             location method      page  \\\n",
       "0  238.07955  paid  Tampa-St. Petersburg-Clearwater, FL    PUT  NextSong   \n",
       "1  151.92771  paid         Lake Havasu City-Kingman, AZ    PUT  NextSong   \n",
       "2  192.52200  paid         Lake Havasu City-Kingman, AZ    PUT  NextSong   \n",
       "3  369.81506  paid  Tampa-St. Petersburg-Clearwater, FL    PUT  NextSong   \n",
       "4  223.55546  paid         Lake Havasu City-Kingman, AZ    PUT  NextSong   \n",
       "\n",
       "   registration  sessionId                                         song  \\\n",
       "0  1.540558e+12        954                            Ain't No Sunshine   \n",
       "1  1.541057e+12        984                   My Advice 2 You (Explicit)   \n",
       "2  1.541057e+12        984  My First Kiss (Feat. Ke$ha) [Album Version]   \n",
       "3  1.540558e+12        954                       The Girl and The Robot   \n",
       "4  1.541057e+12        984                                      Too Shy   \n",
       "\n",
       "   status             ts                                          userAgent  \\\n",
       "0     200  1543449657796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "1     200  1543449690796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "2     200  1543449841796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "3     200  1543449895796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "4     200  1543450033796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "\n",
       "  userId  \n",
       "0     73  \n",
       "1     24  \n",
       "2     24  \n",
       "3     73  \n",
       "4     24  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.query(\"page == 'NextSong' \")\n",
    "df.head()\n",
    "\n",
    "#df.values[0][15]\n",
    "#type(df.values[0][15])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "349   2018-11-29 22:17:38.796\n",
       "350   2018-11-29 22:21:31.796\n",
       "352   2018-11-29 22:27:31.796\n",
       "353   2018-11-29 22:32:49.796\n",
       "354   2018-11-29 22:35:44.796\n",
       "355   2018-11-29 22:39:38.796\n",
       "356   2018-11-29 22:42:52.796\n",
       "357   2018-11-29 22:46:23.796\n",
       "358   2018-11-29 22:50:27.796\n",
       "359   2018-11-29 22:51:18.796\n",
       "360   2018-11-29 22:53:32.796\n",
       "362   2018-11-29 22:57:22.796\n",
       "363   2018-11-29 23:02:05.796\n",
       "364   2018-11-29 23:05:13.796\n",
       "365   2018-11-29 23:18:35.796\n",
       "Name: ts, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert millisecond to datetime\n",
    "t = pd.to_datetime(df.ts, unit='ms')\n",
    "t.tail(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "column_labels = ['start_time', 'hour', 'day', 'week', 'month', 'year', 'weekday']\n",
    "time_data = list(zip(t, t.dt.hour, t.dt.day, t.dt.week, t.dt.month, t.dt.year, t.dt.weekday))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "editable": true
   },
   "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>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>0</th>\n",
       "      <td>2018-11-29 00:00:57.796</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>48</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-11-29 00:01:30.796</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>48</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  hour  day  week  month  year  weekday\n",
       "0 2018-11-29 00:00:57.796     0   29    48     11  2018        3\n",
       "1 2018-11-29 00:01:30.796     0   29    48     11  2018        3"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_df = pd.DataFrame(data=time_data, columns = column_labels)\n",
    "time_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 21,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "for i, row in time_df.iterrows():\n",
    "    cur.execute(time_table_insert, list(row))\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 22,
   "metadata": {
    "editable": true
   },
   "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>Sydney Youngblood</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>M</td>\n",
       "      <td>53</td>\n",
       "      <td>Klein</td>\n",
       "      <td>238.07955</td>\n",
       "      <td>paid</td>\n",
       "      <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540558e+12</td>\n",
       "      <td>954</td>\n",
       "      <td>Ain't No Sunshine</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449657796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gang Starr</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Layla</td>\n",
       "      <td>F</td>\n",
       "      <td>88</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>151.92771</td>\n",
       "      <td>paid</td>\n",
       "      <td>Lake Havasu City-Kingman, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541057e+12</td>\n",
       "      <td>984</td>\n",
       "      <td>My Advice 2 You (Explicit)</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449690796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              artist       auth firstName gender  itemInSession lastName  \\\n",
       "0  Sydney Youngblood  Logged In     Jacob      M             53    Klein   \n",
       "1         Gang Starr  Logged In     Layla      F             88  Griffin   \n",
       "\n",
       "      length level                             location method      page  \\\n",
       "0  238.07955  paid  Tampa-St. Petersburg-Clearwater, FL    PUT  NextSong   \n",
       "1  151.92771  paid         Lake Havasu City-Kingman, AZ    PUT  NextSong   \n",
       "\n",
       "   registration  sessionId                        song  status             ts  \\\n",
       "0  1.540558e+12        954           Ain't No Sunshine     200  1543449657796   \n",
       "1  1.541057e+12        984  My Advice 2 You (Explicit)     200  1543449690796   \n",
       "\n",
       "                                           userAgent userId  \n",
       "0  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...     73  \n",
       "1  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...     24  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "editable": true
   },
   "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>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>73</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>Klein</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "      <td>Layla</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>Layla</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>73</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>Klein</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24</td>\n",
       "      <td>Layla</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  userId firstName lastName gender level\n",
       "0     73     Jacob    Klein      M  paid\n",
       "1     24     Layla  Griffin      F  paid\n",
       "2     24     Layla  Griffin      F  paid\n",
       "3     73     Jacob    Klein      M  paid\n",
       "4     24     Layla  Griffin      F  paid"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_df = df[['userId', 'firstName', 'lastName', 'gender', 'level']]\n",
    "user_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "editable": true
   },
   "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>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>73</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>Klein</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "      <td>Layla</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>Layla</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>73</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>Klein</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24</td>\n",
       "      <td>Layla</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  userId firstName lastName gender level\n",
       "0     73     Jacob    Klein      M  paid\n",
       "1     24     Layla  Griffin      F  paid\n",
       "2     24     Layla  Griffin      F  paid\n",
       "3     73     Jacob    Klein      M  paid\n",
       "4     24     Layla  Griffin      F  paid"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_df = user_df[user_df['userId'].astype(bool)]\n",
    "user_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "editable": true
   },
   "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>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>73</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>Klein</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "      <td>Layla</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>50</td>\n",
       "      <td>Ava</td>\n",
       "      <td>Robinson</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>54</td>\n",
       "      <td>Kaleb</td>\n",
       "      <td>Cook</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>32</td>\n",
       "      <td>Lily</td>\n",
       "      <td>Burns</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>4</td>\n",
       "      <td>Alivia</td>\n",
       "      <td>Terrell</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>52</td>\n",
       "      <td>Theodore</td>\n",
       "      <td>Smith</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>14</td>\n",
       "      <td>Theodore</td>\n",
       "      <td>Harris</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>98</td>\n",
       "      <td>Jordyn</td>\n",
       "      <td>Powell</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>101</td>\n",
       "      <td>Jayden</td>\n",
       "      <td>Fox</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>78</td>\n",
       "      <td>Chloe</td>\n",
       "      <td>Roth</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>10</td>\n",
       "      <td>Sylvie</td>\n",
       "      <td>Cruz</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>49</td>\n",
       "      <td>Chloe</td>\n",
       "      <td>Cuevas</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>16</td>\n",
       "      <td>Rylan</td>\n",
       "      <td>George</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>26</td>\n",
       "      <td>Ryan</td>\n",
       "      <td>Smith</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>79</td>\n",
       "      <td>James</td>\n",
       "      <td>Martin</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>82</td>\n",
       "      <td>Avery</td>\n",
       "      <td>Martinez</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>44</td>\n",
       "      <td>Aleena</td>\n",
       "      <td>Kirby</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>75</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>Gutierrez</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>39</td>\n",
       "      <td>Walter</td>\n",
       "      <td>Frye</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>80</td>\n",
       "      <td>Tegan</td>\n",
       "      <td>Levine</td>\n",
       "      <td>F</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>316</th>\n",
       "      <td>55</td>\n",
       "      <td>Martin</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>12</td>\n",
       "      <td>Austin</td>\n",
       "      <td>Rosales</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>9</td>\n",
       "      <td>Wyatt</td>\n",
       "      <td>Scott</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>22</td>\n",
       "      <td>Sean</td>\n",
       "      <td>Wilson</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>74</td>\n",
       "      <td>Braden</td>\n",
       "      <td>Parker</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>89</td>\n",
       "      <td>Kynnedi</td>\n",
       "      <td>Sanchez</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    userId firstName   lastName gender level\n",
       "0       73     Jacob      Klein      M  paid\n",
       "1       24     Layla    Griffin      F  paid\n",
       "21      50       Ava   Robinson      F  free\n",
       "35      54     Kaleb       Cook      M  free\n",
       "40      32      Lily      Burns      F  free\n",
       "42       4    Alivia    Terrell      F  free\n",
       "44      52  Theodore      Smith      M  free\n",
       "46      14  Theodore     Harris      M  free\n",
       "48      98    Jordyn     Powell      F  free\n",
       "51     101    Jayden        Fox      M  free\n",
       "53      78     Chloe       Roth      F  free\n",
       "56      10    Sylvie       Cruz      F  free\n",
       "60      49     Chloe     Cuevas      F  paid\n",
       "81      16     Rylan     George      M  paid\n",
       "87      26      Ryan      Smith      M  free\n",
       "95      79     James     Martin      M  free\n",
       "152     82     Avery   Martinez      F  paid\n",
       "158     44    Aleena      Kirby      F  paid\n",
       "199     75    Joseph  Gutierrez      M  free\n",
       "204     39    Walter       Frye      M  free\n",
       "296     80     Tegan     Levine      F  paid\n",
       "316     55    Martin    Johnson      M  free\n",
       "328     12    Austin    Rosales      M  free\n",
       "337      9     Wyatt      Scott      M  free\n",
       "346     22      Sean     Wilson      F  free\n",
       "359     74    Braden     Parker      M  free\n",
       "365     89   Kynnedi    Sanchez      F  free"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop duplicate records in userId\n",
    "user_df = user_df.drop_duplicates(subset='userId')\n",
    "user_df.head(5)\n",
    "user_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 26,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "for i, row in user_df.iterrows():\n",
    "    cur.execute(user_table_insert, row)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": 27,
   "metadata": {
    "editable": true
   },
   "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>Sydney Youngblood</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>M</td>\n",
       "      <td>53</td>\n",
       "      <td>Klein</td>\n",
       "      <td>238.07955</td>\n",
       "      <td>paid</td>\n",
       "      <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540558e+12</td>\n",
       "      <td>954</td>\n",
       "      <td>Ain't No Sunshine</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449657796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gang Starr</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Layla</td>\n",
       "      <td>F</td>\n",
       "      <td>88</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>151.92771</td>\n",
       "      <td>paid</td>\n",
       "      <td>Lake Havasu City-Kingman, AZ</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541057e+12</td>\n",
       "      <td>984</td>\n",
       "      <td>My Advice 2 You (Explicit)</td>\n",
       "      <td>200</td>\n",
       "      <td>1543449690796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              artist       auth firstName gender  itemInSession lastName  \\\n",
       "0  Sydney Youngblood  Logged In     Jacob      M             53    Klein   \n",
       "1         Gang Starr  Logged In     Layla      F             88  Griffin   \n",
       "\n",
       "      length level                             location method      page  \\\n",
       "0  238.07955  paid  Tampa-St. Petersburg-Clearwater, FL    PUT  NextSong   \n",
       "1  151.92771  paid         Lake Havasu City-Kingman, AZ    PUT  NextSong   \n",
       "\n",
       "   registration  sessionId                        song  status             ts  \\\n",
       "0  1.540558e+12        954           Ain't No Sunshine     200  1543449657796   \n",
       "1  1.541057e+12        984  My Advice 2 You (Explicit)     200  1543449690796   \n",
       "\n",
       "                                           userAgent userId  \n",
       "0  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...     73  \n",
       "1  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...     24  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cur.execute('ROLLBACK;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "for index, row in df.iterrows():\n",
    "\n",
    "    # get songid and artistid from songs and artists tables\n",
    "    cur.execute(song_select, (row.song, row.artist, row.length))\n",
    "    results = cur.fetchone()\n",
    "    \n",
    "    if results:\n",
    "        song_id, artist_id = results\n",
    "        \n",
    "    else:\n",
    "        song_id, artist_id = None, None\n",
    "        #continue\n",
    "\n",
    "    # insert songplay record\n",
    "    songplay_data = (round(row.ts/1000), row.userId, row.level, \\\n",
    "                     song_id, artist_id, \\\n",
    "                    row.sessionId, row.location, row.userAgent)\n",
    "    #t = pd.to_datetime(df.ts, unit='ms')\n",
    "    #print(songplay_data)\n",
    "    cur.execute(songplay_table_insert, songplay_data)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Close Connection to Sparkify Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Implement `etl.py`\n",
    "Use what you've completed in this notebook to implement `etl.py`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}