Loneliness Process Data - Northern Ireland.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import zipfile as zp\n",
    "import pandas as pd\n",
    "#from pypac import PACSession as Session #or use requests below if non-ONS\n",
    "from requests import Session\n",
    "from io import BytesIO\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "import re\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Summary Prescription Data for Northern Ireland\n",
    "\n",
    "This code has been created to work with 2016, 2017 and 2018 data, earlier data may have format inconsistencies that need to be dealt with."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set File Location of Prescribing Data\n",
    "\n",
    "These files are the \"GP Prescribing Data\" csv files that come from: https://www.opendatani.gov.uk/dataset/gp-prescribing-data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Path to zip files\n",
    "path = r\"[CHANGE THIS PATH]\\NI\\\\\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read in drug data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get drug data (NB some drugs duplicated for illnesses)\n",
    "drug_data = pd.read_csv(path + r\"..\\drug_list.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process Prescribing Data\n",
    "\n",
    "This code iterates over the monthly prescribing data, ultimately producing an aggregate table.\n",
    "\n",
    "Note, if you want to find prescribing for non-loneliness related diseases, all you need to do is provide a different set of drug_data and edit the code_loneliness function.\n",
    "\n",
    "**Filename format:** gp-prescribing---[month name]-[year].csv (e.g. gp-prescribing---january-2018.csv)\n",
    "\n",
    "**Columns:** Practice, Year, Month, VTM_NM, VMP_NM, AMP_NM, Presentation, Strength, Total Items, Total Quantity, Gross Cost (�), Actual Cost (�), BNF Code, BNF Chapter, BNF Section, BNF Paragraph, BNF Sub-Paragraph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# column names\n",
    "col_bnfname = \"VTM_NM\"\n",
    "col_items = \"Total Items\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to find loneliness related prescribing\n",
    "def code_loneliness(x):\n",
    "    out = {}\n",
    "    # coding by illness categories\n",
    "    for illness in drug_data['illness'].unique():\n",
    "        out[illness] = x[col_bnfname].str.contains(\"|\".join(drug_data[drug_data['illness'] == illness]['medication']),\n",
    "                                                 case=False, \n",
    "                                                 regex=True).astype('int16')\n",
    "    # Make dataframe\n",
    "    out = pd.DataFrame(out)\n",
    "    # Add loneliness related disease binary - avoids double counting some drugs.\n",
    "    out['loneliness'] = x[col_bnfname].str.contains(\"|\".join(drug_data['medication'].unique()),\n",
    "                                                  case = False, \n",
    "                                                  regex = True).astype('int16')\n",
    "    # Return dataframe multiplied by counts of items.\n",
    "    return out.multiply(x[col_items], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make dictionary for aggregation\n",
    "agg_cols = {col : 'sum' for col in drug_data['illness'].unique()}\n",
    "agg_cols[col_items] = 'sum'\n",
    "agg_cols['loneliness'] = 'sum'\n",
    "for key in ['Date']:\n",
    "    agg_cols[key] = 'first'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Open prescribing files in pandas.\n",
    "prescribe = pd.read_csv(path + \"Prescriptions\\\\gp-prescribing---september-2018.csv\", encoding = \"ISO-8859-1\")\n",
    "\n",
    "# at least one of the NI prescription files has a blank row at the end - drop it\n",
    "prescribe.dropna(inplace = True)\n",
    "\n",
    "prescribe.columns = prescribe.columns.str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018.0 9.0\n",
      "2018-9\n"
     ]
    }
   ],
   "source": [
    "# make a date column\n",
    "prescribe['Date'] = prescribe['Year'].astype(int).astype(str) + \"-\" + prescribe['Month'].astype(int).astype(str)\n",
    "print(prescribe['Year'].iloc[1], prescribe['Month'].iloc[1])\n",
    "print(prescribe['Date'].iloc[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Practice             float64\n",
       "Year                 float64\n",
       "Month                float64\n",
       "VTM_NM                object\n",
       "VMP_NM                object\n",
       "AMP_NM                object\n",
       "Presentation          object\n",
       "Strength              object\n",
       "Total Items          float64\n",
       "Total Quantity       float64\n",
       "Gross Cost (£)       float64\n",
       "Actual Cost (£)      float64\n",
       "BNF Code              object\n",
       "BNF Chapter          float64\n",
       "BNF Section          float64\n",
       "BNF Paragraph        float64\n",
       "BNF Sub-Paragraph    float64\n",
       "Date                  object\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prescribe.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gp-prescribing---april-2018.csv\n",
      "gp-prescribing---august-2018.csv\n",
      "gp-prescribing---december-2018.csv\n",
      "gp-prescribing---february-2018.csv\n",
      "gp-prescribing---january-2018.csv\n",
      "gp-prescribing---july-2018.csv\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\...\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3049: DtypeWarning: Columns (16) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gp-prescribing---june-2018.csv\n",
      "gp-prescribing---march-2018.csv\n",
      "gp-prescribing---may-2018.csv\n",
      "gp-prescribing---november-2018.csv\n",
      "gp-prescribing---october-2018.csv\n",
      "gp-prescribing---september-2018.csv\n"
     ]
    }
   ],
   "source": [
    "monthly_data = []\n",
    "\n",
    "for file in os.listdir(path + \"Prescriptions\"):\n",
    "    # Open prescribing files in pandas.\n",
    "    prescribe = pd.read_csv(path + \"Prescriptions\\\\\" + file, encoding = \"ISO-8859-1\")\n",
    "    \n",
    "    # at least one of the NI prescription files has a blank row at the end - drop it\n",
    "    prescribe.dropna(inplace = True)\n",
    "    \n",
    "    prescribe.columns = prescribe.columns.str.strip()\n",
    "    \n",
    "    # make a date column\n",
    "    prescribe['Date'] = prescribe['Year'].astype(int).astype(str) + \"-\" + prescribe['Month'].astype(int).astype(str)\n",
    "    \n",
    "    # Get counts of prescribing dataframe for loneliness related diseases\n",
    "    loneliness_prescribing = code_loneliness(prescribe[[col_bnfname, col_items]])\n",
    "    # merge dataframes\n",
    "    prescribe = prescribe.merge(loneliness_prescribing, left_index=True, right_index=True)\n",
    "    del loneliness_prescribing\n",
    "\n",
    "    # merge in address information\n",
    "    #prescribe = prescribe.merge(addr, left_on = 'PracticeID', right_on = 'PracticeId')\n",
    "    #del addr\n",
    "\n",
    "    # Create uniform postcode field\n",
    "    #prescribe['pcstrip'] = prescribe['Postcode'].str.replace(\"\\s\",\"\")\n",
    "\n",
    "    # get a summary - grouping by GPPractice\n",
    "    summary = prescribe.groupby('Practice', as_index=False).agg(agg_cols)\n",
    "    del prescribe\n",
    "\n",
    "    monthly_data.append(summary)\n",
    "    print(file)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate all the monthly data together.\n",
    "data = pd.concat(monthly_data, ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Practice</th>\n",
       "      <th>depression</th>\n",
       "      <th>alzheimers</th>\n",
       "      <th>blood pressure</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>diabeties</th>\n",
       "      <th>cardiovascular disease</th>\n",
       "      <th>insomnia</th>\n",
       "      <th>addiction</th>\n",
       "      <th>social anxiety</th>\n",
       "      <th>Total Items</th>\n",
       "      <th>loneliness</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>919.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>564.0</td>\n",
       "      <td>581.0</td>\n",
       "      <td>141.0</td>\n",
       "      <td>422.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>9823.0</td>\n",
       "      <td>2191.0</td>\n",
       "      <td>2018-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>1121.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>625.0</td>\n",
       "      <td>633.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>560.0</td>\n",
       "      <td>138.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>12108.0</td>\n",
       "      <td>2625.0</td>\n",
       "      <td>2018-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>203.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2874.0</td>\n",
       "      <td>608.0</td>\n",
       "      <td>2018-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.0</td>\n",
       "      <td>863.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>749.0</td>\n",
       "      <td>761.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>513.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11650.0</td>\n",
       "      <td>2382.0</td>\n",
       "      <td>2018-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.0</td>\n",
       "      <td>211.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2624.0</td>\n",
       "      <td>587.0</td>\n",
       "      <td>2018-4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Practice  depression  alzheimers  blood pressure  hypertension  diabeties  \\\n",
       "0       1.0       919.0        23.0           564.0         581.0      141.0   \n",
       "1       3.0      1121.0        42.0           625.0         633.0      128.0   \n",
       "2       5.0       203.0        10.0           149.0         154.0       56.0   \n",
       "3       6.0       863.0        38.0           749.0         761.0      143.0   \n",
       "4      10.0       211.0         7.0           162.0         163.0       39.0   \n",
       "\n",
       "   cardiovascular disease  insomnia  addiction  social anxiety  Total Items  \\\n",
       "0                   422.0     105.0        0.0            22.0       9823.0   \n",
       "1                   560.0     138.0        3.0            15.0      12108.0   \n",
       "2                   175.0      10.0        0.0             1.0       2874.0   \n",
       "3                   513.0      64.0        0.0            14.0      11650.0   \n",
       "4                   130.0      37.0        0.0            10.0       2624.0   \n",
       "\n",
       "   loneliness    Date  \n",
       "0      2191.0  2018-4  \n",
       "1      2625.0  2018-4  \n",
       "2       608.0  2018-4  \n",
       "3      2382.0  2018-4  \n",
       "4       587.0  2018-4  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data[\"loneliness\"] > 0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save aggregated data\n",
    "data.to_csv(path + \"processed_data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import GP Practices\n",
    "\n",
    "## Use only General Practice surgeries\n",
    "\n",
    "Use the 'GP Practice List Sizes' data from: https://www.opendatani.gov.uk/dataset/gp-practice-list-sizes to get GP surgery codes and number of registered patients.\n",
    "\n",
    "Note that NI only publishes these data four times a year (in Jan, Apr, Jul and Oct) - so we'll just use the previous quarter's numbers until the next quarter (e.g. use Jan 18 data for Jan/Feb/Mar)\n",
    "\n",
    "**Filename format:** gp-practice-reference-file---[month name]-[year].csv (e.g. gp-practice-reference-file---january-2018.csv)\n",
    "\n",
    "**Columns:** Practice No, Practice Name, Address1, Address2, Address3, Postcode, LCG, Registered_Patients\n",
    "(The columns are inconsistently named - I've renamed them by hand)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(path + \"processed_data.csv\", index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get GP files\n",
    "gp_path = path + r\"GP data\\\\\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gp-practice-reference-file---april-2018.csv\n",
      "gp-practice-reference-file---august-2018.csv\n",
      "gp-practice-reference-file---december-2018.csv\n",
      "gp-practice-reference-file---february-2018.csv\n",
      "gp-practice-reference-file---january-2018.csv\n",
      "gp-practice-reference-file---july-2018.csv\n",
      "gp-practice-reference-file---june-2018.csv\n",
      "gp-practice-reference-file---march-2018.csv\n",
      "gp-practice-reference-file---may-2018.csv\n",
      "gp-practice-reference-file---november-2018.csv\n",
      "gp-practice-reference-file---october-2018.csv\n",
      "gp-practice-reference-file---september-2018.csv\n"
     ]
    }
   ],
   "source": [
    "gp_combine = []\n",
    "\n",
    "for file in os.listdir(gp_path):\n",
    "    # read file into pandas\n",
    "    year = file[-8:-4]\n",
    "    # month = date[4:]\n",
    "    \n",
    "    try:\n",
    "        month = re.search(\"---([a-z]+)-\", file).group(1)\n",
    "    except AttributeError:\n",
    "        month = '' # it should work but just in case, set to a blank string if not month found\n",
    "    \n",
    "    # Deal with different file structures\n",
    "    gp_data = pd.read_csv(gp_path + file)\n",
    "    #gp_data.columns = gp_data.columns.str.upper().str.replace(\" \",\"_\")\n",
    "    gp_data = gp_data.rename(columns = {'Practice No': 'Practice'})\n",
    "    gp_data['Date'] = month + \"-\" + year\n",
    "    gp_combine.append(gp_data[['Date','Practice','Postcode','Registered_Patients']])\n",
    "    print(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate all the gp data together.\n",
    "gp_data = pd.concat(gp_combine, ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Date</th>\n",
       "      <th>Practice</th>\n",
       "      <th>Postcode</th>\n",
       "      <th>Registered_Patients</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>april-2018</td>\n",
       "      <td>1</td>\n",
       "      <td>BT4 1NS</td>\n",
       "      <td>6874.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>april-2018</td>\n",
       "      <td>3</td>\n",
       "      <td>BT4 1NS</td>\n",
       "      <td>7158.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>april-2018</td>\n",
       "      <td>5</td>\n",
       "      <td>BT5 5FB</td>\n",
       "      <td>1434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>april-2018</td>\n",
       "      <td>6</td>\n",
       "      <td>BT8 8HR</td>\n",
       "      <td>10873.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>april-2018</td>\n",
       "      <td>10</td>\n",
       "      <td>BT5 4PX</td>\n",
       "      <td>2495.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Practice Postcode  Registered_Patients\n",
       "0  april-2018         1  BT4 1NS               6874.0\n",
       "1  april-2018         3  BT4 1NS               7158.0\n",
       "2  april-2018         5  BT5 5FB               1434.0\n",
       "3  april-2018         6  BT8 8HR              10873.0\n",
       "4  april-2018        10  BT5 4PX               2495.0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gp_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3664, 13)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the unique codes for GP surgeries and subset the prescribing data according to these codes.\n",
    "gp_ids = gp_data['Practice'].unique()\n",
    "data = data[data['Practice'].isin(gp_ids)].copy()\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make date a datetime variable\n",
    "gp_data['Date'] = pd.to_datetime(gp_data['Date'], format = '%B-%Y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       2018-4\n",
       "1       2018-4\n",
       "2       2018-4\n",
       "3       2018-4\n",
       "4       2018-4\n",
       "5       2018-4\n",
       "6       2018-4\n",
       "7       2018-4\n",
       "8       2018-4\n",
       "9       2018-4\n",
       "10      2018-4\n",
       "11      2018-4\n",
       "12      2018-4\n",
       "13      2018-4\n",
       "14      2018-4\n",
       "15      2018-4\n",
       "16      2018-4\n",
       "17      2018-4\n",
       "18      2018-4\n",
       "19      2018-4\n",
       "20      2018-4\n",
       "21      2018-4\n",
       "22      2018-4\n",
       "23      2018-4\n",
       "24      2018-4\n",
       "25      2018-4\n",
       "26      2018-4\n",
       "27      2018-4\n",
       "28      2018-4\n",
       "29      2018-4\n",
       "         ...  \n",
       "3634    2018-9\n",
       "3635    2018-9\n",
       "3636    2018-9\n",
       "3637    2018-9\n",
       "3638    2018-9\n",
       "3639    2018-9\n",
       "3640    2018-9\n",
       "3641    2018-9\n",
       "3642    2018-9\n",
       "3643    2018-9\n",
       "3644    2018-9\n",
       "3645    2018-9\n",
       "3646    2018-9\n",
       "3647    2018-9\n",
       "3648    2018-9\n",
       "3649    2018-9\n",
       "3650    2018-9\n",
       "3651    2018-9\n",
       "3652    2018-9\n",
       "3653    2018-9\n",
       "3654    2018-9\n",
       "3655    2018-9\n",
       "3656    2018-9\n",
       "3657    2018-9\n",
       "3658    2018-9\n",
       "3659    2018-9\n",
       "3660    2018-9\n",
       "3661    2018-9\n",
       "3662    2018-9\n",
       "3663    2018-9\n",
       "Name: Date, Length: 3664, dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make date a datetime variable - days are assigned as first day of the month.\n",
    "data['Date'] = pd.to_datetime(data['Date'], format = '%Y-%m')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge on the basis of date and PracCode - produces some nulls for counts.\n",
    "# It may be possible to predict missing values using a time-series model.\n",
    "data = data.merge(gp_data, how = 'left', on = ['Date','Practice'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save aggregated data\n",
    "data.to_csv(path + \"processed_data_with_GPs.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add Postcode Location\n",
    "\n",
    "Postcode location is pulled in from the latest ONS NSPL (National Statistics Postcode Lookup). There is a guide to fields here: http://geoportal.statistics.gov.uk/datasets/0a404beab6f544be8fb72d0c2b12d62b\n",
    "\n",
    "NB - If using an ONS laptop `pip install pypac`, if not comment pypac import above and use requests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv(path + \"processed_data_with_GPs.csv\", index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in postcode lookup data\n",
    "# This is the persistent link to the latest ONS NSPL\n",
    "postcode_url = \"http://geoportal.statistics.gov.uk/datasets/055c2d8135ca4297a85d624bb68aefdb_0.csv\"\n",
    "\n",
    "with Session() as session:\n",
    "    response = session.get(postcode_url, verify = False)\n",
    "\n",
    "field_dtypes = {'objectid': 'int32', 'pcd':'str', 'pcd2': 'str', 'pcds':'str', 'dointr':'str','doterm':'str',\n",
    "                'usertype':'int8','oseast1m': 'float', 'osnorth1m': 'float', 'osgrdind':'int8', 'lat':'float', \n",
    "                'long':'float', 'X':'float', 'Y':'float', 'imd': 'int8',\n",
    "                'oa11':'str', 'cty': 'str', 'ced':'str', 'laua': 'str', 'ward': 'str', 'hlthau':'str',\n",
    "                'ctry': 'str','pcon': 'str','eer': 'str','teclec': 'str','ttwa': 'str','pct': 'str','nuts': 'str',\n",
    "                'park': 'str','lsoa11': 'str','msoa11': 'str','wz11': 'str','ccg': 'str','bua11': 'str',\n",
    "                'buasd11': 'str','ru11ind': 'str','oac11': 'str','lep1': 'str','lep2': 'str','pfa': 'str',\n",
    "                'ced': 'str','nhser': 'str','rgn': 'str','calncv': 'str','stp': 'str'}\n",
    "\n",
    "pc = pd.read_csv(BytesIO(response.content), dtype = field_dtypes)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create pcstrip for matching\n",
    "pc['pcstrip'] = pc['pcd'].str.replace(\"\\s\",\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NB - here I'm joining 2011 LSOA, 2011 MSOA, Rural-Urban Indicator, Region (formally GOR), Local Authority Area (effectively district), and IMD score (NB separate for each country). However, you can add any of the geography codes available in the NSPL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create uniform postcode field\n",
    "data['pcstrip'] = data['Postcode'].str.replace(\"\\s\",\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_temp = data.merge(pc[['pcstrip','oseast1m','osnrth1m','lsoa11','msoa11','ru11ind','rgn','laua','imd']], \n",
    "                       how = 'left',\n",
    "                       on = 'pcstrip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: pcstrip, dtype: int64)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for missing postcodes\n",
    "data_temp[data_temp['oseast1m'].isnull()]['pcstrip'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# no missing postcodes in NI\n",
    "# new_pcs = {'DL154SB': 'DL54SB', 'WR59QT':'WR52QT', 'DN18QN':'DN48QN', 'HU32AE':'HU34AE','GL10QD': 'GL13NN',\n",
    "#            'ME122TZ':'ME102TZ', 'BS378NG':'BS374NG', 'CV115PO':'CV115PQ', 'TW152EA':'TW153EA', 'EN24EJ':'EN80BX', \n",
    "#            'YO302JS':'YO306JA','L62UN':'L67UN', 'NG698DB':'NG98DA', 'HA24ES':'HA14ES'}\n",
    "\n",
    "# data['pcstrip'] = data['pcstrip'].map(new_pcs).fillna(data['pcstrip'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge data\n",
    "data = data.merge(pc[['pcstrip','oseast1m','osnrth1m','lsoa11','msoa11','ru11ind','rgn','laua','imd']], \n",
    "                  how = 'left', \n",
    "                  on = 'pcstrip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save aggregated data\n",
    "data.to_csv(path + \"processed_data_with_postcodes_GPs.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check Postcodes \n",
    "\n",
    "Some Practice Codes have more than one postcode associated with them. Possible reasons for this are:\n",
    "* Practices move to a new location.\n",
    "* Practices are assigned a new postcode but don't physically move.\n",
    "* Practice postcodes are wrongly entered at a particular wave and subsequently fixed.\n",
    "\n",
    "There are 4 practices codes which have more than 1 postcode assigned to them, this is about 1.2% of unique practices.\n",
    "\n",
    "All 4 practices have 2 postcodes associated with them.\n",
    "\n",
    "One practice falls within the same LSOA, the othet three fall within 2 different LSOAs.\n",
    "\n",
    "We'll ignore this for now - this will require some more advanced cleaning - useful to be aware of though."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for 1 postcode per Practice Code\n",
    "pc_prac_counts = data.groupby('Practice')['pcstrip'].unique().map(len)\n",
    "# 28 practices have more than 1 postcode associated with it.\n",
    "pc_prac_counts[pc_prac_counts > 1].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.011904761904761904"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Practice codes with multiple associated postcodes account forc. 2.9% of the data\n",
    "pc_prac_counts[pc_prac_counts > 1].count()/len(pc_prac_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    4\n",
       "Name: pcstrip, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pc_prac_counts[pc_prac_counts > 1].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    3\n",
       "1    1\n",
       "Name: lsoa11, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count of these Practices falling within the same LSOA\n",
    "(data[data['Practice'].isin(pc_prac_counts[pc_prac_counts > 1].index)]\n",
    " .groupby('Practice')['lsoa11']\n",
    " .unique()\n",
    " .map(len)\n",
    " .value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate Statistics from Prescribing Counts\n",
    "\n",
    "## Percentages At Postcode Level\n",
    "\n",
    "Aggregate observations to postcodes and compute percentages for 'depression', 'alzheimers', 'blood pressure', 'hypertension', 'diabetes', 'cardiovascular disease', 'insomnia', 'addiction', 'social anxiety', and 'loneliness'.\n",
    "\n",
    "## Outlier Removal\n",
    "\n",
    "Should we remove some GPs on the basis that they have very low/high values which might indicate they are not accessible to the general population, and instead represent specialist services?\n",
    "\n",
    "Currently, we won't do this, but it's an advanced task to look into.\n",
    "\n",
    "## Standardise Percentages\n",
    "\n",
    "Should we standardise within time points, or across them? Or standardise with GP practices or across them?\n",
    "\n",
    "Can't standardize within GPs, as can't then compare between GPs.\n",
    "\n",
    "Can't standardise across GPs within years, as can't then compare between years.\n",
    "\n",
    "Can't standardise across GPs across years, as can't then disambiguate changes to rank order over time.\n",
    "\n",
    "<u> First Step </u>\n",
    "\n",
    "Take the average percentage of disease groups within postcodes annually - this is then an annual summary measure of prescribing by postcode. Aggregation entire depends on desired time frame for analysis.\n",
    "\n",
    "NB, this is a mean of percentages - could also calculate an overall percentage by summing monthly counts by year and dividing through by monthly sum of items.\n",
    "\n",
    "<u> Second Step </u>\n",
    "\n",
    "z-score standardise for earliest year observed across GPs. Store mean and standard deviation.\n",
    "\n",
    "z-score standardise subsequent years wrt baseline mean and standard deviation.\n",
    "\n",
    "OR\n",
    "\n",
    "Use min-max normalisation by year (decile normalisation?). This standardises the different percentages to the same range - although in theory they're comparable anyway...\n",
    "\n",
    "## Aggregation and Percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(path + \"processed_data_with_postcodes_GPs.csv\", index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Practice                  float64\n",
       "depression                float64\n",
       "alzheimers                float64\n",
       "blood pressure            float64\n",
       "hypertension              float64\n",
       "diabeties                 float64\n",
       "cardiovascular disease    float64\n",
       "insomnia                  float64\n",
       "addiction                 float64\n",
       "social anxiety            float64\n",
       "Total Items               float64\n",
       "loneliness                float64\n",
       "Date                       object\n",
       "Postcode                   object\n",
       "Registered_Patients       float64\n",
       "pcstrip                    object\n",
       "oseast1m                  float64\n",
       "osnrth1m                  float64\n",
       "lsoa11                     object\n",
       "msoa11                     object\n",
       "ru11ind                   float64\n",
       "rgn                        object\n",
       "laua                       object\n",
       "imd                       float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make dictionary for aggregation\n",
    "# counts to sum\n",
    "agg_cols = {col : 'sum' for col in drug_data['illness'].unique()}\n",
    "agg_cols[col_items] = 'sum'\n",
    "agg_cols['loneliness'] = 'sum'\n",
    "agg_cols['Registered_Patients'] = 'sum'\n",
    "\n",
    "# Other data to preserve\n",
    "for key in ['Postcode', \n",
    "            'oseast1m', 'osnrth1m', 'lsoa11', 'msoa11','ru11ind', 'rgn', 'laua', 'imd']:\n",
    "    agg_cols[key] = 'first'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Do aggregation and produce counts by postcode by date.\n",
    "data = data.groupby(['pcstrip', 'Date'], as_index=False).agg(agg_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate percentages\n",
    "perc_cols = drug_data['illness'].unique()\n",
    "target_cols = perc_cols + '_perc'\n",
    "\n",
    "# Percentages for discrete illness groups\n",
    "data[target_cols] = data[perc_cols].divide(data[col_items], axis=0) * 100\n",
    "\n",
    "# Overall percentage for loneliness realted disease prescribing\n",
    "data['loneliness_perc'] = data['loneliness'].divide(data[col_items], axis=0) * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Standardisation (z-scores)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Firstly aggregate percentages by postcodes by year.\n",
    "#data['Year'] = data['Date'].dt.year\n",
    "data['Year'] = 2018\n",
    "\n",
    "# Aggregation\n",
    "cols = {'Registered_Patients': 'mean', 'oseast1m':'first', 'osnrth1m':'first',\n",
    "        'lsoa11': 'first', 'msoa11': 'first', 'ru11ind': 'first', 'rgn': 'first', 'laua':'first', 'imd': 'first',\n",
    "        'depression_perc': 'mean', 'alzheimers_perc': 'mean', 'blood pressure_perc': 'mean', 'hypertension_perc': 'mean',\n",
    "        'diabeties_perc': 'mean', 'cardiovascular disease_perc': 'mean', 'insomnia_perc': 'mean', 'addiction_perc': 'mean',\n",
    "        'social anxiety_perc': 'mean', 'loneliness_perc': 'mean'}\n",
    "\n",
    "data = data.groupby(['pcstrip','Year'], as_index=False).agg(cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The mean value returns a value broadly in the centre of the distribution of respective disease classes.\n",
    "# Therefore we'll go with an un-truncated arithmetic mean.\n",
    "# Can always revisit this assumption later.\n",
    "\n",
    "per_cols = ['depression_perc', 'alzheimers_perc', 'blood pressure_perc', 'hypertension_perc', \n",
    "            'diabeties_perc', 'cardiovascular disease_perc', 'insomnia_perc', 'addiction_perc',\n",
    "            'social anxiety_perc', 'loneliness_perc']\n",
    "\n",
    "# Get mean and std for baseline (2016)\n",
    "mean_std = data[data['Year'] == 2018][per_cols].agg(['mean','std'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make new column names.\n",
    "std_cols = [col[:-4] + 'zscore' for col in per_cols]\n",
    "\n",
    "zscores = []    \n",
    "# z-score standardise for each year by baseline mean and std \n",
    "for year in [2016,2017,2018]:\n",
    "    zscores.append((data.loc[data['Year'] == year, per_cols] - mean_std.loc['mean', per_cols]) / mean_std.loc['std', per_cols])\n",
    "\n",
    "zscores = pd.concat(zscores).sort_index()\n",
    "data[std_cols] = zscores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAzIAAAFpCAYAAABH4x0wAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAHPlJREFUeJzt3X+QpHldH/D3J6yEHwt3xhsHayGMlNxW8NYK7khpjLor/riwFKRSJoEKFkTMlpoQErFkjWVpJWW5avBHEqssC08wIuuKqNSdoqg3YizudI9fe3CuImz0TuSk0NVBIp5+88f0wtzc9HbPdE8//V1fr6qu7R9PP/2eZ57+VL/n6e6t1loAAAB68veGDgAAALBXigwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0J1Di3ywm266qa2trU1c7qMf/Wge//jHH3ygGck5Xz3k7CFjsrec99xzz4dbaysHHGnuzJNh9JCzh4zJ9Zez11ly4403ts/6rM8aOsZYPewny55RvtktOuPU86S1trDT8ePH2zTuvPPOqZYbmpzz1UPOHjK2trecSS60Bc6BeZ3Mk2H0kLOHjK1dfzl7nSU333zznrfJIvWwnyx7Rvlmt+iM084Tby0DAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALozschU1W1V9WBV3bvj+pdV1aWqek9Vfc/BRQQAAHi4aY7IvCbJrduvqKqTSZ6f5HNaa5+d5L/PPxoAAMDuJhaZ1tpbk3xkx9Vfn+Rsa+2vRss8eADZAAAAdrXfz8jcnOSLquruqvr1qvq8eYYCAAC4lmqtTV6oai3J7a21W0aX703ya0lenuTzkvxUkqe1XVZWVaeTnE6S1dXV4+fOnZv4eJubmzl8+PDUP8RQ5JyvHnL2kDHZW86TJ0/e01pbP+BIc2GeDK+HnD1kTK6/nL3OkpWVlePnz58fONF4y7qfXHzgSpLk2JEbljbjVfLNbtEZp54nrbWJpyRrSe7ddvnNSU5su/z7SVYmref48eNtGnfeeedUyw1NzvnqIWcPGVvbW84kF9oUc2DZTubJMHrI2UPG1q6/nL3OkptvvnnP22SRlnU/eeorb29PfeXtrbXlzXiVfLNbdMZp58l+31r2c0m+NEmq6uYkj07y4X2uCwAAYE8OTVqgql6f5ESSm6rq/iTfnuS2JLeN3mL28SQvHrUnAACAAzexyLTWXjjmphfNOQsAAMBU9vvWMgAAgMEoMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdGdikamq26rqwaq6d5fbvqmqWlXddDDxAAAAHmmaIzKvSXLrziur6ilJvjzJH8w5EwAAwDVNLDKttbcm+cguN31/km9O0uYdCgAA4Fr29RmZqnpekgdaa++acx4AAICJqrXJB1Sqai3J7a21W6rqcUnuTPIVrbUrVXU5yXpr7cNj7ns6yekkWV1dPX7u3LmJj7e5uZnDhw9P+zMMRs756iFnDxmTveU8efLkPa219QOONBfmyfB6yNlDxuT6y9nrLFlZWTl+/vz5gRONt6z7ycUHriRJjh254WEZt1+/LJZ1G1617PmSxWecep601iaekqwluXd0/liSB5NcHp0eytbnZJ40aT3Hjx9v07jzzjunWm5ocs5XDzl7yNja3nImudCmmAPLdjJPhtFDzh4ytnb95ex1ltx888173iaLtKz7yVNfeXt76itvb609POP265fFsm7Dq5Y9X2uLzzjtPDm014bUWruY5NOvXp50RAYAAGDepvn65dcneVuSo1V1f1W99OBjAQAAjDfxiExr7YUTbl+bWxoAAIAp7OtbywAAAIakyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQnUNDBwAAYHmtnblj3/e5fPbUvOPAJzgiAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANCdiUWmqm6rqger6t5t131vVf1OVb27qn62qm482JgAAACfNM0RmdckuXXHdW9Jcktr7XOS/G6Sb5lzLgAAgLEmFpnW2luTfGTHdb/cWntodPGuJE8+gGwAAAC7msdnZL4myS/OYT0AAABTqdba5IWq1pLc3lq7Zcf135pkPcm/aGNWVFWnk5xOktXV1ePnzp2b+Hibm5s5fPjwxOWGJud89ZCzh4zJ3nKePHnyntba+gFHmgvzZHg95OwhY3L95ex1lqysrBw/f/78wInGW4b95OIDV8beduzIDdnc3MwHrvzNNddx7MgN8441tWXYhtey7PmSxWecdp7su8hU1YuTfF2SZ7fW/nKaUOvr6+3ChQsTl9vY2MiJEyemWeWg5JyvHnL2kDHZW86q6ubFx3bmyTB6yNlDxuT6y9nrLDl69Gi7dOnS0DHGWob9ZO3MHWNvu3z2VDY2NvKSN3/0muu4fPbUvGNNbRm24bUse75k8RmnnSeH9rnyW5O8MsmXTFtiAAAA5mWar19+fZK3JTlaVfdX1UuT/K8kT0jylqp6Z1X98AHnBAAA+ISJR2Raay/c5eofPYAsAAAAU5nHt5YBAAAslCIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAsnbmjqyduWPoGDA1RQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgOxOLTFXdVlUPVtW92677B1X1lqr6vdG/n3qwMQEAAD5pmiMyr0ly647rziT51dba05P86ugyAADAQkwsMq21tyb5yI6rn5/ktaPzr03yz+ecCwAAYKz9fkZmtbX2wSQZ/fvp84sEAABwbdVam7xQ1VqS21trt4wu/1lr7cZtt/9pa23Xz8lU1ekkp5NkdXX1+Llz5yY+3ubmZg4fPjxN/kHJOV895OwhY7K3nCdPnryntbZ+wJHmwjwZXg85e8iYXH85e50lKysrx8+fPz9wovHmuZ9cfOBKkuTYkRt2vf6qSbfvXHZzczMfuPI313zsnetcpGV/ri17vmTxGaedJ/stMpeSnGitfbCqPiPJRmvt6KT1rK+vtwsXLkx8vI2NjZw4cWLickOTc756yNlDxmRvOauqmxcf25knw+ghZw8Zk+svZ6+z5OjRo+3SpUtDxxhrnvvJ2pk7kiSXz57a9fqrJt2+c9mNjY285M0fveZj71znIi37c23Z8yWLzzjtPNnvW8velOTFo/MvTvLz+1wPAADAnk3z9cuvT/K2JEer6v6qemmSs0m+vKp+L8mXjy4DAAAsxKFJC7TWXjjmpmfPOQsAAMBU9vvWMgAAgMEoMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAOAT1s7ckbUzdwwdAyZSZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6M1ORqar/XFXvqap7q+r1VfWYeQUDAAAYZ99FpqqOJPmPSdZba7ckeVSSF8wrGAAAwDizvrXsUJLHVtWhJI9L8kezRwIAALi2aq3t/85VL0/ynUk+luSXW2v/ZpdlTic5nSSrq6vHz507N3G9m5ubOXz48L5zLYqc89VDzh4yJnvLefLkyXtaa+sHHGkuzJPh9ZCzh4zJ9Zez11mysrJy/Pz58wMnGm+e+8nFB64kSY4dueFhl8eZZrljR27I5uZmPnDlb/aU5eq6F2HZn2vLni9ZfMZp58m+i0xVfWqSn0nyr5P8WZKfTvKG1tpPjLvP+vp6u3DhwsR1b2xs5MSJE/vKtUhyzlcPOXvImOwtZ1V18+JjO/NkGD3k7CFjcv3l7HWWHD16tF26dGnoGGPNcz9ZO3NHkuTy2VMPuzzONMtdPnsqGxsbecmbP7qnLFfXvQjL/lxb9nzJ4jNOO09meWvZlyX5QGvtT1prf53kjUn+yQzrAwAAmMosReYPknx+VT2uqirJs5PcN59YAAAA4+27yLTW7k7yhiRvT3JxtK4fmVMuAACAsQ7NcufW2rcn+fY5ZQEAAJjKrF+/DAAAsHCKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdOTR0AAAAls/amTsGfdzLZ08N8vj0wxEZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN2ZqchU1Y1V9Yaq+p2quq+qvmBewQAAAMY5NOP9fzDJm1trX1VVj07yuDlkAgAAuKZ9F5mqemKSL07ykiRprX08ycfnEwsAAGC8Wd5a9rQkf5Lkx6rqHVX16qp6/JxyAQAAjFWttf3dsWo9yV1JvrC1dndV/WCSP2+tfduO5U4nOZ0kq6urx8+dOzdx3Zubmzl8+PC+ci2SnPPVQ84eMiZ7y3ny5Ml7WmvrBxxpLsyT4fWQs4eMyfWXs9dZsrKycvz8+fMDJxpvlv3k4gNXkiTHjtxwzcuzWn1s8qGP7e0+u2UYl3NWy/5cW/Z8yeIzTjtPZikyT0pyV2ttbXT5i5Kcaa2dGnef9fX1duHChYnr3tjYyIkTJ/aVa5HknK8ecvaQMdlbzqrq5sXHdubJMHrI2UPG5PrL2essOXr0aLt06dLQMcaaZT9ZO3NHkuTy2VPXvDyrVxx7KK+6uLdPK+yWYVzOWS37c23Z8yWLzzjtPNn3W8taa3+c5A+r6ujoqmcnee9+1wcAADCtWb+17GVJXjf6xrL3J/m3s0cCAAC4tpmKTGvtnUm6O4wMAAD0bab/EBMAAGAIigwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7hwaOgAAAAdn7cwd17w8hGkyXF3m8tlTe1rntMvTP0dkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRn5iJTVY+qqndU1e3zCAQAADDJPI7IvDzJfXNYDwAAwFRmKjJV9eQkp5K8ej5xAAAAJpv1iMwPJPnmJH87hywAAABTqdba/u5Y9dwkz2mtfUNVnUjyTa215+6y3Okkp5NkdXX1+Llz5yaue3NzM4cPH95XrkWSc756yNlDxmRvOU+ePHlPa239gCPNhXkyvB5y9pAxuf5y9jpLVlZWjp8/f37gROPtZz+5+MCVA0qzu9XHJh/62OzrOXbkhiTj81+9fZyr99u53LI/15Y9X7L4jNPOk1mKzHcl+eokDyV5TJInJnlja+1F4+6zvr7eLly4MHHdGxsbOXHixL5yLZKc89VDzh4yJnvLWVXdvPjYzjwZRg85e8iYXH85e50lR48ebZcuXRo6xlj72U/WztxxMGHGeMWxh/Kqi4dmXs/ls6eSjM9/9fZxrt5v53LL/lxb9nzJ4jNOO0/2/day1tq3tNae3FpbS/KCJL92rRIDAAAwL/4fGQAAoDuzHwdM0lrbSLIxj3UBAABM4ogMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDu7LvIVNVTqurOqrqvqt5TVS+fZzAAAIBxDs1w34eSvKK19vaqekKSe6rqLa21984pGwAAwK72fUSmtfbB1trbR+f/Isl9SY7MKxgAAMA4c/mMTFWtJXlmkrvnsT4AAIBrqdbabCuoOpzk15N8Z2vtjbvcfjrJ6SRZXV09fu7cuYnr3NzczOHDh2fKtQhyzlcPOXvImOwt58mTJ+9pra0fcKS5ME+G10POHjIm11/OXmfJysrK8fPnzw+caLwHP3IlH/pYcuzIDQ+7/uIDVx6x7NVldrvtIK0+NvnQxxb6kA+z8+feua2W/bm27PmSxWecdp7MVGSq6lOS3J7kl1pr3zdp+fX19XbhwoWJ693Y2MiJEyf2nWtR5JyvHnL2kDHZW86q6ubFx3bmyTB6yNlDxuT6y9nrLDl69Gi7dOnS0DHG+p+v+/m86uKhXD576mHXr5254xHLXl1mt9sO0iuOPZRXXZzlY9ez2flz79xWy/5cW/Z8yeIzTjtPZvnWskryo0num6bEAAAAzMssn5H5wiRfneRLq+qdo9Nz5pQLAABgrH0fB2yt/Z8kNccsAAAAU5nLt5YBAAAskiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcODR0AAKAHa2fuSJJcPntqsHVfXW6vt/1ddHV7vOLYQzmx47qr23nS5Vke9yD2k14d1DZxRAYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgOzMVmaq6taouVdX7qurMvEIBAABcy76LTFU9KskPJflnSZ6R5IVV9Yx5BQMAABhnliMyz0ryvtba+1trH09yLsnz5xMLAABgvFmKzJEkf7jt8v2j6wAAAA5Utdb2d8eqf5nkK1trXzu6/NVJntVae9mO5U4nOT26eDTJpSlWf1OSD+8r2GLJOV895OwhY7K3nE9tra0cZJh5MU+WQg85e8iYXH85e50ltyS5d8A4k/Swnyx7Rvlmt+iMU82TWYrMFyT5jtbaV44uf0uStNa+a18rfPi6L7TW1mddz0GTc756yNlDxqSfnIvQy7aQc356yJjIuSyW/edb9nzJ8meUb3bLmnGWt5b9dpKnV9VnVtWjk7wgyZvmEwsAAGC8Q/u9Y2vtoar6D0l+KcmjktzWWnvP3JIBAACMse8ikySttV9I8gtzyrLdjxzAOg+CnPPVQ84eMib95FyEXraFnPPTQ8ZEzmWx7D/fsudLlj+jfLNbyoz7/owMAADAUGb5jAwAAMAgBi0yVXVrVV2qqvdV1Zldbv/7VfVTo9vvrqq1xaecKuc3VtV7q+rdVfWrVfXUZcy5bbmvqqpWVQv/9olpMlbVvxptz/dU1U8uOuMow6Tf+T+sqjur6h2j3/tzBsh4W1U9WFW7fm1obfkfo5/h3VX1uYvOuEjmyeIybltusFkyenzzZH4ZzZMkVfVNo336pqGzbFdV31tVvzPa9j9bVTcOnSmZflYMoaqeMnpe3Td6/r986EzjVNWjRs//24fOslNV3VhVbxjtf/fV1rcWL4/W2iCnbH1BwO8neVqSRyd5V5Jn7FjmG5L88Oj8C5L81JLmPJnkcaPzX7+sOUfLPSHJW5PclWR92TImeXqSdyT51NHlT1/GbZmt94p+/ej8M5JcHiDnFyf53CT3jrn9OUl+MUkl+fwkdy8645L9zsyTOWUcLTfYLNnDtjRPps/5d36eJHlKtr7A6P8muWnoPDuyfUWSQ6Pz353ku5cg01SzYsB8n5Hkc0fnn5Dkd5cp346s35jkJ5PcPnSWXbK9NsnXjs4/OsmNQ2fafhryiMyzkryvtfb+1trHk5xL8vwdyzw/WxswSd6Q5NlVVQvMmEyRs7V2Z2vtL0cX70ry5AVnTKbbnkny35J8T5L/t8hwI9Nk/HdJfqi19qdJ0lp7cMEZk+lytiRPHJ2/IckfLTDfVoDW3prkI9dY5PlJfrxtuSvJjVX1GYtJt3DmyQIzjgw5SxLzZK7MkyTJ9yf55mz9PpZKa+2XW2sPjS4O9Tpjp2lnxSBaax9srb19dP4vktyX5MiwqR6pqp6c5FSSVw+dZaeqemK2/sjxo0nSWvt4a+3Phk31cEMWmSNJ/nDb5fvzyB3sE8uMnsBXknzaQtLtkmFkt5zbvTRbf7VatIk5q+qZSZ7SWhvq0OU02/LmJDdX1W9W1V1VdevC0n3SNDm/I8mLqur+bH1z38sWE21P9rrv9sw8mZ8eZklinizadT1Pqup5SR5orb1r6CxT+JoM8zpjp272idFbiZ+Z5O5hk+zqB7JVoP926CC7eFqSP0nyY6O3vr26qh4/dKjtZvr65Rnt9pfQnX8FmWaZgzZ1hqp6UZL1JF9yoIl2d82cVfX3svXXppcsKtAuptmWh7L1dpAT2fqL029U1S0L/gvANDlfmOQ1rbVXjd4v+r9HOZdpEC3D82dRzJP56WGWJObJoi3D82cmVfUrSZ60y03fmuS/ZOvtW4O5Vr7W2s+PlvnWJA8led0is43RxT5RVYeT/EyS/9Ra+/Oh82xXVc9N8mBr7Z6qOjF0nl0cytZbTl/WWru7qn4wyZkk3zZsrE8assjcn633o1715DzycPrVZe6vqkPZOuR+rUPfB2GanKmqL8vWMPyS1tpfLSjbdpNyPiHJLUk2Ru+meVKSN1XV81prF5Yk49Vl7mqt/XWSD1TVpWy9EPntxUT8RIZJOV+a5NYkaa29raoek+SmJEO8dWWcqfbd64R5Mj89zJLEPFm07udJa+3Ldru+qo4l+cwk7xrt009O8vaqelZr7Y+HzndVVb04yXOTPLuNPrAwsKXfJ6rqU7JVYl7XWnvj0Hl28YVJnjf6go/HJHliVf1Ea+1FA+e66v4k97fWrh7JekO2iszyGOrDOdkqUe/P1vC4+iGxz96xzL/Pwz+ce35Jcz4zWx94e/oyb88dy29k8R/2n2Zb3prktaPzN2XrsPWnLWHOX0zyktH5f5St4V0D/N7XMv7Duafy8A/n/tai8y3Z78w8mVPGHcsvfJbsYVuaJ3vLap5s/ayXs3wf9r81yXuTrAydZVumPc2KAfJVkh9P8gNDZ5ky74ks54f9fyPJ0dH570jyvUNneli+gTfOc7L1LRK/n61Dp0nyX5M8b3T+MUl+Osn7kvxWkqctac5fSfKhJO8cnd60jDl3LDvUi49J27KSfN9oYF9M8oJl3JbZ+mah3xwN7ncm+YoBMr4+yQeT/HW2/mry0iRfl+Trtm3LHxr9DBeH+H0v2e/MPJlTxh3LDjJLptyW5sn0Gc2TT26Ly1m+IvO+bBXxq3Phh4fONMr1iH17WU5J/mm23ur27m3b7TlD57pG3hNZziLzj5NcGG3Hn8voWyCX5VSjkAAAAN0Y9D/EBAAA2A9FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO/8fxoJWUzg2UBoAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 1008x432 with 3 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# plot zscores for loneliness\n",
    "f, [ax1, ax2, ax3] = plt.subplots(1,3, figsize = (14,6), sharey = True)\n",
    "\n",
    "# Note that there appears to be increasing variation in lonelines prescribing over time.\n",
    "# These means are comparable as standardised using 2016 mean and std.\n",
    "data[data['Year'] == 2016]['loneliness_zscore'].hist(bins=100, ax = ax1)\n",
    "data[data['Year'] == 2017]['loneliness_zscore'].hist(bins=100, ax = ax2)\n",
    "data[data['Year'] == 2018]['loneliness_zscore'].hist(bins=100, ax = ax3);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save aggregated data\n",
    "data.to_csv(path + \"processed_data_with_postcodes_GPs_stats.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Loneliness Variable\n",
    "\n",
    "The actual loneliness variable we work with is the sum of the standardised scores of: depression, alzheimers, hypertension, insomnia, addiction and social anxiety, for each year of interest.\n",
    "\n",
    "This means that the loneliness variable is actually an equally weighted index of the above domains."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sum function ignores NAs\n",
    "data['loneills'] =  data[['depression_zscore', 'alzheimers_zscore', 'hypertension_zscore', 'insomnia_zscore',\n",
    "                          'addiction_zscore','social anxiety_zscore']].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAzIAAAFpCAYAAABH4x0wAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAFlZJREFUeJzt3X+MpPddH/D3B66B1Jc6CSZX10m9UJmIKFZD7hSlQqJ3CgQTSzhIUCUS1FLTXqEkqkQqeVv+qFVU9fojICFF/YkVl4ocbQXE8qWF4J5rFeE052JyDpGbNJzAjuU0SrC4NC0YffvHztmb8+3Ns7ezM/PZe72kRzvPzHdn3/M8M9/b9z7PzNUYIwAAAJ183aoDAAAA7JYiAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANCOIgMAALRzaJk/7KabbhobGxtzx33lK1/JDTfcsP+B9kjOxeqQs0PGZHc5H3vssS+OMb55nyMtnPlkNTrk7JAxOXg5D/pcMk+X/XmJvPuvW+Z1yjt5PhljLG05evTomOLs2bOTxq2anIvVIWeHjGPsLmeSc2OJ88CiFvPJanTI2SHjGAcv50GfS+bpsj8vkXf/dcu8TnmnzidOLQMAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAArgMbm2eysXlm1TFgYRQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBALgObWyeycbmmVXHgGumyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADtzC0yVfW6qjpbVZ+uqk9V1d+eXf/qqvpYVX1m9vVV+x8XAABg2hGZ55O8f4zx7UnemuTHq+oNSTaTPDTGuC3JQ7N1AACAfTe3yIwxnhlj/I/Z5T9M8ukktyS5K8n9s2H3J3nnfoUEAADYblfvkamqjSTfkeTjSY6MMZ5JtspOktcsOhwAAMCV1Bhj2sCqw0n+a5J/OMb4par6gzHGK7fd/uUxxkveJ1NVJ5OcTJIjR44cPX369NyfdfHixRw+fHjiQ1gdORerQ84OGZPd5Txx4sRjY4xj+xxpIcwnq9chZ4eMycHLedDnknk67M/zTz+XJLn9lhtz8eLF/O5zf/LC+rrrsH0v1y3zOuWdPJ+MMeYuSf5Ukl9N8hPbrnsyyc2zyzcneXLe/Rw9enRMcfbs2UnjVk3OxeqQs0PGMXaXM8m5MWEeWLfFfLIaHXJ2yDjGwct50OeSeTrsz1vveXDces+DY4ytvNvX112H7Xu5bpnXKe/U+WTKp5ZVkp9L8ukxxk9vu+mBJHfPLt+d5CPTOhYAAMDeHJow5juT/EiS81X1+Oy6v5fkVJJ/X1XvSfJ7SX5ofyICAAB8rblFZozx35LUDje/bbFxAAAA5tvVp5YBAACsA0UGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAOAA2dg8k43NM1e9/fzTzy0xEewPRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgnblFpqruq6ovVNUT2667t6qerqrHZ8s79jcmAADAi6YckflQkjuucP3PjDHeNFs+uthYAAAAO5tbZMYYjyT50hKyAAAATLKX98i8t6o+OTv17FULSwQAADBHjTHmD6raSPLgGOONs/UjSb6YZCT5qSQ3jzH+2g7fezLJySQ5cuTI0dOnT8/9eRcvXszhw4enPYIVknOxOuTskDHZXc4TJ048NsY4ts+RFsJ8snodcnbImBy8nAd9Lplnnfbn+aefS5LcfsuNV7w+SY68PHn2q7niuHW0Ttt3qm6Z1ynv5PlkjDF3SbKR5Ind3nb5cvTo0THF2bNnJ41bNTkXq0PODhnH2F3OJOfGhNfvui3mk9XokLNDxjEOXs6DPpfMs07789Z7Hhy33vPgjtffes+D42f/3a/sOG4drdP2napb5nXKO3U+uaZTy6rq5m2rP5DkiZ3GAgAALNqheQOq6sNJjie5qaqeSvL3kxyvqjdl69SyC0n+5j5mBAAA+Bpzi8wY491XuPrn9iELAADAJHv51DIAAICVUGQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2jm06gAAAOyfjc0zq44A+8IRGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQC4jm1snsnG5plVx4BdU2QAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaObTqAAAArN7G5pkrXn/h1J1LTgLTOCIDAAC0o8gAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtDO3yFTVfVX1hap6Ytt1r66qj1XVZ2ZfX7W/MQEAAF405YjMh5Lccdl1m0keGmPcluSh2ToAAMBSzC0yY4xHknzpsqvvSnL/7PL9Sd654FwAAAA7utb3yBwZYzyTJLOvr1lcJAAAgKurMcb8QVUbSR4cY7xxtv4HY4xXbrv9y2OMK75PpqpOJjmZJEeOHDl6+vTpuT/v4sWLOXz48JT8KyXnYnXI2SFjsrucJ06ceGyMcWyfIy2E+WT1OuTskDE5eDkP+lwyzzrtz/NPPzd3zJGXJ89+ddr93X7LjXtMtHfrtH2n6pZ5nfJOnU+utcg8meT4GOOZqro5ycNjjNfPu59jx46Nc+fOzf15Dz/8cI4fPz533KrJuVgdcnbImOwuZ1W1+eVjO/PJanTI2SFjcvByHvS5ZJ512p8bm2fmjnn/7c/nA+cPTbq/C6fu3GukPVun7TtVt8zrlHfqfHKtp5Y9kOTu2eW7k3zkGu8HAABg16Z8/PKHk/xmktdX1VNV9Z4kp5J8T1V9Jsn3zNYBAACWYu4xxTHGu3e46W0LzgIAADDJtZ5aBgAAsDKKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADtzP0PMQEAWE8bm2dWHQFWxhEZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABo59CqAwAAcGUbm2eSJBdO3XnF6+F65ogMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtHNoL99cVReS/GGSP0ny/Bjj2CJCAQAAXM2eiszMiTHGFxdwPwAAAJM4tQwAAGhnr0VmJPm1qnqsqk4uIhAAAMA8Nca49m+u+nNjjM9X1WuSfCzJ+8YYj1w25mSSk0ly5MiRo6dPn557vxcvXszhw4evOdeyyLlYHXJ2yJjsLueJEyce6/L+NvPJ6nXI2SFjcvByHvS5ZJ792p/nn35u4feZJEdenjz71d1/3+233JjkxVyX1ncyddw8XV4v23XLvE55p84neyoyX3NHVfcmuTjG+Gc7jTl27Ng4d+7c3Pt6+OGHc/z48YXk2k9yLlaHnB0yJrvLWVVtfvnYznyyGh1ydsiYHLycB30umWe/9ufG5pmF32eSvP/25/OB87t/q/SFU3cmeTHXpfWdTB03T5fXy3bdMq9T3qnzyTWfWlZVN1TVKy5dTvL2JE9c6/0BAABMtZdPLTuS5Jer6tL9/MIY4z8vJBUAAMBVXHORGWN8LslfXGAWAACASXz8MgAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANDOoVUHAAC43mxsnkmSXDh156Tr18WlfLAOHJEBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABo59CqAwAAXO82Ns9cdX1dXcp54dSdK07C9cgRGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoJ1Dqw4AAHC92tg8s+oIC7XT47l0/YVTd84df/mYZdgpH+vNERkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2Dq06AABABxubZ5IkF07dOWncJdvHX37bQTH1cU3ZhlO380FxPTze/XqMjsgAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7eypyFTVHVX1ZFV9tqo2FxUKAADgaq65yFTV1yf5YJLvS/KGJO+uqjcsKhgAAMBO9nJE5i1JPjvG+NwY44+SnE5y12JiAQAA7GwvReaWJL+/bf2p2XUAAAD7qsYY1/aNVT+U5HvHGH99tv4jSd4yxnjfZeNOJjk5W319kicn3P1NSb54TcGWS87F6pCzQ8ZkdzlvHWN8836GWRTzyVrokLNDxuTg5Tzoc8k8XfbnJfLuv26Z1ynvpPlkL0XmLyW5d4zxvbP1v5skY4x/dE13+LX3fW6McWyv97Pf5FysDjk7ZEz65FyGLttCzsXpkDGR86Dptp3k3X/dMnfLm+zt1LJPJLmtqr6lql6W5F1JHlhMLAAAgJ0dutZvHGM8X1XvTfKrSb4+yX1jjE8tLBkAAMAOrrnIJMkY46NJPrqgLNv9q324z/0g52J1yNkhY9In5zJ02RZyLk6HjImcB0237STv/uuWuVvea3+PDAAAwKrs5T0yAAAAK7HSIlNVd1TVk1X12aravMLt31BVvzi7/eNVtbH8lJNy/kRV/U5VfbKqHqqqW9cx57ZxP1hVo6qW/skUUzJW1V+Zbc9PVdUvLDvjLMO8ff7nq+psVf3WbL+/YwUZ76uqL1TVEzvcXlX1s7PH8MmqevOyMy6T+WR5GbeNW9lcMvv55pPFZTSf7FFV3VtVT1fV47Nl6ftxqqmv8XVRVReq6vxsu55bdZ4rudJrqKpeXVUfq6rPzL6+apUZt9shb5vn8AvGGCtZsvUBAf8rybcmeVmS307yhsvG/K0k/2J2+V1JfnFNc55I8qdnl39sXXPOxr0iySNJHk1ybN0yJrktyW8ledVs/TXruC2zdR7pj80uvyHJhRXk/K4kb07yxA63vyPJf0pSSd6a5OPLzrhm+8x8sqCMs3Erm0t2sS3NJ9Nzmk/2vg3vTfJ3Vp1jQs5Jr/F1WpJcSHLTqnPMyfiS11CSf5Jkc3Z5M8k/XnXOOXlbPIe3L6s8IvOWJJ8dY3xujPFHSU4nueuyMXcluX92+T8meVtV1RIzJhNyjjHOjjH+z2z10SSvXXLGZNr2TJKfytYL6/8uM9zMlIx/I8kHxxhfTpIxxheWnDGZlnMk+TOzyzcm+fwS820FGOORJF+6ypC7kvzbseXRJK+sqpuXk27pzCdLzDizyrkkMZ8slPnkujL1Nc4u7PAa2v7vzv1J3rnUUFcx4TXfwiqLzC1Jfn/b+lOz6644ZozxfJLnknzTUtJdIcPMlXJu955s/dVq2ebmrKrvSPK6McaDywy2zZRt+W1Jvq2qfqOqHq2qO5aW7kVTct6b5Ier6qlsfXLf+5YTbVd2+9ztzHyyOB3mksR8smzX03yyF++dnXp33zqdRnSZjvtyJPm1qnqsqk6uOswuHBljPJMks6+vWXGeKTo8h1+wyiJzpb+EXv4RalPG7LfJGarqh5McS/JP9zXRlV01Z1V9XZKfSfL+pSV6qSnb8lC2Tgc5nuTdSf5NVb1yn3NdbkrOdyf50Bjjtdk65eLnZ9t4nazD62dZzCeL02EuScwny7YOr5+Vq6pfr6onrrDcleSfJ/kLSd6U5JkkH1hp2J113JffOcZ4c5LvS/LjVfVdqw50QHV5Dr9gT/+PzB49leR129Zfm5ceTr805qmqOpStQ+7LPgw2JWeq6ruT/GSSvzzG+H9LyrbdvJyvSPLGJA/Pzqb5s0keqKrvH2Ms641zU/f5o2OMP07yu1X1ZLZ+EfnEciK+kGFezvckuSNJxhi/WVXfmOSmJKs4dWUnk567B4T5ZHE6zCWJ+WTZrqf5ZEdjjO+eMq6q/nWSVR6xvJp2+3KM8fnZ1y9U1S9n6/S4R1abapJnq+rmMcYzs1Mx1+k1/RJjjGcvXV7z5/ALVvkXn08kua2qvqWqXpatN98+cNmYB5LcPbv8g0n+y5i9G2mJ5uacnWbxL5N8/4rOwU7m5BxjPDfGuGmMsTHG2MjWuffL/sVjyj7/lWy92TlVdVO2Tg353BIzJtNy/l6StyVJVX17km9M8r+XmnK+B5L81dmnDb01yXOXDnEfQOaTJWVck7lkbs4Z88niXE/zyTW57D1DP5Dkip8AtwamPCfXRlXdUFWvuHQ5yduzvtv2ctv/3bk7yUdWmGWuRs/hF63ykwaydQj9f2br0zN+cnbdP8jWP4rJ1mT+H5J8Nsl/T/Kta5rz15M8m+Tx2fLAOua8bOzDWc0nDc3blpXkp5P8TpLzSd61jtsyW58s9BvZ+rSXx5O8fQUZP5ytQ79/nK2/sL0nyY8m+dFt2/KDs8dwfhX7e832mflkQRkvG7uSuWTitjSfTM9oPtn7Nvz52bb5ZLZ+gb151ZmukvUlz8l1XbL16Wq/PVs+ta55d3gNfVOSh5J8Zvb11avOOSdvm+fwpaVmDwYAAKCNdXszIQAAwFyKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADt/H/p9cznRKotPAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 1008x432 with 3 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# plot zscores for loneills\n",
    "f, [ax1, ax2, ax3] = plt.subplots(1,3, figsize = (14,6), sharey = True)\n",
    "\n",
    "# Note that there appears to be increasing variation in lonelines prescribing over time.\n",
    "data[data['Year'] == 2016]['loneills'].hist(bins=100, ax = ax1)\n",
    "data[data['Year'] == 2017]['loneills'].hist(bins=100, ax = ax2)\n",
    "data[data['Year'] == 2018]['loneills'].hist(bins=100, ax = ax3);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save aggregated data\n",
    "data.to_csv(path + \"final_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}