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 *"
   ]
  },
  {
   "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": [],
   "source": [
    "song_files = get_files('data/song_data/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "filepath = song_files[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>AR7G5I41187FB4CE6C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>London, England</td>\n",
       "      <td>Adam Ant</td>\n",
       "      <td>SONHOTT12A8C13493C</td>\n",
       "      <td>Something Girls</td>\n",
       "      <td>233.40363</td>\n",
       "      <td>1982</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_songs           artist_id  artist_latitude  artist_longitude  \\\n",
       "0          1  AR7G5I41187FB4CE6C              NaN               NaN   \n",
       "\n",
       "   artist_location artist_name             song_id            title  \\\n",
       "0  London, England    Adam Ant  SONHOTT12A8C13493C  Something Girls   \n",
       "\n",
       "    duration  year  \n",
       "0  233.40363  1982  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(filepath, lines=True)\n",
    "df.head()"
   ]
  },
  {
   "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": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SONHOTT12A8C13493C',\n",
       " 'Something Girls',\n",
       " 'AR7G5I41187FB4CE6C',\n",
       " 1982,\n",
       " 233.40363]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_data = list(df[['song_id', 'title', 'artist_id', 'year', 'duration']].values[0])\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": 8,
   "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": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['AR7G5I41187FB4CE6C', 'Adam Ant', 'London, England', nan, nan]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artist_data = list(df[['artist_id', 'artist_name', 'artist_location', 'artist_latitude', 'artist_longitude']].values[0])\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": 10,
   "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": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "log_files = get_files('data/log_data/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "filepath = log_files[0]"
   ]
  },
  {
   "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>0</th>\n",
       "      <td>Frumpies</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>0</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>134.47791</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>Fuck Kitty</td>\n",
       "      <td>200</td>\n",
       "      <td>1541903636796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kenny G with Peabo Bryson</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>264.75057</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>By The Time This Night Is Over</td>\n",
       "      <td>200</td>\n",
       "      <td>1541903770796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Biffy Clyro</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>2</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>189.83138</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>God &amp; Satan</td>\n",
       "      <td>200</td>\n",
       "      <td>1541904034796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Lily</td>\n",
       "      <td>F</td>\n",
       "      <td>0</td>\n",
       "      <td>Burns</td>\n",
       "      <td>NaN</td>\n",
       "      <td>free</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>GET</td>\n",
       "      <td>Home</td>\n",
       "      <td>1.540621e+12</td>\n",
       "      <td>456</td>\n",
       "      <td>None</td>\n",
       "      <td>200</td>\n",
       "      <td>1541910841796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HIM</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Lily</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Burns</td>\n",
       "      <td>212.06159</td>\n",
       "      <td>free</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540621e+12</td>\n",
       "      <td>456</td>\n",
       "      <td>Beautiful</td>\n",
       "      <td>200</td>\n",
       "      <td>1541910973796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      artist       auth firstName gender  itemInSession  \\\n",
       "0                   Frumpies  Logged In  Anabelle      F              0   \n",
       "1  Kenny G with Peabo Bryson  Logged In  Anabelle      F              1   \n",
       "2                Biffy Clyro  Logged In  Anabelle      F              2   \n",
       "3                       None  Logged In      Lily      F              0   \n",
       "4                        HIM  Logged In      Lily      F              1   \n",
       "\n",
       "  lastName     length level                                     location  \\\n",
       "0  Simpson  134.47791  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "1  Simpson  264.75057  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "2  Simpson  189.83138  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "3    Burns        NaN  free        New York-Newark-Jersey City, NY-NJ-PA   \n",
       "4    Burns  212.06159  free        New York-Newark-Jersey City, NY-NJ-PA   \n",
       "\n",
       "  method      page  registration  sessionId                            song  \\\n",
       "0    PUT  NextSong  1.541044e+12        455                      Fuck Kitty   \n",
       "1    PUT  NextSong  1.541044e+12        455  By The Time This Night Is Over   \n",
       "2    PUT  NextSong  1.541044e+12        455                     God & Satan   \n",
       "3    GET      Home  1.540621e+12        456                            None   \n",
       "4    PUT  NextSong  1.540621e+12        456                       Beautiful   \n",
       "\n",
       "   status             ts                                          userAgent  \\\n",
       "0     200  1541903636796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "1     200  1541903770796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "2     200  1541904034796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "3     200  1541910841796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "4     200  1541910973796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "\n",
       "  userId  \n",
       "0     69  \n",
       "1     69  \n",
       "2     69  \n",
       "3     32  \n",
       "4     32  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(filepath, lines=True)\n",
    "df.head()"
   ]
  },
  {
   "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": 14,
   "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>Frumpies</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>0</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>134.47791</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>Fuck Kitty</td>\n",
       "      <td>200</td>\n",
       "      <td>1541903636796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kenny G with Peabo Bryson</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>264.75057</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>By The Time This Night Is Over</td>\n",
       "      <td>200</td>\n",
       "      <td>1541903770796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Biffy Clyro</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>2</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>189.83138</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>God &amp; Satan</td>\n",
       "      <td>200</td>\n",
       "      <td>1541904034796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HIM</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Lily</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Burns</td>\n",
       "      <td>212.06159</td>\n",
       "      <td>free</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540621e+12</td>\n",
       "      <td>456</td>\n",
       "      <td>Beautiful</td>\n",
       "      <td>200</td>\n",
       "      <td>1541910973796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Matmos</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>M</td>\n",
       "      <td>0</td>\n",
       "      <td>Gutierrez</td>\n",
       "      <td>1449.11628</td>\n",
       "      <td>free</td>\n",
       "      <td>Columbia, SC</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540809e+12</td>\n",
       "      <td>284</td>\n",
       "      <td>Supreme Balloon</td>\n",
       "      <td>200</td>\n",
       "      <td>1541911006796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3...</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      artist       auth firstName gender  itemInSession  \\\n",
       "0                   Frumpies  Logged In  Anabelle      F              0   \n",
       "1  Kenny G with Peabo Bryson  Logged In  Anabelle      F              1   \n",
       "2                Biffy Clyro  Logged In  Anabelle      F              2   \n",
       "4                        HIM  Logged In      Lily      F              1   \n",
       "5                     Matmos  Logged In    Joseph      M              0   \n",
       "\n",
       "    lastName      length level                                     location  \\\n",
       "0    Simpson   134.47791  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "1    Simpson   264.75057  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "2    Simpson   189.83138  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "4      Burns   212.06159  free        New York-Newark-Jersey City, NY-NJ-PA   \n",
       "5  Gutierrez  1449.11628  free                                 Columbia, SC   \n",
       "\n",
       "  method      page  registration  sessionId                            song  \\\n",
       "0    PUT  NextSong  1.541044e+12        455                      Fuck Kitty   \n",
       "1    PUT  NextSong  1.541044e+12        455  By The Time This Night Is Over   \n",
       "2    PUT  NextSong  1.541044e+12        455                     God & Satan   \n",
       "4    PUT  NextSong  1.540621e+12        456                       Beautiful   \n",
       "5    PUT  NextSong  1.540809e+12        284                 Supreme Balloon   \n",
       "\n",
       "   status             ts                                          userAgent  \\\n",
       "0     200  1541903636796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "1     200  1541903770796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "2     200  1541904034796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "4     200  1541910973796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "5     200  1541911006796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3...   \n",
       "\n",
       "  userId  \n",
       "0     69  \n",
       "1     69  \n",
       "2     69  \n",
       "4     32  \n",
       "5     75  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.loc[df['page'] == 'NextSong']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2018-11-11 02:33:56.796\n",
       "1   2018-11-11 02:36:10.796\n",
       "2   2018-11-11 02:40:34.796\n",
       "4   2018-11-11 04:36:13.796\n",
       "5   2018-11-11 04:36:46.796\n",
       "Name: ts, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = pd.to_datetime(df['ts'], unit='ms')\n",
    "t.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_data = [df.ts.values, t.dt.hour.values, t.dt.day.values, t.dt.weekofyear.values, t.dt.month.values, t.dt.year.values, t.dt.weekday.values]\n",
    "column_labels = ['start_time', 'hour', 'day', 'week', 'month', 'year', 'weekday']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>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>1541903636796</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1541903770796</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1541904034796</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1541910973796</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1541911006796</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      start_time  hour  day  week  month  year  weekday\n",
       "0  1541903636796     2   11    45     11  2018        6\n",
       "1  1541903770796     2   11    45     11  2018        6\n",
       "2  1541904034796     2   11    45     11  2018        6\n",
       "3  1541910973796     4   11    45     11  2018        6\n",
       "4  1541911006796     4   11    45     11  2018        6"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "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": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in time_df.iterrows():\n",
    "    cur.execute(time_table_insert, list(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": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>69</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>69</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>69</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>F</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>5</th>\n",
       "      <td>75</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>Gutierrez</td>\n",
       "      <td>M</td>\n",
       "      <td>free</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  userId firstName   lastName gender level\n",
       "0     69  Anabelle    Simpson      F  free\n",
       "1     69  Anabelle    Simpson      F  free\n",
       "2     69  Anabelle    Simpson      F  free\n",
       "4     32      Lily      Burns      F  free\n",
       "5     75    Joseph  Gutierrez      M  free"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_df = df[['userId', 'firstName', 'lastName', 'gender', 'level']]\n",
    "user_df.head()"
   ]
  },
  {
   "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": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, 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": 21,
   "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>Frumpies</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>0</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>134.47791</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>Fuck Kitty</td>\n",
       "      <td>200</td>\n",
       "      <td>1541903636796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kenny G with Peabo Bryson</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>264.75057</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>By The Time This Night Is Over</td>\n",
       "      <td>200</td>\n",
       "      <td>1541903770796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Biffy Clyro</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Anabelle</td>\n",
       "      <td>F</td>\n",
       "      <td>2</td>\n",
       "      <td>Simpson</td>\n",
       "      <td>189.83138</td>\n",
       "      <td>free</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.541044e+12</td>\n",
       "      <td>455</td>\n",
       "      <td>God &amp; Satan</td>\n",
       "      <td>200</td>\n",
       "      <td>1541904034796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HIM</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Lily</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Burns</td>\n",
       "      <td>212.06159</td>\n",
       "      <td>free</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540621e+12</td>\n",
       "      <td>456</td>\n",
       "      <td>Beautiful</td>\n",
       "      <td>200</td>\n",
       "      <td>1541910973796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Matmos</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>M</td>\n",
       "      <td>0</td>\n",
       "      <td>Gutierrez</td>\n",
       "      <td>1449.11628</td>\n",
       "      <td>free</td>\n",
       "      <td>Columbia, SC</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540809e+12</td>\n",
       "      <td>284</td>\n",
       "      <td>Supreme Balloon</td>\n",
       "      <td>200</td>\n",
       "      <td>1541911006796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3...</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      artist       auth firstName gender  itemInSession  \\\n",
       "0                   Frumpies  Logged In  Anabelle      F              0   \n",
       "1  Kenny G with Peabo Bryson  Logged In  Anabelle      F              1   \n",
       "2                Biffy Clyro  Logged In  Anabelle      F              2   \n",
       "4                        HIM  Logged In      Lily      F              1   \n",
       "5                     Matmos  Logged In    Joseph      M              0   \n",
       "\n",
       "    lastName      length level                                     location  \\\n",
       "0    Simpson   134.47791  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "1    Simpson   264.75057  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "2    Simpson   189.83138  free  Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "4      Burns   212.06159  free        New York-Newark-Jersey City, NY-NJ-PA   \n",
       "5  Gutierrez  1449.11628  free                                 Columbia, SC   \n",
       "\n",
       "  method      page  registration  sessionId                            song  \\\n",
       "0    PUT  NextSong  1.541044e+12        455                      Fuck Kitty   \n",
       "1    PUT  NextSong  1.541044e+12        455  By The Time This Night Is Over   \n",
       "2    PUT  NextSong  1.541044e+12        455                     God & Satan   \n",
       "4    PUT  NextSong  1.540621e+12        456                       Beautiful   \n",
       "5    PUT  NextSong  1.540809e+12        284                 Supreme Balloon   \n",
       "\n",
       "   status             ts                                          userAgent  \\\n",
       "0     200  1541903636796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "1     200  1541903770796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "2     200  1541904034796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...   \n",
       "4     200  1541910973796  \"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebK...   \n",
       "5     200  1541911006796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3...   \n",
       "\n",
       "  userId  \n",
       "0     69  \n",
       "1     69  \n",
       "2     69  \n",
       "4     32  \n",
       "5     75  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in df.iterrows():\n",
    "\n",
    "    # get songid and artistid from song and artist tables\n",
    "#     results = cur.execute(song_select, (row.song, row.artist, row.length))\n",
    "#     songid, artistid = results if results else None, None\n",
    "    \n",
    "    cur.execute(song_select, (row.song, row.artist, row.length))\n",
    "    result = cur.fetchone()\n",
    "\n",
    "    if result:\n",
    "        songid, artistid = result\n",
    "    else:\n",
    "        songid, artistid = None, None\n",
    "    \n",
    "\n",
    "    # insert songplay record\n",
    "    songplay_data = [row.ts, row.userId, row.level, songid, artistid, row.sessionId, row.location, row.userAgent]\n",
    "    \n",
    "    cur.execute(songplay_table_insert, songplay_data)\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": [
    "# Close Connection to Sparkify Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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": {
  "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}