notebooks/demographic-change.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "65449344-4adf-49f2-93be-42a71fbc57f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from utilities import *\n",
    "\n",
    "\n",
    "sentinel_measures = [\"qrisk2\", \"asthma\", \"copd\", \"sodium\", \"cholesterol\", \"alt\", \"tsh\", \"alt\", \"rbc\", 'hba1c', 'systolic_bp', 'medication_review']\n",
    "\n",
    "demographics = ['region', 'age_band', 'imd', 'sex', 'learning_disability', 'ethnicity']\n",
    "\n",
    "values_dict = {}\n",
    "\n",
    "\n",
    "dates = ['2019-04-01', '2020-04-01', '2021-04-01']\n",
    "\n",
    "differences_list = []\n",
    "\n",
    "for measure in sentinel_measures:\n",
    "    \n",
    "    total_df = load_and_drop(measure, practice=True)\n",
    "    total_df['rate'] = (total_df[measure] / total_df['population'])*1000\n",
    "    total_df = total_df.groupby(by=['date'])[['rate']].mean().reset_index()\n",
    "    \n",
    "    totals_dict = {}\n",
    "    for date in dates:\n",
    "        val = total_df[total_df['date'] == date]['rate']\n",
    "        totals_dict[date] = val\n",
    "    \n",
    "    \n",
    "        \n",
    "\n",
    "    \n",
    "    for d in demographics:\n",
    "        df = pd.read_csv(f'../output/combined_measure_{measure}_{d}.csv', parse_dates=['date']).sort_values(['date'])\n",
    "        \n",
    "        \n",
    "        if d == 'ethnicity':\n",
    "            \n",
    "            #drop missing ethnicity :('0')\n",
    "            df = df[df['ethnicity'] != 0]\n",
    "            \n",
    "            # replace with strings\n",
    "            ethnicity_codes = {1.0: \"White\", 2.0: \"Mixed\", 3.0: \"Asian\", 4.0: \"Black\", 5.0:\"Other\"}\n",
    "            df = df.replace({\"ethnicity\": ethnicity_codes})\n",
    "            \n",
    "        elif d == 'age_band':\n",
    "            df = df[df['age_band'] != 'missing']\n",
    "            \n",
    "        elif d == 'learning_disability':\n",
    "            ld_dict = {0: 'No record of a learning disability', 1: 'Record of a learning disability'}\n",
    "            df = df.replace({\"learning_disability\": ld_dict})\n",
    "        \n",
    "        \n",
    "        if d != 'age_band':\n",
    "            df['rate'] = df[measure]/(df['population']/1000)\n",
    "        \n",
    "        \n",
    "      \n",
    "        for unique_category in df[d].unique():\n",
    "            df_subset = df[df[d] == unique_category]\n",
    "            \n",
    "            \n",
    "            date_values = {}\n",
    "            date_changes = {}\n",
    "            \n",
    "            for date in dates:\n",
    "                val = df_subset[df_subset['date']==date]['rate'].values[0]\n",
    "                total_val = totals_dict[date].values[0]\n",
    "             \n",
    "\n",
    "                difference = round(((val - total_val) / total_val)*100, 2)\n",
    "             \n",
    "                date_values[date]=val\n",
    "                date_changes[date] = difference\n",
    "\n",
    "         \n",
    "            row = [measure, d, unique_category, f'{date_values[\"2019-04-01\"]} ({date_changes[\"2019-04-01\"]})', f'{date_values[\"2020-04-01\"]} ({date_changes[\"2020-04-01\"]})', f'{date_values[\"2021-04-01\"]} ({date_changes[\"2021-04-01\"]})']\n",
    "            differences_list.append(row)\n",
    "        \n",
    " \n",
    "            \n",
    "   \n",
    "    \n",
    "differences_df =pd.DataFrame(differences_list, columns=['measure', 'demographic', 'demographic_subset', '2019-04-01', '2020-04-01', '2021-04-01'])\n",
    "differences_df.to_csv('../output/demographics_differences.csv') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "13d13978-78a9-4f74-a876-cfd475b92ff7",
   "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>measure</th>\n",
       "      <th>demographic</th>\n",
       "      <th>demographic_subset</th>\n",
       "      <th>2019-04-01</th>\n",
       "      <th>2020-04-01</th>\n",
       "      <th>2021-04-01</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qrisk2</td>\n",
       "      <td>region</td>\n",
       "      <td>North East</td>\n",
       "      <td>750.0 (54.76)</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>687.5 (29.77)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>qrisk2</td>\n",
       "      <td>region</td>\n",
       "      <td>Yorkshire and the Humber</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>833.3333333333334 (57.29)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qrisk2</td>\n",
       "      <td>region</td>\n",
       "      <td>West Midlands</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>375.0 (-25.51)</td>\n",
       "      <td>500.0 (-5.62)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qrisk2</td>\n",
       "      <td>region</td>\n",
       "      <td>South East</td>\n",
       "      <td>562.5 (16.07)</td>\n",
       "      <td>523.8095238095237 (4.05)</td>\n",
       "      <td>666.6666666666666 (25.83)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qrisk2</td>\n",
       "      <td>region</td>\n",
       "      <td>North West</td>\n",
       "      <td>600.0 (23.81)</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>medication_review</td>\n",
       "      <td>ethnicity</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>medication_review</td>\n",
       "      <td>ethnicity</td>\n",
       "      <td>White</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>medication_review</td>\n",
       "      <td>ethnicity</td>\n",
       "      <td>Asian</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>medication_review</td>\n",
       "      <td>ethnicity</td>\n",
       "      <td>Black</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>medication_review</td>\n",
       "      <td>ethnicity</td>\n",
       "      <td>Other</td>\n",
       "      <td>666.6666666666666 (30.77)</td>\n",
       "      <td>nan (nan)</td>\n",
       "      <td>nan (nan)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>360 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               measure demographic        demographic_subset  \\\n",
       "0               qrisk2      region                North East   \n",
       "1               qrisk2      region  Yorkshire and the Humber   \n",
       "2               qrisk2      region             West Midlands   \n",
       "3               qrisk2      region                South East   \n",
       "4               qrisk2      region                North West   \n",
       "..                 ...         ...                       ...   \n",
       "355  medication_review   ethnicity                     Mixed   \n",
       "356  medication_review   ethnicity                     White   \n",
       "357  medication_review   ethnicity                     Asian   \n",
       "358  medication_review   ethnicity                     Black   \n",
       "359  medication_review   ethnicity                     Other   \n",
       "\n",
       "                    2019-04-01                2020-04-01  \\\n",
       "0                750.0 (54.76)                 nan (nan)   \n",
       "1                    nan (nan)                 nan (nan)   \n",
       "2                    nan (nan)            375.0 (-25.51)   \n",
       "3                562.5 (16.07)  523.8095238095237 (4.05)   \n",
       "4                600.0 (23.81)                 nan (nan)   \n",
       "..                         ...                       ...   \n",
       "355                  nan (nan)                 nan (nan)   \n",
       "356                  nan (nan)                 nan (nan)   \n",
       "357                  nan (nan)                 nan (nan)   \n",
       "358                  nan (nan)                 nan (nan)   \n",
       "359  666.6666666666666 (30.77)                 nan (nan)   \n",
       "\n",
       "                    2021-04-01  \n",
       "0                687.5 (29.77)  \n",
       "1    833.3333333333334 (57.29)  \n",
       "2                500.0 (-5.62)  \n",
       "3    666.6666666666666 (25.83)  \n",
       "4                    nan (nan)  \n",
       "..                         ...  \n",
       "355                  nan (nan)  \n",
       "356                  nan (nan)  \n",
       "357                  nan (nan)  \n",
       "358                  nan (nan)  \n",
       "359                  nan (nan)  \n",
       "\n",
       "[360 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b94bdb8-15ce-4f83-a7bc-468337aee43d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}