referral-partner-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Referral partner analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Imports\n",
    "\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import calendar\n",
    "from dateutil.relativedelta import relativedelta\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## >> Input required"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Define filepaths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_file = '.../log.csv' # Filepath to log\n",
    "output_folder = '...' # Filepath to folder where the analysis will be saved\n",
    "if not os.path.exists(output_folder):\n",
    "    os.makedirs(output_folder)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Define analysis dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define report end date\n",
    "report_end = datetime.date(2018, 5, 31) # Enter year, month, day\n",
    "\n",
    "# Define analysis window (months)\n",
    "window = 13"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Useful references\n",
    "\n",
    "ordered_categories = [\"contact\",\n",
    "                      \"early_help_assessment_start\",\n",
    "                      \"early_help_assessment_completion\",\n",
    "                      \"referral\",\n",
    "                      \"assessment_start\",\n",
    "                      \"assessment_authorised\",\n",
    "                      \"s47\",\n",
    "                      \"icpc\",\n",
    "                      \"cin_start\",\n",
    "                      \"cin_end\",\n",
    "                      \"cpp_start\",\n",
    "                      \"cpp_end\",\n",
    "                      \"lac_start\",\n",
    "                      \"lac_end\"]\n",
    "referral_sources = {\n",
    "    '1': 'Individual',\n",
    "    '2': 'Schools',\n",
    "    '3': 'Health services',\n",
    "    '4': 'Housing',\n",
    "    '5': 'LA services',\n",
    "    '6': 'Police',\n",
    "    '7': 'Other legal agency',\n",
    "    '8': 'Other',\n",
    "    '9': 'Anonymous',\n",
    "    '10': 'Unknown'    \n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Functions to use during analysis\n",
    "\n",
    "\n",
    "def central_event_to_other_events(central_event, dataframe):\n",
    "    '''\n",
    "    Returns a dataframe matching a central event with all other events that happened to a child\n",
    "    The dataframe includes a days elapsed column counting the days between central event and the other event\n",
    "    Removes the duplicates: a central event cannot be matched to the same central event\n",
    "    '''\n",
    "    # Add column with event sequence to track the order in which the events took place\n",
    "    dataframe['child_event_sequence'] = dataframe.sort_values(['Date','Type']).groupby(['CUID']).cumcount() + 1\n",
    "    \n",
    "    # Get list of all central events we want to assess in relation to other events\n",
    "    central_events = dataframe[dataframe.Type == central_event]\n",
    "\n",
    "    # Merge the dataframe to the central_events: this will pair each central event to any event that happened to the same child\n",
    "    # i.e. there will be multiple lines for each combination of [central event + another event]\n",
    "    central_event_to_other_event = central_events.merge(dataframe, how='left', on='CUID')\n",
    "\n",
    "    # Calculate the number of days between central event and its paired event\n",
    "    central_event_to_other_event[\"days_elapsed\"] = (central_event_to_other_event.Date_y - central_event_to_other_event.Date_x) / np.timedelta64(1, 'D')\n",
    "\n",
    "    # Remove rows where central event = paired event\n",
    "    same = (central_event_to_other_event.child_event_sequence_x == central_event_to_other_event.child_event_sequence_y)\n",
    "    central_event_to_other_event = central_event_to_other_event[~(same)]\n",
    "    \n",
    "    return central_event_to_other_event\n",
    "\n",
    "\n",
    "def apply_duration_filter(data, name, event_type):\n",
    "    '''\n",
    "    Counts days to closest next event\n",
    "    '''\n",
    "    criteria_next = data[(data.Type_y == event_type) & (data.days_elapsed>=0)]\n",
    "    df = criteria_next.groupby([\"CUID\",\"child_event_sequence_x\"]).agg({\"days_elapsed\": 'min'}).reset_index()\n",
    "    df.rename(columns={\"child_event_sequence_x\": \"child_event_sequence\", \"days_elapsed\": 'days_to_{}'.format(name)}, inplace=True)     \n",
    "    return df\n",
    "\n",
    "\n",
    "def start_end_date(date, period):\n",
    "    '''\n",
    "    Returns start and end date based on a given date and given period (months)\n",
    "    The given date must be a Timestamp\n",
    "    The end date will be the last day of the month prior to the given date\n",
    "    The start date will be the first day of the month after calculating (end date - period)\n",
    "    '''\n",
    "    # If given date is already at the end of the month\n",
    "    if date.day == calendar.monthrange(date.year, date.month)[1]:\n",
    "        end_date = date\n",
    "        start_date = end_date + relativedelta(months=-(period-1))\n",
    "        start_date = start_date.replace(day=1)\n",
    "    # If given date is not at the end of the month\n",
    "    else:\n",
    "        end_date_intermediate = date.replace(day=1)\n",
    "        end_date = end_date_intermediate - np.timedelta64(1, 'D')\n",
    "        start_date = end_date_intermediate + relativedelta(months=-period)\n",
    "    try:\n",
    "        start_date = start_date.replace(hour = 0, minute = 0, second = 0)\n",
    "    except:\n",
    "        print('No time in start date')\n",
    "    try:\n",
    "        end_date = end_date.replace(hour = 23, minute = 59, second = 59)\n",
    "    except:\n",
    "        print('No time in end date')\n",
    "    # Turn into pd-compatible format\n",
    "    start_date = pd.to_datetime(start_date)\n",
    "    end_date = pd.to_datetime(end_date)\n",
    "    return start_date, end_date\n",
    "\n",
    "\n",
    "def above_within_below(series, lower_bound, upper_bound):\n",
    "    '''\n",
    "    For a given series, calculates whether its values are above, within, below the given lower_bounds and upper_bounds\n",
    "    '''\n",
    "    values = []\n",
    "    for data, low, high in zip(series, lower_bound, upper_bound):\n",
    "        if data < low:\n",
    "            value = 'below'\n",
    "        elif data > high:\n",
    "            value = 'above'\n",
    "        elif (data >= low) and (data <= high):\n",
    "            value = 'within'\n",
    "        else:\n",
    "            value = np.nan\n",
    "        values.append(value)\n",
    "    return values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Create base dataframe: all_events\n",
    "\n",
    "# Load input_file\n",
    "log = pd.read_csv(input_file)\n",
    "# Set categories of events to make sure we order them correctly if two events happen on the same day\n",
    "# e.g. Contact comes before Referral if same date\n",
    "log.Type = log.Type.astype('category')\n",
    "log.Type.cat.set_categories([c for c in ordered_categories if c in log.Type.unique()], inplace=True, ordered=True)\n",
    "log.Date = pd.to_datetime(log.Date)\n",
    "\n",
    "# Drop rows where the Contact Source was not matched correctly at Annex A cleaner stage: we can't use them\n",
    "contacts_not_matched = log[log['Contact Source'] == 'not matched'].CUID.count()\n",
    "total_contacts = log[log.Type=='contact']['CUID'].count()\n",
    "print('There were {} contacts that were not matched out of {} contacts ({}%)'.format(contacts_not_matched, total_contacts, round(contacts_not_matched/total_contacts*100, 2)))\n",
    "log = log[~(log['Contact Source']=='not matched')]\n",
    "\n",
    "# Drop rows without a Type, as we don't know what they are\n",
    "log = log[~(log.Type.isnull())]\n",
    "\n",
    "# Create a copy of log named all_events - we will use it throughout the notebook\n",
    "all_events = log.copy()\n",
    "\n",
    "# Only keep the columns we need for the analysis\n",
    "all_events = all_events[['CUID', 'Date', 'Type', 'Source', 'Referral Source', 'Contact Source']]\n",
    "all_events.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Create Contact to other events DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Input: all_events\n",
    "- Output: DataFrame of all contacts and their number of days to next contact, next referral and next assessment\n",
    "\n",
    "This is done in four steps: \n",
    "- Step 1: Create a DataFrame matching each contact to all the other events that happened to the same child\n",
    "- Step 2: Create a DataFrame with a single line per contact with number of days from that contact to the nearest contact, referral and assessment\n",
    "- Step 3: Add a few columns to identify referral date, assessment date and deduce contacts outcomes\n",
    "- Step 4: Tidy up DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set up\n",
    "\n",
    "# Choose the 'central' event - all day counts will be based on this event. In this case: contacts.\n",
    "central_event = 'contact'\n",
    "\n",
    "# Write list of events which days will be counted in relation to central event\n",
    "# Syntax: {short name: event type}\n",
    "events = {'contact':'contact',\n",
    "          'referral': 'referral',\n",
    "          'assessment':'assessment_start'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 1 - Match contacts to all other events\n",
    "\n",
    "contacts_to_next_event = central_event_to_other_events(central_event, all_events)\n",
    "\n",
    "\n",
    "\n",
    "# Step 2 - Count days from contact to nearest events mentioned in {events}\n",
    "\n",
    "contacts_day_count = all_events[all_events.Type == central_event]\n",
    "for event in events.keys():\n",
    "    to_next = apply_duration_filter(contacts_to_next_event, event, events[event])\n",
    "    contacts_day_count = contacts_day_count.merge(to_next, how=\"left\", on=[\"CUID\",\"child_event_sequence\"])\n",
    "    \n",
    "\n",
    "    \n",
    "# Step 3 - Add extra columns to define referral date, assessment date and deduce contacts outcomes\n",
    "\n",
    "# Convert day counts into Timedelta\n",
    "for event in events:\n",
    "    contacts_day_count['days_to_{}'.format(event)] = contacts_day_count['days_to_{}'.format(event)].apply(lambda x: pd.Timedelta(x, unit='D'))\n",
    "\n",
    "# Calculate referral and assessment dates\n",
    "contacts_day_count['referral_date'] = contacts_day_count.Date + contacts_day_count.days_to_referral\n",
    "contacts_day_count['assessment_date'] = contacts_day_count.Date + contacts_day_count.days_to_assessment\n",
    "\n",
    "# Match referrals to contacts: must be same CUID, same referral date, same Referral Source\n",
    "referrals = all_events[(all_events.Type == 'referral')&(all_events['Referral Source'] != 'not matched')][['CUID', 'Date', 'Referral Source']]\n",
    "referrals['referral_matched'] = True # This will help track which contacts where successfully matched\n",
    "referrals.rename(columns={'Date': 'referral_date', 'Referral Source':'Contact Source'}, inplace=True)\n",
    "contacts_day_count.drop('Referral Source', axis=1, inplace=True)\n",
    "contacts_day_count = contacts_day_count.merge(referrals, how='left', on=['CUID', 'referral_date', 'Contact Source'])\n",
    "total_referrals = all_events[all_events.Type == 'referral'].shape[0]\n",
    "matched_referrals = contacts_day_count[contacts_day_count.referral_matched == True].shape[0]\n",
    "print('{} referrals were matched to a contact, out of a total of {} referrals ({}%)'.format(matched_referrals, total_referrals, round(matched_referrals/total_referrals*100,2)))\n",
    "\n",
    "# Create outcomes columns: led_to_referral, led_to_assessment and contact_only\n",
    "\n",
    "# Referral\n",
    "# A contact led to referral if:\n",
    "#    1 - A referral was successfully matched to the contact CUID, date and referral source (referral_matched == True)\n",
    "#    2 - The referral happened within 7 days of the contact\n",
    "#    3a - There is no further contact\n",
    "#    3b - OR if there is a further contact, it happens after the referral\n",
    "\n",
    "referral_criteria = ((contacts_day_count['referral_matched'] == True) \n",
    "                     & ((contacts_day_count['referral_date'] - contacts_day_count['Date'])<= np.timedelta64(7, 'D')) \n",
    "                     & (contacts_day_count['days_to_contact'].isnull() \n",
    "                           | (contacts_day_count['days_to_referral'] < contacts_day_count['days_to_contact']))) \n",
    "\n",
    "\n",
    "# Assessment \n",
    "# A contact led to assessment if:\n",
    "#    1 - The assessment happened within 7 days of the referral\n",
    "#    2a - There is no further contact\n",
    "#    2b - OR if there is a further contact, it happens after the assessment\n",
    "assessment_criteria = ((contacts_day_count['assessment_date'] - contacts_day_count['referral_date']<= np.timedelta64(7, 'D')) \n",
    "                     & (contacts_day_count['days_to_contact'].isnull() \n",
    "                           | (contacts_day_count['days_to_assessment'] < contacts_day_count['days_to_contact']))) \n",
    "\n",
    "# Create columns based on criteria\n",
    "contacts_day_count['led_to_referral'] = referral_criteria\n",
    "contacts_day_count['led_to_assessment'] = assessment_criteria\n",
    "contacts_day_count['contact_only'] = (contacts_day_count.led_to_referral == False) & (contacts_day_count.led_to_assessment == False)\n",
    "\n",
    "\n",
    "\n",
    "# Step 4 - Tidy up\n",
    "\n",
    "# Simplify Annex A contact source categories, e.g. 5C becomes LA services\n",
    "contacts_day_count['agg_contact_source'] = contacts_day_count['Contact Source'].apply(lambda x: re.findall(r'(\\d{1,2})[A-Z]?', x)[0])\n",
    "contacts_day_count['agg_contact_source'] = contacts_day_count['agg_contact_source'].map(referral_sources)\n",
    "\n",
    "# Add year_month columns for contacts, referrals and assessments\n",
    "contacts_day_count['year_month'] = contacts_day_count.Date.apply(lambda x: str(x)[:7])\n",
    "contacts_day_count['r_year_month'] = contacts_day_count.referral_date.apply(lambda x: str(x)[:7])\n",
    "contacts_day_count['a_year_month'] = contacts_day_count.assessment_date.apply(lambda x: str(x)[:7])\n",
    "\n",
    "contacts_day_count.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Create \"master\" Dataframe counting Contact, Referrals and Assessments\n",
    "- Input: contacts_day_count\n",
    "- Output: DataFrame with following columns:\n",
    "    - Referral partner (agg_contact_source)\n",
    "    - Year-Month (year_month)\n",
    "    - Number of total contacts (total_contacts)\n",
    "    - Number of contacts NFA (contact_only)\n",
    "    - Number of contacts that reached referral (led_to_referral)\n",
    "    - Number of contacts that reached assessment (led_to_assessment)\n",
    "    - Number of total referrals (total_referrals)\n",
    "    - Number of total assessments (total_assessments)\n",
    "\n",
    "\n",
    "This is done in 2 steps:\n",
    "- Step 1: Count contacts per outcome per source\n",
    "- Step 2: Count referral and assessments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set up\n",
    "\n",
    "# Define dates of report analysis\n",
    "start_date, end_date = start_end_date(report_end, window)\n",
    "print('The report will start on {} and end on {}'.format(start_date, end_date))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 1 - Count contacts per outcome (contact only, led to referral, led to assessment) and per source\n",
    "\n",
    "# Get subset with right time window\n",
    "contact_outcomes = contacts_day_count[(contacts_day_count.Date >= start_date) & (contacts_day_count.Date <= end_date)]\n",
    "\n",
    "# Calculate number of contacts per outcome per contact source\n",
    "contact_outcomes = contact_outcomes.groupby(['agg_contact_source', 'year_month']).agg({'CUID': 'count', 'contact_only': 'sum', 'led_to_referral': 'sum', 'led_to_assessment': 'sum'}).reset_index()\n",
    "contact_outcomes.rename(columns={'CUID': 'total_contacts'}, inplace=True)\n",
    "\n",
    "\n",
    "# Step 2 - Count number of referrals and assessments per month per contact source\n",
    "\n",
    "master = contact_outcomes.copy()\n",
    "for event in ['referral', 'assessment']:\n",
    "    grouped = contacts_day_count[contacts_day_count['led_to_{}'.format(event)]==True][['CUID', 'agg_contact_source', '{}_year_month'.format(event[0])]]\n",
    "    grouped = grouped.groupby(['agg_contact_source', '{}_year_month'.format(event[0])]).agg({'CUID':'count'}).reset_index()\n",
    "    grouped.rename(columns={'CUID': 'total_{}s'.format(event), '{}_year_month'.format(event[0]):'year_month'}, inplace=True)\n",
    "    master = master.merge(grouped, how='left', on=['agg_contact_source', 'year_month'])\n",
    "\n",
    "master.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Create partner, total and comparative dfs and save to Excel\n",
    "- Input: master\n",
    "- Output: Excel spreadsheet with the following tabs:\n",
    "    - Step 1 - One tab per contact source ('{contact source name}') with #contacts, #referrals, #assessments, #contacts_only, #led_to_referral, #led_to_assessment\n",
    "    - Step 2 - Three tabs ('Comparative contacts/referrals/assessments') comparing contacts, referrals and assessments across contact sources, including a lower and higher standard deviation bounds\n",
    "    - Step 3 - One tab ('Contact only') counting volume and proportion of contacts that did not lead to further action, across contact sources, for the final month only\n",
    "    - Step 4 - One tab ('Total') that recaps total #contacts, #referrals, #assessments and standard deviation bounds for all three"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set up\n",
    "\n",
    "# Set up ExcelWriter to store the different tables in separate tabs in a unique Excel spreadsheet\n",
    "tables = pd.ExcelWriter(os.path.join(output_folder, 'referral_partner_analysis.xlsx'), engine='xlsxwriter')\n",
    "\n",
    "# Determine final month of analysis (useful for a few dfs)\n",
    "final_month = master.year_month.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 1 - Data specific to each contact source\n",
    "\n",
    "for source in master.agg_contact_source.unique():\n",
    "    df = master[master.agg_contact_source == source]\n",
    "    \n",
    "    # Save to excel\n",
    "    df.to_excel(tables, sheet_name='{}'.format(source), index=False)\n",
    "    \n",
    "\n",
    "# Step 2 - Compare across contact sources on contacts, referrals and assessments\n",
    "\n",
    "for event in ['contacts', 'referrals', 'assessments']:\n",
    "    df = master[['agg_contact_source', 'year_month', 'total_{}'.format(event)]]\n",
    "    df = df.pivot(index='agg_contact_source', columns='year_month', values='total_{}'.format(event))\n",
    "    df['upper_std'] = df.mean(axis = 1) + df.std(axis = 1) \n",
    "    df['lower_std'] = df.mean(axis = 1) - df.std(axis = 1)\n",
    "    df['{}-situation'.format(final_month)] = above_within_below(df[final_month], df['lower_std'], df['upper_std'])\n",
    "    df.columns.name = 'Monthly {}'.format(event)\n",
    "    \n",
    "    # Save to excel\n",
    "    df.to_excel(tables, sheet_name='Comparative {}'.format(event), index=True)\n",
    "    \n",
    "\n",
    "# Step 3 - Volume and proportion of NFA contacts (final month only)\n",
    "\n",
    "nfa = master[master.year_month == final_month][['agg_contact_source', 'total_contacts', 'contact_only']]\n",
    "nfa['contacts_nfa_pct'] = nfa['contact_only'] / nfa['total_contacts']\n",
    "\n",
    "# Save to excel\n",
    "nfa.to_excel(tables, sheet_name='Contact only', index=False)\n",
    "\n",
    "\n",
    "# Step 4 - Total contacts, referrals, assessments and lower/upper deviation bounds\n",
    "\n",
    "total = master[['year_month', 'total_contacts', 'contact_only',\n",
    "       'led_to_referral', 'led_to_assessment', 'total_referrals',\n",
    "       'total_assessments']].groupby('year_month').sum()\n",
    "\n",
    "# Add standard deviation cols\n",
    "cols = ['total_contacts', 'total_referrals','total_assessments']\n",
    "for col in cols:\n",
    "    total['{}_std_lower'.format(col)] = total[col].mean() - total[col].std()\n",
    "    total['{}_std_upper'.format(col)] = total[col].mean() + total[col].std()\n",
    "\n",
    "# Save to excel\n",
    "total.to_excel(tables, sheet_name='Total', index=True)\n",
    "\n",
    "\n",
    "\n",
    "# Save all tables\n",
    "tables.save()"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}