notebooks/data_exploration_deaths_new_matching.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": "4ca29457-c62d-465c-a475-3bc36bdcaf54",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Will need to take a join of old and new matched joids as well as recalculate the num_client_rows for the first time interactions\n",
    "\"\"\"\n",
    "-- nr of old joids that dont appear in new matches\n",
    "with old_matches as \n",
    "(\n",
    "select joid, matchdate \n",
    "from clean.jocojococlient j \n",
    "where matchdate = '2022-05-23'\n",
    "),\n",
    "new_matches as \n",
    "(\n",
    "select joid, matchdate\n",
    "from clean.jocojococlient j \n",
    "where matchdate = '2022-06-26'\n",
    "),\n",
    "joined as \n",
    "(\n",
    "select coalesce(n.joid, o.joid),\n",
    "o.matchdate as oldmatchdate,\n",
    "n.matchdate as newmatchdate\n",
    "from new_matches n\n",
    "full outer join old_matches o on n.joid = o.joid\n",
    "where (o.matchdate is not null) and (n.matchdate is null)\n",
    ")\n",
    "select count(*)\n",
    "from joined\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a37c943d-f8bc-4c0e-bc6a-c8c5800f4b86",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Order or items below: 1) county abbreviation, 2) medical examiner data matching id, 3) ambulance table name, 4) ambulance id, 5) client to ambulance matching id\n",
    "jc_names = ('jc', 'id', 'jocomedactincidents', 'hash_rcdid', 'hash_sourceid')\n",
    "dc_names = ('dc', 'casenum', 'joco110hsccclientmisc2eaimpression', 'clientid', 'sourceid')  \n",
    "dfs = []\n",
    "for county, table_id, ambulance_table, ambulance_sourceid, client_sourceid in [jc_names, dc_names]:\n",
    "    q = f\"\"\"\n",
    "    with mex as \n",
    "    (\n",
    "    select \n",
    "        client.joid,\n",
    "        jcmex.suicide,\n",
    "        jcmex.overdosed,\n",
    "        client.dob,\n",
    "        jcmex.dateofdeath\n",
    "    from clean.jocojococlient client\n",
    "    join clean.jocojcmexoverdosessuicides jcmex\n",
    "    on client.joid = jcmex.joid\n",
    "    where client.matchdate = '2022-06-26'\n",
    "    UNION\n",
    "    select\n",
    "        client.joid,\n",
    "        dcmex.suicide,\n",
    "        dcmex.suicide,\n",
    "        dcmex.overdosed,\n",
    "        client.dob,\n",
    "        dcmex.dateofdeath\n",
    "    from client\n",
    "    join clean.jocodcmexoverdosessuicides dcmex\n",
    "    on client.joid = dcmex.joid\n",
    "    ),\n",
    "    amb as \n",
    "    (\n",
    "    select\n",
    "        client.joid,\n",
    "        ambulance.suicide_attempt_flag,\n",
    "        ambulance.suicidal_flag,\n",
    "        ambulance.drug_flag\n",
    "    from clean.jocojococlient client\n",
    "    join clean.{ambulance_table} ambulance\n",
    "    on client.{client_sourceid} = ambulance.{ambulance_sourceid}\n",
    "    ),\n",
    "    num_rows as \n",
    "    (\n",
    "    select\n",
    "        joid,\n",
    "        count(*) as num_client_rows\n",
    "    from\n",
    "        clean.jocojococlient client\n",
    "    where\n",
    "        client.matchdate = '2022-06-26'\n",
    "    group by joid\n",
    "    )\n",
    "\n",
    "    select distinct \n",
    "        mex.joid,\n",
    "        mex.suicide,\n",
    "        mex.overdosed,\n",
    "        mex.dob,\n",
    "        mex.dateofdeath,\n",
    "        amb.suicide_attempt_flag,\n",
    "        amb.suicidal_flag,\n",
    "        amb.drug_flag,\n",
    "        num_client_rows\n",
    "    from mex\n",
    "    left join amb\n",
    "    on mex.joid = amb.joid\n",
    "    left join num_rows on mex.joid = num_rows.joid\n",
    "    \"\"\"\n",
    "\n",
    "    result = db_conn.execute(q)\n",
    "    dfs.append(pd.DataFrame(result))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6da51f8-9709-4dfd-9b7d-f63b202efd29",
   "metadata": {},
   "source": [
    "### Start here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa1fc78c-1a7c-4694-862e-49fd9efb9965",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "    -- joids in the medical examiner data \n",
    "    select distinct \n",
    "        jcmex.joid,\n",
    "        dateofdeath\n",
    "    from clean.jocojcmexoverdosessuicides jcmex\n",
    "    where suicide or overdosed\n",
    "    and joid is not null\n",
    "    UNION\n",
    "    select distinct\n",
    "        dcmex.joid,\n",
    "        dateofdeath\n",
    "    from clean.jocodcmexoverdosessuicides dcmex\n",
    "    where (suicide or overdosed)\n",
    "    and joid is not null\n",
    "\"\"\"\n",
    "mex_df = pd.read_sql(q, db_conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c47ae47-dfc4-4a77-af2c-3e6b99bdb23c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# All joids present in the medical examiner data\n",
    "mex_joids = list(mex_df['joid'].values)\n",
    "# mex_suicide_joids = list(mex_df[mex_df['suicide'] == True]['joid'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffb2c3fe-6cd8-4f82-af3e-f2cd2f07906e",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(mex_joids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98f047bc-f303-4eee-81e7-09a24b025663",
   "metadata": {},
   "outputs": [],
   "source": [
    "mex_joids_str = ', '.join([f\"'{mex_joid}'\" for mex_joid in mex_joids])\n",
    "\n",
    "db_conn = get_database_connection()\n",
    "q = f\"\"\"\n",
    "select distinct joid\n",
    "from clean.jocojococlient client\n",
    "where joid in ({mex_joids_str})\n",
    "and source not similar to '.+[JD]CMEX.+'\n",
    "\"\"\"\n",
    "q = f\"\"\"\n",
    "select distinct joid, count(source)\n",
    "from clean.jocojococlient client\n",
    "where joid in ({mex_joids_str})\n",
    "and source not similar to '.+[JD]CMEX.+'\n",
    "group by joid\n",
    "order by count(source) asc\n",
    "\"\"\"\n",
    "# print(q)\n",
    "# ('JOCODCMEXOVERDOSESSUICIDES.CASENUM', 'JOCOJCMEXOVERDOSESSUICIDES.ID')\n",
    "# print(q)\n",
    "query_df = pd.read_sql(q, db_conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6e290ff-568b-4519-a950-e795b47284dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "sum(query_df['count'] > 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3573c798-9e84-47cf-aa62-2a5e378fac29",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(query_df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64ad4ad5-6e0a-4513-95a0-d7fbe14574f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "joids_multi_interaction = list(query_df['joid'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7aa9cdb2-6bd4-4b9d-b0d4-aa054216347e",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(joids_multi_interaction)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "488dccd6-5bc9-497d-a19e-1673b67bc481",
   "metadata": {},
   "outputs": [],
   "source": [
    "joids_single_interaction = set(mex_joids) - set(joids_multi_interaction)\n",
    "assert not (set(joids_multi_interaction) - set(mex_joids))  # This set should be empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bc7347c-5ffb-47f7-ba05-a858f0e1390f",
   "metadata": {},
   "outputs": [],
   "source": [
    "joids_single_interaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c34a20d-bc4a-46b0-9ddc-b7a8c8893814",
   "metadata": {},
   "outputs": [],
   "source": [
    "percent = len(joids_single_interaction) / (len(joids_single_interaction) + len(mex_joids))\n",
    "print(f' joids in the medical examiner data, {len(joids_single_interaction)} out of {len(mex_joids)} ({percent:.2%}) have no previous data.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75eac9af-ebb9-4a4c-ac93-936e1050686c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for which values do we not have \n",
    "\n",
    "# num_failures = 0\n",
    "# for joid in list(joids_multi_interaction):\n",
    "#     q = f\"\"\"\n",
    "    \n",
    "#     -- joids in the medical examiner data \n",
    "#     with medex_joids as\n",
    "#     (\n",
    "#     select distinct \n",
    "#         jcmex.joid, dateofdeath\n",
    "#     from clean.jocojcmexoverdosessuicides jcmex\n",
    "#     where suicide or overdosed\n",
    "#     and joid is not null\n",
    "#     UNION\n",
    "#     select distinct\n",
    "#         dcmex.joid, dateofdeath\n",
    "#     from clean.jocodcmexoverdosessuicides dcmex\n",
    "#     where (suicide or overdosed)\n",
    "#     and joid is not null\n",
    "#     ),\n",
    "#     events_before_death as \n",
    "#     (\n",
    "#     select *, medex_joids.dateofdeath\n",
    "#     from semantic.client_events ce\n",
    "#     join medex_joids on medex_joids.joid = ce.joid\n",
    "#     where ce.joid = '{joid}'\n",
    "#     and event_date < dateofdeath\n",
    "#     limit 5\n",
    "#     )\n",
    "#     select count(*)\n",
    "#     from events_before_death\n",
    "#     \"\"\"\n",
    "#     df = pd.read_sql(q, db_conn)\n",
    "#     # df = df.sort_values(by='event_date')\n",
    "#     # print('testing joid ', joid)\n",
    "#     if not all(df['count'] > 0):\n",
    "#         num_failures += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc82a43f-ad49-44f6-b6e6-a47cb08624a3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1c2ea44-0dfc-4dd7-b086-046844e29b01",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's ensure this code is bug free. For each multi interaction joid, verify it is in the medical examiner data\n",
    "# with a suicide or fatal od and the joid is in the client table with a source other than medical examiner data\n",
    "db_conn = get_database_connection()\n",
    "for joid in list(joids_multi_interaction)[:10]:\n",
    "    date_of_death = mex_df[mex_df['joid'] == joid]['dateofdeath']\n",
    "    q = f\"\"\"\n",
    "    select distinct source\n",
    "    from clean.jocojococlient client\n",
    "    where source not similar to '.+[JD]CMEX.+'\n",
    "    and joid = '{joid}'\n",
    "    limit 5;\n",
    "    \"\"\"\n",
    "    df = pd.read_sql(q, db_conn)\n",
    "    # display(df)\n",
    "    if not len(df.index):\n",
    "        print('failed on joid ', joid)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1581f9dd-8080-4682-aec8-5d298c017017",
   "metadata": {},
   "source": [
    "---\n",
    "End here \n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "196a11d0-e841-40e9-8206-8d2ae116ebd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "dup_joid = np.argmax(mex_df['joid'].duplicated())\n",
    "print(dup_joid)\n",
    "mex_df['joid'].iloc[int(dup_joid)]\n",
    "# mex_df.drop_duplicates(subset=['joid'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14cedf34-094a-41d9-b1b8-4dfc34fe4054",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Order or items below: 1) county abbreviation, 2) medical examiner data matching id, 3) ambulance table name, 4) ambulance id, 5) client to ambulance matching id\n",
    "jc_names = ('jc', 'id', 'jocomedactincidents', 'hash_rcdid', 'hash_sourceid')\n",
    "dc_names = ('dc', 'casenum', 'joco110hsccclientmisc2eaimpression', 'clientid', 'sourceid')  \n",
    "dfs = []\n",
    "for county, table_id, ambulance_table, ambulance_sourceid, client_sourceid in [jc_names, dc_names]:\n",
    "    q = f\"\"\"\n",
    "    with mex as \n",
    "    (\n",
    "    select \n",
    "        client.joid,\n",
    "        j.suicide,\n",
    "        j.overdosed,\n",
    "        client.dob,\n",
    "        j.dateofdeath\n",
    "    from clean.jocojococlient client\n",
    "    join clean.joco{county}mexoverdosessuicides j\n",
    "    on client.sourceid = j.{table_id}\n",
    "    where client.matchdate = '2022-06-26'\n",
    "    ),\n",
    "    amb as \n",
    "    (\n",
    "    select\n",
    "        client.joid,\n",
    "        ambulance.suicide_attempt_flag,\n",
    "        ambulance.suicidal_flag,\n",
    "        ambulance.drug_flag\n",
    "    from clean.jocojococlient client\n",
    "    join clean.{ambulance_table} ambulance\n",
    "    on client.{client_sourceid} = ambulance.{ambulance_sourceid}\n",
    "    ),\n",
    "    num_rows as \n",
    "    (\n",
    "    select\n",
    "        joid,\n",
    "        count(*) as num_client_rows\n",
    "    from\n",
    "        clean.jocojococlient client\n",
    "    where\n",
    "        client.matchdate = '2022-06-26'\n",
    "    group by joid\n",
    "    )\n",
    "\n",
    "    select distinct \n",
    "        mex.joid,\n",
    "        mex.suicide,\n",
    "        mex.overdosed,\n",
    "        mex.dob,\n",
    "        mex.dateofdeath,\n",
    "        amb.suicide_attempt_flag,\n",
    "        amb.suicidal_flag,\n",
    "        amb.drug_flag,\n",
    "        num_client_rows\n",
    "    from mex\n",
    "    left join amb\n",
    "    on mex.joid = amb.joid\n",
    "    left join num_rows on mex.joid = num_rows.joid\n",
    "    \"\"\"\n",
    "\n",
    "    result = db_conn.execute(q)\n",
    "    dfs.append(pd.DataFrame(result))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "731636ba-2e84-41de-b63d-fe1508cb56ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs[0]['county'] = len(dfs[0].index) * ['j'] \n",
    "dfs[1]['county'] = len(dfs[1].index) * ['d'] \n",
    "both_df = pd.concat(dfs, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2616dcc-1bc5-47a8-9316-771cf9470ed0",
   "metadata": {},
   "outputs": [],
   "source": [
    "both_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e33fd07c-20b2-4f77-97cc-27463ec4124d",
   "metadata": {},
   "source": [
    "## Suicide and overdose data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12d65b96-9418-45da-8fa4-2feb643c2d56",
   "metadata": {},
   "outputs": [],
   "source": [
    "def list_to_csv(filename, values):\n",
    "    CSV_DIR = 'csv/'\n",
    "    path = os.path.join(CSV_DIR, filename)\n",
    "    with open(path, 'w') as f:\n",
    "        csv_writer = csv.writer(f)\n",
    "        values = [[value] for value in values]\n",
    "        csv_writer.writerows(values)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c629c649-7251-4d6a-838c-6fcd7ae16016",
   "metadata": {},
   "source": [
    "### Suicides where a previous suicide attempt (or suicidality in general) is known"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25a533b8-957e-4453-ab1f-8f3d6022d0d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "for county in ['johnson', 'douglas']:\n",
    "    county_letter = county[0]\n",
    "    county_df = both_df[both_df['county'] == county_letter]\n",
    "    tot_suicides = sum(county_df['suicide'] == True)\n",
    "    num_prev_attempts = sum((county_df['suicide'] == True) & (county_df['suicide_attempt_flag'] == True))\n",
    "    # The below includes suicidal ideation, self-harm, etc.\n",
    "    num_prev_suicidal = sum((county_df['suicide'] == True) & (county_df['suicidal_flag'] == True))\n",
    "    print(f'{num_prev_attempts} out of {tot_suicides} ({num_prev_attempts / (num_prev_attempts + tot_suicides):.1%}) {county} county residents that committed suicide had a previous suicide attempt (tracked by ambulance data)')\n",
    "    print(f'{num_prev_suicidal} out of {tot_suicides} ({num_prev_suicidal / (num_prev_suicidal + tot_suicides):.1%}) {county} county residents that committed suicide were previously suicidal (tracked by ambulance data)')\n",
    "    print('-'*20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cbeb40f-79f2-4c67-8fc9-5a4b3b30391f",
   "metadata": {},
   "source": [
    "### Drug overdoses and previous drug history"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8852db97-78ca-41da-a283-75718c49331a",
   "metadata": {},
   "outputs": [],
   "source": [
    "for county in ['johnson', 'douglas']:\n",
    "    county_letter = county[0]\n",
    "    county_df = both_df[both_df['county'] == county_letter]\n",
    "    tot_fatal_ods = sum(county_df['overdosed'] == True)\n",
    "    num_od_with_drug_history = sum((county_df['overdosed'] == True) & (county_df['drug_flag'] == True))\n",
    "    print(f'{num_od_with_drug_history} out of {tot_fatal_ods} ({num_od_with_drug_history / (tot_fatal_ods + num_od_with_drug_history):.1%}) {county} county residents that fatally overdosed had previous drug history (as tracked by ambulance data)')\n",
    "    print('-'*20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52010349-5bdf-48c0-9e13-6a23ce9e6594",
   "metadata": {},
   "source": [
    "### First-time interaction with the system is suicide "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e03071a-cd72-429d-85c2-fc40fb7c5004",
   "metadata": {},
   "outputs": [],
   "source": [
    "for county in ['johnson', 'douglas']:\n",
    "    county_letter = county[0]\n",
    "    county_df = both_df[(both_df['county'] == county_letter) & (both_df['suicide'] == True)]\n",
    "    know_only_suicide = county_df[county_df['num_client_rows'] == 1]\n",
    "        \n",
    "    # Export to csv\n",
    "    list_to_csv(f'{county}_only_know_suicide.csv', know_only_suicide['joid'].tolist())\n",
    "    \n",
    "    num_only_know_suicide = len(know_only_suicide.index) \n",
    "    num_know_more_than_just_suicide = len(county_df[county_df['num_client_rows'] > 1].index)\n",
    "    num_suicides = num_only_know_suicide + num_know_more_than_just_suicide\n",
    "    print(f'In {county} county, {num_only_know_suicide} of {num_suicides} ({num_only_know_suicide / (num_only_know_suicide + num_know_more_than_just_suicide):.1%}) total suicides have no prior interaction with the system')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b37a1fc-8011-4a83-82c9-4cb65cdba3b2",
   "metadata": {},
   "source": [
    "### First-time interaction with the system is drug overdose "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee417de3-0a0f-4ed6-bfdc-50da7ed2331c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for county in ['johnson', 'douglas']:\n",
    "    county_letter = county[0]\n",
    "    county_df = both_df[(both_df['county'] == county_letter) & (both_df['overdosed'] == True)]\n",
    "    only_know_od_df = county_df[county_df['num_client_rows'] == 1]\n",
    "    num_only_know_od = len(only_know_od_df.index) \n",
    "    \n",
    "    list_to_csv(f'{county}_only_know_od.csv', only_know_od_df['joid'].tolist())\n",
    "    \n",
    "    num_know_more_than_just_od = len(county_df[county_df['num_client_rows'] > 1].index)\n",
    "    num_ods = num_only_know_od + num_know_more_than_just_od\n",
    "    print(f'In {county} county, {num_only_know_od} of {num_ods} ({num_only_know_od / (num_only_know_od + num_know_more_than_just_od):.1%}) fatal overdoses have no prior interaction with the system (as tracked by ambulance data)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06d901f4-153f-4ce2-b63d-ea3bdff8f5ea",
   "metadata": {},
   "source": [
    "### Suicide and overdoses by age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0ee3472-5186-45f6-8e66-b95ee7de5857",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_age(death_date, dob):\n",
    "    \"\"\" Return age of person at time of death.\"\"\"\n",
    "    if death_date is None or dob is None:\n",
    "        return None\n",
    "    return death_date.year - dob.year - ((death_date.month, death_date.day) < (dob.month, dob.day))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8202524-7933-4ab0-936d-53fa43d6ac71",
   "metadata": {},
   "outputs": [],
   "source": [
    "both_df['age'] = [get_age(death_date, dob) for death_date, dob in zip(both_df['dateofdeath'], both_df['dob'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ff9efd7-2eaa-4168-be4b-eaec7e82bf15",
   "metadata": {},
   "outputs": [],
   "source": [
    "both_df['single_event'] = [True if num_cl_rows == 1 else False for num_cl_rows in both_df['num_client_rows']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79342b42-b09a-447e-8128-1578126a8d16",
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.set()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2aa11264-3bdc-4634-ae79-6685e83df136",
   "metadata": {},
   "source": [
    "#### We explore the age distribution of those that committed suicide, conditioned on i), whether their death is their first interaction with the system and, ii), whether the death was through overdose."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdbdf258-ec87-4c6d-8718-c17b36ab0c31",
   "metadata": {},
   "outputs": [],
   "source": [
    "for death_type in ['suicide', 'overdosed']:\n",
    "    for my_hue in ['single_event', 'overdosed']:\n",
    "        for county in ['j', 'd']:\n",
    "            print(f'Death type: {death_type}')\n",
    "            if death_type == 'overdosed':\n",
    "                print('(Not necessarily suicide)')\n",
    "            county_name = 'Johnson County' if county == 'j' else 'Douglas County'\n",
    "            data_df = both_df[(both_df['county'] == county) & (both_df[death_type] == True)]\n",
    "            sns.histplot(data=data_df, x='age', stat='count', hue=my_hue, binwidth=5)\n",
    "            plt.title(f'{county_name}')\n",
    "            plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00338b90-24a2-499e-b967-3afc71c2e583",
   "metadata": {},
   "source": [
    "## Crosstab of features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2627d4e5-306c-4838-ad11-aa2713f83c1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "both_df"
   ]
  }
 ],
 "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
}