3-referral-journeys.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Referral journeys\n",
    "\n",
    "This notebook looks at referral journeys and whether they end in a S17, S47 or NFA.\n",
    "\n",
    "The input: main flatfile CIN with data from all LAs.\n",
    "\n",
    "The output of this notebook is a table with:\n",
    "- All information related to assessments\n",
    "- days_to_s17: days from referral date until next S17 event\n",
    "- days_to_s47: days from referral date until next S47 event\n",
    "- referral_outcome: either S17, S47, S17 & S47 or NFA\n",
    "- age at referral"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "%run \"00-config.ipynb\"\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Config"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filepaths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_file = os.path.join(flatfile_folder, 'main_flatcin.csv')\n",
    "output_file = os.path.join(output_folder, 'referral_outcomes.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Key assumptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Max days referral -> assessment for both to be linked\n",
    "ref_assessment = 30\n",
    "\n",
    "# Date from which referral is too recent to determine next journey\n",
    "ref_max_date = cin_census_close - pd.Timedelta(\"30 days\") # 30 days before CIN Census closes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data wrangling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load flatfile\n",
    "df = pd.read_csv(input_file)\n",
    "\n",
    "# Only keep 3 subsets: Referral, Assessment and S47 events. \n",
    "ref = df[df.Type == 'CINreferralDate']\n",
    "s17 = df[df.Type == 'AssessmentActualStartDate']\n",
    "s47 = df[df.Type == 'S47ActualStartDate']\n",
    "\n",
    "# Drop empty cols\n",
    "ref.dropna(axis=1, how='all', inplace=True)\n",
    "s17.dropna(axis=1, how='all', inplace=True)\n",
    "s47.dropna(axis=1, how='all', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Match Referrals with S17 assessments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge Referrals and S17 ogether, to match each referral event with an assessment (if it occurred)\n",
    "\n",
    "data_s17 = ref.merge(s17[['LAchildID', 'LA', 'AssessmentActualStartDate']], how='left', on=['LAchildID', 'LA'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We might have merged some S17 events that happened to the child, but before the Referral or much later.\n",
    "# In that case, the Referral and the Assessments are not related.\n",
    "# Rule: if the Assessment happened before the Referral, or more than X days later (defined at top of notebook), they are not related.\n",
    "\n",
    "# Turn relevant columns into dates\n",
    "data_s17['AssessmentActualStartDate'] = pd.to_datetime(data_s17['AssessmentActualStartDate'])\n",
    "data_s17['CINreferralDate'] = pd.to_datetime(data_s17['CINreferralDate'])\n",
    "\n",
    "# Calculate days_to_s17: length of time between Referral and S17\n",
    "data_s17['days_to_s17'] = data_s17['AssessmentActualStartDate'] - data_s17['CINreferralDate']\n",
    "data_s17['days_to_s17'] = data_s17['days_to_s17'].dt.days\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's look at the distribution of days between Referral and S17\n",
    "data_s17.hist(column='days_to_s17')\n",
    "\n",
    "# We can see that some pairs do not make sense: the S17 happened earlier or much later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only keep rows where the Referral goes to S17\n",
    "ref_s17_match = data_s17[((data_s17.days_to_s17 <= ref_assessment) & (data_s17.days_to_s17 >=0))]\n",
    "\n",
    "# Only keep useful cols\n",
    "ref_s17_match = ref_s17_match[['Date', 'LAchildID', 'AssessmentActualStartDate', 'days_to_s17']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Match Referrals with S47 assessments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge Referrals and S47 ogether, to match each referral event with an assessment (if it occurred)\n",
    "\n",
    "data_s47 = ref.merge(s47[['LAchildID', 'LA', 'S47ActualStartDate']], how='left', on=['LAchildID', 'LA'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We might have merged some S47 events that happened to the child, but before the Referral or much later.\n",
    "# In that case, the Referral and the Assessments are not related.\n",
    "# Rule: if the Assessment happened before the Referral, or more than X days later (defined at top of notebook), they are not related.\n",
    "\n",
    "# Turn relevant columns into dates\n",
    "data_s47['S47ActualStartDate'] = pd.to_datetime(data_s47['S47ActualStartDate'])\n",
    "data_s47['CINreferralDate'] = pd.to_datetime(data_s47['CINreferralDate'])\n",
    "\n",
    "# Calculate days_to_s47: length of time between Referral and S17\n",
    "data_s47['days_to_s47'] = data_s47['S47ActualStartDate'] - data_s47['CINreferralDate']\n",
    "data_s47['days_to_s47'] = data_s47['days_to_s47'].dt.days\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's look at the distribution of days between Referral and S47\n",
    "data_s47.hist(column='days_to_s47')\n",
    "\n",
    "# We can see that some pairs do not make sense: the S47 happened earlier or much later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only keep rows where the Referral goes to S47\n",
    "ref_s47_match = data_s47[((data_s47.days_to_s47 <= ref_assessment) & (data_s47.days_to_s47 >=0))]\n",
    "\n",
    "# Only keep useful cols\n",
    "ref_s47_match = ref_s47_match[['Date', 'LAchildID', 'S47ActualStartDate', 'days_to_s47']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create final list of Referrals with outcomes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge with S17 outcomes\n",
    "referral_outcomes = ref.merge(ref_s17_match, on=['Date', 'LAchildID'], how='left')\n",
    "\n",
    "# Merge with S47 outcomes\n",
    "referral_outcomes = referral_outcomes.merge(ref_s47_match, on=['Date', 'LAchildID'], how='left')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add clear outcomes column\n",
    "\n",
    "# By default, set to NFA\n",
    "referral_outcomes[\"referral_outcome\"] = 'NFA'\n",
    "\n",
    "# S17\n",
    "referral_outcomes.loc[referral_outcomes.AssessmentActualStartDate.notnull(), \"referral_outcome\"] = 'S17'\n",
    "\n",
    "# S47\n",
    "referral_outcomes.loc[referral_outcomes.S47ActualStartDate.notnull(), \"referral_outcome\"] = 'S47'\n",
    "\n",
    "# Both\n",
    "referral_outcomes.loc[(referral_outcomes.S47ActualStartDate.notnull() & \n",
    "                       referral_outcomes.AssessmentActualStartDate.notnull()), \"referral_outcome\"] = 'Both S17 & S47'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate age of child during the Referral, based on cin_census_close (defined in config) \n",
    "referral_outcomes['CINreferralDate'] = pd.to_datetime(referral_outcomes['CINreferralDate'])\n",
    "referral_outcomes['Age at referral'] = referral_outcomes['CINreferralDate'].dt.year - referral_outcomes['PersonBirthDate']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "referral_outcomes.to_csv(output_file, index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}