notebooks/suicide-features-crosstab.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6f2c4dc8-df25-46bd-99da-d52bb94b1485",
   "metadata": {},
   "source": [
    "# Suicide and fatal overdoses data questions\n",
    "\n",
    "This notebook contains a cursory look of fatal overdoses and suicide data. Currently,\n",
    "the presence of previous drug history and suicidality is determined solely by ambulance\n",
    "data. A future iteration should look at more carefully aggregating this historical data\n",
    "from additional data sources."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e6bc517-1016-43a6-a4d9-6494215562fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e38919c9-d453-4cca-924a-39f70acd7553",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append('../src/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37fa7b94-68d3-4eb9-a914-2a9b125e1744",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "import json\n",
    "from utils.helpers import get_database_connection, get_events\n",
    "from dateutil.relativedelta import relativedelta\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import csv\n",
    "import os\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a6a0460-bb72-4d6e-95a2-b49133fcbb8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_conn = get_database_connection()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8d99345-62d1-46e3-a73f-e365594d00bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_types = ['AMBULANCE', 'MENTAL HEALTH', 'DEATH', 'HOSPITAL', 'ARREST']\n",
    "query = \"\"\"\n",
    "with table_a as \n",
    "(\n",
    "select joid, event_date, event_type, count(*) as num_events\n",
    "from semantic.client_events\n",
    "group by 1, 2, 3\n",
    "),\n",
    "lifetime_events_table as\n",
    "(\n",
    "select joid, event_type, count(*) as lifetime_events\n",
    "from table_a\n",
    "group by joid, event_type\n",
    "),\n",
    "mex_jc as\n",
    "(\n",
    "select joid, suicide, overdosed, (suicide or overdosed) as suic_or_od\n",
    "from clean.jocojcmexoverdosessuicides j\n",
    "),\n",
    "mex_dc as \n",
    "(\n",
    "select joid, suicide, overdosed, (suicide or overdosed) as suic_or_od\n",
    "from clean.jocodcmexoverdosessuicides j\n",
    ")\n",
    "\n",
    "select \n",
    "    lea.*,\n",
    "    coalesce(mjc.suicide, mdc.suicide, false) as suicide,\n",
    "    coalesce(mjc.overdosed, mdc.overdosed, false) as overdosed,\n",
    "    coalesce(mjc.suic_or_od, mdc.suic_or_od, false) as suic_or_od\n",
    "from lifetime_events_table lea\n",
    "left join mex_jc mjc on lea.joid = mjc.joid\n",
    "left join mex_dc mdc on lea.joid = mdc.joid\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql(query, db_conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "559973fe-626e-4555-8b5b-27fc92d4b4a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt = df.pivot_table(index='joid', columns=['event_type'], aggfunc=sum, fill_value=0)['lifetime_events']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "008ce192-45ab-4736-a0fd-d30396232d2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "labels_dedup = df[['joid', 'suic_or_od']].drop_duplicates().set_index(['joid'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a6496dd-6c9e-4ad7-bfdd-5e13a764dd53",
   "metadata": {},
   "outputs": [],
   "source": [
    "labels_dedup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92ac56c7-935e-4d7e-bef9-ee98bfee57cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "sum(pt['DEATH'] > 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe449a4d-7d53-4dbd-a28e-c297ec0b7efc",
   "metadata": {},
   "outputs": [],
   "source": [
    "sum(df['event_type'] == 'DEATH')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b53429a0-74f6-4f55-bbe1-327a54d3f2fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_df = pt.join(labels_dedup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d4feace-9578-4acc-9080-c6f85ab69f1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fec7140d-caea-4a4f-87f5-93e2a9269311",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_df.iloc[joined_df['ARREST'].argmax()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a438f64-c527-42d7-b75d-71ff5673b0af",
   "metadata": {},
   "outputs": [],
   "source": [
    "sum(joined_df['DEATH'] > 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21e28758-bd90-4365-8aca-453e40794d00",
   "metadata": {},
   "source": [
    "## Aggregate information for true and false labels (suicide or fatal overdose)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc22bf2d-e5c8-45ad-a9b2-dee640e2867e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1 event per day in which there was _some_ interaction\n",
    "pd.set_option('display.float_format', '{:.2f}'.format)\n",
    "labeled_dfs = []\n",
    "display_dfs = []\n",
    "for label_val in [True, False]:\n",
    "    df_where_label = joined_df[joined_df['suic_or_od'] == label_val]\n",
    "    labeled_dfs.append(df_where_label )\n",
    "    print(f'num rows: {len(df_where_label.index)}; label={label_val}')\n",
    "    display_df = joined_df[joined_df['suic_or_od'] == label_val].describe().loc[['mean', 'std', 'min', 'max', '50%']]\n",
    "    display_dfs.append(display_df)\n",
    "    heatmap_df = joined_df[joined_df['suic_or_od'] == label_val].describe().loc[['mean', '50%']]\n",
    "    display(display_df)\n",
    "df1, df2 = display_dfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95b6d207-4653-4211-8960-dfe4c6e482ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('label true minus label false')\n",
    "diff_df = df1 - df2\n",
    "display((df1 - df2).drop(columns=['DEATH']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e830297e-05c0-4653-a467-f43562af0846",
   "metadata": {},
   "outputs": [],
   "source": [
    "normalized_df = joined_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed3cb383-7206-4f54-9896-4fbdc79819fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "normalized_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cac2f986-e5f9-4daa-a4f5-89b17c28f13d",
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in normalized_df.drop(columns=['DEATH', 'suic_or_od']):\n",
    "    normalized_df[column] = (normalized_df[column] - normalized_df[column].mean()) / normalized_df[column].std()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0582fa92-ad08-4258-8392-ce7168f9d470",
   "metadata": {},
   "outputs": [],
   "source": [
    "normalized_df['AMBULANCE']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ae8b856-9634-4135-a3b0-3435807afc82",
   "metadata": {},
   "source": [
    "## Normalize the number of events for easier comparison\n",
    "\n",
    "We use population mean and std to normalize. Since the class with false labels is much larger, the mean and std are very close to the mean and std of the false label population."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8556e1e-fefa-47ff-a56c-20339e22628a",
   "metadata": {},
   "outputs": [],
   "source": [
    "normalized_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dde43bf5-703a-4012-8b0c-532126530923",
   "metadata": {},
   "outputs": [],
   "source": [
    "normed_df_true, normed_df_false = normalized_df[normalized_df['suic_or_od'] == True], normalized_df[normalized_df['suic_or_od'] == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f128ea9-d405-4444-a263-08b277b4183d",
   "metadata": {},
   "outputs": [],
   "source": [
    "normed_df_true.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ce3542f-b244-4be2-ba32-5b34cc5cc2c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "normed_df_false['HOSPITAL'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d49dc22-fada-42d3-83ba-a04c3114c40e",
   "metadata": {},
   "outputs": [],
   "source": [
    "normed_df_false.drop(columns=['DEATH', 'suic_or_od']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86b039cb-b19e-4421-8f35-b6bf17c42424",
   "metadata": {},
   "outputs": [],
   "source": [
    "normed_df_true.drop(columns=['DEATH', 'suic_or_od']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a26f4ec-ec6a-421a-9803-9bfa80a55b2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "melt = pd.melt(\n",
    "normed_df_true.drop(columns=['DEATH', 'suic_or_od'])\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbf8095c-bb44-4c29-bd64-d15cfa7810a1",
   "metadata": {},
   "source": [
    "### Plot comments \n",
    "\n",
    "Since the population with label=false is much greater they essnetially dominate the mean and all their values are close to 0.\n",
    "In the graph below we can observe that individuals with a true label are, on average, $.1-.3$ std above those with label false\n",
    "for all fields except nr of hospital visits. There seem to be outliers with many hospital visits in the label=False population;\n",
    "in all likelihood, this is why hospitalizations are lower for the true labels.\n",
    "\n",
    "A follow-up plot might consider these populations without extreme outliers. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f05cd19-e823-4fc0-8458-4fcf6bbe89ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the dataframe with normalized number of events over the whole population (both label values). \n",
    "# Since the population with label=false is much greater they essnetially dominate the mean and all their values are close to 0.\n",
    "# In the graph below we can observe that individuals with a true label are, on average, .1-.3 std above those with label false\n",
    "# for all fields except nr of hospital visits. There are a few outliers with many hospital visits in the label=False population.\n",
    "sns.barplot(data=melt, x='variable', y='value')\n",
    "plt.title('Normalized number of events for individuals with true label')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08d30539-e2be-43fe-9d69-f9ab04868f9d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}