Getting_LDS_Data_And_Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the LDS Data from the OCR website\n",
    "### Convert each sheet into a Pandas DataFrame object\n",
    "Later this was refactored into a python package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "URL = \"http://www.ocr.org.uk/Images/308727-units-h230-and-h240-large\\\n",
    "-data-set-lds-sample-assessment-material.xlsx\"\n",
    "\n",
    "OCR_LDS = pd.ExcelFile(URL)\n",
    "Data = {\"Travel_2011\" : OCR_LDS.parse(1), \"Travel_2001\" : OCR_LDS.pa\\\n",
    "rse(2),\n",
    "        \"Age_2011\" : OCR_LDS.parse(3), \"Age_2001\" : OCR_LDS.parse(4)\\\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean up data\n",
    "* Drop the NaN column from the 2011 data\n",
    "* Change the titles of the columns\n",
    "* Clean up the dash from the Isle of Scilly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Geo_Code</th>\n",
       "      <th>Region</th>\n",
       "      <th>LA</th>\n",
       "      <th>Employment</th>\n",
       "      <th>Home</th>\n",
       "      <th>Underground</th>\n",
       "      <th>Train</th>\n",
       "      <th>Bus</th>\n",
       "      <th>Taxi</th>\n",
       "      <th>Motorcycle</th>\n",
       "      <th>Drive</th>\n",
       "      <th>Passenger</th>\n",
       "      <th>Bike</th>\n",
       "      <th>Walk</th>\n",
       "      <th>Other</th>\n",
       "      <th>Unemployed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000047</td>\n",
       "      <td>North East</td>\n",
       "      <td>County Durham</td>\n",
       "      <td>227894</td>\n",
       "      <td>20652</td>\n",
       "      <td>323</td>\n",
       "      <td>1865</td>\n",
       "      <td>13732</td>\n",
       "      <td>1401</td>\n",
       "      <td>1038</td>\n",
       "      <td>146644</td>\n",
       "      <td>17362</td>\n",
       "      <td>2205</td>\n",
       "      <td>21490</td>\n",
       "      <td>1182</td>\n",
       "      <td>155902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>North East</td>\n",
       "      <td>Darlington</td>\n",
       "      <td>49014</td>\n",
       "      <td>4180</td>\n",
       "      <td>33</td>\n",
       "      <td>828</td>\n",
       "      <td>3380</td>\n",
       "      <td>403</td>\n",
       "      <td>191</td>\n",
       "      <td>28981</td>\n",
       "      <td>3337</td>\n",
       "      <td>1151</td>\n",
       "      <td>6284</td>\n",
       "      <td>246</td>\n",
       "      <td>27621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E08000020</td>\n",
       "      <td>North East</td>\n",
       "      <td>Gateshead</td>\n",
       "      <td>91877</td>\n",
       "      <td>6383</td>\n",
       "      <td>4270</td>\n",
       "      <td>705</td>\n",
       "      <td>13909</td>\n",
       "      <td>453</td>\n",
       "      <td>364</td>\n",
       "      <td>50236</td>\n",
       "      <td>5830</td>\n",
       "      <td>1314</td>\n",
       "      <td>7966</td>\n",
       "      <td>447</td>\n",
       "      <td>56202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>37767</td>\n",
       "      <td>2473</td>\n",
       "      <td>33</td>\n",
       "      <td>469</td>\n",
       "      <td>2556</td>\n",
       "      <td>673</td>\n",
       "      <td>175</td>\n",
       "      <td>22863</td>\n",
       "      <td>3157</td>\n",
       "      <td>706</td>\n",
       "      <td>4305</td>\n",
       "      <td>357</td>\n",
       "      <td>29037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>North East</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>54547</td>\n",
       "      <td>3337</td>\n",
       "      <td>44</td>\n",
       "      <td>698</td>\n",
       "      <td>4868</td>\n",
       "      <td>902</td>\n",
       "      <td>195</td>\n",
       "      <td>31155</td>\n",
       "      <td>4639</td>\n",
       "      <td>1375</td>\n",
       "      <td>6769</td>\n",
       "      <td>565</td>\n",
       "      <td>46004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Geo_Code      Region             LA  Employment   Home  Underground  \\\n",
       "0  E06000047  North East  County Durham      227894  20652          323   \n",
       "1  E06000005  North East     Darlington       49014   4180           33   \n",
       "2  E08000020  North East      Gateshead       91877   6383         4270   \n",
       "3  E06000001  North East     Hartlepool       37767   2473           33   \n",
       "4  E06000002  North East  Middlesbrough       54547   3337           44   \n",
       "\n",
       "   Train    Bus  Taxi  Motorcycle   Drive  Passenger  Bike   Walk  Other  \\\n",
       "0   1865  13732  1401        1038  146644      17362  2205  21490   1182   \n",
       "1    828   3380   403         191   28981       3337  1151   6284    246   \n",
       "2    705  13909   453         364   50236       5830  1314   7966    447   \n",
       "3    469   2556   673         175   22863       3157   706   4305    357   \n",
       "4    698   4868   902         195   31155       4639  1375   6769    565   \n",
       "\n",
       "   Unemployed  \n",
       "0      155902  \n",
       "1       27621  \n",
       "2       56202  \n",
       "3       29037  \n",
       "4       46004  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df means dataframe\n",
    "for df_title in Data:\n",
    "    Data[df_title] = Data[df_title].dropna(axis=1,how=\"all\")\n",
    "    travel_columns = [\"Geo_Code\",\"Region\",\"LA\",\"Employment\",\"Home\",\"Underground\",\"Train\",\n",
    "                      \"Bus\",\"Taxi\",\"Motorcycle\",\"Drive\",\"Passenger\",\"Bike\",\"Walk\",\"Other\"]\n",
    "    age_columns = [\"Geo_Code\",\"Region\",\"LA\",\"Residents\",\"0-4\",\"5-7\",\"8-9\",\"10-14\",\"15\",\n",
    "                   \"16-17\",\"18-19\",\"20-24\",\"25-29\",\"30-34\",\"45-59\",\"60-64\",\"65-74\",\"75-84\",\n",
    "                   \"85-89\",\"90+\"]\n",
    "    \n",
    "    if \"Travel\" in df_title and \"2001\" in df_title:\n",
    "        Data[df_title].columns = travel_columns\n",
    "    elif \"Travel\" in df_title:\n",
    "        Data[df_title].columns = travel_columns + [\"Unemployed\"]\n",
    "    elif \"2001\" in df_title:\n",
    "        Data[df_title].columns = age_columns \n",
    "    else:\n",
    "        Data[df_title].columns = age_columns + [\"Mean_Age\",\"Median_Age\"]\n",
    "    \n",
    "    Data[df_title] = Data[df_title].replace(\"-\",0) #the value from Isles of Scilly\n",
    "    #Data[df_title] = Data[df_title].set_index('LA')\n",
    "\n",
    "Data[\"Travel_2011\"].head()"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}