2-Refine.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Refine the Data\n",
    " \n",
    "> \"Data is messy\"\n",
    "\n",
    "- **Missing** e.g. Check for missing or incomplete data\n",
    "- **Quality** e.g. Check for duplicates, accuracy, unusual data\n",
    "- **Parse** e.g. extract year and month from date\n",
    "- **Convert** e.g. free text to coded value\n",
    "- **Derive** e.g. gender from title\n",
    "- **Calculate** e.g. percentages, proportion\n",
    "- **Remove** e.g. remove redundant data\n",
    "- **Merge** e.g. first and surname for full name\n",
    "- **Aggregate** e.g. rollup by year, cluster by area\n",
    "- **Filter** e.g. exclude based on location\n",
    "- **Sample** e.g. extract a representative data\n",
    "- **Summary** e.g. show summary stats like mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Load the libraries\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Load the data again!\n",
    "df = pd.read_csv(\"data/Weed_Price.csv\", parse_dates=[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 Missing Data\n",
    "\n",
    "By “missing” data we simply mean null or “not present for whatever reason”. Lets see if we can find the missing data in our data set either because it exists and was not collected or it never existed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State     22899\n",
       "HighQ     22899\n",
       "HighQN    22899\n",
       "MedQ      22899\n",
       "MedQN     22899\n",
       "LowQ      12342\n",
       "LowQN     22899\n",
       "date      22899\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lets start the count to seeing about missing data\n",
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        False\n",
       "1        False\n",
       "2        False\n",
       "3        False\n",
       "4        False\n",
       "5        False\n",
       "6        False\n",
       "7        False\n",
       "8        False\n",
       "9        False\n",
       "10       False\n",
       "11       False\n",
       "12       False\n",
       "13       False\n",
       "14       False\n",
       "15       False\n",
       "16       False\n",
       "17       False\n",
       "18       False\n",
       "19       False\n",
       "20       False\n",
       "21       False\n",
       "22       False\n",
       "23       False\n",
       "24       False\n",
       "25       False\n",
       "26       False\n",
       "27       False\n",
       "28       False\n",
       "29       False\n",
       "         ...  \n",
       "22869     True\n",
       "22870     True\n",
       "22871     True\n",
       "22872     True\n",
       "22873     True\n",
       "22874     True\n",
       "22875     True\n",
       "22876     True\n",
       "22877     True\n",
       "22878     True\n",
       "22879     True\n",
       "22880     True\n",
       "22881     True\n",
       "22882     True\n",
       "22883     True\n",
       "22884     True\n",
       "22885     True\n",
       "22886     True\n",
       "22887     True\n",
       "22888     True\n",
       "22889     True\n",
       "22890     True\n",
       "22891     True\n",
       "22892     True\n",
       "22893     True\n",
       "22894     True\n",
       "22895     True\n",
       "22896     True\n",
       "22897     True\n",
       "22898     True\n",
       "Name: LowQ, dtype: bool"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['LowQ'].isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>HighQ</th>\n",
       "      <th>HighQN</th>\n",
       "      <th>MedQ</th>\n",
       "      <th>MedQN</th>\n",
       "      <th>LowQ</th>\n",
       "      <th>LowQN</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22894</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>364.98</td>\n",
       "      <td>3513</td>\n",
       "      <td>293.12</td>\n",
       "      <td>3079</td>\n",
       "      <td>NaN</td>\n",
       "      <td>284</td>\n",
       "      <td>2014-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22895</th>\n",
       "      <td>Washington</td>\n",
       "      <td>233.05</td>\n",
       "      <td>3337</td>\n",
       "      <td>189.92</td>\n",
       "      <td>3562</td>\n",
       "      <td>NaN</td>\n",
       "      <td>160</td>\n",
       "      <td>2014-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22896</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>359.35</td>\n",
       "      <td>551</td>\n",
       "      <td>224.03</td>\n",
       "      <td>545</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60</td>\n",
       "      <td>2014-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22897</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>350.52</td>\n",
       "      <td>2244</td>\n",
       "      <td>272.71</td>\n",
       "      <td>2221</td>\n",
       "      <td>NaN</td>\n",
       "      <td>167</td>\n",
       "      <td>2014-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22898</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>322.27</td>\n",
       "      <td>131</td>\n",
       "      <td>351.86</td>\n",
       "      <td>197</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>2014-12-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               State   HighQ  HighQN    MedQ  MedQN  LowQ  LowQN       date\n",
       "22894       Virginia  364.98    3513  293.12   3079   NaN    284 2014-12-31\n",
       "22895     Washington  233.05    3337  189.92   3562   NaN    160 2014-12-31\n",
       "22896  West Virginia  359.35     551  224.03    545   NaN     60 2014-12-31\n",
       "22897      Wisconsin  350.52    2244  272.71   2221   NaN    167 2014-12-31\n",
       "22898        Wyoming  322.27     131  351.86    197   NaN     12 2014-12-31"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can see the bottom rows which have NaN values\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas will represent missing value by NaN**\n",
    "\n",
    "What can we do this with missing value?\n",
    "- Drop these rows / columns? Use `.dropna(how='any')`\n",
    "- Fill with a dummy value? Use `.fillna(value=dummy)`\n",
    "- Impute the cell with the most recent value? Use `.fillna(method='ffill')`\n",
    "- Interpolate the amount in a linear fashion? Use `.interpolate()`\n",
    "\n",
    "We use the `inplace = True` operator to avoid making a copy of the dataframe and changing the dataframe itself"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Lets sort this data frame by State and Date\n",
    "df.sort(columns=['State','date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Lets fill the missing value with last available value\n",
    "df.fillna(method = \"ffill\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State     22899\n",
       "HighQ     22899\n",
       "HighQN    22899\n",
       "MedQ      22899\n",
       "MedQN     22899\n",
       "LowQ      22899\n",
       "LowQN     22899\n",
       "date      22899\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "Fill the missing value with a backward fill."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fill the missing values with the mean for the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 Quality of the Data \n",
    "\n",
    "Lets check for completeness.\n",
    "\n",
    "**Say, do we have data on each date for all the 51 states?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Utah                    449\n",
       "Washington              449\n",
       "Massachusetts           449\n",
       "Missouri                449\n",
       "Connecticut             449\n",
       "Vermont                 449\n",
       "Pennsylvania            449\n",
       "Georgia                 449\n",
       "California              449\n",
       "North Carolina          449\n",
       "Florida                 449\n",
       "Oregon                  449\n",
       "Alabama                 449\n",
       "Indiana                 449\n",
       "Kentucky                449\n",
       "Hawaii                  449\n",
       "South Carolina          449\n",
       "Virginia                449\n",
       "Oklahoma                449\n",
       "Texas                   449\n",
       "Illinois                449\n",
       "Nebraska                449\n",
       "Ohio                    449\n",
       "Arizona                 449\n",
       "Rhode Island            449\n",
       "Louisiana               449\n",
       "Wisconsin               449\n",
       "New York                449\n",
       "Arkansas                449\n",
       "Montana                 449\n",
       "New Hampshire           449\n",
       "North Dakota            449\n",
       "New Mexico              449\n",
       "Delaware                449\n",
       "Iowa                    449\n",
       "New Jersey              449\n",
       "Kansas                  449\n",
       "Idaho                   449\n",
       "Colorado                449\n",
       "Maryland                449\n",
       "District of Columbia    449\n",
       "Michigan                449\n",
       "South Dakota            449\n",
       "West Virginia           449\n",
       "Minnesota               449\n",
       "Wyoming                 449\n",
       "Mississippi             449\n",
       "Tennessee               449\n",
       "Nevada                  449\n",
       "Maine                   449\n",
       "Alaska                  449\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"State\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lets check the dates and see if they are all continuous**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1 = df[df.State=='California'].copy()\n",
    "df2 = df[df.State=='California'].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(449, 8)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Earliest Date: 2013-12-27 00:00:00 \n",
      "Latest Date: 2015-06-11 00:00:00 \n",
      "Number of days in between them: 531 days 00:00:00\n"
     ]
    }
   ],
   "source": [
    "print \"Earliest Date:\",df1.date.min(), \"\\n\", \"Latest Date:\",df1.date.max(), \"\\n\",  \\\n",
    "\"Number of days in between them:\", df1.date.max() - df1.date.min() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2013-12-27    1\n",
       "2013-12-28    1\n",
       "2013-12-29    1\n",
       "2013-12-30    1\n",
       "2013-12-31    1\n",
       "2014-01-01    1\n",
       "2014-01-02    1\n",
       "2014-01-03    1\n",
       "2014-01-04    1\n",
       "2014-01-05    1\n",
       "2014-01-06    1\n",
       "2014-01-07    1\n",
       "2014-01-08    1\n",
       "2014-01-09    1\n",
       "2014-01-10    1\n",
       "2014-01-11    1\n",
       "2014-01-12    1\n",
       "2014-01-13    1\n",
       "2014-01-14    1\n",
       "2014-01-15    1\n",
       "2014-01-16    1\n",
       "2014-01-17    1\n",
       "2014-01-18    1\n",
       "2014-01-19    1\n",
       "2014-01-20    1\n",
       "2014-01-21    1\n",
       "2014-01-22    1\n",
       "2014-01-23    1\n",
       "2014-01-24    1\n",
       "2014-01-25    1\n",
       "             ..\n",
       "2015-05-13    1\n",
       "2015-05-14    1\n",
       "2015-05-15    1\n",
       "2015-05-16    1\n",
       "2015-05-17    1\n",
       "2015-05-18    1\n",
       "2015-05-19    1\n",
       "2015-05-20    1\n",
       "2015-05-21    1\n",
       "2015-05-22    1\n",
       "2015-05-23    1\n",
       "2015-05-24    1\n",
       "2015-05-25    1\n",
       "2015-05-26    1\n",
       "2015-05-27    1\n",
       "2015-05-28    1\n",
       "2015-05-29    1\n",
       "2015-05-30    1\n",
       "2015-05-31    1\n",
       "2015-06-01    1\n",
       "2015-06-02    1\n",
       "2015-06-03    1\n",
       "2015-06-04    1\n",
       "2015-06-05    1\n",
       "2015-06-06    1\n",
       "2015-06-07    1\n",
       "2015-06-08    1\n",
       "2015-06-09    1\n",
       "2015-06-10    1\n",
       "2015-06-11    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.groupby(['date']).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.set_index(\"date\", inplace=True)\n",
    "df2.set_index(\"date\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "idx = pd.date_range(df1.index.min(), df1.index.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2013-12-27', '2013-12-28', '2013-12-29', '2013-12-30',\n",
       "               '2013-12-31', '2014-01-01', '2014-01-02', '2014-01-03',\n",
       "               '2014-01-04', '2014-01-05', \n",
       "               ...\n",
       "               '2015-06-02', '2015-06-03', '2015-06-04', '2015-06-05',\n",
       "               '2015-06-06', '2015-06-07', '2015-06-08', '2015-06-09',\n",
       "               '2015-06-10', '2015-06-11'],\n",
       "              dtype='datetime64[ns]', length=532, freq='D', tz=None)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1 = df1.reindex(idx, fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(532, 7)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise** Show the list of dates that were missing. *Hint* Leverage df2. Both df1 and df2 have `date` as index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3  Parse the Data\n",
    "\n",
    "Lets see if we can get the year, month, week and weekdays from the date. Pandas has got good built in functionality for timeseries data using the DatetimeIndex method "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['year'] = pd.DatetimeIndex(df['date']).year\n",
    "df['month'] = pd.DatetimeIndex(df['date']).month\n",
    "df['week'] = pd.DatetimeIndex(df['date']).week\n",
    "df['weekday'] = pd.DatetimeIndex(df['date']).weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State              object\n",
       "HighQ             float64\n",
       "HighQN              int64\n",
       "MedQ              float64\n",
       "MedQN               int64\n",
       "LowQ              float64\n",
       "LowQN               int64\n",
       "date       datetime64[ns]\n",
       "year                int32\n",
       "month               int32\n",
       "week                int32\n",
       "weekday             int32\n",
       "dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>HighQ</th>\n",
       "      <th>HighQN</th>\n",
       "      <th>MedQ</th>\n",
       "      <th>MedQN</th>\n",
       "      <th>LowQ</th>\n",
       "      <th>LowQN</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>week</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4997</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>313.72</td>\n",
       "      <td>148</td>\n",
       "      <td>317.38</td>\n",
       "      <td>226</td>\n",
       "      <td>161.3</td>\n",
       "      <td>13</td>\n",
       "      <td>2015-06-07</td>\n",
       "      <td>2015</td>\n",
       "      <td>6</td>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5762</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>313.72</td>\n",
       "      <td>148</td>\n",
       "      <td>317.38</td>\n",
       "      <td>226</td>\n",
       "      <td>161.3</td>\n",
       "      <td>13</td>\n",
       "      <td>2015-06-08</td>\n",
       "      <td>2015</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6527</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>313.72</td>\n",
       "      <td>148</td>\n",
       "      <td>317.38</td>\n",
       "      <td>226</td>\n",
       "      <td>161.3</td>\n",
       "      <td>13</td>\n",
       "      <td>2015-06-09</td>\n",
       "      <td>2015</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7343</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>313.72</td>\n",
       "      <td>148</td>\n",
       "      <td>317.38</td>\n",
       "      <td>226</td>\n",
       "      <td>161.3</td>\n",
       "      <td>13</td>\n",
       "      <td>2015-06-10</td>\n",
       "      <td>2015</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8159</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>313.72</td>\n",
       "      <td>148</td>\n",
       "      <td>317.38</td>\n",
       "      <td>226</td>\n",
       "      <td>161.3</td>\n",
       "      <td>13</td>\n",
       "      <td>2015-06-11</td>\n",
       "      <td>2015</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State   HighQ  HighQN    MedQ  MedQN   LowQ  LowQN       date  year  \\\n",
       "4997  Wyoming  313.72     148  317.38    226  161.3     13 2015-06-07  2015   \n",
       "5762  Wyoming  313.72     148  317.38    226  161.3     13 2015-06-08  2015   \n",
       "6527  Wyoming  313.72     148  317.38    226  161.3     13 2015-06-09  2015   \n",
       "7343  Wyoming  313.72     148  317.38    226  161.3     13 2015-06-10  2015   \n",
       "8159  Wyoming  313.72     148  317.38    226  161.3     13 2015-06-11  2015   \n",
       "\n",
       "      month  week  weekday  \n",
       "4997      6    23        6  \n",
       "5762      6    24        0  \n",
       "6527      6    24        1  \n",
       "7343      6    24        2  \n",
       "8159      6    24        3  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2014    18564\n",
       "2015     4080\n",
       "2013      255\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['year'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1     3162\n",
       "5     2703\n",
       "2     2244\n",
       "6     2091\n",
       "12    1836\n",
       "10    1581\n",
       "7     1581\n",
       "3     1581\n",
       "11    1530\n",
       "9     1530\n",
       "8     1530\n",
       "4     1530\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['month'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6    3315\n",
       "0    3315\n",
       "4    3264\n",
       "3    3264\n",
       "2    3264\n",
       "1    3264\n",
       "5    3213\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"weekday\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.4 Aggregate the Data\n",
    "\n",
    "To aggregate, we typically use the “group by” function, which involves the following steps\n",
    "\n",
    "- Splitting the data into groups based on some criteria\n",
    "- Applying a function to each group independently\n",
    "- Combining the results into a data structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_mean = df.groupby(\"State\", as_index=False).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(51, 11)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mean.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>HighQ</th>\n",
       "      <th>HighQN</th>\n",
       "      <th>MedQ</th>\n",
       "      <th>MedQN</th>\n",
       "      <th>LowQ</th>\n",
       "      <th>LowQN</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>week</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>339.561849</td>\n",
       "      <td>1379.414254</td>\n",
       "      <td>204.606169</td>\n",
       "      <td>1270.351893</td>\n",
       "      <td>145.978508</td>\n",
       "      <td>161.149220</td>\n",
       "      <td>2014.167038</td>\n",
       "      <td>5.953229</td>\n",
       "      <td>23.812918</td>\n",
       "      <td>2.995546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>291.482004</td>\n",
       "      <td>321.244989</td>\n",
       "      <td>262.046392</td>\n",
       "      <td>407.917595</td>\n",
       "      <td>394.653964</td>\n",
       "      <td>32.334076</td>\n",
       "      <td>2014.167038</td>\n",
       "      <td>5.953229</td>\n",
       "      <td>23.812918</td>\n",
       "      <td>2.995546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>300.667483</td>\n",
       "      <td>2392.465479</td>\n",
       "      <td>209.365345</td>\n",
       "      <td>2137.414254</td>\n",
       "      <td>188.500134</td>\n",
       "      <td>279.006682</td>\n",
       "      <td>2014.167038</td>\n",
       "      <td>5.953229</td>\n",
       "      <td>23.812918</td>\n",
       "      <td>2.995546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>348.056147</td>\n",
       "      <td>751.988864</td>\n",
       "      <td>190.414655</td>\n",
       "      <td>724.683742</td>\n",
       "      <td>126.771269</td>\n",
       "      <td>135.902004</td>\n",
       "      <td>2014.167038</td>\n",
       "      <td>5.953229</td>\n",
       "      <td>23.812918</td>\n",
       "      <td>2.995546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>245.376125</td>\n",
       "      <td>14947.073497</td>\n",
       "      <td>191.268909</td>\n",
       "      <td>16769.821826</td>\n",
       "      <td>189.783586</td>\n",
       "      <td>976.298441</td>\n",
       "      <td>2014.167038</td>\n",
       "      <td>5.953229</td>\n",
       "      <td>23.812918</td>\n",
       "      <td>2.995546</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State       HighQ        HighQN        MedQ         MedQN        LowQ  \\\n",
       "0     Alabama  339.561849   1379.414254  204.606169   1270.351893  145.978508   \n",
       "1      Alaska  291.482004    321.244989  262.046392    407.917595  394.653964   \n",
       "2     Arizona  300.667483   2392.465479  209.365345   2137.414254  188.500134   \n",
       "3    Arkansas  348.056147    751.988864  190.414655    724.683742  126.771269   \n",
       "4  California  245.376125  14947.073497  191.268909  16769.821826  189.783586   \n",
       "\n",
       "        LowQN         year     month       week   weekday  \n",
       "0  161.149220  2014.167038  5.953229  23.812918  2.995546  \n",
       "1   32.334076  2014.167038  5.953229  23.812918  2.995546  \n",
       "2  279.006682  2014.167038  5.953229  23.812918  2.995546  \n",
       "3  135.902004  2014.167038  5.953229  23.812918  2.995546  \n",
       "4  976.298441  2014.167038  5.953229  23.812918  2.995546  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pivot Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>HighQ</th>\n",
       "      <th>HighQN</th>\n",
       "      <th>MedQ</th>\n",
       "      <th>MedQN</th>\n",
       "      <th>LowQ</th>\n",
       "      <th>LowQN</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>week</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20094</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>339.65</td>\n",
       "      <td>1033</td>\n",
       "      <td>198.04</td>\n",
       "      <td>926</td>\n",
       "      <td>147.15</td>\n",
       "      <td>122</td>\n",
       "      <td>2013-12-27</td>\n",
       "      <td>2013</td>\n",
       "      <td>12</td>\n",
       "      <td>52</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20859</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>339.65</td>\n",
       "      <td>1033</td>\n",
       "      <td>198.04</td>\n",
       "      <td>926</td>\n",
       "      <td>147.15</td>\n",
       "      <td>122</td>\n",
       "      <td>2013-12-28</td>\n",
       "      <td>2013</td>\n",
       "      <td>12</td>\n",
       "      <td>52</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21573</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>339.75</td>\n",
       "      <td>1036</td>\n",
       "      <td>198.26</td>\n",
       "      <td>929</td>\n",
       "      <td>149.49</td>\n",
       "      <td>123</td>\n",
       "      <td>2013-12-29</td>\n",
       "      <td>2013</td>\n",
       "      <td>12</td>\n",
       "      <td>52</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22287</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>339.75</td>\n",
       "      <td>1036</td>\n",
       "      <td>198.81</td>\n",
       "      <td>930</td>\n",
       "      <td>149.49</td>\n",
       "      <td>123</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>2013</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22797</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>339.42</td>\n",
       "      <td>1040</td>\n",
       "      <td>198.68</td>\n",
       "      <td>932</td>\n",
       "      <td>149.49</td>\n",
       "      <td>123</td>\n",
       "      <td>2013-12-31</td>\n",
       "      <td>2013</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         State   HighQ  HighQN    MedQ  MedQN    LowQ  LowQN       date  year  \\\n",
       "20094  Alabama  339.65    1033  198.04    926  147.15    122 2013-12-27  2013   \n",
       "20859  Alabama  339.65    1033  198.04    926  147.15    122 2013-12-28  2013   \n",
       "21573  Alabama  339.75    1036  198.26    929  149.49    123 2013-12-29  2013   \n",
       "22287  Alabama  339.75    1036  198.81    930  149.49    123 2013-12-30  2013   \n",
       "22797  Alabama  339.42    1040  198.68    932  149.49    123 2013-12-31  2013   \n",
       "\n",
       "       month  week  weekday  \n",
       "20094     12    52        4  \n",
       "20859     12    52        5  \n",
       "21573     12    52        6  \n",
       "22287     12     1        0  \n",
       "22797     12     1        1  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>weekday</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama</th>\n",
       "      <td>339.556308</td>\n",
       "      <td>339.577656</td>\n",
       "      <td>339.559375</td>\n",
       "      <td>339.552500</td>\n",
       "      <td>339.577969</td>\n",
       "      <td>339.568254</td>\n",
       "      <td>339.541385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alaska</th>\n",
       "      <td>291.463231</td>\n",
       "      <td>291.384687</td>\n",
       "      <td>291.390000</td>\n",
       "      <td>291.597500</td>\n",
       "      <td>291.506406</td>\n",
       "      <td>291.596032</td>\n",
       "      <td>291.438923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>300.612000</td>\n",
       "      <td>300.653906</td>\n",
       "      <td>300.643750</td>\n",
       "      <td>300.655000</td>\n",
       "      <td>300.751562</td>\n",
       "      <td>300.715873</td>\n",
       "      <td>300.642308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>347.930462</td>\n",
       "      <td>348.011250</td>\n",
       "      <td>348.017656</td>\n",
       "      <td>347.934844</td>\n",
       "      <td>348.278281</td>\n",
       "      <td>348.235238</td>\n",
       "      <td>347.991077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>245.348923</td>\n",
       "      <td>245.364375</td>\n",
       "      <td>245.359219</td>\n",
       "      <td>245.342813</td>\n",
       "      <td>245.434219</td>\n",
       "      <td>245.425556</td>\n",
       "      <td>245.359231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>238.948462</td>\n",
       "      <td>238.885938</td>\n",
       "      <td>238.889844</td>\n",
       "      <td>238.977187</td>\n",
       "      <td>238.885937</td>\n",
       "      <td>238.897619</td>\n",
       "      <td>238.944769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Connecticut</th>\n",
       "      <td>341.624615</td>\n",
       "      <td>341.682812</td>\n",
       "      <td>341.633750</td>\n",
       "      <td>341.616406</td>\n",
       "      <td>341.832656</td>\n",
       "      <td>341.819683</td>\n",
       "      <td>341.652308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Delaware</th>\n",
       "      <td>366.712769</td>\n",
       "      <td>366.748750</td>\n",
       "      <td>366.705937</td>\n",
       "      <td>366.659375</td>\n",
       "      <td>366.962344</td>\n",
       "      <td>366.937143</td>\n",
       "      <td>366.750615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District of Columbia</th>\n",
       "      <td>348.205385</td>\n",
       "      <td>348.055000</td>\n",
       "      <td>348.132656</td>\n",
       "      <td>348.145625</td>\n",
       "      <td>348.213594</td>\n",
       "      <td>348.277778</td>\n",
       "      <td>348.212462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Florida</th>\n",
       "      <td>302.548923</td>\n",
       "      <td>302.577031</td>\n",
       "      <td>302.529688</td>\n",
       "      <td>302.510781</td>\n",
       "      <td>302.638437</td>\n",
       "      <td>302.648095</td>\n",
       "      <td>302.541231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Georgia</th>\n",
       "      <td>326.588308</td>\n",
       "      <td>326.635937</td>\n",
       "      <td>326.594219</td>\n",
       "      <td>326.579687</td>\n",
       "      <td>326.805000</td>\n",
       "      <td>326.784762</td>\n",
       "      <td>326.633077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hawaii</th>\n",
       "      <td>309.940615</td>\n",
       "      <td>309.895312</td>\n",
       "      <td>309.855156</td>\n",
       "      <td>309.818750</td>\n",
       "      <td>309.934062</td>\n",
       "      <td>310.033016</td>\n",
       "      <td>309.956462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idaho</th>\n",
       "      <td>275.642615</td>\n",
       "      <td>275.627656</td>\n",
       "      <td>275.609844</td>\n",
       "      <td>275.653437</td>\n",
       "      <td>275.671250</td>\n",
       "      <td>275.654444</td>\n",
       "      <td>275.616923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Illinois</th>\n",
       "      <td>355.406769</td>\n",
       "      <td>355.460313</td>\n",
       "      <td>355.442031</td>\n",
       "      <td>355.427188</td>\n",
       "      <td>355.580625</td>\n",
       "      <td>355.502540</td>\n",
       "      <td>355.426000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indiana</th>\n",
       "      <td>333.147077</td>\n",
       "      <td>333.184531</td>\n",
       "      <td>333.187344</td>\n",
       "      <td>333.145781</td>\n",
       "      <td>333.257031</td>\n",
       "      <td>333.253333</td>\n",
       "      <td>333.164769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iowa</th>\n",
       "      <td>367.059692</td>\n",
       "      <td>367.086094</td>\n",
       "      <td>367.071563</td>\n",
       "      <td>367.055625</td>\n",
       "      <td>367.175781</td>\n",
       "      <td>367.173175</td>\n",
       "      <td>367.051077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kansas</th>\n",
       "      <td>350.760615</td>\n",
       "      <td>350.785469</td>\n",
       "      <td>350.765938</td>\n",
       "      <td>350.778438</td>\n",
       "      <td>350.928906</td>\n",
       "      <td>350.903492</td>\n",
       "      <td>350.813846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kentucky</th>\n",
       "      <td>338.906615</td>\n",
       "      <td>338.857812</td>\n",
       "      <td>338.846719</td>\n",
       "      <td>338.874062</td>\n",
       "      <td>338.796719</td>\n",
       "      <td>338.799048</td>\n",
       "      <td>338.902769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Louisiana</th>\n",
       "      <td>366.743385</td>\n",
       "      <td>366.816562</td>\n",
       "      <td>366.761094</td>\n",
       "      <td>366.733437</td>\n",
       "      <td>367.016406</td>\n",
       "      <td>366.960159</td>\n",
       "      <td>366.800615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maine</th>\n",
       "      <td>315.272615</td>\n",
       "      <td>315.392812</td>\n",
       "      <td>315.384844</td>\n",
       "      <td>315.330156</td>\n",
       "      <td>315.570469</td>\n",
       "      <td>315.523333</td>\n",
       "      <td>315.262308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maryland</th>\n",
       "      <td>370.884308</td>\n",
       "      <td>370.970469</td>\n",
       "      <td>370.912187</td>\n",
       "      <td>370.899219</td>\n",
       "      <td>371.160937</td>\n",
       "      <td>371.162063</td>\n",
       "      <td>370.912462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Massachusetts</th>\n",
       "      <td>348.942462</td>\n",
       "      <td>348.995625</td>\n",
       "      <td>348.968750</td>\n",
       "      <td>348.945625</td>\n",
       "      <td>349.156562</td>\n",
       "      <td>349.100952</td>\n",
       "      <td>348.974615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Michigan</th>\n",
       "      <td>292.022462</td>\n",
       "      <td>292.080312</td>\n",
       "      <td>292.044531</td>\n",
       "      <td>292.004844</td>\n",
       "      <td>292.229844</td>\n",
       "      <td>292.199841</td>\n",
       "      <td>292.055077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Minnesota</th>\n",
       "      <td>358.104154</td>\n",
       "      <td>358.228437</td>\n",
       "      <td>358.188594</td>\n",
       "      <td>358.052969</td>\n",
       "      <td>358.507969</td>\n",
       "      <td>358.471587</td>\n",
       "      <td>358.156308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mississippi</th>\n",
       "      <td>337.535385</td>\n",
       "      <td>337.479062</td>\n",
       "      <td>337.495469</td>\n",
       "      <td>337.532656</td>\n",
       "      <td>337.615937</td>\n",
       "      <td>337.660635</td>\n",
       "      <td>337.597231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Missouri</th>\n",
       "      <td>359.098923</td>\n",
       "      <td>359.278594</td>\n",
       "      <td>359.273594</td>\n",
       "      <td>359.264844</td>\n",
       "      <td>359.418438</td>\n",
       "      <td>359.434921</td>\n",
       "      <td>359.174000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montana</th>\n",
       "      <td>267.832615</td>\n",
       "      <td>267.860625</td>\n",
       "      <td>267.713125</td>\n",
       "      <td>267.700937</td>\n",
       "      <td>267.930781</td>\n",
       "      <td>267.934444</td>\n",
       "      <td>267.850923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nebraska</th>\n",
       "      <td>341.441692</td>\n",
       "      <td>341.522187</td>\n",
       "      <td>341.490000</td>\n",
       "      <td>341.479375</td>\n",
       "      <td>341.768438</td>\n",
       "      <td>341.713968</td>\n",
       "      <td>341.504923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nevada</th>\n",
       "      <td>267.794769</td>\n",
       "      <td>267.827969</td>\n",
       "      <td>267.823594</td>\n",
       "      <td>267.811875</td>\n",
       "      <td>267.866875</td>\n",
       "      <td>267.824286</td>\n",
       "      <td>267.792308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Hampshire</th>\n",
       "      <td>359.469846</td>\n",
       "      <td>359.543906</td>\n",
       "      <td>359.519844</td>\n",
       "      <td>359.362656</td>\n",
       "      <td>359.595469</td>\n",
       "      <td>359.512222</td>\n",
       "      <td>359.331231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Jersey</th>\n",
       "      <td>349.260769</td>\n",
       "      <td>349.313594</td>\n",
       "      <td>349.299219</td>\n",
       "      <td>349.272969</td>\n",
       "      <td>349.435469</td>\n",
       "      <td>349.396667</td>\n",
       "      <td>349.276000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Mexico</th>\n",
       "      <td>289.536769</td>\n",
       "      <td>289.511719</td>\n",
       "      <td>289.499531</td>\n",
       "      <td>289.470156</td>\n",
       "      <td>289.670937</td>\n",
       "      <td>289.685714</td>\n",
       "      <td>289.565231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>346.877692</td>\n",
       "      <td>346.907031</td>\n",
       "      <td>346.866406</td>\n",
       "      <td>346.831875</td>\n",
       "      <td>347.027344</td>\n",
       "      <td>346.995079</td>\n",
       "      <td>346.886154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</th>\n",
       "      <td>349.413846</td>\n",
       "      <td>349.476719</td>\n",
       "      <td>349.435625</td>\n",
       "      <td>349.406563</td>\n",
       "      <td>349.585156</td>\n",
       "      <td>349.565873</td>\n",
       "      <td>349.444308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Dakota</th>\n",
       "      <td>398.349692</td>\n",
       "      <td>398.576562</td>\n",
       "      <td>398.677656</td>\n",
       "      <td>398.550000</td>\n",
       "      <td>399.090469</td>\n",
       "      <td>398.785079</td>\n",
       "      <td>398.658769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>334.526615</td>\n",
       "      <td>334.546562</td>\n",
       "      <td>334.536406</td>\n",
       "      <td>334.532969</td>\n",
       "      <td>334.615938</td>\n",
       "      <td>334.592063</td>\n",
       "      <td>334.533385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oklahoma</th>\n",
       "      <td>361.417077</td>\n",
       "      <td>361.645625</td>\n",
       "      <td>361.615313</td>\n",
       "      <td>361.597656</td>\n",
       "      <td>361.753437</td>\n",
       "      <td>361.598730</td>\n",
       "      <td>361.389846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oregon</th>\n",
       "      <td>208.461385</td>\n",
       "      <td>208.474844</td>\n",
       "      <td>208.454063</td>\n",
       "      <td>208.422344</td>\n",
       "      <td>208.604688</td>\n",
       "      <td>208.579048</td>\n",
       "      <td>208.479385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania</th>\n",
       "      <td>366.067231</td>\n",
       "      <td>366.102969</td>\n",
       "      <td>366.094375</td>\n",
       "      <td>366.060469</td>\n",
       "      <td>366.253125</td>\n",
       "      <td>366.222857</td>\n",
       "      <td>366.081846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rhode Island</th>\n",
       "      <td>323.733077</td>\n",
       "      <td>323.828281</td>\n",
       "      <td>323.801094</td>\n",
       "      <td>323.756094</td>\n",
       "      <td>323.998594</td>\n",
       "      <td>323.951746</td>\n",
       "      <td>323.803538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Carolina</th>\n",
       "      <td>341.612154</td>\n",
       "      <td>341.686094</td>\n",
       "      <td>341.656250</td>\n",
       "      <td>341.641094</td>\n",
       "      <td>341.837500</td>\n",
       "      <td>341.803651</td>\n",
       "      <td>341.617846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Dakota</th>\n",
       "      <td>375.626615</td>\n",
       "      <td>375.828125</td>\n",
       "      <td>375.830937</td>\n",
       "      <td>375.837500</td>\n",
       "      <td>375.946875</td>\n",
       "      <td>376.010159</td>\n",
       "      <td>375.657692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tennessee</th>\n",
       "      <td>352.678000</td>\n",
       "      <td>352.730469</td>\n",
       "      <td>352.711094</td>\n",
       "      <td>352.707344</td>\n",
       "      <td>352.871250</td>\n",
       "      <td>352.868730</td>\n",
       "      <td>352.707846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>337.034154</td>\n",
       "      <td>337.069687</td>\n",
       "      <td>337.037969</td>\n",
       "      <td>337.023750</td>\n",
       "      <td>337.267656</td>\n",
       "      <td>337.235714</td>\n",
       "      <td>337.071231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>289.552308</td>\n",
       "      <td>289.597500</td>\n",
       "      <td>289.586094</td>\n",
       "      <td>289.567344</td>\n",
       "      <td>289.634687</td>\n",
       "      <td>289.671905</td>\n",
       "      <td>289.564923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vermont</th>\n",
       "      <td>374.172462</td>\n",
       "      <td>374.250312</td>\n",
       "      <td>374.204844</td>\n",
       "      <td>374.181250</td>\n",
       "      <td>374.395156</td>\n",
       "      <td>374.335556</td>\n",
       "      <td>374.216615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virginia</th>\n",
       "      <td>368.084308</td>\n",
       "      <td>368.120781</td>\n",
       "      <td>368.112813</td>\n",
       "      <td>368.087969</td>\n",
       "      <td>368.264375</td>\n",
       "      <td>368.245238</td>\n",
       "      <td>368.116462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>233.381231</td>\n",
       "      <td>233.377969</td>\n",
       "      <td>233.361875</td>\n",
       "      <td>233.363594</td>\n",
       "      <td>233.422500</td>\n",
       "      <td>233.426032</td>\n",
       "      <td>233.382308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Virginia</th>\n",
       "      <td>357.408000</td>\n",
       "      <td>357.428906</td>\n",
       "      <td>357.368125</td>\n",
       "      <td>357.330156</td>\n",
       "      <td>357.395938</td>\n",
       "      <td>357.424444</td>\n",
       "      <td>357.406462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wisconsin</th>\n",
       "      <td>352.981077</td>\n",
       "      <td>353.050313</td>\n",
       "      <td>353.040469</td>\n",
       "      <td>353.000938</td>\n",
       "      <td>353.160000</td>\n",
       "      <td>353.141905</td>\n",
       "      <td>352.996308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyoming</th>\n",
       "      <td>338.837538</td>\n",
       "      <td>339.090625</td>\n",
       "      <td>339.045781</td>\n",
       "      <td>338.932969</td>\n",
       "      <td>339.260469</td>\n",
       "      <td>339.072698</td>\n",
       "      <td>338.910769</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "weekday                        0           1           2           3  \\\n",
       "State                                                                  \n",
       "Alabama               339.556308  339.577656  339.559375  339.552500   \n",
       "Alaska                291.463231  291.384687  291.390000  291.597500   \n",
       "Arizona               300.612000  300.653906  300.643750  300.655000   \n",
       "Arkansas              347.930462  348.011250  348.017656  347.934844   \n",
       "California            245.348923  245.364375  245.359219  245.342813   \n",
       "Colorado              238.948462  238.885938  238.889844  238.977187   \n",
       "Connecticut           341.624615  341.682812  341.633750  341.616406   \n",
       "Delaware              366.712769  366.748750  366.705937  366.659375   \n",
       "District of Columbia  348.205385  348.055000  348.132656  348.145625   \n",
       "Florida               302.548923  302.577031  302.529688  302.510781   \n",
       "Georgia               326.588308  326.635937  326.594219  326.579687   \n",
       "Hawaii                309.940615  309.895312  309.855156  309.818750   \n",
       "Idaho                 275.642615  275.627656  275.609844  275.653437   \n",
       "Illinois              355.406769  355.460313  355.442031  355.427188   \n",
       "Indiana               333.147077  333.184531  333.187344  333.145781   \n",
       "Iowa                  367.059692  367.086094  367.071563  367.055625   \n",
       "Kansas                350.760615  350.785469  350.765938  350.778438   \n",
       "Kentucky              338.906615  338.857812  338.846719  338.874062   \n",
       "Louisiana             366.743385  366.816562  366.761094  366.733437   \n",
       "Maine                 315.272615  315.392812  315.384844  315.330156   \n",
       "Maryland              370.884308  370.970469  370.912187  370.899219   \n",
       "Massachusetts         348.942462  348.995625  348.968750  348.945625   \n",
       "Michigan              292.022462  292.080312  292.044531  292.004844   \n",
       "Minnesota             358.104154  358.228437  358.188594  358.052969   \n",
       "Mississippi           337.535385  337.479062  337.495469  337.532656   \n",
       "Missouri              359.098923  359.278594  359.273594  359.264844   \n",
       "Montana               267.832615  267.860625  267.713125  267.700937   \n",
       "Nebraska              341.441692  341.522187  341.490000  341.479375   \n",
       "Nevada                267.794769  267.827969  267.823594  267.811875   \n",
       "New Hampshire         359.469846  359.543906  359.519844  359.362656   \n",
       "New Jersey            349.260769  349.313594  349.299219  349.272969   \n",
       "New Mexico            289.536769  289.511719  289.499531  289.470156   \n",
       "New York              346.877692  346.907031  346.866406  346.831875   \n",
       "North Carolina        349.413846  349.476719  349.435625  349.406563   \n",
       "North Dakota          398.349692  398.576562  398.677656  398.550000   \n",
       "Ohio                  334.526615  334.546562  334.536406  334.532969   \n",
       "Oklahoma              361.417077  361.645625  361.615313  361.597656   \n",
       "Oregon                208.461385  208.474844  208.454063  208.422344   \n",
       "Pennsylvania          366.067231  366.102969  366.094375  366.060469   \n",
       "Rhode Island          323.733077  323.828281  323.801094  323.756094   \n",
       "South Carolina        341.612154  341.686094  341.656250  341.641094   \n",
       "South Dakota          375.626615  375.828125  375.830937  375.837500   \n",
       "Tennessee             352.678000  352.730469  352.711094  352.707344   \n",
       "Texas                 337.034154  337.069687  337.037969  337.023750   \n",
       "Utah                  289.552308  289.597500  289.586094  289.567344   \n",
       "Vermont               374.172462  374.250312  374.204844  374.181250   \n",
       "Virginia              368.084308  368.120781  368.112813  368.087969   \n",
       "Washington            233.381231  233.377969  233.361875  233.363594   \n",
       "West Virginia         357.408000  357.428906  357.368125  357.330156   \n",
       "Wisconsin             352.981077  353.050313  353.040469  353.000938   \n",
       "Wyoming               338.837538  339.090625  339.045781  338.932969   \n",
       "\n",
       "weekday                        4           5           6  \n",
       "State                                                     \n",
       "Alabama               339.577969  339.568254  339.541385  \n",
       "Alaska                291.506406  291.596032  291.438923  \n",
       "Arizona               300.751562  300.715873  300.642308  \n",
       "Arkansas              348.278281  348.235238  347.991077  \n",
       "California            245.434219  245.425556  245.359231  \n",
       "Colorado              238.885937  238.897619  238.944769  \n",
       "Connecticut           341.832656  341.819683  341.652308  \n",
       "Delaware              366.962344  366.937143  366.750615  \n",
       "District of Columbia  348.213594  348.277778  348.212462  \n",
       "Florida               302.638437  302.648095  302.541231  \n",
       "Georgia               326.805000  326.784762  326.633077  \n",
       "Hawaii                309.934062  310.033016  309.956462  \n",
       "Idaho                 275.671250  275.654444  275.616923  \n",
       "Illinois              355.580625  355.502540  355.426000  \n",
       "Indiana               333.257031  333.253333  333.164769  \n",
       "Iowa                  367.175781  367.173175  367.051077  \n",
       "Kansas                350.928906  350.903492  350.813846  \n",
       "Kentucky              338.796719  338.799048  338.902769  \n",
       "Louisiana             367.016406  366.960159  366.800615  \n",
       "Maine                 315.570469  315.523333  315.262308  \n",
       "Maryland              371.160937  371.162063  370.912462  \n",
       "Massachusetts         349.156562  349.100952  348.974615  \n",
       "Michigan              292.229844  292.199841  292.055077  \n",
       "Minnesota             358.507969  358.471587  358.156308  \n",
       "Mississippi           337.615937  337.660635  337.597231  \n",
       "Missouri              359.418438  359.434921  359.174000  \n",
       "Montana               267.930781  267.934444  267.850923  \n",
       "Nebraska              341.768438  341.713968  341.504923  \n",
       "Nevada                267.866875  267.824286  267.792308  \n",
       "New Hampshire         359.595469  359.512222  359.331231  \n",
       "New Jersey            349.435469  349.396667  349.276000  \n",
       "New Mexico            289.670937  289.685714  289.565231  \n",
       "New York              347.027344  346.995079  346.886154  \n",
       "North Carolina        349.585156  349.565873  349.444308  \n",
       "North Dakota          399.090469  398.785079  398.658769  \n",
       "Ohio                  334.615938  334.592063  334.533385  \n",
       "Oklahoma              361.753437  361.598730  361.389846  \n",
       "Oregon                208.604688  208.579048  208.479385  \n",
       "Pennsylvania          366.253125  366.222857  366.081846  \n",
       "Rhode Island          323.998594  323.951746  323.803538  \n",
       "South Carolina        341.837500  341.803651  341.617846  \n",
       "South Dakota          375.946875  376.010159  375.657692  \n",
       "Tennessee             352.871250  352.868730  352.707846  \n",
       "Texas                 337.267656  337.235714  337.071231  \n",
       "Utah                  289.634687  289.671905  289.564923  \n",
       "Vermont               374.395156  374.335556  374.216615  \n",
       "Virginia              368.264375  368.245238  368.116462  \n",
       "Washington            233.422500  233.426032  233.382308  \n",
       "West Virginia         357.395938  357.424444  357.406462  \n",
       "Wisconsin             353.160000  353.141905  352.996308  \n",
       "Wyoming               339.260469  339.072698  338.910769  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, values='HighQ', index=['State'], columns=[\"weekday\"] )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise** Get a cross tabulation: for each state, for each month, get the prices for the weekday as shown in the output "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.5 Derive the Data\n",
    "\n",
    "Lets us load the demographic dataset and create a new column for others in the population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_demo = pd.read_csv(\"data/Demographics_State.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "      <th>total_population</th>\n",
       "      <th>percent_white</th>\n",
       "      <th>percent_black</th>\n",
       "      <th>percent_asian</th>\n",
       "      <th>percent_hispanic</th>\n",
       "      <th>per_capita_income</th>\n",
       "      <th>median_rent</th>\n",
       "      <th>median_age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>4799277</td>\n",
       "      <td>67</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>23680</td>\n",
       "      <td>501</td>\n",
       "      <td>38.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>720316</td>\n",
       "      <td>63</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>32651</td>\n",
       "      <td>978</td>\n",
       "      <td>33.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>6479703</td>\n",
       "      <td>57</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>25358</td>\n",
       "      <td>747</td>\n",
       "      <td>36.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>2933369</td>\n",
       "      <td>74</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>22170</td>\n",
       "      <td>480</td>\n",
       "      <td>37.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>37659181</td>\n",
       "      <td>40</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>38</td>\n",
       "      <td>29527</td>\n",
       "      <td>1119</td>\n",
       "      <td>35.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       region  total_population  percent_white  percent_black  percent_asian  \\\n",
       "0     alabama           4799277             67             26              1   \n",
       "1      alaska            720316             63              3              5   \n",
       "2     arizona           6479703             57              4              3   \n",
       "3    arkansas           2933369             74             15              1   \n",
       "4  california          37659181             40              6             13   \n",
       "\n",
       "   percent_hispanic  per_capita_income  median_rent  median_age  \n",
       "0                 4              23680          501        38.1  \n",
       "1                 6              32651          978        33.6  \n",
       "2                30              25358          747        36.3  \n",
       "3                 7              22170          480        37.5  \n",
       "4                38              29527         1119        35.4  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_demo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_demo[\"percent_other\"] = 100 - df_demo[\"percent_white\"] - df_demo[\"percent_black\"] - df_demo[\"percent_asian\"] - df_demo[\"percent_hispanic\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_demo.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise** Express median rent as a proportion of california's median rent (Compute it as a new column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.6 Merge the Data \n",
    "\n",
    "Lets merge the demographic dataset with the price dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Let us change the column name region to State\n",
    "df_demo = df_demo.rename(columns={'region':'State'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_demo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# We can now merge Demographic and Price mean data into one single data frame\n",
    "df_merge = pd.merge(df_mean, df_demo, how='inner', on='State')\n",
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happened? Why is there no data in the dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Change the State in df_mean to lower case\n",
    "df_mean['State'] = df_mean['State'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_mean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# We can now merge Demographic and Price mean data into one single data frame\n",
    "df_merge = pd.merge(df_mean, df_demo, how='inner', on='State')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.7 Filter the Data\n",
    "\n",
    "Lets start by filtering the data \n",
    "- by location\n",
    "- by Year\n",
    "- by location & Year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Filter data for location California\n",
    "df_cal = df[df[\"State\"] == \"California\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_cal.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Filter data for year\n",
    "df_2014 = df[df[\"year\"] == 2014]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_2014.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_cal_2014 = df[(df[\"year\"] == 2014) & (df[\"State\"] == \"California\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_cal_2014.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_cal_2014.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise** Find the % of hispanic population for the state with max white population (use `df_demo`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.8 Summarise the Data\n",
    "\n",
    "We can use the describe function to get the summary stats for each column in the data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use convenience functions like sum(), count(), mean() etc. to calculate these"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.HighQ.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Lets do this the hard way\n",
    "df.HighQ.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.HighQ.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.HighQ.sum()/df.HighQ.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.HighQ.median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.9 Sample the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "?df.sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample = df[df.State=='California'].sample(n = 50, replace = True, random_state=123)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample.duplicated()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample.loc[8572]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.10 Quirks in Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample.iat[0, 0] = \"Cal\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But changes when you copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_ca_sample2 = df_ca_sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample2.iat[0, 0] = \"CA\"\n",
    "df_ca_sample2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fix the issue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_ca_sample3 = df_ca_sample2.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample3.iat[0, 0] = \"CALIFORNIA\"\n",
    "df_ca_sample3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_ca_sample2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}