Loneliness Process Data - England.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "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 England\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 practice prescribing data - Presentation level\" zip files that come from: https://data.gov.uk/dataset/176ae264-2484-4afe-a297-d51798eb8228/gp-practice-prescribing-data-presentation-level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Path to zip files\n",
    "path = r\"[CHANGE THIS PATH]\\England\\\\\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read in drug data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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 relatived diseases, all you need to do is provide a different set of drug_data and edit the code_loneliness function."
   ]
  },
  {
   "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['BNF NAME'].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['BNF NAME'].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['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['ITEMS'] = 'sum'\n",
    "agg_cols['loneliness'] = 'sum'\n",
    "for key in ['Date','SHA','PCT','pcstrip','CenterName','Street','Town','Town2','Postcode']:\n",
    "    agg_cols[key] = 'first'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018_01_Jan.zip\n",
      "2018_02_Feb.zip\n",
      "2018_03_Mar.zip\n",
      "2018_04_Apr.zip\n",
      "2018_05_May.zip\n",
      "2018_06_Jun.zip\n",
      "2018_07_Jul.zip\n",
      "2018_08_Aug.zip\n",
      "2018_09_Sep.zip\n",
      "2018_10_Oct.zip\n",
      "2018_11_Nov.zip\n",
      "2018_12_Dec.zip\n"
     ]
    }
   ],
   "source": [
    "monthly_data = []\n",
    "\n",
    "for file in os.listdir(path + \"zip\"):\n",
    "    with zp.ZipFile(path + \"zip\\\\\" + file) as zipf:\n",
    "        zip_names = zipf.namelist()\n",
    "\n",
    "        # Deal with Address Files\n",
    "        addr_name = next((filename for filename in zip_names if \"ADDR\" in filename), None)\n",
    "        # Open address file in pandas, set header.\n",
    "        addr = pd.read_csv(zipf.open(addr_name), \n",
    "                           header=0, \n",
    "                           names = [\"Date\", \"PracCode\", \"PracName\",\"CenterName\",\n",
    "                                    \"Street\", \"Town\", \"Town2\", \"Postcode\"], \n",
    "                           usecols = range(8))\n",
    "\n",
    "        # Deal with prescription info\n",
    "        prescribe_name = next((filename for filename in zip_names if \"PDPI\" in filename), None)\n",
    "        # Open prescribing files in pandas.\n",
    "        prescribe = pd.read_csv(zipf.open(prescribe_name))\n",
    "        prescribe.columns = prescribe.columns.str.strip()\n",
    "        # Get counts of prescribing dataframe for loneliness related diseases\n",
    "        loneliness_prescribing = code_loneliness(prescribe[['BNF NAME','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 = 'PRACTICE', right_on = 'PracCode')\n",
    "        del addr\n",
    "        \n",
    "        # Create uniform postcode field\n",
    "        prescribe['pcstrip'] = prescribe['Postcode'].str.replace(\"\\s\",\"\")\n",
    "        \n",
    "        # get a summary - grouping by PracCode\n",
    "        summary = prescribe.groupby('PracCode', as_index=False).agg(agg_cols)\n",
    "        del prescribe\n",
    "        \n",
    "        monthly_data.append(summary)\n",
    "        print(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate all the monthly data together.\n",
    "data = pd.concat(monthly_data, ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save aggregated data\n",
    "data.to_csv(path + \"processed_data.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.csv\", index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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": 11,
   "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": 12,
   "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": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "WR59QT     12\n",
       "DN18QN     12\n",
       "HU32AE     12\n",
       "BS378NG    12\n",
       "DL154SB    12\n",
       "GL10QD     11\n",
       "ME122TZ     6\n",
       "EN24EJ      1\n",
       "Name: pcstrip, dtype: int64"
      ]
     },
     "execution_count": 13,
     "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": {},
   "outputs": [],
   "source": [
    "# Clean Missing Postcodes - appear to be typos.\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": 15,
   "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": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save aggregated data\n",
    "data.to_csv(path + \"processed_data_with_postcodes.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 794 practices codes which have more than 1 postcode assigned to them, this is about 7% of unique practices.\n",
    "\n",
    "764 for those practices have 2 postcodes associated with them, while 30 have 3 postcodes.\n",
    "\n",
    "102 of these practices fall within the same LSOA, 669 fall within 2 different LSOAs, and 30 within 3 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": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "269"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for 1 postcode per Practice Code\n",
    "pc_prac_counts = data.groupby('PracCode')['pcstrip'].unique().map(len)\n",
    "# 794 practices have more than 1 postcode associated with it.\n",
    "pc_prac_counts[pc_prac_counts > 1].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0257194760493355"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Practice codes with multiple associated postcodes account forc. 7% of the data\n",
    "pc_prac_counts[pc_prac_counts > 1].count()/len(pc_prac_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    267\n",
       "3      2\n",
       "Name: pcstrip, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pc_prac_counts[pc_prac_counts > 1].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    239\n",
       "1     29\n",
       "3      1\n",
       "Name: lsoa11, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count of these Practices falling within the same LSOA\n",
    "(data[data['PracCode'].isin(pc_prac_counts[pc_prac_counts > 1].index)]\n",
    " .groupby('PracCode')['lsoa11']\n",
    " .unique()\n",
    " .map(len)\n",
    " .value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Subsetting the Data\n",
    "\n",
    "## Use only General Practice surgeries\n",
    "\n",
    "Use the 'Patients Registered at a GP Practice' data from: https://digital.nhs.uk/data-and-information/publications/statistical/patients-registered-at-a-gp-practice to get GP surgery codes and subset the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(path + \"processed_data_with_postcodes.csv\", index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get GP files\n",
    "gp_path = r\"[CHANGE THIS PATH]\\England\\GP data\\\\\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gp-reg-pat-prac-all-apr-18.csv\n",
      "gp-reg-pat-prac-all-aug-18.csv\n",
      "gp-reg-pat-prac-all-dec-18.csv\n",
      "gp-reg-pat-prac-all-feb-18.csv\n",
      "gp-reg-pat-prac-all-jan-18.csv\n",
      "gp-reg-pat-prac-all-jul-18.csv\n",
      "gp-reg-pat-prac-all-jun-18.csv\n",
      "gp-reg-pat-prac-all-mar-18.csv\n",
      "gp-reg-pat-prac-all-may-18.csv\n",
      "gp-reg-pat-prac-all-nov-18.csv\n",
      "gp-reg-pat-prac-all-oct-18.csv\n",
      "gp-reg-pat-prac-all-sep-18.csv\n"
     ]
    }
   ],
   "source": [
    "gp_combine = []\n",
    "\n",
    "for file in os.listdir(gp_path):\n",
    "    # read file into pandas\n",
    "    month, year = file[-10:-4].split(\"-\")\n",
    "    \n",
    "    # Deal with different file structures\n",
    "    if (year == '16') or (year == '17' and month == 'jan'):\n",
    "        gp_data = pd.read_csv(gp_path + file)\n",
    "        gp_data['DATE'] = \"01\" + month.upper() + \"20\" + year\n",
    "        gp_data.columns = gp_data.columns.str.upper()\n",
    "        gp_data = gp_data.rename(columns = {'DATE':'Date','GP_PRACTICE_CODE':'PracCode','TOTAL_ALL':'NUMBER_OF_PATIENTS'})\n",
    "        gp_combine.append(gp_data[['Date','PracCode','POSTCODE','NUMBER_OF_PATIENTS']])\n",
    "    else:\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 = {'EXTRACT_DATE':'Date', 'CODE':'PracCode'})\n",
    "        if year == '17' and month == 'jun':\n",
    "            gp_data['Date'] = \"01\" + month.upper() + \"20\" + year\n",
    "        gp_combine.append(gp_data[['Date','PracCode','POSTCODE','NUMBER_OF_PATIENTS']])\n",
    "    print(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate all the gp data together.\n",
    "gp_data = pd.concat(gp_combine, ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(86079, 29)"
      ]
     },
     "execution_count": 25,
     "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['PracCode'].unique()\n",
    "data = data[data['PracCode'].isin(gp_ids)].copy()\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make date a datetime variable\n",
    "gp_data['Date'] = pd.to_datetime(gp_data['Date'], format = '%d%b%Y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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": 28,
   "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>PracCode</th>\n",
       "      <th>POSTCODE</th>\n",
       "      <th>NUMBER_OF_PATIENTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83006</td>\n",
       "      <td>DL3 6HZ</td>\n",
       "      <td>8044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83010</td>\n",
       "      <td>DL3 9JP</td>\n",
       "      <td>14070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83013</td>\n",
       "      <td>DL1 4YL</td>\n",
       "      <td>11298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83031</td>\n",
       "      <td>DL3 8SQ</td>\n",
       "      <td>10109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83034</td>\n",
       "      <td>DL3 7JR</td>\n",
       "      <td>10128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83040</td>\n",
       "      <td>DL1 5JN</td>\n",
       "      <td>12093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83047</td>\n",
       "      <td>DL3 0PD</td>\n",
       "      <td>14704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83048</td>\n",
       "      <td>DL2 2DS</td>\n",
       "      <td>5621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>A83070</td>\n",
       "      <td>DL2 1BY</td>\n",
       "      <td>5512</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date PracCode POSTCODE  NUMBER_OF_PATIENTS\n",
       "1 2018-04-01   A83006  DL3 6HZ                8044\n",
       "2 2018-04-01   A83010  DL3 9JP               14070\n",
       "3 2018-04-01   A83013  DL1 4YL               11298\n",
       "4 2018-04-01   A83031  DL3 8SQ               10109\n",
       "5 2018-04-01   A83034  DL3 7JR               10128\n",
       "6 2018-04-01   A83040  DL1 5JN               12093\n",
       "7 2018-04-01   A83047  DL3 0PD               14704\n",
       "8 2018-04-01   A83048  DL2 2DS                5621\n",
       "9 2018-04-01   A83070  DL2 1BY                5512"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gp_data[1:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>PracCode</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>...</th>\n",
       "      <th>Town2</th>\n",
       "      <th>Postcode</th>\n",
       "      <th>oseast1m</th>\n",
       "      <th>osnrth1m</th>\n",
       "      <th>lsoa11</th>\n",
       "      <th>msoa11</th>\n",
       "      <th>ru11ind</th>\n",
       "      <th>rgn</th>\n",
       "      <th>laua</th>\n",
       "      <th>imd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81004</td>\n",
       "      <td>1158</td>\n",
       "      <td>29</td>\n",
       "      <td>1368</td>\n",
       "      <td>1387</td>\n",
       "      <td>327</td>\n",
       "      <td>1145</td>\n",
       "      <td>153</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>TS5 8SB</td>\n",
       "      <td>447840.0</td>\n",
       "      <td>516130.0</td>\n",
       "      <td>E01012024</td>\n",
       "      <td>E02002510</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>-111.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81005</td>\n",
       "      <td>923</td>\n",
       "      <td>43</td>\n",
       "      <td>1568</td>\n",
       "      <td>1575</td>\n",
       "      <td>233</td>\n",
       "      <td>1276</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>TS14 7DJ</td>\n",
       "      <td>461221.0</td>\n",
       "      <td>515668.0</td>\n",
       "      <td>E01012117</td>\n",
       "      <td>E02002532</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000003</td>\n",
       "      <td>-86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81006</td>\n",
       "      <td>2257</td>\n",
       "      <td>63</td>\n",
       "      <td>2625</td>\n",
       "      <td>2653</td>\n",
       "      <td>594</td>\n",
       "      <td>2205</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>...</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 2AT</td>\n",
       "      <td>444478.0</td>\n",
       "      <td>519481.0</td>\n",
       "      <td>E01012267</td>\n",
       "      <td>E02002548</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000004</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81007</td>\n",
       "      <td>1482</td>\n",
       "      <td>39</td>\n",
       "      <td>1335</td>\n",
       "      <td>1371</td>\n",
       "      <td>273</td>\n",
       "      <td>1012</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS24 7PW</td>\n",
       "      <td>450765.0</td>\n",
       "      <td>532203.0</td>\n",
       "      <td>E01011999</td>\n",
       "      <td>E02002489</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>-57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A81009</td>\n",
       "      <td>1315</td>\n",
       "      <td>20</td>\n",
       "      <td>1541</td>\n",
       "      <td>1554</td>\n",
       "      <td>372</td>\n",
       "      <td>1151</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS5 6HF</td>\n",
       "      <td>449121.0</td>\n",
       "      <td>518824.0</td>\n",
       "      <td>E01012078</td>\n",
       "      <td>E02002500</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A81011</td>\n",
       "      <td>1775</td>\n",
       "      <td>33</td>\n",
       "      <td>1808</td>\n",
       "      <td>1813</td>\n",
       "      <td>281</td>\n",
       "      <td>1358</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS24 7PW</td>\n",
       "      <td>450765.0</td>\n",
       "      <td>532203.0</td>\n",
       "      <td>E01011999</td>\n",
       "      <td>E02002489</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>-57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A81012</td>\n",
       "      <td>1282</td>\n",
       "      <td>16</td>\n",
       "      <td>1106</td>\n",
       "      <td>1112</td>\n",
       "      <td>256</td>\n",
       "      <td>877</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>TS3 6AL</td>\n",
       "      <td>450757.0</td>\n",
       "      <td>519893.0</td>\n",
       "      <td>E01012059</td>\n",
       "      <td>E02002497</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A81013</td>\n",
       "      <td>1189</td>\n",
       "      <td>16</td>\n",
       "      <td>1211</td>\n",
       "      <td>1223</td>\n",
       "      <td>242</td>\n",
       "      <td>812</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>TS12 2FF</td>\n",
       "      <td>468872.0</td>\n",
       "      <td>519413.0</td>\n",
       "      <td>E01012098</td>\n",
       "      <td>E02002524</td>\n",
       "      <td>D1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000003</td>\n",
       "      <td>125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A81014</td>\n",
       "      <td>409</td>\n",
       "      <td>32</td>\n",
       "      <td>848</td>\n",
       "      <td>862</td>\n",
       "      <td>147</td>\n",
       "      <td>838</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS23 2LA</td>\n",
       "      <td>445648.0</td>\n",
       "      <td>523909.0</td>\n",
       "      <td>E01012276</td>\n",
       "      <td>E02002536</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000004</td>\n",
       "      <td>123.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  PracCode  depression  alzheimers  blood pressure  hypertension  diabeties  \\\n",
       "1   A81004        1158          29            1368          1387        327   \n",
       "2   A81005         923          43            1568          1575        233   \n",
       "3   A81006        2257          63            2625          2653        594   \n",
       "4   A81007        1482          39            1335          1371        273   \n",
       "5   A81009        1315          20            1541          1554        372   \n",
       "6   A81011        1775          33            1808          1813        281   \n",
       "7   A81012        1282          16            1106          1112        256   \n",
       "8   A81013        1189          16            1211          1223        242   \n",
       "9   A81014         409          32             848           862        147   \n",
       "\n",
       "   cardiovascular disease  insomnia  addiction  social anxiety  ...  \\\n",
       "1                    1145       153          0              11  ...   \n",
       "2                    1276        36          0              24  ...   \n",
       "3                    2205        23          0              18  ...   \n",
       "4                    1012        14          0               6  ...   \n",
       "5                    1151        76          0               5  ...   \n",
       "6                    1358        44          0               6  ...   \n",
       "7                     877        27          0               8  ...   \n",
       "8                     812        29          0               8  ...   \n",
       "9                     838        15          1               7  ...   \n",
       "\n",
       "                       Town2  Postcode  oseast1m  osnrth1m     lsoa11  \\\n",
       "1                             TS5 8SB   447840.0  516130.0  E01012024   \n",
       "2                             TS14 7DJ  461221.0  515668.0  E01012117   \n",
       "3  CLEVELAND                  TS18 2AT  444478.0  519481.0  E01012267   \n",
       "4  CLEVELAND                  TS24 7PW  450765.0  532203.0  E01011999   \n",
       "5  CLEVELAND                  TS5 6HF   449121.0  518824.0  E01012078   \n",
       "6  CLEVELAND                  TS24 7PW  450765.0  532203.0  E01011999   \n",
       "7                             TS3 6AL   450757.0  519893.0  E01012059   \n",
       "8                             TS12 2FF  468872.0  519413.0  E01012098   \n",
       "9  CLEVELAND                  TS23 2LA  445648.0  523909.0  E01012276   \n",
       "\n",
       "      msoa11 ru11ind        rgn       laua    imd  \n",
       "1  E02002510      C1  E12000001  E06000002 -111.0  \n",
       "2  E02002532      C1  E12000001  E06000003  -86.0  \n",
       "3  E02002548      C1  E12000001  E06000004   39.0  \n",
       "4  E02002489      C1  E12000001  E06000001  -57.0  \n",
       "5  E02002500      C1  E12000001  E06000002  107.0  \n",
       "6  E02002489      C1  E12000001  E06000001  -57.0  \n",
       "7  E02002497      C1  E12000001  E06000002   59.0  \n",
       "8  E02002524      D1  E12000001  E06000003  125.0  \n",
       "9  E02002536      C1  E12000001  E06000004  123.0  \n",
       "\n",
       "[9 rows x 29 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[1:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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','PracCode'])\n",
    "# data = data.merge(gp_data, how = 'left', on = ['PracCode'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save aggregated data\n",
    "data.to_csv(path + \"processed_data_with_postcodes_GPs.csv\")"
   ]
  },
  {
   "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": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(path + \"processed_data_with_postcodes_GPs.csv\", index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>PracCode</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>...</th>\n",
       "      <th>oseast1m</th>\n",
       "      <th>osnrth1m</th>\n",
       "      <th>lsoa11</th>\n",
       "      <th>msoa11</th>\n",
       "      <th>ru11ind</th>\n",
       "      <th>rgn</th>\n",
       "      <th>laua</th>\n",
       "      <th>imd</th>\n",
       "      <th>POSTCODE</th>\n",
       "      <th>NUMBER_OF_PATIENTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81004</td>\n",
       "      <td>1158</td>\n",
       "      <td>29</td>\n",
       "      <td>1368</td>\n",
       "      <td>1387</td>\n",
       "      <td>327</td>\n",
       "      <td>1145</td>\n",
       "      <td>153</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>447840.0</td>\n",
       "      <td>516130.0</td>\n",
       "      <td>E01012024</td>\n",
       "      <td>E02002510</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>-111.0</td>\n",
       "      <td>TS5 8SB</td>\n",
       "      <td>9928.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81005</td>\n",
       "      <td>923</td>\n",
       "      <td>43</td>\n",
       "      <td>1568</td>\n",
       "      <td>1575</td>\n",
       "      <td>233</td>\n",
       "      <td>1276</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>...</td>\n",
       "      <td>461221.0</td>\n",
       "      <td>515668.0</td>\n",
       "      <td>E01012117</td>\n",
       "      <td>E02002532</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000003</td>\n",
       "      <td>-86.0</td>\n",
       "      <td>TS14 7DJ</td>\n",
       "      <td>7998.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81006</td>\n",
       "      <td>2257</td>\n",
       "      <td>63</td>\n",
       "      <td>2625</td>\n",
       "      <td>2653</td>\n",
       "      <td>594</td>\n",
       "      <td>2205</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>...</td>\n",
       "      <td>444478.0</td>\n",
       "      <td>519481.0</td>\n",
       "      <td>E01012267</td>\n",
       "      <td>E02002548</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000004</td>\n",
       "      <td>39.0</td>\n",
       "      <td>TS18 2AT</td>\n",
       "      <td>14455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81007</td>\n",
       "      <td>1482</td>\n",
       "      <td>39</td>\n",
       "      <td>1335</td>\n",
       "      <td>1371</td>\n",
       "      <td>273</td>\n",
       "      <td>1012</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>450765.0</td>\n",
       "      <td>532203.0</td>\n",
       "      <td>E01011999</td>\n",
       "      <td>E02002489</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>-57.0</td>\n",
       "      <td>TS24 7PW</td>\n",
       "      <td>10022.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A81009</td>\n",
       "      <td>1315</td>\n",
       "      <td>20</td>\n",
       "      <td>1541</td>\n",
       "      <td>1554</td>\n",
       "      <td>372</td>\n",
       "      <td>1151</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>449121.0</td>\n",
       "      <td>518824.0</td>\n",
       "      <td>E01012078</td>\n",
       "      <td>E02002500</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>107.0</td>\n",
       "      <td>TS5 6HF</td>\n",
       "      <td>8735.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A81011</td>\n",
       "      <td>1775</td>\n",
       "      <td>33</td>\n",
       "      <td>1808</td>\n",
       "      <td>1813</td>\n",
       "      <td>281</td>\n",
       "      <td>1358</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>450765.0</td>\n",
       "      <td>532203.0</td>\n",
       "      <td>E01011999</td>\n",
       "      <td>E02002489</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>-57.0</td>\n",
       "      <td>TS24 7PW</td>\n",
       "      <td>11884.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A81012</td>\n",
       "      <td>1282</td>\n",
       "      <td>16</td>\n",
       "      <td>1106</td>\n",
       "      <td>1112</td>\n",
       "      <td>256</td>\n",
       "      <td>877</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>450757.0</td>\n",
       "      <td>519893.0</td>\n",
       "      <td>E01012059</td>\n",
       "      <td>E02002497</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>59.0</td>\n",
       "      <td>TS3 6AL</td>\n",
       "      <td>4790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A81013</td>\n",
       "      <td>1189</td>\n",
       "      <td>16</td>\n",
       "      <td>1211</td>\n",
       "      <td>1223</td>\n",
       "      <td>242</td>\n",
       "      <td>812</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>468872.0</td>\n",
       "      <td>519413.0</td>\n",
       "      <td>E01012098</td>\n",
       "      <td>E02002524</td>\n",
       "      <td>D1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000003</td>\n",
       "      <td>125.0</td>\n",
       "      <td>TS12 2FF</td>\n",
       "      <td>6403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A81014</td>\n",
       "      <td>409</td>\n",
       "      <td>32</td>\n",
       "      <td>848</td>\n",
       "      <td>862</td>\n",
       "      <td>147</td>\n",
       "      <td>838</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>445648.0</td>\n",
       "      <td>523909.0</td>\n",
       "      <td>E01012276</td>\n",
       "      <td>E02002536</td>\n",
       "      <td>C1</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E06000004</td>\n",
       "      <td>123.0</td>\n",
       "      <td>TS23 2LA</td>\n",
       "      <td>3798.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  PracCode  depression  alzheimers  blood pressure  hypertension  diabeties  \\\n",
       "1   A81004        1158          29            1368          1387        327   \n",
       "2   A81005         923          43            1568          1575        233   \n",
       "3   A81006        2257          63            2625          2653        594   \n",
       "4   A81007        1482          39            1335          1371        273   \n",
       "5   A81009        1315          20            1541          1554        372   \n",
       "6   A81011        1775          33            1808          1813        281   \n",
       "7   A81012        1282          16            1106          1112        256   \n",
       "8   A81013        1189          16            1211          1223        242   \n",
       "9   A81014         409          32             848           862        147   \n",
       "\n",
       "   cardiovascular disease  insomnia  addiction  social anxiety  ...  oseast1m  \\\n",
       "1                    1145       153          0              11  ...  447840.0   \n",
       "2                    1276        36          0              24  ...  461221.0   \n",
       "3                    2205        23          0              18  ...  444478.0   \n",
       "4                    1012        14          0               6  ...  450765.0   \n",
       "5                    1151        76          0               5  ...  449121.0   \n",
       "6                    1358        44          0               6  ...  450765.0   \n",
       "7                     877        27          0               8  ...  450757.0   \n",
       "8                     812        29          0               8  ...  468872.0   \n",
       "9                     838        15          1               7  ...  445648.0   \n",
       "\n",
       "   osnrth1m     lsoa11     msoa11 ru11ind        rgn       laua    imd  \\\n",
       "1  516130.0  E01012024  E02002510      C1  E12000001  E06000002 -111.0   \n",
       "2  515668.0  E01012117  E02002532      C1  E12000001  E06000003  -86.0   \n",
       "3  519481.0  E01012267  E02002548      C1  E12000001  E06000004   39.0   \n",
       "4  532203.0  E01011999  E02002489      C1  E12000001  E06000001  -57.0   \n",
       "5  518824.0  E01012078  E02002500      C1  E12000001  E06000002  107.0   \n",
       "6  532203.0  E01011999  E02002489      C1  E12000001  E06000001  -57.0   \n",
       "7  519893.0  E01012059  E02002497      C1  E12000001  E06000002   59.0   \n",
       "8  519413.0  E01012098  E02002524      D1  E12000001  E06000003  125.0   \n",
       "9  523909.0  E01012276  E02002536      C1  E12000001  E06000004  123.0   \n",
       "\n",
       "   POSTCODE NUMBER_OF_PATIENTS  \n",
       "1   TS5 8SB             9928.0  \n",
       "2  TS14 7DJ             7998.0  \n",
       "3  TS18 2AT            14455.0  \n",
       "4  TS24 7PW            10022.0  \n",
       "5   TS5 6HF             8735.0  \n",
       "6  TS24 7PW            11884.0  \n",
       "7   TS3 6AL             4790.0  \n",
       "8  TS12 2FF             6403.0  \n",
       "9  TS23 2LA             3798.0  \n",
       "\n",
       "[9 rows x 31 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[1:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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['ITEMS'] = 'sum'\n",
    "agg_cols['loneliness'] = 'sum'\n",
    "agg_cols['NUMBER_OF_PATIENTS'] = 'sum'\n",
    "\n",
    "# Other data to preserve\n",
    "for key in ['SHA','PCT','Street','Town','Town2','Postcode','oseast1m', 'osnrth1m',\n",
    "            'lsoa11', 'msoa11','ru11ind', 'rgn', 'laua', 'imd']:\n",
    "    agg_cols[key] = 'first'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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": 36,
   "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['ITEMS'], axis=0) * 100\n",
    "\n",
    "# Overall percentage for loneliness realted disease prescribing\n",
    "data['loneliness_perc'] = data['loneliness'].divide(data['ITEMS'], axis=0) * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Standardisation (z-scores)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>pcstrip</th>\n",
       "      <th>Date</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>...</th>\n",
       "      <th>depression_perc</th>\n",
       "      <th>alzheimers_perc</th>\n",
       "      <th>blood pressure_perc</th>\n",
       "      <th>hypertension_perc</th>\n",
       "      <th>diabeties_perc</th>\n",
       "      <th>cardiovascular disease_perc</th>\n",
       "      <th>insomnia_perc</th>\n",
       "      <th>addiction_perc</th>\n",
       "      <th>social anxiety_perc</th>\n",
       "      <th>loneliness_perc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-02-01</td>\n",
       "      <td>824</td>\n",
       "      <td>32</td>\n",
       "      <td>1277</td>\n",
       "      <td>1306</td>\n",
       "      <td>280</td>\n",
       "      <td>868</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.925926</td>\n",
       "      <td>0.230133</td>\n",
       "      <td>9.183747</td>\n",
       "      <td>9.392305</td>\n",
       "      <td>2.013664</td>\n",
       "      <td>6.242359</td>\n",
       "      <td>0.359583</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.071917</td>\n",
       "      <td>24.163970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>874</td>\n",
       "      <td>35</td>\n",
       "      <td>1381</td>\n",
       "      <td>1409</td>\n",
       "      <td>291</td>\n",
       "      <td>1004</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.617689</td>\n",
       "      <td>0.224965</td>\n",
       "      <td>8.876462</td>\n",
       "      <td>9.056434</td>\n",
       "      <td>1.870420</td>\n",
       "      <td>6.453272</td>\n",
       "      <td>0.321378</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.044993</td>\n",
       "      <td>23.544157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>895</td>\n",
       "      <td>40</td>\n",
       "      <td>1386</td>\n",
       "      <td>1415</td>\n",
       "      <td>291</td>\n",
       "      <td>957</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.945659</td>\n",
       "      <td>0.265728</td>\n",
       "      <td>9.207467</td>\n",
       "      <td>9.400120</td>\n",
       "      <td>1.933169</td>\n",
       "      <td>6.357537</td>\n",
       "      <td>0.279014</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.059789</td>\n",
       "      <td>24.181226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-05-01</td>\n",
       "      <td>861</td>\n",
       "      <td>35</td>\n",
       "      <td>1427</td>\n",
       "      <td>1469</td>\n",
       "      <td>315</td>\n",
       "      <td>965</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.649606</td>\n",
       "      <td>0.229659</td>\n",
       "      <td>9.363517</td>\n",
       "      <td>9.639108</td>\n",
       "      <td>2.066929</td>\n",
       "      <td>6.332021</td>\n",
       "      <td>0.269029</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.045932</td>\n",
       "      <td>24.186352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-06-01</td>\n",
       "      <td>865</td>\n",
       "      <td>32</td>\n",
       "      <td>1397</td>\n",
       "      <td>1428</td>\n",
       "      <td>318</td>\n",
       "      <td>956</td>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.673619</td>\n",
       "      <td>0.209891</td>\n",
       "      <td>9.163059</td>\n",
       "      <td>9.366391</td>\n",
       "      <td>2.085793</td>\n",
       "      <td>6.270497</td>\n",
       "      <td>0.314837</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.065591</td>\n",
       "      <td>23.921028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-07-01</td>\n",
       "      <td>915</td>\n",
       "      <td>35</td>\n",
       "      <td>1433</td>\n",
       "      <td>1466</td>\n",
       "      <td>296</td>\n",
       "      <td>1015</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.843658</td>\n",
       "      <td>0.223528</td>\n",
       "      <td>9.151871</td>\n",
       "      <td>9.362626</td>\n",
       "      <td>1.890407</td>\n",
       "      <td>6.482309</td>\n",
       "      <td>0.293780</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.057479</td>\n",
       "      <td>24.096309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>879</td>\n",
       "      <td>49</td>\n",
       "      <td>1463</td>\n",
       "      <td>1495</td>\n",
       "      <td>327</td>\n",
       "      <td>994</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.735352</td>\n",
       "      <td>0.319718</td>\n",
       "      <td>9.545870</td>\n",
       "      <td>9.754665</td>\n",
       "      <td>2.133629</td>\n",
       "      <td>6.485711</td>\n",
       "      <td>0.300144</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.065249</td>\n",
       "      <td>24.729218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-09-01</td>\n",
       "      <td>869</td>\n",
       "      <td>40</td>\n",
       "      <td>1362</td>\n",
       "      <td>1399</td>\n",
       "      <td>300</td>\n",
       "      <td>940</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.850276</td>\n",
       "      <td>0.269288</td>\n",
       "      <td>9.169247</td>\n",
       "      <td>9.418338</td>\n",
       "      <td>2.019658</td>\n",
       "      <td>6.328262</td>\n",
       "      <td>0.269288</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.053858</td>\n",
       "      <td>24.155110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AL100BS</td>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>997</td>\n",
       "      <td>41</td>\n",
       "      <td>1514</td>\n",
       "      <td>1551</td>\n",
       "      <td>353</td>\n",
       "      <td>1007</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.916563</td>\n",
       "      <td>0.243309</td>\n",
       "      <td>8.984630</td>\n",
       "      <td>9.204202</td>\n",
       "      <td>2.094831</td>\n",
       "      <td>5.975906</td>\n",
       "      <td>0.367931</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.041541</td>\n",
       "      <td>23.802742</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   pcstrip        Date  depression  alzheimers  blood pressure  hypertension  \\\n",
       "1  AL100BS  2018-02-01         824          32            1277          1306   \n",
       "2  AL100BS  2018-03-01         874          35            1381          1409   \n",
       "3  AL100BS  2018-04-01         895          40            1386          1415   \n",
       "4  AL100BS  2018-05-01         861          35            1427          1469   \n",
       "5  AL100BS  2018-06-01         865          32            1397          1428   \n",
       "6  AL100BS  2018-07-01         915          35            1433          1466   \n",
       "7  AL100BS  2018-08-01         879          49            1463          1495   \n",
       "8  AL100BS  2018-09-01         869          40            1362          1399   \n",
       "9  AL100BS  2018-10-01         997          41            1514          1551   \n",
       "\n",
       "   diabeties  cardiovascular disease  insomnia  addiction  ...  \\\n",
       "1        280                     868        50          0  ...   \n",
       "2        291                    1004        50          0  ...   \n",
       "3        291                     957        42          0  ...   \n",
       "4        315                     965        41          0  ...   \n",
       "5        318                     956        48          0  ...   \n",
       "6        296                    1015        46          0  ...   \n",
       "7        327                     994        46          0  ...   \n",
       "8        300                     940        40          0  ...   \n",
       "9        353                    1007        62          0  ...   \n",
       "\n",
       "   depression_perc  alzheimers_perc  blood pressure_perc  hypertension_perc  \\\n",
       "1         5.925926         0.230133             9.183747           9.392305   \n",
       "2         5.617689         0.224965             8.876462           9.056434   \n",
       "3         5.945659         0.265728             9.207467           9.400120   \n",
       "4         5.649606         0.229659             9.363517           9.639108   \n",
       "5         5.673619         0.209891             9.163059           9.366391   \n",
       "6         5.843658         0.223528             9.151871           9.362626   \n",
       "7         5.735352         0.319718             9.545870           9.754665   \n",
       "8         5.850276         0.269288             9.169247           9.418338   \n",
       "9         5.916563         0.243309             8.984630           9.204202   \n",
       "\n",
       "  diabeties_perc cardiovascular disease_perc insomnia_perc addiction_perc  \\\n",
       "1       2.013664                    6.242359      0.359583            0.0   \n",
       "2       1.870420                    6.453272      0.321378            0.0   \n",
       "3       1.933169                    6.357537      0.279014            0.0   \n",
       "4       2.066929                    6.332021      0.269029            0.0   \n",
       "5       2.085793                    6.270497      0.314837            0.0   \n",
       "6       1.890407                    6.482309      0.293780            0.0   \n",
       "7       2.133629                    6.485711      0.300144            0.0   \n",
       "8       2.019658                    6.328262      0.269288            0.0   \n",
       "9       2.094831                    5.975906      0.367931            0.0   \n",
       "\n",
       "  social anxiety_perc loneliness_perc  \n",
       "1            0.071917       24.163970  \n",
       "2            0.044993       23.544157  \n",
       "3            0.059789       24.181226  \n",
       "4            0.045932       24.186352  \n",
       "5            0.065591       23.921028  \n",
       "6            0.057479       24.096309  \n",
       "7            0.065249       24.729218  \n",
       "8            0.053858       24.155110  \n",
       "9            0.041541       23.802742  \n",
       "\n",
       "[9 rows x 38 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[1:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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 = {'NUMBER_OF_PATIENTS': 'mean', 'SHA':'first', 'PCT':'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": 39,
   "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": 40,
   "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": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAzgAAAFpCAYAAABQwY35AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAHjxJREFUeJzt3X2MZeddH/Dvjyx5aSbYThwP7tplgjCUCAuIV65RVNjFNC9OhV0proICsVO3KyBESASlQ5Ha9E0JlUJKJJTibgIOAjZpSupV1gWM4y0NwiHZxvHGMa43xorXdm1I7G2XhIDF0z/mLEwms547s/fOnfPs5yNdzb3nPnPv99xz9/F8fc49t1prAQAA6MHXzTsAAADAtCg4AABANxQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAAADdUHAAAIBuKDgAAEA3ds07QJJceOGFbWlpaaKxf/Znf5bnP//5sw10lsaQMZFz2saQczMZjx49+qettRfPONLUTTqfjGF7JXJO2xhyjiFjcu7MJy9+8YtHsT22Yizvta3odd16Xa9ksnWbeC5prc39csUVV7RJ3XnnnROPnZcxZGxNzmkbQ87NZEzyybYD5ofNXiadT8awvVqTc9rGkHMMGVs7d+aTsWyPrbBu49PrerU22bpNOpc4RA0AAOiGggMAAHRDwQEAALqh4AAAAN1QcAAAgG4oOAAAQDcUHAAAoBsTFZyqOr+qPlRVf1RV91XV91TVC6vq9qp6YPh5wTC2qurdVXW8qu6pqpfNdhUAAABWTLoH5xeS/FZr7e8m+c4k9yVZTnJHa+2yJHcMt5Pk1UkuGy77k7xnqokBAADOYMOCU1XfkOR7k7w3SVprf9FaeyrJtUluGYbdkuS64fq1Sd4/fOHoXUnOr6qLp54cAABgjUn24Hxzkj9J8stV9amqOlBVz0+y2Fp7LEmGnxcN43cneXjV758YlgEAAMxUtdaeeUDVniR3JXl5a+3jVfULSf5vkje31s5fNe7J1toFVXU4ydtbax8blt+R5K2ttaNrHnd/Vg5hy+Li4hUHDx6cKPCpU6eysLAw8QrOwxgyJnJO2xhybibjvn37jrbW9sw40lRsZT4Zw/ZK5Jy2MeQcQ8bk3JlPDhw4MIrtsRVjea9tRa/r1ut6JZOt28RzSWvtGS9JvjHJQ6tu//0kh5Pcn+TiYdnFSe4frv9Skh9aNf6vx53pcsUVV7RJ3XnnnROPnZcxZGxNzmkbQ87NZEzyybbB/LATL5POJ2PYXq3JOW1jyDmGjK2dO/PJWLbHVli38el1vVqbbN0mnUs2PESttfZ/kjxcVd82LLo6yWeTHEpyw7DshiS3DtcPJXnDcDa1q5KcbMOhbAAAMEtLy4dz7JGT847BHO2acNybk/xaVT07yYNJ3piVz+98sKpuSvL5JNcPY29Lck2S40m+NIwFAACYuYkKTmvt7iTrHe929TpjW5I3nWUuAACATZv0e3AAAAB2PAUHAADohoIDAAB0Q8EBAAC6oeAAAADdUHAAAIBuKDgAAEA3FBwAAKAbE33RJwAA7GRLy4fnHYEdwh4cAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAAADdUHAAAOjO0vJhp44+R/keHAAARkuJYS17cAAAgG4oOAAAQDcUHAAAoBsKDgAA0A0FBwAA6IaCAwAAdEPBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAANCtpeXDWVo+PO8YbCMFBwAA6MaueQcAAIDNsleGM7EHBwAA6IaCAwAAdEPBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgGwoOAADQjYkKTlU9VFXHquruqvrksOyFVXV7VT0w/LxgWF5V9e6qOl5V91TVy2a5AgAAAKdtZg/Ovtbad7XW9gy3l5Pc0Vq7LMkdw+0keXWSy4bL/iTvmVZYAACAZ3I2h6hdm+SW4fotSa5btfz9bcVdSc6vqovP4nkAAAAmMmnBaUl+p6qOVtX+Ydlia+2xJBl+XjQs353k4VW/e2JYBgAAMFPVWtt4UNXfbq09WlUXJbk9yZuTHGqtnb9qzJOttQuq6nCSt7fWPjYsvyPJW1trR9c85v6sHMKWxcXFKw4ePDhR4FOnTmVhYWGytZuTMWRM5Jy2MeTcTMZ9+/YdXXVI6o62lflkDNsrkXPaxpBzDBmTc2c+OXDgwCi2x1aM5b12JsceOXnG+xaflzz+5a9edvnu82acaPbGvs2eySTrNulcsmuSJ2ytPTr8fKKqPpzkyiSPV9XFrbXHhkPQnhiGn0hy6apfvyTJo+s85s1Jbk6SPXv2tL17904SJUeOHMmkY+dlDBkTOadtDDnHkHErtjKfjOW1kHO6xpBzDBmT8eTcrLXzycLCQpfrmYx/G964fPiM973l8qfzzmNf/WfuQ6/fO+NEszf2bfZMprluGx6iVlXPr6oXnL6e5BVJPpPkUJIbhmE3JLl1uH4oyRuGs6ldleTk6UPZAAAAZmmSPTiLST5cVafH/3pr7beq6hNJPlhVNyX5fJLrh/G3JbkmyfEkX0ryxqmnBgAAWMeGBae19mCS71xn+ReSXL3O8pbkTVNJBwAAsAlnc5poAACAHUXBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAAADdUHAAAIBuKDgAAEA3FBwAAKAbCg4AANANBQcAAOiGggMAAHRDwQEAALqh4AAAAN1QcAAAgG4oOAAAQDcUHAAAoBsKDgAA0A0FBwAA6IaCAwAAdEPBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6sWveAQAAYNaWlg//9fWH3vGaOSZh1uzBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgG86iBgDAaKw+Gxqsxx4cAACgGwoOAADQjYkLTlU9q6o+VVUfGW6/pKo+XlUPVNUHqurZw/LnDLePD/cvzSY6AADAV9vMHpyfTHLfqts/l+RdrbXLkjyZ5KZh+U1JnmytfUuSdw3jAAAAZm6iglNVlyR5TZIDw+1K8v1JPjQMuSXJdcP1a4fbGe6/ehgPAAAwU5PuwfmPSd6a5K+G2y9K8lRr7enh9okku4fru5M8nCTD/SeH8QAAADNVrbVnHlD1D5Nc01r78aram+Snk7wxyR8Mh6Glqi5Ncltr7fKqujfJK1trJ4b7PpfkytbaF9Y87v4k+5NkcXHxioMHD04U+NSpU1lYWNjEKm6/MWRM5Jy2MeTcTMZ9+/Ydba3tmXGkqdjKfDKG7ZXIOW1jyDmGjMm5M58cOHBgFNtjK8byXlvr2CMnNxyz+Lzk8S+f+f7Ld583xUTbZ6zbbBKTrNukc8kk34Pz8iQ/WFXXJHlukm/Iyh6d86tq17CX5pIkjw7jTyS5NMmJqtqV5LwkX1z7oK21m5PcnCR79uxpe/funSBKcuTIkUw6dl7GkDGRc9rGkHMMGbdiK/PJWF4LOadrDDnHkDEZT87NWjufLCwsdLmeyXi34Y0TfA/OWy5/Ou88duY/cx96/d4pJto+Y91mk5jmum14iFpr7Wdaa5e01paSvC7JR1trr09yZ5LXDsNuSHLrcP3QcDvD/R9tG+0mAgAAmIKz+R6cf57kp6rqeFY+Y/PeYfl7k7xoWP5TSZbPLiIAAMBkJjlE7a+11o4kOTJcfzDJleuM+fMk108hGwAAwKaczR4cAACAHUXBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAAADdUHAAAIBuKDgAAEA3FBwAAKAbCg4AANANBQcAAOiGggMAAHRDwQEAALqh4AAAcE5ZWj6cpeXD847BjCg4AABANxQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAAADdUHAAAIBuKDgAAEA3FBwAAKAbCg4AANANBQcAAOiGggMAAHRj17wDAADARpaWD887AiNhDw4AANANBQcAAOiGggMAAHRDwQEAALqh4AAAAN1QcAAAgG4oOAAAQDcUHAAAoBsKDgAA0A0FBwAA6MaGBaeqnltVf1hVn66qe6vqXw/LX1JVH6+qB6rqA1X17GH5c4bbx4f7l2a7CgAAACsm2YPzlSTf31r7ziTfleRVVXVVkp9L8q7W2mVJnkxy0zD+piRPtta+Jcm7hnEAAAAzt2HBaStODTe/fri0JN+f5EPD8luSXDdcv3a4neH+q6uqppYYAADgDCb6DE5VPauq7k7yRJLbk3wuyVOttaeHISeS7B6u707ycJIM959M8qJphgYAAFhPtdYmH1x1fpIPJ/mXSX55OAwtVXVpkttaa5dX1b1JXtlaOzHc97kkV7bWvrDmsfYn2Z8ki4uLVxw8eHCiDKdOncrCwsLEmedhDBkTOadtDDk3k3Hfvn1HW2t7ZhxpKrYyn4xheyVyTtsYco4hY3LuzCcHDhwYxfbYirG810479sjJiccuPi95/Msbj7t893lnkWj7jW2bbcYk6zbpXLJrM0/cWnuqqo4kuSrJ+VW1a9hLc0mSR4dhJ5JcmuREVe1Kcl6SL67zWDcnuTlJ9uzZ0/bu3TtRhiNHjmTSsfMyhoyJnNM2hpxjyLgVW5lPxvJayDldY8g5hozJeHJu1tr5ZGFhocv1TMa3DW9cPjzx2Ldc/nTeeWzjP3Mfev3es0i0/ca2zTZjmus2yVnUXjzsuUlVPS/JDyS5L8mdSV47DLshya3D9UPD7Qz3f7RtZjcRAADAFk2yB+fiJLdU1bOyUog+2Fr7SFV9NsnBqvp3ST6V5L3D+Pcm+dWqOp6VPTevm0FuAACAr7FhwWmt3ZPku9dZ/mCSK9dZ/udJrp9KOgAAgE2Y6CxqAAAAY6DgAAAA3VBwAACAbig4AABANxQcAACgGwoOAADnpKXlw1naxBeIMg4KDgAA0A0FBwAA6IaCAwAAdEPBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAAADdUHAAAIBuKDgAAEA3FBwAAKAbCg4AANANBQcAAOiGggMAAHRj17wDAADAmSwtH553BEbGHhwAAKAbCg4AANANBQcAAOiGggMAAHRDwQEAALqh4AAAAN1QcAAAgG4oOAAAQDcUHAAAoBsKDgAA0A0FBwAA6IaCAwAAdEPBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgGxsWnKq6tKrurKr7qureqvrJYfkLq+r2qnpg+HnBsLyq6t1Vdbyq7qmql816JQAAAJLJ9uA8neQtrbVvT3JVkjdV1UuTLCe5o7V2WZI7httJ8uoklw2X/UneM/XUAAAA69iw4LTWHmut/a/h+v9Lcl+S3UmuTXLLMOyWJNcN169N8v624q4k51fVxVNPDgAAsMamPoNTVUtJvjvJx5MsttYeS1ZKUJKLhmG7kzy86tdODMsAAABmqlprkw2sWkjyP5L8+9bab1bVU62181fd/2Rr7YKqOpzk7a21jw3L70jy1tba0TWPtz8rh7BlcXHxioMHD06U49SpU1lYWJho7LyMIWMi57SNIedmMu7bt+9oa23PjCNNxVbmkzFsr0TOaRtDzjFkTM6d+eTAgQOj2B5bMZb32rFHTm76dxaflzz+5cnHX777vE0/xzyMZZttxSTrNulcsmuSJ6yqr0/yX5P8WmvtN4fFj1fVxa21x4ZD0J4Ylp9IcumqX78kyaNrH7O1dnOSm5Nkz549be/evZNEyZEjRzLp2HkZQ8ZEzmkbQ84xZNyKrcwnY3kt5JyuMeQcQ8ZkPDk3a+18srCw0OV6JuPZhjcuH97077zl8qfzzmMT/ZmbJHno9Xs3/RzzMJZtthXTXLdJzqJWSd6b5L7W2s+vuutQkhuG6zckuXXV8jcMZ1O7KsnJ04eyAQAAzNIk1fblSX4kybGquntY9i+SvCPJB6vqpiSfT3L9cN9tSa5JcjzJl5K8caqJAQAAzmDDgjN8lqbOcPfV64xvSd50lrkAAAA2bVNnUQMAANjJFBwAAKAbCg4AAOe0peXDWdrC2drYmRQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAAADdUHAAAIBuKDgAAEA3FBwAAKAbCg4AANANBQcAAOiGggMAAHRDwQEAALqh4AAAQJKl5cNZWj487xicJQUHAADohoIDAAB0Y9e8AwAAwFoOFWOr7MEBAAC6oeAAAADdUHAAAIBuKDgAAEA3FBwAAKAbCg4AANANBQcAAOiGggMAAHRDwQEAALqh4AAAAN1QcAAAgG4oOAAAQDcUHAAAoBsKDgAA0A0FBwAA6IaCAwAAdEPBAQAAurFr3gEAAOC0peXD847AyNmDAwAAdEPBAQAAuqHgAAAA3VBwAACAbig4AABANxQcAACgGxsWnKp6X1U9UVWfWbXshVV1e1U9MPy8YFheVfXuqjpeVfdU1ctmGR4AAGC1Sfbg/EqSV61ZtpzkjtbaZUnuGG4nyauTXDZc9id5z3RiAgAAbGzDgtNa+70kX1yz+NoktwzXb0ly3arl728r7kpyflVdPK2wAAAAz2Srn8FZbK09liTDz4uG5buTPLxq3IlhGQAAwMxVa23jQVVLST7SWvuO4fZTrbXzV93/ZGvtgqo6nOTtrbWPDcvvSPLW1trRdR5zf1YOY8vi4uIVBw8enCjwqVOnsrCwMNHYeRlDxkTOaRtDzs1k3Ldv39HW2p4ZR5qKrcwnY9heiZzTNoacY8iYnDvzyYEDB0axPbZip77Xjj1y8qwfY/F5yeNf3vrvX777vLPOMAs7dZtNwyTrNulcsmuLGR6vqotba48Nh6A9MSw/keTSVeMuSfLoeg/QWrs5yc1JsmfPnrZ3796JnvjIkSOZdOy8jCFjIue0jSHnGDJuxVbmk7G8FnJO1xhyjiFjMp6cm7V2PllYWOhyPZOduw1vXD581o/xlsufzjuPbfXP3OSh1+896wyzsFO32TRMc922eojaoSQ3DNdvSHLrquVvGM6mdlWSk6cPZQMAAJi1DattVf1Gkr1JLqyqE0n+VZJ3JPlgVd2U5PNJrh+G35bkmiTHk3wpyRtnkBkAAGBdGxac1toPneGuq9cZ25K86WxDAQAAbMVWD1EDAADYcRQcAACgGwoOAADQDQUHAADohoIDAAB0Q8EBAAC6oeAAAMAqS8uHs7R8eN4x2KINvwcHAABmaaeWidO5HnrHa+achM2wBwcAAOiGggMAAHRDwQEAALqh4AAAAN1QcAAAgG4oOAAAQDcUHAAAoBsKDgAA0A0FBwAA6IaCAwAAdEPBAQAAurFr3gEAADg3LS0fnncEOmQPDgAA0A0FBwAA6IaCAwAAdEPBAQAAuqHgAAAA3VBwAACAbig4AABAN3wPDgAA28r33zBL9uAAAMAElpYPK2cjoOAAAADdcIgaAAA8A3ttxsUeHAAA2ASHqu1sCg4AANANBQcAAOiGz+AAALAtHNbFdrAHBwAA6IaCAwAAdEPBAQAAuqHgAADAFjhd9M6k4AAAAN1wFjUAAGbi9N6Nh97xmjknma3Ve3F6X9cxsAcHAADohj04AADMlM+psJ1mUnCq6lVJfiHJs5IcaK29YxbPAwDAzqPQME9TLzhV9awkv5jkHyQ5keQTVXWotfbZaT8XAMC8nSufM5mEYvM3vC/mZxZ7cK5Mcry19mCSVNXBJNcmUXAAAHa4M5UUf6hP5kyv39rCowDNziwKzu4kD6+6fSLJ35vB8wAA7Bhn+oN1mn/IbuaxNhp77JGTuXETe1zsnZmOta/j2tubeZ9MWkan8d7caOxW3uezKnnVWpvuA1Zdn+SVrbV/Otz+kSRXttbevGbc/iT7h5vfluT+CZ/iwiR/OqW4szKGjImc0zaGnJvJ+E2ttRfPMsy0bHE+GcP2SuSctjHkHEPG5NyZT76QcWyPrRjLe20rel23XtcrmWzdJppLZlFwvifJ21prrxxu/0yStNbePqXH/2Rrbc80HmtWxpAxkXPaxpBzDBm3y1heCzmnaww5x5AxGU/Os9Xzelq38el1vZLprtssvgfnE0kuq6qXVNWzk7wuyaEZPA8AAMBXmfpncFprT1fVTyT57aycJvp9rbV7p/08AAAAa83ke3Baa7cluW0Wj53k5hk97jSNIWMi57SNIecYMm6XsbwWck7XGHKOIWMynpxnq+f1tG7j0+t6JVNct6l/BgcAAGBeZvEZHAAAgLnYkQWnql5VVfdX1fGqWl7n/udU1QeG+z9eVUvbn3KinD9VVZ+tqnuq6o6q+qadmHPVuNdWVauquZydY5KcVfWPh9f03qr69Z2Wsar+TlXdWVWfGrb7Ndudccjxvqp6oqo+c4b7q6rePazHPVX1su3OuF3MJ9ubc9W4uc0nY5hLhgw7fj45l+eSqrp+eH/81dr3cVX9zLDO91fVK+eVcRqq6m1V9UhV3T1c5vLfrWmZdI4ao6p6qKqODdvpk/POczbWm1uq6oVVdXtVPTD8vGDLT9Ba21GXrJyY4HNJvjnJs5N8OslL14z58ST/abj+uiQf2KE59yX5W8P1H9upOYdxL0jye0nuSrJnJ+ZMclmSTyW5YLh90Q7MeHOSHxuuvzTJQ9v9Wg7P/b1JXpbkM2e4/5ok/z1JJbkqycfnkXOHbDPzyRRzDuPmNp+MYS7ZRM65zyfn8lyS5Nuz8j04R1a/j4dt8ekkz0nykmE7Pmveec9iPd+W5KfnnWNK6zLRHDXWS5KHklw47xxTWpevmVuS/Icky8P15SQ/t9XH34l7cK5Mcry19mBr7S+SHExy7Zox1ya5Zbj+oSRXV1VtY8ZkgpyttTtba18abt6V5JJtzphM9nomyb/Nyhvrz7cz3CqT5PxnSX6xtfZkkrTWntiBGVuSbxiun5fk0W3M9zchWvu9JF98hiHXJnl/W3FXkvOr6uLtSbetzCfTNYb5ZAxzSTKS+eRcnktaa/e11tb70uBrkxxsrX2ltfbHSY5nZXsyf5POUczZGeaW1f89viXJdVt9/J1YcHYneXjV7RPDsnXHtNaeTnIyyYu2Jd06GQbr5Vztpqz8X67ttmHOqvruJJe21j6yncHWmOT1/NYk31pVv19Vd1XVq7Yt3YpJMr4tyQ9X1YmsnEnwzdsTbdM2+/4dK/PJdI1hPhnDXJL0M5+cK3PJaj2u808Mhxi+76wOC5q/HrfNai3J71TV0araP+8wM7DYWnssSYafF231gWZymuiztN7/OV17qrdJxszaxBmq6oeT7EnyfTNNtL5nzFlVX5fkXUlu3K5AZzDJ67krK4eW7M3K/73+n1X1Ha21p2ac7bRJMv5Qkl9prb2zqr4nya8OGf9q9vE2ZSf8G9oO5pPpGsN8Moa5JOlnPtkJ/362rKp+N8k3rnPXz7bWbj3Tr62zbEev8zOtZ5L3ZGWvaxt+vjPJP9m+dFM1um2zSS9vrT1aVRclub2q/mjYE8IaO7HgnEhy6arbl+Rrd8ufHnOiqnZlZdf9M+1Cn4VJcqaqfiArE8j3tda+sk3ZVtso5wuSfEeSI8NROd+Y5FBV/WBrbTs/wDbpdr+rtfaXSf64qu7Pyh8pn9ieiBNlvCnJq5KktfYHVfXcJBcmmcchMM9kovdvB8wn0zWG+WQMc8npDD3MJ6OeS1prP7CFXxvdOk+6nlX1n5PM82iOszW6bbMZrbVHh59PVNWHs3JIXk8F5/Gquri19thwqOuW57qdeIjaJ5JcVlUvqapnZ+VDv4fWjDmU5Ibh+muTfLQNn0jaRhvmHA7V+KUkPzinY7yTDXK21k621i5srS211paycmz/dpebDXMO/ltWPmidqrowK4eZPLjDMn4+ydVDxm9P8twkf7KNGSd1KMkbhjMgXZXk5Ondwp0xn0zXGOaTMcwlST/zybkyl6x2KMnrauUMjC/JSjn+wzln2rI1n5n6R0nWPWPeSEzy72qUqur5VfWC09eTvCLj3lbrWf3f4xuSnGkv6sa2enaCWV6yclaW/52VM2H87LDs32TlP5TJyiT/X7Lywb4/TPLNOzTn7yZ5PMndw+XQTsy5ZuyRzOEsahO+npXk55N8NsmxJK/bgRlfmuT3s3LmlruTvGJOr+VvJHksyV9m5f9o3ZTkR5P86KrX8heH9Tg2r22+Q7aZ+WSKOdeMnct8Moa5ZMKcc59PzuW5JCt/7J9I8pXh395vr7rvZ4d1vj/Jq+ed9SzX81eHbXdPVv7AvHjemc5yfb7m31UPl6ycGe7Tw+Xesa/bGeaWFyW5I8kDw88XbvXxa3gSAACA0duJh6gBAABsiYIDAAB0Q8EBAAC6oeAAAADdUHAAAIBuKDgAAEA3FBwAAKAbCg4AANCN/w90PFKD1yNZfQAAAABJRU5ErkJggg==\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": 42,
   "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": 43,
   "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": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAz8AAAFpCAYAAACyHZaAAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAHbFJREFUeJzt3X+wpXddH/D3x6xBYDUJRLaZTcqFMVpT0o5kJ8bSOrvEkRAcwnSgTQYl0tgdFaktdMhSp4OjdcBaRJmhSoZQQlUWRGp2srE0DdkyOiSSCM0mxJgVU7IhDSrJ6gr+SP32j/MEbpb9ce69555zn/t9vWbO3PM8z/ee837uved7973Pc55brbUAAABsdl+36AAAAADzoPwAAABdUH4AAIAuKD8AAEAXlB8AAKALyg8AANAF5QcAAOiC8gMAAHRB+QEAALqg/AAAAF3YsugAJ3P22We3paWlqcb+xV/8RZ75zGeub6AZGEPOMWRM5Jy1aXPeddddf9Ja++Y5RJqpzTafjCFjIuesjSHnSjL2MJ9sRGP4OZq1Hvc56Wu/p51PNnT5WVpayp133jnV2AMHDmTnzp3rG2gGxpBzDBkTOWdt2pxV9X/WP83sbbb5ZAwZEzlnbQw5V5Kxh/lkIxrDz9Gs9bjPSV/7Pe184rQ3AACgC8oPAADQBeUHAADogvIDAAB0QfkBAAC6oPwAAABdUH4AAIAuKD8AAEAXlB8AAKALyg8AANAF5QcAAOiC8gMAAHRB+QEAALqwZdEBAABYf0t79idJ3nfZMxecBBbHkR8AAKALpyw/VfXeqvpCVd2zbN3PVdXvV9XdVfXfqurMZdveXFWHqur+qnrJsvWXDesOVdWe2e8KAADAiU1z5Od9SS47Zt0tSV7QWvsHSf4gyZuTpKouSHJlkr8/fM5/rqrTquq0JO9K8tIkFyS5ahgLAAAwF6csP621jyf54jHr/kdr7Ylh8fYk5w73r0iyt7X2V621P0pyKMnFw+1Qa+2zrbW/TrJ3GAsAADAXs3jPz79I8lvD/e1JHlq27fCw7kTrAQAA5qJaa6ceVLWU5KbW2guOWf8TSXYk+aettVZV70ryidbarwzbr09ycyYl6yWttR8a1v9Akotba68/znPtTrI7SbZt23bR3r17p9qRo0ePZuvWrVONXaQx5BxDxkTOWZs2565du+5qre2YQ6Q128zzyRgyJnLO2hhyriRjD/PJRnLw4SNJkuedcdqG/zmatTG8dtZDT/s99XzSWjvlLclSknuOWXd1kk8kecaydW9O8uZlyx9N8l3D7aMnGnei20UXXdSmddttt009dpHGkHMMGVuTc9amzZnkzjbFvLHRbpttPhlDxtbknLUx5FxJxh7mk43kudfe1J577U2j+DmatR73ubW+9nva+WRVp71V1WVJrk3y8tbal5Zt2pfkyqp6WlU9L8n5SX43ySeTnF9Vz6uq0zO5KMK+1Tw3AADAapzyj5xW1QeS7ExydlUdTvKWTI7cPC3JLVWVJLe31n64tXZvVX0oyWeSPJHkda21/zc8zo9lciTotCTvba3duw77AwAAcFynLD+ttauOs/r6k4z/mSQ/c5z1N2fy/h8AAIC5m8XV3gAAADY85QcAAOiC8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwAA6ILyAwAAdEH5AQAAuqD8AAAAXVB+AACALig/AABAF5QfAACgC8oPAADQBeUHAADogvIDAAB0QfkBAAC6oPwAAHTk4MNHsrRn/6JjwEIoPwAAQBeUHwAAoAvKDwAA0AXlBwAA6ILyAwAAdEH5AQAAuqD8AAAAXVB+AACALig/AABAF5QfAACgC8oPAADQBeUHAADogvIDAAB0QfkBAAC6oPwAAABdUH4AAIAuKD8AAEAXlB8AAKALyg8AANAF5QcAAOiC8gMAAHThlOWnqt5bVV+oqnuWrXtWVd1SVQ8MH88a1ldVvbOqDlXV3VX1wmWfc/Uw/oGqunp9dgcAAOD4pjny874klx2zbk+SW1tr5ye5dVhOkpcmOX+47U7yS8mkLCV5S5LvTHJxkrc8WZgAAADm4ZTlp7X28SRfPGb1FUluGO7fkOQVy9a/v03cnuTMqjonyUuS3NJa+2Jr7bEkt+RrCxUAAMC6We17fra11h5JkuHjc4b125M8tGzc4WHdidYDAADMRbXWTj2oainJTa21FwzLj7fWzly2/bHW2llVtT/JW1trvz2svzXJm5K8OMnTWmv/YVj/75N8qbX29uM81+5MTpnLtm3bLtq7d+9UO3L06NFs3bp1qrGLNIacY8iYyDlr0+bctWvXXa21HXOItGabeT4ZQ8ZEzlkbQ86VZOxhPtlIDj58JEmy7enJo19OLtx+xoITzc8YXjvroaf9nno+aa2d8pZkKck9y5bvT3LOcP+cJPcP99+d5KpjxyW5Ksm7l61/yrgT3S666KI2rdtuu23qsYs0hpxjyNianLM2bc4kd7Yp5o2Ndtts88kYMrYm56yNIedKMvYwn2wkz732pvbca29q7/yV32zPvfamRceZqzG8dtZDT/s97Xyy2tPe9iV58optVye5cdn61wxXfbskyZE2OS3uo0m+t6rOGi508L3DOgAAgLnYcqoBVfWBJDuTnF1VhzO5atvbknyoqq5J8rkkrxqG35zk8iSHknwpyWuTpLX2xar66SSfHMb9VGvt2IsoAAAArJtTlp/W2lUn2HTpcca2JK87weO8N8l7V5QOAABgRlZ72hsAAMCoKD8AAEAXlB8AAKALyg8AANAF5QcAAOiC8gMAAHThlJe6BgBgvJb27F90BNgwHPkBAAC6oPwAAABdUH4AAIAuKD8AAEAXlB8AAKALyg8AANAF5QcAAOiC8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwAA6ILyAwAAdEH5AQAAuqD8AAAAXVB+AACALig/AABAF5QfAACgC8oPAADQBeUHAADogvIDAAB0QfkBAAC6oPwAAABdUH4AAIAuKD8AAEAXlB8AAKALyg8AANAF5QcAAOiC8gMAAHRB+QEAALqg/AAAAF1QfgAAgC6sqfxU1b+pqnur6p6q+kBVfUNVPa+q7qiqB6rqg1V1+jD2acPyoWH70ix2AAAAYBqrLj9VtT3Jv0qyo7X2giSnJbkyyc8meUdr7fwkjyW5ZviUa5I81lr7liTvGMYBAADMxVpPe9uS5OlVtSXJM5I8kuTFST48bL8hySuG+1cMyxm2X1pVtcbnBwAAmMqqy09r7eEk/ynJ5zIpPUeS3JXk8dbaE8Oww0m2D/e3J3lo+NwnhvHPXu3zAwAArES11lb3iVVnJfmNJP88yeNJfn1Yfstwaluq6rwkN7fWLqyqe5O8pLV2eNj2h0kubq396TGPuzvJ7iTZtm3bRXv37p0qz9GjR7N169ZV7cs8jSHnGDImcs7atDl37dp1V2ttxxwirdlmnk/GkDGRc9bGkHMlGXuYTzaCgw8fecrytqcnj345uXD7GQtKNH9jeO2sh572e9r5ZMsanuN7kvxRa+2Pk6SqPpLkHyU5s6q2DEd3zk3y+WH84STnJTk8nCZ3RpIvHvugrbXrklyXJDt27Gg7d+6cKsyBAwcy7dhFGkPOMWRM5Jy1seRcic08n4whYyLnrI0h5xgyrsZq55ON4Af37H/K8hsvfCJvP7glD75652ICLcBm/bk8lV73+2TW8p6fzyW5pKqeMbx359Ikn0lyW5JXDmOuTnLjcH/fsJxh+8faag87AQAArNBa3vNzRyYXLvi9JAeHx7ouybVJ3lBVhzJ5T8/1w6dcn+TZw/o3JNmzhtwAAAArspbT3tJae0uStxyz+rNJLj7O2L9M8qq1PB8AAMBqrfVS1wAAAKOg/AAAAF1QfgAAgC4oPwAAQBeUHwCADi3t2Z+lY/4GEGx2yg8AANAF5QcAAOiC8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwAA6ILyAwAAdEH5AQAAuqD8AAAAXVB+AACALig/AABAF5QfAACgC8oPAADQBeUHAADogvIDAAB0QfkBAAC6oPwAAABdUH4AAIAuKD8AAEAXlB8AAKALyg8AANAF5QcAAOiC8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwAA6ILyAwAAdEH5AQAAurCm8lNVZ1bVh6vq96vqvqr6rqp6VlXdUlUPDB/PGsZWVb2zqg5V1d1V9cLZ7AIAAMCprfXIzy8m+e+ttb+X5B8muS/JniS3ttbOT3LrsJwkL01y/nDbneSX1vjcAAAAU1t1+amqb0ry3UmuT5LW2l+31h5PckWSG4ZhNyR5xXD/iiTvbxO3Jzmzqs5ZdXIAAIAVWMuRn+cn+eMk/6WqPlVV76mqZybZ1lp7JEmGj88Zxm9P8tCyzz88rAMAAFh31Vpb3SdW7Uhye5IXtdbuqKpfTPJnSV7fWjtz2bjHWmtnVdX+JG9trf32sP7WJG9qrd11zOPuzuS0uGzbtu2ivXv3TpXn6NGj2bp166r2ZZ7GkHMMGRM5Z23anLt27bqrtbZjDpHWbDPPJ2PImMg5a2PIuZKMPcwnG8HBh488ZXnb05NHv/zV5Qu3nzHnRPM3htfOeuhpv6edT9ZSfv5Okttba0vD8j/J5P0935JkZ2vtkeG0tgOttW+rqncP9z8wjL//yXEneo4dO3a0O++8c6o8Bw4cyM6dO1e1L/M0hpxjyJjIOWvT5qyq0fxjZbnNNp+MIWMi56yNIedKMvYwn2wES3v2P2X5jRc+kbcf3PKV5Qff9rJ5R5q7Mbx21kNP+z3tfLLq095aa/83yUNV9W3DqkuTfCbJviRXD+uuTnLjcH9fktcMV327JMmRkxUfAACAWdpy6iEn9fokv1pVpyf5bJLXZlKoPlRV1yT5XJJXDWNvTnJ5kkNJvjSMBQAAmIs1lZ/W2qeTHO/w0qXHGduSvG4tzwcAALBaa/07PwAAAKOg/AAAAF1QfgAAgC6s9YIHAABsQMde4hpw5AcAAOiE8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwAA6ILyAwAAdEH5AQAAuqD8AAAAXVB+AACALig/AABAF5QfAACgC8oPAADQBeUHAADogvIDANCxpT37s7Rn/6JjwFwoPwAAQBeUHwAAoAvKDwAA0AXlBwAA6ILyAwAAdEH5AQAAuqD8AAAAXVB+AACALig/AABAF5QfAACgC8oPAADQBeUHAADogvIDAAB0QfkBAAC6oPwAAABdUH4AAIAuKD8AAEAXlB8AAKALyg8AANAF5QcAAOjCmstPVZ1WVZ+qqpuG5edV1R1V9UBVfbCqTh/WP21YPjRsX1rrcwMAAExrFkd+fjzJfcuWfzbJO1pr5yd5LMk1w/prkjzWWvuWJO8YxgEAAMzFmspPVZ2b5GVJ3jMsV5IXJ/nwMOSGJK8Y7l8xLGfYfukwHgAAYN2t9cjPLyR5U5K/HZafneTx1toTw/LhJNuH+9uTPJQkw/Yjw3gAAIB1V6211X1i1fcluby19qNVtTPJv03y2iSfGE5tS1Wdl+Tm1tqFVXVvkpe01g4P2/4wycWttT895nF3J9mdJNu2bbto7969U+U5evRotm7duqp9macx5BxDxkTOWZs2565du+5qre2YQ6Q128zzyRgyJnLO2hhyriRjD/PJIh18+Mhx1297evLol792/YXbz1jnRIszhtfOeuhpv6edT7as4TlelOTlVXV5km9I8k2ZHAk6s6q2DEd3zk3y+WH84STnJTlcVVuSnJHki8c+aGvtuiTXJcmOHTvazp07pwpz4MCBTDt2kcaQcwwZEzlnbSw5V2IzzydjyJjIOWtjyDmGjKux2vlkkX5wz/7jrn/jhU/k7Qe/9p+AD7565zonWpzN+nN5Kr3u98ms+rS31tqbW2vnttaWklyZ5GOttVcnuS3JK4dhVye5cbi/b1jOsP1jbbWHnQAAAFZoPf7Oz7VJ3lBVhzJ5T8/1w/rrkzx7WP+GJHvW4bkBAACOay2nvX1Fa+1AkgPD/c8mufg4Y/4yyatm8XwAAAArtR5HfgAAADYc5QcAAOiC8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwAA6MKWRQcAAGB2lvbsX3QE2LAc+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwCALO3Z7zLZbHrKDwAA0AXlBwAA6ILyAwAAdEH5AQAAuqD8AAAAXVB+AACALig/AABAF5QfAACgC8oPAADQBeUHAADogvIDAAB0QfkBAAC6oPwAAABdUH4AAIAuKD8AAEAXlB8AAKALyg8AANAF5QcAAOiC8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAurLj9VdV5V3VZV91XVvVX148P6Z1XVLVX1wPDxrGF9VdU7q+pQVd1dVS+c1U4AAACcylqO/DyR5I2ttW9PckmS11XVBUn2JLm1tXZ+kluH5SR5aZLzh9vuJL+0hucGAABYkVWXn9baI6213xvu/3mS+5JsT3JFkhuGYTckecVw/4ok728Ttyc5s6rOWXVyAACAFZjJe36qainJdyS5I8m21tojyaQgJXnOMGx7koeWfdrhYR0AAMC6q9ba2h6gamuS/5XkZ1prH6mqx1trZy7b/lhr7ayq2p/kra213x7W35rkTa21u455vN2ZnBaXbdu2XbR3796pchw9ejRbt25d077MwxhyjiFjIuesTZtz165dd7XWdswh0ppt5vlkDBkTOWdtDDlXkrGH+WQRDj585KTbtz09efTLJ95+4fYzZpxo8cbw2lkPPe33tPPJlrU8SVV9fZLfSPKrrbWPDKsfrapzWmuPDKe1fWFYfzjJecs+/dwknz/2MVtr1yW5Lkl27NjRdu7cOVWWAwcOZNqxizSGnGPImMg5a2PJuRKbeT4ZQ8ZEzlkbQ84xZFyN1c4ni/CDe/afdPsbL3wibz944n8CPvjqnTNOtHib9efyVHrd75NZy9XeKsn1Se5rrf38sk37klw93L86yY3L1r9muOrbJUmOPHl6HAAAwHpby5GfFyX5gSQHq+rTw7p/l+RtST5UVdck+VySVw3bbk5yeZJDSb6U5LVreG4AAIAVWXX5Gd67UyfYfOlxxrckr1vt8wEAcHxLpzjVDZiYydXeAAAANjrlBwCAr1jas9+RJDYt5QcAAOiC8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwAA6ILyAwAAdEH5AQAAuqD8AAAAXVB+AACALig/AABAF5QfAACgC8oPAADQBeUHAADogvIDAAB0QfkBAAC6oPwAAABdUH4AAIAubFl0AAAAVmdpz/51f+wH3/aydXsOmDdHfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AXlBwAA6IKrvQEAjMx6XuUNNjNHfgAAgC4oPwAAQBeUHwAAoAvKDwAA0AUXPAAA4ISWX1zhwbe9bIFJYO0c+QEAALqg/AAAAF1QfgAAgC54zw8AwEj446awNo78AAAAXVB+AACYytKe/Y4+MWrKDwAAK6IEMVZzf89PVV2W5BeTnJbkPa21t807AwDAmGzUovFkLn//h7GY65GfqjotybuSvDTJBUmuqqoL5pkBAIDZciSIsZj3kZ+LkxxqrX02Sapqb5IrknxmzjkAANbVLI6KKBQwW/MuP9uTPLRs+XCS75xzBgCADW2spedUuZ8sgicrhvM8lc5pe/2p1tr8nqzqVUle0lr7oWH5B5Jc3Fp7/bIxu5PsHha/Lcn9Uz782Un+ZIZx18sYco4hYyLnrE2b87mttW9e7zCzsMnnkzFkTOSctTHkXEnGHuaTjWgMP0ez1uM+J33t91TzybzLz3cl+cnW2kuG5TcnSWvtrTN47DtbazvW+jjrbQw5x5AxkXPWxpJzHsbwtRhDxkTOWRtDzjFk7F2P36Me9znpd79PZt6Xuv5kkvOr6nlVdXqSK5Psm3MGAACgQ3N9z09r7Ymq+rEkH83kUtfvba3dO88MAABAn+b+d35aazcnuXkdHvq6dXjM9TCGnGPImMg5a2PJOQ9j+FqMIWMi56yNIecYMvaux+9Rj/uc9LvfJzTX9/wAAAAsyrzf8wMAALAQoys/VXVZVd1fVYeqas9xtj+tqj44bL+jqpY2YMY3VNVnquruqrq1qp4774zT5Fw27pVV1apqIVcLmSZnVf2z4Wt6b1X92rwzDhlO9X3/u1V1W1V9avjeX76AjO+tqi9U1T0n2F5V9c5hH+6uqhfOO+O8jGEuGXKYT2ZoDPPJGOaSIYf5ZGSmfZ2OXVWdN7xG7htexz8+rH9WVd1SVQ8MH89adNZZq6rThrnhpmH5ecPvsAeG32mnLzrjwrXWRnPL5CIJf5jk+UlOT/K/k1xwzJgfTfLLw/0rk3xwA2bcleQZw/0fmXfGaXMO474xyceT3J5kx0bMmeT8JJ9Kctaw/JwNmvO6JD8y3L8gyYMLyPndSV6Y5J4TbL88yW8lqSSXJLlj3hk30PdroXPJCnKaT2b79VzofDKWuWR4bvPJiG7Tvk43wy3JOUleONz/xiR/MLxW/mOSPcP6PUl+dtFZ12Hf35Dk15LcNCx/KMmVw/1ffnLu6Pk2tiM/Fyc51Fr7bGvtr5PsTXLFMWOuSHLDcP/DSS6tqtpIGVtrt7XWvjQs3p7k3Dnme9I0X8sk+elMJou/nGe4ZabJ+S+TvKu19liStNa+MOeMyXQ5W5JvGu6fkeTzc8w3CdDax5N88SRDrkjy/jZxe5Izq+qc+aSbqzHMJYn5ZNbGMJ+MYi5JzCcjNO3rdPRaa4+01n5vuP/nSe5Lsj1PnddvSPKKxSRcH1V1bpKXJXnPsFxJXpzJ77BkE+7zaoyt/GxP8tCy5cPDuuOOaa09keRIkmfPJd0xzz84Xsblrsnkf8bm7ZQ5q+o7kpzXWrtpnsGOMc3X81uTfGtV/U5V3V5Vl80t3VdNk/Mnk3x/VR3O5IqHr59PtBVZ6c/vWI1hLnlKhoH5ZG3GMJ9slrkk6Wc+GYsuvx/DKcvfkeSOJNtaa48kk4KU5DmLS7YufiHJm5L87bD87CSPD7/Dkk6+56cytvJzvP91PfZyddOMWU9TP39VfX+SHUl+bl0THd9Jc1bV1yV5R5I3zi3R8U3z9dySyakqO5NcleQ9VXXmOuc61jQ5r0ryvtbauZmcDvJfh6/zRrLo18+8jGEuWVEG88lUxjCfbJa5JNkYryG+qrvvR1VtTfIbSf51a+3PFp1nPVXV9yX5QmvtruWrjzN0U3/Pp7ERJ8uTOZzkvGXL5+ZrD/d/ZUxVbcnklICTHZaftWkypqq+J8lPJHl5a+2v5pRtuVPl/MYkL0hyoKoezOR87X0LeJPytN/zG1trf9Na+6Mk92fyj5d5mibnNZmce5vW2ieSfEOSs+eSbnpT/fxuAmOYS56SYWA+WZsxzCebZS5J+plPxqKr70dVfX0mxedXW2sfGVY/+uSpl8PHRZwmv15elOTlwxy7N5PT3X4hk9NNn/y7npv6ez6tsZWfTyY5f7hyxemZvAl53zFj9iW5erj/yiQfa63Ns+WeMuNw+se7M/mHyqJeeCfN2Vo70lo7u7W21FpbyuS9BC9vrd25kXIOfjOTN32nqs7O5LSVz8415XQ5P5fk0iSpqm/P5B8sfzzXlKe2L8lrhqs0XZLkyJOnCGwyY5hLEvPJXHMOFj2fbJa5JOlnPhmLaX62NoXhvS7XJ7mvtfbzyzYtn9evTnLjvLOtl9bam1tr5w5z7JWZ/M56dZLbMvkdlmyyfV61RV9xYaW3TA7x/0EmVyz5iWHdT2XyizSZ/BL49SSHkvxukudvwIz/M8mjST493PZtxK/lMWMPZAFXZ5ry61lJfj7JZ5IczHBVkw2Y84Ikv5PJFXY+neR7F5DxA0keSfI3mfwv4DVJfjjJDy/7Wr5r2IeDi/qeb5Dv18Lnkilzmk9m+/Vc+HwyhrlkyGE+GdnteD9bm/GW5B9ncnrX3cvmxsszeQ/MrUkeGD4+a9FZ12n/d+arV3t7/vA77NDwO+1pi8636FsNXxgAAIBNbWynvQEAAKyK8gMAAHRB+QEAALqg/AAAAF1QfgAAgC4oPwAAQBeUHwAAoAvKDwAA0IX/D1pXP9YKfVAoAAAAAElFTkSuQmCC\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": 45,
   "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
}