Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Imports\" data-toc-modified-id=\"Imports-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Imports</a></span></li><li><span><a href=\"#Functions\" data-toc-modified-id=\"Functions-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Functions</a></span></li><li><span><a href=\"#Cleaning:-Lyrics-DataFrame\" data-toc-modified-id=\"Cleaning:-Lyrics-DataFrame-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Cleaning: Lyrics DataFrame</a></span></li><li><span><a href=\"#Database-&amp;-Data-Dictionary\" data-toc-modified-id=\"Database-&amp;-Data-Dictionary-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Database &amp; Data Dictionary</a></span><ul class=\"toc-item\"><li><span><a href=\"#Postgres-Database:-capstone\" data-toc-modified-id=\"Postgres-Database:-capstone-4.1\"><span class=\"toc-item-num\">4.1&nbsp;&nbsp;</span>Postgres Database: capstone</a></span></li><li><span><a href=\"#Data-Dictionary-of-Capstone-Database\" data-toc-modified-id=\"Data-Dictionary-of-Capstone-Database-4.2\"><span class=\"toc-item-num\">4.2&nbsp;&nbsp;</span>Data Dictionary of Capstone Database</a></span></li></ul></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import unidecode\n",
    "import seaborn as sns\n",
    "import matplotlib as plt\n",
    "import string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def wrong_artist_index(dataframe, artists_list):\n",
    "    '''\n",
    "    This is a function which will receive a dataframe, here the freshly scrapped dataframe,\n",
    "    and the list of curated artists as arguments and return the indexes of artists in the dataframe that\n",
    "    are not found in the artists list.\n",
    "    '''\n",
    "    unique_artists = list(dataframe.artist_name.unique())\n",
    "\n",
    "    wrong_artists = []\n",
    "    for i, j in zip([x.lower() for x in unique_artists], unique_artists):\n",
    "        if i not in [x.lower() for x in artists_list]:\n",
    "            wrong_artists.append(j)\n",
    "    return list(dataframe[dataframe.artist_name.isin(wrong_artists)].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_spelling(dataframe, artists_list):\n",
    "    '''\n",
    "    This function accepts a dataframe and an artist list as arguments.\n",
    "    It checks if the artist names from the the dataframe are spelled the same way\n",
    "    than the artists in the artist list.\n",
    "    It returns a list of artist which spelling does not match.\n",
    "    '''\n",
    "\n",
    "    unique_artists = list(dataframe.artist_name.unique())\n",
    "\n",
    "    false_spelling = []\n",
    "    for i in unique_artists:\n",
    "        if i not in artists_list:\n",
    "            false_spelling.append(i)\n",
    "    return false_spelling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning: Lyrics DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Removing duplicated entries\n",
    "\n",
    "* Adjust artist name spellings of the Genius Data to the Spotify spelling\n",
    "\n",
    "* Removing entries with more than primary artists or unwanted artists\n",
    "\n",
    "* Dropping genius_url column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyrics_df = pd.read_csv(\n",
    "    '/Users/constancemaurer/GA DSI 12/DSI12-lessons/projects/project-capstone/personal-github/Resources/lyrics_df.csv'\n",
    ")\n",
    "spotify = pd.read_csv(\n",
    "    '/Users/constancemaurer/GA DSI 12/DSI12-lessons/projects/project-capstone/personal-github/Resources/Spotify_Kaggle_noduplicates.csv'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "artists = [\n",
    "    'John Legend', 'Adele', 'Frank Ocean', 'Aretha Franklin', 'Amy Winehouse',\n",
    "    'The Rolling Stones', 'Led Zeppelin', 'Arctic Monkeys', 'The Beatles',\n",
    "    'Queen', 'Fall Out Boy', 'Tame Impala', 'Beach House', 'Kings of Leon',\n",
    "    'Radiohead', 'Red Hot Chilli Peppers', 'Gorillaz', 'Linkin Park',\n",
    "    'The Black Keys', 'Slipknot', 'Metallica', 'Korn', 'Nirvana',\n",
    "    'Foo Fighters', 'The Neighborhood', 'The Weeknd', 'Kayne West', 'Beyoncé',\n",
    "    'Rihanna', 'Shine Down', 'Kendrick Lamar', 'Florence & The Machine',\n",
    "    'Tyler, The Creator', 'A$AP Rocky', 'Lil Wayne', 'System Of A Down',\n",
    "    'Passion Pit', 'Caribou', 'Twin Shadow', 'HAIM', 'Sister Sledge',\n",
    "    'Taylor Swift', 'Mac Miller', 'Red Hot Chili Peppers', 'The Weeknd',\n",
    "    'Fleetwood Mac', 'Wiz Khalifa', 'Lady Gaga', 'LCD Soundsystem'\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Removing duplicates with .drop_duplicates( )**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyrics_df.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Removing entries with more than primary artists or unwanted artists**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[35, 356, 374, 377, 378]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wrong_art_index = wrong_artist_index(lyrics_df, artists)\n",
    "wrong_art_index[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyrics_df.drop(index=wrong_art_index, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1688, 6)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lyrics_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Adjust artist name spellings of the Genius Data to the Spotify spelling**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Checked which artists are spelled incorrectly by using the check_spelling( ) function (see Functions sections)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['System of a Down']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check_spelling(lyrics_df, artists)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Changed Genius' 'System of a Down' to 'System Of A Down'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyrics_df.artist_name = lyrics_df.artist_name.apply(lambda x: x.replace('System of a Down', 'System Of A Down'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>track_name</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>release_year</th>\n",
       "      <th>spotify_uri</th>\n",
       "      <th>genius_url</th>\n",
       "      <th>lyrics</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [track_name, artist_name, release_year, spotify_uri, genius_url, lyrics]\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lyrics_df[lyrics_df.artist_name=='System of a Down'].head(3)"
   ]
  },
  {
   "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>track_name</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>release_year</th>\n",
       "      <th>spotify_uri</th>\n",
       "      <th>genius_url</th>\n",
       "      <th>lyrics</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1576</td>\n",
       "      <td>Sugar</td>\n",
       "      <td>System Of A Down</td>\n",
       "      <td>1998-05-24</td>\n",
       "      <td>spotify:track:31RTFPrB7wmYBhlkM2ILXG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Chorus: Serj Tankian &amp; Daron Malakian]\\nThe K...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1577</td>\n",
       "      <td>Lonely Day</td>\n",
       "      <td>System Of A Down</td>\n",
       "      <td>2005-11-22</td>\n",
       "      <td>spotify:track:1VNWaY3uNfoeWqb5U8x2QX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Verse 1: Daron Malakian]\\nSuch a lonely day a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1578</td>\n",
       "      <td>Radio/Video</td>\n",
       "      <td>System Of A Down</td>\n",
       "      <td>2005-05-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Intro: Serj Tankian]\\nLa la la la la la\\n\\n[C...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       track_name       artist_name release_year  \\\n",
       "1576        Sugar  System Of A Down   1998-05-24   \n",
       "1577   Lonely Day  System Of A Down   2005-11-22   \n",
       "1578  Radio/Video  System Of A Down   2005-05-17   \n",
       "\n",
       "                               spotify_uri  genius_url  \\\n",
       "1576  spotify:track:31RTFPrB7wmYBhlkM2ILXG         NaN   \n",
       "1577  spotify:track:1VNWaY3uNfoeWqb5U8x2QX         NaN   \n",
       "1578                                   NaN         NaN   \n",
       "\n",
       "                                                 lyrics  \n",
       "1576  [Chorus: Serj Tankian & Daron Malakian]\\nThe K...  \n",
       "1577  [Verse 1: Daron Malakian]\\nSuch a lonely day a...  \n",
       "1578  [Intro: Serj Tankian]\\nLa la la la la la\\n\\n[C...  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lyrics_df[lyrics_df.artist_name=='System Of A Down'].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Dropping the genius_url column**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dropped the genius_url column as the initial code I wrote did not work for the majority of artist (JSON files weren't structured reliably for me). Please try it yourself :)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyrics_df.drop(columns=['genius_url'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>track_name</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>release_year</th>\n",
       "      <th>spotify_uri</th>\n",
       "      <th>lyrics</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Distorted Records</td>\n",
       "      <td>A$AP Rocky</td>\n",
       "      <td>2018-05-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Intro]\\nUh, uh\\n\\n[Pre-Chorus 1]\\nI can feel ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Jukebox Joints</td>\n",
       "      <td>A$AP Rocky</td>\n",
       "      <td>2015-05-26</td>\n",
       "      <td>spotify:track:2gAGWaK4wvt2xrFUlR4mK8</td>\n",
       "      <td>[Part I]\\n\\n[Chorus: A$AP Rocky &amp; Joe Fox]\\nAn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>West Side Highway</td>\n",
       "      <td>A$AP Rocky</td>\n",
       "      <td>2015-05-26</td>\n",
       "      <td>spotify:track:3RySVukMPe4R7lAP8oGI17</td>\n",
       "      <td>[Chorus: A$AP Rocky]\\nThese days of preference...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1Train</td>\n",
       "      <td>A$AP Rocky</td>\n",
       "      <td>2013-01-15</td>\n",
       "      <td>spotify:track:223cmlU9Vd2vNTlfMdWCy4</td>\n",
       "      <td>[Produced by HitBoy]\\n\\n[Verse 1: A$AP Rocky]\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Fashion Killa</td>\n",
       "      <td>A$AP Rocky</td>\n",
       "      <td>2013-01-15</td>\n",
       "      <td>spotify:track:40H5libEZWrbkc8HTlXGbt</td>\n",
       "      <td>[Chorus]\\nHer pistol go…\\n(Doot-doot-doot, ban...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          track_name artist_name release_year  \\\n",
       "0  Distorted Records  A$AP Rocky   2018-05-25   \n",
       "1     Jukebox Joints  A$AP Rocky   2015-05-26   \n",
       "2  West Side Highway  A$AP Rocky   2015-05-26   \n",
       "3             1Train  A$AP Rocky   2013-01-15   \n",
       "4      Fashion Killa  A$AP Rocky   2013-01-15   \n",
       "\n",
       "                            spotify_uri  \\\n",
       "0                                   NaN   \n",
       "1  spotify:track:2gAGWaK4wvt2xrFUlR4mK8   \n",
       "2  spotify:track:3RySVukMPe4R7lAP8oGI17   \n",
       "3  spotify:track:223cmlU9Vd2vNTlfMdWCy4   \n",
       "4  spotify:track:40H5libEZWrbkc8HTlXGbt   \n",
       "\n",
       "                                              lyrics  \n",
       "0  [Intro]\\nUh, uh\\n\\n[Pre-Chorus 1]\\nI can feel ...  \n",
       "1  [Part I]\\n\\n[Chorus: A$AP Rocky & Joe Fox]\\nAn...  \n",
       "2  [Chorus: A$AP Rocky]\\nThese days of preference...  \n",
       "3  [Produced by HitBoy]\\n\\n[Verse 1: A$AP Rocky]\\...  \n",
       "4  [Chorus]\\nHer pistol go…\\n(Doot-doot-doot, ban...  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lyrics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "track_name      object\n",
       "artist_name     object\n",
       "release_year    object\n",
       "spotify_uri     object\n",
       "lyrics          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lyrics_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1688, 5)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lyrics_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyrics_df.to_csv(r'lyrics_table', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Database & Data Dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Generating an SQL database for General Assembly Capstone project\n",
    "\n",
    "* Adding spotify and genius datasets to the database\n",
    "\n",
    "* Creating SQL and pandas query functions for easier navigation\n",
    "\n",
    "* Merging the lyrics and spotify dataset to form model's dataframe\n",
    "\n",
    "* Creating a data dictionary for the raw data and adding it to the SQL database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Postgres Database: capstone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "from pandas.io import sql\n",
    "\n",
    "# DSN (data source name) format for database connections:  \n",
    "# [protocol / database  name]://[username]:[password]@[hostname / ip]:[port]/[database name here]\n",
    "\n",
    "\n",
    "# on your computer you are the user postgres (full administrative access)\n",
    "db_user = 'postgres'\n",
    "# if you need a password to access a database, put it here\n",
    "db_password = ''\n",
    "# on your computer, use localhost\n",
    "db_host = 'localhost'\n",
    "# the default port for postgres is 5432\n",
    "db_port = 5432\n",
    "# we want to creat a database with the following name\n",
    "database = 'gacapstone'\n",
    "#set up a connection to postgres without specifying the database\n",
    "conn_postgres = f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Create an sqlalchemy connection:*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "engine = sqlalchemy.engine.create_engine(conn_postgres)```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Connect using sqlalchemy:*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "connection = engine.connect()```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Commit connection:*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "connection.execute('commit')```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Create database:*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "connection.execute(f\"create database {database}\")```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Close connection:*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "connection.close()```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Opened a new connection to the database & add tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_str = f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{database}'\n",
    "conn = psycopg2.connect(conn_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Connect using sqlalchemy:*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sqlalchemy.engine.create_engine(conn_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "spotify.to_sql(name='spotify_tracks',\n",
    "                  con=engine,\n",
    "                  if_exists='replace',\n",
    "                  index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>genre</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>track_name</th>\n",
       "      <th>track_id</th>\n",
       "      <th>popularity</th>\n",
       "      <th>acousticness</th>\n",
       "      <th>danceability</th>\n",
       "      <th>duration_ms</th>\n",
       "      <th>energy</th>\n",
       "      <th>instrumentalness</th>\n",
       "      <th>key</th>\n",
       "      <th>liveness</th>\n",
       "      <th>loudness</th>\n",
       "      <th>mode</th>\n",
       "      <th>speechiness</th>\n",
       "      <th>tempo</th>\n",
       "      <th>time_signature</th>\n",
       "      <th>valence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Movie</td>\n",
       "      <td>Henri Salvador</td>\n",
       "      <td>C'est beau de faire un Show</td>\n",
       "      <td>0BRjO6ga9RKCKjfDqeFgWV</td>\n",
       "      <td>0</td>\n",
       "      <td>0.611</td>\n",
       "      <td>0.389</td>\n",
       "      <td>99373</td>\n",
       "      <td>0.910</td>\n",
       "      <td>0.000</td>\n",
       "      <td>C#</td>\n",
       "      <td>0.3460</td>\n",
       "      <td>-1.828</td>\n",
       "      <td>Major</td>\n",
       "      <td>0.0525</td>\n",
       "      <td>166.969</td>\n",
       "      <td>4/4</td>\n",
       "      <td>0.814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Movie</td>\n",
       "      <td>Martin &amp; les fées</td>\n",
       "      <td>Perdu d'avance (par Gad Elmaleh)</td>\n",
       "      <td>0BjC1NfoEOOusryehmNudP</td>\n",
       "      <td>1</td>\n",
       "      <td>0.246</td>\n",
       "      <td>0.590</td>\n",
       "      <td>137373</td>\n",
       "      <td>0.737</td>\n",
       "      <td>0.000</td>\n",
       "      <td>F#</td>\n",
       "      <td>0.1510</td>\n",
       "      <td>-5.559</td>\n",
       "      <td>Minor</td>\n",
       "      <td>0.0868</td>\n",
       "      <td>174.003</td>\n",
       "      <td>4/4</td>\n",
       "      <td>0.816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Movie</td>\n",
       "      <td>Joseph Williams</td>\n",
       "      <td>Don't Let Me Be Lonely Tonight</td>\n",
       "      <td>0CoSDzoNIKCRs124s9uTVy</td>\n",
       "      <td>3</td>\n",
       "      <td>0.952</td>\n",
       "      <td>0.663</td>\n",
       "      <td>170267</td>\n",
       "      <td>0.131</td>\n",
       "      <td>0.000</td>\n",
       "      <td>C</td>\n",
       "      <td>0.1030</td>\n",
       "      <td>-13.879</td>\n",
       "      <td>Minor</td>\n",
       "      <td>0.0362</td>\n",
       "      <td>99.488</td>\n",
       "      <td>5/4</td>\n",
       "      <td>0.368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Movie</td>\n",
       "      <td>Henri Salvador</td>\n",
       "      <td>Dis-moi Monsieur Gordon Cooper</td>\n",
       "      <td>0Gc6TVm52BwZD07Ki6tIvf</td>\n",
       "      <td>0</td>\n",
       "      <td>0.703</td>\n",
       "      <td>0.240</td>\n",
       "      <td>152427</td>\n",
       "      <td>0.326</td>\n",
       "      <td>0.000</td>\n",
       "      <td>C#</td>\n",
       "      <td>0.0985</td>\n",
       "      <td>-12.178</td>\n",
       "      <td>Major</td>\n",
       "      <td>0.0395</td>\n",
       "      <td>171.758</td>\n",
       "      <td>4/4</td>\n",
       "      <td>0.227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Movie</td>\n",
       "      <td>Fabien Nataf</td>\n",
       "      <td>Ouverture</td>\n",
       "      <td>0IuslXpMROHdEPvSl1fTQK</td>\n",
       "      <td>4</td>\n",
       "      <td>0.950</td>\n",
       "      <td>0.331</td>\n",
       "      <td>82625</td>\n",
       "      <td>0.225</td>\n",
       "      <td>0.123</td>\n",
       "      <td>F</td>\n",
       "      <td>0.2020</td>\n",
       "      <td>-21.150</td>\n",
       "      <td>Major</td>\n",
       "      <td>0.0456</td>\n",
       "      <td>140.576</td>\n",
       "      <td>4/4</td>\n",
       "      <td>0.390</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   genre        artist_name                        track_name  \\\n",
       "0  Movie     Henri Salvador       C'est beau de faire un Show   \n",
       "1  Movie  Martin & les fées  Perdu d'avance (par Gad Elmaleh)   \n",
       "2  Movie    Joseph Williams    Don't Let Me Be Lonely Tonight   \n",
       "3  Movie     Henri Salvador    Dis-moi Monsieur Gordon Cooper   \n",
       "4  Movie       Fabien Nataf                         Ouverture   \n",
       "\n",
       "                 track_id  popularity  acousticness  danceability  \\\n",
       "0  0BRjO6ga9RKCKjfDqeFgWV           0         0.611         0.389   \n",
       "1  0BjC1NfoEOOusryehmNudP           1         0.246         0.590   \n",
       "2  0CoSDzoNIKCRs124s9uTVy           3         0.952         0.663   \n",
       "3  0Gc6TVm52BwZD07Ki6tIvf           0         0.703         0.240   \n",
       "4  0IuslXpMROHdEPvSl1fTQK           4         0.950         0.331   \n",
       "\n",
       "   duration_ms  energy  instrumentalness key  liveness  loudness   mode  \\\n",
       "0        99373   0.910             0.000  C#    0.3460    -1.828  Major   \n",
       "1       137373   0.737             0.000  F#    0.1510    -5.559  Minor   \n",
       "2       170267   0.131             0.000   C    0.1030   -13.879  Minor   \n",
       "3       152427   0.326             0.000  C#    0.0985   -12.178  Major   \n",
       "4        82625   0.225             0.123   F    0.2020   -21.150  Major   \n",
       "\n",
       "   speechiness    tempo time_signature  valence  \n",
       "0       0.0525  166.969            4/4    0.814  \n",
       "1       0.0868  174.003            4/4    0.816  \n",
       "2       0.0362   99.488            5/4    0.368  \n",
       "3       0.0395  171.758            4/4    0.227  \n",
       "4       0.0456  140.576            4/4    0.390  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''SELECT * FROM spotify_tracks'''\n",
    "\n",
    "df = pd.read_sql(query, con=conn)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "genius_table = pd.read_csv('/Users/constancemaurer/GA DSI 12/DSI12-lessons/projects/project-capstone/personal-github/Resources/lyrics_table')\n",
    "\n",
    "genius_table.to_sql(name='genius_lyrics',\n",
    "                    con=engine,\n",
    "                    if_exists='replace',\n",
    "                    index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Function to query sql more flexibily and return SQL or pandas dataframe:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sql_Q(query, conn=conn):\n",
    "    '''This function queries SQL databases by \n",
    "    accepting a query string and the connection.\n",
    "    It returns an SQL table.'''\n",
    "    return sql.read_sql(query, con=conn)\n",
    "\n",
    "\n",
    "def pd_Q(query, conn=conn):\n",
    "    '''This function queries SQL databases for tables by \n",
    "    accepting a query string and the connection.\n",
    "    It returns a pandas dataframe.'''\n",
    "    return pd.read_sql(query, con=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Extracting tables as pandas dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "g = pd_Q('''SELECT * FROM genius_lyrics \n",
    "        ORDER BY artist_name''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd_Q('''SELECT * FROM spotify_tracks\n",
    "             ORDER BY artist_name''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Merging the genius_lyrics (g) with the matching spotify_tracks (s) rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "new = g.merge(s, how='inner', left_on=['track_name', 'artist_name'], right_on=['track_name', 'artist_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "new.to_csv('data_RAW.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data Dictionary of Capstone Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the data dictionary, I created a dataframe and then added it as a table to the postgres capstone database.\n",
    "The features/variables of the original spotify dataset, sourced from a Kaggle competition (https://www.kaggle.com/zaheenhamidani/ultimate-spotify-tracks-db/data), I described using spotify's own documentation given here (https://developer.spotify.com/documentation/web-api/reference/tracks/get-audio-features/). The data dictionary contains both the spotify_tracks table and the genius_lyrics table's variables. The data dictionary contains information on:\n",
    "    - variable name\n",
    "    - description of the variable\n",
    "    - data type of the variable\n",
    "    - location (referring to which table) the variable belongs to"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Extracted the variable names by appending the column names of the two tables to a list. From genius_lyrics table I excluded the track_name and artist_name as these are contained in both tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "variables = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "for item in pd_Q('''SELECT * FROM spotify_tracks\n",
    "        LIMIT 1''').columns:\n",
    "    variables.append(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "for item in pd_Q('''SELECT * FROM genius_lyrics\n",
    "        LIMIT 1''').columns[2:]:\n",
    "    variables.append(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['genre',\n",
       " 'artist_name',\n",
       " 'track_name',\n",
       " 'track_id',\n",
       " 'popularity',\n",
       " 'acousticness',\n",
       " 'danceability',\n",
       " 'duration_ms',\n",
       " 'energy',\n",
       " 'instrumentalness',\n",
       " 'key',\n",
       " 'liveness',\n",
       " 'loudness',\n",
       " 'mode',\n",
       " 'speechiness',\n",
       " 'tempo',\n",
       " 'time_signature',\n",
       " 'valence',\n",
       " 'release_year',\n",
       " 'spotify_uri',\n",
       " 'lyrics']"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Defined variables matching the column names to write their description as a string. I created a dictionary with the variable names as one entry and appended the descriptions as another entry."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "genre = '''The music genre of the song.'''\n",
    "\n",
    "artist_n = '''The artist or band name of the song.'''\n",
    "\n",
    "track_n = '''The track name is simply title of the song.'''\n",
    "\n",
    "track_id = '''The spotify ID number which at the time the dataset was collected functioned as the uri but might not match with current uris and IDs.'''\n",
    "\n",
    "popularity = '''Generally speaking, songs that are being played a lot now will have a higher popularity than songs that were played a lot in the past. Duplicate tracks (e.g. the same track from a single and an album) are rated independently. Artist and album popularity is derived mathematically from track popularity. Note that the popularity value may lag actual popularity by a few days: the value is not updated in real time.'''\n",
    "\n",
    "acousticness = '''A confidence measure from 0.0 to 1.0 of whether the track is acoustic. 1.0 represents high confidence the track is acoustic.'''\n",
    "\n",
    "danceability = '''Danceability describes how suitable a track is for dancing based on a combination of musical elements including tempo, rhythm stability, beat strength, and overall regularity. A value of 0.0 is least danceable and 1.0 is most danceable.'''\n",
    "\n",
    "duration_ms = '''The duration of the track in milliseconds.'''\n",
    "\n",
    "energy = '''Energy is a measure from 0.0 to 1.0 and represents a perceptual measure of intensity and activity. Typically, energetic tracks feel fast, loud, and noisy. For example, death metal has high energy, while a Bach prelude scores low on the scale. Perceptual features contributing to this attribute include dynamic range, perceived loudness, timbre, onset rate, and general entropy.'''\n",
    "\n",
    "instrumentalness = '''Predicts whether a track contains no vocals. “Ooh” and “aah” sounds are treated as instrumental in this context. Rap or spoken word tracks are clearly “vocal”. The closer the instrumentalness value is to 1.0, the greater likelihood the track contains no vocal content. Values above 0.5 are intended to represent instrumental tracks, but confidence is higher as the value approaches 1.0.'''\n",
    "\n",
    "key = '''The estimated overall key of the track. Integers map to pitches using standard Pitch Class notation. E.g. 0 = C, 1 = C♯/D♭, 2 = D, and so on. If no key was detected, the value is -1.'''\n",
    "\n",
    "liveness = '''Detects the presence of an audience in the recording. Higher liveness values represent an increased probability that the track was performed live. A value above 0.8 provides strong likelihood that the track is live.'''\n",
    "loudness = '''The overall loudness of a track in decibels (dB). Loudness values are averaged across the entire track and are useful for comparing relative loudness of tracks. Loudness is the quality of a sound that is the primary psychological correlate of physical strength (amplitude). Values typical range between -60 and 0 db.'''\n",
    "\n",
    "mode = '''Mode indicates the modality (major or minor) of a track, the type of scale from which its melodic content is derived. Major is represented by 1 and minor is 0.'''\n",
    "\n",
    "speechiness = '''Speechiness detects the presence of spoken words in a track. The more exclusively speech-like the recording (e.g. talk show, audio book, poetry), the closer to 1.0 the attribute value. Values above 0.66 describe tracks that are probably made entirely of spoken words. Values between 0.33 and 0.66 describe tracks that may contain both music and speech, either in sections or layered, including such cases as rap music. Values below 0.33 most likely represent music and other non-speech-like tracks.'''\n",
    "\n",
    "tempo = '''The overall estimated tempo of a track in beats per minute (BPM). In musical terminology, tempo is the speed or pace of a given piece and derives directly from the average beat duration.'''\n",
    "\n",
    "time_signature = '''An estimated overall time signature of a track. The time signature (meter) is a notational convention to specify how many beats are in each bar (or measure).'''\n",
    "\n",
    "valence = '''A measure from 0.0 to 1.0 describing the musical positiveness conveyed by a track. Tracks with high valence sound more positive (e.g. happy, cheerful, euphoric), while tracks with low valence sound more negative (e.g. sad, depressed, angry).'''\n",
    "\n",
    "release_year = '''The date the track/song was released.'''\n",
    "\n",
    "spotify_uri = '''Genius' record of spotify uri for a track.'''\n",
    "\n",
    "lyrics = '''Web scrapped lyrics from genius.com website.'''```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dictionary = {'Variable': variables}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dictionary['Description'] = [\n",
    "    genre, artist_n, track_n, track_id, popularity, acousticness, danceability,\n",
    "    duration_ms, energy, instrumentalness, key, liveness, loudness, mode,\n",
    "    speechiness, tempo, time_signature, valence, release_year, spotify_uri,\n",
    "    lyrics\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Checked all the current data types of the variables and added them to the data dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "genre                object\n",
       "artist_name          object\n",
       "track_name           object\n",
       "track_id             object\n",
       "popularity            int64\n",
       "acousticness        float64\n",
       "danceability        float64\n",
       "duration_ms           int64\n",
       "energy              float64\n",
       "instrumentalness    float64\n",
       "key                  object\n",
       "liveness            float64\n",
       "loudness            float64\n",
       "mode                 object\n",
       "speechiness         float64\n",
       "tempo               float64\n",
       "time_signature       object\n",
       "valence             float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_Q('''SELECT * FROM spotify_tracks \n",
    "        ORDER BY artist_name\n",
    "        LIMIT 1''').dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "track_name      object\n",
       "artist_name     object\n",
       "release_year    object\n",
       "spotify_uri     object\n",
       "lyrics          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_Q('''SELECT * FROM genius_lyrics \n",
    "        LIMIT 1''').dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dictionary['Data Type'] = [\n",
    "    'string object', 'string object', 'string object', 'string object',\n",
    "    'int64', 'float64', 'float64', 'int64', 'float64', 'float64',\n",
    "    'string object', 'float64', 'float64', 'string object', 'float64',\n",
    "    'float64', 'string object', 'float 64',\n",
    "    'string object - has to be changed to datetime', 'string object',\n",
    "    'string object'\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Added the location of each variables to the data dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "locations = [['spotify_track', 'genius_lyrics'],\n",
    "             ['spotify_track', 'genius_lyrics']]\n",
    "\n",
    "for i in range(0 + 2,\n",
    "               len(pd_Q('''SELECT * FROM spotify_tracks LIMIT 1''').columns)):\n",
    "    locations.append(['spotify_track'])\n",
    "\n",
    "for i in range(0 + 2,\n",
    "               len(pd_Q('''SELECT * FROM genius_lyrics LIMIT 1''').columns)):\n",
    "    locations.append(['genius_lyrics'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dictionary['Location'] = locations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>Variable</th>\n",
       "      <th>Description</th>\n",
       "      <th>Data Type</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>genre</td>\n",
       "      <td>The music genre of the song.</td>\n",
       "      <td>string object</td>\n",
       "      <td>[spotify_track, genius_lyrics]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>artist_name</td>\n",
       "      <td>The artist or band name of the song.</td>\n",
       "      <td>string object</td>\n",
       "      <td>[spotify_track, genius_lyrics]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>track_name</td>\n",
       "      <td>The track name is simply title of the song.</td>\n",
       "      <td>string object</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>track_id</td>\n",
       "      <td>The spotify ID number which at the time the da...</td>\n",
       "      <td>string object</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>popularity</td>\n",
       "      <td>Generally speaking, songs that are being playe...</td>\n",
       "      <td>int64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>acousticness</td>\n",
       "      <td>A confidence measure from 0.0 to 1.0 of whethe...</td>\n",
       "      <td>float64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>danceability</td>\n",
       "      <td>Danceability describes how suitable a track is...</td>\n",
       "      <td>float64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>duration_ms</td>\n",
       "      <td>The duration of the track in milliseconds.</td>\n",
       "      <td>int64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>energy</td>\n",
       "      <td>Energy is a measure from 0.0 to 1.0 and repres...</td>\n",
       "      <td>float64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>instrumentalness</td>\n",
       "      <td>Predicts whether a track contains no vocals. “...</td>\n",
       "      <td>float64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>key</td>\n",
       "      <td>The estimated overall key of the track. Intege...</td>\n",
       "      <td>string object</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>liveness</td>\n",
       "      <td>Detects the presence of an audience in the rec...</td>\n",
       "      <td>float64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>loudness</td>\n",
       "      <td>The overall loudness of a track in decibels (d...</td>\n",
       "      <td>float64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>mode</td>\n",
       "      <td>Mode indicates the modality (major or minor) o...</td>\n",
       "      <td>string object</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>speechiness</td>\n",
       "      <td>Speechiness detects the presence of spoken wor...</td>\n",
       "      <td>float64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>tempo</td>\n",
       "      <td>The overall estimated tempo of a track in beat...</td>\n",
       "      <td>float64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>time_signature</td>\n",
       "      <td>An estimated overall time signature of a track...</td>\n",
       "      <td>string object</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>valence</td>\n",
       "      <td>A measure from 0.0 to 1.0 describing the music...</td>\n",
       "      <td>float 64</td>\n",
       "      <td>[spotify_track]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>release_year</td>\n",
       "      <td>The date the track/song was released.</td>\n",
       "      <td>string object - has to be changed to datetime</td>\n",
       "      <td>[genius_lyrics]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>spotify_uri</td>\n",
       "      <td>Genius' record of spotify uri for a track.</td>\n",
       "      <td>string object</td>\n",
       "      <td>[genius_lyrics]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>lyrics</td>\n",
       "      <td>Web scrapped lyrics from genius.com website.</td>\n",
       "      <td>string object</td>\n",
       "      <td>[genius_lyrics]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Variable                                        Description  \\\n",
       "0              genre                       The music genre of the song.   \n",
       "1        artist_name               The artist or band name of the song.   \n",
       "2         track_name        The track name is simply title of the song.   \n",
       "3           track_id  The spotify ID number which at the time the da...   \n",
       "4         popularity  Generally speaking, songs that are being playe...   \n",
       "5       acousticness  A confidence measure from 0.0 to 1.0 of whethe...   \n",
       "6       danceability  Danceability describes how suitable a track is...   \n",
       "7        duration_ms         The duration of the track in milliseconds.   \n",
       "8             energy  Energy is a measure from 0.0 to 1.0 and repres...   \n",
       "9   instrumentalness  Predicts whether a track contains no vocals. “...   \n",
       "10               key  The estimated overall key of the track. Intege...   \n",
       "11          liveness  Detects the presence of an audience in the rec...   \n",
       "12          loudness  The overall loudness of a track in decibels (d...   \n",
       "13              mode  Mode indicates the modality (major or minor) o...   \n",
       "14       speechiness  Speechiness detects the presence of spoken wor...   \n",
       "15             tempo  The overall estimated tempo of a track in beat...   \n",
       "16    time_signature  An estimated overall time signature of a track...   \n",
       "17           valence  A measure from 0.0 to 1.0 describing the music...   \n",
       "18      release_year              The date the track/song was released.   \n",
       "19       spotify_uri         Genius' record of spotify uri for a track.   \n",
       "20            lyrics       Web scrapped lyrics from genius.com website.   \n",
       "\n",
       "                                        Data Type  \\\n",
       "0                                   string object   \n",
       "1                                   string object   \n",
       "2                                   string object   \n",
       "3                                   string object   \n",
       "4                                           int64   \n",
       "5                                         float64   \n",
       "6                                         float64   \n",
       "7                                           int64   \n",
       "8                                         float64   \n",
       "9                                         float64   \n",
       "10                                  string object   \n",
       "11                                        float64   \n",
       "12                                        float64   \n",
       "13                                  string object   \n",
       "14                                        float64   \n",
       "15                                        float64   \n",
       "16                                  string object   \n",
       "17                                       float 64   \n",
       "18  string object - has to be changed to datetime   \n",
       "19                                  string object   \n",
       "20                                  string object   \n",
       "\n",
       "                          Location  \n",
       "0   [spotify_track, genius_lyrics]  \n",
       "1   [spotify_track, genius_lyrics]  \n",
       "2                  [spotify_track]  \n",
       "3                  [spotify_track]  \n",
       "4                  [spotify_track]  \n",
       "5                  [spotify_track]  \n",
       "6                  [spotify_track]  \n",
       "7                  [spotify_track]  \n",
       "8                  [spotify_track]  \n",
       "9                  [spotify_track]  \n",
       "10                 [spotify_track]  \n",
       "11                 [spotify_track]  \n",
       "12                 [spotify_track]  \n",
       "13                 [spotify_track]  \n",
       "14                 [spotify_track]  \n",
       "15                 [spotify_track]  \n",
       "16                 [spotify_track]  \n",
       "17                 [spotify_track]  \n",
       "18                 [genius_lyrics]  \n",
       "19                 [genius_lyrics]  \n",
       "20                 [genius_lyrics]  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict_df = pd.DataFrame(data_dictionary)\n",
    "data_dict_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Added the data dictionary to the gacapstone database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict_df.to_sql(name = 'data_dictionary', con = engine, if_exists = 'replace', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>Variable</th>\n",
       "      <th>Description</th>\n",
       "      <th>Data Type</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>genre</td>\n",
       "      <td>The music genre of the song.</td>\n",
       "      <td>string object</td>\n",
       "      <td>{spotify_track,genius_lyrics}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>artist_name</td>\n",
       "      <td>The artist or band name of the song.</td>\n",
       "      <td>string object</td>\n",
       "      <td>{spotify_track,genius_lyrics}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>track_name</td>\n",
       "      <td>The track name is simply title of the song.</td>\n",
       "      <td>string object</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>track_id</td>\n",
       "      <td>The spotify ID number which at the time the da...</td>\n",
       "      <td>string object</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>popularity</td>\n",
       "      <td>Generally speaking, songs that are being playe...</td>\n",
       "      <td>int64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>acousticness</td>\n",
       "      <td>A confidence measure from 0.0 to 1.0 of whethe...</td>\n",
       "      <td>float64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>danceability</td>\n",
       "      <td>Danceability describes how suitable a track is...</td>\n",
       "      <td>float64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>duration_ms</td>\n",
       "      <td>The duration of the track in milliseconds.</td>\n",
       "      <td>int64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>energy</td>\n",
       "      <td>Energy is a measure from 0.0 to 1.0 and repres...</td>\n",
       "      <td>float64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>instrumentalness</td>\n",
       "      <td>Predicts whether a track contains no vocals. “...</td>\n",
       "      <td>float64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>key</td>\n",
       "      <td>The estimated overall key of the track. Intege...</td>\n",
       "      <td>string object</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>liveness</td>\n",
       "      <td>Detects the presence of an audience in the rec...</td>\n",
       "      <td>float64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>loudness</td>\n",
       "      <td>The overall loudness of a track in decibels (d...</td>\n",
       "      <td>float64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>mode</td>\n",
       "      <td>Mode indicates the modality (major or minor) o...</td>\n",
       "      <td>string object</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>speechiness</td>\n",
       "      <td>Speechiness detects the presence of spoken wor...</td>\n",
       "      <td>float64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>tempo</td>\n",
       "      <td>The overall estimated tempo of a track in beat...</td>\n",
       "      <td>float64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>time_signature</td>\n",
       "      <td>An estimated overall time signature of a track...</td>\n",
       "      <td>string object</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>valence</td>\n",
       "      <td>A measure from 0.0 to 1.0 describing the music...</td>\n",
       "      <td>float 64</td>\n",
       "      <td>{spotify_track}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>release_year</td>\n",
       "      <td>The date the track/song was released.</td>\n",
       "      <td>string object - has to be changed to datetime</td>\n",
       "      <td>{genius_lyrics}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>spotify_uri</td>\n",
       "      <td>Genius' record of spotify uri for a track.</td>\n",
       "      <td>string object</td>\n",
       "      <td>{genius_lyrics}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>lyrics</td>\n",
       "      <td>Web scrapped lyrics from genius.com website.</td>\n",
       "      <td>string object</td>\n",
       "      <td>{genius_lyrics}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Variable                                        Description  \\\n",
       "0              genre                       The music genre of the song.   \n",
       "1        artist_name               The artist or band name of the song.   \n",
       "2         track_name        The track name is simply title of the song.   \n",
       "3           track_id  The spotify ID number which at the time the da...   \n",
       "4         popularity  Generally speaking, songs that are being playe...   \n",
       "5       acousticness  A confidence measure from 0.0 to 1.0 of whethe...   \n",
       "6       danceability  Danceability describes how suitable a track is...   \n",
       "7        duration_ms         The duration of the track in milliseconds.   \n",
       "8             energy  Energy is a measure from 0.0 to 1.0 and repres...   \n",
       "9   instrumentalness  Predicts whether a track contains no vocals. “...   \n",
       "10               key  The estimated overall key of the track. Intege...   \n",
       "11          liveness  Detects the presence of an audience in the rec...   \n",
       "12          loudness  The overall loudness of a track in decibels (d...   \n",
       "13              mode  Mode indicates the modality (major or minor) o...   \n",
       "14       speechiness  Speechiness detects the presence of spoken wor...   \n",
       "15             tempo  The overall estimated tempo of a track in beat...   \n",
       "16    time_signature  An estimated overall time signature of a track...   \n",
       "17           valence  A measure from 0.0 to 1.0 describing the music...   \n",
       "18      release_year              The date the track/song was released.   \n",
       "19       spotify_uri         Genius' record of spotify uri for a track.   \n",
       "20            lyrics       Web scrapped lyrics from genius.com website.   \n",
       "\n",
       "                                        Data Type  \\\n",
       "0                                   string object   \n",
       "1                                   string object   \n",
       "2                                   string object   \n",
       "3                                   string object   \n",
       "4                                           int64   \n",
       "5                                         float64   \n",
       "6                                         float64   \n",
       "7                                           int64   \n",
       "8                                         float64   \n",
       "9                                         float64   \n",
       "10                                  string object   \n",
       "11                                        float64   \n",
       "12                                        float64   \n",
       "13                                  string object   \n",
       "14                                        float64   \n",
       "15                                        float64   \n",
       "16                                  string object   \n",
       "17                                       float 64   \n",
       "18  string object - has to be changed to datetime   \n",
       "19                                  string object   \n",
       "20                                  string object   \n",
       "\n",
       "                         Location  \n",
       "0   {spotify_track,genius_lyrics}  \n",
       "1   {spotify_track,genius_lyrics}  \n",
       "2                 {spotify_track}  \n",
       "3                 {spotify_track}  \n",
       "4                 {spotify_track}  \n",
       "5                 {spotify_track}  \n",
       "6                 {spotify_track}  \n",
       "7                 {spotify_track}  \n",
       "8                 {spotify_track}  \n",
       "9                 {spotify_track}  \n",
       "10                {spotify_track}  \n",
       "11                {spotify_track}  \n",
       "12                {spotify_track}  \n",
       "13                {spotify_track}  \n",
       "14                {spotify_track}  \n",
       "15                {spotify_track}  \n",
       "16                {spotify_track}  \n",
       "17                {spotify_track}  \n",
       "18                {genius_lyrics}  \n",
       "19                {genius_lyrics}  \n",
       "20                {genius_lyrics}  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_Q('''SELECT * FROM data_dictionary ''')"
   ]
  }
 ],
 "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}