03-tidy-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\projects\\temp\\spreadsheets-to-dataframes\n"
     ]
    }
   ],
   "source": [
    "# original source:\n",
    "# https://github.com/chendaniely/pydatadc_2018-tidy/blob/master/notebooks/tidy.ipynb\n",
    "import os\n",
    "print(os.getcwd())\n",
    "\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# columns contining values, not variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "pew = pd.read_csv(r'./data/pew.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>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",
       "      <th>$75-100k</th>\n",
       "      <th>$100-150k</th>\n",
       "      <th>&gt;150k</th>\n",
       "      <th>Don't know/refused</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",
       "      <td>122</td>\n",
       "      <td>109</td>\n",
       "      <td>84</td>\n",
       "      <td>96</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",
       "      <td>73</td>\n",
       "      <td>59</td>\n",
       "      <td>74</td>\n",
       "      <td>76</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",
       "      <td>62</td>\n",
       "      <td>39</td>\n",
       "      <td>53</td>\n",
       "      <td>54</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",
       "      <td>949</td>\n",
       "      <td>792</td>\n",
       "      <td>633</td>\n",
       "      <td>1489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Don’t 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",
       "      <td>21</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>116</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",
       "      <td>949</td>\n",
       "      <td>723</td>\n",
       "      <td>414</td>\n",
       "      <td>1529</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",
       "      <td>47</td>\n",
       "      <td>48</td>\n",
       "      <td>54</td>\n",
       "      <td>37</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",
       "      <td>131</td>\n",
       "      <td>81</td>\n",
       "      <td>78</td>\n",
       "      <td>339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Jehovah's 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",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>37</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",
       "      <td>69</td>\n",
       "      <td>87</td>\n",
       "      <td>151</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Mainline Prot</td>\n",
       "      <td>289</td>\n",
       "      <td>495</td>\n",
       "      <td>619</td>\n",
       "      <td>655</td>\n",
       "      <td>651</td>\n",
       "      <td>1107</td>\n",
       "      <td>939</td>\n",
       "      <td>753</td>\n",
       "      <td>634</td>\n",
       "      <td>1328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Mormon</td>\n",
       "      <td>29</td>\n",
       "      <td>40</td>\n",
       "      <td>48</td>\n",
       "      <td>51</td>\n",
       "      <td>56</td>\n",
       "      <td>112</td>\n",
       "      <td>85</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Muslim</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Orthodox</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "      <td>23</td>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>47</td>\n",
       "      <td>38</td>\n",
       "      <td>42</td>\n",
       "      <td>46</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Other Christian</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>18</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Other Faiths</td>\n",
       "      <td>20</td>\n",
       "      <td>33</td>\n",
       "      <td>40</td>\n",
       "      <td>46</td>\n",
       "      <td>49</td>\n",
       "      <td>63</td>\n",
       "      <td>46</td>\n",
       "      <td>40</td>\n",
       "      <td>41</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Other World Religions</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Unaffiliated</td>\n",
       "      <td>217</td>\n",
       "      <td>299</td>\n",
       "      <td>374</td>\n",
       "      <td>365</td>\n",
       "      <td>341</td>\n",
       "      <td>528</td>\n",
       "      <td>407</td>\n",
       "      <td>321</td>\n",
       "      <td>258</td>\n",
       "      <td>597</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        Don’t 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         Jehovah's Witness     20       27       24       24       21   \n",
       "9                    Jewish     19       19       25       25       30   \n",
       "10            Mainline Prot    289      495      619      655      651   \n",
       "11                   Mormon     29       40       48       51       56   \n",
       "12                   Muslim      6        7        9       10        9   \n",
       "13                 Orthodox     13       17       23       32       32   \n",
       "14          Other Christian      9        7       11       13       13   \n",
       "15             Other Faiths     20       33       40       46       49   \n",
       "16    Other World Religions      5        2        3        4        2   \n",
       "17             Unaffiliated    217      299      374      365      341   \n",
       "\n",
       "    $50-75k  $75-100k  $100-150k  >150k  Don't know/refused  \n",
       "0       137       122        109     84                  96  \n",
       "1        70        73         59     74                  76  \n",
       "2        58        62         39     53                  54  \n",
       "3      1116       949        792    633                1489  \n",
       "4        35        21         17     18                 116  \n",
       "5      1486       949        723    414                1529  \n",
       "6        34        47         48     54                  37  \n",
       "7       223       131         81     78                 339  \n",
       "8        30        15         11      6                  37  \n",
       "9        95        69         87    151                 162  \n",
       "10     1107       939        753    634                1328  \n",
       "11      112        85         49     42                  69  \n",
       "12       23        16          8      6                  22  \n",
       "13       47        38         42     46                  73  \n",
       "14       14        18         14     12                  18  \n",
       "15       63        46         40     41                  71  \n",
       "16        7         3          4      4                   8  \n",
       "17      528       407        321    258                 597  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# wide format\n",
    "pew"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "pew_long = pd.melt(pew, id_vars='religion')"
   ]
  },
  {
   "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>religion</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</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>1</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buddhist</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Catholic</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Don’t know/refused</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>Orthodox</td>\n",
       "      <td>Don't know/refused</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>Other Christian</td>\n",
       "      <td>Don't know/refused</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>Other Faiths</td>\n",
       "      <td>Don't know/refused</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>Other World Religions</td>\n",
       "      <td>Don't know/refused</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Unaffiliated</td>\n",
       "      <td>Don't know/refused</td>\n",
       "      <td>597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>180 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  religion            variable  value\n",
       "0                 Agnostic               <$10k     27\n",
       "1                  Atheist               <$10k     12\n",
       "2                 Buddhist               <$10k     27\n",
       "3                 Catholic               <$10k    418\n",
       "4       Don’t know/refused               <$10k     15\n",
       "..                     ...                 ...    ...\n",
       "175               Orthodox  Don't know/refused     73\n",
       "176        Other Christian  Don't know/refused     18\n",
       "177           Other Faiths  Don't know/refused     71\n",
       "178  Other World Religions  Don't know/refused      8\n",
       "179           Unaffiliated  Don't know/refused    597\n",
       "\n",
       "[180 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# transforms to wide format\n",
    "pew.melt(id_vars='religion')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to change change the melt variable column name, specify names\n",
    "pew_tidy = pew.melt(id_vars='religion', var_name=\"income\", value_name=\"count\")"
   ]
  },
  {
   "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>religion</th>\n",
       "      <th>income</th>\n",
       "      <th>count</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>1</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buddhist</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Catholic</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Don’t know/refused</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             religion income  count\n",
       "0            Agnostic  <$10k     27\n",
       "1             Atheist  <$10k     12\n",
       "2            Buddhist  <$10k     27\n",
       "3            Catholic  <$10k    418\n",
       "4  Don’t know/refused  <$10k     15"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pew_tidy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "billboard = pd.read_csv('./data/billboard.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>year</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>wk1</th>\n",
       "      <th>wk2</th>\n",
       "      <th>wk3</th>\n",
       "      <th>wk4</th>\n",
       "      <th>wk5</th>\n",
       "      <th>...</th>\n",
       "      <th>wk67</th>\n",
       "      <th>wk68</th>\n",
       "      <th>wk69</th>\n",
       "      <th>wk70</th>\n",
       "      <th>wk71</th>\n",
       "      <th>wk72</th>\n",
       "      <th>wk73</th>\n",
       "      <th>wk74</th>\n",
       "      <th>wk75</th>\n",
       "      <th>wk76</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...</td>\n",
       "      <td>4:22</td>\n",
       "      <td>2000-02-26</td>\n",
       "      <td>87</td>\n",
       "      <td>82.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>87.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>2Ge+her</td>\n",
       "      <td>The Hardest Part Of ...</td>\n",
       "      <td>3:15</td>\n",
       "      <td>2000-09-02</td>\n",
       "      <td>91</td>\n",
       "      <td>87.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>3 Doors Down</td>\n",
       "      <td>Kryptonite</td>\n",
       "      <td>3:53</td>\n",
       "      <td>2000-04-08</td>\n",
       "      <td>81</td>\n",
       "      <td>70.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>66.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>3 Doors Down</td>\n",
       "      <td>Loser</td>\n",
       "      <td>4:24</td>\n",
       "      <td>2000-10-21</td>\n",
       "      <td>76</td>\n",
       "      <td>76.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>67.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>504 Boyz</td>\n",
       "      <td>Wobble Wobble</td>\n",
       "      <td>3:35</td>\n",
       "      <td>2000-04-15</td>\n",
       "      <td>57</td>\n",
       "      <td>34.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>17.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>5 rows × 81 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   year        artist                    track  time date.entered  wk1   wk2  \\\n",
       "0  2000         2 Pac  Baby Don't Cry (Keep...  4:22   2000-02-26   87  82.0   \n",
       "1  2000       2Ge+her  The Hardest Part Of ...  3:15   2000-09-02   91  87.0   \n",
       "2  2000  3 Doors Down               Kryptonite  3:53   2000-04-08   81  70.0   \n",
       "3  2000  3 Doors Down                    Loser  4:24   2000-10-21   76  76.0   \n",
       "4  2000      504 Boyz            Wobble Wobble  3:35   2000-04-15   57  34.0   \n",
       "\n",
       "    wk3   wk4   wk5  ...  wk67  wk68  wk69  wk70  wk71  wk72  wk73  wk74  \\\n",
       "0  72.0  77.0  87.0  ...   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1  92.0   NaN   NaN  ...   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "2  68.0  67.0  66.0  ...   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "3  72.0  69.0  67.0  ...   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "4  25.0  17.0  17.0  ...   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   wk75  wk76  \n",
       "0   NaN   NaN  \n",
       "1   NaN   NaN  \n",
       "2   NaN   NaN  \n",
       "3   NaN   NaN  \n",
       "4   NaN   NaN  \n",
       "\n",
       "[5 rows x 81 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billboard.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(317, 81)\n"
     ]
    },
    {
     "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>year</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>week</th>\n",
       "      <th>rank</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...</td>\n",
       "      <td>4:22</td>\n",
       "      <td>2000-02-26</td>\n",
       "      <td>wk1</td>\n",
       "      <td>87.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 ...</td>\n",
       "      <td>3:15</td>\n",
       "      <td>2000-09-02</td>\n",
       "      <td>wk1</td>\n",
       "      <td>91.0</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>2000-04-08</td>\n",
       "      <td>wk1</td>\n",
       "      <td>81.0</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>2000-10-21</td>\n",
       "      <td>wk1</td>\n",
       "      <td>76.0</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>2000-04-15</td>\n",
       "      <td>wk1</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24087</th>\n",
       "      <td>2000</td>\n",
       "      <td>Yankee Grey</td>\n",
       "      <td>Another Nine Minutes</td>\n",
       "      <td>3:10</td>\n",
       "      <td>2000-04-29</td>\n",
       "      <td>wk76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24088</th>\n",
       "      <td>2000</td>\n",
       "      <td>Yearwood, Trisha</td>\n",
       "      <td>Real Live Woman</td>\n",
       "      <td>3:55</td>\n",
       "      <td>2000-04-01</td>\n",
       "      <td>wk76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24089</th>\n",
       "      <td>2000</td>\n",
       "      <td>Ying Yang Twins</td>\n",
       "      <td>Whistle While You Tw...</td>\n",
       "      <td>4:19</td>\n",
       "      <td>2000-03-18</td>\n",
       "      <td>wk76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24090</th>\n",
       "      <td>2000</td>\n",
       "      <td>Zombie Nation</td>\n",
       "      <td>Kernkraft 400</td>\n",
       "      <td>3:30</td>\n",
       "      <td>2000-09-02</td>\n",
       "      <td>wk76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24091</th>\n",
       "      <td>2000</td>\n",
       "      <td>matchbox twenty</td>\n",
       "      <td>Bent</td>\n",
       "      <td>4:12</td>\n",
       "      <td>2000-04-29</td>\n",
       "      <td>wk76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24092 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       year            artist                    track  time date.entered  \\\n",
       "0      2000             2 Pac  Baby Don't Cry (Keep...  4:22   2000-02-26   \n",
       "1      2000           2Ge+her  The Hardest Part Of ...  3:15   2000-09-02   \n",
       "2      2000      3 Doors Down               Kryptonite  3:53   2000-04-08   \n",
       "3      2000      3 Doors Down                    Loser  4:24   2000-10-21   \n",
       "4      2000          504 Boyz            Wobble Wobble  3:35   2000-04-15   \n",
       "...     ...               ...                      ...   ...          ...   \n",
       "24087  2000       Yankee Grey     Another Nine Minutes  3:10   2000-04-29   \n",
       "24088  2000  Yearwood, Trisha          Real Live Woman  3:55   2000-04-01   \n",
       "24089  2000   Ying Yang Twins  Whistle While You Tw...  4:19   2000-03-18   \n",
       "24090  2000     Zombie Nation            Kernkraft 400  3:30   2000-09-02   \n",
       "24091  2000   matchbox twenty                     Bent  4:12   2000-04-29   \n",
       "\n",
       "       week  rank  \n",
       "0       wk1  87.0  \n",
       "1       wk1  91.0  \n",
       "2       wk1  81.0  \n",
       "3       wk1  76.0  \n",
       "4       wk1  57.0  \n",
       "...     ...   ...  \n",
       "24087  wk76   NaN  \n",
       "24088  wk76   NaN  \n",
       "24089  wk76   NaN  \n",
       "24090  wk76   NaN  \n",
       "24091  wk76   NaN  \n",
       "\n",
       "[24092 rows x 7 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we actually have 81 columns\n",
    "print(billboard.shape)\n",
    "\n",
    "\n",
    "#  example where we want to keep the first set of columns fixed while melting the rest of the columns\n",
    "# I'm holding the year, artist, track, time, and date entered columns fixed\n",
    "#  rest of the columns will be pivoted into a new variable called week\n",
    "# And the values of those columns will be pivoted into a new column called rating\n",
    "billboard.melt(id_vars=['year', 'artist', 'track', 'time', 'date.entered'],\n",
    "               value_name='rank',\n",
    "               var_name='week')\n",
    "\n",
    "# All of the week values have been melted into a single column, followed by a rating.\n",
    "# This is a useful feature, because if you remember from the plotting exercises,\n",
    "# we can then do something like a faceted plot along the week column.\n",
    "# This is one of the reasons why tidy data is so important,\n",
    "# because it allows us to manipulate and visualize our data sets extremely quickly.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "artist\n",
       "2 Pac               85.428571\n",
       "2Ge+her             90.000000\n",
       "3 Doors Down        37.602740\n",
       "504 Boyz            56.222222\n",
       "98^0                37.650000\n",
       "                      ...    \n",
       "Yankee Grey         83.125000\n",
       "Yearwood, Trisha    84.166667\n",
       "Ying Yang Twins     88.857143\n",
       "Zombie Nation       99.000000\n",
       "matchbox twenty     18.641026\n",
       "Name: rank, Length: 228, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "billboard\n",
    "  .melt(id_vars=['year', 'artist', 'track', 'time', 'date.entered'],\n",
    "        value_name='rank',\n",
    "        var_name='week')\n",
    "  .groupby('artist')['rank']\n",
    "  .mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "artist\n",
       "2 Pac               85.428571\n",
       "2Ge+her             90.000000\n",
       "3 Doors Down        37.602740\n",
       "504 Boyz            56.222222\n",
       "98^0                37.650000\n",
       "                      ...    \n",
       "Yankee Grey         83.125000\n",
       "Yearwood, Trisha    84.166667\n",
       "Ying Yang Twins     88.857143\n",
       "Zombie Nation       99.000000\n",
       "matchbox twenty     18.641026\n",
       "Name: rank, Length: 228, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billboard\\\n",
    "  .melt(id_vars=['year', 'artist', 'track', 'time', 'date.entered'],\n",
    "        value_name='rank',\n",
    "        var_name='week')\\\n",
    "  .groupby('artist')['rank']\\\n",
    "  .mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola = pd.read_csv('./data/country_timeseries.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Day</th>\n",
       "      <th>Cases_Guinea</th>\n",
       "      <th>Cases_Liberia</th>\n",
       "      <th>Cases_SierraLeone</th>\n",
       "      <th>Cases_Nigeria</th>\n",
       "      <th>Cases_Senegal</th>\n",
       "      <th>Cases_UnitedStates</th>\n",
       "      <th>Cases_Spain</th>\n",
       "      <th>Cases_Mali</th>\n",
       "      <th>Deaths_Guinea</th>\n",
       "      <th>Deaths_Liberia</th>\n",
       "      <th>Deaths_SierraLeone</th>\n",
       "      <th>Deaths_Nigeria</th>\n",
       "      <th>Deaths_Senegal</th>\n",
       "      <th>Deaths_UnitedStates</th>\n",
       "      <th>Deaths_Spain</th>\n",
       "      <th>Deaths_Mali</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1/5/2015</td>\n",
       "      <td>289</td>\n",
       "      <td>2776.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10030.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1786.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2977.0</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>1/4/2015</td>\n",
       "      <td>288</td>\n",
       "      <td>2775.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9780.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1781.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2943.0</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>1/3/2015</td>\n",
       "      <td>287</td>\n",
       "      <td>2769.0</td>\n",
       "      <td>8166.0</td>\n",
       "      <td>9722.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1767.0</td>\n",
       "      <td>3496.0</td>\n",
       "      <td>2915.0</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>1/2/2015</td>\n",
       "      <td>286</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8157.0</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>3496.0</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>12/31/2014</td>\n",
       "      <td>284</td>\n",
       "      <td>2730.0</td>\n",
       "      <td>8115.0</td>\n",
       "      <td>9633.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1739.0</td>\n",
       "      <td>3471.0</td>\n",
       "      <td>2827.0</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",
       "</div>"
      ],
      "text/plain": [
       "         Date  Day  Cases_Guinea  Cases_Liberia  Cases_SierraLeone  \\\n",
       "0    1/5/2015  289        2776.0            NaN            10030.0   \n",
       "1    1/4/2015  288        2775.0            NaN             9780.0   \n",
       "2    1/3/2015  287        2769.0         8166.0             9722.0   \n",
       "3    1/2/2015  286           NaN         8157.0                NaN   \n",
       "4  12/31/2014  284        2730.0         8115.0             9633.0   \n",
       "\n",
       "   Cases_Nigeria  Cases_Senegal  Cases_UnitedStates  Cases_Spain  Cases_Mali  \\\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",
       "\n",
       "   Deaths_Guinea  Deaths_Liberia  Deaths_SierraLeone  Deaths_Nigeria  \\\n",
       "0         1786.0             NaN              2977.0             NaN   \n",
       "1         1781.0             NaN              2943.0             NaN   \n",
       "2         1767.0          3496.0              2915.0             NaN   \n",
       "3            NaN          3496.0                 NaN             NaN   \n",
       "4         1739.0          3471.0              2827.0             NaN   \n",
       "\n",
       "   Deaths_Senegal  Deaths_UnitedStates  Deaths_Spain  Deaths_Mali  \n",
       "0             NaN                  NaN           NaN          NaN  \n",
       "1             NaN                  NaN           NaN          NaN  \n",
       "2             NaN                  NaN           NaN          NaN  \n",
       "3             NaN                  NaN           NaN          NaN  \n",
       "4             NaN                  NaN           NaN          NaN  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If you look at the columns of our data, you can see that most of these columns actually contain two variables.\n",
    "# It tells us whether this is a count or a death count, and what country it's in\n",
    "#  we really need to do is separate out those variables.\n",
    "\n",
    "ebola.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_long = ebola.melt(id_vars=['Date', 'Day'],\n",
    "                       var_name='cd_country',\n",
    "                       value_name='count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Day</th>\n",
       "      <th>cd_country</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1/5/2015</td>\n",
       "      <td>289</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2776.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1/4/2015</td>\n",
       "      <td>288</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2775.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1/3/2015</td>\n",
       "      <td>287</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2769.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1/2/2015</td>\n",
       "      <td>286</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12/31/2014</td>\n",
       "      <td>284</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2730.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Day    cd_country   count\n",
       "0    1/5/2015  289  Cases_Guinea  2776.0\n",
       "1    1/4/2015  288  Cases_Guinea  2775.0\n",
       "2    1/3/2015  287  Cases_Guinea  2769.0\n",
       "3    1/2/2015  286  Cases_Guinea     NaN\n",
       "4  12/31/2014  284  Cases_Guinea  2730.0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ebola_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['hello', 'world']"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'hello_world'.split('_')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_split = ebola_long['cd_country'].str.split('_', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_long['test'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Day</th>\n",
       "      <th>cd_country</th>\n",
       "      <th>count</th>\n",
       "      <th>test</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1/5/2015</td>\n",
       "      <td>289</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2776.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1/4/2015</td>\n",
       "      <td>288</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2775.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1/3/2015</td>\n",
       "      <td>287</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2769.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1/2/2015</td>\n",
       "      <td>286</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12/31/2014</td>\n",
       "      <td>284</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2730.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Day    cd_country   count  test\n",
       "0    1/5/2015  289  Cases_Guinea  2776.0     1\n",
       "1    1/4/2015  288  Cases_Guinea  2775.0     1\n",
       "2    1/3/2015  287  Cases_Guinea  2769.0     1\n",
       "3    1/2/2015  286  Cases_Guinea     NaN     1\n",
       "4  12/31/2014  284  Cases_Guinea  2730.0     1"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ebola_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_long[['status', 'country']] = ebola_split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Date</th>\n",
       "      <th>Day</th>\n",
       "      <th>cd_country</th>\n",
       "      <th>count</th>\n",
       "      <th>test</th>\n",
       "      <th>status</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1/5/2015</td>\n",
       "      <td>289</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2776.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Cases</td>\n",
       "      <td>Guinea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1/4/2015</td>\n",
       "      <td>288</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2775.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Cases</td>\n",
       "      <td>Guinea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1/3/2015</td>\n",
       "      <td>287</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2769.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Cases</td>\n",
       "      <td>Guinea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1/2/2015</td>\n",
       "      <td>286</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Cases</td>\n",
       "      <td>Guinea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12/31/2014</td>\n",
       "      <td>284</td>\n",
       "      <td>Cases_Guinea</td>\n",
       "      <td>2730.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Cases</td>\n",
       "      <td>Guinea</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Day    cd_country   count  test status country\n",
       "0    1/5/2015  289  Cases_Guinea  2776.0     1  Cases  Guinea\n",
       "1    1/4/2015  288  Cases_Guinea  2775.0     1  Cases  Guinea\n",
       "2    1/3/2015  287  Cases_Guinea  2769.0     1  Cases  Guinea\n",
       "3    1/2/2015  286  Cases_Guinea     NaN     1  Cases  Guinea\n",
       "4  12/31/2014  284  Cases_Guinea  2730.0     1  Cases  Guinea"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ebola_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here are the first five rows and twelve columns of a weather data set. You can see we have an ID, a year, a month, an element that tells us whether this is the max temperature or min temperature, and days.\n",
    "# What we wanna do is melt down the days so that we have a column that represents day of the month, and another column for a value.\n",
    "# However, what's problematic is this element column, what we really want is to have a column that represents the day of the month, and the value for the max temperature and a value for the minimum temperature.\n",
    "weather = pd.read_csv('./data/weather.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>element</th>\n",
       "      <th>d1</th>\n",
       "      <th>d2</th>\n",
       "      <th>d3</th>\n",
       "      <th>d4</th>\n",
       "      <th>d5</th>\n",
       "      <th>d6</th>\n",
       "      <th>...</th>\n",
       "      <th>d22</th>\n",
       "      <th>d23</th>\n",
       "      <th>d24</th>\n",
       "      <th>d25</th>\n",
       "      <th>d26</th>\n",
       "      <th>d27</th>\n",
       "      <th>d28</th>\n",
       "      <th>d29</th>\n",
       "      <th>d30</th>\n",
       "      <th>d31</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>27.8</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>14.5</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>NaN</td>\n",
       "      <td>27.3</td>\n",
       "      <td>24.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.9</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>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.4</td>\n",
       "      <td>14.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.7</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>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.1</td>\n",
       "      <td>NaN</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>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  year  month element  d1    d2    d3  d4    d5  d6  ...  d22   d23  \\\n",
       "0  MX17004  2010      1    tmax NaN   NaN   NaN NaN   NaN NaN  ...  NaN   NaN   \n",
       "1  MX17004  2010      1    tmin NaN   NaN   NaN NaN   NaN NaN  ...  NaN   NaN   \n",
       "2  MX17004  2010      2    tmax NaN  27.3  24.1 NaN   NaN NaN  ...  NaN  29.9   \n",
       "3  MX17004  2010      2    tmin NaN  14.4  14.4 NaN   NaN NaN  ...  NaN  10.7   \n",
       "4  MX17004  2010      3    tmax NaN   NaN   NaN NaN  32.1 NaN  ...  NaN   NaN   \n",
       "\n",
       "   d24  d25  d26  d27  d28  d29   d30  d31  \n",
       "0  NaN  NaN  NaN  NaN  NaN  NaN  27.8  NaN  \n",
       "1  NaN  NaN  NaN  NaN  NaN  NaN  14.5  NaN  \n",
       "2  NaN  NaN  NaN  NaN  NaN  NaN   NaN  NaN  \n",
       "3  NaN  NaN  NaN  NaN  NaN  NaN   NaN  NaN  \n",
       "4  NaN  NaN  NaN  NaN  NaN  NaN   NaN  NaN  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# So this is the problem that we're going to address here. Since I already know how to solve part of the problem, that is how do we take the days of the month and bring it down, we can use the melt function, so let's first do that.\n",
    "weather_long = weather.melt(id_vars=['id', 'year', 'month', 'element'],\n",
    "                            var_name='day',\n",
    "                            value_name='temp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>element</th>\n",
       "      <th>day</th>\n",
       "      <th>temp</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  year  month element day  temp\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"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>element</th>\n",
       "      <th>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</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</td>\n",
       "      <td>1</td>\n",
       "      <td>d30</td>\n",
       "      <td>27.8</td>\n",
       "      <td>14.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>d11</td>\n",
       "      <td>29.7</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>d2</td>\n",
       "      <td>27.3</td>\n",
       "      <td>14.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>d23</td>\n",
       "      <td>29.9</td>\n",
       "      <td>10.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>d3</td>\n",
       "      <td>24.1</td>\n",
       "      <td>14.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>d10</td>\n",
       "      <td>34.5</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>d16</td>\n",
       "      <td>31.1</td>\n",
       "      <td>17.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>d5</td>\n",
       "      <td>32.1</td>\n",
       "      <td>14.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>4</td>\n",
       "      <td>d27</td>\n",
       "      <td>36.3</td>\n",
       "      <td>16.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>5</td>\n",
       "      <td>d27</td>\n",
       "      <td>33.2</td>\n",
       "      <td>18.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>6</td>\n",
       "      <td>d17</td>\n",
       "      <td>28.0</td>\n",
       "      <td>17.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>6</td>\n",
       "      <td>d29</td>\n",
       "      <td>30.1</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>7</td>\n",
       "      <td>d3</td>\n",
       "      <td>28.6</td>\n",
       "      <td>17.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>7</td>\n",
       "      <td>d14</td>\n",
       "      <td>29.9</td>\n",
       "      <td>16.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>8</td>\n",
       "      <td>d23</td>\n",
       "      <td>26.4</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>8</td>\n",
       "      <td>d5</td>\n",
       "      <td>29.6</td>\n",
       "      <td>15.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>8</td>\n",
       "      <td>d29</td>\n",
       "      <td>28.0</td>\n",
       "      <td>15.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>8</td>\n",
       "      <td>d13</td>\n",
       "      <td>29.8</td>\n",
       "      <td>16.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>8</td>\n",
       "      <td>d25</td>\n",
       "      <td>29.7</td>\n",
       "      <td>15.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>8</td>\n",
       "      <td>d31</td>\n",
       "      <td>25.4</td>\n",
       "      <td>15.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>8</td>\n",
       "      <td>d8</td>\n",
       "      <td>29.0</td>\n",
       "      <td>17.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "      <td>d5</td>\n",
       "      <td>27.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "      <td>d14</td>\n",
       "      <td>29.5</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "      <td>d15</td>\n",
       "      <td>28.7</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "      <td>d28</td>\n",
       "      <td>31.2</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "      <td>d7</td>\n",
       "      <td>28.1</td>\n",
       "      <td>12.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>11</td>\n",
       "      <td>d2</td>\n",
       "      <td>31.3</td>\n",
       "      <td>16.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>11</td>\n",
       "      <td>d5</td>\n",
       "      <td>26.3</td>\n",
       "      <td>7.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>11</td>\n",
       "      <td>d27</td>\n",
       "      <td>27.7</td>\n",
       "      <td>14.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>11</td>\n",
       "      <td>d26</td>\n",
       "      <td>28.1</td>\n",
       "      <td>12.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>11</td>\n",
       "      <td>d4</td>\n",
       "      <td>27.2</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>12</td>\n",
       "      <td>d1</td>\n",
       "      <td>29.9</td>\n",
       "      <td>13.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>12</td>\n",
       "      <td>d6</td>\n",
       "      <td>27.8</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "element       id  year  month  day  tmax  tmin\n",
       "0        MX17004  2010      1  d30  27.8  14.5\n",
       "1        MX17004  2010      2  d11  29.7  13.4\n",
       "2        MX17004  2010      2   d2  27.3  14.4\n",
       "3        MX17004  2010      2  d23  29.9  10.7\n",
       "4        MX17004  2010      2   d3  24.1  14.4\n",
       "5        MX17004  2010      3  d10  34.5  16.8\n",
       "6        MX17004  2010      3  d16  31.1  17.6\n",
       "7        MX17004  2010      3   d5  32.1  14.2\n",
       "8        MX17004  2010      4  d27  36.3  16.7\n",
       "9        MX17004  2010      5  d27  33.2  18.2\n",
       "10       MX17004  2010      6  d17  28.0  17.5\n",
       "11       MX17004  2010      6  d29  30.1  18.0\n",
       "12       MX17004  2010      7   d3  28.6  17.5\n",
       "13       MX17004  2010      7  d14  29.9  16.5\n",
       "14       MX17004  2010      8  d23  26.4  15.0\n",
       "15       MX17004  2010      8   d5  29.6  15.8\n",
       "16       MX17004  2010      8  d29  28.0  15.3\n",
       "17       MX17004  2010      8  d13  29.8  16.5\n",
       "18       MX17004  2010      8  d25  29.7  15.6\n",
       "19       MX17004  2010      8  d31  25.4  15.4\n",
       "20       MX17004  2010      8   d8  29.0  17.3\n",
       "21       MX17004  2010     10   d5  27.0  14.0\n",
       "22       MX17004  2010     10  d14  29.5  13.0\n",
       "23       MX17004  2010     10  d15  28.7  10.5\n",
       "24       MX17004  2010     10  d28  31.2  15.0\n",
       "25       MX17004  2010     10   d7  28.1  12.9\n",
       "26       MX17004  2010     11   d2  31.3  16.3\n",
       "27       MX17004  2010     11   d5  26.3   7.9\n",
       "28       MX17004  2010     11  d27  27.7  14.2\n",
       "29       MX17004  2010     11  d26  28.1  12.1\n",
       "30       MX17004  2010     11   d4  27.2  12.0\n",
       "31       MX17004  2010     12   d1  29.9  13.8\n",
       "32       MX17004  2010     12   d6  27.8  10.5"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# After we melt the data, you can see that we're half of the way there.\n",
    "# We have a column that represents the day of the month, and another column that represents the temperature.\n",
    "# The next problem we have is essentially how do we undo the melt command. The opposite of the melt command is using pivot table.\n",
    "# What pivot table does, is it takes a column, and takes each individual value of that column and turns it into another column.\n",
    "# You then specify another column, here temperature, that will be used to fill in the rest of the table.\n",
    "# Here's the documentation for the pandas.pivot_table function one of the first things we need to specify is the index.\n",
    "# Again, those are the columns that will remain the same during this operation.\n",
    "# The next parameter that we need to specify is the columns, that's the column where we want to undo the melt command.\n",
    "# Lastly, we provide the values column that tells the pivot table function what are the values to fill in the cell.\n",
    "\n",
    "(weather_long.pivot_table(index=['id', 'year', 'month', 'day'],\n",
    "                         columns='element',\n",
    "                         values='temp')\n",
    ".reset_index()\n",
    ")\n",
    "#  We can now see, after we reset the index, we now get a flat rectangular data set in the shape that we want.\n",
    "# So when you have a data set where variables are stored in both rows and columns, you first have to do a melt on a subset of the columns, and then do a pivot on one of the other columns. Finally, to make sure you get a nice square or rectangular data set, you have to reset all the indices.\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.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}