tabular_data_pt.1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lecture 2.2: Tabular Data Pt.1\n",
    "\n",
    "\n",
    "This lecture, we are going to investigate the data exploration powers of [pandas](https://pandas.pydata.org/).\n",
    "\n",
    "**Learning goals:**\n",
    "- Index and select from Dataframes\n",
    "- Clean missing values\n",
    "- Combine Dataframes\n",
    "---\n",
    "\n",
    "## 1. Indexing and selecting data\n",
    "\n",
    "Since we will using `DataFrame`s _often_, it is important to get comfortable with common manipulation operations with pandas 🐼, such as indexing and selecting data.\n",
    "\n",
    "### 1.1 `[]`\n",
    "\n",
    "There are three main ways of selecting data in a `DataFrame`. The simplest is `[]`, i.e the standard python list notation. \n",
    "\n",
    "\n",
    "On a `DataFrame` object, column name(s) are passed as argument(s) to return the selected columns: \n",
    "\n",
    "    column_a = df['A']\n",
    "    \n",
    "On a `Series` object, an index label is used as argument to return the selected value:\n",
    "    \n",
    "    value_4 = s[4]\n",
    "    \n",
    "Let's try this out on a real dataset. The `top50.csv` file contains data on the spotify top songs of 2019. 🎵\n",
    "We can load this conveniently with the `.read_csv()` function, one of many [i/o tools](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html) in pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>Unnamed: 0</th>\n",
       "      <th>Track.Name</th>\n",
       "      <th>Artist.Name</th>\n",
       "      <th>Genre</th>\n",
       "      <th>Beats.Per.Minute</th>\n",
       "      <th>Energy</th>\n",
       "      <th>Danceability</th>\n",
       "      <th>Loudness..dB..</th>\n",
       "      <th>Liveness</th>\n",
       "      <th>Valence.</th>\n",
       "      <th>Length.</th>\n",
       "      <th>Acousticness..</th>\n",
       "      <th>Speechiness.</th>\n",
       "      <th>Popularity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Señorita</td>\n",
       "      <td>Shawn Mendes</td>\n",
       "      <td>canadian pop</td>\n",
       "      <td>117</td>\n",
       "      <td>55</td>\n",
       "      <td>76</td>\n",
       "      <td>-6</td>\n",
       "      <td>8</td>\n",
       "      <td>75</td>\n",
       "      <td>191</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>China</td>\n",
       "      <td>Anuel AA</td>\n",
       "      <td>reggaeton flow</td>\n",
       "      <td>105</td>\n",
       "      <td>81</td>\n",
       "      <td>79</td>\n",
       "      <td>-4</td>\n",
       "      <td>8</td>\n",
       "      <td>61</td>\n",
       "      <td>302</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>boyfriend (with Social House)</td>\n",
       "      <td>Ariana Grande</td>\n",
       "      <td>dance pop</td>\n",
       "      <td>190</td>\n",
       "      <td>80</td>\n",
       "      <td>40</td>\n",
       "      <td>-4</td>\n",
       "      <td>16</td>\n",
       "      <td>70</td>\n",
       "      <td>186</td>\n",
       "      <td>12</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Beautiful People (feat. Khalid)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>93</td>\n",
       "      <td>65</td>\n",
       "      <td>64</td>\n",
       "      <td>-8</td>\n",
       "      <td>8</td>\n",
       "      <td>55</td>\n",
       "      <td>198</td>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Goodbyes (Feat. Young Thug)</td>\n",
       "      <td>Post Malone</td>\n",
       "      <td>dfw rap</td>\n",
       "      <td>150</td>\n",
       "      <td>65</td>\n",
       "      <td>58</td>\n",
       "      <td>-4</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "      <td>175</td>\n",
       "      <td>45</td>\n",
       "      <td>7</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                       Track.Name    Artist.Name           Genre  \\\n",
       "0           1                         Señorita   Shawn Mendes    canadian pop   \n",
       "1           2                            China       Anuel AA  reggaeton flow   \n",
       "2           3    boyfriend (with Social House)  Ariana Grande       dance pop   \n",
       "3           4  Beautiful People (feat. Khalid)     Ed Sheeran             pop   \n",
       "4           5      Goodbyes (Feat. Young Thug)    Post Malone         dfw rap   \n",
       "\n",
       "   Beats.Per.Minute  Energy  Danceability  Loudness..dB..  Liveness  Valence.  \\\n",
       "0               117      55            76              -6         8        75   \n",
       "1               105      81            79              -4         8        61   \n",
       "2               190      80            40              -4        16        70   \n",
       "3                93      65            64              -8         8        55   \n",
       "4               150      65            58              -4        11        18   \n",
       "\n",
       "   Length.  Acousticness..  Speechiness.  Popularity  \n",
       "0      191               4             3          79  \n",
       "1      302               8             9          92  \n",
       "2      186              12            46          85  \n",
       "3      198              12            19          86  \n",
       "4      175              45             7          94  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_csv('top50.csv', encoding = \"latin\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.head()` method gives us an overview of the first five rows. There are a lot of columns though... Let's just select the artist column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     Shawn Mendes\n",
       "1         Anuel AA\n",
       "2    Ariana Grande\n",
       "3       Ed Sheeran\n",
       "4      Post Malone\n",
       "Name: Artist.Name, dtype: object"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Artist.Name'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we would like to see the song names too. This can be done with the `[]` operator by passing a list of column names. The double `[[` looks a bit goofy, but it works!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Señorita</td>\n",
       "      <td>Shawn Mendes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>China</td>\n",
       "      <td>Anuel AA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>boyfriend (with Social House)</td>\n",
       "      <td>Ariana Grande</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Beautiful People (feat. Khalid)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Goodbyes (Feat. Young Thug)</td>\n",
       "      <td>Post Malone</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Track.Name    Artist.Name\n",
       "0                         Señorita   Shawn Mendes\n",
       "1                            China       Anuel AA\n",
       "2    boyfriend (with Social House)  Ariana Grande\n",
       "3  Beautiful People (feat. Khalid)     Ed Sheeran\n",
       "4      Goodbyes (Feat. Young Thug)    Post Malone"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['Track.Name', 'Artist.Name']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we would like to index (add) data? The `[]` notation offers a neat way to add or replace columns in dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    120\n",
       "1    108\n",
       "2    193\n",
       "3     96\n",
       "4    153\n",
       "Name: Beats.Per.Minute.But.A.Bit.Faster, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Beats.Per.Minute.But.A.Bit.Faster'] = df['Beats.Per.Minute'] + 3\n",
    "df['Beats.Per.Minute.But.A.Bit.Faster'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ℹ️ Adding or replacing entire columns is fine, but careful with trying to set specific row values with the double `[][]` notation. This can lead to problems because the `[]` selector doesn't consistently return a view or a copy of the data. In those cases, pandas will raise a `SettingWithCopy` warning. More details [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#indexing-view-versus-copy)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 `.loc[]`\n",
    "\n",
    "`[]` is a convenient way to select data, but not the best way. Don't take my word for it, instead listen to the pandas [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html):\n",
    "\n",
    ">The Python and NumPy indexing operators [] and attribute operator . provide quick and easy access to pandas data structures across a wide range of use cases. This makes interactive work intuitive, as there’s little new to learn if you already know how to deal with Python dictionaries and NumPy arrays. However, since the type of the data to be accessed isn’t known in advance, directly using standard operators has some optimization limits. For production code, we recommended that you take advantage of the optimized pandas data access methods exposed in this chapter.\n",
    "\n",
    "One of these \"optimized pandas data access methods\" is `.loc[]`. It's a _label_ based selection method. It can be used with one or two arguments:\n",
    "\n",
    "    df.loc[row_label]\n",
    "    df.loc[row_label, column_label]\n",
    "\n",
    "ℹ️ `.loc[]`'s first argument is the _row_ label. Remember that this is different from column names with `[]`! The reason `.loc[]` uses this \"row first\" notation, is because often we'll want to select on rows, and not on columns. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0                                    5\n",
       "Track.Name          Goodbyes (Feat. Young Thug)\n",
       "Artist.Name                         Post Malone\n",
       "Genre                                   dfw rap\n",
       "Beats.Per.Minute                            150\n",
       "Energy                                       65\n",
       "Danceability                                 58\n",
       "Loudness..dB..                               -4\n",
       "Liveness                                     11\n",
       "Valence.                                     18\n",
       "Length.                                     175\n",
       "Acousticness..                               45\n",
       "Speechiness.                                  7\n",
       "Popularity                                   94\n",
       "Name: 4, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select a row\n",
    "df.loc[4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Goodbyes (Feat. Young Thug)'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select a cell\n",
    "df.loc[4, 'Track.Name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     Shawn Mendes\n",
       "1         Anuel AA\n",
       "2    Ariana Grande\n",
       "3       Ed Sheeran\n",
       "4      Post Malone\n",
       "Name: Artist.Name, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#select a column\n",
    "df.loc[:, 'Artist.Name'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`.loc[]` is also cool because it supports label _slicing_. 😎"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Unnamed: 0</th>\n",
       "      <th>Track.Name</th>\n",
       "      <th>Artist.Name</th>\n",
       "      <th>Genre</th>\n",
       "      <th>Beats.Per.Minute</th>\n",
       "      <th>Energy</th>\n",
       "      <th>Danceability</th>\n",
       "      <th>Loudness..dB..</th>\n",
       "      <th>Liveness</th>\n",
       "      <th>Valence.</th>\n",
       "      <th>Length.</th>\n",
       "      <th>Acousticness..</th>\n",
       "      <th>Speechiness.</th>\n",
       "      <th>Popularity</th>\n",
       "      <th>Beats.Per.Minute.But.A.Bit.Faster</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>boyfriend (with Social House)</td>\n",
       "      <td>Ariana Grande</td>\n",
       "      <td>dance pop</td>\n",
       "      <td>190</td>\n",
       "      <td>80</td>\n",
       "      <td>40</td>\n",
       "      <td>-4</td>\n",
       "      <td>16</td>\n",
       "      <td>70</td>\n",
       "      <td>186</td>\n",
       "      <td>12</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Beautiful People (feat. Khalid)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>93</td>\n",
       "      <td>65</td>\n",
       "      <td>64</td>\n",
       "      <td>-8</td>\n",
       "      <td>8</td>\n",
       "      <td>55</td>\n",
       "      <td>198</td>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "      <td>86</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Goodbyes (Feat. Young Thug)</td>\n",
       "      <td>Post Malone</td>\n",
       "      <td>dfw rap</td>\n",
       "      <td>150</td>\n",
       "      <td>65</td>\n",
       "      <td>58</td>\n",
       "      <td>-4</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "      <td>175</td>\n",
       "      <td>45</td>\n",
       "      <td>7</td>\n",
       "      <td>94</td>\n",
       "      <td>153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>I Don't Care (with Justin Bieber)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>102</td>\n",
       "      <td>68</td>\n",
       "      <td>80</td>\n",
       "      <td>-5</td>\n",
       "      <td>9</td>\n",
       "      <td>84</td>\n",
       "      <td>220</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>84</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                         Track.Name    Artist.Name      Genre  \\\n",
       "2           3      boyfriend (with Social House)  Ariana Grande  dance pop   \n",
       "3           4    Beautiful People (feat. Khalid)     Ed Sheeran        pop   \n",
       "4           5        Goodbyes (Feat. Young Thug)    Post Malone    dfw rap   \n",
       "5           6  I Don't Care (with Justin Bieber)     Ed Sheeran        pop   \n",
       "\n",
       "   Beats.Per.Minute  Energy  Danceability  Loudness..dB..  Liveness  Valence.  \\\n",
       "2               190      80            40              -4        16        70   \n",
       "3                93      65            64              -8         8        55   \n",
       "4               150      65            58              -4        11        18   \n",
       "5               102      68            80              -5         9        84   \n",
       "\n",
       "   Length.  Acousticness..  Speechiness.  Popularity  \\\n",
       "2      186              12            46          85   \n",
       "3      198              12            19          86   \n",
       "4      175              45             7          94   \n",
       "5      220               9             4          84   \n",
       "\n",
       "   Beats.Per.Minute.But.A.Bit.Faster  \n",
       "2                                193  \n",
       "3                                 96  \n",
       "4                                153  \n",
       "5                                105  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rows with label 2 to 5\n",
    "df.loc[2:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Track.Name</th>\n",
       "      <th>Artist.Name</th>\n",
       "      <th>Genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Señorita</td>\n",
       "      <td>Shawn Mendes</td>\n",
       "      <td>canadian pop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>China</td>\n",
       "      <td>Anuel AA</td>\n",
       "      <td>reggaeton flow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>boyfriend (with Social House)</td>\n",
       "      <td>Ariana Grande</td>\n",
       "      <td>dance pop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Beautiful People (feat. Khalid)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Goodbyes (Feat. Young Thug)</td>\n",
       "      <td>Post Malone</td>\n",
       "      <td>dfw rap</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Track.Name    Artist.Name           Genre\n",
       "0                         Señorita   Shawn Mendes    canadian pop\n",
       "1                            China       Anuel AA  reggaeton flow\n",
       "2    boyfriend (with Social House)  Ariana Grande       dance pop\n",
       "3  Beautiful People (feat. Khalid)     Ed Sheeran             pop\n",
       "4      Goodbyes (Feat. Young Thug)    Post Malone         dfw rap"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# columns with labels between Track.Name and Genre\n",
    "df.loc[:, 'Track.Name':'Genre'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how the column slicing uses the _order_ of the column names to determine what's between `Track.Name` and `Genre`.\n",
    "\n",
    "To select several rows/columns, we can also directly use label lists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Energy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>dfw rap</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>pop</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>r&amp;b en espanol</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Genre  Energy\n",
       "4          dfw rap      65\n",
       "12             pop      41\n",
       "44  r&b en espanol      69"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rows 4, 12, 44 and columns Genre & Energy\n",
    "df.loc[[4, 12, 44], ['Genre', 'Energy']].head()\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we want to index data? With `.loc[]`, we just have to assign data of correct shape to the selection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "⏮ Before: \n",
      "Track.Name     boyfriend (with Social House)\n",
      "Artist.Name                    Ariana Grande\n",
      "Name: 2, dtype: object\n",
      "\n",
      "⏭ After: \n",
      "Track.Name     girlfriend (with Antisocial Flat)\n",
      "Artist.Name                         Ariana Small\n",
      "Name: 2, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print('⏮ Before: ')\n",
    "print(df.loc[2, ['Track.Name', 'Artist.Name']])\n",
    "df.loc[2, ['Track.Name', 'Artist.Name']] = ['girlfriend (with Antisocial Flat)', 'Ariana Small']\n",
    "print('\\n⏭ After: ')\n",
    "print(df.loc[2, ['Track.Name', 'Artist.Name']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "💪 A mistake seems to have slipped in our dataset... Using the method of your choice, write a function which sets the `Energy` of `ROSALÍA`'s big tune `Con Altura` to 9999. The method should return the `DataFrame` after having modified it. The unit test should pass!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success! 🎉\n",
      "    Unnamed: 0  Track.Name Artist.Name           Genre  Beats.Per.Minute  \\\n",
      "44          45  Con Altura     ROSALÍA  r&b en espanol                98   \n",
      "\n",
      "    Energy  Danceability  Loudness..dB..  Liveness  Valence.  Length.  \\\n",
      "44      69            88              -4         5        75      162   \n",
      "\n",
      "    Acousticness..  Speechiness.  Popularity  \n",
      "44              39            12          88  \n"
     ]
    }
   ],
   "source": [
    "def fix_energy(df):\n",
    "    df.loc[44, 'Energy'] = 9999\n",
    "\n",
    "    return df\n",
    "    \n",
    "def test_energy():\n",
    "    fixed_df = fix_energy(df.copy())\n",
    "    assert fixed_df.loc[44, 'Energy'] == 9999\n",
    "    print('Success! 🎉')\n",
    "    \n",
    "test_energy()\n",
    "\n",
    "print (df[df['Track.Name'] == 'Con Altura']) #Just for checking the row index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3 `.iloc[]`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`.iloc[]` works just like `.loc[]`, except with _indices_ instead of _labels_. i.e It accepts the row and column positions as opposed to their names. Careful, it can get confusing when your index labels are numbers! It can be used with one or two arguments:\n",
    "\n",
    "    df.iloc[row_index]\n",
    "    df.iloc[row_index, column_index]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0                                        4\n",
       "Track.Name          Beautiful People (feat. Khalid)\n",
       "Artist.Name                              Ed Sheeran\n",
       "Genre                                           pop\n",
       "Beats.Per.Minute                                 93\n",
       "Energy                                           65\n",
       "Danceability                                     64\n",
       "Loudness..dB..                                   -8\n",
       "Liveness                                          8\n",
       "Valence.                                         55\n",
       "Length.                                         198\n",
       "Acousticness..                                   12\n",
       "Speechiness.                                     19\n",
       "Popularity                                       86\n",
       "Name: 3, dtype: object"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# row at position 3\n",
    "df.iloc[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Señorita</td>\n",
       "      <td>Shawn Mendes</td>\n",
       "      <td>canadian pop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Beautiful People (feat. Khalid)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Ransom</td>\n",
       "      <td>Lil Tecca</td>\n",
       "      <td>trap music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>bad guy</td>\n",
       "      <td>Billie Eilish</td>\n",
       "      <td>electropop</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Track.Name    Artist.Name         Genre\n",
       "0                         Señorita   Shawn Mendes  canadian pop\n",
       "3  Beautiful People (feat. Khalid)     Ed Sheeran           pop\n",
       "6                           Ransom      Lil Tecca    trap music\n",
       "9                          bad guy  Billie Eilish    electropop"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# every 3rd row between the first and 11th, and every column between the second and fith\n",
    "df.iloc[0:10:3, 1:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To index values with `.iloc[]`, we also just have to assign data of correct shape to the selection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0                     10\n",
       "Track.Name               good gal\n",
       "Artist.Name         Billie Eilish\n",
       "Genre                  electropop\n",
       "Beats.Per.Minute              135\n",
       "Energy                         43\n",
       "Danceability                   70\n",
       "Loudness..dB..                -11\n",
       "Liveness                       10\n",
       "Valence.                       56\n",
       "Length.                       194\n",
       "Acousticness..                 33\n",
       "Speechiness.                   38\n",
       "Popularity                     95\n",
       "Name: 9, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[9, 1] = 'good gal'\n",
    "df.iloc[9]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.4 Boolean masks\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's imagine that we are music snobs, and don't agree with the genre allocation of the songs. Everyone knows that pop with a bpm < 100 is actually called _slow pop_ 💁‍♂️. We _need_ to fix this travesty! \n",
    "\n",
    "Since there's 50 rows of data, we don't particularly want to go through the data manually. If only there was a way to do this easily with pandas..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Unnamed: 0</th>\n",
       "      <th>Track.Name</th>\n",
       "      <th>Artist.Name</th>\n",
       "      <th>Genre</th>\n",
       "      <th>Beats.Per.Minute</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Beautiful People (feat. Khalid)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>slow pop</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>I Don't Care (with Justin Bieber)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>How Do You Sleep?</td>\n",
       "      <td>Sam Smith</td>\n",
       "      <td>pop</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Someone You Loved</td>\n",
       "      <td>Lewis Capaldi</td>\n",
       "      <td>pop</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>Antisocial (with Travis Scott)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>Talk</td>\n",
       "      <td>Khalid</td>\n",
       "      <td>pop</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>50</td>\n",
       "      <td>Cross Me (feat. Chance the Rapper &amp; PnB Rock)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>slow pop</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0                                     Track.Name    Artist.Name  \\\n",
       "3            4                Beautiful People (feat. Khalid)     Ed Sheeran   \n",
       "5            6              I Don't Care (with Justin Bieber)     Ed Sheeran   \n",
       "7            8                              How Do You Sleep?      Sam Smith   \n",
       "12          13                              Someone You Loved  Lewis Capaldi   \n",
       "37          38                 Antisocial (with Travis Scott)     Ed Sheeran   \n",
       "43          44                                           Talk         Khalid   \n",
       "49          50  Cross Me (feat. Chance the Rapper & PnB Rock)     Ed Sheeran   \n",
       "\n",
       "       Genre  Beats.Per.Minute  \n",
       "3   slow pop                93  \n",
       "5        pop               102  \n",
       "7        pop               111  \n",
       "12       pop               110  \n",
       "37       pop               152  \n",
       "43       pop               136  \n",
       "49  slow pop                95  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# update slow pop\n",
    "df.loc[(df['Genre'] == 'pop') & (df['Beats.Per.Minute'] < 100), 'Genre'] = 'slow pop'\n",
    "# show rows\n",
    "df.loc[[3, 5, 7, 12, 37, 43, 49], :'Beats.Per.Minute']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Wow, all it took was one line! It's a complicated expression though, so let's break it down a bit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2    False\n",
       "3    False\n",
       "4    False\n",
       "5     True\n",
       "6    False\n",
       "7     True\n",
       "8    False\n",
       "9    False\n",
       "Name: Genre, dtype: bool"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask = (df['Genre'] == 'pop')\n",
    "mask.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is an example of an overloaded operator. The `==` here is applied _element wise_ to the `Series` returned by the `[]` selection. Therefore the one line expression is equivalent to the following:\n",
    "\n",
    "    column_series = df['Genre']\n",
    "    mask_series = (column == 'pop')\n",
    "    \n",
    "The result is a _mask_, i.e a list/array of booleans which indicates which elements of a matching list/array we wish to select. Here the mask is `True` only if the `Genre` is `pop`. What makes masks useful is that they share indices with their matching data list/array. So we can now use this mask to filter `pop` rows in `df`!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Unnamed: 0</th>\n",
       "      <th>Track.Name</th>\n",
       "      <th>Artist.Name</th>\n",
       "      <th>Genre</th>\n",
       "      <th>Beats.Per.Minute</th>\n",
       "      <th>Energy</th>\n",
       "      <th>Danceability</th>\n",
       "      <th>Loudness..dB..</th>\n",
       "      <th>Liveness</th>\n",
       "      <th>Valence.</th>\n",
       "      <th>Length.</th>\n",
       "      <th>Acousticness..</th>\n",
       "      <th>Speechiness.</th>\n",
       "      <th>Popularity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>I Don't Care (with Justin Bieber)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>102</td>\n",
       "      <td>68</td>\n",
       "      <td>80</td>\n",
       "      <td>-5</td>\n",
       "      <td>9</td>\n",
       "      <td>84</td>\n",
       "      <td>220</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>How Do You Sleep?</td>\n",
       "      <td>Sam Smith</td>\n",
       "      <td>pop</td>\n",
       "      <td>111</td>\n",
       "      <td>68</td>\n",
       "      <td>48</td>\n",
       "      <td>-5</td>\n",
       "      <td>8</td>\n",
       "      <td>35</td>\n",
       "      <td>202</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Someone You Loved</td>\n",
       "      <td>Lewis Capaldi</td>\n",
       "      <td>pop</td>\n",
       "      <td>110</td>\n",
       "      <td>41</td>\n",
       "      <td>50</td>\n",
       "      <td>-6</td>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>182</td>\n",
       "      <td>75</td>\n",
       "      <td>3</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>Antisocial (with Travis Scott)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>152</td>\n",
       "      <td>82</td>\n",
       "      <td>72</td>\n",
       "      <td>-5</td>\n",
       "      <td>36</td>\n",
       "      <td>91</td>\n",
       "      <td>162</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>Talk</td>\n",
       "      <td>Khalid</td>\n",
       "      <td>pop</td>\n",
       "      <td>136</td>\n",
       "      <td>40</td>\n",
       "      <td>90</td>\n",
       "      <td>-9</td>\n",
       "      <td>6</td>\n",
       "      <td>35</td>\n",
       "      <td>198</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0                         Track.Name    Artist.Name Genre  \\\n",
       "5            6  I Don't Care (with Justin Bieber)     Ed Sheeran   pop   \n",
       "7            8                  How Do You Sleep?      Sam Smith   pop   \n",
       "12          13                  Someone You Loved  Lewis Capaldi   pop   \n",
       "37          38     Antisocial (with Travis Scott)     Ed Sheeran   pop   \n",
       "43          44                               Talk         Khalid   pop   \n",
       "\n",
       "    Beats.Per.Minute  Energy  Danceability  Loudness..dB..  Liveness  \\\n",
       "5                102      68            80              -5         9   \n",
       "7                111      68            48              -5         8   \n",
       "12               110      41            50              -6        11   \n",
       "37               152      82            72              -5        36   \n",
       "43               136      40            90              -9         6   \n",
       "\n",
       "    Valence.  Length.  Acousticness..  Speechiness.  Popularity  \n",
       "5         84      220               9             4          84  \n",
       "7         35      202              15             9          90  \n",
       "12        45      182              75             3          88  \n",
       "37        91      162              13             5          87  \n",
       "43        35      198               5            13          84  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Genre'] == 'pop']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we used a mask as argument to the `[]` operator, which returned all the rows where the mask was `True`. This creates an intuitive, terse, and powerful notation. Mastering boolean masks with pandas can greatly simplify your code!\n",
    "\n",
    "Let's take another look at the original one liner:\n",
    "\n",
    "    df.loc[(df['Genre'] == 'pop') & (df['Beats.Per.Minute'] < 100), 'Genre'] = 'slow pop'\n",
    "\n",
    "We can notice the use of the `&` symbol. This is because boolean logic operators are themselves overloaded. This means that we can chain masks together. Say we wanted to view rows that qualify as `pop` _or_ were performed by `Katy Perry`: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Unnamed: 0</th>\n",
       "      <th>Track.Name</th>\n",
       "      <th>Artist.Name</th>\n",
       "      <th>Genre</th>\n",
       "      <th>Beats.Per.Minute</th>\n",
       "      <th>Energy</th>\n",
       "      <th>Danceability</th>\n",
       "      <th>Loudness..dB..</th>\n",
       "      <th>Liveness</th>\n",
       "      <th>Valence.</th>\n",
       "      <th>Length.</th>\n",
       "      <th>Acousticness..</th>\n",
       "      <th>Speechiness.</th>\n",
       "      <th>Popularity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>I Don't Care (with Justin Bieber)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>102</td>\n",
       "      <td>68</td>\n",
       "      <td>80</td>\n",
       "      <td>-5</td>\n",
       "      <td>9</td>\n",
       "      <td>84</td>\n",
       "      <td>220</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>How Do You Sleep?</td>\n",
       "      <td>Sam Smith</td>\n",
       "      <td>pop</td>\n",
       "      <td>111</td>\n",
       "      <td>68</td>\n",
       "      <td>48</td>\n",
       "      <td>-5</td>\n",
       "      <td>8</td>\n",
       "      <td>35</td>\n",
       "      <td>202</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Someone You Loved</td>\n",
       "      <td>Lewis Capaldi</td>\n",
       "      <td>pop</td>\n",
       "      <td>110</td>\n",
       "      <td>41</td>\n",
       "      <td>50</td>\n",
       "      <td>-6</td>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>182</td>\n",
       "      <td>75</td>\n",
       "      <td>3</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>Never Really Over</td>\n",
       "      <td>Katy Perry</td>\n",
       "      <td>dance pop</td>\n",
       "      <td>100</td>\n",
       "      <td>88</td>\n",
       "      <td>77</td>\n",
       "      <td>-5</td>\n",
       "      <td>32</td>\n",
       "      <td>39</td>\n",
       "      <td>224</td>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>Antisocial (with Travis Scott)</td>\n",
       "      <td>Ed Sheeran</td>\n",
       "      <td>pop</td>\n",
       "      <td>152</td>\n",
       "      <td>82</td>\n",
       "      <td>72</td>\n",
       "      <td>-5</td>\n",
       "      <td>36</td>\n",
       "      <td>91</td>\n",
       "      <td>162</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>Talk</td>\n",
       "      <td>Khalid</td>\n",
       "      <td>pop</td>\n",
       "      <td>136</td>\n",
       "      <td>40</td>\n",
       "      <td>90</td>\n",
       "      <td>-9</td>\n",
       "      <td>6</td>\n",
       "      <td>35</td>\n",
       "      <td>198</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0                         Track.Name    Artist.Name      Genre  \\\n",
       "5            6  I Don't Care (with Justin Bieber)     Ed Sheeran        pop   \n",
       "7            8                  How Do You Sleep?      Sam Smith        pop   \n",
       "12          13                  Someone You Loved  Lewis Capaldi        pop   \n",
       "34          35                  Never Really Over     Katy Perry  dance pop   \n",
       "37          38     Antisocial (with Travis Scott)     Ed Sheeran        pop   \n",
       "43          44                               Talk         Khalid        pop   \n",
       "\n",
       "    Beats.Per.Minute  Energy  Danceability  Loudness..dB..  Liveness  \\\n",
       "5                102      68            80              -5         9   \n",
       "7                111      68            48              -5         8   \n",
       "12               110      41            50              -6        11   \n",
       "34               100      88            77              -5        32   \n",
       "37               152      82            72              -5        36   \n",
       "43               136      40            90              -9         6   \n",
       "\n",
       "    Valence.  Length.  Acousticness..  Speechiness.  Popularity  \n",
       "5         84      220               9             4          84  \n",
       "7         35      202              15             9          90  \n",
       "12        45      182              75             3          88  \n",
       "34        39      224              19             6          89  \n",
       "37        91      162              13             5          87  \n",
       "43        35      198               5            13          84  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df['Genre'] == 'pop') | (df['Artist.Name'] == 'Katy Perry')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we used two masks, chained together with an OR operator (`|`), and used as argument to the `[]` selection operator. The above line is equivalent to the following code:\n",
    "\n",
    "    mask1 = (df['Genre'] == 'pop')\n",
    "    mask2 = (df['Artist.Name'] == 'Katy Perry')\n",
    "    mask1_or_mask2 = mask1 | mask2\n",
    "    pop_or_katy_perry_df = df[mask1_or_mask2]\n",
    "    \n",
    "We can basically select anything! However, you might recall that in the complex one liner, we not only _selected_ data using chained boolean masks, but we also _replaced_ some of the selected values:\n",
    "\n",
    "    df.loc[(df['Genre'] == 'pop') & (df['Beats.Per.Minute'] < 100), 'Genre'] = 'slow pop'\n",
    "    \n",
    "We've seen that updating specific rows is a bad idea using the `[]` notation, so we'll be using `.loc[]` instead here. The trick is that these masks can be fed as first argument of the `.loc[]` method, whilst the second argument can be used to select some columns. In our case, we'd like to select the `Genre` column of the filtered rows, since that's the column we wish to update to `slow pop`. So the complex one liner is equivalent to the following code:\n",
    "\n",
    "    pop_mask = (df['Genre'] == 'pop')\n",
    "    slow_bpm_mask = (df['Beats.Per.Minute'] < 100)\n",
    "    pop_and_slow_bpm_mask = pop_mask & slow_bpm_mask\n",
    "    cells_to_update = df.loc[pop_and_slow_bpm_mask, 'Genre']\n",
    "    cells_to_update = 'slow pop'\n",
    "    \n",
    "🧠 There's a lot happening here, so take the time to understand how the code block above corresponds to the long one liner. Don't be worried if boolean masks feel like magic at first, they take a while to get used to! 🧙‍♀️\n",
    "    \n",
    "💪💪 Using a single line expression with boolean masks, update the `Genre` of the cells with `Loudness..dB..` >= -2.0 _OR_ `Liveness` > 40.0 as `annoying`. Your function below should return the modified `DataFrame`, and the unit test should pass!\n",
    "  \n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success! 🎉\n"
     ]
    }
   ],
   "source": [
    "def replace_annoying(df):\n",
    "    # INSERT YOUR CODE HERE\n",
    "    df.loc[(df['Loudness..dB..'] >= -2.0)|(df['Liveness']>40.0),'Genre'] = 'annoying'\n",
    "            \n",
    "    return df\n",
    "\n",
    "def test_annoying():\n",
    "    new_df = replace_annoying(df.copy())\n",
    "    n_replaced = new_df.loc[new_df['Genre'] == 'annoying', :].shape[0]\n",
    "    assert n_replaced == 4\n",
    "    print('Success! 🎉')\n",
    "    \n",
    "test_annoying()\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Cleaning missing data\n",
    "\n",
    "Missing values are common when exploring tabular or time series data. e.g missing records or temporary sensor failures. These can become a problem if used to train or test machine learning models. Knowing how to remove or fill in these missing values is therefore an essential skill for data scientists. Luckily, pandas offers many useful methods to make this process easier.\n",
    "\n",
    "Let's take our spotify `DataFrame` and add a few random `None` values to imitate a faulty dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_missing_values(df, n):\n",
    "    n_rows, n_columns = df.shape\n",
    "    np.random.seed(1337)\n",
    "    row_indices = np.random.randint(0, n_rows, n)\n",
    "    column_indices = np.random.randint(0, n_columns, n)\n",
    "    index_locations = zip(row_indices, column_indices)\n",
    "    for iloc in index_locations:\n",
    "        df.iloc[iloc] = None\n",
    "    return df\n",
    "        \n",
    "df = pd.read_csv('top50.csv', encoding = \"latin\")\n",
    "dirty_df = add_missing_values(df, 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've added 10 missing values to a $50\\times14$ matrix ... It's not going to be simple to spot them. We could try to make a boolean masks by checking if the values are null, but pandas makes it easy by supplying the mask directly with the `DataFrame.isna()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dirty_df.isna().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can therefore use it to find the rows with a little help from the `.any()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show rows with missing values\n",
    "dirty_df.loc[df.isna().any(axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "🧠 Can you explain how the previous cell used boolean masks to return the rows with missing elements?\n",
    "\n",
    "ℹ️ Here, missing values are represented as `NaN`. pandas actually supports several missing data types depending on the `dtype` of the `DataFrame`, and tries to take care of conversions in the background. Being aware of the differences can help when debugging, more details can be found in the [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html#integer-dtypes-and-missing-data).\n",
    "\n",
    "Now we see the extent of the damage to our data 😰. The sight of this corrupted dataset is unbearable and therefore we would like to clean the `DataFrame`. Since there is no way for us to guess what the values were, the best we can do to make downstream analysis smoother is to remove all the rows with `NaN` values.\n",
    "\n",
    "We've learned how to set values using boolean masks, but again, pandas makes things easy for us:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the rows with missing values\n",
    "clean_df = df.dropna()\n",
    "# show rows with missing values\n",
    "clean_df.loc[clean_df.isna().any(axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.dropna()` method has magically removed all the dirty rows! More details about this api can be found [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html#dropping-axis-labels-with-missing-data-dropna).\n",
    "\n",
    "Sometimes, we don't want to remove missing data, but replace it with a default value instead. Let's take our `dirty_df` and replace all the `NaN` values in the `Danceability` column with a default value of 80:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fill_df = dirty_df.copy()\n",
    "fill_df['Danceability'] = fill_df['Danceability'].fillna(80.0)\n",
    "fill_df.iloc[[8, 28, 40], [1, 2, 6]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Groovy 🕺. The `.fillna()` method makes it easy to replace `NaN`s. pandas has versatile tools for missing data, e.g: interpolation of values, or replacing anything with anything really. More details can be found [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Combining data\n",
    "\n",
    "Whether we have loaded datasets separately, or we want to aggregate different selections, we'll often have to combine `DataFrame` objects. There are three main methods available:\n",
    "\n",
    "### 3.1 `df.append()`\n",
    "\n",
    "Following the philosophy of keeping notation as close to python as possible, pandas `DataFrame` offers the same `.append()` method as lists.\n",
    "\n",
    "Here, we select two row slices with `.iloc[]`, which we then combine together using `df.append()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('top50.csv', encoding = \"latin\")\n",
    "df1 = df.iloc[:3, :3]\n",
    "df2 = df.iloc[5:8, :3]\n",
    "df3 = df1.append(df2)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 `pd.concat()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`.concat()` offers more control and flexibility than `.append()`, mainly around column and index conflicts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.concat([df1, df2])\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3 `pd.merge()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas also offers an api for database-style joins: `pd.merge()`. This function is complex and requires relational database basics, since it mirrors the SQL syntax used in join queries. We won't cover this api in this course, but keep in mind that it is useful because of its fast performance and high level of control. Detailed documentation can be found [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html#database-style-dataframe-or-named-series-joining-merging)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Summary\n",
    "\n",
    "Today, we learned more about **pandas**. We're now pros at **indexing** and **selecting** from `DataFrame`s, with the **`.loc[]`** and **`.iloc[]`** methods as well as **boolean masks**. We also know how to clean **missing values**. Finally, we saw how to combine `DataFrame`s.\n",
    "\n",
    "\n",
    "# Resources\n",
    "\n",
    "## Core Resources\n",
    "\n",
    "The following four links are from the excellent official pandas documentation:\n",
    "- [I/O tools](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html)\n",
    "- [Indexing and selecting data](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html)\n",
    "- [Merge, join, and concatenate](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html)\n",
    "- [Working with missing data](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html)\n",
    "\n",
    "## Additional Resources\n",
    "\n",
    "- [10 minutes to pandas](https://pandas.pydata.org/docs/getting_started/10min.html)  \n",
    "Official introduction to the pandas library\n",
    "- [Fast pandas](https://tomaugspurger.github.io/modern-4-performance)\n"
   ]
  }
 ],
 "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}