dev.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request\r\n",
    "import pandas as pd\r\n",
    "import zipfile\r\n",
    "import requests\r\n",
    "import plotly.graph_objects as go\r\n",
    "import folium \r\n",
    "from branca.element import Template, MacroElement\r\n",
    "from bs4 import BeautifulSoup\r\n",
    "from datetime import datetime\r\n",
    "from dateutil.relativedelta import relativedelta\r\n",
    "\r\n",
    "from geopy.geocoders import Nominatim\r\n",
    "from geopy.extra.rate_limiter import RateLimiter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://files.digital.nhs.uk/assets/ods/current/epraccur.zip'\r\n",
    "filehandle, _ = urllib.request.urlretrieve(url)\r\n",
    "zip_file_object = zipfile.ZipFile(filehandle, 'r')\r\n",
    "first_file = zip_file_object.namelist()[0]\r\n",
    "file = zip_file_object.open(first_file)\r\n",
    "content = file.read()\r\n",
    "csv_file = open('assets/data/epraccur_data.csv', 'wb')\r\n",
    "csv_file.write(content)\r\n",
    "csv_file.close()\r\n",
    "header_list = [\"Organisation Code\", \"Name\", \"National Grouping\", \"High Level Health Geography\", \"Address line 1\", \"Address line 2\", \"Address line 3\", \r\n",
    "\"Address line 4\", \"Address line 5\",\"Postcode\",\"Open Date\",\"Close Date\",\"Status Code\",\"Organisation Sub-Type Code\",\"Commissioner\",\"Join Provider/Purchaser Date\",\r\n",
    "\"Left Provider/Purchaser Date\",\"Contact Telephone Number\", \"Null 1\", \"Null 2\", \"Null 3\", \"Amended Record Indicator\", \"Null 4\", \"Provider/Purchaser\",\r\n",
    "\"Null 5\", \"Prescribing Setting\", \"Null 6\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gp_practice_df = pd.read_csv('assets/data/epraccur_data.csv', names=header_list)\r\n",
    "gp_practice_df.fillna('', inplace=True)\r\n",
    "gp_practice_df['Partial Address'] = gp_practice_df[['Address line 1', 'Address line 2', 'Address line 3', 'Address line 4',]].agg(', '.join, axis=1)\r\n",
    "gp_practice_df['Full Address'] = gp_practice_df[['Partial Address', 'Address line 5',]].agg(' '.join, axis=1)\r\n",
    "gp_practice_df['Full Address'] = gp_practice_df['Full Address'].str.title()\r\n",
    "gp_practice_df['Name'] = gp_practice_df['Name'].str.title()\r\n",
    "gp_practice_df_1 = gp_practice_df.drop(columns = {\"High Level Health Geography\", \"Address line 1\", \"Address line 2\", \"Address line 3\", \"Address line 4\", \r\n",
    "\"Address line 5\", \"Open Date\", \"Close Date\", \"Organisation Sub-Type Code\", \"Commissioner\", \"Join Provider/Purchaser Date\", \"Left Provider/Purchaser Date\",\r\n",
    "\"Null 1\", \"Null 2\", \"Null 3\", \"Amended Record Indicator\", \"Null 4\", \"Partial Address\", \"Provider/Purchaser\", \"Null 5\", \"Null 6\"})\r\n",
    "gp_practice_df_2 =  gp_practice_df_1[gp_practice_df_1[\"Status Code\"] == \"A\"]\r\n",
    "gp_practice_df_3 =  gp_practice_df_2[gp_practice_df_2[\"Prescribing Setting\"] == 4]\r\n",
    "gp_practice_df_eng = gp_practice_df_3[gp_practice_df_3[\"National Grouping\"].str.contains(\"YAC|YAD|YAE|YAF|W00\")==False]\r\n",
    "gp_practice_df_eng_1 = gp_practice_df_eng.reset_index(drop = True)\r\n",
    "gp_practice_df_eng_2 = gp_practice_df_eng_1.copy()\r\n",
    "gp_practice_df_eng_3 = gp_practice_df_eng_2.drop( columns = {\"Status Code\", \"Prescribing Setting\"})\r\n",
    "gp_practice_df_ldn = gp_practice_df_eng_3[gp_practice_df_eng_3[\"National Grouping\"].str.contains(\"Y56\")==True]\r\n",
    "gp_practice_df_ldn['Name'] = gp_practice_df_ldn['Name'].str.replace('Gp', 'GP')\r\n",
    "gp_practice_df_ldn['Full Address'] = gp_practice_df_ldn['Full Address'].str.replace(' ,', ' ').str.replace('  ', ' ').str.replace('Gp', 'GP').map(lambda x: x.rstrip(', '))\r\n",
    "gp_practice_df_ldn_2  = gp_practice_df_ldn[gp_practice_df_ldn[\"Organisation Code\"].str.contains(\"E85124|Y06487\")==False]\r\n",
    "gp_practice_df_ldn_3 = gp_practice_df_ldn_2.reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "month_year_variable = datetime.now().strftime('%B-%Y').lower()\r\n",
    "url = \"https://digital.nhs.uk/data-and-information/publications/statistical/patients-registered-at-a-gp-practice/%s\" %month_year_variable \r\n",
    "response = urllib.request.urlopen(url)\r\n",
    "soup = BeautifulSoup(response.read(), \"lxml\")\r\n",
    "data = soup.select_one(\"a[href*='gp-reg-pat-prac-all.csv']\")\r\n",
    "if data != None: \r\n",
    "    csv_url = data['href']\r\n",
    "    req = requests.get(csv_url)\r\n",
    "    url_content = req.content\r\n",
    "    csv_file = open('assets/data/gp_pop_data.csv', 'wb')\r\n",
    "    csv_file.write(url_content)\r\n",
    "    csv_file.close()\r\n",
    "else:\r\n",
    "    last_month = datetime.now() - relativedelta(months=1)\r\n",
    "    last_month_year_variable = last_month.strftime('%B-%Y').lower()\r\n",
    "    url = \"https://digital.nhs.uk/data-and-information/publications/statistical/patients-registered-at-a-gp-practice/%s\" %last_month_year_variable\r\n",
    "    response = urllib.request.urlopen(url)\r\n",
    "    soup = BeautifulSoup(response.read(), \"lxml\")\r\n",
    "    data = soup.select_one(\"a[href*='gp-reg-pat-prac-all.csv']\")\r\n",
    "    csv_url = data['href']\r\n",
    "    req = requests.get(csv_url)\r\n",
    "    url_content = req.content\r\n",
    "    csv_file = open('assets/data/gp_pop_data.csv', 'wb')\r\n",
    "    csv_file.write(url_content)\r\n",
    "    csv_file.close()\r\n",
    "gp_pop_df = pd.read_csv('assets/data/gp_pop_data.csv')\r\n",
    "gp_pop_df.rename(columns={'CODE': 'Organisation Code', 'NUMBER_OF_PATIENTS': 'Number of patients registered at GP practices in England'}, inplace=True)\r\n",
    "gp_pop_df_1 = gp_pop_df.drop(columns = {'PUBLICATION', 'EXTRACT_DATE', 'TYPE', 'CCG_CODE', 'ONS_CCG_CODE', 'SEX', 'AGE', 'POSTCODE'})\r\n",
    "gp_pop_df_1 = gp_pop_df_1[gp_pop_df_1 [\"Organisation Code\"].str.contains(\"E85124|Y06487\")==False]\r\n",
    "gp_pop_df_1 = gp_pop_df_1.reset_index(drop = True)\r\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gp_pop_ldn = gp_practice_df_ldn_3.join(gp_pop_df_1, rsuffix='Organisation Code')\r\n",
    "gp_pop_ldn.rename(columns={'Number of patients registered at GP practices in England': 'Number of patients registered at the GP practice'}, inplace=True)\r\n",
    "gp_pop_ldn[\"Address\"] = gp_pop_ldn[[\"Full Address\", \"Postcode\"]].agg(', '.join, axis=1)\r\n",
    "gp_pop_ldn_1 = gp_pop_ldn.drop(columns={'Organisation CodeOrganisation Code', 'National Grouping', 'Full Address'})\r\n",
    "gp_pop_ldn_1 = gp_pop_ldn_1[[\"Organisation Code\", \"Name\", \"Address\", \"Postcode\", \"Contact Telephone Number\", \"Number of patients registered at the GP practice\"]]\r\n",
    "gp_pop_ldn_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x0 = gp_pop_ldn_1['Number of patients registered at the GP practice']\r\n",
    "x1 = gp_pop_df_1['Number of patients registered at GP practices in England']\r\n",
    "count_england = gp_pop_df_1['Number of patients registered at GP practices in England'].count()\r\n",
    "count_london = gp_pop_ldn_1['Number of patients registered at the GP practice'].count()\r\n",
    "fig_1 = go.Figure()\r\n",
    "fig_1.add_trace(go.Box(x=x0, \r\n",
    "boxmean=True,  \r\n",
    "boxpoints= 'all', \r\n",
    "jitter=0.3, \r\n",
    "name=\"London\", \r\n",
    "marker_color =\"#0072CE\", \r\n",
    "whiskerwidth=0.5, \r\n",
    "marker_size=3,\r\n",
    "line_width=2))\r\n",
    "fig_1.add_trace(go.Box(x=x1, \r\n",
    "boxmean=True, \r\n",
    "boxpoints= 'all', \r\n",
    "jitter=0.3, \r\n",
    "name=\"England\", \r\n",
    "marker_color = \"#003087\", \r\n",
    "whiskerwidth=0.5, \r\n",
    "marker_size=3,\r\n",
    "line_width=2))\r\n",
    "fig_1.update_layout(\r\n",
    "    {\"plot_bgcolor\": \"rgba(0, 0, 0, 0)\", \"paper_bgcolor\": \"rgba(0, 0, 0, 0)\"},\r\n",
    "    font = dict(family = \"Arial\", size = 16),\r\n",
    "    autosize=True,\r\n",
    "    margin=dict(l=75, r=50, b=160, t=30, pad=4, autoexpand=True), hoverlabel=dict(\r\n",
    "        font_size=12,\r\n",
    "        font_family=\"Arial\"\r\n",
    "    ), xaxis=dict(title='Number of patients registered at individual GP practices', zeroline=False))\r\n",
    "fig_1.add_annotation(dict(font=dict(family = \"Arial\",size=15),\r\n",
    "                                        x=0.33,\r\n",
    "                                        y=-0.40,\r\n",
    "                                        showarrow=False,\r\n",
    "                                        text=\"Number of GP practices in England: %s\" %count_england,\r\n",
    "                                        textangle=0,\r\n",
    "                                        xanchor='right',\r\n",
    "                                        xref=\"paper\",\r\n",
    "                                        yref=\"paper\"))\r\n",
    "fig_1.add_annotation(dict(font=dict(family = \"Arial\",size=15),\r\n",
    "                                        x=0.322,\r\n",
    "                                        y=-0.46,\r\n",
    "                                        showarrow=False,\r\n",
    "                                        text=\"Number of GP practices in London: %s\" %count_london,\r\n",
    "                                        textangle=0,\r\n",
    "                                        xanchor='right',\r\n",
    "                                        xref=\"paper\",\r\n",
    "                                        yref=\"paper\"))       "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_name = 'assets/data/gp_pop_ldn_mapped.csv'\n",
    "old_data = pd.read_csv(file_name, index_col=0)\n",
    "gp_pop_ldn_1 = gp_pop_ldn_1.merge(old_data[['Organisation Code','loc', 'Point', 'Latitude', 'Longitude', 'Altitude']],on='Organisation Code', how = 'left')\n",
    "gp_pop_ldn_1.rename(columns={'loc_x': 'loc', 'Point_x': 'Point', 'Latitude_x': 'Latitude', 'Longitude_x': 'Longitude', 'Altitude_x': 'Altitude' }, inplace=True)\n",
    "gp_pop_ldn_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "geolocator = Nominatim(user_agent=\"open_access_nhs\")\n",
    "geocode = RateLimiter(geolocator.geocode, min_delay_seconds=1)\n",
    "\n",
    "if gp_pop_ldn_1['loc'].count() != gp_pop_ldn_1['Organisation Code'].count():\n",
    "    missing_data = pd.isnull(gp_pop_ldn_1[\"loc\"])\n",
    "    missing_data_df = gp_pop_ldn_1[missing_data]\n",
    "    missing_data_df[\"loc\"] = missing_data_df[\"Postcode\"].apply(geolocator.geocode)\n",
    "    missing_data_df[\"Point\"]= missing_data_df[\"loc\"].apply(lambda loc: tuple(loc.point) if loc else None)\n",
    "    missing_data_df[['Latitude', 'Longitude', 'Altitude']] = pd.DataFrame(missing_data_df['Point'].to_list(), index=missing_data_df.index)\n",
    "    gp_pop_ldn_1 = gp_pop_ldn_1.dropna()\n",
    "    gp_pop_ldn_1 = pd.concat([gp_pop_ldn_1, missing_data_df], ignore_index=True)\n",
    "gp_pop_ldn_1.to_csv(file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gp_prac_pop_df_1 = pd.read_csv(file_name, index_col=0)\n",
    "gp_prac_pop_df_1['GP Patient Number Quintile'] = pd.qcut(gp_prac_pop_df_1['Number of patients registered at the GP practice'], 5, labels=False)\n",
    "gp_prac_pop_df_1['GP Patient Number Quintile'] = gp_prac_pop_df_1['GP Patient Number Quintile']  + 1\n",
    "gp_prac_pop_df_1.sort_values('GP Patient Number Quintile')\n",
    "colordict = {1: 'green', 2: 'lightgreen', 3: 'cadetblue', 4: 'blue', 5: 'darkblue'}\n",
    "frame = folium.Figure(width=900, height=500)\n",
    "fig_2 = folium.Map(\n",
    "    location=[51.5, -0.1],\n",
    "    tiles=\"cartodbpositron\",\n",
    "    zoom_start=10.2).add_to(frame)\n",
    "for lat, lon, name, code, address, population, number, pop_qin in zip(gp_prac_pop_df_1['Latitude'], \n",
    "gp_prac_pop_df_1['Longitude'], \n",
    "gp_prac_pop_df_1['Name'],\n",
    "gp_prac_pop_df_1['Organisation Code'], \n",
    "gp_prac_pop_df_1['Address'], \n",
    "gp_prac_pop_df_1['Number of patients registered at the GP practice'],\n",
    "gp_prac_pop_df_1['Contact Telephone Number'], \n",
    "gp_prac_pop_df_1['GP Patient Number Quintile']):\n",
    "    folium.CircleMarker(\n",
    "        [lat, lon],\n",
    "        radius=0.065*((population/2)**(1./2.)+35),\n",
    "        popup = folium.Popup('<b>' + 'Name: ' + '</b>'  + str(name) + '<br>'\n",
    "        '<b>' + 'GP Practice Code: ' + '</b>' + str(code) + '<br>'\n",
    "        '<b>' + 'Address: ' + '</b>' + str(address) + '<br>'\n",
    "        '<b>' + 'Telephone Number: ' + '</b>' + str(number) + '<br>'\n",
    "        '<b>' + 'Number of Patients Registered: ' + '</b>' + str(population) + '<br>', max_width=len(address)*20),\n",
    "        color='b',\n",
    "        key_on = pop_qin,\n",
    "        threshold_scale=[0,1,2,3,4,5],\n",
    "        fill_color=colordict[pop_qin],\n",
    "        fill=True,\n",
    "        fill_opacity=0.9\n",
    "        ).add_to(fig_2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "template = \"\"\"\n",
    "{% macro html(this, kwargs) %}\n",
    "\n",
    "<!doctype html>\n",
    "<html lang=\"en\">\n",
    "<div id='maplegend' class='maplegend' \n",
    "    style='position: absolute; z-index:9999; border:2px solid grey; background-color:rgba(255, 255, 255, 0.8);\n",
    "     border-radius:6px; padding: 10px; font-size:14px; right: 20px; top: 20px;'>\n",
    "     \n",
    "<div class='legend-title'>GP Patient Number Quintile</div>\n",
    "<div class='legend-scale'>\n",
    "  <ul class='legend-labels'>\n",
    "    <li><span style='width:8px;height:8px;background:green;opacity:0.8;'></span><p>&nbsp;&nbsp;</p>\n",
    "1</li>\n",
    "    <li><span style='width:12px;height:12px;background:lightgreen;opacity:0.8;'></span><p>&nbsp;&nbsp;</p>\n",
    "2</li>\n",
    "    <li><span style='width:16px;height:16px;background:cadetblue;opacity:0.8;'></span><p>&nbsp;&nbsp;</p>\n",
    "3</li>\n",
    "    <li><span style='width:20px;height:20px;background:blue;opacity:0.8;'></span><p>&nbsp;&nbsp;</p>\n",
    "4</li>\n",
    "    <li><span style='width:24px;height:24px;background:darkblue;opacity:0.8;'></span><p>&nbsp;&nbsp;</p>\n",
    "5</li>\n",
    "  </ul>\n",
    "  <!-- <ul class='legend-labels-2'>\n",
    "    <li><span style='background:lightgreen;opacity:0.8;'></span></ul><ul class='legend-text'>2</li>\n",
    "  </ul>\n",
    "  <ul class='legend-labels-3'>\n",
    "    <li><span style='background:orange;opacity:0.8;'></span></ul><ul class='legend-text'>3</li>\n",
    "  </ul>\n",
    "  <ul class='legend-labels-4'>\n",
    "    <li><span style='background:red;opacity:0.8;'></span></ul><ul class='legend-text'>4</li>\n",
    "  </ul>\n",
    "  <ul class='legend-labels-5'>\n",
    "    <li><span style='background:darkred;opacity:0.8;'></span></ul><ul class='legend-text'>5</li>\n",
    "  </ul> -->\n",
    "</div>\n",
    "<b><div class='legend-source'>Note: </b>Circle radius is relative to the number <br> of patients registered at a GP practice</br></div>\n",
    "</div>\n",
    " \n",
    "</body>\n",
    "</html>\n",
    "\n",
    "<style type='text/css'>\n",
    "  .maplegend .legend-title {\n",
    "    text-align: left;\n",
    "    margin-bottom: 5px;\n",
    "    font-weight: bold;\n",
    "    font-size: 85%;\n",
    "    }\n",
    "  .maplegend .legend-text {\n",
    "    text-align: left;\n",
    "    margin-bottom: 5px;\n",
    "    font-size: 85%;\n",
    "    }\n",
    "  .maplegend .legend-scale ul {\n",
    "    margin: 0;\n",
    "    margin-bottom: 5px;\n",
    "    padding: 0;\n",
    "    float: left;\n",
    "    list-style: none;\n",
    "    }\n",
    "  .maplegend .legend-scale ul li {\n",
    "    font-size: 80%;\n",
    "    list-style: none;\n",
    "    margin-left: 0;\n",
    "    line-height: 18px;\n",
    "    margin-bottom: 2px;\n",
    "    }\n",
    "  .maplegend ul.legend-labels {\n",
    "    display: flex;\n",
    "    justify-content: space-between;\n",
    "    width: 100%;\n",
    "  }\n",
    "  .maplegend ul.legend-labels li {\n",
    "    display: flex;\n",
    "    align-items: center;\n",
    "  }\n",
    "  .maplegend ul.legend-labels li span {\n",
    "    height: 24px;\n",
    "    width: 24px;\n",
    "    border-radius: 5em;\n",
    "  }\n",
    "\n",
    "  .maplegend .legend-source {\n",
    "    font-size: 80%;\n",
    "    color: #777;\n",
    "    clear: both;\n",
    "    }\n",
    "  .maplegend a {\n",
    "    color: #777;\n",
    "    }\n",
    "</style>\n",
    "{% endmacro %}\"\"\"\n",
    "macro = MacroElement()\n",
    "macro._template = Template(template)\n",
    "fig_2.get_root().add_child(macro)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gp_prac_pop_df_tosave = gp_prac_pop_df_1.drop(columns ={'Postcode', 'loc', 'Point', 'Altitude'})\n",
    "gp_prac_pop_df_tosave = gp_prac_pop_df_tosave.reset_index(drop = True)\n",
    "gp_prac_pop_df_tosave.index.name = 'Unique ID'\n",
    "gp_prac_pop_df_tosave.to_csv(\"assets/data/gp_pop_london_mapped_final.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "292c795e24700ffeb49481d87674b4a2462b1f5926d60159546f48d450d30bc4"
  },
  "kernelspec": {
   "display_name": "Python 3.9.1 64-bit",
   "name": "python391jvsc74a57bd0aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "language_info": {
   "name": "python",
   "version": ""
  },
  "metadata": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  },
  "orig_nbformat": 2
 },
 "nbformat": 4,
 "nbformat_minor": 2
}