notebooks/Data Cleaning in Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### In this exercise, we will perform some data cleaning tasks:\n",
    "(i) checking for completeness, (ii) merging/ appending, (iii) checking of values to ensure validity, (iv) de-duplication, (v) recoding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# The dataset Class1.csv can be found here: \n",
    "# https://github.com/hxchua/datadoubleconfirm/tree/master/datasets\n",
    "data1 = pd.read_csv(\"Class1.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100, 5)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking if the number of rows/ columns in the dataset was as expected\n",
    "data1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Male</td>\n",
       "      <td>12.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Male</td>\n",
       "      <td>34.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Male</td>\n",
       "      <td>23.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Male</td>\n",
       "      <td>15.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id gender  test1  test2  test3\n",
       "0   1   Male   12.0   56.0     50\n",
       "1   2   Male   34.0   58.0     51\n",
       "2   3   Male   23.0   61.0     63\n",
       "3   4   Male   15.0   67.0     62\n",
       "4   5   Male    NaN   37.0     68"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# previewing the data by looking at first few rows of dataset\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>100.000000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>50.500000</td>\n",
       "      <td>19.655556</td>\n",
       "      <td>56.077778</td>\n",
       "      <td>57.090000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>29.011492</td>\n",
       "      <td>10.235608</td>\n",
       "      <td>7.159857</td>\n",
       "      <td>8.001382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>40.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>25.750000</td>\n",
       "      <td>12.250000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>52.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>50.500000</td>\n",
       "      <td>17.500000</td>\n",
       "      <td>55.500000</td>\n",
       "      <td>57.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>75.250000</td>\n",
       "      <td>26.750000</td>\n",
       "      <td>60.750000</td>\n",
       "      <td>63.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>100.000000</td>\n",
       "      <td>41.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id      test1      test2       test3\n",
       "count  100.000000  90.000000  90.000000  100.000000\n",
       "mean    50.500000  19.655556  56.077778   57.090000\n",
       "std     29.011492  10.235608   7.159857    8.001382\n",
       "min      1.000000   1.000000  36.000000   40.000000\n",
       "25%     25.750000  12.250000  52.000000   52.000000\n",
       "50%     50.500000  17.500000  55.500000   57.000000\n",
       "75%     75.250000  26.750000  60.750000   63.000000\n",
       "max    100.000000  41.000000  75.000000   79.000000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# finding out the ranges of values for each variable to check validity\n",
    "data1.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Male      50\n",
       "Female    50\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# note that the describe function only covers numerical variables\n",
    "# use value_counts function to get the breakdown for categorical variables\n",
    "data1[\"gender\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.0</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>Male</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60.0</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>Male</td>\n",
       "      <td>39.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48.0</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56.0</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>Male</td>\n",
       "      <td>36.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>40</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>42</td>\n",
       "      <td>Male</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>45</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.0</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>48</td>\n",
       "      <td>Male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>50</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.0</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>54</td>\n",
       "      <td>Female</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>60</td>\n",
       "      <td>Female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  gender  test1  test2  test3\n",
       "4    5    Male    NaN   37.0     68\n",
       "5    6    Male   18.0    NaN     66\n",
       "9   10    Male    NaN   51.0     62\n",
       "11  12    Male   17.0    NaN     54\n",
       "14  15    Male    NaN   59.0     52\n",
       "17  18    Male   12.0    NaN     48\n",
       "19  20    Male    NaN   60.0     57\n",
       "23  24    Male   39.0    NaN     52\n",
       "24  25    Male    NaN   48.0     47\n",
       "29  30    Male    NaN    NaN     60\n",
       "34  35    Male    NaN   56.0     52\n",
       "35  36    Male   36.0    NaN     66\n",
       "39  40    Male    NaN   59.0     56\n",
       "41  42    Male   16.0    NaN     64\n",
       "44  45    Male    NaN   43.0     61\n",
       "47  48    Male   30.0    NaN     54\n",
       "49  50    Male    NaN   45.0     43\n",
       "53  54  Female   37.0    NaN     56\n",
       "59  60  Female   38.0    NaN     69"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# finding out rows with missing data\n",
    "data1[data1.isnull().any(axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Say, I have a new dataset on scores of test 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "random.seed(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test = []\n",
    "for i in range(0,100):\n",
    "    id = i+1\n",
    "    test4 = round(np.random.normal(62, 7))\n",
    "    test.append((id, test4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 52), (2, 53), (3, 65)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test[0:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test = pd.DataFrame(test, columns = ['id','test4'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  test4\n",
       "0   1     52\n",
       "1   2     53\n",
       "2   3     65\n",
       "3   4     64\n",
       "4   5     55"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### I want to merge this dataset with the original dataset consisting the three test scores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data2 = pd.merge(data1, test, on = 'id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>100.000000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>50.500000</td>\n",
       "      <td>19.655556</td>\n",
       "      <td>56.077778</td>\n",
       "      <td>57.090000</td>\n",
       "      <td>63.280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>29.011492</td>\n",
       "      <td>10.235608</td>\n",
       "      <td>7.159857</td>\n",
       "      <td>8.001382</td>\n",
       "      <td>6.362898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>25.750000</td>\n",
       "      <td>12.250000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>59.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>50.500000</td>\n",
       "      <td>17.500000</td>\n",
       "      <td>55.500000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>64.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>75.250000</td>\n",
       "      <td>26.750000</td>\n",
       "      <td>60.750000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>67.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>100.000000</td>\n",
       "      <td>41.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>78.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id      test1      test2       test3       test4\n",
       "count  100.000000  90.000000  90.000000  100.000000  100.000000\n",
       "mean    50.500000  19.655556  56.077778   57.090000   63.280000\n",
       "std     29.011492  10.235608   7.159857    8.001382    6.362898\n",
       "min      1.000000   1.000000  36.000000   40.000000   45.000000\n",
       "25%     25.750000  12.250000  52.000000   52.000000   59.000000\n",
       "50%     50.500000  17.500000  55.500000   57.000000   64.000000\n",
       "75%     75.250000  26.750000  60.750000   63.000000   67.000000\n",
       "max    100.000000  41.000000  75.000000   79.000000   78.000000"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Say, I have a batch of student results that was accidentally omitted and now they are provided to me. Now I want to append these results together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# The dataset Class2.csv can be found here: \n",
    "# https://github.com/hxchua/datadoubleconfirm/tree/master/datasets \n",
    "new = pd.read_csv(\"Class2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91</td>\n",
       "      <td>Female</td>\n",
       "      <td>37</td>\n",
       "      <td>53</td>\n",
       "      <td>50</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92</td>\n",
       "      <td>Female</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>59</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>93</td>\n",
       "      <td>Female</td>\n",
       "      <td>43</td>\n",
       "      <td>50</td>\n",
       "      <td>67</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94</td>\n",
       "      <td>Female</td>\n",
       "      <td>37</td>\n",
       "      <td>34</td>\n",
       "      <td>64</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>95</td>\n",
       "      <td>Female</td>\n",
       "      <td>40</td>\n",
       "      <td>60</td>\n",
       "      <td>62</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  gender  test1  test2  test3  test4\n",
       "0  91  Female     37     53     50     75\n",
       "1  92  Female     42     45     59     69\n",
       "2  93  Female     43     50     67     67\n",
       "3  94  Female     37     34     64     65\n",
       "4  95  Female     40     60     62     69"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>140.500000</td>\n",
       "      <td>39.600000</td>\n",
       "      <td>52.960000</td>\n",
       "      <td>56.880000</td>\n",
       "      <td>65.01000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>29.011492</td>\n",
       "      <td>4.915447</td>\n",
       "      <td>8.271797</td>\n",
       "      <td>6.755888</td>\n",
       "      <td>5.34373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>91.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>41.000000</td>\n",
       "      <td>48.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>115.750000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>47.000000</td>\n",
       "      <td>52.750000</td>\n",
       "      <td>62.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>140.500000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>65.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>165.250000</td>\n",
       "      <td>43.000000</td>\n",
       "      <td>58.250000</td>\n",
       "      <td>62.000000</td>\n",
       "      <td>68.25000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>73.000000</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>77.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       test1       test2       test3      test4\n",
       "count  100.000000  100.000000  100.000000  100.000000  100.00000\n",
       "mean   140.500000   39.600000   52.960000   56.880000   65.01000\n",
       "std     29.011492    4.915447    8.271797    6.755888    5.34373\n",
       "min     91.000000   27.000000   34.000000   41.000000   48.00000\n",
       "25%    115.750000   37.000000   47.000000   52.750000   62.00000\n",
       "50%    140.500000   39.000000   52.000000   57.000000   65.00000\n",
       "75%    165.250000   43.000000   58.250000   62.000000   68.25000\n",
       "max    190.000000   52.000000   73.000000   72.000000   77.00000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# note that there are ids similar in Class1 as the minimum id is less than 100 \n",
    "new.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# appending the data together\n",
    "data3 = data2.append(new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>200.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>200.000000</td>\n",
       "      <td>200.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>30.152632</td>\n",
       "      <td>54.436842</td>\n",
       "      <td>56.985000</td>\n",
       "      <td>64.145000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>53.596764</td>\n",
       "      <td>12.715564</td>\n",
       "      <td>7.900419</td>\n",
       "      <td>7.386989</td>\n",
       "      <td>5.924481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>50.750000</td>\n",
       "      <td>18.250000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>61.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>64.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>140.250000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>59.750000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>68.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>78.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       test1       test2       test3       test4\n",
       "count  200.000000  190.000000  190.000000  200.000000  200.000000\n",
       "mean    95.500000   30.152632   54.436842   56.985000   64.145000\n",
       "std     53.596764   12.715564    7.900419    7.386989    5.924481\n",
       "min      1.000000    1.000000   34.000000   40.000000   45.000000\n",
       "25%     50.750000   18.250000   50.000000   52.000000   61.000000\n",
       "50%     95.500000   35.000000   54.000000   57.000000   64.000000\n",
       "75%    140.250000   40.000000   59.750000   63.000000   68.000000\n",
       "max    190.000000   52.000000   75.000000   79.000000   78.000000"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data3.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Male      100\n",
       "Female    100\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data3[\"gender\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We noticed that there are some duplicate records after appending and so we want to drop the older records as, say, we found out that those older records were erroneous."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data4 = data3.drop_duplicates(['id'], keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>180.000000</td>\n",
       "      <td>180.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>30.533333</td>\n",
       "      <td>54.333333</td>\n",
       "      <td>57.052632</td>\n",
       "      <td>64.147368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>54.992424</td>\n",
       "      <td>12.676199</td>\n",
       "      <td>8.047346</td>\n",
       "      <td>7.354038</td>\n",
       "      <td>5.851728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>48.250000</td>\n",
       "      <td>18.750000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>61.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>64.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>142.750000</td>\n",
       "      <td>40.250000</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>68.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>78.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       test1       test2       test3       test4\n",
       "count  190.000000  180.000000  180.000000  190.000000  190.000000\n",
       "mean    95.500000   30.533333   54.333333   57.052632   64.147368\n",
       "std     54.992424   12.676199    8.047346    7.354038    5.851728\n",
       "min      1.000000    1.000000   34.000000   40.000000   45.000000\n",
       "25%     48.250000   18.750000   50.000000   52.000000   61.000000\n",
       "50%     95.500000   36.000000   54.000000   57.000000   64.000000\n",
       "75%    142.750000   40.250000   60.000000   63.000000   68.000000\n",
       "max    190.000000   52.000000   75.000000   79.000000   78.000000"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data4.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Say, I have some students who had missed test 2 decided to retake."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>retest2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  retest2\n",
       "0  24       50\n",
       "1  60       54\n",
       "2  30       52"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retest = {'id': [24, 60, 30],  \n",
    "        'retest2': [50, 54, 52]}\n",
    "retake = pd.DataFrame(retest, columns = ['id', 'retest2'])\n",
    "retake"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>retest2</th>\n",
       "      <th>gender</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24</td>\n",
       "      <td>50</td>\n",
       "      <td>Male</td>\n",
       "      <td>39.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60</td>\n",
       "      <td>54</td>\n",
       "      <td>Female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30</td>\n",
       "      <td>52</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  retest2  gender  test1  test2  test3  test4\n",
       "0  24       50    Male   39.0    NaN     52     64\n",
       "1  60       54  Female   38.0    NaN     69     64\n",
       "2  30       52    Male    NaN    NaN     60     59"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# doing a vlookup\n",
    "base1 = retake.merge(data4, on = 'id', how = 'left')\n",
    "base1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# updating test2 with retest2 scores\n",
    "base1['test2'] = base1['retest2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>retest2</th>\n",
       "      <th>gender</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24</td>\n",
       "      <td>50</td>\n",
       "      <td>Male</td>\n",
       "      <td>39.0</td>\n",
       "      <td>50</td>\n",
       "      <td>52</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60</td>\n",
       "      <td>54</td>\n",
       "      <td>Female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>54</td>\n",
       "      <td>69</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30</td>\n",
       "      <td>52</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52</td>\n",
       "      <td>60</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  retest2  gender  test1  test2  test3  test4\n",
       "0  24       50    Male   39.0     50     52     64\n",
       "1  60       54  Female   38.0     54     69     64\n",
       "2  30       52    Male    NaN     52     60     59"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# dropping restest2 column\n",
    "base1 = base1.drop(['retest2'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# we append the dataset and drop the duplicates again\n",
    "data5 = data4.append(base1)\n",
    "data5 = data5.drop_duplicates(['id'], keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>180.000000</td>\n",
       "      <td>183.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>30.533333</td>\n",
       "      <td>54.295082</td>\n",
       "      <td>57.052632</td>\n",
       "      <td>64.147368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>54.992424</td>\n",
       "      <td>12.676199</td>\n",
       "      <td>7.989026</td>\n",
       "      <td>7.354038</td>\n",
       "      <td>5.851728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>48.250000</td>\n",
       "      <td>18.750000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>61.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>64.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>142.750000</td>\n",
       "      <td>40.250000</td>\n",
       "      <td>59.500000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>68.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>78.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       test1       test2       test3       test4\n",
       "count  190.000000  180.000000  183.000000  190.000000  190.000000\n",
       "mean    95.500000   30.533333   54.295082   57.052632   64.147368\n",
       "std     54.992424   12.676199    7.989026    7.354038    5.851728\n",
       "min      1.000000    1.000000   34.000000   40.000000   45.000000\n",
       "25%     48.250000   18.750000   50.000000   52.000000   61.000000\n",
       "50%     95.500000   36.000000   54.000000   57.000000   64.000000\n",
       "75%    142.750000   40.250000   59.500000   63.000000   68.000000\n",
       "max    190.000000   52.000000   75.000000   79.000000   78.000000"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# note that the count for test2 has increased\n",
    "data5.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>54</td>\n",
       "      <td>Female</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24</td>\n",
       "      <td>Male</td>\n",
       "      <td>39.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>52</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>60</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  gender  test1  test2  test3  test4\n",
       "53  54  Female   37.0    NaN     56     64\n",
       "0   24    Male   39.0   50.0     52     64\n",
       "2   30    Male    NaN   52.0     60     59"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check that the correct records are kept\n",
    "data5.loc[data5['id'].isin([24, 30, 54])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sometimes, we want to replace missing data with zero (but note that this will skew the statistics) or the column mean (this will retain the distribution)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Male</td>\n",
       "      <td>12.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>50</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Male</td>\n",
       "      <td>34.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>51</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Male</td>\n",
       "      <td>23.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>63</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Male</td>\n",
       "      <td>15.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>62</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>68</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id gender  test1  test2  test3  test4\n",
       "0   1   Male   12.0   56.0     50     52\n",
       "1   2   Male   34.0   58.0     51     53\n",
       "2   3   Male   23.0   61.0     63     65\n",
       "3   4   Male   15.0   67.0     62     64\n",
       "4   5   Male    NaN   37.0     68     55"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data6 = data5\n",
    "data6.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# if we want to replace missing values with zero\n",
    "data6 = data6.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>28.926316</td>\n",
       "      <td>52.294737</td>\n",
       "      <td>57.052632</td>\n",
       "      <td>64.147368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>54.992424</td>\n",
       "      <td>14.103729</td>\n",
       "      <td>12.908201</td>\n",
       "      <td>7.354038</td>\n",
       "      <td>5.851728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>48.250000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>49.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>61.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>34.500000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>64.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>142.750000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>59.000000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>68.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>78.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       test1       test2       test3       test4\n",
       "count  190.000000  190.000000  190.000000  190.000000  190.000000\n",
       "mean    95.500000   28.926316   52.294737   57.052632   64.147368\n",
       "std     54.992424   14.103729   12.908201    7.354038    5.851728\n",
       "min      1.000000    0.000000    0.000000   40.000000   45.000000\n",
       "25%     48.250000   17.000000   49.000000   52.000000   61.000000\n",
       "50%     95.500000   34.500000   54.000000   57.000000   64.000000\n",
       "75%    142.750000   40.000000   59.000000   63.000000   68.000000\n",
       "max    190.000000   52.000000   75.000000   79.000000   78.000000"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the statistics for test1 and test2 has changed\n",
    "data6.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Male</td>\n",
       "      <td>12.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>50</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Male</td>\n",
       "      <td>34.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>51</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Male</td>\n",
       "      <td>23.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>63</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Male</td>\n",
       "      <td>15.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>62</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>68</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id gender  test1  test2  test3  test4\n",
       "0   1   Male   12.0   56.0     50     52\n",
       "1   2   Male   34.0   58.0     51     53\n",
       "2   3   Male   23.0   61.0     63     65\n",
       "3   4   Male   15.0   67.0     62     64\n",
       "4   5   Male    NaN   37.0     68     55"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data7 = data5.copy()\n",
    "data7.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# if we want to replace missing values with column mean \n",
    "data7[\"test1\"].fillna(data7[\"test1\"].mean(), inplace=True)\n",
    "data7[\"test2\"].fillna(data7[\"test2\"].mean(), inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>30.533333</td>\n",
       "      <td>54.295082</td>\n",
       "      <td>57.052632</td>\n",
       "      <td>64.147368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>54.992424</td>\n",
       "      <td>12.336293</td>\n",
       "      <td>7.839685</td>\n",
       "      <td>7.354038</td>\n",
       "      <td>5.851728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>48.250000</td>\n",
       "      <td>20.250000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>61.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>34.500000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>64.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>142.750000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>59.000000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>68.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>78.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       test1       test2       test3       test4\n",
       "count  190.000000  190.000000  190.000000  190.000000  190.000000\n",
       "mean    95.500000   30.533333   54.295082   57.052632   64.147368\n",
       "std     54.992424   12.336293    7.839685    7.354038    5.851728\n",
       "min      1.000000    1.000000   34.000000   40.000000   45.000000\n",
       "25%     48.250000   20.250000   50.000000   52.000000   61.000000\n",
       "50%     95.500000   34.500000   54.000000   57.000000   64.000000\n",
       "75%    142.750000   40.000000   59.000000   63.000000   68.000000\n",
       "max    190.000000   52.000000   75.000000   79.000000   78.000000"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the statistics for test1 and test2 has changed\n",
    "data7.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We might also want to recode some values. For example, there is a moderation in scores and the minimum score for test 1 is moderated to be 15."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>180.000000</td>\n",
       "      <td>183.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>30.533333</td>\n",
       "      <td>54.295082</td>\n",
       "      <td>57.052632</td>\n",
       "      <td>64.147368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>54.992424</td>\n",
       "      <td>12.676199</td>\n",
       "      <td>7.989026</td>\n",
       "      <td>7.354038</td>\n",
       "      <td>5.851728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>48.250000</td>\n",
       "      <td>18.750000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>61.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>64.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>142.750000</td>\n",
       "      <td>40.250000</td>\n",
       "      <td>59.500000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>68.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>78.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       test1       test2       test3       test4\n",
       "count  190.000000  180.000000  183.000000  190.000000  190.000000\n",
       "mean    95.500000   30.533333   54.295082   57.052632   64.147368\n",
       "std     54.992424   12.676199    7.989026    7.354038    5.851728\n",
       "min      1.000000    1.000000   34.000000   40.000000   45.000000\n",
       "25%     48.250000   18.750000   50.000000   52.000000   61.000000\n",
       "50%     95.500000   36.000000   54.000000   57.000000   64.000000\n",
       "75%    142.750000   40.250000   59.500000   63.000000   68.000000\n",
       "max    190.000000   52.000000   75.000000   79.000000   78.000000"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data8 = data5\n",
    "data8.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data8['test1'] = data8['test1'].mask(data8['test1'] < 15, 15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>test1</th>\n",
       "      <th>test2</th>\n",
       "      <th>test3</th>\n",
       "      <th>test4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>180.000000</td>\n",
       "      <td>183.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>31.450000</td>\n",
       "      <td>54.295082</td>\n",
       "      <td>57.052632</td>\n",
       "      <td>64.147368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>54.992424</td>\n",
       "      <td>11.112565</td>\n",
       "      <td>7.989026</td>\n",
       "      <td>7.354038</td>\n",
       "      <td>5.851728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>48.250000</td>\n",
       "      <td>18.750000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>61.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>95.500000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>64.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>142.750000</td>\n",
       "      <td>40.250000</td>\n",
       "      <td>59.500000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>68.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>190.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>78.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       test1       test2       test3       test4\n",
       "count  190.000000  180.000000  183.000000  190.000000  190.000000\n",
       "mean    95.500000   31.450000   54.295082   57.052632   64.147368\n",
       "std     54.992424   11.112565    7.989026    7.354038    5.851728\n",
       "min      1.000000   15.000000   34.000000   40.000000   45.000000\n",
       "25%     48.250000   18.750000   50.000000   52.000000   61.000000\n",
       "50%     95.500000   36.000000   54.000000   57.000000   64.000000\n",
       "75%    142.750000   40.250000   59.500000   63.000000   68.000000\n",
       "max    190.000000   52.000000   75.000000   79.000000   78.000000"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data8.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Male      100\n",
       "Female     90\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data8[\"gender\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### References for alternative solutions to filtering and recoding \n",
    "https://stackoverflow.com/questions/17071871/select-rows-from-a-dataframe-based-on-values-in-a-column-in-pandas  \n",
    "https://stackoverflow.com/questions/46168450/replace-specific-range-of-values-in-data-frame-pandas"
   ]
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}