EV_chargepoints_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b203b329",
   "metadata": {},
   "source": [
    "## Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27015ea1",
   "metadata": {},
   "source": [
    "This analysis uses public EV chargepoint and plug-in vehicle (PiV) registration data to understand, by local authority district (LAD), which areas have the best and worst EV infrastructure.\n",
    "\n",
    "### What data is included?\n",
    "\n",
    "First, four features are calculated to allow for initial exploration of the data. Details of these are as follows:\n",
    "\n",
    "**Count of PiVs**\n",
    "\n",
    "*Source: DVLA and DfT dataset*\n",
    "\n",
    "* A simple count of registered PiVs by LAD is calculated.\n",
    "* This includes all PiVs with `Private` keepership. This excludes PiVs where the keepership is `Company` to avoid skewing the analysis with large quantities of fleet vehicles registered to a single address.\n",
    "* Where data has been suppressed in the raw file, the `[c]` value has been replaced with `NaN`\n",
    "\n",
    "**Count of EV chargepoints**\n",
    "\n",
    "*Source: Public EV chargepoint registry*\n",
    "\n",
    "* A simple count of public EV chargepoints by LAD is calculated.\n",
    "* There has been no cleaning of this data beyond converting the `latitude` and `longitude` into a `point`. This is a public dataset and further accuracy has not been verified.\n",
    "\n",
    "**Ratio of PiVs to EV chargepoints**\n",
    "\n",
    "*Source: Author's calculations*\n",
    "\n",
    "* Taking the count of PiVs in each LAD and dividing this by the count of public EV chargepoints in each LAD.\n",
    "* A higher ratio means there are more PiVs to each chargepoint and therefore public infrastructure may be lacking.\n",
    "\n",
    "**Average distance to nearest EV chargepoint**\n",
    "\n",
    "*Source: Geographies from ONS via Open Geography Portal*\n",
    "\n",
    "* Using the population-weighted centroids (PWCs) for each output area (OA) within an LAD, the nearest public EV chargepoint is calculated. OA is the smallest census geography and contains between 40 and 250 households.\n",
    "* Then this is aggregated to LAD level by taking the average distance of all OAs within the LAD to obtain the average distance to the nearest public EV chargepoint by LAD.\n",
    "\n",
    "\n",
    "### What do these features show?\n",
    "\n",
    "The following choropleths map these four features. The absolute number of PiVs varies across England and Wales, with LADs across Wales, Lincolnshire and Cumbria having particularly low numbers. By contrast, the highest number of public EV chargepoints by LAD is concentrated in Greater London, with hotspots dotted elsewhere across the country. If we instead look at this as the ratio of PiVs to chargepoints, we again see the patterns change. Hotspots around the southeast of England highlight that there is potentially infrastructure lacking in these areas compared to PiV numbers. Lastly, we can see the average distance to the nearest chargepoint is perhaps unsurprisingly highest in rural areas, particularly in the north of England and Wales.\n",
    "\n",
    "![](Outputs/Feature_choropleths.png)\n",
    "\n",
    "*Code to create an interactive version of this map can be found below.*\n",
    "\n",
    "\n",
    "### What is the relationship between the features?\n",
    "\n",
    "The correlation matrix heatmap below shows the relationship between each feature. There is some positive correlation between chargepoints and PiVs, which is good as this suggests that where there are more PiVs there tend to be more chargepoints. There is also some positive correlation between average distance to the nearest chargepoint and the ratio of PiVs to chargepoints. This means that if the distance is greater, there are also more PiVs to chargepoints perhaps suggesting these areas lack infrastructure.\n",
    "\n",
    "<img src=\"Outputs/CorrelationMatrix.png\" alt=\"\" width=\"800\">\n",
    "\n",
    "The following scatter plots illustrate this further, adding a regression line. We can see that there are outliers across both, with a cluster of LADs on the lower end.\n",
    "\n",
    "![](Outputs/Scatter_reg_plots.png)\n",
    "\n",
    "\n",
    "### So what does this mean for public EV infrastructure?\n",
    "\n",
    "Taking the ratio of PiVs to public EV chargepoints and the average distance to the nearest chargepoint, a simple ranking is created combining the two. Areas with a high ratio of PiVs to chargepoints and a large distance to the nearest chargepoint could be lacking in public EV infrastructure.\n",
    "\n",
    "**Top 10**\n",
    "The best performing LADs were all in London, suggesting infrastructure in the capital is well established.\n",
    "\n",
    "**Bottom 10**\n",
    "The worst performing LADs are more dispersed. Most are more rural areas, or include only one or two larger towns.\n",
    "\n",
    "<div style=\"display: flex; gap: 0px;\">\n",
    "    <img src=\"Outputs/Map_of_Top_10.png\" alt=\"Top 10\" width=\"450\" style=\"margin: 0; padding: 0;\">\n",
    "    <img src=\"Outputs/Map_of_Bottom_10.png\" alt=\"Bottom 10\" width=\"500\" style=\"margin: 0; padding: 0;\">\n",
    "</div>\n",
    "\n",
    "This information could be used by both public and private organisations alike to understand where public EV infrastructure is lacking and therefore incentivise installation of new infrastructure.\n",
    "\n",
    "\n",
    "### Considerations and future work\n",
    "\n",
    "This analysis has demonstrated that current public EV structure may be lacking in some areas based on the current number of PiVs. This does not include:\n",
    "\n",
    "* A temporal view of how PiV ownership has changed over time - there may be areas experiencing higher growth, with could arguably justify increased investment over other areas.\n",
    "* EV infrastructure on private property (e.g. at home) - areas with a higher number of terraced houses or flats may require more public infrastructure as it can often be more challenging to install at these types of properties. Areas with higher rates of rented accomodation over private ownership could also face challenges as it would be the responsibility of the landlord to install EV chargepoints. Again, these areas may require more public infrastructure and so overlaying this data could be an interesting investigation to see how it affects the ranking.\n",
    "\n",
    "The public EV chargepoint registry has also not been quality checked. Further investigation into this dataset and wrangling/cleaning as appropriate may impact results."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9692871",
   "metadata": {},
   "source": [
    "## Coding"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fdd3be6",
   "metadata": {},
   "source": [
    "## Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 369,
   "id": "1d77f3f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from shapely.geometry import Point\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "from scipy.stats import zscore\n",
    "import matplotlib.pyplot as plt\n",
    "import contextily as ctx\n",
    "import folium"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bf0fc49",
   "metadata": {},
   "source": [
    "## Data wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d0b2b8b",
   "metadata": {},
   "source": [
    "### EV Chargepoint data\n",
    "\n",
    "* **Public EV chargepoint registry**, available from: https://www.gov.uk/guidance/find-and-use-data-on-public-electric-vehicle-chargepoints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3c572392",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import csv\n",
    "chargepoints_RAW = pd.read_csv('Data/national-charge-point-registry_230524.csv', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e154cad2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reduce to just required columns\n",
    "chargepoints_TRIM = chargepoints_RAW[['chargeDeviceID','reference','name','latitude','longitude']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e03ab284",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "40580"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check length of df\n",
    "len(chargepoints_TRIM)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "1f59de07",
   "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>chargeDeviceID</th>\n",
       "      <th>reference</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b86a77a42bb68c81946ec50cfc95e89d</td>\n",
       "      <td>11172306P</td>\n",
       "      <td>Network Rail Westwood Centre 1</td>\n",
       "      <td>52.386590</td>\n",
       "      <td>-1.587384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>dc1c347d471f68e41ad2a9a1145941d6</td>\n",
       "      <td>APT-0296-0015/13P</td>\n",
       "      <td>Brindley Drive Car Park Birmingham - 70524</td>\n",
       "      <td>52.480918</td>\n",
       "      <td>-1.907710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7d545ad9367ccb8a80c94a953314ae71</td>\n",
       "      <td>CM123</td>\n",
       "      <td>Renault Liverpool</td>\n",
       "      <td>53.383579</td>\n",
       "      <td>-2.977230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>68c7fca1e3bba5e49ec90847dcdd456b</td>\n",
       "      <td>CM164</td>\n",
       "      <td>NCP Portman Square</td>\n",
       "      <td>51.516201</td>\n",
       "      <td>-0.157996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ac7a21c48f5833b33a5b606b2089e6a9</td>\n",
       "      <td>CM167</td>\n",
       "      <td>NCP Prince Street Car Park</td>\n",
       "      <td>51.450340</td>\n",
       "      <td>-2.596704</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     chargeDeviceID          reference  \\\n",
       "0  b86a77a42bb68c81946ec50cfc95e89d          11172306P   \n",
       "1  dc1c347d471f68e41ad2a9a1145941d6  APT-0296-0015/13P   \n",
       "2  7d545ad9367ccb8a80c94a953314ae71              CM123   \n",
       "3  68c7fca1e3bba5e49ec90847dcdd456b              CM164   \n",
       "4  ac7a21c48f5833b33a5b606b2089e6a9              CM167   \n",
       "\n",
       "                                         name   latitude  longitude  \n",
       "0              Network Rail Westwood Centre 1  52.386590  -1.587384  \n",
       "1  Brindley Drive Car Park Birmingham - 70524  52.480918  -1.907710  \n",
       "2                           Renault Liverpool  53.383579  -2.977230  \n",
       "3                          NCP Portman Square  51.516201  -0.157996  \n",
       "4                  NCP Prince Street Car Park  51.450340  -2.596704  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "chargepoints_TRIM.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "19546b46",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Geocode locations using lat and long, set crs to 4326 and then convert to 27700 (British National Grid), drop lat and long\n",
    "chargepoints_gdf = gpd.GeoDataFrame(chargepoints_TRIM,\n",
    "                                    geometry=gpd.points_from_xy(chargepoints_TRIM.longitude, chargepoints_TRIM.latitude)\n",
    "                                   ).set_crs(epsg=4326, inplace=True).to_crs(epsg=27700).drop(columns=['latitude','longitude'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "693522c3",
   "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>chargeDeviceID</th>\n",
       "      <th>reference</th>\n",
       "      <th>name</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b86a77a42bb68c81946ec50cfc95e89d</td>\n",
       "      <td>11172306P</td>\n",
       "      <td>Network Rail Westwood Centre 1</td>\n",
       "      <td>POINT (428179.411 276586.797)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>dc1c347d471f68e41ad2a9a1145941d6</td>\n",
       "      <td>APT-0296-0015/13P</td>\n",
       "      <td>Brindley Drive Car Park Birmingham - 70524</td>\n",
       "      <td>POINT (406364.849 287003.294)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7d545ad9367ccb8a80c94a953314ae71</td>\n",
       "      <td>CM123</td>\n",
       "      <td>Renault Liverpool</td>\n",
       "      <td>POINT (335096.815 387859.900)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>68c7fca1e3bba5e49ec90847dcdd456b</td>\n",
       "      <td>CM164</td>\n",
       "      <td>NCP Portman Square</td>\n",
       "      <td>POINT (527908.652 181305.630)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ac7a21c48f5833b33a5b606b2089e6a9</td>\n",
       "      <td>CM167</td>\n",
       "      <td>NCP Prince Street Car Park</td>\n",
       "      <td>POINT (358631.700 172541.813)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     chargeDeviceID          reference  \\\n",
       "0  b86a77a42bb68c81946ec50cfc95e89d          11172306P   \n",
       "1  dc1c347d471f68e41ad2a9a1145941d6  APT-0296-0015/13P   \n",
       "2  7d545ad9367ccb8a80c94a953314ae71              CM123   \n",
       "3  68c7fca1e3bba5e49ec90847dcdd456b              CM164   \n",
       "4  ac7a21c48f5833b33a5b606b2089e6a9              CM167   \n",
       "\n",
       "                                         name                       geometry  \n",
       "0              Network Rail Westwood Centre 1  POINT (428179.411 276586.797)  \n",
       "1  Brindley Drive Car Park Birmingham - 70524  POINT (406364.849 287003.294)  \n",
       "2                           Renault Liverpool  POINT (335096.815 387859.900)  \n",
       "3                          NCP Portman Square  POINT (527908.652 181305.630)  \n",
       "4                  NCP Prince Street Car Park  POINT (358631.700 172541.813)  "
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "chargepoints_gdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b990698f",
   "metadata": {},
   "source": [
    "### EV Cars data\n",
    "\n",
    "* **df_VEH0145**: Licensed plug-in vehicles (PiVs) at the end of the quarter by fuel type and lower super output area (LSOA): United Kingdom, available from: https://www.gov.uk/government/statistical-data-sets/vehicle-licensing-statistics-data-files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "f8676544",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import csv\n",
    "PiVs_RAW = pd.read_csv('Data/df_VEH0145.csv', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "b311d20f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reduce to just required columns\n",
    "PiVs_TRIM = PiVs_RAW[['LSOA11CD','LSOA11NM','Fuel','Keepership','2023 Q4']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "1aeeba42",
   "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>LSOA11CD</th>\n",
       "      <th>LSOA11NM</th>\n",
       "      <th>Fuel</th>\n",
       "      <th>Keepership</th>\n",
       "      <th>2023 Q4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>Aldergrove 1</td>\n",
       "      <td>Battery electric</td>\n",
       "      <td>Company</td>\n",
       "      <td>[c]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>95AA01S2</td>\n",
       "      <td>Aldergrove 2</td>\n",
       "      <td>Battery electric</td>\n",
       "      <td>Company</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>95AA01S3</td>\n",
       "      <td>Aldergrove 3</td>\n",
       "      <td>Battery electric</td>\n",
       "      <td>Company</td>\n",
       "      <td>[c]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>95AA02W1</td>\n",
       "      <td>Balloo</td>\n",
       "      <td>Battery electric</td>\n",
       "      <td>Company</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>95AA03W1</td>\n",
       "      <td>Ballycraigy</td>\n",
       "      <td>Battery electric</td>\n",
       "      <td>Company</td>\n",
       "      <td>[c]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   LSOA11CD      LSOA11NM              Fuel Keepership 2023 Q4\n",
       "0  95AA01S1  Aldergrove 1  Battery electric    Company     [c]\n",
       "1  95AA01S2  Aldergrove 2  Battery electric    Company       9\n",
       "2  95AA01S3  Aldergrove 3  Battery electric    Company     [c]\n",
       "3  95AA02W1        Balloo  Battery electric    Company       5\n",
       "4  95AA03W1   Ballycraigy  Battery electric    Company     [c]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "PiVs_TRIM.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "id": "7ce79a2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter to all Fuel and private Keepership types, then drop those columns\n",
    "PiVs_FILTERED = PiVs_TRIM[(PiVs_TRIM['Fuel'] == 'Total') & \n",
    "                          (PiVs_TRIM['Keepership'] == 'Private')].drop(columns=['Fuel', 'Keepership'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "id": "898dc3dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace the suppressed [c] data with NaN\n",
    "PiVs_FILTERED['2023 Q4'] = PiVs_FILTERED['2023 Q4'].replace('[c]', np.nan)\n",
    "\n",
    "# Convert the '2023 Q4' column to numeric data type\n",
    "PiVs_FILTERED['2023 Q4'] = pd.to_numeric(PiVs_FILTERED['2023 Q4'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "id": "e9e11aa4",
   "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>LSOA11CD</th>\n",
       "      <th>LSOA11NM</th>\n",
       "      <th>2023 Q4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>189862</th>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>Aldergrove 1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189863</th>\n",
       "      <td>95AA01S2</td>\n",
       "      <td>Aldergrove 2</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189864</th>\n",
       "      <td>95AA01S3</td>\n",
       "      <td>Aldergrove 3</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189865</th>\n",
       "      <td>95AA02W1</td>\n",
       "      <td>Balloo</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189866</th>\n",
       "      <td>95AA03W1</td>\n",
       "      <td>Ballycraigy</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        LSOA11CD      LSOA11NM  2023 Q4\n",
       "189862  95AA01S1  Aldergrove 1      NaN\n",
       "189863  95AA01S2  Aldergrove 2     15.0\n",
       "189864  95AA01S3  Aldergrove 3     19.0\n",
       "189865  95AA02W1        Balloo     10.0\n",
       "189866  95AA03W1   Ballycraigy      NaN"
      ]
     },
     "execution_count": 304,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "PiVs_FILTERED.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b15a88ab",
   "metadata": {},
   "source": [
    "#### LSOA lookup\n",
    "\n",
    "The PiV data uses the LSOA codes from 2011 and so this will need to be changed to the LSOA 2021 codes.\n",
    "\n",
    "* **LSOA best fit lookup 2011 to 2021**, available from: https://geoportal.statistics.gov.uk/datasets/b14d449ba10a48508bd05cd4a9775e2b_0/explore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "id": "0b848e11",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import csv\n",
    "LSOA_lookup = pd.read_csv(\n",
    "    'Data/LSOA_(2011)_to_LSOA_(2021)_to_Local_Authority_District_(2022)_Best_Fit_Lookup_for_EW_(V2).csv',\n",
    "    low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "id": "2b990016",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reduce to just required columns\n",
    "LSOA_lookup = LSOA_lookup[['LSOA11CD','LSOA21CD','LAD22CD','LAD22NM']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "id": "e0e8203d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Match 2021 to 2011 codes using lookup\n",
    "PiVs_FILTERED = pd.merge(PiVs_FILTERED, LSOA_lookup, on='LSOA11CD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "id": "553b0bbe",
   "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>LSOA11CD</th>\n",
       "      <th>LSOA11NM</th>\n",
       "      <th>2023 Q4</th>\n",
       "      <th>LSOA21CD</th>\n",
       "      <th>LAD22CD</th>\n",
       "      <th>LAD22NM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E01000001</td>\n",
       "      <td>City of London 001A</td>\n",
       "      <td>28.0</td>\n",
       "      <td>E01000001</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>City of London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E01000002</td>\n",
       "      <td>City of London 001B</td>\n",
       "      <td>30.0</td>\n",
       "      <td>E01000002</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>City of London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E01000003</td>\n",
       "      <td>City of London 001C</td>\n",
       "      <td>15.0</td>\n",
       "      <td>E01000003</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>City of London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E01000005</td>\n",
       "      <td>City of London 001E</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E01000005</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>City of London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E01000006</td>\n",
       "      <td>Barking and Dagenham 016A</td>\n",
       "      <td>18.0</td>\n",
       "      <td>E01000006</td>\n",
       "      <td>E09000002</td>\n",
       "      <td>Barking and Dagenham</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    LSOA11CD                   LSOA11NM  2023 Q4   LSOA21CD    LAD22CD  \\\n",
       "0  E01000001        City of London 001A     28.0  E01000001  E09000001   \n",
       "1  E01000002        City of London 001B     30.0  E01000002  E09000001   \n",
       "2  E01000003        City of London 001C     15.0  E01000003  E09000001   \n",
       "3  E01000005        City of London 001E      NaN  E01000005  E09000001   \n",
       "4  E01000006  Barking and Dagenham 016A     18.0  E01000006  E09000002   \n",
       "\n",
       "                LAD22NM  \n",
       "0        City of London  \n",
       "1        City of London  \n",
       "2        City of London  \n",
       "3        City of London  \n",
       "4  Barking and Dagenham  "
      ]
     },
     "execution_count": 308,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "PiVs_FILTERED.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9c479eb",
   "metadata": {},
   "source": [
    "### LADs\n",
    "\n",
    "* **LADs 2021 polygons**, available from: https://geoportal.statistics.gov.uk/datasets/305779d69bf44feea05eeaa78ca26b5f_0/explore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "id": "05175a86",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import shp file\n",
    "LADs = gpd.read_file('Data/Local_Authority_Districts_December_2022_UK_BFC_V2_-177113771882051469/LAD_DEC_2022_UK_BFC_V2.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "id": "0c02645b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reduce to just required columns\n",
    "LADs = LADs[['LAD22CD','LAD22NM','geometry']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "id": "e7e2a54c",
   "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>LAD22CD</th>\n",
       "      <th>LAD22NM</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>MULTIPOLYGON (((450154.599 525938.201, 450140....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>MULTIPOLYGON (((446854.700 517192.700, 446854....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000003</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>MULTIPOLYGON (((451747.397 520561.100, 451792....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000004</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>MULTIPOLYGON (((447177.704 517811.797, 447176....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>Darlington</td>\n",
       "      <td>POLYGON ((423496.602 524724.299, 423497.204 52...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     LAD22CD               LAD22NM  \\\n",
       "0  E06000001            Hartlepool   \n",
       "1  E06000002         Middlesbrough   \n",
       "2  E06000003  Redcar and Cleveland   \n",
       "3  E06000004      Stockton-on-Tees   \n",
       "4  E06000005            Darlington   \n",
       "\n",
       "                                            geometry  \n",
       "0  MULTIPOLYGON (((450154.599 525938.201, 450140....  \n",
       "1  MULTIPOLYGON (((446854.700 517192.700, 446854....  \n",
       "2  MULTIPOLYGON (((451747.397 520561.100, 451792....  \n",
       "3  MULTIPOLYGON (((447177.704 517811.797, 447176....  \n",
       "4  POLYGON ((423496.602 524724.299, 423497.204 52...  "
      ]
     },
     "execution_count": 312,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "LADs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "id": "618f9a35",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "374"
      ]
     },
     "execution_count": 319,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(LADs)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1397d6c",
   "metadata": {},
   "source": [
    "### OAs\n",
    "\n",
    "This analysis will use the population-weighted centroids (PWCs) to find the nearest EV chargepoints. This will then be aggregated to LAD level, so the OA to LAD lookup is required.\n",
    "\n",
    "* **OA 2021 PWCs**, available from: https://geoportal.statistics.gov.uk/datasets/b9b2b2440af240ce9d30a1d39a7507c2_0/explore\n",
    "* **OA to LAD lookup**, available from: https://geoportal.statistics.gov.uk/datasets/b9ca90c10aaa4b8d9791e9859a38ca67_0/explore"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5192b82",
   "metadata": {},
   "source": [
    "#### OA PWCs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "bf0ba69c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import shp file\n",
    "OA_PWC = gpd.read_file('Data/Output_Areas_2021_PWC_V3_-1981902074309169314/PopCentroids_EW_2021_V3.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "acb6a85d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reduce to just required columns\n",
    "OA_PWC = OA_PWC[['OA21CD','geometry']].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35af05e4",
   "metadata": {},
   "source": [
    "#### OA to LAD lookup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "id": "72a30dc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import csv\n",
    "OA_lookup = pd.read_csv(\n",
    "    'Data/Output_Area_to_Lower_layer_Super_Output_Area_to_Middle_layer_Super_Output_Area_to_Local_Authority_District_(December_2021)_Lookup_in_England_and_Wales_v3.csv',\n",
    "    low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "id": "4030390d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep only required columns\n",
    "OA_lookup = OA_lookup[['OA21CD','LSOA21CD','LAD22CD']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "id": "1364b0a7",
   "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>OA21CD</th>\n",
       "      <th>LSOA21CD</th>\n",
       "      <th>LAD22CD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E00060358</td>\n",
       "      <td>E01011968</td>\n",
       "      <td>E06000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E00060359</td>\n",
       "      <td>E01011968</td>\n",
       "      <td>E06000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E00060360</td>\n",
       "      <td>E01011968</td>\n",
       "      <td>E06000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E00060361</td>\n",
       "      <td>E01011968</td>\n",
       "      <td>E06000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E00060362</td>\n",
       "      <td>E01011970</td>\n",
       "      <td>E06000001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      OA21CD   LSOA21CD    LAD22CD\n",
       "0  E00060358  E01011968  E06000001\n",
       "1  E00060359  E01011968  E06000001\n",
       "2  E00060360  E01011968  E06000001\n",
       "3  E00060361  E01011968  E06000001\n",
       "4  E00060362  E01011970  E06000001"
      ]
     },
     "execution_count": 314,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "OA_lookup.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f470a70",
   "metadata": {},
   "source": [
    "## Feature engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5906d6ae",
   "metadata": {},
   "source": [
    "### Aggregate EV car ownership data to LAD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "id": "159889e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate to LAD and calculate total number of PiVs\n",
    "PiVs_LAD = PiVs_FILTERED.groupby('LAD22CD')['2023 Q4'].agg('sum').reset_index()\n",
    "\n",
    "# Rename column to PiVs\n",
    "PiVs_LAD.rename(columns={'2023 Q4': 'PiVs'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "id": "4c0f0c4d",
   "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>LAD22CD</th>\n",
       "      <th>PiVs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>410.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>391.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000003</td>\n",
       "      <td>580.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000004</td>\n",
       "      <td>1308.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>719.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     LAD22CD    PiVs\n",
       "0  E06000001   410.0\n",
       "1  E06000002   391.0\n",
       "2  E06000003   580.0\n",
       "3  E06000004  1308.0\n",
       "4  E06000005   719.0"
      ]
     },
     "execution_count": 317,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "PiVs_LAD.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "id": "3d093008",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "331"
      ]
     },
     "execution_count": 318,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(PiVs_LAD)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c02abd2",
   "metadata": {},
   "source": [
    "### Calculate distance to nearest chargepoint\n",
    "\n",
    "* Using the OA PWCs, calculate the nearest EV chargepoint.\n",
    "* Aggregate this to LAD level to get the average distance to the nearest chargepoint by LAD."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "id": "749bc2e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the nearest chargepoint to each OA PWC and calculate distance in metres\n",
    "OA_nearest_chargepoint = OA_PWC.sjoin_nearest(chargepoints_gdf, distance_col='distance', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "id": "e9eda942",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge on OA_lookup_Leeds to get the LAD22CDs\n",
    "OA_nearest_chargepoint = pd.merge(OA_nearest_chargepoint, OA_lookup, on='OA21CD', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "id": "492c8309",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep only required columns\n",
    "OA_nearest_chargepoint = OA_nearest_chargepoint[['OA21CD','geometry','distance','LAD22CD']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "id": "cad3f0a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate to LAD and calculate average distance to nearest chargepoint\n",
    "avg_dist_nearest_chargepoint_LAD = OA_nearest_chargepoint.groupby('LAD22CD')['distance'].agg('mean').reset_index()\n",
    "\n",
    "# Rename column to avg_distance\n",
    "avg_dist_nearest_chargepoint_LAD.rename(columns={'distance': 'avg_distance'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "id": "ee1f9cca",
   "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>LAD22CD</th>\n",
       "      <th>avg_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>1649.498055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>1088.305461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000003</td>\n",
       "      <td>1454.585351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000004</td>\n",
       "      <td>911.259807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>1188.923394</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     LAD22CD  avg_distance\n",
       "0  E06000001   1649.498055\n",
       "1  E06000002   1088.305461\n",
       "2  E06000003   1454.585351\n",
       "3  E06000004    911.259807\n",
       "4  E06000005   1188.923394"
      ]
     },
     "execution_count": 325,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "avg_dist_nearest_chargepoint_LAD.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "id": "f7571ce1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "331"
      ]
     },
     "execution_count": 326,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(avg_dist_nearest_chargepoint_LAD)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f37ddca0",
   "metadata": {},
   "source": [
    "### Calculate number of EV chargepoints in each LAD\n",
    "\n",
    "* Aggregate the EV chargepoint data to LAD level to get total number of chargepoints in each LAD."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "id": "5047db60",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Spatially match\n",
    "chargepoints_LAD = gpd.sjoin(LADs, chargepoints_gdf, predicate='intersects')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 328,
   "id": "3ab1b64a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate to LAD and calculate total number of chargepoints\n",
    "chargepoints_LAD = chargepoints_LAD.groupby('LAD22CD')['index_right'].agg('count').reset_index()\n",
    "\n",
    "# Rename column to total_chargepoints\n",
    "chargepoints_LAD.rename(columns={'index_right': 'total_chargepoints'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "id": "3c2b089d",
   "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>LAD22CD</th>\n",
       "      <th>total_chargepoints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000003</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000004</td>\n",
       "      <td>167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     LAD22CD  total_chargepoints\n",
       "0  E06000001                  42\n",
       "1  E06000002                  51\n",
       "2  E06000003                  46\n",
       "3  E06000004                 167\n",
       "4  E06000005                  76"
      ]
     },
     "execution_count": 329,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "chargepoints_LAD.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 330,
   "id": "b18b492b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "373"
      ]
     },
     "execution_count": 330,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(chargepoints_LAD)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de3a2b60",
   "metadata": {},
   "source": [
    "### Calculate ratio of EV cars to chargepoints in each LAD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 401,
   "id": "da9fa62b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge PiVs and chargepoint count dataframes\n",
    "ratio_PiVs_to_chargepoints = pd.merge(PiVs_LAD, chargepoints_LAD, on='LAD22CD', how='left')\n",
    "\n",
    "# Replace NaN values with 0 where there are no chargepoints in an MSOA\n",
    "ratio_PiVs_to_chargepoints.fillna({'total_chargepoints': 0}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 402,
   "id": "006e5e38",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate ratio\n",
    "ratio_PiVs_to_chargepoints['ratio_PiVs_to_chargepoints'] = (\n",
    "    ratio_PiVs_to_chargepoints['PiVs'] / ratio_PiVs_to_chargepoints['total_chargepoints'])\n",
    "\n",
    "# Replace 'inf' with NaN\n",
    "ratio_PiVs_to_chargepoints['ratio_PiVs_to_chargepoints'] = ratio_PiVs_to_chargepoints[\n",
    "    'ratio_PiVs_to_chargepoints'].replace([np.inf, -np.inf], np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "157567f3",
   "metadata": {},
   "source": [
    "### Add average distance and LAD polygon to final dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 403,
   "id": "2335bd21",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add nearest chargepoint\n",
    "final_df = pd.merge(ratio_PiVs_to_chargepoints,avg_dist_nearest_chargepoint_LAD, on='LAD22CD')\n",
    "\n",
    "# Add LAD polygons\n",
    "final_df = pd.merge(LADs, final_df, on='LAD22CD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 404,
   "id": "299e85b0",
   "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>LAD22CD</th>\n",
       "      <th>LAD22NM</th>\n",
       "      <th>geometry</th>\n",
       "      <th>PiVs</th>\n",
       "      <th>total_chargepoints</th>\n",
       "      <th>ratio_PiVs_to_chargepoints</th>\n",
       "      <th>avg_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>MULTIPOLYGON (((450154.599 525938.201, 450140....</td>\n",
       "      <td>410.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>9.761905</td>\n",
       "      <td>1649.498055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>MULTIPOLYGON (((446854.700 517192.700, 446854....</td>\n",
       "      <td>391.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>7.666667</td>\n",
       "      <td>1088.305461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000003</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>MULTIPOLYGON (((451747.397 520561.100, 451792....</td>\n",
       "      <td>580.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>12.608696</td>\n",
       "      <td>1454.585351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000004</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>MULTIPOLYGON (((447177.704 517811.797, 447176....</td>\n",
       "      <td>1308.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>7.832335</td>\n",
       "      <td>911.259807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>Darlington</td>\n",
       "      <td>POLYGON ((423496.602 524724.299, 423497.204 52...</td>\n",
       "      <td>719.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.460526</td>\n",
       "      <td>1188.923394</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     LAD22CD               LAD22NM  \\\n",
       "0  E06000001            Hartlepool   \n",
       "1  E06000002         Middlesbrough   \n",
       "2  E06000003  Redcar and Cleveland   \n",
       "3  E06000004      Stockton-on-Tees   \n",
       "4  E06000005            Darlington   \n",
       "\n",
       "                                            geometry    PiVs  \\\n",
       "0  MULTIPOLYGON (((450154.599 525938.201, 450140....   410.0   \n",
       "1  MULTIPOLYGON (((446854.700 517192.700, 446854....   391.0   \n",
       "2  MULTIPOLYGON (((451747.397 520561.100, 451792....   580.0   \n",
       "3  MULTIPOLYGON (((447177.704 517811.797, 447176....  1308.0   \n",
       "4  POLYGON ((423496.602 524724.299, 423497.204 52...   719.0   \n",
       "\n",
       "   total_chargepoints  ratio_PiVs_to_chargepoints  avg_distance  \n",
       "0                42.0                    9.761905   1649.498055  \n",
       "1                51.0                    7.666667   1088.305461  \n",
       "2                46.0                   12.608696   1454.585351  \n",
       "3               167.0                    7.832335    911.259807  \n",
       "4                76.0                    9.460526   1188.923394  "
      ]
     },
     "execution_count": 404,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a66ae265",
   "metadata": {},
   "source": [
    "### Handle outliers \n",
    "\n",
    "There are some outliers on the top end, so anything outside 2sd will be amended."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 405,
   "id": "02277938",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loop through each variable and calculate mean, std, and threshold\n",
    "for variable in ['PiVs', 'total_chargepoints', 'ratio_PiVs_to_chargepoints', 'avg_distance']:\n",
    "    mean_var = final_df[variable].mean()\n",
    "    std_var = final_df[variable].std()\n",
    "    threshold_var = mean_var + 2 * std_var\n",
    "    \n",
    "    # Create new column based on outlier condition\n",
    "    final_df[f'amended_{variable}'] = final_df[variable].apply(lambda x: threshold_var if x > threshold_var else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 407,
   "id": "31e40e9f",
   "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>LAD22CD</th>\n",
       "      <th>LAD22NM</th>\n",
       "      <th>geometry</th>\n",
       "      <th>PiVs</th>\n",
       "      <th>total_chargepoints</th>\n",
       "      <th>ratio_PiVs_to_chargepoints</th>\n",
       "      <th>avg_distance</th>\n",
       "      <th>amended_PiVs</th>\n",
       "      <th>amended_total_chargepoints</th>\n",
       "      <th>amended_ratio_PiVs_to_chargepoints</th>\n",
       "      <th>amended_avg_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>MULTIPOLYGON (((450154.599 525938.201, 450140....</td>\n",
       "      <td>410.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>9.761905</td>\n",
       "      <td>1649.498055</td>\n",
       "      <td>410.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>9.761905</td>\n",
       "      <td>1649.498055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>MULTIPOLYGON (((446854.700 517192.700, 446854....</td>\n",
       "      <td>391.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>7.666667</td>\n",
       "      <td>1088.305461</td>\n",
       "      <td>391.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>7.666667</td>\n",
       "      <td>1088.305461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000003</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>MULTIPOLYGON (((451747.397 520561.100, 451792....</td>\n",
       "      <td>580.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>12.608696</td>\n",
       "      <td>1454.585351</td>\n",
       "      <td>580.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>12.608696</td>\n",
       "      <td>1454.585351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000004</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>MULTIPOLYGON (((447177.704 517811.797, 447176....</td>\n",
       "      <td>1308.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>7.832335</td>\n",
       "      <td>911.259807</td>\n",
       "      <td>1308.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>7.832335</td>\n",
       "      <td>911.259807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>Darlington</td>\n",
       "      <td>POLYGON ((423496.602 524724.299, 423497.204 52...</td>\n",
       "      <td>719.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.460526</td>\n",
       "      <td>1188.923394</td>\n",
       "      <td>719.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.460526</td>\n",
       "      <td>1188.923394</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     LAD22CD               LAD22NM  \\\n",
       "0  E06000001            Hartlepool   \n",
       "1  E06000002         Middlesbrough   \n",
       "2  E06000003  Redcar and Cleveland   \n",
       "3  E06000004      Stockton-on-Tees   \n",
       "4  E06000005            Darlington   \n",
       "\n",
       "                                            geometry    PiVs  \\\n",
       "0  MULTIPOLYGON (((450154.599 525938.201, 450140....   410.0   \n",
       "1  MULTIPOLYGON (((446854.700 517192.700, 446854....   391.0   \n",
       "2  MULTIPOLYGON (((451747.397 520561.100, 451792....   580.0   \n",
       "3  MULTIPOLYGON (((447177.704 517811.797, 447176....  1308.0   \n",
       "4  POLYGON ((423496.602 524724.299, 423497.204 52...   719.0   \n",
       "\n",
       "   total_chargepoints  ratio_PiVs_to_chargepoints  avg_distance  amended_PiVs  \\\n",
       "0                42.0                    9.761905   1649.498055         410.0   \n",
       "1                51.0                    7.666667   1088.305461         391.0   \n",
       "2                46.0                   12.608696   1454.585351         580.0   \n",
       "3               167.0                    7.832335    911.259807        1308.0   \n",
       "4                76.0                    9.460526   1188.923394         719.0   \n",
       "\n",
       "   amended_total_chargepoints  amended_ratio_PiVs_to_chargepoints  \\\n",
       "0                        42.0                            9.761905   \n",
       "1                        51.0                            7.666667   \n",
       "2                        46.0                           12.608696   \n",
       "3                       167.0                            7.832335   \n",
       "4                        76.0                            9.460526   \n",
       "\n",
       "   amended_avg_distance  \n",
       "0           1649.498055  \n",
       "1           1088.305461  \n",
       "2           1454.585351  \n",
       "3            911.259807  \n",
       "4           1188.923394  "
      ]
     },
     "execution_count": 407,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visual check\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1283cb76",
   "metadata": {},
   "source": [
    "## Visualising data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48b52ab8",
   "metadata": {},
   "source": [
    "The following code builds an interactive map of the features in `final_df`. This is saved down into an `Outputs/` folder as an HTML file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 410,
   "id": "3977fac0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\anaconda3\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1446: UserWarning: KMeans is known to have a memory leak on Windows with MKL, when there are less chunks than available threads. You can avoid it by setting the environment variable OMP_NUM_THREADS=2.\n",
      "  warnings.warn(\n",
      "C:\\ProgramData\\anaconda3\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1446: UserWarning: KMeans is known to have a memory leak on Windows with MKL, when there are less chunks than available threads. You can avoid it by setting the environment variable OMP_NUM_THREADS=2.\n",
      "  warnings.warn(\n",
      "C:\\ProgramData\\anaconda3\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1446: UserWarning: KMeans is known to have a memory leak on Windows with MKL, when there are less chunks than available threads. You can avoid it by setting the environment variable OMP_NUM_THREADS=2.\n",
      "  warnings.warn(\n",
      "C:\\ProgramData\\anaconda3\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1446: UserWarning: KMeans is known to have a memory leak on Windows with MKL, when there are less chunks than available threads. You can avoid it by setting the environment variable OMP_NUM_THREADS=2.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "# Create the individual layers\n",
    "m = final_df.explore(\n",
    "    column=\"amended_PiVs\",\n",
    "    scheme=\"naturalbreaks\",\n",
    "    cmap='YlOrRd',\n",
    "    legend=False,\n",
    "    k=10,\n",
    "    tooltip=[\"LAD22CD\", \"LAD22NM\", \"amended_PiVs\"],\n",
    "    style_kwds=dict(color='black', weight=0.5, fillOpacity=0.8),\n",
    "    highlight_kwds=dict(fillOpacity=1),\n",
    "    name=\"Plug-in Vehicles\",\n",
    "    show=True)\n",
    "\n",
    "final_df.explore(\n",
    "    m=m,\n",
    "    column=\"amended_total_chargepoints\",\n",
    "    scheme=\"naturalbreaks\",\n",
    "    cmap='YlOrRd',\n",
    "    legend=False,\n",
    "    k=10,\n",
    "    tooltip=[\"LAD22CD\", \"LAD22NM\", \"amended_total_chargepoints\"],\n",
    "    style_kwds=dict(color='black', weight=0.5, fillOpacity=0.8),\n",
    "    highlight_kwds=dict(fillOpacity=1),\n",
    "    name=\"Total EV Chargepoints\",\n",
    "    show=False)\n",
    "\n",
    "final_df.explore(\n",
    "    m=m,\n",
    "    column=\"amended_ratio_PiVs_to_chargepoints\",\n",
    "    scheme=\"naturalbreaks\",\n",
    "    cmap='YlOrRd',\n",
    "    legend=False,\n",
    "    k=10,\n",
    "    tooltip=[\"LAD22CD\", \"LAD22NM\", \"amended_ratio_PiVs_to_chargepoints\"],\n",
    "    style_kwds=dict(color='black', weight=0.5, fillOpacity=0.8),\n",
    "    highlight_kwds=dict(fillOpacity=1),\n",
    "    name=\"Ratio of Plug-in Vehicles to EV Chargepoints\",\n",
    "    show=False)\n",
    "\n",
    "final_df.explore(\n",
    "    m=m,\n",
    "    column=\"amended_avg_distance\",\n",
    "    scheme=\"naturalbreaks\",\n",
    "    cmap='YlOrRd',\n",
    "    legend=False,\n",
    "    k=10,\n",
    "    tooltip=[\"LAD22CD\", \"LAD22NM\", \"amended_avg_distance\"],\n",
    "    style_kwds=dict(color='black', weight=0.5, fillOpacity=0.8),\n",
    "    highlight_kwds=dict(fillOpacity=1),\n",
    "    name=\"Average distance to nearest EV Chargepoint by OA\",\n",
    "    show=False)\n",
    "\n",
    "# Add the map base\n",
    "folium.TileLayer(\"CartoDB positron\", show=True).add_to(m)\n",
    "\n",
    "# Add layer control to map\n",
    "folium.LayerControl().add_to(m)\n",
    "\n",
    "# Save the map to an HTML file\n",
    "m.save(\"Outputs/Interactive_map.html\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e1c71eb",
   "metadata": {},
   "source": [
    "The following code builds four choropleth maps of the features in `final_df`. This is saved down into an `Outputs/` folder as a .png file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 420,
   "id": "db1ec001",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create 2x2 subplots\n",
    "fig, axs = plt.subplots(2, 2, figsize=(12, 12))\n",
    "\n",
    "# Define aliases for column names\n",
    "column_aliases = {\n",
    "    'amended_PiVs': 'Plug-in Vehicles (PiVs)',\n",
    "    'amended_total_chargepoints': 'Total EV Chargepoints (EVCs)',\n",
    "    'amended_ratio_PiVs_to_chargepoints': 'Ratio of PiVs to EVCs',\n",
    "    'amended_avg_distance': 'Average distance to nearest EVC by OA (metres)'\n",
    "}\n",
    "\n",
    "# Loop through each variable and corresponding subplot\n",
    "variables = list(column_aliases.keys())\n",
    "for variable, ax in zip(variables, axs.flatten()):\n",
    "    final_df.plot(column=variable, cmap='YlOrRd', ax=ax, legend=True)\n",
    "    ax.set_title(f'{column_aliases[variable]}')  # Set the title using the alias\n",
    "    ax.set_axis_off()  # Turn off axis\n",
    "    \n",
    "plt.subplots_adjust(wspace=0.05, hspace=0.05)  # Adjust space between subplots\n",
    "plt.tight_layout()  # Adjust layout to prevent overlap\n",
    "\n",
    "# Export to Outputs/ folder\n",
    "plt.savefig('Outputs/Feature_choropleths.png', dpi=300)\n",
    "\n",
    "# Close figure\n",
    "plt.close(fig)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a46ef31",
   "metadata": {},
   "source": [
    "The following code creates two scatterplots with regression lines for selected features from the `final_df`. This is saved down into an `Outputs/` folder as a .png file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 458,
   "id": "feec4558",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a subplot grid with 1 row and 2 columns\n",
    "fig, axs = plt.subplots(1, 2, figsize=(15, 6))\n",
    "\n",
    "# Plot the first scatter plot\n",
    "sns.scatterplot(x='amended_PiVs', y='amended_total_chargepoints', data=final_df, ax=axs[0])\n",
    "sns.regplot(x='amended_PiVs', y='amended_total_chargepoints', data=final_df, scatter=False, ax=axs[0])\n",
    "axs[0].set_xlabel(column_aliases['amended_PiVs'])  # Set x-axis label using alias\n",
    "axs[0].set_ylabel(column_aliases['amended_total_chargepoints'])  # Set y-axis label using alias\n",
    "axs[0].set_title('Plug-in Vehicles vs. Total EV Chargepoints')  # Set the title\n",
    "\n",
    "# Plot the second scatter plot\n",
    "sns.scatterplot(x='amended_ratio_PiVs_to_chargepoints', y='amended_avg_distance', data=final_df, ax=axs[1])\n",
    "sns.regplot(x='amended_ratio_PiVs_to_chargepoints', y='amended_avg_distance', data=final_df, scatter=False, ax=axs[1])\n",
    "axs[1].set_xlabel(column_aliases['amended_ratio_PiVs_to_chargepoints'])  # Set x-axis label using alias\n",
    "axs[1].set_ylabel(column_aliases['amended_avg_distance'])  # Set y-axis label using alias\n",
    "axs[1].set_title('Ratio vs. Distance')  # Set the title\n",
    "\n",
    "# Adjust layout\n",
    "plt.tight_layout()\n",
    "\n",
    "# Export to Outputs/ folder\n",
    "plt.savefig('Outputs/Scatter_Reg_Plots.png', dpi=300)\n",
    "\n",
    "# Close figure\n",
    "plt.close(fig)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "243b03c9",
   "metadata": {},
   "source": [
    "The following code creates a correlation matrix heatmap of the features in `final_df`. This is saved down into an `Outputs/` folder as a .png file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 462,
   "id": "86d88bc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the correlation matrix\n",
    "correlation_matrix = final_df[[\n",
    "    'amended_PiVs','amended_total_chargepoints',\n",
    "    'amended_ratio_PiVs_to_chargepoints','amended_avg_distance']].corr()\n",
    "\n",
    "# Generate a mask for the diagonal cells\n",
    "mask = np.triu(np.ones_like(correlation_matrix, dtype=bool))\n",
    "\n",
    "# Generate a heatmap\n",
    "plt.figure(figsize=(8, 7))\n",
    "sns.heatmap(correlation_matrix, cmap='RdYlGn', annot=True, \n",
    "            fmt=\".2f\", mask=mask, vmin=-1, vmax=1, cbar_kws={\"shrink\": 0.75}, linewidth=.5)\n",
    "\n",
    "# Rotate axis labels\n",
    "plt.xticks(rotation=45)\n",
    "plt.yticks(rotation=45)\n",
    "\n",
    "# Set axis labels using aliases and wrap text\n",
    "plt.xticks(ticks=range(len(correlation_matrix.columns)), \n",
    "           labels=['Plug-in Vehicles', \n",
    "                   'Total EV Chargepoints', \n",
    "                   'Ratio of PiVs to EVCs', \n",
    "                   'Avg dist to nearest EVC'])\n",
    "\n",
    "plt.yticks(ticks=np.arange(len(correlation_matrix.columns))+0.5, \n",
    "           labels=['Plug-in Vehicles', \n",
    "                   'Total EV Chargepoints', \n",
    "                   'Ratio of PiVs to EVCs', \n",
    "                   'Avg dist to nearest EVC'])\n",
    "\n",
    "plt.title('Correlation Matrix Heatmap', fontweight='bold')\n",
    "\n",
    "plt.tight_layout()\n",
    "\n",
    "# Export to Outputs/ folder\n",
    "plt.savefig('Outputs/CorrelationMatrix.png', dpi=300)\n",
    "\n",
    "# Close figure\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1e77275",
   "metadata": {},
   "source": [
    "## Creating a ranking"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31b2d7d2",
   "metadata": {},
   "source": [
    "The following code creates a simple ranking of LADs based on:\n",
    "\n",
    "* Average distance to the nearest EV chargepoint\n",
    "* Ratio of Plug-in vehicles to EV chargepoints\n",
    "\n",
    "The idea is that if the average distance is high, and the ratio is high, then these areas may be lacking public EV infrastructure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 484,
   "id": "6dde3f93",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new dataframe containing just the amended features for the ranking\n",
    "ranking = final_df.drop(final_df.columns[3:9], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 491,
   "id": "11416a2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rank the values in each column\n",
    "ranking['rank_ratio'] = ranking['amended_ratio_PiVs_to_chargepoints'].rank(ascending=True, method='min')\n",
    "ranking['rank_distance'] = ranking['amended_avg_distance'].rank(ascending=True, method='min')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 492,
   "id": "ea52b482",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute the combined ranking\n",
    "ranking['combined_rank'] = (ranking['rank_ratio'] + ranking['rank_distance']) / 2\n",
    "ranking['combined_rank'] = ranking['combined_rank'].rank(ascending=True, method='min')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02fc1cb2",
   "metadata": {},
   "source": [
    "The following code creates a map of the top 10 `combined_rank` LADs in `ranking`. This is saved down into an `Outputs/` folder as a .png file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 534,
   "id": "42cf730a",
   "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>LAD22CD</th>\n",
       "      <th>LAD22NM</th>\n",
       "      <th>combined_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>288</th>\n",
       "      <td>E09000013</td>\n",
       "      <td>Hammersmith and Fulham</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>E09000033</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>E09000028</td>\n",
       "      <td>Southwark</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276</th>\n",
       "      <td>E09000001</td>\n",
       "      <td>City of London</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>E09000020</td>\n",
       "      <td>Kensington and Chelsea</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>E09000024</td>\n",
       "      <td>Merton</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>307</th>\n",
       "      <td>E09000032</td>\n",
       "      <td>Wandsworth</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>E09000007</td>\n",
       "      <td>Camden</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>E09000012</td>\n",
       "      <td>Hackney</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>294</th>\n",
       "      <td>E09000019</td>\n",
       "      <td>Islington</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       LAD22CD                 LAD22NM  combined_rank\n",
       "288  E09000013  Hammersmith and Fulham            1.0\n",
       "308  E09000033             Westminster            2.0\n",
       "303  E09000028               Southwark            3.0\n",
       "276  E09000001          City of London            4.0\n",
       "295  E09000020  Kensington and Chelsea            4.0\n",
       "299  E09000024                  Merton            6.0\n",
       "307  E09000032              Wandsworth            6.0\n",
       "282  E09000007                  Camden            8.0\n",
       "287  E09000012                 Hackney            9.0\n",
       "294  E09000019               Islington            9.0"
      ]
     },
     "execution_count": 534,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get top 10 areas\n",
    "ranking[['LAD22CD','LAD22NM','combined_rank']].sort_values(by='combined_rank', ascending=True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 547,
   "id": "fa9fb947",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort and select the top 10 areas\n",
    "top_10_areas = ranking.sort_values(by='combined_rank', ascending=True).head(10)\n",
    "\n",
    "# Plot only the top 10 areas\n",
    "fig, ax = plt.subplots(1, 1, figsize=(8, 10))\n",
    "top_10_areas.plot(ax=ax, color='#88C88E', edgecolor='black', linewidth=1, legend=True, alpha=0.5)\n",
    "\n",
    "# Annotate the top 10 areas with their rank\n",
    "for idx, row in top_10_areas.iterrows():\n",
    "    plt.annotate(text=int(row['combined_rank']), xy=(row.geometry.centroid.x, row.geometry.centroid.y),\n",
    "                 horizontalalignment='center', fontsize=12, weight='bold', color='black')\n",
    "\n",
    "# Plot basemap\n",
    "ctx.add_basemap(ax, crs=top_10_areas.crs.to_string(), source=ctx.providers.CartoDB.Positron)\n",
    "\n",
    "# Set title and remove axis\n",
    "ax.set_title('Top 10 Areas by Combined Rank', fontsize=12, fontweight='bold')\n",
    "ax.set_axis_off()\n",
    "\n",
    "# Adjust layout\n",
    "plt.tight_layout()\n",
    "\n",
    "# Export to Outputs/ folder\n",
    "plt.savefig('Outputs/Map_of_Top_10.png', dpi=300)\n",
    "\n",
    "# Close figure\n",
    "plt.close(fig)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8482a3d4",
   "metadata": {},
   "source": [
    "The following code creates a map of the bottom 10 `combined_rank` LADs in `ranking`. This is saved down into an `Outputs/` folder as a .png file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 496,
   "id": "589f1295",
   "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>LAD22CD</th>\n",
       "      <th>LAD22NM</th>\n",
       "      <th>combined_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>E07000074</td>\n",
       "      <td>Maldon</td>\n",
       "      <td>330.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>E07000064</td>\n",
       "      <td>Rother</td>\n",
       "      <td>329.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>E07000242</td>\n",
       "      <td>East Hertfordshire</td>\n",
       "      <td>328.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>E06000017</td>\n",
       "      <td>Rutland</td>\n",
       "      <td>327.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>E07000075</td>\n",
       "      <td>Rochford</td>\n",
       "      <td>326.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>E07000047</td>\n",
       "      <td>West Devon</td>\n",
       "      <td>325.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>E07000169</td>\n",
       "      <td>Selby</td>\n",
       "      <td>324.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>E07000139</td>\n",
       "      <td>North Kesteven</td>\n",
       "      <td>323.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>E07000044</td>\n",
       "      <td>South Hams</td>\n",
       "      <td>321.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>E07000198</td>\n",
       "      <td>Staffordshire Moorlands</td>\n",
       "      <td>321.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       LAD22CD                  LAD22NM  combined_rank\n",
       "99   E07000074                   Maldon          330.0\n",
       "89   E07000064                   Rother          329.0\n",
       "235  E07000242       East Hertfordshire          328.0\n",
       "16   E06000017                  Rutland          327.0\n",
       "100  E07000075                 Rochford          326.0\n",
       "85   E07000047               West Devon          325.0\n",
       "177  E07000169                    Selby          324.0\n",
       "160  E07000139           North Kesteven          323.0\n",
       "82   E07000044               South Hams          321.0\n",
       "199  E07000198  Staffordshire Moorlands          321.0"
      ]
     },
     "execution_count": 496,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show bottom 10 areas\n",
    "ranking[['LAD22CD','LAD22NM','combined_rank']].sort_values(by='combined_rank', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 549,
   "id": "186e1951",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort and select the bottom 10 areas\n",
    "bottom_10_areas = ranking.sort_values(by='combined_rank', ascending=False).head(10)\n",
    "\n",
    "# Plot only the top 10 areas\n",
    "fig, ax = plt.subplots(1, 1, figsize=(10, 10))\n",
    "bottom_10_areas.plot(ax=ax, color='#DE6464', edgecolor='black', linewidth=1, legend=True, alpha=0.5)\n",
    "\n",
    "# Annotate the top 10 areas with their rank\n",
    "for idx, row in bottom_10_areas.iterrows():\n",
    "    plt.annotate(text=int(row['combined_rank']), xy=(row.geometry.centroid.x, row.geometry.centroid.y),\n",
    "                 horizontalalignment='center', fontsize=12, weight='bold', color='black')\n",
    "\n",
    "# Plot basemap\n",
    "ctx.add_basemap(ax, crs=bottom_10_areas.crs.to_string(), source=ctx.providers.CartoDB.Positron)\n",
    "\n",
    "# Set title and remove axis\n",
    "ax.set_title('Bottom 10 Areas by Combined Rank', fontsize=12, fontweight='bold')\n",
    "ax.set_axis_off()\n",
    "\n",
    "# Adjust layout\n",
    "plt.tight_layout()\n",
    "\n",
    "# Export to Outputs/ folder\n",
    "plt.savefig('Outputs/Map_of_Bottom_10.png', dpi=300)\n",
    "\n",
    "# Close figure\n",
    "plt.close(fig)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}