3-s47-journeys.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# S47 journeys\n",
    "This notebook looks at S47 journeys and whether they follow the usual trajectory of S47 -> ICPC -> CPP start.\n",
    "\n",
    "The input: main flatfile CIN with data from all LAs.\n",
    "\n",
    "The output of this notebook is a table with the columns:\n",
    "- Source\n",
    "- Destination\n",
    "- Count\n",
    "- Local Authority\n",
    "- Demographics (gender, age, ethnicity, disability)\n",
    "\n",
    "This is the shape required to plug the table into a PowerBI Sankey diagram."
   ]
  },
  {
   "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, 's47-sankey.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Key assumptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Max days S47 -> CCP for both to be linked\n",
    "s47_cpp = 60\n",
    "\n",
    "# Max days ICPC -> CCP for both to be linked\n",
    "icpc_cpp = 45\n",
    "\n",
    "# Date from which S47 is too recent to determine next journey\n",
    "s47_max_date = cin_census_close - pd.Timedelta(\"60 days\") # 60 days before CIN Census closes\n",
    "\n",
    "# Date from which ICPC is too recent to determine next journey\n",
    "icpc_max_date = cin_census_close - pd.Timedelta(\"45 days\") # 45 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 2 subsets: S47 events and CPP start events. \n",
    "s47 = df[df.Type == 'S47ActualStartDate']\n",
    "cpp = df[df.Type == 'CPPstartDate']\n",
    "\n",
    "# Drop empty cols\n",
    "s47.dropna(axis=1, how='all', inplace=True)\n",
    "cpp.dropna(axis=1, how='all', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge S47 and CPP together, to match each S47 event with CPP events (if they occurred)\n",
    "data = s47.merge(cpp[['LAchildID', 'LA', 'CPPstartDate']], how='left', on=['LAchildID', 'LA'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We might have merged some CPP events that happened to the child, but before the ICPC or much later.\n",
    "# In that case, the ICPC and the CPP are not related.\n",
    "# Rule: if the CPP happened before the ICPC, or more than X days later (defined at top of notebook), they are not related.\n",
    "\n",
    "# Turn relevant columns into dates\n",
    "data['S47ActualStartDate'] = pd.to_datetime(data['S47ActualStartDate'])\n",
    "data['DateOfInitialCPC'] = pd.to_datetime(data['DateOfInitialCPC'])\n",
    "data['CPPstartDate'] = pd.to_datetime(data['CPPstartDate'])\n",
    "\n",
    "# Calculate icpc_to_cpp: length of time between ICPC and CPP\n",
    "data['icpc_to_cpp'] = data['CPPstartDate'] - data['DateOfInitialCPC']\n",
    "data['icpc_to_cpp'] = data['icpc_to_cpp'].dt.days\n",
    "\n",
    "# Calculate s47_to_cpp: length of time between S47 and CPP\n",
    "data['s47_to_cpp'] = data['CPPstartDate'] - data['S47ActualStartDate']\n",
    "data['s47_to_cpp'] = data['s47_to_cpp'].dt.days\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's look at the distribution of days between ICPC and CPP start\n",
    "data.hist(column='icpc_to_cpp')\n",
    "\n",
    "# We can see that some pairs do not make sense: the CPP happened earlier or much later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only keep CPP matches:\n",
    "\n",
    "# CPP start within X days of ICPC (defined top of notebook), or\n",
    "# CPP start within X days of S47 (defined top of notebook), or\n",
    "\n",
    "cpp_match = data[((data.icpc_to_cpp >= 0) & (data.icpc_to_cpp <= icpc_cpp)) | \n",
    "                     ((data.s47_to_cpp >= 0) & (data.s47_to_cpp <= s47_cpp))]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add the matches back to the list of S47s\n",
    "s47_outcomes = s47.merge(cpp_match[['Date', 'LAchildID', 'CPPstartDate', 'icpc_to_cpp', 's47_to_cpp']], how='left', on=['Date', 'LAchildID'])\n",
    "\n",
    "# Turn columns into dates\n",
    "s47_outcomes['DateOfInitialCPC'] = pd.to_datetime(s47_outcomes['DateOfInitialCPC'])\n",
    "s47_outcomes['S47ActualStartDate'] = pd.to_datetime(s47_outcomes['S47ActualStartDate'])\n",
    "s47_outcomes['CPPstartDate'] = pd.to_datetime(s47_outcomes['CPPstartDate'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Shaping the PowerBI Sankey diagram input\n",
    "PowerBI requires a dataset with a Source (start of flow) and Destination (end of flow), with a count of occurrences.\n",
    "https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-sankey-diagram/\n",
    "\n",
    "We have several Source -> Destination options:\n",
    "- Step 1:\n",
    "    - S47 to ICPC\n",
    "    - S47 to CPP directly\n",
    "    - S47 to nothing (no ICPC nor CPP)\n",
    "    - S47 TBD (S47 occurred within 21 days of closing the CIN Census)\n",
    "- Step 2:\n",
    "    - ICPC to CPP\n",
    "    - ICPC to no CPP\n",
    "    - ICPC TBD (ICPC occurred within 2 months of closing the CIN Census)\n",
    "\n",
    "We also want to keep the Demographics columns (age, ethnicity, etc.) to be able to filter the sankeys."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate Source and Destination for step 1\n",
    "\n",
    "step1 = s47_outcomes.copy()\n",
    "\n",
    "# The source is necessarily S47 for step 1\n",
    "step1['Source'] = 'S47 strategy discussion'\n",
    "\n",
    "# The destination varies\n",
    "step1['Destination'] = np.nan # Create empty col\n",
    "# S47 -> ICPC\n",
    "step1.loc[step1['DateOfInitialCPC'].notnull(), 'Destination'] = 'ICPC'\n",
    "# S47 -> CPP directly\n",
    "step1.loc[step1['DateOfInitialCPC'].isnull() & step1['CPPstartDate'].notnull(), 'Destination'] = 'CPP start'\n",
    "# S47 -> TBD (too recent)\n",
    "step1.loc[step1['Destination'].isnull() & (step1['S47ActualStartDate'] >= s47_max_date), 'Destination'] = 'TBD - S47 too recent'\n",
    "# S47 -> No ICPC\n",
    "step1.loc[step1['Destination'].isnull(), 'Destination'] = 'No ICPC nor CPP'\n",
    "\n",
    "\n",
    "# Look at resulting trends\n",
    "step1.Destination.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate Source and Destination for step 2\n",
    "\n",
    "# Step 2 starts from all S47 that got to ICPC in step 1\n",
    "step2 = step1[step1.Destination == 'ICPC']\n",
    "\n",
    "# The source is necessarily ICPC for step 2\n",
    "step2['Source'] = 'ICPC'\n",
    "\n",
    "# The destination varies\n",
    "step2['Destination'] = np.nan # Empty col\n",
    "# ICPC -> CPP\n",
    "step2.loc[step2['CPPstartDate'].notnull(), 'Destination'] = 'CPP start'\n",
    "# ICPC -> TBD (too recent)\n",
    "step2.loc[step2['Destination'].isnull() & (step2['DateOfInitialCPC'] >= icpc_max_date), 'Destination'] = 'TBD - ICPC too recent'\n",
    "# ICPC -> No CPP\n",
    "step2.loc[step2['Destination'].isnull(), 'Destination'] = 'No CPP'\n",
    "\n",
    "# Look at resulting trends\n",
    "step2.Destination.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bring Steps 1 & 2 together\n",
    "s47_journey = pd.concat([step1, step2])\n",
    "\n",
    "# Calculate age of child during the S47, based on cin_census_close (defined in config) \n",
    "s47_journey['Age at S47'] = s47_journey['S47ActualStartDate'].dt.year - s47_journey['PersonBirthDate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save\n",
    "s47_journey.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
}