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": [
    "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": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "song_files = get_files(\"data/song_data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "filepath = song_files[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>ARXR32B1187FB57099</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gob</td>\n",
       "      <td>209.60608</td>\n",
       "      <td>1</td>\n",
       "      <td>SOFSOCN12A8C143F5D</td>\n",
       "      <td>Face the Ashes</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            artist_id  artist_latitude artist_location  artist_longitude  \\\n",
       "0  ARXR32B1187FB57099              NaN                               NaN   \n",
       "\n",
       "  artist_name   duration  num_songs             song_id           title  year  \n",
       "0         Gob  209.60608          1  SOFSOCN12A8C143F5D  Face the Ashes  2007  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(filepath, lines=True)\n",
    "df.head()"
   ]
  },
  {
   "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": 7,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SOFSOCN12A8C143F5D', 'Face the Ashes', 'ARXR32B1187FB57099', 2007, 209.60608]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_data = df[['song_id','title','artist_id','year','duration']].values\n",
    "song_data = list(song_data[0])\n",
    "song_data"
   ]
  },
  {
   "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": 8,
   "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": 9,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "artist_data = df[['artist_id','artist_name','artist_location','artist_latitude','artist_longitude']].values\n",
    "artist_data = list(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": 10,
   "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": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "log_files = get_files('data/log_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "filepath = log_files[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>None</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>M</td>\n",
       "      <td>0</td>\n",
       "      <td>Arellano</td>\n",
       "      <td>NaN</td>\n",
       "      <td>free</td>\n",
       "      <td>Harrisburg-Carlisle, PA</td>\n",
       "      <td>GET</td>\n",
       "      <td>Home</td>\n",
       "      <td>1.540007e+12</td>\n",
       "      <td>514</td>\n",
       "      <td>None</td>\n",
       "      <td>200</td>\n",
       "      <td>1542069417796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fu</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>Arellano</td>\n",
       "      <td>280.05832</td>\n",
       "      <td>free</td>\n",
       "      <td>Harrisburg-Carlisle, PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540007e+12</td>\n",
       "      <td>514</td>\n",
       "      <td>Ja I Ty</td>\n",
       "      <td>200</td>\n",
       "      <td>1542069637796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Maia</td>\n",
       "      <td>F</td>\n",
       "      <td>0</td>\n",
       "      <td>Burke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>free</td>\n",
       "      <td>Houston-The Woodlands-Sugar Land, TX</td>\n",
       "      <td>GET</td>\n",
       "      <td>Home</td>\n",
       "      <td>1.540677e+12</td>\n",
       "      <td>510</td>\n",
       "      <td>None</td>\n",
       "      <td>200</td>\n",
       "      <td>1542071524796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>All Time Low</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Maia</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Burke</td>\n",
       "      <td>177.84118</td>\n",
       "      <td>free</td>\n",
       "      <td>Houston-The Woodlands-Sugar Land, TX</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540677e+12</td>\n",
       "      <td>510</td>\n",
       "      <td>A Party Song (The Walk of Shame)</td>\n",
       "      <td>200</td>\n",
       "      <td>1542071549796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Nik &amp; Jay</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Wyatt</td>\n",
       "      <td>M</td>\n",
       "      <td>0</td>\n",
       "      <td>Scott</td>\n",
       "      <td>196.51873</td>\n",
       "      <td>free</td>\n",
       "      <td>Eureka-Arcata-Fortuna, CA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540872e+12</td>\n",
       "      <td>379</td>\n",
       "      <td>Pop-Pop!</td>\n",
       "      <td>200</td>\n",
       "      <td>1542079142796</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7....</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         artist       auth firstName gender  itemInSession  lastName  \\\n",
       "0          None  Logged In     Kevin      M              0  Arellano   \n",
       "1            Fu  Logged In     Kevin      M              1  Arellano   \n",
       "2          None  Logged In      Maia      F              0     Burke   \n",
       "3  All Time Low  Logged In      Maia      F              1     Burke   \n",
       "4     Nik & Jay  Logged In     Wyatt      M              0     Scott   \n",
       "\n",
       "      length level                              location method      page  \\\n",
       "0        NaN  free               Harrisburg-Carlisle, PA    GET      Home   \n",
       "1  280.05832  free               Harrisburg-Carlisle, PA    PUT  NextSong   \n",
       "2        NaN  free  Houston-The Woodlands-Sugar Land, TX    GET      Home   \n",
       "3  177.84118  free  Houston-The Woodlands-Sugar Land, TX    PUT  NextSong   \n",
       "4  196.51873  free             Eureka-Arcata-Fortuna, CA    PUT  NextSong   \n",
       "\n",
       "   registration  sessionId                              song  status  \\\n",
       "0  1.540007e+12        514                              None     200   \n",
       "1  1.540007e+12        514                           Ja I Ty     200   \n",
       "2  1.540677e+12        510                              None     200   \n",
       "3  1.540677e+12        510  A Party Song (The Walk of Shame)     200   \n",
       "4  1.540872e+12        379                          Pop-Pop!     200   \n",
       "\n",
       "              ts                                          userAgent userId  \n",
       "0  1542069417796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...     66  \n",
       "1  1542069637796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...     66  \n",
       "2  1542071524796  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     51  \n",
       "3  1542071549796  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     51  \n",
       "4  1542079142796  Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7....      9  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(filepath,lines=True)\n",
    "df.head()"
   ]
  },
  {
   "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": 14,
   "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>1</th>\n",
       "      <td>Fu</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>Arellano</td>\n",
       "      <td>280.05832</td>\n",
       "      <td>free</td>\n",
       "      <td>Harrisburg-Carlisle, PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540007e+12</td>\n",
       "      <td>514</td>\n",
       "      <td>Ja I Ty</td>\n",
       "      <td>200</td>\n",
       "      <td>1542069637796</td>\n",
       "      <td>\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>All Time Low</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Maia</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>Burke</td>\n",
       "      <td>177.84118</td>\n",
       "      <td>free</td>\n",
       "      <td>Houston-The Woodlands-Sugar Land, TX</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540677e+12</td>\n",
       "      <td>510</td>\n",
       "      <td>A Party Song (The Walk of Shame)</td>\n",
       "      <td>200</td>\n",
       "      <td>1542071549796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Nik &amp; Jay</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Wyatt</td>\n",
       "      <td>M</td>\n",
       "      <td>0</td>\n",
       "      <td>Scott</td>\n",
       "      <td>196.51873</td>\n",
       "      <td>free</td>\n",
       "      <td>Eureka-Arcata-Fortuna, CA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540872e+12</td>\n",
       "      <td>379</td>\n",
       "      <td>Pop-Pop!</td>\n",
       "      <td>200</td>\n",
       "      <td>1542079142796</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7....</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Quad City DJ's</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Chloe</td>\n",
       "      <td>F</td>\n",
       "      <td>0</td>\n",
       "      <td>Cuevas</td>\n",
       "      <td>451.44771</td>\n",
       "      <td>free</td>\n",
       "      <td>San Francisco-Oakland-Hayward, CA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540941e+12</td>\n",
       "      <td>506</td>\n",
       "      <td>C'mon N' Ride It (The Train) (LP Version)</td>\n",
       "      <td>200</td>\n",
       "      <td>1542081112796</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 5.1; rv:31.0) Gecko/20...</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Hoobastank</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Noah</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>Chavez</td>\n",
       "      <td>232.17587</td>\n",
       "      <td>free</td>\n",
       "      <td>Ogden-Clearfield, UT</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540971e+12</td>\n",
       "      <td>492</td>\n",
       "      <td>Born To Lead</td>\n",
       "      <td>200</td>\n",
       "      <td>1542085206796</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; WOW64; rv:32.0) G...</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           artist       auth firstName gender  itemInSession  lastName  \\\n",
       "1              Fu  Logged In     Kevin      M              1  Arellano   \n",
       "3    All Time Low  Logged In      Maia      F              1     Burke   \n",
       "4       Nik & Jay  Logged In     Wyatt      M              0     Scott   \n",
       "5  Quad City DJ's  Logged In     Chloe      F              0    Cuevas   \n",
       "8      Hoobastank  Logged In      Noah      M              1    Chavez   \n",
       "\n",
       "      length level                              location method      page  \\\n",
       "1  280.05832  free               Harrisburg-Carlisle, PA    PUT  NextSong   \n",
       "3  177.84118  free  Houston-The Woodlands-Sugar Land, TX    PUT  NextSong   \n",
       "4  196.51873  free             Eureka-Arcata-Fortuna, CA    PUT  NextSong   \n",
       "5  451.44771  free     San Francisco-Oakland-Hayward, CA    PUT  NextSong   \n",
       "8  232.17587  free                  Ogden-Clearfield, UT    PUT  NextSong   \n",
       "\n",
       "   registration  sessionId                                       song  status  \\\n",
       "1  1.540007e+12        514                                    Ja I Ty     200   \n",
       "3  1.540677e+12        510           A Party Song (The Walk of Shame)     200   \n",
       "4  1.540872e+12        379                                   Pop-Pop!     200   \n",
       "5  1.540941e+12        506  C'mon N' Ride It (The Train) (LP Version)     200   \n",
       "8  1.540971e+12        492                               Born To Lead     200   \n",
       "\n",
       "              ts                                          userAgent userId  \n",
       "1  1542069637796  \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4...     66  \n",
       "3  1542071549796  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     51  \n",
       "4  1542079142796  Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7....      9  \n",
       "5  1542081112796  Mozilla/5.0 (Windows NT 5.1; rv:31.0) Gecko/20...     49  \n",
       "8  1542085206796  Mozilla/5.0 (Windows NT 6.1; WOW64; rv:32.0) G...     94  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[df['page']=='NextSong']\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "unsupported operand type(s) for /: 'Timestamp' and 'float'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-22-5e3ace2e9765>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mdatetime\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfromtimestamp\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mx\u001b[0m\u001b[0;34m/\u001b[0m\u001b[0;36m1000.0\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m \u001b[0;31m#convert millisecond to seconds before transforming to datetinme\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'start_time'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstrftime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'%Y-%M-%d %H:%M:%S'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'hour'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhour\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'day'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mday\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.6/site-packages/pandas/core/series.py\u001b[0m in \u001b[0;36mapply\u001b[0;34m(self, func, convert_dtype, args, **kwds)\u001b[0m\n\u001b[1;32m   3192\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3193\u001b[0m                 \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3194\u001b[0;31m                 \u001b[0mmapped\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlib\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmap_infer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconvert\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconvert_dtype\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3195\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3196\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmapped\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmapped\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mSeries\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/src/inference.pyx\u001b[0m in \u001b[0;36mpandas._libs.lib.map_infer\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m<ipython-input-22-5e3ace2e9765>\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mdatetime\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfromtimestamp\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mx\u001b[0m\u001b[0;34m/\u001b[0m\u001b[0;36m1000.0\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m \u001b[0;31m#convert millisecond to seconds before transforming to datetinme\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'start_time'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstrftime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'%Y-%M-%d %H:%M:%S'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'hour'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhour\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'day'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ts'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mday\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mTypeError\u001b[0m: unsupported operand type(s) for /: 'Timestamp' and 'float'"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "df['ts'] = df['ts'].apply(lambda x: datetime.fromtimestamp(x/1000.0) ) #convert millisecond to seconds before transforming to datetinme\n",
    "df['start_time'] = df['ts'].apply(lambda x: x.strftime('%Y-%M-%d %H:%M:%S') )\n",
    "df['hour'] = df['ts'].apply(lambda x: x.hour )\n",
    "df['day'] = df['ts'].apply(lambda x: x.day )\n",
    "df['week'] = df['ts'].apply(lambda x: x.isocalendar()[1] )\n",
    "df['month'] = df['ts'].apply(lambda x: x.month )\n",
    "df['year'] = df['ts'].apply(lambda x: x.year )\n",
    "df['weekday'] = df['ts'].apply(lambda x: x.weekday() )\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# time_data = ()\n",
    "# column_labels = ()\n",
    "#df[df['artist']=='Gob']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>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>1</th>\n",
       "      <td>66</td>\n",
       "      <td>2018-40-13 00:40:37</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51</td>\n",
       "      <td>2018-12-13 01:12:29</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>2018-19-13 03:19:02</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>49</td>\n",
       "      <td>2018-51-13 03:51:52</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>94</td>\n",
       "      <td>2018-00-13 05:00:06</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  userId           start_time  hour  day  week  month  year  weekday\n",
       "1     66  2018-40-13 00:40:37     0   13    46     11  2018        1\n",
       "3     51  2018-12-13 01:12:29     1   13    46     11  2018        1\n",
       "4      9  2018-19-13 03:19:02     3   13    46     11  2018        1\n",
       "5     49  2018-51-13 03:51:52     3   13    46     11  2018        1\n",
       "8     94  2018-00-13 05:00:06     5   13    46     11  2018        1"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_df = df[['userId','start_time','hour','day','week','month','year','weekday']]\n",
    "time_df.head()"
   ]
  },
  {
   "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": 29,
   "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": 30,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "user_df = df[['userId','firstName','lastName','gender','level']]"
   ]
  },
  {
   "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": 31,
   "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": 32,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "for index, row in df.iterrows():\n",
    "\n",
    "    # get songid and artistid from song and artist tables\n",
    "    \n",
    "    #row.length\n",
    "    cur.execute(song_select, (row.song,  ,row.artist))\n",
    "    results = cur.fetchone()\n",
    "\n",
    "    if results:\n",
    "        songid, artistid = results\n",
    "    else:\n",
    "        songid, artistid = None, None\n",
    "\n",
    "    # insert songplay record\n",
    "    songplay_data = (row.start_time,row.userId,row.level,songid,artistid,row.sessionId,row.location,row.userAgent)\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": 22,
   "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": []
  },
  {
   "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
}