notebooks/prevalence_deepdive.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\",\"by-codelist\"), exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dtypes={\"sex\":\"category\"}\n",
    "\n",
    "df = pd.read_csv(os.path.join(\"..\",\"output\",\"input_with_codes.csv\"), dtype=dtypes)\n",
    "\n",
    "if \"hashed_organisation\" in df.columns:\n",
    "    df = df.drop(\"hashed_organisation\", axis=1)\n",
    "\n",
    "for col in df.columns:\n",
    "    if col in [\"patient_id\", \"age\", \"sex\"]:\n",
    "        continue\n",
    "    if \"_date\" not in col: # float16 not suitable for snomed codes\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\")\n"
   ]
  },
  {
   "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'] = np.where(df['sex'].isin(['I','U']), np.nan, df[\"sex\"])"
   ]
  },
  {
   "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\"]) & (\"_date\" not in c))]\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>1590</td>\n",
       "      <td>1520</td>\n",
       "      <td>1863</td>\n",
       "      <td>1777</td>\n",
       "      <td>605</td>\n",
       "      <td>627</td>\n",
       "      <td>7982</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  1590  1520  1863  1777  605  627  7982"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "out2 = df1.groupby([\"ageband\", \"sex\"])[[\"patient_id\"]].nunique().rename(columns={\"patient_id\":\"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 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></th>\n",
       "      <th>registered</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">16-39</th>\n",
       "      <th>F</th>\n",
       "      <td>0</td>\n",
       "      <td>47.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>1</td>\n",
       "      <td>952.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>1</td>\n",
       "      <td>950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">40-69</th>\n",
       "      <th>F</th>\n",
       "      <td>0</td>\n",
       "      <td>50.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>1</td>\n",
       "      <td>949.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>0</td>\n",
       "      <td>48.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>1</td>\n",
       "      <td>951.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">70+</th>\n",
       "      <th>F</th>\n",
       "      <td>0</td>\n",
       "      <td>54.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>1</td>\n",
       "      <td>945.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>0</td>\n",
       "      <td>52.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>1</td>\n",
       "      <td>947.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             registered  rate_per_1000\n",
       "ageband sex                           \n",
       "16-39   F             0           47.8\n",
       "        F             1          952.2\n",
       "        M             0           50.0\n",
       "        M             1          950.0\n",
       "40-69   F             0           50.5\n",
       "        F             1          949.5\n",
       "        M             0           48.4\n",
       "        M             1          951.6\n",
       "70+     F             0           54.5\n",
       "        F             1          945.5\n",
       "        M             0           52.6\n",
       "        M             1          947.4"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>bmi_stage</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">16-39</th>\n",
       "      <th>F</th>\n",
       "      <td>35425004</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>819948005</td>\n",
       "      <td>52.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>35425004</td>\n",
       "      <td>45.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>819948005</td>\n",
       "      <td>41.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">40-69</th>\n",
       "      <th>F</th>\n",
       "      <td>35425004</td>\n",
       "      <td>47.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>819948005</td>\n",
       "      <td>47.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>35425004</td>\n",
       "      <td>56.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>819948005</td>\n",
       "      <td>52.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">70+</th>\n",
       "      <th>F</th>\n",
       "      <td>35425004</td>\n",
       "      <td>49.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>819948005</td>\n",
       "      <td>52.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>35425004</td>\n",
       "      <td>44.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>819948005</td>\n",
       "      <td>54.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             bmi_stage  rate_per_1000\n",
       "ageband sex                          \n",
       "16-39   F     35425004           43.4\n",
       "        F    819948005           52.2\n",
       "        M     35425004           45.4\n",
       "        M    819948005           41.4\n",
       "40-69   F     35425004           47.8\n",
       "        F    819948005           47.2\n",
       "        M     35425004           56.8\n",
       "        M    819948005           52.3\n",
       "70+     F     35425004           49.6\n",
       "        F    819948005           52.9\n",
       "        M     35425004           44.7\n",
       "        M    819948005           54.2"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>carehome</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">16-39</th>\n",
       "      <th>F</th>\n",
       "      <td>1092561000000107</td>\n",
       "      <td>93.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>1092561000000107</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">40-69</th>\n",
       "      <th>F</th>\n",
       "      <td>1092561000000107</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>158944006</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>1092561000000107</td>\n",
       "      <td>93.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">70+</th>\n",
       "      <th>F</th>\n",
       "      <td>1092561000000107</td>\n",
       "      <td>79.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>1092561000000107</td>\n",
       "      <td>89.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     carehome  rate_per_1000\n",
       "ageband sex                                 \n",
       "16-39   F    1092561000000107           93.7\n",
       "        M    1092561000000107          100.0\n",
       "40-69   F    1092561000000107          102.0\n",
       "        M           158944006            6.2\n",
       "        M    1092561000000107           93.4\n",
       "70+     F    1092561000000107           79.3\n",
       "        M    1092561000000107           89.3"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>carer</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">16-39</th>\n",
       "      <th>F</th>\n",
       "      <td>276048003</td>\n",
       "      <td>55.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>407542009</td>\n",
       "      <td>49.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>276048003</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>407542009</td>\n",
       "      <td>53.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">40-69</th>\n",
       "      <th>F</th>\n",
       "      <td>276048003</td>\n",
       "      <td>41.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>407542009</td>\n",
       "      <td>42.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>276048003</td>\n",
       "      <td>53.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>407542009</td>\n",
       "      <td>59.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">70+</th>\n",
       "      <th>F</th>\n",
       "      <td>276048003</td>\n",
       "      <td>56.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>407542009</td>\n",
       "      <td>52.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>276048003</td>\n",
       "      <td>44.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>407542009</td>\n",
       "      <td>49.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 carer  rate_per_1000\n",
       "ageband sex                          \n",
       "16-39   F    276048003           55.3\n",
       "        F    407542009           49.1\n",
       "        M    276048003           43.4\n",
       "        M    407542009           53.9\n",
       "40-69   F    276048003           41.9\n",
       "        F    407542009           42.4\n",
       "        M    276048003           53.5\n",
       "        M    407542009           59.1\n",
       "70+     F    276048003           56.2\n",
       "        F    407542009           52.9\n",
       "        M    276048003           44.7\n",
       "        M    407542009           49.4"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>notcarer</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">16-39</th>\n",
       "      <th>F</th>\n",
       "      <td>199361000000101</td>\n",
       "      <td>50.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>506401000000109</td>\n",
       "      <td>49.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>199361000000101</td>\n",
       "      <td>51.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>506401000000109</td>\n",
       "      <td>38.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">40-69</th>\n",
       "      <th>F</th>\n",
       "      <td>199361000000101</td>\n",
       "      <td>54.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>506401000000109</td>\n",
       "      <td>54.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>199361000000101</td>\n",
       "      <td>49.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>506401000000109</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">70+</th>\n",
       "      <th>F</th>\n",
       "      <td>199361000000101</td>\n",
       "      <td>44.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>506401000000109</td>\n",
       "      <td>51.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>199361000000101</td>\n",
       "      <td>52.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>506401000000109</td>\n",
       "      <td>57.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    notcarer  rate_per_1000\n",
       "ageband sex                                \n",
       "16-39   F    199361000000101           50.9\n",
       "        F    506401000000109           49.1\n",
       "        M    199361000000101           51.3\n",
       "        M    506401000000109           38.8\n",
       "40-69   F    199361000000101           54.2\n",
       "        F    506401000000109           54.2\n",
       "        M    199361000000101           49.5\n",
       "        M    506401000000109           54.0\n",
       "70+     F    199361000000101           44.6\n",
       "        F    506401000000109           51.2\n",
       "        M    199361000000101           52.6\n",
       "        M    506401000000109           57.4"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>preg</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [preg, rate_per_1000]\n",
       "Index: []"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>pregdel</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [pregdel, rate_per_1000]\n",
       "Index: []"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>sev_mental</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">16-39</th>\n",
       "      <th>F</th>\n",
       "      <td>13746004</td>\n",
       "      <td>42.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>49468007</td>\n",
       "      <td>52.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>13746004</td>\n",
       "      <td>67.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>49468007</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">40-69</th>\n",
       "      <th>F</th>\n",
       "      <td>13746004</td>\n",
       "      <td>47.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>49468007</td>\n",
       "      <td>53.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>13746004</td>\n",
       "      <td>46.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>49468007</td>\n",
       "      <td>46.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">70+</th>\n",
       "      <th>F</th>\n",
       "      <td>13746004</td>\n",
       "      <td>69.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>49468007</td>\n",
       "      <td>33.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>13746004</td>\n",
       "      <td>55.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>49468007</td>\n",
       "      <td>47.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             sev_mental  rate_per_1000\n",
       "ageband sex                           \n",
       "16-39   F      13746004           42.1\n",
       "        F      49468007           52.8\n",
       "        M      13746004           67.8\n",
       "        M      49468007           43.4\n",
       "40-69   F      13746004           47.2\n",
       "        F      49468007           53.1\n",
       "        M      13746004           46.7\n",
       "        M      49468007           46.7\n",
       "70+     F      13746004           69.4\n",
       "        F      49468007           33.1\n",
       "        M      13746004           55.8\n",
       "        M      49468007           47.8"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>sev_obesity</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">16-39</th>\n",
       "      <th>F</th>\n",
       "      <td>408512008</td>\n",
       "      <td>28.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>819948005</td>\n",
       "      <td>31.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>914741000000103</td>\n",
       "      <td>28.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>408512008</td>\n",
       "      <td>28.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>819948005</td>\n",
       "      <td>33.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>914741000000103</td>\n",
       "      <td>37.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">40-69</th>\n",
       "      <th>F</th>\n",
       "      <td>408512008</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>819948005</td>\n",
       "      <td>29.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>914741000000103</td>\n",
       "      <td>30.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>408512008</td>\n",
       "      <td>32.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>819948005</td>\n",
       "      <td>37.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>914741000000103</td>\n",
       "      <td>33.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">70+</th>\n",
       "      <th>F</th>\n",
       "      <td>408512008</td>\n",
       "      <td>41.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>819948005</td>\n",
       "      <td>41.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>914741000000103</td>\n",
       "      <td>24.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>408512008</td>\n",
       "      <td>28.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>819948005</td>\n",
       "      <td>39.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>914741000000103</td>\n",
       "      <td>47.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 sev_obesity  rate_per_1000\n",
       "ageband sex                                \n",
       "16-39   F          408512008           28.3\n",
       "        F          819948005           31.4\n",
       "        F    914741000000103           28.3\n",
       "        M          408512008           28.3\n",
       "        M          819948005           33.6\n",
       "        M    914741000000103           37.5\n",
       "40-69   F          408512008           36.0\n",
       "        F          819948005           29.5\n",
       "        F    914741000000103           30.6\n",
       "        M          408512008           32.1\n",
       "        M          819948005           37.1\n",
       "        M    914741000000103           33.2\n",
       "70+     F          408512008           41.3\n",
       "        F          819948005           41.3\n",
       "        F    914741000000103           24.8\n",
       "        M          408512008           28.7\n",
       "        M          819948005           39.9\n",
       "        M    914741000000103           47.8"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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></th>\n",
       "      <th>smhres</th>\n",
       "      <th>rate_per_1000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ageband</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">16-39</th>\n",
       "      <th>F</th>\n",
       "      <td>41836007</td>\n",
       "      <td>25.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>698951002</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>41836007</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>698951002</td>\n",
       "      <td>21.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">40-69</th>\n",
       "      <th>F</th>\n",
       "      <td>41836007</td>\n",
       "      <td>26.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>698951002</td>\n",
       "      <td>23.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>41836007</td>\n",
       "      <td>21.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>698951002</td>\n",
       "      <td>27.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">70+</th>\n",
       "      <th>F</th>\n",
       "      <td>41836007</td>\n",
       "      <td>23.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>698951002</td>\n",
       "      <td>21.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>41836007</td>\n",
       "      <td>33.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>698951002</td>\n",
       "      <td>33.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                smhres  rate_per_1000\n",
       "ageband sex                          \n",
       "16-39   F     41836007           25.2\n",
       "        F    698951002           22.0\n",
       "        M     41836007           27.0\n",
       "        M    698951002           21.7\n",
       "40-69   F     41836007           26.3\n",
       "        F    698951002           23.1\n",
       "        M     41836007           21.9\n",
       "        M    698951002           27.6\n",
       "70+     F     41836007           23.1\n",
       "        F    698951002           21.5\n",
       "        M     41836007           33.5\n",
       "        M    698951002           33.5"
      ]
     },
     "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[f\"{c}_date\"]<2020), c] = np.nan\n",
    "\n",
    "# exclude date columns\n",
    "out = df1.copy()[cols_allyears]\n",
    "    \n",
    "# summarise most common codes for each codelist, by age and gender\n",
    "for c in out.columns.drop([\"sex\", \"ageband\"]):\n",
    "    # count number of occurrences for each code by ageband and sex:\n",
    "    out = df1.groupby([\"ageband\", \"sex\",c])[\"patient_id\"].nunique().reset_index()\n",
    "    out = out.rename(columns={\"patient_id\":\"patient_count\"})\n",
    "    \n",
    "    out[c] = out[c].astype(int)\n",
    "    \n",
    "    # rank codes by number of occurrences\n",
    "    out[\"rank\"] = out.groupby([\"ageband\", \"sex\"])[[\"patient_count\"]].rank(method=\"min\", ascending=False)\n",
    "    \n",
    "    # keep top 10 codes and those with more than 10 occurrences only\n",
    "    out = out[(out[\"rank\"]<=10) & (out[\"patient_count\"]>10)].drop(\"rank\", axis=1)\n",
    "    \n",
    "    \n",
    "    # join population denominators\n",
    "    out = out.set_index([\"ageband\", \"sex\"])\n",
    "    out = out.join(out2.transpose())    \n",
    "\n",
    "    # calculate rates\n",
    "    out[\"rate_per_1000\"] = (1000*(out[\"patient_count\"]/out[\"total_population\"]))\n",
    "    \n",
    "    # round based on values\n",
    "    dp = 1\n",
    "    if out[\"rate_per_1000\"].max() <1:\n",
    "        dp = 2\n",
    "    out[\"rate_per_1000\"] = out[\"rate_per_1000\"].round(dp)\n",
    "    \n",
    "    out = out.drop([\"patient_count\",\"total_population\"], 1)\n",
    "\n",
    "    display(out)\n",
    "\n",
    "    # export to csv    \n",
    "    out.to_csv(os.path.join(\"..\",\"safe-outputs\",\"by-codelist\",f\"code-prevalence-by-age-and-sex_{c}_{suffix}.csv\"))\n",
    "\n"
   ]
  }
 ],
 "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
}