initial_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A notebook to manipulate Scottish Healthboard population data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set up our environment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing the necessary files\n",
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get our data\n",
    "We could do this from a remote source by I previously downloaded it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the data for healthboards - codes and names\n",
    "\n",
    "dfcodes = pd.read_csv('data/raw/HB_Codes.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HB2014</th>\n",
       "      <th>HB2014Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>S08000015</td>\n",
       "      <td>NHS Ayrshire and Arran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>S08000016</td>\n",
       "      <td>NHS Borders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>S08000017</td>\n",
       "      <td>NHS Dumfries and Galloway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>S08000018</td>\n",
       "      <td>NHS Fife</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>S08000019</td>\n",
       "      <td>NHS Forth Valley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>S08000020</td>\n",
       "      <td>NHS Grampian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>S08000021</td>\n",
       "      <td>NHS Greater Glasgow and Clyde</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>S08000022</td>\n",
       "      <td>NHS Highland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>S08000023</td>\n",
       "      <td>NHS Lanarkshire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>S08000024</td>\n",
       "      <td>NHS Lothian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>S08000025</td>\n",
       "      <td>NHS Orkney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>S08000026</td>\n",
       "      <td>NHS Shetland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>S08000027</td>\n",
       "      <td>NHS Tayside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>S08000028</td>\n",
       "      <td>NHS Western Isles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>S08000029</td>\n",
       "      <td>NHS Fife</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>S08000030</td>\n",
       "      <td>NHS Tayside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>S08000031</td>\n",
       "      <td>NHS Greater Glasgow and Clyde</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>S08000032</td>\n",
       "      <td>NHS Lanarkshire</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       HB2014                     HB2014Name\n",
       "0   S08000015         NHS Ayrshire and Arran\n",
       "1   S08000016                    NHS Borders\n",
       "2   S08000017      NHS Dumfries and Galloway\n",
       "3   S08000018                       NHS Fife\n",
       "4   S08000019               NHS Forth Valley\n",
       "5   S08000020                   NHS Grampian\n",
       "6   S08000021  NHS Greater Glasgow and Clyde\n",
       "7   S08000022                   NHS Highland\n",
       "8   S08000023                NHS Lanarkshire\n",
       "9   S08000024                    NHS Lothian\n",
       "10  S08000025                     NHS Orkney\n",
       "11  S08000026                   NHS Shetland\n",
       "12  S08000027                    NHS Tayside\n",
       "13  S08000028              NHS Western Isles\n",
       "14  S08000029                       NHS Fife\n",
       "15  S08000030                    NHS Tayside\n",
       "16  S08000031  NHS Greater Glasgow and Clyde\n",
       "17  S08000032                NHS Lanarkshire"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfcodes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Change the column names to something more meaningful, and get rid of the \"NHS \" at the start of each name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>S08000015</td>\n",
       "      <td>Ayrshire and Arran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>S08000016</td>\n",
       "      <td>Borders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>S08000017</td>\n",
       "      <td>Dumfries and Galloway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>S08000018</td>\n",
       "      <td>Fife</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>S08000019</td>\n",
       "      <td>Forth Valley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>S08000020</td>\n",
       "      <td>Grampian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>S08000021</td>\n",
       "      <td>Greater Glasgow and Clyde</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>S08000022</td>\n",
       "      <td>Highland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>S08000023</td>\n",
       "      <td>Lanarkshire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>S08000024</td>\n",
       "      <td>Lothian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>S08000025</td>\n",
       "      <td>Orkney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>S08000026</td>\n",
       "      <td>Shetland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>S08000027</td>\n",
       "      <td>Tayside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>S08000028</td>\n",
       "      <td>Western Isles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>S08000029</td>\n",
       "      <td>Fife</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>S08000030</td>\n",
       "      <td>Tayside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>S08000031</td>\n",
       "      <td>Greater Glasgow and Clyde</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>S08000032</td>\n",
       "      <td>Lanarkshire</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Code                       Name\n",
       "0   S08000015         Ayrshire and Arran\n",
       "1   S08000016                    Borders\n",
       "2   S08000017      Dumfries and Galloway\n",
       "3   S08000018                       Fife\n",
       "4   S08000019               Forth Valley\n",
       "5   S08000020                   Grampian\n",
       "6   S08000021  Greater Glasgow and Clyde\n",
       "7   S08000022                   Highland\n",
       "8   S08000023                Lanarkshire\n",
       "9   S08000024                    Lothian\n",
       "10  S08000025                     Orkney\n",
       "11  S08000026                   Shetland\n",
       "12  S08000027                    Tayside\n",
       "13  S08000028              Western Isles\n",
       "14  S08000029                       Fife\n",
       "15  S08000030                    Tayside\n",
       "16  S08000031  Greater Glasgow and Clyde\n",
       "17  S08000032                Lanarkshire"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfcodes.columns = ['Code', 'Name']\n",
    "dfcodes['Name'] = dfcodes['Name'].str[4:]\n",
    "dfcodes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing the population sizes for each Health Board\n",
    "While I grabbed just the 2018 data, this is more detailed than we want for now with many columns that we don't need for this exercise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HB2014</th>\n",
       "      <th>Sex</th>\n",
       "      <th>AllAges</th>\n",
       "      <th>Age0</th>\n",
       "      <th>Age1</th>\n",
       "      <th>Age2</th>\n",
       "      <th>Age3</th>\n",
       "      <th>Age4</th>\n",
       "      <th>Age5</th>\n",
       "      <th>...</th>\n",
       "      <th>Age81</th>\n",
       "      <th>Age82</th>\n",
       "      <th>Age83</th>\n",
       "      <th>Age84</th>\n",
       "      <th>Age85</th>\n",
       "      <th>Age86</th>\n",
       "      <th>Age87</th>\n",
       "      <th>Age88</th>\n",
       "      <th>Age89</th>\n",
       "      <th>Age90plus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000015</td>\n",
       "      <td>Female</td>\n",
       "      <td>192454</td>\n",
       "      <td>1616</td>\n",
       "      <td>1645</td>\n",
       "      <td>1771</td>\n",
       "      <td>1822</td>\n",
       "      <td>1799</td>\n",
       "      <td>1815</td>\n",
       "      <td>...</td>\n",
       "      <td>1388</td>\n",
       "      <td>1272</td>\n",
       "      <td>1131</td>\n",
       "      <td>1067</td>\n",
       "      <td>987</td>\n",
       "      <td>887</td>\n",
       "      <td>732</td>\n",
       "      <td>639</td>\n",
       "      <td>532</td>\n",
       "      <td>2302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000015</td>\n",
       "      <td>Male</td>\n",
       "      <td>177216</td>\n",
       "      <td>1727</td>\n",
       "      <td>1736</td>\n",
       "      <td>1932</td>\n",
       "      <td>1965</td>\n",
       "      <td>1875</td>\n",
       "      <td>1956</td>\n",
       "      <td>...</td>\n",
       "      <td>1032</td>\n",
       "      <td>908</td>\n",
       "      <td>864</td>\n",
       "      <td>705</td>\n",
       "      <td>657</td>\n",
       "      <td>592</td>\n",
       "      <td>490</td>\n",
       "      <td>344</td>\n",
       "      <td>310</td>\n",
       "      <td>961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000016</td>\n",
       "      <td>Female</td>\n",
       "      <td>59281</td>\n",
       "      <td>472</td>\n",
       "      <td>501</td>\n",
       "      <td>555</td>\n",
       "      <td>570</td>\n",
       "      <td>603</td>\n",
       "      <td>615</td>\n",
       "      <td>...</td>\n",
       "      <td>489</td>\n",
       "      <td>377</td>\n",
       "      <td>387</td>\n",
       "      <td>365</td>\n",
       "      <td>331</td>\n",
       "      <td>282</td>\n",
       "      <td>268</td>\n",
       "      <td>230</td>\n",
       "      <td>206</td>\n",
       "      <td>715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000016</td>\n",
       "      <td>Male</td>\n",
       "      <td>55989</td>\n",
       "      <td>545</td>\n",
       "      <td>514</td>\n",
       "      <td>572</td>\n",
       "      <td>530</td>\n",
       "      <td>624</td>\n",
       "      <td>655</td>\n",
       "      <td>...</td>\n",
       "      <td>332</td>\n",
       "      <td>338</td>\n",
       "      <td>293</td>\n",
       "      <td>253</td>\n",
       "      <td>203</td>\n",
       "      <td>169</td>\n",
       "      <td>201</td>\n",
       "      <td>162</td>\n",
       "      <td>125</td>\n",
       "      <td>309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000017</td>\n",
       "      <td>Female</td>\n",
       "      <td>76469</td>\n",
       "      <td>641</td>\n",
       "      <td>614</td>\n",
       "      <td>633</td>\n",
       "      <td>657</td>\n",
       "      <td>626</td>\n",
       "      <td>683</td>\n",
       "      <td>...</td>\n",
       "      <td>627</td>\n",
       "      <td>607</td>\n",
       "      <td>520</td>\n",
       "      <td>514</td>\n",
       "      <td>459</td>\n",
       "      <td>406</td>\n",
       "      <td>351</td>\n",
       "      <td>317</td>\n",
       "      <td>268</td>\n",
       "      <td>975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000017</td>\n",
       "      <td>Male</td>\n",
       "      <td>72321</td>\n",
       "      <td>607</td>\n",
       "      <td>644</td>\n",
       "      <td>670</td>\n",
       "      <td>669</td>\n",
       "      <td>727</td>\n",
       "      <td>721</td>\n",
       "      <td>...</td>\n",
       "      <td>545</td>\n",
       "      <td>492</td>\n",
       "      <td>388</td>\n",
       "      <td>362</td>\n",
       "      <td>360</td>\n",
       "      <td>276</td>\n",
       "      <td>231</td>\n",
       "      <td>198</td>\n",
       "      <td>155</td>\n",
       "      <td>482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000019</td>\n",
       "      <td>Female</td>\n",
       "      <td>156903</td>\n",
       "      <td>1376</td>\n",
       "      <td>1458</td>\n",
       "      <td>1410</td>\n",
       "      <td>1495</td>\n",
       "      <td>1539</td>\n",
       "      <td>1561</td>\n",
       "      <td>...</td>\n",
       "      <td>1025</td>\n",
       "      <td>917</td>\n",
       "      <td>831</td>\n",
       "      <td>682</td>\n",
       "      <td>623</td>\n",
       "      <td>628</td>\n",
       "      <td>473</td>\n",
       "      <td>451</td>\n",
       "      <td>405</td>\n",
       "      <td>1491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000019</td>\n",
       "      <td>Male</td>\n",
       "      <td>149167</td>\n",
       "      <td>1486</td>\n",
       "      <td>1453</td>\n",
       "      <td>1677</td>\n",
       "      <td>1622</td>\n",
       "      <td>1639</td>\n",
       "      <td>1700</td>\n",
       "      <td>...</td>\n",
       "      <td>746</td>\n",
       "      <td>639</td>\n",
       "      <td>663</td>\n",
       "      <td>531</td>\n",
       "      <td>458</td>\n",
       "      <td>407</td>\n",
       "      <td>322</td>\n",
       "      <td>260</td>\n",
       "      <td>212</td>\n",
       "      <td>657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000020</td>\n",
       "      <td>Female</td>\n",
       "      <td>293822</td>\n",
       "      <td>2866</td>\n",
       "      <td>2906</td>\n",
       "      <td>3088</td>\n",
       "      <td>3025</td>\n",
       "      <td>3108</td>\n",
       "      <td>3076</td>\n",
       "      <td>...</td>\n",
       "      <td>1723</td>\n",
       "      <td>1628</td>\n",
       "      <td>1553</td>\n",
       "      <td>1390</td>\n",
       "      <td>1245</td>\n",
       "      <td>1241</td>\n",
       "      <td>1069</td>\n",
       "      <td>883</td>\n",
       "      <td>797</td>\n",
       "      <td>3003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000020</td>\n",
       "      <td>Male</td>\n",
       "      <td>290728</td>\n",
       "      <td>3041</td>\n",
       "      <td>3074</td>\n",
       "      <td>3316</td>\n",
       "      <td>3205</td>\n",
       "      <td>3208</td>\n",
       "      <td>3297</td>\n",
       "      <td>...</td>\n",
       "      <td>1313</td>\n",
       "      <td>1226</td>\n",
       "      <td>1122</td>\n",
       "      <td>986</td>\n",
       "      <td>876</td>\n",
       "      <td>727</td>\n",
       "      <td>632</td>\n",
       "      <td>514</td>\n",
       "      <td>414</td>\n",
       "      <td>1378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000022</td>\n",
       "      <td>Female</td>\n",
       "      <td>163493</td>\n",
       "      <td>1305</td>\n",
       "      <td>1353</td>\n",
       "      <td>1447</td>\n",
       "      <td>1575</td>\n",
       "      <td>1482</td>\n",
       "      <td>1569</td>\n",
       "      <td>...</td>\n",
       "      <td>1192</td>\n",
       "      <td>1148</td>\n",
       "      <td>1073</td>\n",
       "      <td>919</td>\n",
       "      <td>841</td>\n",
       "      <td>816</td>\n",
       "      <td>715</td>\n",
       "      <td>584</td>\n",
       "      <td>512</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000022</td>\n",
       "      <td>Male</td>\n",
       "      <td>158307</td>\n",
       "      <td>1388</td>\n",
       "      <td>1502</td>\n",
       "      <td>1553</td>\n",
       "      <td>1581</td>\n",
       "      <td>1600</td>\n",
       "      <td>1596</td>\n",
       "      <td>...</td>\n",
       "      <td>866</td>\n",
       "      <td>791</td>\n",
       "      <td>765</td>\n",
       "      <td>702</td>\n",
       "      <td>587</td>\n",
       "      <td>546</td>\n",
       "      <td>432</td>\n",
       "      <td>379</td>\n",
       "      <td>326</td>\n",
       "      <td>870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000024</td>\n",
       "      <td>Female</td>\n",
       "      <td>460831</td>\n",
       "      <td>4388</td>\n",
       "      <td>4474</td>\n",
       "      <td>4563</td>\n",
       "      <td>4638</td>\n",
       "      <td>4827</td>\n",
       "      <td>4840</td>\n",
       "      <td>...</td>\n",
       "      <td>2443</td>\n",
       "      <td>2395</td>\n",
       "      <td>2141</td>\n",
       "      <td>1982</td>\n",
       "      <td>1776</td>\n",
       "      <td>1750</td>\n",
       "      <td>1588</td>\n",
       "      <td>1359</td>\n",
       "      <td>1095</td>\n",
       "      <td>4442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000024</td>\n",
       "      <td>Male</td>\n",
       "      <td>436939</td>\n",
       "      <td>4607</td>\n",
       "      <td>4684</td>\n",
       "      <td>4932</td>\n",
       "      <td>4990</td>\n",
       "      <td>5035</td>\n",
       "      <td>4984</td>\n",
       "      <td>...</td>\n",
       "      <td>1750</td>\n",
       "      <td>1693</td>\n",
       "      <td>1541</td>\n",
       "      <td>1352</td>\n",
       "      <td>1152</td>\n",
       "      <td>1044</td>\n",
       "      <td>932</td>\n",
       "      <td>688</td>\n",
       "      <td>618</td>\n",
       "      <td>1971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000025</td>\n",
       "      <td>Female</td>\n",
       "      <td>11151</td>\n",
       "      <td>96</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>94</td>\n",
       "      <td>87</td>\n",
       "      <td>91</td>\n",
       "      <td>...</td>\n",
       "      <td>85</td>\n",
       "      <td>64</td>\n",
       "      <td>64</td>\n",
       "      <td>69</td>\n",
       "      <td>50</td>\n",
       "      <td>40</td>\n",
       "      <td>51</td>\n",
       "      <td>53</td>\n",
       "      <td>34</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000025</td>\n",
       "      <td>Male</td>\n",
       "      <td>11039</td>\n",
       "      <td>82</td>\n",
       "      <td>107</td>\n",
       "      <td>83</td>\n",
       "      <td>114</td>\n",
       "      <td>106</td>\n",
       "      <td>124</td>\n",
       "      <td>...</td>\n",
       "      <td>65</td>\n",
       "      <td>58</td>\n",
       "      <td>69</td>\n",
       "      <td>50</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>25</td>\n",
       "      <td>35</td>\n",
       "      <td>25</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000026</td>\n",
       "      <td>Female</td>\n",
       "      <td>11277</td>\n",
       "      <td>106</td>\n",
       "      <td>109</td>\n",
       "      <td>126</td>\n",
       "      <td>123</td>\n",
       "      <td>118</td>\n",
       "      <td>142</td>\n",
       "      <td>...</td>\n",
       "      <td>68</td>\n",
       "      <td>53</td>\n",
       "      <td>68</td>\n",
       "      <td>56</td>\n",
       "      <td>49</td>\n",
       "      <td>36</td>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>37</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000026</td>\n",
       "      <td>Male</td>\n",
       "      <td>11713</td>\n",
       "      <td>110</td>\n",
       "      <td>138</td>\n",
       "      <td>131</td>\n",
       "      <td>114</td>\n",
       "      <td>120</td>\n",
       "      <td>143</td>\n",
       "      <td>...</td>\n",
       "      <td>59</td>\n",
       "      <td>59</td>\n",
       "      <td>52</td>\n",
       "      <td>36</td>\n",
       "      <td>30</td>\n",
       "      <td>28</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000028</td>\n",
       "      <td>Female</td>\n",
       "      <td>13583</td>\n",
       "      <td>92</td>\n",
       "      <td>117</td>\n",
       "      <td>113</td>\n",
       "      <td>115</td>\n",
       "      <td>127</td>\n",
       "      <td>137</td>\n",
       "      <td>...</td>\n",
       "      <td>124</td>\n",
       "      <td>103</td>\n",
       "      <td>97</td>\n",
       "      <td>99</td>\n",
       "      <td>99</td>\n",
       "      <td>91</td>\n",
       "      <td>66</td>\n",
       "      <td>58</td>\n",
       "      <td>60</td>\n",
       "      <td>231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000028</td>\n",
       "      <td>Male</td>\n",
       "      <td>13247</td>\n",
       "      <td>122</td>\n",
       "      <td>134</td>\n",
       "      <td>129</td>\n",
       "      <td>112</td>\n",
       "      <td>138</td>\n",
       "      <td>121</td>\n",
       "      <td>...</td>\n",
       "      <td>82</td>\n",
       "      <td>68</td>\n",
       "      <td>70</td>\n",
       "      <td>65</td>\n",
       "      <td>52</td>\n",
       "      <td>36</td>\n",
       "      <td>40</td>\n",
       "      <td>21</td>\n",
       "      <td>43</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000029</td>\n",
       "      <td>Female</td>\n",
       "      <td>191290</td>\n",
       "      <td>1705</td>\n",
       "      <td>1722</td>\n",
       "      <td>1872</td>\n",
       "      <td>2000</td>\n",
       "      <td>1907</td>\n",
       "      <td>1978</td>\n",
       "      <td>...</td>\n",
       "      <td>1283</td>\n",
       "      <td>1180</td>\n",
       "      <td>1033</td>\n",
       "      <td>960</td>\n",
       "      <td>881</td>\n",
       "      <td>756</td>\n",
       "      <td>688</td>\n",
       "      <td>645</td>\n",
       "      <td>544</td>\n",
       "      <td>2092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000029</td>\n",
       "      <td>Male</td>\n",
       "      <td>180620</td>\n",
       "      <td>1798</td>\n",
       "      <td>1907</td>\n",
       "      <td>1925</td>\n",
       "      <td>2027</td>\n",
       "      <td>2093</td>\n",
       "      <td>2115</td>\n",
       "      <td>...</td>\n",
       "      <td>930</td>\n",
       "      <td>848</td>\n",
       "      <td>770</td>\n",
       "      <td>650</td>\n",
       "      <td>604</td>\n",
       "      <td>501</td>\n",
       "      <td>395</td>\n",
       "      <td>343</td>\n",
       "      <td>324</td>\n",
       "      <td>941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000030</td>\n",
       "      <td>Female</td>\n",
       "      <td>213394</td>\n",
       "      <td>1800</td>\n",
       "      <td>1888</td>\n",
       "      <td>1918</td>\n",
       "      <td>2011</td>\n",
       "      <td>1971</td>\n",
       "      <td>2174</td>\n",
       "      <td>...</td>\n",
       "      <td>1509</td>\n",
       "      <td>1427</td>\n",
       "      <td>1375</td>\n",
       "      <td>1271</td>\n",
       "      <td>1058</td>\n",
       "      <td>1052</td>\n",
       "      <td>959</td>\n",
       "      <td>827</td>\n",
       "      <td>720</td>\n",
       "      <td>2924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000030</td>\n",
       "      <td>Male</td>\n",
       "      <td>202686</td>\n",
       "      <td>1904</td>\n",
       "      <td>1992</td>\n",
       "      <td>2082</td>\n",
       "      <td>2212</td>\n",
       "      <td>2181</td>\n",
       "      <td>2198</td>\n",
       "      <td>...</td>\n",
       "      <td>1123</td>\n",
       "      <td>1044</td>\n",
       "      <td>960</td>\n",
       "      <td>846</td>\n",
       "      <td>743</td>\n",
       "      <td>741</td>\n",
       "      <td>641</td>\n",
       "      <td>507</td>\n",
       "      <td>385</td>\n",
       "      <td>1240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000031</td>\n",
       "      <td>Female</td>\n",
       "      <td>605223</td>\n",
       "      <td>5750</td>\n",
       "      <td>5843</td>\n",
       "      <td>6067</td>\n",
       "      <td>6086</td>\n",
       "      <td>6255</td>\n",
       "      <td>6429</td>\n",
       "      <td>...</td>\n",
       "      <td>3629</td>\n",
       "      <td>3326</td>\n",
       "      <td>3222</td>\n",
       "      <td>2931</td>\n",
       "      <td>2610</td>\n",
       "      <td>2484</td>\n",
       "      <td>2175</td>\n",
       "      <td>1891</td>\n",
       "      <td>1534</td>\n",
       "      <td>6030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000031</td>\n",
       "      <td>Male</td>\n",
       "      <td>569757</td>\n",
       "      <td>5974</td>\n",
       "      <td>6391</td>\n",
       "      <td>6306</td>\n",
       "      <td>6450</td>\n",
       "      <td>6555</td>\n",
       "      <td>6568</td>\n",
       "      <td>...</td>\n",
       "      <td>2408</td>\n",
       "      <td>2156</td>\n",
       "      <td>1990</td>\n",
       "      <td>1649</td>\n",
       "      <td>1459</td>\n",
       "      <td>1373</td>\n",
       "      <td>1104</td>\n",
       "      <td>941</td>\n",
       "      <td>735</td>\n",
       "      <td>2396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000032</td>\n",
       "      <td>Female</td>\n",
       "      <td>340178</td>\n",
       "      <td>3265</td>\n",
       "      <td>3311</td>\n",
       "      <td>3392</td>\n",
       "      <td>3558</td>\n",
       "      <td>3494</td>\n",
       "      <td>3587</td>\n",
       "      <td>...</td>\n",
       "      <td>2073</td>\n",
       "      <td>1996</td>\n",
       "      <td>1755</td>\n",
       "      <td>1579</td>\n",
       "      <td>1451</td>\n",
       "      <td>1270</td>\n",
       "      <td>1119</td>\n",
       "      <td>913</td>\n",
       "      <td>846</td>\n",
       "      <td>2859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000032</td>\n",
       "      <td>Male</td>\n",
       "      <td>319022</td>\n",
       "      <td>3441</td>\n",
       "      <td>3535</td>\n",
       "      <td>3724</td>\n",
       "      <td>3705</td>\n",
       "      <td>3707</td>\n",
       "      <td>3837</td>\n",
       "      <td>...</td>\n",
       "      <td>1359</td>\n",
       "      <td>1324</td>\n",
       "      <td>1075</td>\n",
       "      <td>976</td>\n",
       "      <td>921</td>\n",
       "      <td>763</td>\n",
       "      <td>643</td>\n",
       "      <td>494</td>\n",
       "      <td>399</td>\n",
       "      <td>1198</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28 rows × 95 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year     HB2014     Sex  AllAges  Age0  Age1  Age2  Age3  Age4  Age5  ...  \\\n",
       "0   2018  S08000015  Female   192454  1616  1645  1771  1822  1799  1815  ...   \n",
       "1   2018  S08000015    Male   177216  1727  1736  1932  1965  1875  1956  ...   \n",
       "2   2018  S08000016  Female    59281   472   501   555   570   603   615  ...   \n",
       "3   2018  S08000016    Male    55989   545   514   572   530   624   655  ...   \n",
       "4   2018  S08000017  Female    76469   641   614   633   657   626   683  ...   \n",
       "5   2018  S08000017    Male    72321   607   644   670   669   727   721  ...   \n",
       "6   2018  S08000019  Female   156903  1376  1458  1410  1495  1539  1561  ...   \n",
       "7   2018  S08000019    Male   149167  1486  1453  1677  1622  1639  1700  ...   \n",
       "8   2018  S08000020  Female   293822  2866  2906  3088  3025  3108  3076  ...   \n",
       "9   2018  S08000020    Male   290728  3041  3074  3316  3205  3208  3297  ...   \n",
       "10  2018  S08000022  Female   163493  1305  1353  1447  1575  1482  1569  ...   \n",
       "11  2018  S08000022    Male   158307  1388  1502  1553  1581  1600  1596  ...   \n",
       "12  2018  S08000024  Female   460831  4388  4474  4563  4638  4827  4840  ...   \n",
       "13  2018  S08000024    Male   436939  4607  4684  4932  4990  5035  4984  ...   \n",
       "14  2018  S08000025  Female    11151    96   100    97    94    87    91  ...   \n",
       "15  2018  S08000025    Male    11039    82   107    83   114   106   124  ...   \n",
       "16  2018  S08000026  Female    11277   106   109   126   123   118   142  ...   \n",
       "17  2018  S08000026    Male    11713   110   138   131   114   120   143  ...   \n",
       "18  2018  S08000028  Female    13583    92   117   113   115   127   137  ...   \n",
       "19  2018  S08000028    Male    13247   122   134   129   112   138   121  ...   \n",
       "20  2018  S08000029  Female   191290  1705  1722  1872  2000  1907  1978  ...   \n",
       "21  2018  S08000029    Male   180620  1798  1907  1925  2027  2093  2115  ...   \n",
       "22  2018  S08000030  Female   213394  1800  1888  1918  2011  1971  2174  ...   \n",
       "23  2018  S08000030    Male   202686  1904  1992  2082  2212  2181  2198  ...   \n",
       "24  2018  S08000031  Female   605223  5750  5843  6067  6086  6255  6429  ...   \n",
       "25  2018  S08000031    Male   569757  5974  6391  6306  6450  6555  6568  ...   \n",
       "26  2018  S08000032  Female   340178  3265  3311  3392  3558  3494  3587  ...   \n",
       "27  2018  S08000032    Male   319022  3441  3535  3724  3705  3707  3837  ...   \n",
       "\n",
       "    Age81  Age82  Age83  Age84  Age85  Age86  Age87  Age88  Age89  Age90plus  \n",
       "0    1388   1272   1131   1067    987    887    732    639    532       2302  \n",
       "1    1032    908    864    705    657    592    490    344    310        961  \n",
       "2     489    377    387    365    331    282    268    230    206        715  \n",
       "3     332    338    293    253    203    169    201    162    125        309  \n",
       "4     627    607    520    514    459    406    351    317    268        975  \n",
       "5     545    492    388    362    360    276    231    198    155        482  \n",
       "6    1025    917    831    682    623    628    473    451    405       1491  \n",
       "7     746    639    663    531    458    407    322    260    212        657  \n",
       "8    1723   1628   1553   1390   1245   1241   1069    883    797       3003  \n",
       "9    1313   1226   1122    986    876    727    632    514    414       1378  \n",
       "10   1192   1148   1073    919    841    816    715    584    512       2000  \n",
       "11    866    791    765    702    587    546    432    379    326        870  \n",
       "12   2443   2395   2141   1982   1776   1750   1588   1359   1095       4442  \n",
       "13   1750   1693   1541   1352   1152   1044    932    688    618       1971  \n",
       "14     85     64     64     69     50     40     51     53     34        129  \n",
       "15     65     58     69     50     37     37     25     35     25         70  \n",
       "16     68     53     68     56     49     36     32     32     37        114  \n",
       "17     59     59     52     36     30     28     30     19     19         48  \n",
       "18    124    103     97     99     99     91     66     58     60        231  \n",
       "19     82     68     70     65     52     36     40     21     43         99  \n",
       "20   1283   1180   1033    960    881    756    688    645    544       2092  \n",
       "21    930    848    770    650    604    501    395    343    324        941  \n",
       "22   1509   1427   1375   1271   1058   1052    959    827    720       2924  \n",
       "23   1123   1044    960    846    743    741    641    507    385       1240  \n",
       "24   3629   3326   3222   2931   2610   2484   2175   1891   1534       6030  \n",
       "25   2408   2156   1990   1649   1459   1373   1104    941    735       2396  \n",
       "26   2073   1996   1755   1579   1451   1270   1119    913    846       2859  \n",
       "27   1359   1324   1075    976    921    763    643    494    399       1198  \n",
       "\n",
       "[28 rows x 95 columns]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import the data for healthboard sizes\n",
    "\n",
    "df = pd.read_csv('data/raw/HB_pop_est_2018_only.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Year',\n",
       " 'HB2014',\n",
       " 'Sex',\n",
       " 'AllAges',\n",
       " 'Age0',\n",
       " 'Age1',\n",
       " 'Age2',\n",
       " 'Age3',\n",
       " 'Age4',\n",
       " 'Age5',\n",
       " 'Age6',\n",
       " 'Age7',\n",
       " 'Age8',\n",
       " 'Age9',\n",
       " 'Age10',\n",
       " 'Age11',\n",
       " 'Age12',\n",
       " 'Age13',\n",
       " 'Age14',\n",
       " 'Age15',\n",
       " 'Age16',\n",
       " 'Age17',\n",
       " 'Age18',\n",
       " 'Age19',\n",
       " 'Age20',\n",
       " 'Age21',\n",
       " 'Age22',\n",
       " 'Age23',\n",
       " 'Age24',\n",
       " 'Age25',\n",
       " 'Age26',\n",
       " 'Age27',\n",
       " 'Age28',\n",
       " 'Age29',\n",
       " 'Age30',\n",
       " 'Age31',\n",
       " 'Age32',\n",
       " 'Age33',\n",
       " 'Age34',\n",
       " 'Age35',\n",
       " 'Age36',\n",
       " 'Age37',\n",
       " 'Age38',\n",
       " 'Age39',\n",
       " 'Age40',\n",
       " 'Age41',\n",
       " 'Age42',\n",
       " 'Age43',\n",
       " 'Age44',\n",
       " 'Age45',\n",
       " 'Age46',\n",
       " 'Age47',\n",
       " 'Age48',\n",
       " 'Age49',\n",
       " 'Age50',\n",
       " 'Age51',\n",
       " 'Age52',\n",
       " 'Age53',\n",
       " 'Age54',\n",
       " 'Age55',\n",
       " 'Age56',\n",
       " 'Age57',\n",
       " 'Age58',\n",
       " 'Age59',\n",
       " 'Age60',\n",
       " 'Age61',\n",
       " 'Age62',\n",
       " 'Age63',\n",
       " 'Age64',\n",
       " 'Age65',\n",
       " 'Age66',\n",
       " 'Age67',\n",
       " 'Age68',\n",
       " 'Age69',\n",
       " 'Age70',\n",
       " 'Age71',\n",
       " 'Age72',\n",
       " 'Age73',\n",
       " 'Age74',\n",
       " 'Age75',\n",
       " 'Age76',\n",
       " 'Age77',\n",
       " 'Age78',\n",
       " 'Age79',\n",
       " 'Age80',\n",
       " 'Age81',\n",
       " 'Age82',\n",
       " 'Age83',\n",
       " 'Age84',\n",
       " 'Age85',\n",
       " 'Age86',\n",
       " 'Age87',\n",
       " 'Age88',\n",
       " 'Age89',\n",
       " 'Age90plus']"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#List all of our columns / variables - see all those Age(N) columns\n",
    "list(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HB2014</th>\n",
       "      <th>Sex</th>\n",
       "      <th>AllAges</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000015</td>\n",
       "      <td>Female</td>\n",
       "      <td>192454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000015</td>\n",
       "      <td>Male</td>\n",
       "      <td>177216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000016</td>\n",
       "      <td>Female</td>\n",
       "      <td>59281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000016</td>\n",
       "      <td>Male</td>\n",
       "      <td>55989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000017</td>\n",
       "      <td>Female</td>\n",
       "      <td>76469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000017</td>\n",
       "      <td>Male</td>\n",
       "      <td>72321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000019</td>\n",
       "      <td>Female</td>\n",
       "      <td>156903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000019</td>\n",
       "      <td>Male</td>\n",
       "      <td>149167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000020</td>\n",
       "      <td>Female</td>\n",
       "      <td>293822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000020</td>\n",
       "      <td>Male</td>\n",
       "      <td>290728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000022</td>\n",
       "      <td>Female</td>\n",
       "      <td>163493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000022</td>\n",
       "      <td>Male</td>\n",
       "      <td>158307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000024</td>\n",
       "      <td>Female</td>\n",
       "      <td>460831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000024</td>\n",
       "      <td>Male</td>\n",
       "      <td>436939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000025</td>\n",
       "      <td>Female</td>\n",
       "      <td>11151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000025</td>\n",
       "      <td>Male</td>\n",
       "      <td>11039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000026</td>\n",
       "      <td>Female</td>\n",
       "      <td>11277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000026</td>\n",
       "      <td>Male</td>\n",
       "      <td>11713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000028</td>\n",
       "      <td>Female</td>\n",
       "      <td>13583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000028</td>\n",
       "      <td>Male</td>\n",
       "      <td>13247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000029</td>\n",
       "      <td>Female</td>\n",
       "      <td>191290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000029</td>\n",
       "      <td>Male</td>\n",
       "      <td>180620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000030</td>\n",
       "      <td>Female</td>\n",
       "      <td>213394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000030</td>\n",
       "      <td>Male</td>\n",
       "      <td>202686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000031</td>\n",
       "      <td>Female</td>\n",
       "      <td>605223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000031</td>\n",
       "      <td>Male</td>\n",
       "      <td>569757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000032</td>\n",
       "      <td>Female</td>\n",
       "      <td>340178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>2018</td>\n",
       "      <td>S08000032</td>\n",
       "      <td>Male</td>\n",
       "      <td>319022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year     HB2014     Sex  AllAges\n",
       "0   2018  S08000015  Female   192454\n",
       "1   2018  S08000015    Male   177216\n",
       "2   2018  S08000016  Female    59281\n",
       "3   2018  S08000016    Male    55989\n",
       "4   2018  S08000017  Female    76469\n",
       "5   2018  S08000017    Male    72321\n",
       "6   2018  S08000019  Female   156903\n",
       "7   2018  S08000019    Male   149167\n",
       "8   2018  S08000020  Female   293822\n",
       "9   2018  S08000020    Male   290728\n",
       "10  2018  S08000022  Female   163493\n",
       "11  2018  S08000022    Male   158307\n",
       "12  2018  S08000024  Female   460831\n",
       "13  2018  S08000024    Male   436939\n",
       "14  2018  S08000025  Female    11151\n",
       "15  2018  S08000025    Male    11039\n",
       "16  2018  S08000026  Female    11277\n",
       "17  2018  S08000026    Male    11713\n",
       "18  2018  S08000028  Female    13583\n",
       "19  2018  S08000028    Male    13247\n",
       "20  2018  S08000029  Female   191290\n",
       "21  2018  S08000029    Male   180620\n",
       "22  2018  S08000030  Female   213394\n",
       "23  2018  S08000030    Male   202686\n",
       "24  2018  S08000031  Female   605223\n",
       "25  2018  S08000031    Male   569757\n",
       "26  2018  S08000032  Female   340178\n",
       "27  2018  S08000032    Male   319022"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We dont need the Age(N) columns for this. Drop them all - but we do need the AllAges one.\n",
    "# So, we keep only those columns whose first three chars are not 'age' \n",
    "cols = [c for c in df.columns if c.lower()[:3] != 'age']\n",
    "\n",
    "df=df[cols]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop the Year column - its all 2018 data anyway\n",
    "df = df.drop('Year', axis=1)\n",
    "\n",
    "#drop the Sex column - we'll soon be adding genders together\n",
    "df = df.drop('Sex', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "#change the remaining column names\n",
    "df.columns = ['Code',\"Pops\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now we need to add up what were the Male and Female total poulatioms for each Healthboard areas\n",
    "df = df.groupby(\"Code\").Pops.sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code</th>\n",
       "      <th>Pops</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>S08000015</td>\n",
       "      <td>369670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>S08000016</td>\n",
       "      <td>115270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>S08000017</td>\n",
       "      <td>148790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>S08000019</td>\n",
       "      <td>306070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>S08000020</td>\n",
       "      <td>584550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>S08000022</td>\n",
       "      <td>321800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>S08000024</td>\n",
       "      <td>897770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>S08000025</td>\n",
       "      <td>22190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>S08000026</td>\n",
       "      <td>22990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>S08000028</td>\n",
       "      <td>26830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>S08000029</td>\n",
       "      <td>371910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>S08000030</td>\n",
       "      <td>416080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>S08000031</td>\n",
       "      <td>1174980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>S08000032</td>\n",
       "      <td>659200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Code     Pops\n",
       "0   S08000015   369670\n",
       "1   S08000016   115270\n",
       "2   S08000017   148790\n",
       "3   S08000019   306070\n",
       "4   S08000020   584550\n",
       "5   S08000022   321800\n",
       "6   S08000024   897770\n",
       "7   S08000025    22190\n",
       "8   S08000026    22990\n",
       "9   S08000028    26830\n",
       "10  S08000029   371910\n",
       "11  S08000030   416080\n",
       "12  S08000031  1174980\n",
       "13  S08000032   659200"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check what it now looks like\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge our two data frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "#We use the common column name ('Code') to join the two dataframes\n",
    "dfnew = pd.merge(dfcodes, df, on='Code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now we can get rid of the Code column \n",
    "dfnew = dfnew.drop('Code', axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfnew.columns = ['Name','Population']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Ayrshire and Arran</td>\n",
       "      <td>369670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Borders</td>\n",
       "      <td>115270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Dumfries and Galloway</td>\n",
       "      <td>148790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Forth Valley</td>\n",
       "      <td>306070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Grampian</td>\n",
       "      <td>584550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Highland</td>\n",
       "      <td>321800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Lothian</td>\n",
       "      <td>897770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Orkney</td>\n",
       "      <td>22190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Shetland</td>\n",
       "      <td>22990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Western Isles</td>\n",
       "      <td>26830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>Fife</td>\n",
       "      <td>371910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>Tayside</td>\n",
       "      <td>416080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>Greater Glasgow and Clyde</td>\n",
       "      <td>1174980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>Lanarkshire</td>\n",
       "      <td>659200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Name  Population\n",
       "0          Ayrshire and Arran      369670\n",
       "1                     Borders      115270\n",
       "2       Dumfries and Galloway      148790\n",
       "3                Forth Valley      306070\n",
       "4                    Grampian      584550\n",
       "5                    Highland      321800\n",
       "6                     Lothian      897770\n",
       "7                      Orkney       22190\n",
       "8                    Shetland       22990\n",
       "9               Western Isles       26830\n",
       "10                       Fife      371910\n",
       "11                    Tayside      416080\n",
       "12  Greater Glasgow and Clyde     1174980\n",
       "13                Lanarkshire      659200"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfnew"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dumping the data to file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's dump the data to a file for re-use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfnew.to_csv(r'data/processed/HB_Populations.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}