Tidy Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tidy Data in Python\n",
    "by [Jean-Nicholas Hould](http://www.jeannicholashould.com/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "from os import listdir\n",
    "from os.path import isfile, join\n",
    "import glob\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Column headers are values, not variable names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pew Research Center Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>religion</th>\n",
       "      <th>&lt;$10k</th>\n",
       "      <th>$10-20k</th>\n",
       "      <th>$20-30k</th>\n",
       "      <th>$30-40k</th>\n",
       "      <th>$40-50k</th>\n",
       "      <th>$50-75k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>27</td>\n",
       "      <td>34</td>\n",
       "      <td>60</td>\n",
       "      <td>81</td>\n",
       "      <td>76</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>12</td>\n",
       "      <td>27</td>\n",
       "      <td>37</td>\n",
       "      <td>52</td>\n",
       "      <td>35</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buddhist</td>\n",
       "      <td>27</td>\n",
       "      <td>21</td>\n",
       "      <td>30</td>\n",
       "      <td>34</td>\n",
       "      <td>33</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Catholic</td>\n",
       "      <td>418</td>\n",
       "      <td>617</td>\n",
       "      <td>732</td>\n",
       "      <td>670</td>\n",
       "      <td>638</td>\n",
       "      <td>1116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Dont know/refused</td>\n",
       "      <td>15</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Evangelical Prot</td>\n",
       "      <td>575</td>\n",
       "      <td>869</td>\n",
       "      <td>1064</td>\n",
       "      <td>982</td>\n",
       "      <td>881</td>\n",
       "      <td>1486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Historically Black Prot</td>\n",
       "      <td>228</td>\n",
       "      <td>244</td>\n",
       "      <td>236</td>\n",
       "      <td>238</td>\n",
       "      <td>197</td>\n",
       "      <td>223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Jehovahs Witness</td>\n",
       "      <td>20</td>\n",
       "      <td>27</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>21</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Jewish</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>30</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   religion   <$10k   $10-20k  $20-30k  $30-40k   $40-50k  \\\n",
       "0                  Agnostic      27        34       60       81        76   \n",
       "1                   Atheist      12        27       37       52        35   \n",
       "2                  Buddhist      27        21       30       34        33   \n",
       "3                  Catholic     418       617      732      670       638   \n",
       "4         Dont know/refused      15        14       15       11        10   \n",
       "5         Evangelical Prot      575       869     1064      982       881   \n",
       "6                    Hindu        1         9        7        9        11   \n",
       "7  Historically Black Prot      228       244      236      238       197   \n",
       "8         Jehovahs Witness       20        27       24       24        21   \n",
       "9                   Jewish       19        19       25       25        30   \n",
       "\n",
       "   $50-75k  \n",
       "0      137  \n",
       "1       70  \n",
       "2       58  \n",
       "3     1116  \n",
       "4       35  \n",
       "5     1486  \n",
       "6       34  \n",
       "7      223  \n",
       "8       30  \n",
       "9       95  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"./data/pew-raw.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>religion</th>\n",
       "      <th>income</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$30-40k</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$40-50k</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$50-75k</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$20-30k</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>$40-50k</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>$20-30k</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>$30-40k</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    religion    income  freq\n",
       "0   Agnostic     <$10k    27\n",
       "30  Agnostic   $30-40k    81\n",
       "40  Agnostic   $40-50k    76\n",
       "50  Agnostic   $50-75k   137\n",
       "10  Agnostic   $10-20k    34\n",
       "20  Agnostic   $20-30k    60\n",
       "41   Atheist   $40-50k    35\n",
       "21   Atheist   $20-30k    37\n",
       "11   Atheist   $10-20k    27\n",
       "31   Atheist   $30-40k    52"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "formatted_df = pd.melt(df,[\"religion\"], var_name=\"income\", value_name=\"freq\")\n",
    "formatted_df = formatted_df.sort_values(by=[\"religion\"])\n",
    "formatted_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Billboard Top 100 Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>artist.inverted</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "      <th>x1st.week</th>\n",
       "      <th>x2nd.week</th>\n",
       "      <th>x3rd.week</th>\n",
       "      <th>...</th>\n",
       "      <th>x67th.week</th>\n",
       "      <th>x68th.week</th>\n",
       "      <th>x69th.week</th>\n",
       "      <th>x70th.week</th>\n",
       "      <th>x71st.week</th>\n",
       "      <th>x72nd.week</th>\n",
       "      <th>x73rd.week</th>\n",
       "      <th>x74th.week</th>\n",
       "      <th>x75th.week</th>\n",
       "      <th>x76th.week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>Destiny's Child</td>\n",
       "      <td>Independent Women Part I</td>\n",
       "      <td>3:38</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-09-23</td>\n",
       "      <td>2000-11-18</td>\n",
       "      <td>78</td>\n",
       "      <td>63.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000</td>\n",
       "      <td>Santana</td>\n",
       "      <td>Maria, Maria</td>\n",
       "      <td>4:18</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-02-12</td>\n",
       "      <td>2000-04-08</td>\n",
       "      <td>15</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000</td>\n",
       "      <td>Savage Garden</td>\n",
       "      <td>I Knew I Loved You</td>\n",
       "      <td>4:07</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-10-23</td>\n",
       "      <td>2000-01-29</td>\n",
       "      <td>71</td>\n",
       "      <td>48.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000</td>\n",
       "      <td>Madonna</td>\n",
       "      <td>Music</td>\n",
       "      <td>3:45</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-08-12</td>\n",
       "      <td>2000-09-16</td>\n",
       "      <td>41</td>\n",
       "      <td>23.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000</td>\n",
       "      <td>Aguilera, Christina</td>\n",
       "      <td>Come On Over Baby (All I Want Is You)</td>\n",
       "      <td>3:38</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-08-05</td>\n",
       "      <td>2000-10-14</td>\n",
       "      <td>57</td>\n",
       "      <td>47.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2000</td>\n",
       "      <td>Janet</td>\n",
       "      <td>Doesn't Really Matter</td>\n",
       "      <td>4:17</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-06-17</td>\n",
       "      <td>2000-08-26</td>\n",
       "      <td>59</td>\n",
       "      <td>52.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2000</td>\n",
       "      <td>Destiny's Child</td>\n",
       "      <td>Say My Name</td>\n",
       "      <td>4:31</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-12-25</td>\n",
       "      <td>2000-03-18</td>\n",
       "      <td>83</td>\n",
       "      <td>83.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2000</td>\n",
       "      <td>Iglesias, Enrique</td>\n",
       "      <td>Be With You</td>\n",
       "      <td>3:36</td>\n",
       "      <td>Latin</td>\n",
       "      <td>2000-04-01</td>\n",
       "      <td>2000-06-24</td>\n",
       "      <td>63</td>\n",
       "      <td>45.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2000</td>\n",
       "      <td>Sisqo</td>\n",
       "      <td>Incomplete</td>\n",
       "      <td>3:52</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-06-24</td>\n",
       "      <td>2000-08-12</td>\n",
       "      <td>77</td>\n",
       "      <td>66.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2000</td>\n",
       "      <td>Lonestar</td>\n",
       "      <td>Amazed</td>\n",
       "      <td>4:25</td>\n",
       "      <td>Country</td>\n",
       "      <td>1999-06-05</td>\n",
       "      <td>2000-03-04</td>\n",
       "      <td>81</td>\n",
       "      <td>54.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 83 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   year      artist.inverted                                  track  time  \\\n",
       "0  2000      Destiny's Child               Independent Women Part I  3:38   \n",
       "1  2000              Santana                           Maria, Maria  4:18   \n",
       "2  2000        Savage Garden                     I Knew I Loved You  4:07   \n",
       "3  2000              Madonna                                  Music  3:45   \n",
       "4  2000  Aguilera, Christina  Come On Over Baby (All I Want Is You)  3:38   \n",
       "5  2000                Janet                  Doesn't Really Matter  4:17   \n",
       "6  2000      Destiny's Child                            Say My Name  4:31   \n",
       "7  2000    Iglesias, Enrique                            Be With You  3:36   \n",
       "8  2000                Sisqo                             Incomplete  3:52   \n",
       "9  2000             Lonestar                                 Amazed  4:25   \n",
       "\n",
       "     genre date.entered date.peaked  x1st.week  x2nd.week  x3rd.week  \\\n",
       "0     Rock   2000-09-23  2000-11-18         78       63.0       49.0   \n",
       "1     Rock   2000-02-12  2000-04-08         15        8.0        6.0   \n",
       "2     Rock   1999-10-23  2000-01-29         71       48.0       43.0   \n",
       "3     Rock   2000-08-12  2000-09-16         41       23.0       18.0   \n",
       "4     Rock   2000-08-05  2000-10-14         57       47.0       45.0   \n",
       "5     Rock   2000-06-17  2000-08-26         59       52.0       43.0   \n",
       "6     Rock   1999-12-25  2000-03-18         83       83.0       44.0   \n",
       "7    Latin   2000-04-01  2000-06-24         63       45.0       34.0   \n",
       "8     Rock   2000-06-24  2000-08-12         77       66.0       61.0   \n",
       "9  Country   1999-06-05  2000-03-04         81       54.0       44.0   \n",
       "\n",
       "      ...      x67th.week  x68th.week  x69th.week  x70th.week  x71st.week  \\\n",
       "0     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "1     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "2     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "3     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "4     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "5     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "6     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "7     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "8     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "9     ...             NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "   x72nd.week  x73rd.week  x74th.week  x75th.week  x76th.week  \n",
       "0         NaN         NaN         NaN         NaN         NaN  \n",
       "1         NaN         NaN         NaN         NaN         NaN  \n",
       "2         NaN         NaN         NaN         NaN         NaN  \n",
       "3         NaN         NaN         NaN         NaN         NaN  \n",
       "4         NaN         NaN         NaN         NaN         NaN  \n",
       "5         NaN         NaN         NaN         NaN         NaN  \n",
       "6         NaN         NaN         NaN         NaN         NaN  \n",
       "7         NaN         NaN         NaN         NaN         NaN  \n",
       "8         NaN         NaN         NaN         NaN         NaN  \n",
       "9         NaN         NaN         NaN         NaN         NaN  \n",
       "\n",
       "[10 rows x 83 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"./data/billboard.csv\", encoding=\"mac_latin2\")\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>artist.inverted</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>week</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>2000</td>\n",
       "      <td>2 Pac</td>\n",
       "      <td>Baby Don't Cry (Keep Ya Head Up II)</td>\n",
       "      <td>4:22</td>\n",
       "      <td>Rap</td>\n",
       "      <td>1</td>\n",
       "      <td>87</td>\n",
       "      <td>2000-02-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>2000</td>\n",
       "      <td>2 Pac</td>\n",
       "      <td>Baby Don't Cry (Keep Ya Head Up II)</td>\n",
       "      <td>4:22</td>\n",
       "      <td>Rap</td>\n",
       "      <td>2</td>\n",
       "      <td>82</td>\n",
       "      <td>2000-03-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>880</th>\n",
       "      <td>2000</td>\n",
       "      <td>2 Pac</td>\n",
       "      <td>Baby Don't Cry (Keep Ya Head Up II)</td>\n",
       "      <td>4:22</td>\n",
       "      <td>Rap</td>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>2000-03-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1197</th>\n",
       "      <td>2000</td>\n",
       "      <td>2 Pac</td>\n",
       "      <td>Baby Don't Cry (Keep Ya Head Up II)</td>\n",
       "      <td>4:22</td>\n",
       "      <td>Rap</td>\n",
       "      <td>4</td>\n",
       "      <td>77</td>\n",
       "      <td>2000-03-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1514</th>\n",
       "      <td>2000</td>\n",
       "      <td>2 Pac</td>\n",
       "      <td>Baby Don't Cry (Keep Ya Head Up II)</td>\n",
       "      <td>4:22</td>\n",
       "      <td>Rap</td>\n",
       "      <td>5</td>\n",
       "      <td>87</td>\n",
       "      <td>2000-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1831</th>\n",
       "      <td>2000</td>\n",
       "      <td>2 Pac</td>\n",
       "      <td>Baby Don't Cry (Keep Ya Head Up II)</td>\n",
       "      <td>4:22</td>\n",
       "      <td>Rap</td>\n",
       "      <td>6</td>\n",
       "      <td>94</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2148</th>\n",
       "      <td>2000</td>\n",
       "      <td>2 Pac</td>\n",
       "      <td>Baby Don't Cry (Keep Ya Head Up II)</td>\n",
       "      <td>4:22</td>\n",
       "      <td>Rap</td>\n",
       "      <td>7</td>\n",
       "      <td>99</td>\n",
       "      <td>2000-04-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>2000</td>\n",
       "      <td>2Ge+her</td>\n",
       "      <td>The Hardest Part Of Breaking Up (Is Getting Ba...</td>\n",
       "      <td>3:15</td>\n",
       "      <td>R&amp;B</td>\n",
       "      <td>1</td>\n",
       "      <td>91</td>\n",
       "      <td>2000-09-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>604</th>\n",
       "      <td>2000</td>\n",
       "      <td>2Ge+her</td>\n",
       "      <td>The Hardest Part Of Breaking Up (Is Getting Ba...</td>\n",
       "      <td>3:15</td>\n",
       "      <td>R&amp;B</td>\n",
       "      <td>2</td>\n",
       "      <td>87</td>\n",
       "      <td>2000-09-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>921</th>\n",
       "      <td>2000</td>\n",
       "      <td>2Ge+her</td>\n",
       "      <td>The Hardest Part Of Breaking Up (Is Getting Ba...</td>\n",
       "      <td>3:15</td>\n",
       "      <td>R&amp;B</td>\n",
       "      <td>3</td>\n",
       "      <td>92</td>\n",
       "      <td>2000-09-16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      year artist.inverted                                              track  \\\n",
       "246   2000           2 Pac                Baby Don't Cry (Keep Ya Head Up II)   \n",
       "563   2000           2 Pac                Baby Don't Cry (Keep Ya Head Up II)   \n",
       "880   2000           2 Pac                Baby Don't Cry (Keep Ya Head Up II)   \n",
       "1197  2000           2 Pac                Baby Don't Cry (Keep Ya Head Up II)   \n",
       "1514  2000           2 Pac                Baby Don't Cry (Keep Ya Head Up II)   \n",
       "1831  2000           2 Pac                Baby Don't Cry (Keep Ya Head Up II)   \n",
       "2148  2000           2 Pac                Baby Don't Cry (Keep Ya Head Up II)   \n",
       "287   2000         2Ge+her  The Hardest Part Of Breaking Up (Is Getting Ba...   \n",
       "604   2000         2Ge+her  The Hardest Part Of Breaking Up (Is Getting Ba...   \n",
       "921   2000         2Ge+her  The Hardest Part Of Breaking Up (Is Getting Ba...   \n",
       "\n",
       "      time genre  week  rank       date  \n",
       "246   4:22   Rap     1    87 2000-02-26  \n",
       "563   4:22   Rap     2    82 2000-03-04  \n",
       "880   4:22   Rap     3    72 2000-03-11  \n",
       "1197  4:22   Rap     4    77 2000-03-18  \n",
       "1514  4:22   Rap     5    87 2000-03-25  \n",
       "1831  4:22   Rap     6    94 2000-04-01  \n",
       "2148  4:22   Rap     7    99 2000-04-08  \n",
       "287   3:15   R&B     1    91 2000-09-02  \n",
       "604   3:15   R&B     2    87 2000-09-09  \n",
       "921   3:15   R&B     3    92 2000-09-16  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Melting\n",
    "id_vars = [\"year\",\"artist.inverted\",\"track\",\"time\",\"genre\",\"date.entered\",\"date.peaked\"]\n",
    "df = pd.melt(frame=df,id_vars=id_vars, var_name=\"week\", value_name=\"rank\")\n",
    "\n",
    "# Formatting \n",
    "df[\"week\"] = df['week'].str.extract('(\\d+)', expand=False).astype(int)\n",
    "df[\"rank\"] = df[\"rank\"].astype(int)\n",
    "\n",
    "# Cleaning out unnecessary rows\n",
    "df = df.dropna()\n",
    "\n",
    "# Create \"date\" columns\n",
    "df['date'] = pd.to_datetime(df['date.entered']) + pd.to_timedelta(df['week'], unit='w') - pd.DateOffset(weeks=1)\n",
    "\n",
    "df = df[[\"year\", \"artist.inverted\", \"track\", \"time\", \"genre\", \"week\", \"rank\", \"date\"]]\n",
    "df = df.sort_values(ascending=True, by=[\"year\",\"artist.inverted\",\"track\",\"week\",\"rank\"])\n",
    "\n",
    "# Assigning the tidy dataset to a variable for future usage\n",
    "billboard = df\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multiple types in one table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>artist.inverted</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>song_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>2 Pac</td>\n",
       "      <td>Baby Don't Cry (Keep Ya Head Up II)</td>\n",
       "      <td>4:22</td>\n",
       "      <td>Rap</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000</td>\n",
       "      <td>2Ge+her</td>\n",
       "      <td>The Hardest Part Of Breaking Up (Is Getting Ba...</td>\n",
       "      <td>3:15</td>\n",
       "      <td>R&amp;B</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000</td>\n",
       "      <td>3 Doors Down</td>\n",
       "      <td>Kryptonite</td>\n",
       "      <td>3:53</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000</td>\n",
       "      <td>3 Doors Down</td>\n",
       "      <td>Loser</td>\n",
       "      <td>4:24</td>\n",
       "      <td>Rock</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000</td>\n",
       "      <td>504 Boyz</td>\n",
       "      <td>Wobble Wobble</td>\n",
       "      <td>3:35</td>\n",
       "      <td>Rap</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2000</td>\n",
       "      <td>98°</td>\n",
       "      <td>Give Me Just One Night (Una Noche)</td>\n",
       "      <td>3:24</td>\n",
       "      <td>Rock</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2000</td>\n",
       "      <td>A*Teens</td>\n",
       "      <td>Dancing Queen</td>\n",
       "      <td>3:44</td>\n",
       "      <td>Pop</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2000</td>\n",
       "      <td>Aaliyah</td>\n",
       "      <td>I Don't Wanna</td>\n",
       "      <td>4:15</td>\n",
       "      <td>Rock</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2000</td>\n",
       "      <td>Aaliyah</td>\n",
       "      <td>Try Again</td>\n",
       "      <td>4:03</td>\n",
       "      <td>Rock</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2000</td>\n",
       "      <td>Adams, Yolanda</td>\n",
       "      <td>Open My Heart</td>\n",
       "      <td>5:30</td>\n",
       "      <td>Gospel</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year artist.inverted                                              track  \\\n",
       "0  2000           2 Pac                Baby Don't Cry (Keep Ya Head Up II)   \n",
       "1  2000         2Ge+her  The Hardest Part Of Breaking Up (Is Getting Ba...   \n",
       "2  2000    3 Doors Down                                         Kryptonite   \n",
       "3  2000    3 Doors Down                                              Loser   \n",
       "4  2000        504 Boyz                                      Wobble Wobble   \n",
       "5  2000             98°                 Give Me Just One Night (Una Noche)   \n",
       "6  2000         A*Teens                                      Dancing Queen   \n",
       "7  2000         Aaliyah                                      I Don't Wanna   \n",
       "8  2000         Aaliyah                                          Try Again   \n",
       "9  2000  Adams, Yolanda                                      Open My Heart   \n",
       "\n",
       "   time   genre  song_id  \n",
       "0  4:22     Rap        0  \n",
       "1  3:15     R&B        1  \n",
       "2  3:53    Rock        2  \n",
       "3  4:24    Rock        3  \n",
       "4  3:35     Rap        4  \n",
       "5  3:24    Rock        5  \n",
       "6  3:44     Pop        6  \n",
       "7  4:15    Rock        7  \n",
       "8  4:03    Rock        8  \n",
       "9  5:30  Gospel        9  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "songs_cols = [\"year\", \"artist.inverted\", \"track\", \"time\", \"genre\"]\n",
    "songs = billboard[songs_cols].drop_duplicates()\n",
    "songs = songs.reset_index(drop=True)\n",
    "songs[\"song_id\"] = songs.index\n",
    "songs.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>date</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2000-02-26</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>2000-03-04</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>2000-03-11</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>2000-03-18</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>2000-03-25</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>2000-04-01</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>2000-04-08</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>2000-09-02</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>2000-09-09</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>2000-09-16</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   song_id       date  rank\n",
       "0        0 2000-02-26    87\n",
       "1        0 2000-03-04    82\n",
       "2        0 2000-03-11    72\n",
       "3        0 2000-03-18    77\n",
       "4        0 2000-03-25    87\n",
       "5        0 2000-04-01    94\n",
       "6        0 2000-04-08    99\n",
       "7        1 2000-09-02    91\n",
       "8        1 2000-09-09    87\n",
       "9        1 2000-09-16    92"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ranks = pd.merge(billboard, songs, on=[\"year\",\"artist.inverted\", \"track\", \"time\", \"genre\"])\n",
    "ranks = ranks[[\"song_id\", \"date\",\"rank\"]]\n",
    "ranks.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Multiple variables stored in one column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tubercolosis Example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A few notes on the raw data set:\n",
    "\n",
    "- The columns starting with \"m\" or \"f\" contain multiple variables: \n",
    "    - Sex (\"m\" or \"f\")\n",
    "    - Age Group (\"0-14\",\"15-24\", \"25-34\", \"45-54\", \"55-64\", \"65\", \"unknown\")\n",
    "- Mixture of 0s and missing values(\"NaN\"). This is due to the data collection process and the distinction is important for this dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>m014</th>\n",
       "      <th>m1524</th>\n",
       "      <th>m2534</th>\n",
       "      <th>m3544</th>\n",
       "      <th>m4554</th>\n",
       "      <th>m5564</th>\n",
       "      <th>m65</th>\n",
       "      <th>mu</th>\n",
       "      <th>f014</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AE</td>\n",
       "      <td>2000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AF</td>\n",
       "      <td>2000</td>\n",
       "      <td>52.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>129</td>\n",
       "      <td>94</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AG</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL</td>\n",
       "      <td>2000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>24</td>\n",
       "      <td>19</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AM</td>\n",
       "      <td>2000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>131.0</td>\n",
       "      <td>63</td>\n",
       "      <td>26</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AN</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AO</td>\n",
       "      <td>2000</td>\n",
       "      <td>186.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>1003.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>482</td>\n",
       "      <td>312</td>\n",
       "      <td>194.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>247.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AR</td>\n",
       "      <td>2000</td>\n",
       "      <td>97.0</td>\n",
       "      <td>278.0</td>\n",
       "      <td>594.0</td>\n",
       "      <td>402.0</td>\n",
       "      <td>419</td>\n",
       "      <td>368</td>\n",
       "      <td>330.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>121.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AS</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country  year   m014  m1524   m2534  m3544  m4554  m5564    m65  mu   f014\n",
       "0      AD  2000    0.0    0.0     1.0    0.0      0      0    0.0 NaN    NaN\n",
       "1      AE  2000    2.0    4.0     4.0    6.0      5     12   10.0 NaN    3.0\n",
       "2      AF  2000   52.0  228.0   183.0  149.0    129     94   80.0 NaN   93.0\n",
       "3      AG  2000    0.0    0.0     0.0    0.0      0      0    1.0 NaN    1.0\n",
       "4      AL  2000    2.0   19.0    21.0   14.0     24     19   16.0 NaN    3.0\n",
       "5      AM  2000    2.0  152.0   130.0  131.0     63     26   21.0 NaN    1.0\n",
       "6      AN  2000    0.0    0.0     1.0    2.0      0      0    0.0 NaN    0.0\n",
       "7      AO  2000  186.0  999.0  1003.0  912.0    482    312  194.0 NaN  247.0\n",
       "8      AR  2000   97.0  278.0   594.0  402.0    419    368  330.0 NaN  121.0\n",
       "9      AS  2000    NaN    NaN     NaN    NaN      1      1    NaN NaN    NaN"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"./data/tb-raw.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>cases</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>m</td>\n",
       "      <td>0-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>m</td>\n",
       "      <td>15-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>m</td>\n",
       "      <td>25-34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>m</td>\n",
       "      <td>35-44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>m</td>\n",
       "      <td>45-54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>m</td>\n",
       "      <td>55-64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>AE</td>\n",
       "      <td>2000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AE</td>\n",
       "      <td>2000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>m</td>\n",
       "      <td>0-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AE</td>\n",
       "      <td>2000</td>\n",
       "      <td>4.0</td>\n",
       "      <td>m</td>\n",
       "      <td>15-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AE</td>\n",
       "      <td>2000</td>\n",
       "      <td>4.0</td>\n",
       "      <td>m</td>\n",
       "      <td>25-34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  year  cases sex    age\n",
       "0       AD  2000    0.0   m   0-14\n",
       "10      AD  2000    0.0   m  15-24\n",
       "20      AD  2000    1.0   m  25-34\n",
       "30      AD  2000    0.0   m  35-44\n",
       "40      AD  2000    0.0   m  45-54\n",
       "50      AD  2000    0.0   m  55-64\n",
       "81      AE  2000    3.0   f   0-14\n",
       "1       AE  2000    2.0   m   0-14\n",
       "11      AE  2000    4.0   m  15-24\n",
       "21      AE  2000    4.0   m  25-34"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.melt(df, id_vars=[\"country\",\"year\"], value_name=\"cases\", var_name=\"sex_and_age\")\n",
    "\n",
    "# Extract Sex, Age lower bound and Age upper bound group\n",
    "tmp_df = df[\"sex_and_age\"].str.extract(\"(\\D)(\\d+)(\\d{2})\", expand=False)    \n",
    "\n",
    "# Name columns\n",
    "tmp_df.columns = [\"sex\", \"age_lower\", \"age_upper\"]\n",
    "\n",
    "# Create `age`column based on `age_lower` and `age_upper`\n",
    "tmp_df[\"age\"] = tmp_df[\"age_lower\"] + \"-\" + tmp_df[\"age_upper\"]\n",
    "\n",
    "# Merge \n",
    "df = pd.concat([df, tmp_df], axis=1)\n",
    "\n",
    "# Drop unnecessary columns and rows\n",
    "df = df.drop(['sex_and_age',\"age_lower\",\"age_upper\"], axis=1)\n",
    "df = df.dropna()\n",
    "df = df.sort_values(ascending=True,by=[\"country\", \"year\", \"sex\", \"age\"])\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Variables are stored in both rows and columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Global Historical Climatology Network Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./data/weather-raw.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>element</th>\n",
       "      <th>day_raw</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>4</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>4</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>5</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>5</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id  year  month element day_raw  value\n",
       "0   MX17004  2010      1    tmax      d1    NaN\n",
       "1   MX17004  2010      1    tmin      d1    NaN\n",
       "2  MX17004   2010      2    tmax      d1    NaN\n",
       "3   MX17004  2010      2    tmin      d1    NaN\n",
       "4   MX17004  2010      3    tmax      d1    NaN\n",
       "5   MX17004  2010      3    tmin      d1    NaN\n",
       "6   MX17004  2010      4    tmax      d1    NaN\n",
       "7   MX17004  2010      4    tmin      d1    NaN\n",
       "8   MX17004  2010      5    tmax      d1    NaN\n",
       "9   MX17004  2010      5    tmin      d1    NaN"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.melt(df, id_vars=[\"id\", \"year\",\"month\",\"element\"], var_name=\"day_raw\")\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>element</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tmax</th>\n",
       "      <th>tmin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010-02-02</td>\n",
       "      <td>27.3</td>\n",
       "      <td>14.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010-02-03</td>\n",
       "      <td>24.1</td>\n",
       "      <td>14.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010-03-05</td>\n",
       "      <td>32.1</td>\n",
       "      <td>14.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "element       id       date  tmax  tmin\n",
       "0        MX17004 2010-02-02  27.3  14.4\n",
       "1        MX17004 2010-02-03  24.1  14.4\n",
       "2        MX17004 2010-03-05  32.1  14.2"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extracting day\n",
    "df[\"day\"] = df[\"day_raw\"].str.extract(\"d(\\d+)\", expand=False)  \n",
    "df[\"id\"] = \"MX17004\"\n",
    "\n",
    "# To numeric values\n",
    "df[[\"year\",\"month\",\"day\"]] = df[[\"year\",\"month\",\"day\"]].apply(lambda x: pd.to_numeric(x, errors='ignore'))\n",
    "\n",
    "# Creating a date from the different columns\n",
    "def create_date_from_year_month_day(row):\n",
    "    return datetime.datetime(year=row[\"year\"], month=int(row[\"month\"]), day=row[\"day\"])\n",
    "\n",
    "df[\"date\"] = df.apply(lambda row: create_date_from_year_month_day(row), axis=1)\n",
    "df = df.drop(['year',\"month\",\"day\", \"day_raw\"], axis=1)\n",
    "df = df.dropna()\n",
    "\n",
    "# Unmelting column \"element\"\n",
    "df = df.pivot_table(index=[\"id\",\"date\"], columns=\"element\", values=\"value\")\n",
    "df.reset_index(drop=False, inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## One type in multiple tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Baby Names in Illinois"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rank</th>\n",
       "      <th>name</th>\n",
       "      <th>frequency</th>\n",
       "      <th>sex</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Noah</td>\n",
       "      <td>837</td>\n",
       "      <td>Male</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>747</td>\n",
       "      <td>Male</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>William</td>\n",
       "      <td>687</td>\n",
       "      <td>Male</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Michael</td>\n",
       "      <td>680</td>\n",
       "      <td>Male</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Liam</td>\n",
       "      <td>670</td>\n",
       "      <td>Male</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rank       name  frequency   sex  year\n",
       "0     1       Noah        837  Male  2014\n",
       "1     2  Alexander        747  Male  2014\n",
       "2     3    William        687  Male  2014\n",
       "3     4    Michael        680  Male  2014\n",
       "4     5       Liam        670  Male  2014"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def extract_year(string):\n",
    "    match = re.match(\".+(\\d{4})\", string) \n",
    "    if match != None: return match.group(1)\n",
    "    \n",
    "path = './data'\n",
    "allFiles = glob.glob(path + \"/201*-baby-names-illinois.csv\")\n",
    "frame = pd.DataFrame()\n",
    "df_list= []\n",
    "for file_ in allFiles:\n",
    "    df = pd.read_csv(file_,index_col=None, header=0)\n",
    "    df.columns = map(str.lower, df.columns)\n",
    "    df[\"year\"] = extract_year(file_)\n",
    "    df_list.append(df)\n",
    "    \n",
    "df = pd.concat(df_list)\n",
    "df.head(5)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}