data_cleaning_py.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "PLEASE NOTE THAT THE BELOW HAS NOT BE THOROUGHLY CLEANED, IT REFLECTS THE CALCULATIONS MADE TO INFORM THE MAIN MODEL FUNCTION\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from collections import OrderedDict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Helper function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Regroup various age-group representations into our internal one, and vice versa\n",
    "def regroup_by_age(\n",
    "    inp, # first dimension is ages, others dont matter.\n",
    "    fromAgeSplits, toAgeSplits, maxAge=100., maxAgeWeight = 5.):\n",
    "    fromAgeSplits = np.concatenate([np.array([0]), fromAgeSplits, np.array([maxAge])]) # Add a zero at beginning for calculations\n",
    "    toAgeSplits = np.concatenate([np.array([0]), toAgeSplits, np.array([maxAge])]) # Add inf at end for calculations\n",
    "    def getOverlap(a, b):\n",
    "        return max(0, min(a[1], b[1]) - max(a[0], b[0]))\n",
    "    out = np.zeros((len(toAgeSplits)-1,)+inp.shape[1:])\n",
    "    for from_ind in range(1, len(fromAgeSplits)):\n",
    "        # Redistribute to the new bins by calculating how many years in from_ind-1:from_ind falls into each output bin\n",
    "        cur_out_distribution = (\n",
    "        [getOverlap(toAgeSplits[cur_to_ind-1:cur_to_ind+1],fromAgeSplits[from_ind-1:from_ind+1])  for cur_to_ind in range(1, len(toAgeSplits))]\n",
    "        )\n",
    "        \n",
    "        if cur_out_distribution[-1] > 0:\n",
    "            cur_out_distribution[-1] = maxAgeWeight # Define the relative number of ages if we have to distribute between second to last and last age groups\n",
    "\n",
    "        cur_out_distribution = cur_out_distribution/np.sum(cur_out_distribution)\n",
    "        \n",
    "        for to_ind in range(len(out)):\n",
    "            out[to_ind] += cur_out_distribution[to_ind] * inp[from_ind-1]\n",
    "            \n",
    "    return out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CHESS -  COVID-19 Hospitalisation in England Surveillance System\n",
    "\n",
    "Aggregate data available on request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_UK_CHESSagg=pd.read_csv(\"~/covidwarwick/data/CHESSAggregateReport.csv\")\n",
    "aggDict={key: 'sum'  for key in df_UK_CHESSagg.columns[5:66]}\n",
    "# Aggregate to national level\n",
    "df_UK_CHESS=df_UK_CHESSagg.groupby(\"DateOfAdmission\").agg(aggDict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_UK_CHESS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bit of data cleaning (the dates are wrong!, first line is sum of all column)\n",
    "# Note that proper reporting started \n",
    "df_UK_chess_cumulative_05Apr = df_UK_CHESS.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_UK_chess_cumulative_05Apr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nDataTypes = 6\n",
    "nAgeGroups = 10 # Note that age ranges are <1, 1-4, 5-14, 15-24 ... 75-84 and 85+, let's make matrices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "allVectors = OrderedDict()\n",
    "for d1 in range(nDataTypes):\n",
    "    if d1 == 0:\n",
    "        allVectors[df_UK_chess_cumulative_05Apr.index[1+d1*nAgeGroups][:-len(\"_1_4\")]] = (\n",
    "            df_UK_chess_cumulative_05Apr.values[range(1+d1*nAgeGroups, 1+(d1+1)*nAgeGroups)])\n",
    "    else:\n",
    "        allVectors[df_UK_chess_cumulative_05Apr.index[d1*nAgeGroups][:-len(\"_LessThan_1\")]] = (\n",
    "            df_UK_chess_cumulative_05Apr.values[range(d1*nAgeGroups, (d1+1)*nAgeGroups)])\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "allVectors.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalSympAdmitted_byAge = allVectors[\"NewHospitalAdmissionsWithAcuteRespiratoryInfectionAdmittedDuringThePast24Hours\"]\n",
    "totalSympAdmitted_byAge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalTested_byAge = allVectors[\"AllAdmittedPatientsTestedForCOVID19\"]\n",
    "totalTested_byAge "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalPositive_byAge = allVectors['AllAdmittedPatientsWithNewLabConfirmedCOVID19']\n",
    "totalPositive_byAge "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalPositive_byAge/totalTested_byAge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "icuriskSymp_byAge = allVectors['NewICU_HDUAdmissionsWithAcuteRespiratoryInfection']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "icuriskCovid_byAge = allVectors['NewLabConfirmedCOVID19PatientsOnICU_HDU']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "icuriskCovid_byAge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "icuriskSymp_byAge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(icuriskSymp_byAge)\n",
    "            \n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.concatenate([np.array([1,5,15,25]),np.arange(45,85+1,10)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalSympAdmitted_byAge_regroup = regroup_by_age(\n",
    "    totalSympAdmitted_byAge,\n",
    "    fromAgeSplits=np.concatenate([np.array([1,5,15,25]),np.arange(45,85+1,10)]),\n",
    "    toAgeSplits=np.arange(10,80+1,10)\n",
    ")\n",
    "\n",
    "\n",
    "totalCOVIDAdmitted_byAge_regroup = regroup_by_age(\n",
    "    totalPositive_byAge,\n",
    "    fromAgeSplits=np.concatenate([np.array([1,5,15,25]),np.arange(45,85+1,10)]),\n",
    "    toAgeSplits=np.arange(10,80+1,10)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load age data from Imperial college data:\n",
    "agePopulationTotal = 1000.*np.array([8044.056,7642.473,8558.707,9295.024,8604.251,9173.465,7286.777,5830.635,3450.616])\n",
    "#agePopulationTotal = 1000.*pd.read_csv(\"https://raw.githubusercontent.com/ImperialCollegeLondon/covid19model/master/data/ages.csv\").iloc[3].values[2:]\n",
    "agePopulationRatio = agePopulationTotal/np.sum(agePopulationTotal)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "relativeAdmissionRisk_given_symptoms_by_age = totalSympAdmitted_byAge_regroup/agePopulationTotal\n",
    "relativeAdmissionRisk_given_symptoms_by_age /= np.mean(relativeAdmissionRisk_given_symptoms_by_age)\n",
    "relativeAdmissionRisk_given_symptoms_by_age -= 1\n",
    "\n",
    "relativeAdmissionRisk_given_symptoms_by_age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalSympAdmitted_byAge_regroup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalCOVIDAdmitted_byAge_regroup/agePopulationTotal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "1.61840668e-03/3.99559366e-04"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "relativeAdmissionRisk_given_COVID_by_age = totalCOVIDAdmitted_byAge_regroup/agePopulationTotal\n",
    "relativeAdmissionRisk_given_COVID_by_age /= np.mean(relativeAdmissionRisk_given_COVID_by_age)\n",
    "relativeAdmissionRisk_given_COVID_by_age -= 1\n",
    "\n",
    "\n",
    "relativeAdmissionRisk_given_COVID_by_age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# From earlier version, very similar, seems stable, we can keep using it!\n",
    "relativeAdmissionRisk_given_COVID_by_age = np.array([-0.94886625, -0.96332087, -0.86528671, -0.79828999, -0.61535305,\n",
    "       -0.35214767,  0.12567034,  0.85809052,  3.55950368])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Above two are quite similar, which makes one confident that testing in hospitals itself is not too biased!\n",
    "# For now we can trust these computed parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NHS England COVID deaths by age group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# NHS daily deaths report (about 24 hours behind, with ~5-7 days of `unreliable` data)\n",
    "# TODO manually update link and column numbers (maybe not consistent across days, cannot yet automate)\n",
    "df_UK_NHS_daily_COVID_deaths = pd.read_excel(\n",
    "    \"https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2020/04/COVID-19-total-announced-deaths-6-April-2020.xlsx\",\n",
    "    sheet_name = \"COVID19 total deaths by age\",\n",
    "    index_col=0,\n",
    "    usecols = \"B,E:AN\",\n",
    "    skip_rows = range(17),\n",
    "    nrows = 22\n",
    ").iloc[14:].transpose().set_index(\"Age group\").rename_axis(index = \"Date\", columns = \"AgeGroup\")\n",
    "\n",
    "df_UK_NHS_daily_COVID_deaths.index = pd.to_datetime(df_UK_NHS_daily_COVID_deaths.index, format=\"%Y-%m-%d\")\n",
    "\n",
    "df_UK_NHS_daily_COVID_deaths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_UK_NHS_daily_COVID_deaths.drop(df_UK_NHS_daily_COVID_deaths.columns[:2], axis=1).to_hdf(\"data/all_data.h5\", key = \"clean_NHS_England_daily_COVID_deaths_by_age\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalDeaths_byAge = df_UK_NHS_daily_COVID_deaths.sum(0).values[2:]\n",
    "totalDeaths_byAge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.arange(20,80+1,20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.arange(10,80+1,10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalDeaths_byAge_regroup = regroup_by_age(\n",
    "    totalDeaths_byAge,\n",
    "    fromAgeSplits=np.arange(20,80+1,20),\n",
    "    toAgeSplits=np.arange(10,80+1,10)\n",
    ")\n",
    "\n",
    "totalDeaths_byAge_regroup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is not a very good assumption, we rather want to make it linear with age, \n",
    "# which with 50-50% splits of the bins would mean that 75% of the higher bin\n",
    "# Probably more realistic\n",
    "totalDeaths_byAge_regroupLinear = np.array([5*0.25, 5*0.75, 38*0.25, 38*0.75, 353*0.25, 353*0.75, 1946*0.25, 1946*0.75, 2555.3])\n",
    "totalDeaths_byAge_regroupLinear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "1.47973579e-03/6.21576976e-07"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "relativeDeathRisk_given_COVID_by_age = totalDeaths_byAge_regroupLinear/agePopulationTotal\n",
    "relativeDeathRisk_given_COVID_by_age /= np.mean(relativeDeathRisk_given_COVID_by_age)\n",
    "relativeDeathRisk_given_COVID_by_age -= 1\n",
    "\n",
    "\n",
    "relativeDeathRisk_given_COVID_by_age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "relativeDeathRisk_given_COVID_by_age = np.array([-0.99873039, -0.99599102, -0.99093115, -0.97494866, -0.91620111,\n",
    "       -0.7642025 , -0.4545135 ,  1.04514869,  5.05036963])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalDeaths_byAge_regroup.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "caseFatalityRatioHospital_given_COVID_by_age = totalDeaths_byAge_regroupLinear/totalCOVIDAdmitted_byAge_regroup\n",
    "caseFatalityRatioHospital_given_COVID_by_age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "caseFatalityRatioHospital_given_COVID_by_age = np.array([0.00856164, 0.03768844, 0.02321319, 0.04282494, 0.07512237,\n",
    "       0.12550367, 0.167096  , 0.37953452, 0.45757006])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalCOVIDAdmitted_byAge_regroup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Overall hospitalised case fatality ratio\n",
    "np.sum(totalDeaths_byAge_regroup)/np.sum(totalCOVIDAdmitted_byAge_regroup)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A&E attandances by age group\n",
    "\n",
    "NHS data 2018-19, Table 6 of https://digital.nhs.uk/data-and-information/publications/statistical/hospital-accident--emergency-activity/2018-19, https://files.digital.nhs.uk/06/C0AC02/AE1819_National_Data_Tables_v4.xlsx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ae_attendances_by_age = np.array([\n",
    "    619842+1607748+1091497, \n",
    "    1147661+222246+203356+228898+287171+322682,\n",
    "    1641251 + 1648698,\n",
    "    1534060 + 1298664,\n",
    "    1108324 + 1185519,\n",
    "    1210073 + 1101959,\n",
    "    940500 +  879026,\n",
    "    970384 +  869129,\n",
    "    846931 +  674704 + 471264 + 256260\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "riskOfAEAttandance = ae_attendances_by_age/agePopulationTotal\n",
    "riskOfAEAttandance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "riskOfAEAttandance = np.array([0.41261361, 0.31560648, 0.3843979 , 0.30475704, 0.26659415,\n",
    "       0.25203475, 0.24970244, 0.31549102, 0.65181376])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "riskOfAEAttandance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:.conda-py-corona-2-ensemble]",
   "language": "python",
   "name": "conda-env-.conda-py-corona-2-ensemble-py"
  },
  "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.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}