notebooks/prevalence_comparison.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prevalence of PRIMIS codelists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "\n",
    "suffix = f\"_{os.environ.get('OPENSAFELY_BACKEND', 'tpp')}\"\n",
    "os.makedirs(os.path.join(\"..\",\"safe-outputs\"), exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import first row to get col names\n",
    "df_head = pd.read_csv(os.path.join(\"..\",\"output\",\"input.csv\"), nrows=1)\n",
    "\n",
    "# filter out columns not needed\n",
    "cols_all = df_head.columns\n",
    "cols_to_use = [c for c in cols_all if c not in [\"hashed_organisation\", \"patient_id\"]]\n",
    "\n",
    "# import full dataset without columns not needed\n",
    "df = pd.read_csv(os.path.join(\"..\",\"output\",\"input.csv\"), usecols=cols_to_use)\n",
    "\n",
    "for col in df.columns:\n",
    "    if col in [\"age\", \"sex\"]:\n",
    "        continue\n",
    "    # Most columns only contain years or NaN so we can store them as\n",
    "    # float16s, which saves a lot of memory\n",
    "    df[col] = df[col].astype(\"float16\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create ageband"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "agebands = ['16-39', '40-69', '70+']\n",
    "conditions = [\n",
    "    (df['age'] >= 16) & (df['age'] < 40),\n",
    "    (df['age'] >= 40) & (df['age'] < 70),\n",
    "    (df['age'] >= 70) & (df['age'] < 120)]\n",
    "choices = agebands\n",
    "df['ageband'] = np.select(conditions, choices, default=np.nan)\n",
    "\n",
    "# filter to largest sex groups\n",
    "df['sex'] = df['sex'].replace(['I','U'], np.nan)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summarise data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# list columns of interest \n",
    "cols_allyears = [c for c in df.columns if c not in [\"age\",\"patient_id\"]]\n",
    "cols_recent = [\"preg\", \"pregdel\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter to valid sexes and agegroups only\n",
    "df1 = df.copy().loc[(df[\"sex\"].isin([\"F\",\"M\"])) & (df[\"ageband\"].isin(agebands))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate population denominators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th colspan=\"2\" halign=\"left\">16-39</th>\n",
       "      <th colspan=\"2\" halign=\"left\">40-69</th>\n",
       "      <th colspan=\"2\" halign=\"left\">70+</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>total_population</th>\n",
       "      <td>1500</td>\n",
       "      <td>1475</td>\n",
       "      <td>1837</td>\n",
       "      <td>1857</td>\n",
       "      <td>629</td>\n",
       "      <td>632</td>\n",
       "      <td>7930</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ageband          16-39       40-69        70+      total\n",
       "sex                  F     M     F     M    F    M      \n",
       "total_population  1500  1475  1837  1857  629  632  7930"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "out2 = df1.groupby([\"ageband\", \"sex\"])[[\"registered\"]].count().rename(columns={\"registered\":\"total_population\"}).transpose()\n",
    "\n",
    "# calculate total population across all ages and sexes\n",
    "out2[\"total\"] = out2.sum(axis=1)\n",
    "\n",
    "out2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Codelist counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th colspan=\"2\" halign=\"left\">16-39</th>\n",
       "      <th colspan=\"2\" halign=\"left\">40-69</th>\n",
       "      <th colspan=\"2\" halign=\"left\">70+</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>shield</th>\n",
       "      <td>152</td>\n",
       "      <td>140</td>\n",
       "      <td>173</td>\n",
       "      <td>193</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>smhres</th>\n",
       "      <td>145</td>\n",
       "      <td>118</td>\n",
       "      <td>191</td>\n",
       "      <td>207</td>\n",
       "      <td>61</td>\n",
       "      <td>58</td>\n",
       "      <td>780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>spln_cov</th>\n",
       "      <td>147</td>\n",
       "      <td>156</td>\n",
       "      <td>195</td>\n",
       "      <td>171</td>\n",
       "      <td>80</td>\n",
       "      <td>61</td>\n",
       "      <td>810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>registered</th>\n",
       "      <td>1500</td>\n",
       "      <td>1475</td>\n",
       "      <td>1837</td>\n",
       "      <td>1857</td>\n",
       "      <td>629</td>\n",
       "      <td>632</td>\n",
       "      <td>7930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total_population</th>\n",
       "      <td>1500</td>\n",
       "      <td>1475</td>\n",
       "      <td>1837</td>\n",
       "      <td>1857</td>\n",
       "      <td>629</td>\n",
       "      <td>632</td>\n",
       "      <td>7930</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ageband          16-39       40-69        70+      total\n",
       "sex                  F     M     F     M    F    M      \n",
       "shield             152   140   173   193   60   70   788\n",
       "smhres             145   118   191   207   61   58   780\n",
       "spln_cov           147   156   195   171   80   61   810\n",
       "registered        1500  1475  1837  1857  629  632  7930\n",
       "total_population  1500  1475  1837  1857  629  632  7930"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# for codes that are only relevant if recent (pregnancy/delivery), remove any older dates\n",
    "for c in cols_recent:\n",
    "    df1.loc[(df1[c]<2020), c] = np.nan\n",
    "\n",
    "# summarise by age and gender\n",
    "out = df1[cols_allyears].groupby([\"ageband\", \"sex\"]).count().transpose()\n",
    "\n",
    "# suppress low numbers\n",
    "out = out.replace([0,1,2,3,4,5],0)\n",
    "\n",
    "# calculate total count for each codelist across all ages and sexes\n",
    "out[\"total\"] = out.sum(axis=1)\n",
    "\n",
    "# add population denominators\n",
    "out = pd.concat([out,out2])\n",
    "\n",
    "display(out.tail())\n",
    "\n",
    "# export to csv\n",
    "\n",
    "out_exp = out.copy()\n",
    "out_exp = 5*((out_exp/5).round(0)).astype(int)\n",
    "out_exp = out_exp.replace([0,5], \"<8\")\n",
    "\n",
    "# additional check for small numbers of suppressed small numbers\n",
    "for i in out_exp.index:\n",
    "    check = out_exp.transpose().groupby(i).agg({i:\"count\"})\n",
    "    if \"<8\" in check.index:\n",
    "        # if only 1-2 suppressed value(s) appears in row, apply additional suppression\n",
    "        if (check.loc[\"<8\"][0]<=2) & (10 in check.index):\n",
    "            out_exp = out_exp.replace([\"<8\", 10], \"<13\")\n",
    "        elif (check.loc[\"<8\"][0]<=2) & (10 not in check.index):\n",
    "            out_exp = out_exp.replace([\"<8\", 15], \"<18\")\n",
    "\n",
    "out_exp.to_csv(os.path.join(\"..\",\"safe-outputs\",f\"code-counts-by-age-and-sex{suffix}.csv\"))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Codelist prevalence rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th colspan=\"2\" halign=\"left\">16-39</th>\n",
       "      <th colspan=\"2\" halign=\"left\">40-69</th>\n",
       "      <th colspan=\"2\" halign=\"left\">70+</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ast</th>\n",
       "      <td>90.7</td>\n",
       "      <td>97.6</td>\n",
       "      <td>101.8</td>\n",
       "      <td>107.7</td>\n",
       "      <td>108.1</td>\n",
       "      <td>88.6</td>\n",
       "      <td>99.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>astadm</th>\n",
       "      <td>92.7</td>\n",
       "      <td>100.3</td>\n",
       "      <td>100.7</td>\n",
       "      <td>99.6</td>\n",
       "      <td>100.2</td>\n",
       "      <td>113.9</td>\n",
       "      <td>99.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>astrx</th>\n",
       "      <td>91.3</td>\n",
       "      <td>92.2</td>\n",
       "      <td>96.4</td>\n",
       "      <td>110.9</td>\n",
       "      <td>98.6</td>\n",
       "      <td>104.4</td>\n",
       "      <td>98.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bmi</th>\n",
       "      <td>109.3</td>\n",
       "      <td>96.3</td>\n",
       "      <td>102.3</td>\n",
       "      <td>91.0</td>\n",
       "      <td>109.7</td>\n",
       "      <td>94.9</td>\n",
       "      <td>99.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bmi_stage</th>\n",
       "      <td>92.0</td>\n",
       "      <td>96.9</td>\n",
       "      <td>99.1</td>\n",
       "      <td>100.2</td>\n",
       "      <td>98.6</td>\n",
       "      <td>121.8</td>\n",
       "      <td>99.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carehome</th>\n",
       "      <td>99.3</td>\n",
       "      <td>104.4</td>\n",
       "      <td>106.2</td>\n",
       "      <td>95.3</td>\n",
       "      <td>109.7</td>\n",
       "      <td>102.8</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carer</th>\n",
       "      <td>100.7</td>\n",
       "      <td>101.7</td>\n",
       "      <td>95.3</td>\n",
       "      <td>102.3</td>\n",
       "      <td>84.3</td>\n",
       "      <td>98.1</td>\n",
       "      <td>98.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>chd_cov</th>\n",
       "      <td>104.0</td>\n",
       "      <td>103.1</td>\n",
       "      <td>98.5</td>\n",
       "      <td>102.9</td>\n",
       "      <td>85.9</td>\n",
       "      <td>91.8</td>\n",
       "      <td>99.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ckd15</th>\n",
       "      <td>100.7</td>\n",
       "      <td>103.1</td>\n",
       "      <td>98.0</td>\n",
       "      <td>103.9</td>\n",
       "      <td>101.7</td>\n",
       "      <td>91.8</td>\n",
       "      <td>100.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ckd35</th>\n",
       "      <td>102.0</td>\n",
       "      <td>98.3</td>\n",
       "      <td>104.5</td>\n",
       "      <td>100.2</td>\n",
       "      <td>95.4</td>\n",
       "      <td>104.4</td>\n",
       "      <td>101.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ckd_cov</th>\n",
       "      <td>103.3</td>\n",
       "      <td>89.5</td>\n",
       "      <td>108.3</td>\n",
       "      <td>98.5</td>\n",
       "      <td>90.6</td>\n",
       "      <td>85.4</td>\n",
       "      <td>98.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cld</th>\n",
       "      <td>98.7</td>\n",
       "      <td>105.1</td>\n",
       "      <td>95.8</td>\n",
       "      <td>95.9</td>\n",
       "      <td>98.6</td>\n",
       "      <td>107.6</td>\n",
       "      <td>99.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cns_cov</th>\n",
       "      <td>92.7</td>\n",
       "      <td>103.7</td>\n",
       "      <td>89.3</td>\n",
       "      <td>109.3</td>\n",
       "      <td>108.1</td>\n",
       "      <td>91.8</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cov1decl</th>\n",
       "      <td>109.3</td>\n",
       "      <td>100.3</td>\n",
       "      <td>103.4</td>\n",
       "      <td>98.5</td>\n",
       "      <td>92.2</td>\n",
       "      <td>98.1</td>\n",
       "      <td>101.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cov2decl</th>\n",
       "      <td>102.0</td>\n",
       "      <td>103.7</td>\n",
       "      <td>106.7</td>\n",
       "      <td>115.8</td>\n",
       "      <td>106.5</td>\n",
       "      <td>90.2</td>\n",
       "      <td>106.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covadm1</th>\n",
       "      <td>112.0</td>\n",
       "      <td>107.1</td>\n",
       "      <td>101.8</td>\n",
       "      <td>98.0</td>\n",
       "      <td>109.7</td>\n",
       "      <td>99.7</td>\n",
       "      <td>104.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covadm2</th>\n",
       "      <td>129.3</td>\n",
       "      <td>94.2</td>\n",
       "      <td>89.8</td>\n",
       "      <td>100.7</td>\n",
       "      <td>103.3</td>\n",
       "      <td>96.5</td>\n",
       "      <td>102.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covcontra</th>\n",
       "      <td>108.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>93.6</td>\n",
       "      <td>100.2</td>\n",
       "      <td>100.2</td>\n",
       "      <td>98.1</td>\n",
       "      <td>99.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covrx</th>\n",
       "      <td>94.0</td>\n",
       "      <td>115.3</td>\n",
       "      <td>94.2</td>\n",
       "      <td>94.8</td>\n",
       "      <td>111.3</td>\n",
       "      <td>98.1</td>\n",
       "      <td>99.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>diab</th>\n",
       "      <td>90.0</td>\n",
       "      <td>109.8</td>\n",
       "      <td>96.4</td>\n",
       "      <td>103.4</td>\n",
       "      <td>77.9</td>\n",
       "      <td>126.6</td>\n",
       "      <td>100.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dmres</th>\n",
       "      <td>108.7</td>\n",
       "      <td>98.3</td>\n",
       "      <td>99.1</td>\n",
       "      <td>96.9</td>\n",
       "      <td>109.7</td>\n",
       "      <td>90.2</td>\n",
       "      <td>100.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>domcare</th>\n",
       "      <td>100.7</td>\n",
       "      <td>113.2</td>\n",
       "      <td>95.3</td>\n",
       "      <td>93.2</td>\n",
       "      <td>114.5</td>\n",
       "      <td>82.3</td>\n",
       "      <td>99.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eth2001</th>\n",
       "      <td>96.7</td>\n",
       "      <td>100.3</td>\n",
       "      <td>100.2</td>\n",
       "      <td>101.8</td>\n",
       "      <td>117.6</td>\n",
       "      <td>96.5</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eth_norecord</th>\n",
       "      <td>111.3</td>\n",
       "      <td>101.0</td>\n",
       "      <td>87.6</td>\n",
       "      <td>103.4</td>\n",
       "      <td>108.1</td>\n",
       "      <td>104.4</td>\n",
       "      <td>101.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eth_notgiptref</th>\n",
       "      <td>102.0</td>\n",
       "      <td>98.3</td>\n",
       "      <td>94.2</td>\n",
       "      <td>90.5</td>\n",
       "      <td>106.5</td>\n",
       "      <td>123.4</td>\n",
       "      <td>98.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eth_notstated</th>\n",
       "      <td>92.0</td>\n",
       "      <td>96.3</td>\n",
       "      <td>112.1</td>\n",
       "      <td>91.5</td>\n",
       "      <td>87.4</td>\n",
       "      <td>99.7</td>\n",
       "      <td>97.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hhld_imdef</th>\n",
       "      <td>108.7</td>\n",
       "      <td>80.7</td>\n",
       "      <td>97.4</td>\n",
       "      <td>100.7</td>\n",
       "      <td>87.4</td>\n",
       "      <td>94.9</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>immdx_cov</th>\n",
       "      <td>109.3</td>\n",
       "      <td>105.8</td>\n",
       "      <td>91.5</td>\n",
       "      <td>91.0</td>\n",
       "      <td>100.2</td>\n",
       "      <td>115.5</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>immrx</th>\n",
       "      <td>90.7</td>\n",
       "      <td>97.6</td>\n",
       "      <td>104.5</td>\n",
       "      <td>95.9</td>\n",
       "      <td>101.7</td>\n",
       "      <td>120.3</td>\n",
       "      <td>99.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>learndis</th>\n",
       "      <td>104.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>101.8</td>\n",
       "      <td>104.5</td>\n",
       "      <td>98.6</td>\n",
       "      <td>104.4</td>\n",
       "      <td>102.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longres</th>\n",
       "      <td>101.3</td>\n",
       "      <td>96.3</td>\n",
       "      <td>98.0</td>\n",
       "      <td>107.7</td>\n",
       "      <td>95.4</td>\n",
       "      <td>93.4</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>non_eth2001</th>\n",
       "      <td>98.7</td>\n",
       "      <td>105.1</td>\n",
       "      <td>100.2</td>\n",
       "      <td>103.4</td>\n",
       "      <td>111.3</td>\n",
       "      <td>101.3</td>\n",
       "      <td>102.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nonshield</th>\n",
       "      <td>89.3</td>\n",
       "      <td>101.7</td>\n",
       "      <td>101.8</td>\n",
       "      <td>106.6</td>\n",
       "      <td>87.4</td>\n",
       "      <td>109.2</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>notcarer</th>\n",
       "      <td>110.7</td>\n",
       "      <td>103.7</td>\n",
       "      <td>92.5</td>\n",
       "      <td>96.9</td>\n",
       "      <td>93.8</td>\n",
       "      <td>106.0</td>\n",
       "      <td>100.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nursehome</th>\n",
       "      <td>84.0</td>\n",
       "      <td>95.6</td>\n",
       "      <td>96.4</td>\n",
       "      <td>111.5</td>\n",
       "      <td>103.3</td>\n",
       "      <td>110.8</td>\n",
       "      <td>99.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>preg</th>\n",
       "      <td>0.0</td>\n",
       "      <td>4.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pregdel</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>resp_cov</th>\n",
       "      <td>97.3</td>\n",
       "      <td>96.9</td>\n",
       "      <td>98.5</td>\n",
       "      <td>98.5</td>\n",
       "      <td>116.1</td>\n",
       "      <td>96.5</td>\n",
       "      <td>99.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sev_mental</th>\n",
       "      <td>102.7</td>\n",
       "      <td>93.6</td>\n",
       "      <td>99.6</td>\n",
       "      <td>96.9</td>\n",
       "      <td>100.2</td>\n",
       "      <td>112.3</td>\n",
       "      <td>99.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sev_obesity</th>\n",
       "      <td>88.0</td>\n",
       "      <td>84.7</td>\n",
       "      <td>102.9</td>\n",
       "      <td>108.2</td>\n",
       "      <td>108.1</td>\n",
       "      <td>118.7</td>\n",
       "      <td>99.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shield</th>\n",
       "      <td>101.3</td>\n",
       "      <td>94.9</td>\n",
       "      <td>94.2</td>\n",
       "      <td>103.9</td>\n",
       "      <td>95.4</td>\n",
       "      <td>110.8</td>\n",
       "      <td>99.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>smhres</th>\n",
       "      <td>96.7</td>\n",
       "      <td>80.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>111.5</td>\n",
       "      <td>97.0</td>\n",
       "      <td>91.8</td>\n",
       "      <td>98.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>spln_cov</th>\n",
       "      <td>98.0</td>\n",
       "      <td>105.8</td>\n",
       "      <td>106.2</td>\n",
       "      <td>92.1</td>\n",
       "      <td>127.2</td>\n",
       "      <td>96.5</td>\n",
       "      <td>102.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>registered</th>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total_population</th>\n",
       "      <td>1500.0</td>\n",
       "      <td>1475.0</td>\n",
       "      <td>1837.0</td>\n",
       "      <td>1857.0</td>\n",
       "      <td>629.0</td>\n",
       "      <td>632.0</td>\n",
       "      <td>7930.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ageband            16-39           40-69             70+           total\n",
       "sex                    F       M       F       M       F       M        \n",
       "ast                 90.7    97.6   101.8   107.7   108.1    88.6    99.7\n",
       "astadm              92.7   100.3   100.7    99.6   100.2   113.9    99.9\n",
       "astrx               91.3    92.2    96.4   110.9    98.6   104.4    98.9\n",
       "bmi                109.3    96.3   102.3    91.0   109.7    94.9    99.9\n",
       "bmi_stage           92.0    96.9    99.1   100.2    98.6   121.8    99.4\n",
       "carehome            99.3   104.4   106.2    95.3   109.7   102.8   102.0\n",
       "carer              100.7   101.7    95.3   102.3    84.3    98.1    98.5\n",
       "chd_cov            104.0   103.1    98.5   102.9    85.9    91.8    99.9\n",
       "ckd15              100.7   103.1    98.0   103.9   101.7    91.8   100.6\n",
       "ckd35              102.0    98.3   104.5   100.2    95.4   104.4   101.1\n",
       "ckd_cov            103.3    89.5   108.3    98.5    90.6    85.4    98.4\n",
       "cld                 98.7   105.1    95.8    95.9    98.6   107.6    99.2\n",
       "cns_cov             92.7   103.7    89.3   109.3   108.1    91.8    99.0\n",
       "cov1decl           109.3   100.3   103.4    98.5    92.2    98.1   101.5\n",
       "cov2decl           102.0   103.7   106.7   115.8   106.5    90.2   106.1\n",
       "covadm1            112.0   107.1   101.8    98.0   109.7    99.7   104.3\n",
       "covadm2            129.3    94.2    89.8   100.7   103.3    96.5   102.3\n",
       "covcontra          108.0    99.0    93.6   100.2   100.2    98.1    99.7\n",
       "covrx               94.0   115.3    94.2    94.8   111.3    98.1    99.9\n",
       "diab                90.0   109.8    96.4   103.4    77.9   126.6   100.3\n",
       "dmres              108.7    98.3    99.1    96.9   109.7    90.2   100.4\n",
       "domcare            100.7   113.2    95.3    93.2   114.5    82.3    99.6\n",
       "eth2001             96.7   100.3   100.2   101.8   117.6    96.5   101.0\n",
       "eth_norecord       111.3   101.0    87.6   103.4   108.1   104.4   101.3\n",
       "eth_notgiptref     102.0    98.3    94.2    90.5   106.5   123.4    98.9\n",
       "eth_notstated       92.0    96.3   112.1    91.5    87.4    99.7    97.6\n",
       "hhld_imdef         108.7    80.7    97.4   100.7    87.4    94.9    96.2\n",
       "immdx_cov          109.3   105.8    91.5    91.0   100.2   115.5   100.0\n",
       "immrx               90.7    97.6   104.5    95.9   101.7   120.3    99.6\n",
       "learndis           104.0    99.0   101.8   104.5    98.6   104.4   102.3\n",
       "longres            101.3    96.3    98.0   107.7    95.4    93.4   100.0\n",
       "non_eth2001         98.7   105.1   100.2   103.4   111.3   101.3   102.5\n",
       "nonshield           89.3   101.7   101.8   106.6    87.4   109.2   100.0\n",
       "notcarer           110.7   103.7    92.5    96.9    93.8   106.0   100.3\n",
       "nursehome           84.0    95.6    96.4   111.5   103.3   110.8    99.1\n",
       "preg                 0.0     4.1     0.0     0.0     0.0     0.0     0.8\n",
       "pregdel              0.0     0.0     4.4     0.0     0.0     0.0     1.0\n",
       "resp_cov            97.3    96.9    98.5    98.5   116.1    96.5    99.2\n",
       "sev_mental         102.7    93.6    99.6    96.9   100.2   112.3    99.5\n",
       "sev_obesity         88.0    84.7   102.9   108.2   108.1   118.7    99.6\n",
       "shield             101.3    94.9    94.2   103.9    95.4   110.8    99.4\n",
       "smhres              96.7    80.0   104.0   111.5    97.0    91.8    98.4\n",
       "spln_cov            98.0   105.8   106.2    92.1   127.2    96.5   102.1\n",
       "registered        1000.0  1000.0  1000.0  1000.0  1000.0  1000.0  1000.0\n",
       "total_population  1500.0  1475.0  1837.0  1857.0   629.0   632.0  7930.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate rates\n",
    "for i in out.index.drop(\"total_population\"):\n",
    "    out.loc[i] = (1000*out.loc[i]/out.loc[\"total_population\"]).round(1)\n",
    "    \n",
    "# export to csv    \n",
    "out.to_csv(os.path.join(\"..\",\"safe-outputs\",f\"code-prevalence-by-age-and-sex{suffix}.csv\"))\n",
    "\n",
    "out"
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "all",
   "notebook_metadata_filter": "all,-language_info",
   "text_representation": {
    "extension": ".py",
    "format_name": "light",
    "format_version": "1.5",
    "jupytext_version": "1.3.3"
   }
  },
  "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}