002_data_organization_00_slides.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Disclaimer**: functions and methods used in this notebook are covered in the next lessons. Here it is only to generate the tables to show different versions of storing data. If you want to study these examples, come back after the next few notebooks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.19.2\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "print(pd.__version__)\n",
    "\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Organizing data\n",
    "\n",
    "Topics we will discuss in this lesson are closely related to database management. The concept of *tidy data* was introduced by Hadley Wickham (Chief scientist in **R** project) and was inspired by databases. It turns out that scientists and statisticians can benifit very much from the same concepts. In particular, structuring your data in a tidy way will facilitate any type of analysis you want to do.\n",
    "\n",
    "The core ideas in this lesson are taken from Hadley Wickham's seminal paper \"Tidy data\" [1], which IMO every person who works with complex datasets should read. Some examples in this notebook are from that paper, while others I made up myself.\n",
    "\n",
    "[1] Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1-23."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tidying: structuring datasets to facilitate analysis\n",
    "\n",
    "The principles of tidy data provide a standard way to organize data values within a dataset. Current tools often require translation: you have to spend time munging the output from one tool so you can input it into another. Tidy datasets and tools for them work hand in hand to make data analysis easier, allowing you to focus on the interesting domain problem, not on the uninteresting logistics of data.\n",
    "\n",
    "Let's start with an example.\n",
    "\n",
    "Consider the following 2 ways of presenting the same toy data. Think about whether there is any difference how we organize it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>treatment_a</th>\n",
       "      <th>treatment_b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jane Doe</th>\n",
       "      <td>16.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mary Johnson</th>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              treatment_a  treatment_b\n",
       "John Smith            NaN            2\n",
       "Jane Doe             16.0           11\n",
       "Mary Johnson          3.0            1"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "untidy = pd.DataFrame({'treatment_a':[np.nan, 16, 3],'treatment_b':[2,11,1]}, \n",
    "                      index=['John Smith', 'Jane Doe','Mary Johnson'])\n",
    "untidy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>John Smith</th>\n",
       "      <th>Jane Doe</th>\n",
       "      <th>Mary Johnson</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>treatment_a</th>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>treatment_b</th>\n",
       "      <td>2.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             John Smith  Jane Doe  Mary Johnson\n",
       "treatment_a         NaN      16.0           3.0\n",
       "treatment_b         2.0      11.0           1.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "untidy.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A dataset is a collection of values, usually either numbers (if quantitative) or strings (if\n",
    "qualitative). Values are organized in two ways. Every value belongs to a variable and an\n",
    "observation. **A variable contains all values that measure the same underlying attribute** (like\n",
    "height, temperature, duration) across units. **An observation contains all values measured on\n",
    "the same unit** (like a person, or a day, or a race) across attributes.\n",
    "\n",
    "Let's restructure the dataset in the following (*tidy*, as we will learn later) way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>person</th>\n",
       "      <th>treatment</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>a</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>a</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>b</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>b</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         person treatment  result\n",
       "0    John Smith         a     NaN\n",
       "1      Jane Doe         a    16.0\n",
       "2  Mary Johnson         a     3.0\n",
       "3    John Smith         b     2.0\n",
       "4      Jane Doe         b    11.0\n",
       "5  Mary Johnson         b     1.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "untidy.index.name = 'person'\n",
    "untidy.columns.name = 'treatment'\n",
    "tidy = pd.melt(untidy.reset_index(),id_vars=['person'],value_name='result')\n",
    "tidy['treatment'].replace({'treatment_a':'a','treatment_b':'b'}, inplace=True)\n",
    "tidy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This makes it clear that the\n",
    "dataset contains 18 values representing three variables and six observations. The variables\n",
    "are:\n",
    "1. person, with three possible values (John Smith, Mary Johnson, and Jane Doe).\n",
    "2. treatment, with two possible values (a and b).\n",
    "3. result, with five or six values depending on how you think of the missing value (—,\n",
    "16, 3, 2, 11, 1).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tidy data\n",
    "Tidy data is a standard way of mapping the meaning of a dataset to its structure. A dataset is\n",
    "messy or tidy depending on how rows, columns and tables are matched up with observations,\n",
    "variables and types. Core principles of tidy data are simple:\n",
    "1. Each **variable** forms a **column**\n",
    "2. Each **observation** forms a **row**\n",
    "3. Each **type of observational unit** forms a **table**"
   ]
  },
  {
   "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>person</th>\n",
       "      <th>treatment</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>a</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>a</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>b</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>b</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         person treatment  result\n",
       "0    John Smith         a     NaN\n",
       "1      Jane Doe         a    16.0\n",
       "2  Mary Johnson         a     3.0\n",
       "3    John Smith         b     2.0\n",
       "4      Jane Doe         b    11.0\n",
       "5  Mary Johnson         b     1.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tidy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>treatment</th>\n",
       "      <th>treatment_a</th>\n",
       "      <th>treatment_b</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>person</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jane Doe</th>\n",
       "      <td>16.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mary Johnson</th>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "treatment     treatment_a  treatment_b\n",
       "person                                \n",
       "John Smith            NaN            2\n",
       "Jane Doe             16.0           11\n",
       "Mary Johnson          3.0            1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "untidy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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",
       "      <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": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_untidy = pd.read_csv('data\\pew.csv')\n",
    "income_untidy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(18, 11)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_untidy.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here there is a `religion` column, which forms a separate variable, but all other columns actually contain the same variable -- `count`. `income` forms another (\"groupping\") variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(180, 3)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_tidy.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Evangelical Prot</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Historically Black Prot</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Jehovah's Witness</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Jewish</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Mainline Prot</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Mormon</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Muslim</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Orthodox</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Other Christian</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Other Faiths</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Other World Religions</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Unaffiliated</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Buddhist</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Catholic</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Don’t know/refused</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Evangelical Prot</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>9</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\n",
       "5          Evangelical Prot    <$10k    575\n",
       "6                     Hindu    <$10k      1\n",
       "7   Historically Black Prot    <$10k    228\n",
       "8         Jehovah's Witness    <$10k     20\n",
       "9                    Jewish    <$10k     19\n",
       "10            Mainline Prot    <$10k    289\n",
       "11                   Mormon    <$10k     29\n",
       "12                   Muslim    <$10k      6\n",
       "13                 Orthodox    <$10k     13\n",
       "14          Other Christian    <$10k      9\n",
       "15             Other Faiths    <$10k     20\n",
       "16    Other World Religions    <$10k      5\n",
       "17             Unaffiliated    <$10k    217\n",
       "18                 Agnostic  $10-20k     34\n",
       "19                  Atheist  $10-20k     27\n",
       "20                 Buddhist  $10-20k     21\n",
       "21                 Catholic  $10-20k    617\n",
       "22       Don’t know/refused  $10-20k     14\n",
       "23         Evangelical Prot  $10-20k    869\n",
       "24                    Hindu  $10-20k      9"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_tidy = pd.melt(income_untidy,id_vars=['religion'],var_name='income',value_name='count')\n",
    "income_tidy.head(25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some things I can do with untidy as well as with tidy, but what if I wanted to change to proportion for each religion to have certain income, but keep the count as well (because it tells me how precise is the measurement and is need to calculating statistics?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "income_tidy['prop_income'] = income_tidy.groupby('religion')['count'].transform(lambda x: x/x.sum()*100)"
   ]
  },
  {
   "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>religion</th>\n",
       "      <th>income</th>\n",
       "      <th>count</th>\n",
       "      <th>prop_income</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",
       "      <td>3.268765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>12</td>\n",
       "      <td>2.330097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buddhist</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>27</td>\n",
       "      <td>6.569343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Catholic</td>\n",
       "      <td>&lt;$10k</td>\n",
       "      <td>418</td>\n",
       "      <td>5.189968</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",
       "      <td>5.514706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             religion income  count  prop_income\n",
       "0            Agnostic  <$10k     27     3.268765\n",
       "1             Atheist  <$10k     12     2.330097\n",
       "2            Buddhist  <$10k     27     6.569343\n",
       "3            Catholic  <$10k    418     5.189968\n",
       "4  Don’t know/refused  <$10k     15     5.514706"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_tidy.head()"
   ]
  },
  {
   "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>religion</th>\n",
       "      <th>income</th>\n",
       "      <th>count</th>\n",
       "      <th>prop_income</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",
       "      <td>3.268765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>34</td>\n",
       "      <td>4.116223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$20-30k</td>\n",
       "      <td>60</td>\n",
       "      <td>7.263923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$30-40k</td>\n",
       "      <td>81</td>\n",
       "      <td>9.806295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$40-50k</td>\n",
       "      <td>76</td>\n",
       "      <td>9.200969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$50-75k</td>\n",
       "      <td>137</td>\n",
       "      <td>16.585956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$75-100k</td>\n",
       "      <td>122</td>\n",
       "      <td>14.769976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$100-150k</td>\n",
       "      <td>109</td>\n",
       "      <td>13.196126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>84</td>\n",
       "      <td>10.169492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>Don't know/refused</td>\n",
       "      <td>96</td>\n",
       "      <td>11.622276</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     religion              income  count  prop_income\n",
       "0    Agnostic               <$10k     27     3.268765\n",
       "18   Agnostic             $10-20k     34     4.116223\n",
       "36   Agnostic             $20-30k     60     7.263923\n",
       "54   Agnostic             $30-40k     81     9.806295\n",
       "72   Agnostic             $40-50k     76     9.200969\n",
       "90   Agnostic             $50-75k    137    16.585956\n",
       "108  Agnostic            $75-100k    122    14.769976\n",
       "126  Agnostic           $100-150k    109    13.196126\n",
       "144  Agnostic               >150k     84    10.169492\n",
       "162  Agnostic  Don't know/refused     96    11.622276"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_tidy.loc[income_tidy['religion']=='Agnostic']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>count</th>\n",
       "      <th>prop_income</th>\n",
       "      <th>prop_religion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Jewish</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>151</td>\n",
       "      <td>22.140762</td>\n",
       "      <td>5.789877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>54</td>\n",
       "      <td>21.011673</td>\n",
       "      <td>2.070552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>74</td>\n",
       "      <td>14.368932</td>\n",
       "      <td>2.837423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Buddhist</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>53</td>\n",
       "      <td>12.895377</td>\n",
       "      <td>2.032209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>Orthodox</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>46</td>\n",
       "      <td>12.672176</td>\n",
       "      <td>1.763804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>84</td>\n",
       "      <td>10.169492</td>\n",
       "      <td>3.220859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Other World Religions</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>4</td>\n",
       "      <td>9.523810</td>\n",
       "      <td>0.153374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>Other Christian</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>12</td>\n",
       "      <td>9.302326</td>\n",
       "      <td>0.460123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>Other Faiths</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>41</td>\n",
       "      <td>9.131403</td>\n",
       "      <td>1.572086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>Mainline Prot</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>634</td>\n",
       "      <td>8.487282</td>\n",
       "      <td>24.309816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Catholic</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>633</td>\n",
       "      <td>7.859449</td>\n",
       "      <td>24.271472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Mormon</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>42</td>\n",
       "      <td>7.228916</td>\n",
       "      <td>1.610429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>Unaffiliated</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>258</td>\n",
       "      <td>6.959806</td>\n",
       "      <td>9.892638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>Don’t know/refused</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>18</td>\n",
       "      <td>6.617647</td>\n",
       "      <td>0.690184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>Muslim</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>6</td>\n",
       "      <td>5.172414</td>\n",
       "      <td>0.230061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Evangelical Prot</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>414</td>\n",
       "      <td>4.370777</td>\n",
       "      <td>15.874233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>Historically Black Prot</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>78</td>\n",
       "      <td>3.909774</td>\n",
       "      <td>2.990798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>Jehovah's Witness</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>6</td>\n",
       "      <td>2.790698</td>\n",
       "      <td>0.230061</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    religion income  count  prop_income  prop_religion\n",
       "153                   Jewish  >150k    151    22.140762       5.789877\n",
       "150                    Hindu  >150k     54    21.011673       2.070552\n",
       "145                  Atheist  >150k     74    14.368932       2.837423\n",
       "146                 Buddhist  >150k     53    12.895377       2.032209\n",
       "157                 Orthodox  >150k     46    12.672176       1.763804\n",
       "144                 Agnostic  >150k     84    10.169492       3.220859\n",
       "160    Other World Religions  >150k      4     9.523810       0.153374\n",
       "158          Other Christian  >150k     12     9.302326       0.460123\n",
       "159             Other Faiths  >150k     41     9.131403       1.572086\n",
       "154            Mainline Prot  >150k    634     8.487282      24.309816\n",
       "147                 Catholic  >150k    633     7.859449      24.271472\n",
       "155                   Mormon  >150k     42     7.228916       1.610429\n",
       "161             Unaffiliated  >150k    258     6.959806       9.892638\n",
       "148       Don’t know/refused  >150k     18     6.617647       0.690184\n",
       "156                   Muslim  >150k      6     5.172414       0.230061\n",
       "149         Evangelical Prot  >150k    414     4.370777      15.874233\n",
       "151  Historically Black Prot  >150k     78     3.909774       2.990798\n",
       "152        Jehovah's Witness  >150k      6     2.790698       0.230061"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_tidy.loc[income_tidy['income']=='>150k'].sort_values('prop_income', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I can also easily add proportion of religions for each income group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "income_tidy['prop_religion'] = income_tidy.groupby('income')['count'].transform(lambda x: x/x.sum()*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>count</th>\n",
       "      <th>prop_income</th>\n",
       "      <th>prop_religion</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",
       "      <td>3.268765</td>\n",
       "      <td>1.398964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$10-20k</td>\n",
       "      <td>34</td>\n",
       "      <td>4.116223</td>\n",
       "      <td>1.222582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$20-30k</td>\n",
       "      <td>60</td>\n",
       "      <td>7.263923</td>\n",
       "      <td>1.787310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$30-40k</td>\n",
       "      <td>81</td>\n",
       "      <td>9.806295</td>\n",
       "      <td>2.453059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$40-50k</td>\n",
       "      <td>76</td>\n",
       "      <td>9.200969</td>\n",
       "      <td>2.463533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$50-75k</td>\n",
       "      <td>137</td>\n",
       "      <td>16.585956</td>\n",
       "      <td>2.642237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$75-100k</td>\n",
       "      <td>122</td>\n",
       "      <td>14.769976</td>\n",
       "      <td>3.057644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>$100-150k</td>\n",
       "      <td>109</td>\n",
       "      <td>13.196126</td>\n",
       "      <td>3.409446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>84</td>\n",
       "      <td>10.169492</td>\n",
       "      <td>3.220859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>Don't know/refused</td>\n",
       "      <td>96</td>\n",
       "      <td>11.622276</td>\n",
       "      <td>1.568371</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     religion              income  count  prop_income  prop_religion\n",
       "0    Agnostic               <$10k     27     3.268765       1.398964\n",
       "18   Agnostic             $10-20k     34     4.116223       1.222582\n",
       "36   Agnostic             $20-30k     60     7.263923       1.787310\n",
       "54   Agnostic             $30-40k     81     9.806295       2.453059\n",
       "72   Agnostic             $40-50k     76     9.200969       2.463533\n",
       "90   Agnostic             $50-75k    137    16.585956       2.642237\n",
       "108  Agnostic            $75-100k    122    14.769976       3.057644\n",
       "126  Agnostic           $100-150k    109    13.196126       3.409446\n",
       "144  Agnostic               >150k     84    10.169492       3.220859\n",
       "162  Agnostic  Don't know/refused     96    11.622276       1.568371"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_tidy.loc[income_tidy['religion']=='Agnostic']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>count</th>\n",
       "      <th>prop_income</th>\n",
       "      <th>prop_religion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>Mainline Prot</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>634</td>\n",
       "      <td>8.487282</td>\n",
       "      <td>24.309816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Catholic</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>633</td>\n",
       "      <td>7.859449</td>\n",
       "      <td>24.271472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Evangelical Prot</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>414</td>\n",
       "      <td>4.370777</td>\n",
       "      <td>15.874233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>Unaffiliated</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>258</td>\n",
       "      <td>6.959806</td>\n",
       "      <td>9.892638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Jewish</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>151</td>\n",
       "      <td>22.140762</td>\n",
       "      <td>5.789877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Agnostic</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>84</td>\n",
       "      <td>10.169492</td>\n",
       "      <td>3.220859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>Historically Black Prot</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>78</td>\n",
       "      <td>3.909774</td>\n",
       "      <td>2.990798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Atheist</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>74</td>\n",
       "      <td>14.368932</td>\n",
       "      <td>2.837423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>54</td>\n",
       "      <td>21.011673</td>\n",
       "      <td>2.070552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Buddhist</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>53</td>\n",
       "      <td>12.895377</td>\n",
       "      <td>2.032209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>Orthodox</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>46</td>\n",
       "      <td>12.672176</td>\n",
       "      <td>1.763804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Mormon</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>42</td>\n",
       "      <td>7.228916</td>\n",
       "      <td>1.610429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>Other Faiths</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>41</td>\n",
       "      <td>9.131403</td>\n",
       "      <td>1.572086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>Don’t know/refused</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>18</td>\n",
       "      <td>6.617647</td>\n",
       "      <td>0.690184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>Other Christian</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>12</td>\n",
       "      <td>9.302326</td>\n",
       "      <td>0.460123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>Jehovah's Witness</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>6</td>\n",
       "      <td>2.790698</td>\n",
       "      <td>0.230061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>Muslim</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>6</td>\n",
       "      <td>5.172414</td>\n",
       "      <td>0.230061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Other World Religions</td>\n",
       "      <td>&gt;150k</td>\n",
       "      <td>4</td>\n",
       "      <td>9.523810</td>\n",
       "      <td>0.153374</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    religion income  count  prop_income  prop_religion\n",
       "154            Mainline Prot  >150k    634     8.487282      24.309816\n",
       "147                 Catholic  >150k    633     7.859449      24.271472\n",
       "149         Evangelical Prot  >150k    414     4.370777      15.874233\n",
       "161             Unaffiliated  >150k    258     6.959806       9.892638\n",
       "153                   Jewish  >150k    151    22.140762       5.789877\n",
       "144                 Agnostic  >150k     84    10.169492       3.220859\n",
       "151  Historically Black Prot  >150k     78     3.909774       2.990798\n",
       "145                  Atheist  >150k     74    14.368932       2.837423\n",
       "150                    Hindu  >150k     54    21.011673       2.070552\n",
       "146                 Buddhist  >150k     53    12.895377       2.032209\n",
       "157                 Orthodox  >150k     46    12.672176       1.763804\n",
       "155                   Mormon  >150k     42     7.228916       1.610429\n",
       "159             Other Faiths  >150k     41     9.131403       1.572086\n",
       "148       Don’t know/refused  >150k     18     6.617647       0.690184\n",
       "158          Other Christian  >150k     12     9.302326       0.460123\n",
       "152        Jehovah's Witness  >150k      6     2.790698       0.230061\n",
       "156                   Muslim  >150k      6     5.172414       0.230061\n",
       "160    Other World Religions  >150k      4     9.523810       0.153374"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income_tidy.loc[income_tidy['income']=='>150k'].sort_values('prop_religion',ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Advantages of tidy data:\n",
    "\n",
    "1. Adding new variables is easy and straightforward. It doesn't complicate the data structure or analysis.\n",
    "\n",
    "2. When you have structurally missing data (like number of pregnancies for males), you can throw out some observations (in untidy table they will have to exist, although they make no sense)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(317, 83)"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billboard_untidy.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false,
    "scrolled": 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</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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year               artist                                  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",
       "\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  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billboard_untidy = pd.read_csv('data/billboard.csv')\n",
    "billboard_untidy[billboard_untidy.columns[:10]].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If I wanted to look at, say, all songs which reached the 1st place, and see which song did it faster, there is no easy way of doing it in this data form. However, it is very easy to do with tidy data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>entered</th>\n",
       "      <th>peaked</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>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>1</td>\n",
       "      <td>78.0</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>1</td>\n",
       "      <td>15.0</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>1</td>\n",
       "      <td>71.0</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>1</td>\n",
       "      <td>41.0</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>1</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year               artist                                  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",
       "\n",
       "  genre    entered     peaked  week  rank  \n",
       "0  Rock 2000-09-23 2000-11-18     1  78.0  \n",
       "1  Rock 2000-02-12 2000-04-08     1  15.0  \n",
       "2  Rock 1999-10-23 2000-01-29     1  71.0  \n",
       "3  Rock 2000-08-12 2000-09-16     1  41.0  \n",
       "4  Rock 2000-08-05 2000-10-14     1  57.0  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billboard_tidy = pd.melt(billboard_untidy,id_vars=['year','artist',\n",
    "                                                   'track','time','genre',\n",
    "                                                   'date.entered','date.peaked'],\n",
    "                        var_name='week',value_name='rank')\n",
    "\n",
    "def keep_num(string):\n",
    "    digits_list = [c for c in string if c.isdigit()]\n",
    "    digits_string = ''.join(digits_list)\n",
    "    number = int(digits_string)\n",
    "    return number\n",
    "\n",
    "billboard_tidy['week'].replace({s:keep_num(s) for s in billboard_tidy['week']}, inplace=True)\n",
    "billboard_tidy.rename(columns={'date.entered':'entered', \n",
    "                               'date.peaked':'peaked'}, \n",
    "                      inplace=True)\n",
    "\n",
    "billboard_tidy['entered'] = pd.to_datetime(billboard_tidy['entered'])\n",
    "billboard_tidy['peaked'] = pd.to_datetime(billboard_tidy['peaked'])\n",
    "\n",
    "billboard_tidy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(24092, 9)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billboard_tidy.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "scrolled": 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</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>entered</th>\n",
       "      <th>peaked</th>\n",
       "      <th>week</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1588</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>6</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1905</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>7</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2222</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>8</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2227</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>8</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2230</th>\n",
       "      <td>2000</td>\n",
       "      <td>Aguilera, Christina</td>\n",
       "      <td>What A Girl Wants</td>\n",
       "      <td>3:18</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-11-27</td>\n",
       "      <td>2000-01-15</td>\n",
       "      <td>8</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2536</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>9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2537</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>9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2539</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>9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2544</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>9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2547</th>\n",
       "      <td>2000</td>\n",
       "      <td>Aguilera, Christina</td>\n",
       "      <td>What A Girl Wants</td>\n",
       "      <td>3:18</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-11-27</td>\n",
       "      <td>2000-01-15</td>\n",
       "      <td>9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      year               artist                     track  time genre  \\\n",
       "1588  2000              Madonna                     Music  3:45  Rock   \n",
       "1905  2000              Madonna                     Music  3:45  Rock   \n",
       "2222  2000              Madonna                     Music  3:45  Rock   \n",
       "2227  2000                Sisqo                Incomplete  3:52  Rock   \n",
       "2230  2000  Aguilera, Christina         What A Girl Wants  3:18  Rock   \n",
       "2536  2000      Destiny's Child  Independent Women Part I  3:38  Rock   \n",
       "2537  2000              Santana              Maria, Maria  4:18  Rock   \n",
       "2539  2000              Madonna                     Music  3:45  Rock   \n",
       "2544  2000                Sisqo                Incomplete  3:52  Rock   \n",
       "2547  2000  Aguilera, Christina         What A Girl Wants  3:18  Rock   \n",
       "\n",
       "        entered     peaked  week  rank  \n",
       "1588 2000-08-12 2000-09-16     6   1.0  \n",
       "1905 2000-08-12 2000-09-16     7   1.0  \n",
       "2222 2000-08-12 2000-09-16     8   1.0  \n",
       "2227 2000-06-24 2000-08-12     8   1.0  \n",
       "2230 1999-11-27 2000-01-15     8   1.0  \n",
       "2536 2000-09-23 2000-11-18     9   1.0  \n",
       "2537 2000-02-12 2000-04-08     9   1.0  \n",
       "2539 2000-08-12 2000-09-16     9   1.0  \n",
       "2544 2000-06-24 2000-08-12     9   1.0  \n",
       "2547 1999-11-27 2000-01-15     9   1.0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billboard_tidy.loc[billboard_tidy['rank']==1].sort_values('week').head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "During tidying, each type of observational unit should be stored in its own table. This\n",
    "is closely related to the idea of database normalization, where each fact is expressed in only\n",
    "one place. If this is not done, it is possible for inconsistencies to occur.\n",
    "\n",
    "The Billboard dataset described before actually contains observations on two types of\n",
    "observational units: the song and its rank in each week. This manifests itself through the\n",
    "duplication of facts about the song: `year`, `artist`, `track`, `time`, `genre`, `entered` and `peaked` are repeated for every `song` in each `week`.\n",
    "\n",
    "But this examples is a bit complicated, so let's look at another one, which can be quite frequent in research. Afterwards we will get back to the billboard dataset to reorganize it as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# generate subjects data\n",
    "\n",
    "# number of subjects\n",
    "n = 10\n",
    "\n",
    "subjects_id = np.arange(n)\n",
    "\n",
    "# if you have module `names`, it will generate names with it;\n",
    "# otherwise, names will be just tokens 'Name Surname #'\n",
    "try:\n",
    "    import names\n",
    "    subjects_names = []\n",
    "    subjects_gender = []\n",
    "    for g in ['male','female']:\n",
    "        for i in np.arange(n/2):\n",
    "            subjects_names.append(names.get_full_name(gender=g))\n",
    "            subjects_gender.append(g)\n",
    "except:\n",
    "    subjects_names = ['Name Surname {}'.format(i+1) for i in range(n)]\n",
    "    subjects_gender = ['male','female']*int(n/2)\n",
    "\n",
    "subjects_age = np.random.randint(15, 85, size=n)\n",
    "\n",
    "task_names = ['memory_matrix', 'labyrinth', 'math_game', 'names_memory', 'survey_score', \n",
    "              'session_1', 'session_2', 'session_3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# set subjects data into dataframe\n",
    "df = pd.DataFrame({'id': subjects_id, 'name': subjects_names, 'gender': subjects_gender, 'age': subjects_age})\n",
    "\n",
    "# generate scores for each task\n",
    "for subj_id in df.index:\n",
    "    for task in task_names:\n",
    "        df.at[subj_id, task] = np.random.randint(100-df.at[subj_id, 'age'], 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>memory_matrix</th>\n",
       "      <th>labyrinth</th>\n",
       "      <th>math_game</th>\n",
       "      <th>names_memory</th>\n",
       "      <th>survey_score</th>\n",
       "      <th>session_1</th>\n",
       "      <th>session_2</th>\n",
       "      <th>session_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>68</td>\n",
       "      <td>male</td>\n",
       "      <td>0</td>\n",
       "      <td>Ross Richard</td>\n",
       "      <td>35.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>Jesus Thompson</td>\n",
       "      <td>74.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>Jonathan Corkill</td>\n",
       "      <td>63.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>Ray Sarwinski</td>\n",
       "      <td>86.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>83</td>\n",
       "      <td>male</td>\n",
       "      <td>4</td>\n",
       "      <td>Jonathan Wingfield</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>56</td>\n",
       "      <td>female</td>\n",
       "      <td>5</td>\n",
       "      <td>Louise Viernes</td>\n",
       "      <td>84.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>66</td>\n",
       "      <td>female</td>\n",
       "      <td>6</td>\n",
       "      <td>Bonnie Shae</td>\n",
       "      <td>44.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>48</td>\n",
       "      <td>female</td>\n",
       "      <td>7</td>\n",
       "      <td>Rose Neal</td>\n",
       "      <td>87.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>50</td>\n",
       "      <td>female</td>\n",
       "      <td>8</td>\n",
       "      <td>Alex Campbell</td>\n",
       "      <td>85.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>Josie Vance</td>\n",
       "      <td>90.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age  gender  id                name  memory_matrix  labyrinth  math_game  \\\n",
       "0   68    male   0        Ross Richard           35.0       35.0       58.0   \n",
       "1   33    male   1      Jesus Thompson           74.0       73.0       76.0   \n",
       "2   47    male   2    Jonathan Corkill           63.0       89.0       58.0   \n",
       "3   39    male   3       Ray Sarwinski           86.0       83.0       80.0   \n",
       "4   83    male   4  Jonathan Wingfield           72.0       70.0       45.0   \n",
       "5   56  female   5      Louise Viernes           84.0       44.0       59.0   \n",
       "6   66  female   6         Bonnie Shae           44.0       61.0       94.0   \n",
       "7   48  female   7           Rose Neal           87.0       71.0       69.0   \n",
       "8   50  female   8       Alex Campbell           85.0       87.0       98.0   \n",
       "9   28  female   9         Josie Vance           90.0       84.0       75.0   \n",
       "\n",
       "   names_memory  survey_score  session_1  session_2  session_3  \n",
       "0          71.0          38.0       46.0       81.0       55.0  \n",
       "1          96.0          68.0       96.0       71.0       68.0  \n",
       "2          54.0          90.0       91.0       73.0       97.0  \n",
       "3          67.0          74.0       88.0       77.0       82.0  \n",
       "4          30.0          29.0       58.0       31.0       64.0  \n",
       "5          59.0          94.0       68.0       55.0       56.0  \n",
       "6          70.0          64.0       81.0       69.0       99.0  \n",
       "7          98.0          74.0       88.0       88.0       68.0  \n",
       "8          57.0          92.0       54.0       55.0       88.0  \n",
       "9          91.0          76.0       90.0       89.0       94.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>name</th>\n",
       "      <th>id</th>\n",
       "      <th>task</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>0</td>\n",
       "      <td>memory_matrix</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>0</td>\n",
       "      <td>session_1</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>0</td>\n",
       "      <td>survey_score</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>0</td>\n",
       "      <td>math_game</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>0</td>\n",
       "      <td>session_3</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>0</td>\n",
       "      <td>names_memory</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>0</td>\n",
       "      <td>session_2</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>0</td>\n",
       "      <td>labyrinth</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>1</td>\n",
       "      <td>session_3</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>1</td>\n",
       "      <td>math_game</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>1</td>\n",
       "      <td>session_1</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>1</td>\n",
       "      <td>session_2</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>1</td>\n",
       "      <td>names_memory</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>1</td>\n",
       "      <td>memory_matrix</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>1</td>\n",
       "      <td>labyrinth</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>1</td>\n",
       "      <td>survey_score</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>2</td>\n",
       "      <td>session_1</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>2</td>\n",
       "      <td>session_2</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>2</td>\n",
       "      <td>labyrinth</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>2</td>\n",
       "      <td>memory_matrix</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>2</td>\n",
       "      <td>names_memory</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>2</td>\n",
       "      <td>math_game</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>2</td>\n",
       "      <td>survey_score</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>2</td>\n",
       "      <td>session_3</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>3</td>\n",
       "      <td>session_2</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>3</td>\n",
       "      <td>names_memory</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>3</td>\n",
       "      <td>survey_score</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>3</td>\n",
       "      <td>math_game</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>3</td>\n",
       "      <td>labyrinth</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>3</td>\n",
       "      <td>memory_matrix</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>3</td>\n",
       "      <td>session_1</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>3</td>\n",
       "      <td>session_3</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age  gender             name  id           task  score\n",
       "0    58    male   Steven Frazier   0  memory_matrix   83.0\n",
       "20   58    male   Steven Frazier   0      session_1   43.0\n",
       "16   58    male   Steven Frazier   0   survey_score   60.0\n",
       "8    58    male   Steven Frazier   0      math_game   60.0\n",
       "28   58    male   Steven Frazier   0      session_3   74.0\n",
       "12   58    male   Steven Frazier   0   names_memory   83.0\n",
       "24   58    male   Steven Frazier   0      session_2   63.0\n",
       "4    58    male   Steven Frazier   0      labyrinth   79.0\n",
       "29   42    male    Timothy Taber   1      session_3   65.0\n",
       "9    42    male    Timothy Taber   1      math_game   62.0\n",
       "21   42    male    Timothy Taber   1      session_1   95.0\n",
       "25   42    male    Timothy Taber   1      session_2   76.0\n",
       "13   42    male    Timothy Taber   1   names_memory   73.0\n",
       "1    42    male    Timothy Taber   1  memory_matrix   58.0\n",
       "5    42    male    Timothy Taber   1      labyrinth   76.0\n",
       "17   42    male    Timothy Taber   1   survey_score   82.0\n",
       "22   24  female  Michelle Burton   2      session_1   86.0\n",
       "26   24  female  Michelle Burton   2      session_2   86.0\n",
       "6    24  female  Michelle Burton   2      labyrinth   91.0\n",
       "2    24  female  Michelle Burton   2  memory_matrix   91.0\n",
       "14   24  female  Michelle Burton   2   names_memory   98.0\n",
       "10   24  female  Michelle Burton   2      math_game   92.0\n",
       "18   24  female  Michelle Burton   2   survey_score   86.0\n",
       "30   24  female  Michelle Burton   2      session_3   91.0\n",
       "27   21  female  Harriet Coulter   3      session_2   90.0\n",
       "15   21  female  Harriet Coulter   3   names_memory   83.0\n",
       "19   21  female  Harriet Coulter   3   survey_score   79.0\n",
       "11   21  female  Harriet Coulter   3      math_game   90.0\n",
       "7    21  female  Harriet Coulter   3      labyrinth   82.0\n",
       "3    21  female  Harriet Coulter   3  memory_matrix   79.0\n",
       "23   21  female  Harriet Coulter   3      session_1   98.0\n",
       "31   21  female  Harriet Coulter   3      session_3   83.0"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_1 = pd.melt(df, id_vars=['age','gender','name','id'],var_name='task',value_name='score')\n",
    "df_tidy_1.sort_values('id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "task\n",
       "labyrinth        76.833333\n",
       "math_game        75.833333\n",
       "memory_matrix    83.000000\n",
       "names_memory     78.666667\n",
       "session_1        80.833333\n",
       "session_2        76.333333\n",
       "session_3        85.666667\n",
       "survey_score     79.333333\n",
       "Name: score, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_1.groupby('task')['score'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gender  task         \n",
       "female  labyrinth        70.333333\n",
       "        math_game        77.000000\n",
       "        memory_matrix    72.000000\n",
       "        names_memory     76.333333\n",
       "        session_1        73.000000\n",
       "        session_2        78.666667\n",
       "        session_3        81.666667\n",
       "        survey_score     82.333333\n",
       "male    labyrinth        83.333333\n",
       "        math_game        74.666667\n",
       "        memory_matrix    94.000000\n",
       "        names_memory     81.000000\n",
       "        session_1        88.666667\n",
       "        session_2        74.000000\n",
       "        session_3        89.666667\n",
       "        survey_score     76.333333\n",
       "Name: score, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_1.groupby(['gender','task'])['score'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age\n",
       "24    87.000\n",
       "27    84.750\n",
       "32    88.250\n",
       "38    74.375\n",
       "53    70.125\n",
       "76    72.875\n",
       "Name: score, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_1.groupby('age')['score'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x20d04cb7160>"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEKCAYAAAAVaT4rAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAFVBJREFUeJzt3X+MXeWd3/H3JzZsBi+NCZlSbJICWtZbShLTjCgJ2bT8\nCMYoCySVUtPNKsluZa0UQhepRFgrrdJK7aI10abSZlM5USqkNiSE2l608WJI0h9KpBDGOMEmZMKP\nYMKYwKRbsylMi+18+8ecWQYz47nXM+MZ+3m/pNHc89zznPk+F/OZM8899zypKiRJ7XjDYhcgSTq+\nDH5JaozBL0mNMfglqTEGvyQ1xuCXpMYY/JLUGINfkhpj8EtSY5YvdgHTectb3lLnnnvuYpchSSeM\nXbt2/byqBnvZd0kG/7nnnsvw8PBilyFJJ4wk+3rd16keSWqMwS9JjTH4JakxBr8kNcbgl6TGLMmr\nek4G23ePsnnnCPsPjLNq5QC3rlvDDRevXuyyJMngXwjbd4+yaesexg8eBmD0wDibtu4BMPwlLTqn\nehbA5p0jfxv6k8YPHmbzzpFFqkiSXmXwL4D9B8b7apek48ngXwCrVg701S5Jx5PBvwBuXbeGgVOW\nvaZt4JRl3LpuzSJVJEmv8s3dBTD5Bq5X9Uhaigz+BXLDxasNeklLUk/Bn+QW4F8CBewBPg7cCUzO\nXawEDlTV2mn6Pg38AjgMHKqqobmXLUk6VrMGf5LVwM3AhVU1nuRuYENV/fMp+3wGePEoh7m8qn4+\n52olSXPW61TPcmAgyUHgNGD/5BNJAnwYuGL+y5MkzbdZr+qpqlHgDuAZ4Dngxaq6f8ouvwk8X1WP\nz3QI4BtJdiXZONPPSbIxyXCS4bGxsd5HIEnqy6zBn+QM4HrgPGAVsCLJR6bsciNw11EO8d5u7n89\n8Ikk75tup6raUlVDVTU0ONjT6mGSpGPQy3X8VwE/qaqxqjoIbAXeA5BkOfAh4Kszde7+YqCqXgC2\nAZfMtWhJ0rHrJfifAS5Nclo3n38l8Fj33FXAj6rq2ek6JlmR5PTJx8DVwN7ZfuCe0Re57PZvsX33\naC9jkCT1oZc5/geBe4CHmbiU8w3Alu7pDRwxzZNkVZId3eZZwLeT/AD4HvD1qrqvl8Im72hp+EvS\n/EpVLXYNr/MrZ19QZ3/0swCsXjnAd27zgiFJOpoku3r9nNSSv1ePd7SUpPm15IPfO1pK0vxa0sHv\nHS0laf4t2Zu0rfaOlpK0IJZk8L999Zt8Q1eSFsiSnuqRJM0/g1+SGmPwS1JjDH5JaozBL0mNMfgl\nqTEGvyQ1xuCXpMYY/JLUGINfkhpj8EtSYwx+SWqMwS9Jjekp+JPckuTRJHuT3JXkjUk+nWQ0yfe7\nr2tn6HtNkpEkTyS5bX7LlyT1a9bgT7IauBkYqqqLgGVMLLIO8KdVtbb72jFN32XA54D1wIXAjUku\nnLfqJUl963WqZzkwkGQ5cBqwv8d+lwBPVNVTVfUK8BXg+v7LlCTNl1mDv6pGgTuAZ4DngBer6v7u\n6U8meSTJl5KcMU331cBPp2w/27UtuO27R7ns9m9x3m1f57Lbv8X23aPH48dK0pLXy1TPGUycpZ8H\nrAJWJPkI8HngfGAtE78QPjOXQpJsTDKcZHhsbGwuh2L77lE2bd3D6IFxChg9MM6mrXsMf0mit6me\nq4CfVNVYVR0EtgLvqarnq+pwVf0S+AIT0zpHGgXeOmX7nK7tdapqS1UNVdXQ4OBgf6M4wuadI4wf\nPPyatvGDh9m8c2ROx5Wkk0Evwf8McGmS05IEuBJ4LMnZU/b5ILB3mr4PARckOS/JqUy8KXzvXIue\nzf4D4321S1JLepnjfxC4B3gY2NP12QL8SZI9SR4BLgduAUiyKsmOru8h4CZgJ/AYcHdVPboQA5lq\n1cqBvtolqSWpqsWu4XWGhoZqeHj4mPtPzvFPne4ZOGUZf/yht3PDxcflvWVJOq6S7KqqoV72Xb7Q\nxSyGyXDfvHOE/QfGWbVygFvXrTH0JYmTNPhhIvwNekl6Pe/VI0mNMfglqTEGvyQ1xuCXpMYY/JLU\nGINfkhpj8EtSYwx+SWqMwS9JjTH4JakxBr8kNcbgl6TGGPyS1BiDX5IaY/BLUmMMfklqTE/Bn+SW\nJI8m2ZvkriRvTLI5yY+SPJJkW5KVM/R9ulub9/tJjn09RUnSvJg1+JOsBm4GhqrqImAZsAF4ALio\nqt4B/BjYdJTDXF5Va3tdD1KStHB6nepZDgwkWQ6cBuyvqvur6lD3/HeBcxaiQEnS/Jo1+KtqFLgD\neAZ4Dnixqu4/YrffBf5qpkMA30iyK8nGuRQrSZq7XqZ6zgCuB84DVgErknxkyvN/CBwC/ssMh3hv\nVa0F1gOfSPK+GX7OxiTDSYbHxsb6HIYkqVe9TPVcBfykqsaq6iCwFXgPQJKPAR8AfruqarrO3V8M\nVNULwDbgkhn221JVQ1U1NDg42PdAJEm96SX4nwEuTXJakgBXAo8luQb4FHBdVb08XcckK5KcPvkY\nuBrYOz+lS5KOxfLZdqiqB5PcAzzMxJTObmAL8CjwK8ADE78P+G5V/X6SVcAXq+pa4CxgW/f8cuDL\nVXXfgoxEktSTzDBDs6iGhoZqeNhL/iWpV0l29XrJvJ/claTGGPyS1BiDX5IaY/BLUmMMfklqjMEv\nSY0x+CWpMQa/JDXG4Jekxhj8ktQYg1+SGmPwS1JjDH5JaozBL0mNMfglqTEGvyQ1xuCXpMYY/JLU\nGINfkhrTU/AnuSXJo0n2JrkryRuTvDnJA0ke776fMUPfa5KMJHkiyW3zW74kqV+zBn+S1cDNwFBV\nXQQsAzYAtwHfrKoLgG9220f2XQZ8DlgPXAjcmOTC+StfktSvXqd6lgMDSZYDpwH7geuBO7vn7wRu\nmKbfJcATVfVUVb0CfKXrJ0laJLMGf1WNAncAzwDPAS9W1f3AWVX1XLfbz4Czpum+GvjplO1nu7bX\nSbIxyXCS4bGxsT6GIEnqRy9TPWcwcZZ+HrAKWJHkI1P3qaoCai6FVNWWqhqqqqHBwcG5HEqSdBS9\nTPVcBfykqsaq6iCwFXgP8HySswG67y9M03cUeOuU7XO6NknSIukl+J8BLk1yWpIAVwKPAfcCH+32\n+SjwF9P0fQi4IMl5SU5l4k3he+detiTpWC2fbYeqejDJPcDDwCFgN7AF+FXg7iS/B+wDPgyQZBXw\nxaq6tqoOJbkJ2MnE1UBfqqpHF2YokqReZGJ6fmkZGhqq4eHhxS5Dkk4YSXZV1VAv+/rJXUlqjMEv\nSY0x+CWpMQa/JDXG4Jekxhj8ktQYg1+SGmPwS1JjDH5JaozBL0mNMfglqTEGvyQ1xuCXpMYY/JLU\nGINfkhpj8EtSYwx+SWqMwS9JjZl1zd0ka4CvTmk6H/gj4N3Amq5tJXCgqtZO0/9p4BfAYeBQr0uD\nSZIWRi+LrY8AawGSLANGgW1V9dnJfZJ8BnjxKIe5vKp+PsdaJUnzYNbgP8KVwJNVtW+yIUmADwNX\nzGdhkqSF0e8c/wbgriPafhN4vqoen6FPAd9IsivJxpkOnGRjkuEkw2NjY32WJUnqVc/Bn+RU4Drg\na0c8dSOv/2Uw1Xu7uf/1wCeSvG+6napqS1UNVdXQ4OBgr2VJkvrUzxn/euDhqnp+siHJcuBDvPbN\n39eoqtHu+wvANuCSYytVkjQf+gn+6c7srwJ+VFXPTtchyYokp08+Bq4G9h5LoZKk+dFT8Heh/X5g\n6xFPvW7OP8mqJDu6zbOAbyf5AfA94OtVdd/cSpYkzUVPV/VU1UvAmdO0f2yatv3Atd3jp4B3zq1E\nSdJ88pO7ktQYg1+SGmPwS1JjDH5JaozBL0mNMfglqTEGvyQ1xuCXpMYY/JLUGINfkhpj8EtSYwx+\nSWqMwS9JjTH4JakxBr8kNcbgl6TGGPyS1BiDX5IaM2vwJ1mT5PtTvv4myR8k+XSS0Snt187Q/5ok\nI0meSHLb/A9BktSPWdfcraoRYC1AkmXAKLAN+Djwp1V1x0x9u/0/x8RC7c8CDyW5t6p+OA+1S5KO\nQb9TPVcCT1bVvh73vwR4oqqeqqpXgK8A1/f5MyVJ86jf4N8A3DVl+5NJHknypSRnTLP/auCnU7af\n7dokSYuk5+BPcipwHfC1runzwPlMTAM9B3xmLoUk2ZhkOMnw2NjYXA4lSTqKfs741wMPV9XzAFX1\nfFUdrqpfAl9gYlrnSKPAW6dsn9O1vU5VbamqoaoaGhwc7KMsSVI/+gn+G5kyzZPk7CnPfRDYO02f\nh4ALkpzX/cWwAbj3WAqVJM2PnoI/yQomrszZOqX5T5LsSfIIcDlwS7fvqiQ7AKrqEHATsBN4DLi7\nqh6dx/olSX2a9XJOgKp6CTjziLbfmWHf/cC1U7Z3ADvmUKPm2fbdo2zeOcL+A+OsWjnArevWcMPF\nvucutaKn4NfJY/vuUTZt3cP4wcMAjB4YZ9PWPQCGv9QIb9nQmM07R/429CeNHzzM5p0ji1SRpOPN\n4G/M/gPjfbVLOvkY/I1ZtXKgr3ZJJx+DvzG3rlvDwCnLXtM2cMoybl23ZpEqknS8+eZuYybfwPWq\nHqldBn+Dbrh4tUEvNcypHklqjMEvSY0x+CWpMQa/JDXG4Jekxhj8ktQYg1+SGmPwS1Jj/ACXJJ3A\nJtfXOPXv/dq7eu1j8EvSCerI9TV65VSPJJ2gpltfoxeznvEnWQN8dUrT+cAfAauB3wJeAZ4EPl5V\nB6bp/zTwC+AwcKiqhvquUpL0Ose6jsasZ/xVNVJVa6tqLfAu4GVgG/AAcFFVvQP4MbDpKIe5vDuG\noS9J8+RY19Hod6rnSuDJqtpXVfdX1aGu/bvAOcdUgSTpmEy3vkYv+g3+DcBd07T/LvBXM/Qp4BtJ\ndiXZ2OfPkyTN4IaLV/PHH3o7q/s8809V9bZjciqwH/iHVfX8lPY/BIaAD9U0B0uyuqpGk/xdJqaH\nPllV/3Oa/TYCGwHe9ra3vWvfvn19DUSSWpZkV6/T6f2c8a8HHj4i9D8GfAD47elCH6CqRrvvLzDx\n3sAlM+y3paqGqmpocHCwj7IkSf3oJ/hvZMo0T5JrgE8B11XVy9N1SLIiyemTj4Grgb3HXq4kaa56\nCv4utN8PbJ3S/GfA6cADSb6f5D92+65KsqPb5yzg20l+AHwP+HpV3Tdv1UuS+tbTJ3er6iXgzCPa\nfm2GffcD13aPnwLeOccaJUnzyE/uSlJjDH5JaozBL0mNMfglqTEGvyQ1xuCXpMYY/JLUGINfkhpj\n8EtSYwx+SWqMwS9JjTH4JakxBr8kNcbgl6TGGPyS1BiDX5IaY/BLUmMMfklqTE9LL0qaP9t3j7J5\n5wj7D4yzauUAt65bww0Xr17sstSQWc/4k6zpFlOf/PqbJH+Q5M1JHkjyePf9jBn6X5NkJMkTSW6b\n/yFIJ47tu0fZtHUPowfGKWD0wDibtu5h++7RxS5NDZk1+KtqpKrWVtVa4F3Ay8A24Dbgm1V1AfDN\nbvs1kiwDPgesBy4Ebkxy4TzWL51QNu8cYfzg4de0jR88zOadI4tUkVrU7xz/lcCTVbUPuB64s2u/\nE7hhmv0vAZ6oqqeq6hXgK10/qUn7D4z31S4thH6DfwNwV/f4rKp6rnv8M+CsafZfDfx0yvazXdvr\nJNmYZDjJ8NjYWJ9lSSeGVSsH+mqXFkLPwZ/kVOA64GtHPldVBdRcCqmqLVU1VFVDg4ODczmUtGTd\num4NA6cse03bwCnLuHXdmkWqSC3q56qe9cDDVfV8t/18krOr6rkkZwMvTNNnFHjrlO1zujapSZNX\n73hVjxZTP8F/I69O8wDcC3wUuL37/hfT9HkIuCDJeUwE/gbgXxxbqdLJ4YaLVxv0WlQ9TfUkWQG8\nH9g6pfl24P1JHgeu6rZJsirJDoCqOgTcBOwEHgPurqpH5698SVK/ejrjr6qXgDOPaPtfTFzlc+S+\n+4Frp2zvAHbMrUxJ0nzxlg2S1BiDX5IaY/BLUmMycQn+0pJkDNi32HVM4y3Azxe7iEXW+mvg+Nse\nPyzd1+DvV1VPH4JaksG/VCUZrqqhxa5jMbX+Gjj+tscPJ8dr4FSPJDXG4Jekxhj8/dmy2AUsAa2/\nBo5fJ/xr4By/JDXGM35JaozBP4Mkb0zyvSQ/SPJokn/Ttfe05OTJIsmyJLuT/GW33cz4kzydZE+3\n5Ohw19bM+AGSrExyT5IfJXksybtbeQ3muuzsUmbwz+z/AVdU1TuBtcA1SS6lhyUnTzL/iokb7E1q\nbfyXd0uPTl6+19r4/wNwX1X9BvBOJv4tNPEazGXZ2SWvqvya5Qs4DXgY+MfACHB21342MLLY9S3g\nuM9h4h/2FcBfdm0tjf9p4C1HtLU0/jcBP6F7L7DF12DKmK8GvnOyjN8z/qPopjm+z8QiMw9U1YP0\ntuTkyeKzwKeAX05pa2n8BXwjya4kG7u2lsZ/HjAG/Kduuu+L3S3aW3oNJvW77OySZvAfRVUdrok/\n884BLkly0RHPz3nJyaUqyQeAF6pq10z7nMzj77y3+++/HvhEkvdNfbKB8S8H/hHw+aq6GHiJI6Y1\nGngNFnzZ2cVg8Pegqg4A/w24hm7JSYCjLDl5MrgMuC7J08BXgCuS/GfaGT9VNdp9f4GJud1LaGj8\nwLPAs91fugD3MPGLoKXXAGZYdhZO3PEb/DNIMphkZfd4gIkVyH7Eq0tOwsxLTp7wqmpTVZ1TVecy\n8Wfut6rqIzQy/iQrkpw++ZiJOd69NDJ+gKr6GfDTJJMrwV8J/JCGXoPOTMvOwgk6fj/ANYMk7wDu\nBJYx8Qvy7qr6t0nOBO4G3sbEHUQ/XFV/vXiVLrwk/xT411X1gVbGn+R8Js7yYWLK48tV9e9aGf+k\nJGuBLwKnAk8BH6f7/4EGXoPul/4zwPlV9WLXdsL/GzD4JakxTvVIUmMMfklqjMEvSY0x+CWpMQa/\nJDXG4Jekxhj8ktQYg1+aIsn27qZsj07emC3J7yX5cbc+wxeS/FnXPpjkvyZ5qPu6bHGrl3rjB7ik\nKZK8uar+urtNx0PAOuA7TNyj5hfAt4AfVNVNSb4M/HlVfTvJ24CdVfUPFq14qUfLF7sAaYm5OckH\nu8dvBX4H+B+TH8lP8jXg17vnrwIuTDLZ9+8k+dWq+j/Hs2CpXwa/1OnuSXQV8O6qejnJf2fixnwz\nncW/Abi0qv7v8alQmh/O8UuvehPwv7vQ/w3gUmAF8E+SnJFkOfDPpux/P/DJyY3uhmbSkmfwS6+6\nD1ie5DHgduC7wCjw74HvMTHX/zTwYrf/zcBQkkeS/BD4/eNesXQMfHNXmsXkvH13xr8N+FJVbZut\nn7RUecYvze7T3drLe5lYfHz7ItcjzYln/JLUGM/4JakxBr8kNcbgl6TGGPyS1BiDX5IaY/BLUmP+\nP9Lr5CmLlr3hAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x20d044a82e8>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%matplotlib inline\n",
    "df_tidy_1.groupby('age')['score'].mean().plot(marker='o',linestyle='')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For some applications this is fine. However, if I am interested in the sessions' scores, it is somewhat tedious to work with them. In a way, score on every task is actually a separate variable, because they are not directly comparable. Except for sessions' scores -- they are on the same task and they are directly comparable. How to organize this then? We could treat other scores, except for sessions, as separate variables, and keep scores for sessions and session # as another 2 variables, like so:"
   ]
  },
  {
   "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>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>name</th>\n",
       "      <th>id</th>\n",
       "      <th>memory_matrix</th>\n",
       "      <th>labyrinth</th>\n",
       "      <th>math_game</th>\n",
       "      <th>names_memory</th>\n",
       "      <th>survey_score</th>\n",
       "      <th>session</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>68</td>\n",
       "      <td>male</td>\n",
       "      <td>Ross Richard</td>\n",
       "      <td>0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>68</td>\n",
       "      <td>male</td>\n",
       "      <td>Ross Richard</td>\n",
       "      <td>0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>3</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>68</td>\n",
       "      <td>male</td>\n",
       "      <td>Ross Richard</td>\n",
       "      <td>0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>2</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>male</td>\n",
       "      <td>Jesus Thompson</td>\n",
       "      <td>1</td>\n",
       "      <td>74.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>33</td>\n",
       "      <td>male</td>\n",
       "      <td>Jesus Thompson</td>\n",
       "      <td>1</td>\n",
       "      <td>74.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>3</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>33</td>\n",
       "      <td>male</td>\n",
       "      <td>Jesus Thompson</td>\n",
       "      <td>1</td>\n",
       "      <td>74.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>2</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47</td>\n",
       "      <td>male</td>\n",
       "      <td>Jonathan Corkill</td>\n",
       "      <td>2</td>\n",
       "      <td>63.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>47</td>\n",
       "      <td>male</td>\n",
       "      <td>Jonathan Corkill</td>\n",
       "      <td>2</td>\n",
       "      <td>63.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3</td>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>47</td>\n",
       "      <td>male</td>\n",
       "      <td>Jonathan Corkill</td>\n",
       "      <td>2</td>\n",
       "      <td>63.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39</td>\n",
       "      <td>male</td>\n",
       "      <td>Ray Sarwinski</td>\n",
       "      <td>3</td>\n",
       "      <td>86.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>39</td>\n",
       "      <td>male</td>\n",
       "      <td>Ray Sarwinski</td>\n",
       "      <td>3</td>\n",
       "      <td>86.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>3</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>39</td>\n",
       "      <td>male</td>\n",
       "      <td>Ray Sarwinski</td>\n",
       "      <td>3</td>\n",
       "      <td>86.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>2</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>83</td>\n",
       "      <td>male</td>\n",
       "      <td>Jonathan Wingfield</td>\n",
       "      <td>4</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>83</td>\n",
       "      <td>male</td>\n",
       "      <td>Jonathan Wingfield</td>\n",
       "      <td>4</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>83</td>\n",
       "      <td>male</td>\n",
       "      <td>Jonathan Wingfield</td>\n",
       "      <td>4</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>56</td>\n",
       "      <td>female</td>\n",
       "      <td>Louise Viernes</td>\n",
       "      <td>5</td>\n",
       "      <td>84.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>2</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>56</td>\n",
       "      <td>female</td>\n",
       "      <td>Louise Viernes</td>\n",
       "      <td>5</td>\n",
       "      <td>84.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>1</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>56</td>\n",
       "      <td>female</td>\n",
       "      <td>Louise Viernes</td>\n",
       "      <td>5</td>\n",
       "      <td>84.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>3</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>66</td>\n",
       "      <td>female</td>\n",
       "      <td>Bonnie Shae</td>\n",
       "      <td>6</td>\n",
       "      <td>44.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>2</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>66</td>\n",
       "      <td>female</td>\n",
       "      <td>Bonnie Shae</td>\n",
       "      <td>6</td>\n",
       "      <td>44.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>1</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>66</td>\n",
       "      <td>female</td>\n",
       "      <td>Bonnie Shae</td>\n",
       "      <td>6</td>\n",
       "      <td>44.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>3</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>48</td>\n",
       "      <td>female</td>\n",
       "      <td>Rose Neal</td>\n",
       "      <td>7</td>\n",
       "      <td>87.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>2</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>48</td>\n",
       "      <td>female</td>\n",
       "      <td>Rose Neal</td>\n",
       "      <td>7</td>\n",
       "      <td>87.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>48</td>\n",
       "      <td>female</td>\n",
       "      <td>Rose Neal</td>\n",
       "      <td>7</td>\n",
       "      <td>87.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>3</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>50</td>\n",
       "      <td>female</td>\n",
       "      <td>Alex Campbell</td>\n",
       "      <td>8</td>\n",
       "      <td>85.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>3</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>50</td>\n",
       "      <td>female</td>\n",
       "      <td>Alex Campbell</td>\n",
       "      <td>8</td>\n",
       "      <td>85.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>2</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>50</td>\n",
       "      <td>female</td>\n",
       "      <td>Alex Campbell</td>\n",
       "      <td>8</td>\n",
       "      <td>85.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>1</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>Josie Vance</td>\n",
       "      <td>9</td>\n",
       "      <td>90.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>2</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>Josie Vance</td>\n",
       "      <td>9</td>\n",
       "      <td>90.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>Josie Vance</td>\n",
       "      <td>9</td>\n",
       "      <td>90.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age  gender                name  id  memory_matrix  labyrinth  math_game  \\\n",
       "0    68    male        Ross Richard   0           35.0       35.0       58.0   \n",
       "20   68    male        Ross Richard   0           35.0       35.0       58.0   \n",
       "10   68    male        Ross Richard   0           35.0       35.0       58.0   \n",
       "1    33    male      Jesus Thompson   1           74.0       73.0       76.0   \n",
       "21   33    male      Jesus Thompson   1           74.0       73.0       76.0   \n",
       "11   33    male      Jesus Thompson   1           74.0       73.0       76.0   \n",
       "2    47    male    Jonathan Corkill   2           63.0       89.0       58.0   \n",
       "22   47    male    Jonathan Corkill   2           63.0       89.0       58.0   \n",
       "12   47    male    Jonathan Corkill   2           63.0       89.0       58.0   \n",
       "3    39    male       Ray Sarwinski   3           86.0       83.0       80.0   \n",
       "23   39    male       Ray Sarwinski   3           86.0       83.0       80.0   \n",
       "13   39    male       Ray Sarwinski   3           86.0       83.0       80.0   \n",
       "24   83    male  Jonathan Wingfield   4           72.0       70.0       45.0   \n",
       "14   83    male  Jonathan Wingfield   4           72.0       70.0       45.0   \n",
       "4    83    male  Jonathan Wingfield   4           72.0       70.0       45.0   \n",
       "15   56  female      Louise Viernes   5           84.0       44.0       59.0   \n",
       "5    56  female      Louise Viernes   5           84.0       44.0       59.0   \n",
       "25   56  female      Louise Viernes   5           84.0       44.0       59.0   \n",
       "16   66  female         Bonnie Shae   6           44.0       61.0       94.0   \n",
       "6    66  female         Bonnie Shae   6           44.0       61.0       94.0   \n",
       "26   66  female         Bonnie Shae   6           44.0       61.0       94.0   \n",
       "17   48  female           Rose Neal   7           87.0       71.0       69.0   \n",
       "7    48  female           Rose Neal   7           87.0       71.0       69.0   \n",
       "27   48  female           Rose Neal   7           87.0       71.0       69.0   \n",
       "28   50  female       Alex Campbell   8           85.0       87.0       98.0   \n",
       "18   50  female       Alex Campbell   8           85.0       87.0       98.0   \n",
       "8    50  female       Alex Campbell   8           85.0       87.0       98.0   \n",
       "19   28  female         Josie Vance   9           90.0       84.0       75.0   \n",
       "9    28  female         Josie Vance   9           90.0       84.0       75.0   \n",
       "29   28  female         Josie Vance   9           90.0       84.0       75.0   \n",
       "\n",
       "    names_memory  survey_score  session  score  \n",
       "0           71.0          38.0        1   46.0  \n",
       "20          71.0          38.0        3   55.0  \n",
       "10          71.0          38.0        2   81.0  \n",
       "1           96.0          68.0        1   96.0  \n",
       "21          96.0          68.0        3   68.0  \n",
       "11          96.0          68.0        2   71.0  \n",
       "2           54.0          90.0        1   91.0  \n",
       "22          54.0          90.0        3   97.0  \n",
       "12          54.0          90.0        2   73.0  \n",
       "3           67.0          74.0        1   88.0  \n",
       "23          67.0          74.0        3   82.0  \n",
       "13          67.0          74.0        2   77.0  \n",
       "24          30.0          29.0        3   64.0  \n",
       "14          30.0          29.0        2   31.0  \n",
       "4           30.0          29.0        1   58.0  \n",
       "15          59.0          94.0        2   55.0  \n",
       "5           59.0          94.0        1   68.0  \n",
       "25          59.0          94.0        3   56.0  \n",
       "16          70.0          64.0        2   69.0  \n",
       "6           70.0          64.0        1   81.0  \n",
       "26          70.0          64.0        3   99.0  \n",
       "17          98.0          74.0        2   88.0  \n",
       "7           98.0          74.0        1   88.0  \n",
       "27          98.0          74.0        3   68.0  \n",
       "28          57.0          92.0        3   88.0  \n",
       "18          57.0          92.0        2   55.0  \n",
       "8           57.0          92.0        1   54.0  \n",
       "19          91.0          76.0        2   89.0  \n",
       "9           91.0          76.0        1   90.0  \n",
       "29          91.0          76.0        3   94.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_2 = pd.melt(df, id_vars=['age','gender','name','id','memory_matrix', \n",
    "                     'labyrinth', 'math_game', 'names_memory', 'survey_score'],\n",
    "                    var_name='session',value_name='score')\n",
    "\n",
    "df_tidy_2['session'].replace({s:int(s[-1]) for s in df_tidy_2['session'].unique()}, inplace=True)\n",
    "df_tidy_2.sort_values('id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "math_game\n",
       "45.0    51.000000\n",
       "58.0    73.833333\n",
       "59.0    59.666667\n",
       "69.0    81.333333\n",
       "75.0    91.000000\n",
       "76.0    78.333333\n",
       "80.0    82.333333\n",
       "94.0    83.000000\n",
       "98.0    65.666667\n",
       "Name: score, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_2.groupby('math_game')['score'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x28b253b00b8>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_2.groupby('math_game')['score'].mean().plot(marker='o',linestyle='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name                score\n",
       "Alex Campbell       54.0     92.0\n",
       "                    55.0     92.0\n",
       "                    88.0     92.0\n",
       "Bonnie Shae         69.0     64.0\n",
       "                    81.0     64.0\n",
       "                    99.0     64.0\n",
       "Jesus Thompson      68.0     68.0\n",
       "                    71.0     68.0\n",
       "                    96.0     68.0\n",
       "Jonathan Corkill    73.0     90.0\n",
       "                    91.0     90.0\n",
       "                    97.0     90.0\n",
       "Jonathan Wingfield  31.0     29.0\n",
       "                    58.0     29.0\n",
       "                    64.0     29.0\n",
       "Josie Vance         89.0     76.0\n",
       "                    90.0     76.0\n",
       "                    94.0     76.0\n",
       "Louise Viernes      55.0     94.0\n",
       "                    56.0     94.0\n",
       "                    68.0     94.0\n",
       "Ray Sarwinski       77.0     74.0\n",
       "                    82.0     74.0\n",
       "                    88.0     74.0\n",
       "Rose Neal           68.0     74.0\n",
       "                    88.0     74.0\n",
       "Ross Richard        46.0     38.0\n",
       "                    55.0     38.0\n",
       "                    81.0     38.0\n",
       "Name: survey_score, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_2.groupby(['name','score'])['survey_score'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name\n",
       "Alex Campbell         65.666667\n",
       "Bonnie Shae           83.000000\n",
       "Jesus Thompson        78.333333\n",
       "Jonathan Corkill      87.000000\n",
       "Jonathan Wingfield    51.000000\n",
       "Josie Vance           91.000000\n",
       "Louise Viernes        59.666667\n",
       "Ray Sarwinski         82.333333\n",
       "Rose Neal             81.333333\n",
       "Ross Richard          60.666667\n",
       "Name: score, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy_2.groupby(['name'])['score'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['memory_matrix', 'labyrinth', 'math_game', 'names_memory', 'survey_score']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_names[:-3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>name</th>\n",
       "      <th>memory_matrix</th>\n",
       "      <th>labyrinth</th>\n",
       "      <th>math_game</th>\n",
       "      <th>names_memory</th>\n",
       "      <th>survey_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>68</td>\n",
       "      <td>male</td>\n",
       "      <td>Ross Richard</td>\n",
       "      <td>35.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>male</td>\n",
       "      <td>Jesus Thompson</td>\n",
       "      <td>74.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47</td>\n",
       "      <td>male</td>\n",
       "      <td>Jonathan Corkill</td>\n",
       "      <td>63.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39</td>\n",
       "      <td>male</td>\n",
       "      <td>Ray Sarwinski</td>\n",
       "      <td>86.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>83</td>\n",
       "      <td>male</td>\n",
       "      <td>Jonathan Wingfield</td>\n",
       "      <td>72.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>56</td>\n",
       "      <td>female</td>\n",
       "      <td>Louise Viernes</td>\n",
       "      <td>84.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>66</td>\n",
       "      <td>female</td>\n",
       "      <td>Bonnie Shae</td>\n",
       "      <td>44.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>48</td>\n",
       "      <td>female</td>\n",
       "      <td>Rose Neal</td>\n",
       "      <td>87.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>50</td>\n",
       "      <td>female</td>\n",
       "      <td>Alex Campbell</td>\n",
       "      <td>85.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>Josie Vance</td>\n",
       "      <td>90.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age  gender                name  memory_matrix  labyrinth  math_game  \\\n",
       "id                                                                         \n",
       "0    68    male        Ross Richard           35.0       35.0       58.0   \n",
       "1    33    male      Jesus Thompson           74.0       73.0       76.0   \n",
       "2    47    male    Jonathan Corkill           63.0       89.0       58.0   \n",
       "3    39    male       Ray Sarwinski           86.0       83.0       80.0   \n",
       "4    83    male  Jonathan Wingfield           72.0       70.0       45.0   \n",
       "5    56  female      Louise Viernes           84.0       44.0       59.0   \n",
       "6    66  female         Bonnie Shae           44.0       61.0       94.0   \n",
       "7    48  female           Rose Neal           87.0       71.0       69.0   \n",
       "8    50  female       Alex Campbell           85.0       87.0       98.0   \n",
       "9    28  female         Josie Vance           90.0       84.0       75.0   \n",
       "\n",
       "    names_memory  survey_score  \n",
       "id                              \n",
       "0           71.0          38.0  \n",
       "1           96.0          68.0  \n",
       "2           54.0          90.0  \n",
       "3           67.0          74.0  \n",
       "4           30.0          29.0  \n",
       "5           59.0          94.0  \n",
       "6           70.0          64.0  \n",
       "7           98.0          74.0  \n",
       "8           57.0          92.0  \n",
       "9           91.0          76.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subj_df = df[['age','gender','name','id','memory_matrix', 'labyrinth', \n",
    "              'math_game', 'names_memory', 'survey_score']].set_index('id', drop='True')\n",
    "subj_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>session</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  session  score\n",
       "0    0        1   46.0\n",
       "1    1        1   96.0\n",
       "2    2        1   91.0\n",
       "3    3        1   88.0\n",
       "4    4        1   58.0\n",
       "5    5        1   68.0\n",
       "6    6        1   81.0\n",
       "7    7        1   88.0\n",
       "8    8        1   54.0\n",
       "9    9        1   90.0\n",
       "10   0        2   81.0\n",
       "11   1        2   71.0\n",
       "12   2        2   73.0\n",
       "13   3        2   77.0\n",
       "14   4        2   31.0\n",
       "15   5        2   55.0\n",
       "16   6        2   69.0\n",
       "17   7        2   88.0\n",
       "18   8        2   55.0\n",
       "19   9        2   89.0\n",
       "20   0        3   55.0\n",
       "21   1        3   68.0\n",
       "22   2        3   97.0\n",
       "23   3        3   82.0\n",
       "24   4        3   64.0\n",
       "25   5        3   56.0\n",
       "26   6        3   99.0\n",
       "27   7        3   68.0\n",
       "28   8        3   88.0\n",
       "29   9        3   94.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores_df = pd.melt(df[['id','session_1','session_2','session_3']],id_vars='id',var_name='session',value_name='score')\n",
    "scores_df['session'].replace({s:int(s[-1]) for s in scores_df['session'].unique()}, inplace=True)\n",
    "scores_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>name</th>\n",
       "      <th>memory_matrix</th>\n",
       "      <th>labyrinth</th>\n",
       "      <th>math_game</th>\n",
       "      <th>names_memory</th>\n",
       "      <th>survey_score</th>\n",
       "      <th>id</th>\n",
       "      <th>session</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>83.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>83.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>58</td>\n",
       "      <td>male</td>\n",
       "      <td>Steven Frazier</td>\n",
       "      <td>83.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>58.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>58.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>Timothy Taber</td>\n",
       "      <td>58.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>91.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>91.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>Michelle Burton</td>\n",
       "      <td>91.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>79.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>79.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>21</td>\n",
       "      <td>female</td>\n",
       "      <td>Harriet Coulter</td>\n",
       "      <td>79.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age  gender             name  memory_matrix  labyrinth  math_game  \\\n",
       "0    58    male   Steven Frazier           83.0       79.0       60.0   \n",
       "4    58    male   Steven Frazier           83.0       79.0       60.0   \n",
       "8    58    male   Steven Frazier           83.0       79.0       60.0   \n",
       "1    42    male    Timothy Taber           58.0       76.0       62.0   \n",
       "5    42    male    Timothy Taber           58.0       76.0       62.0   \n",
       "9    42    male    Timothy Taber           58.0       76.0       62.0   \n",
       "2    24  female  Michelle Burton           91.0       91.0       92.0   \n",
       "6    24  female  Michelle Burton           91.0       91.0       92.0   \n",
       "10   24  female  Michelle Burton           91.0       91.0       92.0   \n",
       "3    21  female  Harriet Coulter           79.0       82.0       90.0   \n",
       "7    21  female  Harriet Coulter           79.0       82.0       90.0   \n",
       "11   21  female  Harriet Coulter           79.0       82.0       90.0   \n",
       "\n",
       "    names_memory  survey_score  id  session  score  \n",
       "0           83.0          60.0   0        1   43.0  \n",
       "4           83.0          60.0   0        2   63.0  \n",
       "8           83.0          60.0   0        3   74.0  \n",
       "1           73.0          82.0   1        1   95.0  \n",
       "5           73.0          82.0   1        2   76.0  \n",
       "9           73.0          82.0   1        3   65.0  \n",
       "2           98.0          86.0   2        1   86.0  \n",
       "6           98.0          86.0   2        2   86.0  \n",
       "10          98.0          86.0   2        3   91.0  \n",
       "3           83.0          79.0   3        1   98.0  \n",
       "7           83.0          79.0   3        2   90.0  \n",
       "11          83.0          79.0   3        3   83.0  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(subj_df, scores_df, left_index=True, right_on='id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's get back to the billboard dataset, and tidy it in the same manner."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false,
    "scrolled": 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</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    year               artist                                  track  time  \\\n",
       "id                                                                           \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",
       "\n",
       "   genre date.entered date.peaked  \n",
       "id                                 \n",
       "0   Rock   2000-09-23  2000-11-18  \n",
       "1   Rock   2000-02-12  2000-04-08  \n",
       "2   Rock   1999-10-23  2000-01-29  \n",
       "3   Rock   2000-08-12  2000-09-16  \n",
       "4   Rock   2000-08-05  2000-10-14  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billboard_untidy.index.name = 'id'\n",
    "\n",
    "songs_keys = billboard_untidy.keys()[:7]\n",
    "songs_df = billboard_untidy[songs_keys]\n",
    "songs_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>week</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  week  rank\n",
       "0    0     1  78.0\n",
       "1    1     1  15.0\n",
       "2    2     1  71.0\n",
       "3    3     1  41.0\n",
       "4    4     1  57.0\n",
       "5    5     1  59.0\n",
       "6    6     1  83.0\n",
       "7    7     1  63.0\n",
       "8    8     1  77.0\n",
       "9    9     1  81.0\n",
       "10  10     1  82.0\n",
       "11  11     1  71.0\n",
       "12  12     1  70.0\n",
       "13  13     1  84.0\n",
       "14  14     1  59.0\n",
       "15  15     1  60.0\n",
       "16  16     1  82.0\n",
       "17  17     1  81.0\n",
       "18  18     1  72.0\n",
       "19  19     1  99.0"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rank_keys = billboard_untidy.keys()[7:]\n",
    "rank_df = billboard_untidy[rank_keys]\n",
    "rank_df.index.name = 'id'\n",
    "rank_df = rank_df.reset_index()\n",
    "rank_df = pd.melt(rank_df, id_vars='id', var_name = 'week', value_name = 'rank')\n",
    "rank_df['week'].replace({s:keep_num(s) for s in rank_df['week']}, inplace=True)\n",
    "rank_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>week</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24072</th>\n",
       "      <td>297</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24073</th>\n",
       "      <td>298</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24074</th>\n",
       "      <td>299</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24075</th>\n",
       "      <td>300</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24076</th>\n",
       "      <td>301</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24077</th>\n",
       "      <td>302</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24078</th>\n",
       "      <td>303</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24079</th>\n",
       "      <td>304</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24080</th>\n",
       "      <td>305</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24081</th>\n",
       "      <td>306</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24082</th>\n",
       "      <td>307</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24083</th>\n",
       "      <td>308</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24084</th>\n",
       "      <td>309</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24085</th>\n",
       "      <td>310</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24086</th>\n",
       "      <td>311</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24087</th>\n",
       "      <td>312</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24088</th>\n",
       "      <td>313</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24089</th>\n",
       "      <td>314</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24090</th>\n",
       "      <td>315</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24091</th>\n",
       "      <td>316</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  week  rank\n",
       "24072  297    76   NaN\n",
       "24073  298    76   NaN\n",
       "24074  299    76   NaN\n",
       "24075  300    76   NaN\n",
       "24076  301    76   NaN\n",
       "24077  302    76   NaN\n",
       "24078  303    76   NaN\n",
       "24079  304    76   NaN\n",
       "24080  305    76   NaN\n",
       "24081  306    76   NaN\n",
       "24082  307    76   NaN\n",
       "24083  308    76   NaN\n",
       "24084  309    76   NaN\n",
       "24085  310    76   NaN\n",
       "24086  311    76   NaN\n",
       "24087  312    76   NaN\n",
       "24088  313    76   NaN\n",
       "24089  314    76   NaN\n",
       "24090  315    76   NaN\n",
       "24091  316    76   NaN"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rank_df.tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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</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>id</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>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>0</td>\n",
       "      <td>1</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</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>0</td>\n",
       "      <td>2</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>634</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>0</td>\n",
       "      <td>3</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951</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>0</td>\n",
       "      <td>4</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1268</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>0</td>\n",
       "      <td>5</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      year           artist                     track  time genre  \\\n",
       "0     2000  Destiny's Child  Independent Women Part I  3:38  Rock   \n",
       "317   2000  Destiny's Child  Independent Women Part I  3:38  Rock   \n",
       "634   2000  Destiny's Child  Independent Women Part I  3:38  Rock   \n",
       "951   2000  Destiny's Child  Independent Women Part I  3:38  Rock   \n",
       "1268  2000  Destiny's Child  Independent Women Part I  3:38  Rock   \n",
       "\n",
       "     date.entered date.peaked  id  week  rank  \n",
       "0      2000-09-23  2000-11-18   0     1  78.0  \n",
       "317    2000-09-23  2000-11-18   0     2  63.0  \n",
       "634    2000-09-23  2000-11-18   0     3  49.0  \n",
       "951    2000-09-23  2000-11-18   0     4  33.0  \n",
       "1268   2000-09-23  2000-11-18   0     5  23.0  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(songs_df, rank_df, left_index=True, right_on='id').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(24092, 10)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(songs_df, rank_df, left_index=True, right_on='id').shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Afterword\n",
    "\n",
    "Tidy data is only worthwhile if it\n",
    "makes analysis easier. **Tidy tools** take tidy datasets as input\n",
    "and return tidy datasets as output. **Tidy tools** are useful because the output of one tool\n",
    "can be used as the input to another. This allows you to simply and easily compose multiple\n",
    "tools to solve a problem. Tidy data also ensures that variables are stored in a consistent,\n",
    "explicit manner. This makes each tool simpler, because it does not need a Swiss Army knife\n",
    "of parameters for dealing with different dataset structures.\n",
    "\n",
    "Tools can be messy for two reasons: either they take messy datasets as input (**messy-input\n",
    "tools**) or they produce messy datasets as output (**messy-output tools**). Messy-input tools are\n",
    "typically more complicated than tidy-input tools because they need to include some parts of\n",
    "the tidying process. This can be useful for common types of messy datasets, but it typically\n",
    "makes the function more complex, harder to use and harder to maintain. Messy-output tools\n",
    "are frustrating and slow down analysis because they cannot be easily composed and you must\n",
    "constantly think about how to convert from one format to another.\n",
    "\n",
    "We will see examples of messy tools and, more importantly, tidy tools in our future lessons."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Possible exercises\n",
    "These are some exercise drafts, some of them became of them will become real exercises, but I may not have thoroughly tested them yet. For example, they may require using somethig which we didn't learn in class. You can try them at your own risk, but it is likely that it is going to be quite difficult :) Try them only after you've studied other notebooks in this lesson (`002_data_organization`). For some exercises I provide my solutions below (they may not be well commented, because it is a draft)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 1**: Find top 10 songs, which stayed in the chart for the longest duration (number of weeks). How long have they stayed? Find the song names. **Optional**: do it with the \"tidyest\" dataset, the one where information about songs and chart info are split into 2 dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Solutions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>dur</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     dur\n",
       "id      \n",
       "46  57.0\n",
       "9   55.0\n",
       "24  53.0\n",
       "17  53.0\n",
       "13  47.0\n",
       "28  44.0\n",
       "12  41.0\n",
       "15  39.0\n",
       "20  37.0\n",
       "47  34.0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def how_many_nan(x):\n",
    "    return np.sum(np.isnan(x))\n",
    "\n",
    "top_durations = len(rank_df['week'].unique()) - rank_df.groupby('id')['rank'].agg(how_many_nan).sort_values().iloc[:10]\n",
    "top_durations = pd.DataFrame(top_durations)\n",
    "top_durations.rename(columns={'rank':'dur'}, inplace=True)\n",
    "top_durations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>dur</th>\n",
       "      <th>year</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>55.0</td>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>41.0</td>\n",
       "      <td>2000</td>\n",
       "      <td>Vertical Horizon</td>\n",
       "      <td>Everything You Want</td>\n",
       "      <td>4:01</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-01-22</td>\n",
       "      <td>2000-07-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>47.0</td>\n",
       "      <td>2000</td>\n",
       "      <td>Creed</td>\n",
       "      <td>With Arms Wide Open</td>\n",
       "      <td>3:52</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-05-13</td>\n",
       "      <td>2000-11-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>39.0</td>\n",
       "      <td>2000</td>\n",
       "      <td>matchbox twenty</td>\n",
       "      <td>Bent</td>\n",
       "      <td>4:12</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-04-29</td>\n",
       "      <td>2000-07-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>53.0</td>\n",
       "      <td>2000</td>\n",
       "      <td>Hill, Faith</td>\n",
       "      <td>Breathe</td>\n",
       "      <td>4:04</td>\n",
       "      <td>Rap</td>\n",
       "      <td>1999-11-06</td>\n",
       "      <td>2000-04-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>37.0</td>\n",
       "      <td>2000</td>\n",
       "      <td>Braxton, Toni</td>\n",
       "      <td>He Wasn't Man Enough</td>\n",
       "      <td>4:21</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-03-18</td>\n",
       "      <td>2000-05-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>53.0</td>\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>2000-04-08</td>\n",
       "      <td>2000-11-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>44.0</td>\n",
       "      <td>2000</td>\n",
       "      <td>Joe</td>\n",
       "      <td>I Wanna Know</td>\n",
       "      <td>4:57</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>2000-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>57.0</td>\n",
       "      <td>2000</td>\n",
       "      <td>Creed</td>\n",
       "      <td>Higher</td>\n",
       "      <td>5:16</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-09-11</td>\n",
       "      <td>2000-07-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>34.0</td>\n",
       "      <td>2000</td>\n",
       "      <td>Nelly</td>\n",
       "      <td>(Hot S**t) Country Grammar</td>\n",
       "      <td>4:17</td>\n",
       "      <td>Rap</td>\n",
       "      <td>2000-04-29</td>\n",
       "      <td>2000-09-16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     dur  year            artist                       track  time    genre  \\\n",
       "id                                                                            \n",
       "9   55.0  2000          Lonestar                      Amazed  4:25  Country   \n",
       "12  41.0  2000  Vertical Horizon         Everything You Want  4:01     Rock   \n",
       "13  47.0  2000             Creed         With Arms Wide Open  3:52     Rock   \n",
       "15  39.0  2000   matchbox twenty                        Bent  4:12     Rock   \n",
       "17  53.0  2000       Hill, Faith                     Breathe  4:04      Rap   \n",
       "20  37.0  2000     Braxton, Toni        He Wasn't Man Enough  4:21     Rock   \n",
       "24  53.0  2000      3 Doors Down                  Kryptonite  3:53     Rock   \n",
       "28  44.0  2000               Joe                I Wanna Know  4:57     Rock   \n",
       "46  57.0  2000             Creed                      Higher  5:16     Rock   \n",
       "47  34.0  2000             Nelly  (Hot S**t) Country Grammar  4:17      Rap   \n",
       "\n",
       "   date.entered date.peaked  \n",
       "id                           \n",
       "9    1999-06-05  2000-03-04  \n",
       "12   2000-01-22  2000-07-15  \n",
       "13   2000-05-13  2000-11-11  \n",
       "15   2000-04-29  2000-07-22  \n",
       "17   1999-11-06  2000-04-22  \n",
       "20   2000-03-18  2000-05-06  \n",
       "24   2000-04-08  2000-11-11  \n",
       "28   2000-01-01  2000-07-01  \n",
       "46   1999-09-11  2000-07-22  \n",
       "47   2000-04-29  2000-09-16  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(top_durations, songs_df, left_index=True, right_index=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.6",
   "language": "python",
   "name": "py36"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}