Loneliness Process Data - Scotland.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",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Summary Prescription Data for Scotland\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 \"Prescriptions in the Community\" .csv files that come from: https://www.opendata.nhs.scot/dataset/prescriptions-in-the-community"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Path to zip files\n",
    "path = r\"[CHANGE THIS PATH]\\Scotland\\\\\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read in drug data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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:** pitc[year][month].csv (e.g. pitc201801.csv)\n",
    "\n",
    "**Columns:** HBT2014, GPPractice, BNFItemCode, BNFItemDescription, ClassOfPreparationCode, NumberOfPaidItems, PaidQuantity, GrossIngredientCost, PaidDateMonth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# column names\n",
    "col_bnfname = \"BNFItemDescription\"\n",
    "col_items = \"NumberOfPaidItems\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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": 39,
   "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', 'HBT2014']:\n",
    "    agg_cols[key] = 'first'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pitc201801.csv\n",
      "pitc201802.csv\n",
      "pitc201803.csv\n",
      "pitc201804.csv\n",
      "pitc201805.csv\n",
      "pitc201806.csv\n",
      "pitc201807.csv\n",
      "pitc201808.csv\n",
      "pitc201809.csv\n",
      "pitc201810.csv\n",
      "pitc201811.csv\n",
      "pitc201812.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)\n",
    "    prescribe.columns = prescribe.columns.str.strip()\n",
    "    # Rename 'PaidDateMonth' column to 'date'\n",
    "    prescribe.rename(columns = {'PaidDateMonth': 'Date'}, inplace = True) \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('GPPractice', as_index=False).agg(agg_cols)\n",
    "    del prescribe\n",
    "\n",
    "    monthly_data.append(summary)\n",
    "    print(file)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate all the monthly data together.\n",
    "data = pd.concat(monthly_data, ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>GPPractice</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>NumberOfPaidItems</th>\n",
       "      <th>loneliness</th>\n",
       "      <th>Date</th>\n",
       "      <th>HBT2014</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10002</td>\n",
       "      <td>508</td>\n",
       "      <td>21</td>\n",
       "      <td>691</td>\n",
       "      <td>693</td>\n",
       "      <td>61</td>\n",
       "      <td>431</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>9060</td>\n",
       "      <td>1760</td>\n",
       "      <td>201801</td>\n",
       "      <td>S08000027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10017</td>\n",
       "      <td>527</td>\n",
       "      <td>36</td>\n",
       "      <td>625</td>\n",
       "      <td>630</td>\n",
       "      <td>80</td>\n",
       "      <td>373</td>\n",
       "      <td>72</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>9557</td>\n",
       "      <td>1720</td>\n",
       "      <td>201801</td>\n",
       "      <td>S08000027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10036</td>\n",
       "      <td>289</td>\n",
       "      <td>21</td>\n",
       "      <td>450</td>\n",
       "      <td>452</td>\n",
       "      <td>50</td>\n",
       "      <td>285</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5677</td>\n",
       "      <td>1148</td>\n",
       "      <td>201801</td>\n",
       "      <td>S08000027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10106</td>\n",
       "      <td>493</td>\n",
       "      <td>23</td>\n",
       "      <td>754</td>\n",
       "      <td>759</td>\n",
       "      <td>97</td>\n",
       "      <td>516</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>9834</td>\n",
       "      <td>1949</td>\n",
       "      <td>201801</td>\n",
       "      <td>S08000027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10125</td>\n",
       "      <td>318</td>\n",
       "      <td>20</td>\n",
       "      <td>485</td>\n",
       "      <td>489</td>\n",
       "      <td>76</td>\n",
       "      <td>341</td>\n",
       "      <td>34</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>6662</td>\n",
       "      <td>1280</td>\n",
       "      <td>201801</td>\n",
       "      <td>S08000027</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   GPPractice  depression  alzheimers  blood pressure  hypertension  \\\n",
       "0       10002         508          21             691           693   \n",
       "1       10017         527          36             625           630   \n",
       "3       10036         289          21             450           452   \n",
       "4       10106         493          23             754           759   \n",
       "5       10125         318          20             485           489   \n",
       "\n",
       "   diabeties  cardiovascular disease  insomnia  addiction  social anxiety  \\\n",
       "0         61                     431        46          0               3   \n",
       "1         80                     373        72          2               2   \n",
       "3         50                     285        50          1               1   \n",
       "4         97                     516        60          1               7   \n",
       "5         76                     341        34          2               5   \n",
       "\n",
       "   NumberOfPaidItems  loneliness    Date    HBT2014  \n",
       "0               9060        1760  201801  S08000027  \n",
       "1               9557        1720  201801  S08000027  \n",
       "3               5677        1148  201801  S08000027  \n",
       "4               9834        1949  201801  S08000027  \n",
       "5               6662        1280  201801  S08000027  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data[\"loneliness\"] > 0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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 Contact Details and List Sizes' data from: https://www.opendata.nhs.scot/dataset/gp-practice-contact-details-and-list-sizes to get GP surgery codes and number of registered patients.\n",
    "\n",
    "Note that Scotland 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:** gppractices[year][month][day].csv (e.g. gppractices20180101.csv)\n",
    "\n",
    "**Columns:** PracticeCode, PracticeName, PracticeListSize, AddressLine1, AddressLine2, AddressLine3, AddressLine4, Postcode, TelephoneNumber, PracticeType, Dispensing, HB2014 [Health Board], HB1995 [Health Board], HSCP2016 [Health and Social Care Partnership], CA2011 [Council Area], DZ2011 [Data Zone], DZ2001 [Data Zone]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv(path + \"processed_data.csv\", index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get GP files\n",
    "gp_path = path + r\"GP data\\\\\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gppractices20180101.csv\n",
      "gppractices20180201.csv\n",
      "gppractices20180301.csv\n",
      "gppractices20180401.csv\n",
      "gppractices20180501.csv\n",
      "gppractices20180601.csv\n",
      "gppractices20180701.csv\n",
      "gppractices20180801.csv\n",
      "gppractices20180901.csv\n",
      "gppractices20181001.csv\n",
      "gppractices20181101.csv\n",
      "gppractices20181201.csv\n"
     ]
    }
   ],
   "source": [
    "gp_combine = []\n",
    "\n",
    "for file in os.listdir(gp_path):\n",
    "    # read file into pandas\n",
    "    date = file[-12:-6]\n",
    "    year = date[:4]\n",
    "    month = date[4:]\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 = {'PracticeCode': 'GPPractice'})\n",
    "    gp_data['Date'] = year + month\n",
    "    gp_combine.append(gp_data[['Date','GPPractice','Postcode','PracticeListSize']])\n",
    "    print(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate all the gp data together.\n",
    "gp_data = pd.concat(gp_combine, ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>GPPractice</th>\n",
       "      <th>Postcode</th>\n",
       "      <th>PracticeListSize</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201801</td>\n",
       "      <td>10002.0</td>\n",
       "      <td>DD2 5NH</td>\n",
       "      <td>7484.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201801</td>\n",
       "      <td>10017.0</td>\n",
       "      <td>PH7 3SA</td>\n",
       "      <td>6685.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201801</td>\n",
       "      <td>10036.0</td>\n",
       "      <td>PH15 2BL</td>\n",
       "      <td>4593.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201801</td>\n",
       "      <td>10106.0</td>\n",
       "      <td>DD5 1DU</td>\n",
       "      <td>6102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201801</td>\n",
       "      <td>10125.0</td>\n",
       "      <td>PH11 8EQ</td>\n",
       "      <td>4245.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Date  GPPractice  Postcode  PracticeListSize\n",
       "0  201801     10002.0   DD2 5NH            7484.0\n",
       "1  201801     10017.0   PH7 3SA            6685.0\n",
       "2  201801     10036.0  PH15 2BL            4593.0\n",
       "3  201801     10106.0   DD5 1DU            6102.0\n",
       "4  201801     10125.0  PH11 8EQ            4245.0"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gp_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11446, 14)"
      ]
     },
     "execution_count": 56,
     "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['GPPractice'].unique()\n",
    "data = data[data['GPPractice'].isin(gp_ids)].copy()\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make date a datetime variable\n",
    "gp_data['Date'] = pd.to_datetime(gp_data['Date'], format = '%Y%m')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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": 59,
   "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','GPPractice'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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": 62,
   "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": 63,
   "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": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create uniform postcode field\n",
    "data['pcstrip'] = data['Postcode'].str.replace(\"\\s\",\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: pcstrip, dtype: int64)"
      ]
     },
     "execution_count": 66,
     "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 Scotland\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": 67,
   "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": 70,
   "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 28 practices codes which have more than 1 postcode assigned to them, this is about 2.9% of unique practices.\n",
    "\n",
    "All 28 practices have 2 postcodes associated with them.\n",
    "\n",
    "3 of these practices fall within the same LSOA, 25 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": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for 1 postcode per Practice Code\n",
    "pc_prac_counts = data.groupby('GPPractice')['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": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.029166666666666667"
      ]
     },
     "execution_count": 72,
     "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": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    28\n",
       "Name: pcstrip, dtype: int64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pc_prac_counts[pc_prac_counts > 1].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    25\n",
       "1     3\n",
       "Name: lsoa11, dtype: int64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count of these Practices falling within the same LSOA\n",
    "(data[data['GPPractice'].isin(pc_prac_counts[pc_prac_counts > 1].index)]\n",
    " .groupby('GPPractice')['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": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(path + \"processed_data_with_postcodes_GPs.csv\", index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GPPractice                  int64\n",
       "depression                  int64\n",
       "alzheimers                  int64\n",
       "blood pressure              int64\n",
       "hypertension                int64\n",
       "diabeties                   int64\n",
       "cardiovascular disease      int64\n",
       "insomnia                    int64\n",
       "addiction                   int64\n",
       "social anxiety              int64\n",
       "NumberOfPaidItems           int64\n",
       "loneliness                  int64\n",
       "Date                       object\n",
       "HBT2014                    object\n",
       "Postcode                   object\n",
       "PracticeListSize          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": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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['PracticeListSize'] = 'sum'\n",
    "\n",
    "# Other data to preserve\n",
    "for key in ['HBT2014', 'Postcode', \n",
    "            'oseast1m', 'osnrth1m', 'lsoa11', 'msoa11','ru11ind', 'rgn', 'laua', 'imd']:\n",
    "    agg_cols[key] = 'first'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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": 80,
   "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": 81,
   "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 = {'PracticeListSize': 'mean', 'HBT2014': 'first', '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": 82,
   "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": 83,
   "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": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAzIAAAFpCAYAAABH4x0wAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAHVFJREFUeJzt3X+wpXddH/D3R9YUZDEJhFxioFwcYyq6I7B3qNSO7hqRSByTP8CBqU60qTv+YrDGKdc6Y9Pajks78deMrWOFujrKQik0mSwqMd2trZXoBgILxDSQrpgQw6hJdIWqW7/94z4hd3fv3nPu3fPre/N6zZy55znne8553+e55zv73ud5zqnWWgAAAHryBfMOAAAAsFWKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQnV2zfLHLLrusLS8vjxz3l3/5l3n2s589/UAXSM7J6iFnDxmTreW85557/qS19vwpR5o488l89JCzh4zJzsu50+eSSVi0bS7P5hYpzyJlSaafZ+z5pLU2s8vevXvbOI4ePTrWuHmTc7J6yNlDxta2ljPJ8TbDeWBSF/PJfPSQs4eMre28nDt9LpmERdvm8mxukfIsUpbWpp9n3PnEoWUAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQBgJpZXj2R59ci8Y7BDKDIAAEB3FBkAAKA7igwAANAdRQYAgJlyrgyToMgAAADdUWQAAIDuKDIAAEB3FBkAAKA7I4tMVV1dVfeuu/x5Vf1gVT23qu6sqgeGn5fOIjAAAMDIItNau7+19rLW2suS7E3y2STvTbKa5K7W2lVJ7hqWAQAApm6rh5Zdk+STrbU/THJ9kkPD7YeS3DDJYAAAAOez1SLzhiTvGK4vtdYeSZLh5+WTDAYAAHA+1Vobb2DVRUk+neQrW2uPVtXjrbVL1t3/WGvtnPNkqupAkgNJsrS0tPfw4cMjX+vUqVPZvXv3mL/C/Mg5WT3k7CFjsrWc+/fvv6e1tjLlSBNhPpm/HnL2kDHZeTl3+lwyCYu2zeeR58TDT5yxvOfKi+eaZzOLlGeRsiTTzzP2fNJaG+uStUPJ3r9u+f4kVwzXr0hy/6jn2Lt3bxvH0aNHxxo3b3JOVg85e8jY2tZyJjnexpwHFuliPpmPHnL2kLG1nZdzp88lk7Bo23weeV78ljvOuMw7z2YWKc8iZWlt+nnGnU+2cmjZG/PUYWVJcnuSG4frNya5bQvPBQAAsG1jFZmq+qIkr07ynnU3H0zy6qp6YLjv4OTjAQAAnGvXOINaa59N8ryzbvvTrH2KGQAAwExt9VPLAAAA5k6RAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAwFwsrx7J8uqRecegU4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0J2xikxVXVJV766qP6iq+6rqVVX13Kq6s6oeGH5eOu2wAAAAyfh7ZH4myW+01v5ekq9Ocl+S1SR3tdauSnLXsAwAADB1I4tMVX1xkq9L8rYkaa39dWvt8STXJzk0DDuU5IZphQQAAFivWmubD6h6WZJfSPLxrO2NuSfJm5M83Fq7ZN24x1pr5xxeVlUHkhxIkqWlpb2HDx8eGerUqVPZvXv3Fn6N+ZBzsnrI2UPGZGs59+/ff09rbWXKkSbCfDJ/PeTsIWOy83Lu9LlkEhZtm88jz4mHn9jw9j1XXmz9bGKRsiTTzzP2fNJa2/SSZCXJ6SR/f1j+mSQ/nuTxs8Y9Nuq59u7d28Zx9OjRscbNm5yT1UPOHjK2trWcSY63Ee/dRbyYT+ajh5w9ZGxt5+Xc6XPJJCzaNp9Hnhe/5Y4NL/PKs5lFyrNIWVqbfp5x55NxzpF5KMlDrbW7h+V3J3lFkker6ookGX5+ZvyeBQAAsH0ji0xr7Y+T/FFVXT3cdE3WDjO7PcmNw203JrltKgkBAADOsmvMcW9K8qtVdVGSB5N8V9ZK0Luq6qYkn0ry+ulEBAAAONNYRaa1dm/WzpU52zWTjQMAADDauN8jAwAAsDAUGQAAoDuKDAAA0B1FBgCAhbG8eiTLq0fmHYMOKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7uyadwAAAHa25dUj847ADmSPDAAA0B1FBgAA6I4iAwAAdEeRAQBgrpZXj+TEw084l4YtUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN3ZNc6gqjqZ5C+S/L8kp1trK1X13CTvTLKc5GSSb2utPTadmAAAAE/Zyh6Z/a21l7XWVobl1SR3tdauSnLXsAwAADB1F3Jo2fVJDg3XDyW54cLjAAAAjDZukWlJ3l9V91TVgeG2pdbaI0ky/Lx8GgEBAADOVq210YOqvqS19umqujzJnUnelOT21tol68Y81lq7dIPHHkhyIEmWlpb2Hj58eOTrnTp1Krt37x7/t5gTOSerh5w9ZEy2lnP//v33rDtkdKGZT+avh5w9ZEx2Xs6dPpdMwqJt81nmOfHwEyPHLD0refRzTy3vufLiKSYabZG21yJlSaafZ9z5ZKwic8YDqm5JcirJdyfZ11p7pKquSHKstXb1Zo9dWVlpx48fH/kax44dy759+7aUax7knKwecvaQMdlazqrq5h8f65lP5qOHnD1kTHZezp0+l0zCom3zWeZZXj0ycszNe07n1hNPfQ7VyYPXTTPSSIu0vRYpSzL9POPOJyMPLauqZ1fVc568nuSbknw0ye1JbhyG3Zjktu3HBQAAGN84H7+8lOS9VfXk+F9rrf1GVf1+kndV1U1JPpXk9dOLCQAA8JSRRaa19mCSr97g9j9Ncs00QgEA0L9xDimD7bqQj18GAACYC0UGAADojiIDAAB0R5EBAAC6o8gAALBwlleP+LAANqXIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQnbGLTFU9o6o+VFV3DMsvqaq7q+qBqnpnVV00vZgAAABP2coemTcnuW/d8luT/FRr7aokjyW5aZLBAAAAzmesIlNVL0xyXZJfHJYryTckefcw5FCSG6YREAAA4Gzj7pH56ST/LMnfDsvPS/J4a+30sPxQkisnnA0AAGBD1VrbfEDVtyR5bWvt+6pqX5IfTvJdSX63tfZlw5gXJXlfa23PBo8/kORAkiwtLe09fPjwyFCnTp3K7t27t/irzJ6ck9VDzh4yJlvLuX///ntaaytTjjQR5pP56yFnDxmTnZdzp88lk7Bo23yaeU48/MSWH7P0rOTRz517+54rL55Aoq1bpO21SFmS6ecZdz4Zp8j8RJLvSHI6yTOTfHGS9yZ5TZIXtNZOV9WrktzSWnvNZs+1srLSjh8/PjL8sWPHsm/fvpHj5k3OyeohZw8Zk63lrKpu/vGxnvlkPnrI2UPGZOfl3OlzySQs2jafZp7l1SNbfszNe07n1hO7zrn95MHrJhFpyxZpey1SlmT6ecadT0YeWtZa+5HW2gtba8tJ3pDkv7XW/lGSo0leNwy7McltF5AXAABgbBfyPTJvSfJDVfWJrJ0z87bJRAIAANjcufvvNtFaO5bk2HD9wSSvnHwkAACAzV3IHhkAAIC5UGQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6I4iAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN0ZWWSq6plV9XtV9eGq+lhV/cvh9pdU1d1V9UBVvbOqLpp+XAAAgPH2yPxVkm9orX11kpclubaqvibJW5P8VGvtqiSPJblpejEBAACeMrLItDWnhsUvHC4tyTckefdw+6EkN0wlIQAAwFnGOkemqp5RVfcm+UySO5N8MsnjrbXTw5CHklw5nYgAAABnqtba+IOrLkny3iQ/luQ/tda+bLj9RUne11rbs8FjDiQ5kCRLS0t7Dx8+PPJ1Tp06ld27d4+da17knKwecvaQMdlazv3799/TWluZcqSJMJ/MXw85e8iY7LycO30umYRF2+bTzHPi4Se2/JilZyWPfu7c2/dcefEEEm3dIm2vRcqSTD/PuPPJlopMklTVv0jy2SRvSfKC1trpqnpVkltaa6/Z7LErKyvt+PHjI1/j2LFj2bdv35ZyzYOck9VDzh4yJlvLWVXd/ONjPfPJfPSQs4eMyc7LudPnkklYtG0+zTzLq0e2/Jib95zOrSd2nXP7yYPXTSLSli3S9lqkLMn084w7n4zzqWXPH/bEpKqeleQbk9yX5GiS1w3Dbkxy2/bjAgAAjO/c2nuuK5IcqqpnZK34vKu1dkdVfTzJ4ar610k+lORtU8wJAADweSOLTGvtI0levsHtDyZ55TRCAQAAbGasTy0DAABYJIoMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAABbe8uqRLK8emXcMFogiAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQnV3zDgAAAOfju2M4H3tkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANCdXfMOAADAzrC8emTmr3Xy4HUze00Wiz0yAABAdxQZAACgO4oMAADQHUUGAADozsgiU1UvqqqjVXVfVX2sqt483P7cqrqzqh4Yfl46/bgAAADj7ZE5neTm1tpXJPmaJN9fVS9NsprkrtbaVUnuGpYBAACmbmSRaa090lr74HD9L5Lcl+TKJNcnOTQMO5TkhmmFBAAAWG9L58hU1XKSlye5O8lSa+2RZK3sJLl80uEAAAA2Uq218QZW7U7y35P8m9bae6rq8dbaJevuf6y1ds55MlV1IMmBJFlaWtp7+PDhka916tSp7N69e8xfYX7knKwecvaQMdlazv3799/TWluZcqSJMJ/MXw85e8iY7LycO30umYRF2+bTyHPi4Se2/dilZyWPfm70uD1XXnzGaz25PGmLtL0WKUsy/TzjzidjFZmq+sIkdyT5zdbaTw633Z9kX2vtkaq6Ismx1trVmz3PyspKO378+MjXO3bsWPbt2zdy3LzJOVk95OwhY7K1nFXVzT8+1jOfzEcPOXvImOy8nDt9LpmERdvm08izvHpk24+9ec/p3Hpi18hxJw9ed8ZrPbk8aYu0vRYpSzL9POPOJ+N8alkleVuS+54sMYPbk9w4XL8xyW3bCQoAALBVo2tv8rVJviPJiaq6d7jtnyc5mORdVXVTkk8lef10IgIAAJxpZJFprf3PJHWeu6+ZbBwAAHpzIYeUwXZt6VPLAAAAFoEiAwAAdEeRAQAAuqPIAAAA3VFkAACA7igyAABAdxQZAACgO4oMAADQHUUGAADojiIDAAB0R5EBAAC6o8gAAADdUWQAAIDuKDIAAEB3FBkAAKA7igwAANCdXfMOAAAA41pePbLh8smD1224zM5ljwwAANAdRQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHd8jwwAANty9ne6wCzZIwMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDuKDAAA0B1FBgAA6M7IIlNVb6+qz1TVR9fd9tyqurOqHhh+XjrdmAAAAE8ZZ4/MLyW59qzbVpPc1Vq7KsldwzIAAMBMjCwyrbXfTvJnZ918fZJDw/VDSW6YcC4AAIDz2u45MkuttUeSZPh5+eQiAQAAbK5aa6MHVS0nuaO19lXD8uOttUvW3f9Ya23D82Sq6kCSA0mytLS09/DhwyNf79SpU9m9e/c4+edKzsnqIWcPGZOt5dy/f/89rbWVKUeaCPPJ/PWQs4eMyc7LudPnkklYtG0+iTwnHn5iQmmSpWclj35u+4/fc+XFSZ7K9OTydi3S9lqkLMn084w7n2y3yNyfZF9r7ZGquiLJsdba1aOeZ2VlpR0/fnzk6x07diz79u0bOW7e5JysHnL2kDHZWs6q6uYfH+uZT+ajh5w9ZEx2Xs6dPpdMwqJt80nkWV49MpkwSW7eczq3nti17cefPHhdkqcyPbm8XYu0vRYpSzL9POPOJ9s9tOz2JDcO129Mcts2nwcAAGDLxvn45Xck+d0kV1fVQ1V1U5KDSV5dVQ8kefWwDAAAMBMj99+11t54nruumXAWAACAsWz30DIAAIC5UWQAAIDuKDIAAEB3FBkAAKA7igwAADvO8uqRiX7PDYtHkQEAALqjyAAAAN1RZAAAgO6M/EJMAADo/XyT9flPHrxujkmYFHtkAACA7igyAABAdxQZAACgO86RAQCge5M8h2d59Uhu3nM6+yb2jEyDPTIAAEB3FBkAAKA7igwAANAdRQYAAOiOIgMAwNPS8uqR7r/o8+lMkQEAALqjyAAAAN1RZAAAgO74QkwAAD7v7HNGTh68bk5Jpsd5MTuDPTIAAEB3FBkAAKA7igwAANAd58gAALBjXcj5ME+H84V6Zo8MAADQHUUGAADojiIDAAB0xzkyAAA8rT15LoxzYCZv/XlGk16/9sgAAADdUWQAAIDuKDIAAEB3FBkAAKA7F3Syf1Vdm+RnkjwjyS+21g5OJBUAwIKZ5Qnhs3itEw8/ke9cPfL51zjfF0deyBdK9mbU77qdL8g833P6YIELt+09MlX1jCQ/l+Sbk7w0yRur6qWTCgYAAHA+F3Jo2SuTfKK19mBr7a+THE5y/WRiAQAAnN+FFJkrk/zRuuWHhtsAAACmqlpr23tg1euTvKa19k+G5e9I8srW2pvOGncgyYFh8eok94/x9Jcl+ZNtBZstOSerh5w9ZEy2lvPFrbXnTzPMpJhPFkIPOXvImOy8nDt9LpmERdvm8mxukfIsUpZk+nnGmk8upMi8KsktrbXXDMs/kiSttZ/Y1hOe+dzHW2srF/o80ybnZPWQs4eMST85Z6GXdSHn5PSQMZHz6WjR1qU8m1ukPIuUJVmcPBdyaNnvJ7mqql5SVRcleUOS2ycTCwAA4Py2/fHLrbXTVfUDSX4zax+//PbW2scmlgwAAOA8Luh7ZFpr70vyvgllWe8XpvCc0yDnZPWQs4eMST85Z6GXdSHn5PSQMZHz6WjR1qU8m1ukPIuUJVmQPNs+RwYAAGBeLuQcGQAAgLmYa5Gpqmur6v6q+kRVrW5w/9+pqncO999dVcuzTzlWzh+qqo9X1Ueq6q6qevEi5lw37nVV1apq5p82MU7Gqvq2YX1+rKp+bdYZhwyjtvnfraqjVfWhYbu/dg4Z315Vn6mqj57n/qqqnx1+h49U1StmnXGWephPzCWTZT6ZaEbzyRRU1S1V9XBV3TtcNty2477nJpDn31XVHwzb8L1Vdcl5xp2sqhND5uMTzrAwc3VVvWh47903zBFv3mDMvqp6Yt02/LFp5Rleb9N1P8v3YlVdve73vreq/ryqfvCsMTNdP+dorc3lkrUPCPhkki9NclGSDyd56Vljvi/Jzw/X35DknQuac3+SLxquf++i5hzGPSfJbyf5QJKVRcuY5KokH0py6bB8+SKuy6wdG/q9w/WXJjk5h5xfl+QVST56nvtfm+TXk1SSr0ly96wzLtg2m+t8Yi6Zy/o0n4yf03wynfV6S5IfvtC/kQnm+aYku4brb03y1vOMO5nksim8/kLN1UmuSPKK4fpzkvzvDfLsS3LHDP9mNl3383ovDtvuj7P2/S5zWz9nX+a5R+aVST7RWnuwtfbXSQ4nuf6sMdcnOTRcf3eSa6qqZpgxGSNna+1oa+2zw+IHkrxwxhmT8dZnkvx4kn+b5P/OMtxgnIzfneTnWmuPJUlr7TMzzpiMl7Ml+eLh+sVJPj3DfGsBWvvtJH+2yZDrk/xyW/OBJJdU1RWzSTdzPcwn5pLJMp9MkPlkrsZ9z12w1tr7W2unh8V5zDELNVe31h5prX1wuP4XSe5LcuU0XmuC5vVevCbJJ1trfziD1xrbPIvMlUn+aN3yQzn3j+fzY4Y33hNJnjeTdBtkGGyUc72bstaUZ21kzqp6eZIXtdbumGWwdcZZl1+e5Mur6neq6gNVde3M0j1lnJy3JPn2qnooa5/c96bZRNuSrf7t9qyH+cRcMlnmk9l6Os0nk/YDwyFAb6+qSze4f17r9h/n/HNMS/L+qrqnqg5M8DUXdq4eDmF7eZK7N7j7VVX14ar69ar6yilHGbXu5/X38oYk7zjPfbNcP2e4oI9fvkAbteuzP0JtnDHTNnaGqvr2JCtJvn6qiTa2ac6q+oIkP5XkO2cVaAPjrMtdWTscZF/W/qfof1TVV7XWHp9ytvXGyfnGJL/UWru1ql6V5FeGnH87/XhjW4T3z6z0MJ+YSybLfDJb837/LKyq+q0kL9jgrh9N8h+ytveyDT9vzVqBOOMpNnjsttftZnlaa7cNY340yekkv3qep/na1tqnq+ryJHdW1R8Me+0u1ELO1VW1O8l/SfKDrbU/P+vuD2btcKpTwzlO/zVr88q0jFr381g/FyX51iQ/ssHds14/Z5hnkXkoyYvWLb8w5+5Of3LMQ1W1K2u73Dfb9T0N4+RMVX1j1iatr2+t/dWMsq03KudzknxVkmPDHtoXJLm9qr61tTbRE/kuIOOTYz7QWvubJP+nqu7P2hvi92cT8fMZRuW8Kcm1SdJa+92qemaSy5LM49CV8xnrb3eH6GE+MZdMlvlktp5O88mWtNa+cZxxVfUfk2y0F3Oi63ZUnqq6Mcm3JLmmDSc5bPAcnx5+fqaq3pu1Q8ImUWQWbq6uqi/MWon51dbae86+f32xaa29r6r+fVVd1lr7k2nkGWPdz+O9+M1JPthae/TsO2a9fs4x7ZNwznfJWol6MMlL8tQJX1951pjvz5knfL1rQXO+PGsnr121yOvzrPHHMvuT/cdZl9cmOTRcvyxru0+ft4A5fz3Jdw7XvyJrk0jNYbsv5/wn516XM08I/L1Z51uwbTbX+cRcMpf1aT7ZWlbzyeTX6RXrrv/TJIe38zcywTzXJvl4kudvMubZSZ6z7vr/SnLthF5/oebq4e/5l5P89CZjXvDk+zFrpeJT03p/jrPu5/FezNq5TN817/Wz4evP6oXO88u/NmufEPHJrO3yTJJ/leRbh+vPTPKfk3wiye8l+dIFzflbSR5Ncu9wuX0Rc5419ljm84+PUeuykvzkMNGeSPKGRVyXWftkod8ZJuF7k3zTHDK+I8kjSf4ma/9Dc1OS70nyPevW5c8Nv8OJeWzvBdtmc59PzCUzX5/mk/Ezmk+ms15/ZVhfH0lye4Zik+RLkrxvs7+RKeX5RNYK/ZNzzM+fnSdrnyj24eHysUnnWaS5Osk/zNphWR9Zt05ee9bf/g8M6+HDWfuAhH8wxTwbrvt5vheTfFGSP01y8brb5rJ+Nro82aAAAAC6MdcvxAQAANgORQYAAOiOIgMAAHRHkQEAALqjyAAAAN1RZAAAgO4oMgAAQHcUGQAAoDv/H/ZXlWRAtumXAAAAAElFTkSuQmCC\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": 85,
   "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": 86,
   "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": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAzIAAAFpCAYAAABH4x0wAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAF6BJREFUeJzt3X2MpWd5HvDrjheHjwUMGDbIuIwRDsWK24JXFihttIujQnEU8we0oIZYkVsrSUtpQ1UmjVTol2LaAqUSShrhNG6UZiEkrS2WFlFnt1Gj2MkaCAu4xK5rgV0XgoqdLKUlbp/+Me+S2fXuzpndM+fMffz7SUd73jPvnLme8/Gcufb9mBpjBAAAoJPvWHYAAACA7VJkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHb2LPKHXXrppWNtbW3L9b7xjW/kGc94xs4HukByzleHnB0yJtvLec8993xtjPH8HY40d+aT5eiQs0PGZPVyrvpcstt1eT1t1yqOaxXHlMx3XDPPJ2OMhV2uueaaMYsjR47MtN6yyTlfHXJ2yDjG9nImOTYWOA/M62I+WY4OOTtkHGP1cq76XLLbdXk9bdcqjmsVxzTGfMc163xi1zIAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAoJm19cNZWz+87BiwVIoMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDt7lh0AAIDzs7Z+OEnyC697xpKTwOLZIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0M1ORqaq/VVWfr6rPVdUvV9VTq+qKqrq7qu6rqg9X1cU7HRYAACCZochU1WVJ/kaS/WOM70lyUZI3J3lPkvePMa5M8vUkN+1kUAAAgJNm3bVsT5KnVdWeJE9P8kiS1yT56PT125K8Yf7xAAAAnmjLIjPGeDjJP0vypWwUmMeS3JPk0THG49NqDyW5bKdCAgAAbFZjjHOvUPWcJL+a5C8leTTJr0zL7xpjvHRa5/IkHx9jXH2G7785yc1Jsm/fvmsOHTq0ZagTJ05k79692xvJEsg5Xx1ydsiYbC/nwYMH7xlj7N/hSHNhPlm+Djk7ZExWL+eqzyW7zfGHHztl+YpnX9Ti9bRdXd4n27GKY0rmO66Z55MxxjkvSd6U5NZNyz+c5GeSfC3Jnum2Vyf5xFb3dc0114xZHDlyZKb1lk3O+eqQs0PGMbaXM8mxscV7dzdezCfL0SFnh4xjrF7OVZ9LdpsXv/Njp1y6vJ62axXHtYpjGmO+45p1PpnlGJkvJXlVVT29qirJdUm+kORIkjdO69yY5PaZaxYAAMAFmOUYmbuzcVD/p5Icn77n55K8M8lPVNX9SZ6X5NYdzAkAAPBte2ZZaYzxriTvOu3mB5JcO/dEAAAAW5j19MsAAAC7hiIDAAC0o8gAADR3/OHHsrZ+eNkxYKEUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANrZs+wAAADMZm398LIjwK5hiwwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtLNn2QEAADi3tfXD217vwVuu36k4sCvYIgMAALSjyAAAAO3MVGSq6pKq+mhV/ZequreqXl1Vz62qT1bVfdO/z9npsAAAAMnsW2Q+kOQ/jDH+ZJI/neTeJOtJ7hxjXJnkzmkZAABgx21ZZKrqWUm+L8mtSTLG+NYY49EkNyS5bVrttiRv2KmQAAAAm82yReYlSX4/yb+qqk9X1Yeq6hlJ9o0xHkmS6d8X7GBOAACAb6sxxrlXqNqf5K4k3zvGuLuqPpDkD5K8bYxxyab1vj7GeMJxMlV1c5Kbk2Tfvn3XHDp0aMtQJ06cyN69e7c1kGWQc7465OyQMdlezoMHD94zxti/w5HmwnyyfB1ydsiYrF7OVZ9Llu34w4+d8+v7npZ85Zun3nb1Zc/ewUSL0eV9sh2rOKZkvuOadT6Zpch8V5K7xhhr0/Kfy8bxMC9NcmCM8UhVvTDJ0THGy851X/v37x/Hjh3bMvzRo0dz4MCBLddbNjnnq0PODhmT7eWsqja/fGxmPlmODjk7ZExWL+eqzyXLttXfkXnH1Y/nvcdP/fOAq/B3ZLq8T7ZjFceUzHdcs84nW+5aNsb4H0m+XFUnS8p1Sb6Q5I4kN0633Zjk9vPMCgAAsC17tl4lSfK2JL9UVRcneSDJj2SjBH2kqm5K8qUkb9qZiAAAAKeaqciMMT6T5Eybd66bbxwAAICtzfp3ZAAAAHYNRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoZ8+yAwAAcGZr64eXHQF2LVtkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdmYuMlV1UVV9uqo+Ni1fUVV3V9V9VfXhqrp452ICAAD8se1skXl7kns3Lb8nyfvHGFcm+XqSm+YZDAAA4GxmKjJV9aIk1yf50LRcSV6T5KPTKrclecNOBAQAADjdrFtk/nmSv5Pk/03Lz0vy6Bjj8Wn5oSSXzTkbAADAGdUY49wrVP1AktePMX68qg4k+dtJfiTJb40xXjqtc3mSj48xrj7D99+c5OYk2bdv3zWHDh3aMtSJEyeyd+/ebQ5l8eScrw45O2RMtpfz4MGD94wx9u9wpLkwnyxfh5wdMiarl3PV55JlOf7wYzOtt+9pyVe+eeptV1/27FPu4+RyJ13eJ9uximNK5juuWeeTWYrMTyd5a5LHkzw1ybOS/Nskr03yXWOMx6vq1UnePcZ47bnua//+/ePYsWNbhj969GgOHDiw5XrLJud8dcjZIWOyvZxV1eaXj83MJ8vRIWeHjMnq5Vz1uWRZ1tYPz7TeO65+PO89vueU2x685fpT7uPkcidd3ifbsYpjSuY7rlnnky13LRtj/OQY40VjjLUkb07y62OMv5zkSJI3TqvdmOT2C8gLAAAwsz1br3JW70xyqKr+UZJPJ7l1PpEAALhQs27Nga62VWTGGEeTHJ2uP5Dk2vlHAgAAOLft/B0ZAACAXUGRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAngTW1g9nbf3wsmPA3CgyAABAO4oMAADQjiIDAAC0s2fZAQAAOJVjWWBrtsgAAADtKDIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQjiIDAAC0o8gAAADt7Fl2AAAANqytH152BGjDFhkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2tmyyFTV5VV1pKrurarPV9Xbp9ufW1WfrKr7pn+fs/NxAQC4EGvrh7O2fnjZMeCCzbJF5vEk7xhjvDzJq5L8taq6Ksl6kjvHGFcmuXNaBgAA2HFbFpkxxiNjjE9N1/8wyb1JLktyQ5LbptVuS/KGnQoJAACw2baOkamqtSSvSHJ3kn1jjEeSjbKT5AXzDgcAAHAmNcaYbcWqvUn+U5J/PMb4tap6dIxxyaavf32M8YTjZKrq5iQ3J8m+ffuuOXTo0JY/68SJE9m7d++MQ1geOeerQ84OGZPt5Tx48OA9Y4z9OxxpLswny9chZ4eMyerlXPW5ZFGOP/zYeX3fvqclX/nm9r7n6suefV4/a5G6vE+2YxXHlMx3XLPOJzMVmap6SpKPJfnEGON9021fTHJgjPFIVb0wydExxsvOdT/79+8fx44d2/LnHT16NAcOHNhyvWWTc7465OyQMdlezqpq88vHZuaT5eiQs0PGZPVyrvpcsijnexD+O65+PO89vmdb3/PgLdef189apC7vk+1YxTEl8x3XrPPJLGctqyS3Jrn3ZImZ3JHkxun6jUluP5+gAAAA2zVLdf/eJG9NcryqPjPd9neT3JLkI1V1U5IvJXnTzkQEAAA41ZZFZozxn5PUWb583XzjAAAAbG1bZy0DAADYDRQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAgCextfXD5/2HOGGZFBkAAKAdRQYAAGhHkQEAANpRZAAAgHYUGQAAoB1FBgAAaEeRAQAA2lFkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGhHkQEAANpRZAAAgHb2LDsAAACLt7Z+eNkR4ILYIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7Tj9MgDAkjkVMmyfLTIAAEA7igwAANCOIgMAALSjyAAAAO0oMgAAQDuKDAAA0I4iAwAAtKPIAAAA7SgyAABAO4oMAADQzp5lBwAAeLJaWz+87AjfdjLLg7dcv+QkMBtbZAAAgHYUGQAAoB1FBgCAJ1hbP7yrdn2D0ykyAABAO4oMAADQjiIDAAC0o8gAAADtKDIAAEA7igwAANDOnmUHAAB4Mth8KuMHb7l+iUlgNdgiAwAAtKPIAAAA7di1DABgwTbvZtbZyXHYVY5lsEUGAABoR5EBAADaUWQAAIB2HCMDAMC3ne34HaePZrexRQYAAGhHkQEAANq5oF3Lqup1ST6Q5KIkHxpj3DKXVAAAu8xWpxo+fZesk+t1P9XymfKfftusj83pX+9y+uYuOU/aTXl3cpfE894iU1UXJflgkr+Q5Kokb6mqq+YVDAAA4GwuZNeya5PcP8Z4YIzxrSSHktwwn1gAAABndyFF5rIkX960/NB0GwAAwI6qMcb5fWPVm5K8dozxV6bltya5dozxttPWuznJzdPiy5J8cYa7vzTJ184r2GLJOV8dcnbImGwv54vHGM/fyTDzYj7ZFTrk7JAxWb2cqz6X7HZdXk/btYrjWsUxJfMd10zzyYUUmVcnefcY47XT8k8myRjjp8/rDk+972NjjP0Xej87Tc756pCzQ8akT85F6PJYyDk/HTImcjJfq/o8reK4VnFMyXLGdSG7lv1Okiur6oqqujjJm5PcMZ9YAAAAZ3fep18eYzxeVX89ySeycfrlnx9jfH5uyQAAAM7igv6OzBjj40k+Pqcsm/3cDtznTpBzvjrk7JAx6ZNzEbo8FnLOT4eMiZzM16o+T6s4rlUcU7KEcZ33MTIAAADLciHHyAAAACzFUotMVb2uqr5YVfdX1foZvv6dVfXh6et3V9Xa4lPOlPMnquoLVfXZqrqzql68G3NuWu+NVTWqauFnzJglY1X9xenx/HxV/ZtFZ5wybPWc/4mqOlJVn56e99cvIePPV9VXq+pzZ/l6VdW/mMbw2ap65aIzLlKH+cRcMl/mk7lmNJ80Nut7djc702uwqp5bVZ+sqvumf5+zzIzno6oun97f907z0Nun29uOraqeWlW/XVW/O43p70+3XzF9vt43fd5evONhxhhLuWTjBAH/NclLklyc5HeTXHXaOj+e5Gen629O8uFdmvNgkqdP139st+ac1ntmkt9IcleS/bstY5Irk3w6yXOm5RfsxscyG/uB/th0/aokDy4h5/cleWWSz53l669P8u+TVJJXJbl70Rl32XO21PnEXLKUx9N8MntO80nTy6zv2d1+OdNrMMk/SbI+XV9P8p5l5zyPcb0wySun689M8nvT+7zt2KZ5YO90/SlJ7p7mhY8kefN0+8+enNd28rLMLTLXJrl/jPHAGONbSQ4lueG0dW5Ictt0/aNJrquqWmDGZIacY4wjY4z/NS3eleRFC86YzPZ4Jsk/zMab538vMtxklox/NckHxxhfT5IxxlcXnDGZLedI8qzp+rOT/PcF5tsIMMZvJPmf51jlhiT/emy4K8klVfXCxaRbuA7ziblkvswnc2Q+aW3W9+yudpbX4OZ5+7Ykb1hoqDkYYzwyxvjUdP0Pk9yb5LI0Hts0D5yYFp8yXUaS12Tj8zVZ0JiWWWQuS/LlTcsPTbedcZ0xxuNJHkvyvIWkO0OGyZlybnZTNv7XatG2zFlVr0hy+RjjY4sMtsksj+V3J/nuqvrNqrqrql63sHR/bJac707yQ1X1UDbO3Pe2xUTblu2+djvrMJ+YS+bLfLJYT6b5pJtVfm72jTEeSTYKQZIXLDnPBZl2aX5FNrZgtB5bVV1UVZ9J8tUkn8zGVsFHp8/XZEGvw2UWmTP9T+jpp1CbZZ2dNnOGqvqhJPuT/NMdTXRm58xZVd+R5P1J3rGwRE80y2O5Jxu7gxxI8pYkH6qqS3Y41+lmyfmWJL8wxnhRNna5+MXpMd5NdsP7Z1E6zCfmkvkynyzWst8/nJ3npoGq2pvkV5P8zTHGHyw7z4UaY/zfMcafycaeA9cmefmZVtvpHMucKB9Kcvmm5RfliZvTv71OVe3Jxib3c2363gmz5ExVfX+Sn0ryg2OM/7OgbJttlfOZSb4nydGqejAb+zLeseCDdGd9zm8fY/zRGOO/JfliNn4RWaRZct6UjX1BM8b4rSRPTXLpQtLNbqbX7oroMJ+YS+bLfLJYT6b5pJtVfm6+cnIXxunfZeweesGq6inZKDG/NMb4tenmlRjbGOPRJEez8VlwyfT5mizodbjMIvM7Sa6cznBwcTYOvr3jtHXuSHLjdP2NSX59TEcQLdCWOafdLP5lNn7xWNYL8Zw5xxiPjTEuHWOsjTHWsrH//Q+OMY7tloyTf5eNA55TVZdmY9eQBxaYMZkt55eSXJckVfXybPzi8fsLTbm1O5L88HS2oVcleezkZuwV1GE+MZcsMOfEfDI/T6b5pJtZXmNdbZ63b0xy+xKznJfpWMxbk9w7xnjfpi+1HVtVPf/k1u2qelqS78/GsT9HsvH5mixqTDt9NoFzXbKxCf33srFf3U9Nt/2DbHwoJhuT+a8kuT/Jbyd5yS7N+R+TfCXJZ6bLHbsx52nrHs1yzjS01WNZSd6X5AtJjmc6+8UuzHlVkt/MxtlhPpPkzy8h4y8neSTJH2Xjf+RuSvKjSX5002P5wWkMx5fxfO+y52zp84m5ZOGPp/lk9ozmk8aXM73Gul3O8hp8XpI7k9w3/fvcZec8j3H92WzsYvXZTXP76zuPLcmfysYZIT+b5HNJ/t50+0umz9f7p8/b79zpLDX9YAAAgDZ228GEAAAAW1JkAACAdhQZAACgHUUGAABoR5EBAADaUWQAAIB2FBkAAKAdRQYAAGjn/wPQkT/ll0eFOAAAAABJRU5ErkJggg==\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": 88,
   "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
}