09- MissingValues_ed.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've seen a preview of how Pandas handles missing values using the None type and NumPy NaN values. Missing\n",
    "values are pretty common in data cleaning activities. And, missing values can be there for any number of\n",
    "reasons, and I just want to touch on a few here.\n",
    "\n",
    "For instance, if you are running a survey and a respondant didn't answer a question the missing value is\n",
    "actually an omission. This kind of missing data is called **Missing at Random** if there are other variables\n",
    "that might be used to predict the variable which is missing. In my work when I delivery surveys I often find\n",
    "that missing data, say the interest in being involved in a follow up study, often has some correlation with\n",
    "another data field, like gender or ethnicity. If there is no relationship to other variables, then we call\n",
    "this data **Missing Completely at Random (MCAR)**.\n",
    "\n",
    "These are just two examples of missing data, and there are many more. For instance, data might be missing\n",
    "because it wasn't collected, either by the process responsible for collecting that data, such as a\n",
    "researcher, or because it wouldn't make sense if it were collected. This last example is extremely common\n",
    "when you start joining DataFrames together from multiple sources, such as joining a list of people at a\n",
    "university with a list of offices in the university (students generally don't have offices).\n",
    "\n",
    "Let's look at some ways of handling missing data in pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets import pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Prefix</th>\n",
       "      <th>Assignment</th>\n",
       "      <th>Tutorial</th>\n",
       "      <th>Midterm</th>\n",
       "      <th>TakeHome</th>\n",
       "      <th>Final</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>57.14</td>\n",
       "      <td>34.09</td>\n",
       "      <td>64.38</td>\n",
       "      <td>51.48</td>\n",
       "      <td>52.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>95.05</td>\n",
       "      <td>105.49</td>\n",
       "      <td>67.50</td>\n",
       "      <td>99.07</td>\n",
       "      <td>68.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>83.70</td>\n",
       "      <td>83.17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.15</td>\n",
       "      <td>48.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.38</td>\n",
       "      <td>105.93</td>\n",
       "      <td>80.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>91.32</td>\n",
       "      <td>93.64</td>\n",
       "      <td>95.00</td>\n",
       "      <td>107.41</td>\n",
       "      <td>73.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>95.00</td>\n",
       "      <td>92.58</td>\n",
       "      <td>93.12</td>\n",
       "      <td>97.78</td>\n",
       "      <td>68.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8</td>\n",
       "      <td>95.05</td>\n",
       "      <td>102.99</td>\n",
       "      <td>56.25</td>\n",
       "      <td>99.07</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>72.85</td>\n",
       "      <td>86.85</td>\n",
       "      <td>60.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>84.26</td>\n",
       "      <td>93.10</td>\n",
       "      <td>47.50</td>\n",
       "      <td>18.52</td>\n",
       "      <td>50.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7</td>\n",
       "      <td>90.10</td>\n",
       "      <td>97.55</td>\n",
       "      <td>51.25</td>\n",
       "      <td>88.89</td>\n",
       "      <td>63.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Prefix  Assignment  Tutorial  Midterm  TakeHome  Final\n",
       "0       5       57.14     34.09    64.38     51.48  52.50\n",
       "1       8       95.05    105.49    67.50     99.07  68.33\n",
       "2       8       83.70     83.17      NaN     63.15  48.89\n",
       "3       7         NaN       NaN    49.38    105.93  80.56\n",
       "4       8       91.32     93.64    95.00    107.41  73.89\n",
       "5       7       95.00     92.58    93.12     97.78  68.06\n",
       "6       8       95.05    102.99    56.25     99.07  50.00\n",
       "7       7       72.85     86.85    60.00       NaN  56.11\n",
       "8       8       84.26     93.10    47.50     18.52  50.83\n",
       "9       7       90.10     97.55    51.25     88.89  63.61"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas is pretty good at detecting missing values directly from underlying data formats, like CSV files.\n",
    "# Although most missing valuse are often formatted as NaN, NULL, None, or N/A, sometimes missing values are\n",
    "# not labeled so clearly. For example, I've worked with social scientists who regularly used the value of 99\n",
    "# in binary categories to indicate a missing value. The pandas read_csv() function has a parameter called\n",
    "# na_values to let us specify the form of missing values. It allows scalar, string, list, or dictionaries to\n",
    "# be used.\n",
    "\n",
    "# Let's load a piece of data from a file called log.csv\n",
    "df = pd.read_csv('datasets/class_grades.csv')\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Prefix</th>\n",
       "      <th>Assignment</th>\n",
       "      <th>Tutorial</th>\n",
       "      <th>Midterm</th>\n",
       "      <th>TakeHome</th>\n",
       "      <th>Final</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Prefix  Assignment  Tutorial  Midterm  TakeHome  Final\n",
       "0   False       False     False    False     False  False\n",
       "1   False       False     False    False     False  False\n",
       "2   False       False     False     True     False  False\n",
       "3   False        True      True    False     False  False\n",
       "4   False       False     False    False     False  False\n",
       "5   False       False     False    False     False  False\n",
       "6   False       False     False    False     False  False\n",
       "7   False       False     False    False      True  False\n",
       "8   False       False     False    False     False  False\n",
       "9   False       False     False    False     False  False"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can actually use the function .isnull() to create a boolean mask of the whole dataframe. This effectively\n",
    "# broadcasts the isnull() function to every cell of data.\n",
    "mask=df.isnull()\n",
    "mask.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Prefix</th>\n",
       "      <th>Assignment</th>\n",
       "      <th>Tutorial</th>\n",
       "      <th>Midterm</th>\n",
       "      <th>TakeHome</th>\n",
       "      <th>Final</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>57.14</td>\n",
       "      <td>34.09</td>\n",
       "      <td>64.38</td>\n",
       "      <td>51.48</td>\n",
       "      <td>52.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>95.05</td>\n",
       "      <td>105.49</td>\n",
       "      <td>67.50</td>\n",
       "      <td>99.07</td>\n",
       "      <td>68.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>91.32</td>\n",
       "      <td>93.64</td>\n",
       "      <td>95.00</td>\n",
       "      <td>107.41</td>\n",
       "      <td>73.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>95.00</td>\n",
       "      <td>92.58</td>\n",
       "      <td>93.12</td>\n",
       "      <td>97.78</td>\n",
       "      <td>68.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8</td>\n",
       "      <td>95.05</td>\n",
       "      <td>102.99</td>\n",
       "      <td>56.25</td>\n",
       "      <td>99.07</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>84.26</td>\n",
       "      <td>93.10</td>\n",
       "      <td>47.50</td>\n",
       "      <td>18.52</td>\n",
       "      <td>50.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7</td>\n",
       "      <td>90.10</td>\n",
       "      <td>97.55</td>\n",
       "      <td>51.25</td>\n",
       "      <td>88.89</td>\n",
       "      <td>63.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7</td>\n",
       "      <td>80.44</td>\n",
       "      <td>90.20</td>\n",
       "      <td>75.00</td>\n",
       "      <td>91.48</td>\n",
       "      <td>39.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8</td>\n",
       "      <td>97.16</td>\n",
       "      <td>103.71</td>\n",
       "      <td>72.50</td>\n",
       "      <td>93.52</td>\n",
       "      <td>63.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>7</td>\n",
       "      <td>91.28</td>\n",
       "      <td>83.53</td>\n",
       "      <td>81.25</td>\n",
       "      <td>99.81</td>\n",
       "      <td>92.22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Prefix  Assignment  Tutorial  Midterm  TakeHome  Final\n",
       "0        5       57.14     34.09    64.38     51.48  52.50\n",
       "1        8       95.05    105.49    67.50     99.07  68.33\n",
       "4        8       91.32     93.64    95.00    107.41  73.89\n",
       "5        7       95.00     92.58    93.12     97.78  68.06\n",
       "6        8       95.05    102.99    56.25     99.07  50.00\n",
       "8        8       84.26     93.10    47.50     18.52  50.83\n",
       "9        7       90.10     97.55    51.25     88.89  63.61\n",
       "10       7       80.44     90.20    75.00     91.48  39.72\n",
       "12       8       97.16    103.71    72.50     93.52  63.33\n",
       "13       7       91.28     83.53    81.25     99.81  92.22"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This can be useful for processing rows based on certain columns of data. Another useful operation is to be\n",
    "# able to drop all of those rows which have any missing data, which can be done with the dropna() function.\n",
    "df.dropna().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Prefix</th>\n",
       "      <th>Assignment</th>\n",
       "      <th>Tutorial</th>\n",
       "      <th>Midterm</th>\n",
       "      <th>TakeHome</th>\n",
       "      <th>Final</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>57.14</td>\n",
       "      <td>34.09</td>\n",
       "      <td>64.38</td>\n",
       "      <td>51.48</td>\n",
       "      <td>52.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>95.05</td>\n",
       "      <td>105.49</td>\n",
       "      <td>67.50</td>\n",
       "      <td>99.07</td>\n",
       "      <td>68.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>83.70</td>\n",
       "      <td>83.17</td>\n",
       "      <td>0.00</td>\n",
       "      <td>63.15</td>\n",
       "      <td>48.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>49.38</td>\n",
       "      <td>105.93</td>\n",
       "      <td>80.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>91.32</td>\n",
       "      <td>93.64</td>\n",
       "      <td>95.00</td>\n",
       "      <td>107.41</td>\n",
       "      <td>73.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>95.00</td>\n",
       "      <td>92.58</td>\n",
       "      <td>93.12</td>\n",
       "      <td>97.78</td>\n",
       "      <td>68.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8</td>\n",
       "      <td>95.05</td>\n",
       "      <td>102.99</td>\n",
       "      <td>56.25</td>\n",
       "      <td>99.07</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>72.85</td>\n",
       "      <td>86.85</td>\n",
       "      <td>60.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>56.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>84.26</td>\n",
       "      <td>93.10</td>\n",
       "      <td>47.50</td>\n",
       "      <td>18.52</td>\n",
       "      <td>50.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7</td>\n",
       "      <td>90.10</td>\n",
       "      <td>97.55</td>\n",
       "      <td>51.25</td>\n",
       "      <td>88.89</td>\n",
       "      <td>63.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Prefix  Assignment  Tutorial  Midterm  TakeHome  Final\n",
       "0       5       57.14     34.09    64.38     51.48  52.50\n",
       "1       8       95.05    105.49    67.50     99.07  68.33\n",
       "2       8       83.70     83.17     0.00     63.15  48.89\n",
       "3       7        0.00      0.00    49.38    105.93  80.56\n",
       "4       8       91.32     93.64    95.00    107.41  73.89\n",
       "5       7       95.00     92.58    93.12     97.78  68.06\n",
       "6       8       95.05    102.99    56.25     99.07  50.00\n",
       "7       7       72.85     86.85    60.00      0.00  56.11\n",
       "8       8       84.26     93.10    47.50     18.52  50.83\n",
       "9       7       90.10     97.55    51.25     88.89  63.61"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note how the rows indexed with 2, 3, 7, and 11 are now gone. One of the handy functions that Pandas has for\n",
    "# working with missing values is the filling function, fillna(). This function takes a number or parameters.\n",
    "# You could pass in a single value which is called a scalar value to change all of the missing data to one\n",
    "# value. This isn't really applicable in this case, but it's a pretty common use case.\n",
    "\n",
    "# So, if we wanted to fill all missing values with 0, we would use fillna\n",
    "df.fillna(0, inplace=True)\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note that the inplace attribute causes pandas to fill the values inline and does not return a copy of the\n",
    "# dataframe, but instead modifies the dataframe you have."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>user</th>\n",
       "      <th>video</th>\n",
       "      <th>playback position</th>\n",
       "      <th>paused</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1469974424</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1469974454</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1469974544</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1469974574</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1469977514</td>\n",
       "      <td>bob</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1469977544</td>\n",
       "      <td>bob</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1469977574</td>\n",
       "      <td>bob</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1469977604</td>\n",
       "      <td>bob</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1469974604</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1469974694</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1469974724</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1469974454</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1469974524</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1469974424</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1469974554</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1469974624</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1469974654</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1469974724</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1469974484</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1469974514</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          time    user          video  playback position paused  volume\n",
       "0   1469974424  cheryl     intro.html                  5  False    10.0\n",
       "1   1469974454  cheryl     intro.html                  6    NaN     NaN\n",
       "2   1469974544  cheryl     intro.html                  9    NaN     NaN\n",
       "3   1469974574  cheryl     intro.html                 10    NaN     NaN\n",
       "4   1469977514     bob     intro.html                  1    NaN     NaN\n",
       "5   1469977544     bob     intro.html                  1    NaN     NaN\n",
       "6   1469977574     bob     intro.html                  1    NaN     NaN\n",
       "7   1469977604     bob     intro.html                  1    NaN     NaN\n",
       "8   1469974604  cheryl     intro.html                 11    NaN     NaN\n",
       "9   1469974694  cheryl     intro.html                 14    NaN     NaN\n",
       "10  1469974724  cheryl     intro.html                 15    NaN     NaN\n",
       "11  1469974454     sue  advanced.html                 24    NaN     NaN\n",
       "12  1469974524     sue  advanced.html                 25    NaN     NaN\n",
       "13  1469974424     sue  advanced.html                 23  False    10.0\n",
       "14  1469974554     sue  advanced.html                 26    NaN     NaN\n",
       "15  1469974624     sue  advanced.html                 27    NaN     NaN\n",
       "16  1469974654     sue  advanced.html                 28    NaN     5.0\n",
       "17  1469974724     sue  advanced.html                 29    NaN     NaN\n",
       "18  1469974484  cheryl     intro.html                  7    NaN     NaN\n",
       "19  1469974514  cheryl     intro.html                  8    NaN     NaN"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can also use the na_filter option to turn off white space filtering, if white space is an actual value of\n",
    "# interest. But in practice, this is pretty rare. In data without any NAs, passing na_filter=False, can\n",
    "# improve the performance of reading a large file.\n",
    "\n",
    "# In addition to rules controlling how missing values might be loaded, it's sometimes useful to consider\n",
    "# missing values as actually having information. I'll give an example from my own research.  I often deal with\n",
    "# logs from online learning systems. I've looked at video use in lecture capture systems. In these systems\n",
    "# it's common for the player for have a heartbeat functionality where playback statistics are sent to the\n",
    "# server every so often, maybe every 30 seconds. These heartbeats can get big as they can carry the whole\n",
    "# state of the playback system such as where the video play head is at, where the video size is, which video\n",
    "# is being rendered to the screen, how loud the volume is.\n",
    "\n",
    "# If we load the data file log.csv, we can see an example of what this might look like.\n",
    "df = pd.read_csv(\"datasets/log.csv\")\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In this data the first column is a timestamp in the Unix epoch format. The next column is the user name\n",
    "# followed by a web page they're visiting and the video that they're playing. Each row of the DataFrame has a\n",
    "# playback position. And we can see that as the playback position increases by one, the time stamp increases\n",
    "# by about 30 seconds.\n",
    "\n",
    "# Except for user Bob. It turns out that Bob has paused his playback so as time increases the playback\n",
    "# position doesn't change. Note too how difficult it is for us to try and derive this knowledge from the data,\n",
    "# because it's not sorted by time stamp as one might expect. This is actually not uncommon on systems which\n",
    "# have a high degree of parallelism. There are a lot of missing values in the paused and volume columns. It's\n",
    "# not efficient to send this information across the network if it hasn't changed. So this articular system\n",
    "# just inserts null values into the database if there's no changes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>video</th>\n",
       "      <th>playback position</th>\n",
       "      <th>paused</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</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>1469974424</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974424</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974454</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974454</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974484</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974514</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974524</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974544</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974554</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974574</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974604</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974624</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974634</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974654</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974664</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974694</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974724</th>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974724</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974754</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974824</th>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              user          video  playback position paused  volume\n",
       "time                                                               \n",
       "1469974424  cheryl     intro.html                  5  False    10.0\n",
       "1469974424     sue  advanced.html                 23  False    10.0\n",
       "1469974454  cheryl     intro.html                  6    NaN     NaN\n",
       "1469974454     sue  advanced.html                 24    NaN     NaN\n",
       "1469974484  cheryl     intro.html                  7    NaN     NaN\n",
       "1469974514  cheryl     intro.html                  8    NaN     NaN\n",
       "1469974524     sue  advanced.html                 25    NaN     NaN\n",
       "1469974544  cheryl     intro.html                  9    NaN     NaN\n",
       "1469974554     sue  advanced.html                 26    NaN     NaN\n",
       "1469974574  cheryl     intro.html                 10    NaN     NaN\n",
       "1469974604  cheryl     intro.html                 11    NaN     NaN\n",
       "1469974624     sue  advanced.html                 27    NaN     NaN\n",
       "1469974634  cheryl     intro.html                 12    NaN     NaN\n",
       "1469974654     sue  advanced.html                 28    NaN     5.0\n",
       "1469974664  cheryl     intro.html                 13    NaN     NaN\n",
       "1469974694  cheryl     intro.html                 14    NaN     NaN\n",
       "1469974724  cheryl     intro.html                 15    NaN     NaN\n",
       "1469974724     sue  advanced.html                 29    NaN     NaN\n",
       "1469974754     sue  advanced.html                 30    NaN     NaN\n",
       "1469974824     sue  advanced.html                 31    NaN     NaN"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Next up is the method parameter(). The two common fill values are ffill and bfill. ffill is for forward\n",
    "# filling and it updates an na value for a particular cell with the value from the previous row. bfill is\n",
    "# backward filling, which is the opposite of ffill. It fills the missing values with the next valid value.\n",
    "# It's important to note that your data needs to be sorted in order for this to have the effect you might\n",
    "# want. Data which comes from traditional database management systems usually has no order guarantee, just\n",
    "# like this data. So be careful.\n",
    "\n",
    "# In Pandas we can sort either by index or by values. Here we'll just promote the time stamp to an index then\n",
    "# sort on the index.\n",
    "df = df.set_index('time')\n",
    "df = df.sort_index()\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>video</th>\n",
       "      <th>playback position</th>\n",
       "      <th>paused</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>user</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1469974424</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1469974454</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974484</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974514</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974524</th>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974544</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974554</th>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974574</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974604</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974624</th>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974634</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974654</th>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974664</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974694</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1469974724</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974754</th>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974824</th>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974854</th>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974924</th>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977424</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977454</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977484</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977514</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977544</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977574</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977604</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977634</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977664</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977694</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469977724</th>\n",
       "      <th>bob</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           video  playback position paused  volume\n",
       "time       user                                                   \n",
       "1469974424 cheryl     intro.html                  5  False    10.0\n",
       "           sue     advanced.html                 23  False    10.0\n",
       "1469974454 cheryl     intro.html                  6    NaN     NaN\n",
       "           sue     advanced.html                 24    NaN     NaN\n",
       "1469974484 cheryl     intro.html                  7    NaN     NaN\n",
       "1469974514 cheryl     intro.html                  8    NaN     NaN\n",
       "1469974524 sue     advanced.html                 25    NaN     NaN\n",
       "1469974544 cheryl     intro.html                  9    NaN     NaN\n",
       "1469974554 sue     advanced.html                 26    NaN     NaN\n",
       "1469974574 cheryl     intro.html                 10    NaN     NaN\n",
       "1469974604 cheryl     intro.html                 11    NaN     NaN\n",
       "1469974624 sue     advanced.html                 27    NaN     NaN\n",
       "1469974634 cheryl     intro.html                 12    NaN     NaN\n",
       "1469974654 sue     advanced.html                 28    NaN     5.0\n",
       "1469974664 cheryl     intro.html                 13    NaN     NaN\n",
       "1469974694 cheryl     intro.html                 14    NaN     NaN\n",
       "1469974724 cheryl     intro.html                 15    NaN     NaN\n",
       "           sue     advanced.html                 29    NaN     NaN\n",
       "1469974754 sue     advanced.html                 30    NaN     NaN\n",
       "1469974824 sue     advanced.html                 31    NaN     NaN\n",
       "1469974854 sue     advanced.html                 32    NaN     NaN\n",
       "1469974924 sue     advanced.html                 33    NaN     NaN\n",
       "1469977424 bob        intro.html                  1   True    10.0\n",
       "1469977454 bob        intro.html                  1    NaN     NaN\n",
       "1469977484 bob        intro.html                  1    NaN     NaN\n",
       "1469977514 bob        intro.html                  1    NaN     NaN\n",
       "1469977544 bob        intro.html                  1    NaN     NaN\n",
       "1469977574 bob        intro.html                  1    NaN     NaN\n",
       "1469977604 bob        intro.html                  1    NaN     NaN\n",
       "1469977634 bob        intro.html                  1    NaN     NaN\n",
       "1469977664 bob        intro.html                  1    NaN     NaN\n",
       "1469977694 bob        intro.html                  1    NaN     NaN\n",
       "1469977724 bob        intro.html                  1    NaN     NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If we look closely at the output though we'll notice that the index \n",
    "# isn't really unique. Two users seem to be able to use the system at the same \n",
    "# time. Again, a very common case. Let's reset the index, and use some \n",
    "# multi-level indexing on time AND user together instead,\n",
    "# promote the user name to a second level of the index to deal with that issue.\n",
    "\n",
    "df = df.reset_index()\n",
    "df = df.set_index(['time', 'user'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>video</th>\n",
       "      <th>playback position</th>\n",
       "      <th>paused</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>user</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1469974424</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1469974454</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>6</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sue</th>\n",
       "      <td>advanced.html</td>\n",
       "      <td>24</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1469974484</th>\n",
       "      <th>cheryl</th>\n",
       "      <td>intro.html</td>\n",
       "      <td>7</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           video  playback position  paused  volume\n",
       "time       user                                                    \n",
       "1469974424 cheryl     intro.html                  5   False    10.0\n",
       "           sue     advanced.html                 23   False    10.0\n",
       "1469974454 cheryl     intro.html                  6   False    10.0\n",
       "           sue     advanced.html                 24   False    10.0\n",
       "1469974484 cheryl     intro.html                  7   False    10.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now that we have the data indexed and sorted appropriately, we can fill the missing datas using ffill. It's\n",
    "# good to remember when dealing with missing values so you can deal with individual columns or sets of columns\n",
    "# by projecting them. So you don't have to fix all missing values in one command.\n",
    "\n",
    "df = df.fillna(method='ffill')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "0  1  3  a\n",
       "1  1  6  b\n",
       "2  2  3  c\n",
       "3  3  8  d\n",
       "4  4  9  e"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can also do customized fill-in to replace values with the replace() function. It allows replacement from\n",
    "# several approaches: value-to-value, list, dictionary, regex Let's generate a simple example\n",
    "df = pd.DataFrame({'A': [1, 1, 2, 3, 4],\n",
    "                   'B': [3, 6, 3, 8, 9],\n",
    "                   'C': ['a', 'b', 'c', 'd', 'e']})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>3</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>6</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A  B  C\n",
       "0  100  3  a\n",
       "1  100  6  b\n",
       "2    2  3  c\n",
       "3    3  8  d\n",
       "4    4  9  e"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can replace 1's with 100, let's try the value-to-value approach\n",
    "df.replace(1, 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>300</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>6</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>300</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>300</td>\n",
       "      <td>8</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B  C\n",
       "0  100  300  a\n",
       "1  100    6  b\n",
       "2    2  300  c\n",
       "3  300    8  d\n",
       "4    4    9  e"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How about changing two values? Let's try the list approach For example, we want to change 1's to 100 and 3's\n",
    "# to 300\n",
    "df.replace([1, 3], [100, 300])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>user</th>\n",
       "      <th>video</th>\n",
       "      <th>playback position</th>\n",
       "      <th>paused</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1469974424</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1469974454</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1469974544</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1469974574</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1469977514</td>\n",
       "      <td>bob</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1469977544</td>\n",
       "      <td>bob</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1469977574</td>\n",
       "      <td>bob</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1469977604</td>\n",
       "      <td>bob</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1469974604</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1469974694</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1469974724</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1469974454</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1469974524</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1469974424</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1469974554</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1469974624</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1469974654</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1469974724</td>\n",
       "      <td>sue</td>\n",
       "      <td>advanced.html</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1469974484</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1469974514</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>intro.html</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          time    user          video  playback position paused  volume\n",
       "0   1469974424  cheryl     intro.html                  5  False    10.0\n",
       "1   1469974454  cheryl     intro.html                  6    NaN     NaN\n",
       "2   1469974544  cheryl     intro.html                  9    NaN     NaN\n",
       "3   1469974574  cheryl     intro.html                 10    NaN     NaN\n",
       "4   1469977514     bob     intro.html                  1    NaN     NaN\n",
       "5   1469977544     bob     intro.html                  1    NaN     NaN\n",
       "6   1469977574     bob     intro.html                  1    NaN     NaN\n",
       "7   1469977604     bob     intro.html                  1    NaN     NaN\n",
       "8   1469974604  cheryl     intro.html                 11    NaN     NaN\n",
       "9   1469974694  cheryl     intro.html                 14    NaN     NaN\n",
       "10  1469974724  cheryl     intro.html                 15    NaN     NaN\n",
       "11  1469974454     sue  advanced.html                 24    NaN     NaN\n",
       "12  1469974524     sue  advanced.html                 25    NaN     NaN\n",
       "13  1469974424     sue  advanced.html                 23  False    10.0\n",
       "14  1469974554     sue  advanced.html                 26    NaN     NaN\n",
       "15  1469974624     sue  advanced.html                 27    NaN     NaN\n",
       "16  1469974654     sue  advanced.html                 28    NaN     5.0\n",
       "17  1469974724     sue  advanced.html                 29    NaN     NaN\n",
       "18  1469974484  cheryl     intro.html                  7    NaN     NaN\n",
       "19  1469974514  cheryl     intro.html                  8    NaN     NaN"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What's really cool about pandas replacement is that it supports regex too!\n",
    "# Let's look at our data from the dataset logs again\n",
    "df = pd.read_csv(\"datasets/log.csv\")\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To replace using a regex we make the first parameter to replace the regex pattern we want to match, the\n",
    "# second parameter the value we want to emit upon match, and then we pass in a third parameter \"regex=True\".\n",
    "\n",
    "# Take a moment to pause this video and think about this problem: imagine we want to detect all html pages in\n",
    "# the \"video\" column, lets say that just means they end with \".html\", and we want to overwrite that with the\n",
    "# keyword \"webpage\". How could we accomplish this?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>user</th>\n",
       "      <th>video</th>\n",
       "      <th>playback position</th>\n",
       "      <th>paused</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1469974424</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1469974454</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1469974544</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1469974574</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1469977514</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1469977544</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1469977574</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1469977604</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1469974604</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1469974694</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1469974724</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1469974454</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1469974524</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1469974424</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1469974554</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1469974624</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1469974654</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1469974724</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1469974484</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1469974514</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1469974754</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1469974824</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1469974854</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1469974924</td>\n",
       "      <td>sue</td>\n",
       "      <td>webpage</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1469977424</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1469977454</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1469977484</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1469977634</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1469977664</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1469974634</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1469974664</td>\n",
       "      <td>cheryl</td>\n",
       "      <td>webpage</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1469977694</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1469977724</td>\n",
       "      <td>bob</td>\n",
       "      <td>webpage</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          time    user    video  playback position paused  volume\n",
       "0   1469974424  cheryl  webpage                  5  False    10.0\n",
       "1   1469974454  cheryl  webpage                  6    NaN     NaN\n",
       "2   1469974544  cheryl  webpage                  9    NaN     NaN\n",
       "3   1469974574  cheryl  webpage                 10    NaN     NaN\n",
       "4   1469977514     bob  webpage                  1    NaN     NaN\n",
       "5   1469977544     bob  webpage                  1    NaN     NaN\n",
       "6   1469977574     bob  webpage                  1    NaN     NaN\n",
       "7   1469977604     bob  webpage                  1    NaN     NaN\n",
       "8   1469974604  cheryl  webpage                 11    NaN     NaN\n",
       "9   1469974694  cheryl  webpage                 14    NaN     NaN\n",
       "10  1469974724  cheryl  webpage                 15    NaN     NaN\n",
       "11  1469974454     sue  webpage                 24    NaN     NaN\n",
       "12  1469974524     sue  webpage                 25    NaN     NaN\n",
       "13  1469974424     sue  webpage                 23  False    10.0\n",
       "14  1469974554     sue  webpage                 26    NaN     NaN\n",
       "15  1469974624     sue  webpage                 27    NaN     NaN\n",
       "16  1469974654     sue  webpage                 28    NaN     5.0\n",
       "17  1469974724     sue  webpage                 29    NaN     NaN\n",
       "18  1469974484  cheryl  webpage                  7    NaN     NaN\n",
       "19  1469974514  cheryl  webpage                  8    NaN     NaN\n",
       "20  1469974754     sue  webpage                 30    NaN     NaN\n",
       "21  1469974824     sue  webpage                 31    NaN     NaN\n",
       "22  1469974854     sue  webpage                 32    NaN     NaN\n",
       "23  1469974924     sue  webpage                 33    NaN     NaN\n",
       "24  1469977424     bob  webpage                  1   True    10.0\n",
       "25  1469977454     bob  webpage                  1    NaN     NaN\n",
       "26  1469977484     bob  webpage                  1    NaN     NaN\n",
       "27  1469977634     bob  webpage                  1    NaN     NaN\n",
       "28  1469977664     bob  webpage                  1    NaN     NaN\n",
       "29  1469974634  cheryl  webpage                 12    NaN     NaN\n",
       "30  1469974664  cheryl  webpage                 13    NaN     NaN\n",
       "31  1469977694     bob  webpage                  1    NaN     NaN\n",
       "32  1469977724     bob  webpage                  1    NaN     NaN"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Here's my solution, first matching any number of characters then ending in .html\n",
    "df.replace(to_replace=\".*.html$\", value=\"webpage\", regex=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One last note on missing values. When you use statistical functions on DataFrames, these functions typically\n",
    "ignore missing values. For instance if you try and calculate the mean value of a DataFrame, the underlying\n",
    "NumPy function will ignore missing values. This is usually what you want but you should be aware that values\n",
    "are being excluded. Why you have missing values really matters depending upon the problem you are trying to\n",
    "solve. It might be unreasonable to infer missing values, for instance, if the data shouldn't exist in the\n",
    "first place."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}