notebooks/texas-cpa-deficiencies.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:dc7258766cb9f7cadf3df1b29389ff735db700dd78c77183137bdf130d154535"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Analysis: \"Deficiencies\" among Texas foster care child placing agencies"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The data analyzed below were extracted from the Texas Department of Family and Protective Services' [online database of foster care child placing agencies (CPAs)](https://www.dfps.state.tx.us/Child_Care/Search_Texas_Child_Care/ppFacilitySearchFoster.asp) on Feb. 18, 2015.\n",
      "\n",
      "For each CPA, Texas lists all inspections and __\"deficiencies\"__ (i.e., violations of Texas DFPS standards) from \"the past two years.\" Texas categorizes deficiencies into five categories of increasing severity: \"Low,\" \"Medium Low,\" \"Medium,\" \"Medium High,\" and \"High.\" This analysis is focused on __\"High\" deficiencies__. Texas also lists the number of __\"open foster homes\"__ \u2014\u00a0i.e., capacity \u2014\u00a0for each CPA."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Loading the data"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Load the data we extracted:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "facilities = pd.read_csv(\"../data/agency-info.csv\").set_index(\"facility_id\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Many of the agencies listed are run by the same parent agency. In a separate file, we've mapped the names of the branches to a \"canonical\" name for each parent agency. Here, we build a Python dictionary that maps names in the data to canonical names:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "agency_names = pd.read_csv(\"../scripts/agency-names.csv\").fillna(\"\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "agency_name_dict = {}\n",
      "for name, alts in agency_names.itertuples(index=False):\n",
      "    agency_name_dict[name] = name\n",
      "    for alt in alts.split(\"|\"):\n",
      "        if alt: agency_name_dict[alt] = name"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Here we add the canonical name to the rest of the data, and note whether it is Mentor-affiliated.  If there is a name we have not yet accounted for, the script should throw an error."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "facilities[\"canonical_name\"] = facilities[\"operation_caregiver_name\"].apply(lambda x: agency_name_dict[x.upper()])\n",
      "facilities[\"mentor_affiliation\"] = facilities[\"canonical_name\"]\\\n",
      "    .apply(lambda x: \"Mentor\" if x == \"TEXAS MENTOR\" else \"Non-Mentor\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 5
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Grouping facilities"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Here, we group each facility/agency by its canonical name, and compute aggregate counts for the key figures reported in the DFPS online database."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "by_agency = facilities.groupby(\"canonical_name\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def aggregate_facilities(grouper):\n",
      "    return pd.DataFrame({\n",
      "        \"n_facilities\": grouper.size(),\n",
      "        \"n_open_homes\": grouper[\"open_foster_homes\"].sum(),\n",
      "        \"n_inspections\": grouper[\"n_inspections\"].sum(),\n",
      "        \"n_reports\": grouper[\"n_reports\"].sum(),\n",
      "        \"n_self_reported_incidents\": grouper[\"n_self_reported_incidents\"].sum(),\n",
      "        \"n_defic_low\": grouper[\"n_defic_low\"].sum(),\n",
      "        \"n_defic_medium_low\": grouper[\"n_defic_medium_low\"].sum(),\n",
      "        \"n_defic_medium\": grouper[\"n_defic_medium\"].sum(),\n",
      "        \"n_defic_medium_high\": grouper[\"n_defic_medium_high\"].sum(),\n",
      "        \"n_defic_high\": grouper[\"n_defic_high\"].sum(),\n",
      "        \"mentor_affiliation\": grouper[\"mentor_affiliation\"].first()\n",
      "    })"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "agencies = aggregate_facilities(facilities.groupby(\"canonical_name\"))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Cross-referencing w/ deficiency details"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Each facility's main directory page includes aggregate statistics about deficiences, but also links to a more detailed listing of them. Here, we import that data, and cross-reference it with the information on the directory pages."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "deficiencies = pd.read_csv(\"../data/deficiencies.csv\", na_values=[\"\\xc2\\xa0\"])\\\n",
      "    .set_index(\"facility_id\")\\\n",
      "    .join(facilities[[ \"canonical_name\" ]])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 9
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Make sure that our two files contain the same deficiency counts for each facility:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "joined = deficiencies.groupby([ deficiencies.index, \"risk_level\" ]).size().unstack().fillna(0).join(facilities)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "assert sum(joined[\"High\"]) > 0, \"Deficiency counts missing\"\n",
      "assert sum(joined[\"High\"] != joined[\"n_defic_high\"]) == 0, \"High counts in the two files are different\"\n",
      "assert sum(joined[\"Medium High\"] != joined[\"n_defic_medium_high\"]) == 0, \"Medium High counts in the two files are different\"\n",
      "assert sum(joined[\"Medium\"] != joined[\"n_defic_medium\"]) == 0, \"Medium counts in the two files are different\"\n",
      "assert sum(joined[\"Medium Low\"] != joined[\"n_defic_medium_low\"]) == 0, \"Medium Low counts in the two files are different\"\n",
      "assert sum(joined[\"Low\"] != joined[\"n_defic_low\"]) == 0, \"Low counts in the two files are different\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 11
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Most deficiencies stem from reports (e.g., phoned-in tips) and inspections:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "deficiencies[\"source\"].value_counts()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 12,
       "text": [
        "Reports                    2698\n",
        "Monitoring Inspections     2390\n",
        "Self Reported Incidents     487\n",
        "Assessments                 287\n",
        "dtype: int64"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Here, we add information from the detailed deficiencies into our main `agencies` dataframe."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "agencies[\"n_defic_high_from_inspections\"] = deficiencies[\n",
      "    (deficiencies[\"risk_level\"] == \"High\") & \n",
      "    (deficiencies[\"source\"] == \"Monitoring Inspections\") \n",
      "].groupby(\"canonical_name\").size()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "agencies[\"n_defic_high_from_reports\"] = deficiencies[\n",
      "    (deficiencies[\"risk_level\"] == \"High\") & \n",
      "    (deficiencies[\"source\"] == \"Reports\") \n",
      "].groupby(\"canonical_name\").size()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 14
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Ranking agencies by deficiencies per home"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_deficiency_rates(df, groupby=None, rounding=1):\n",
      "    cohort = df[(df[\"n_open_homes\"] >= 200)]\n",
      "    if groupby: cohort = cohort.fillna(0).groupby(groupby).sum()\n",
      "    metric = (cohort[\"n_defic_high\"] * 100.0 / cohort[\"n_open_homes\"])\n",
      "    return metric.order(ascending=False).apply(lambda x: round(x, rounding))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_deficiency_rates(agencies)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 16,
       "text": [
        "canonical_name\n",
        "TEXAS MENTOR                                   47.7\n",
        "THERAPEUTIC FAMILY LIFE                        39.4\n",
        "BENCHMARK FAMILY SERVICES                      24.3\n",
        "LUTHERAN SOCIAL SERVICES OF THE SOUTH, INC.    22.9\n",
        "ARROW CHILD AND FAMILY MINISTRIES OF TEXAS     18.5\n",
        "A WORLD FOR CHILDREN                           18.3\n",
        "BAIR FOUNDATION                                12.3\n",
        "PATHWAYS YOUTH AND FAMILY SERVICES, INC.       10.5\n",
        "BUCKNER BAPTIST CHILDRENS HOME                  9.4\n",
        "COVENANT KIDS, INC.                             8.3\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_deficiency_rates(agencies, groupby=\"mentor_affiliation\", rounding=2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 17,
       "text": [
        "mentor_affiliation\n",
        "Mentor                47.66\n",
        "Non-Mentor            17.60\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Ranking agencies by deficiencies per inspection"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Here, we calculate and rank the rate of high-level deficiencies cited per 100 inspections. We focus our attention on CPAs that have been subject to at least 100 inspections."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_inspection_rates(df, numerator=\"n_defic_high\", groupby=None, rounding=1):\n",
      "    inspection_cohort = df[(df[\"n_inspections\"] >= 100) & (df[\"n_open_homes\"] > 0)]\n",
      "    if groupby: inspection_cohort = inspection_cohort.fillna(0).groupby(groupby).sum()\n",
      "    metric = (inspection_cohort[numerator] * 100.0 / inspection_cohort[\"n_inspections\"])\n",
      "    return metric.order(ascending=False).apply(lambda x: round(x, rounding))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 18
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_inspection_rates(agencies)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 19,
       "text": [
        "canonical_name\n",
        "DBA CARING FAMILY NETWORK                      35.5\n",
        "ARROW CHILD AND FAMILY MINISTRIES OF TEXAS     34.9\n",
        "TEXAS MENTOR                                   29.0\n",
        "HOPE FOR TOMORROW                              28.5\n",
        "AZLEWAY INC                                    28.0\n",
        "DEPELCHIN CHILDREN'S CENTER                    27.2\n",
        "THE BURKE FOUNDATION CHILD PLACING AGENCY      24.5\n",
        "THE GIOCOSA FOUNDATION                         24.2\n",
        "LIFELINE CHILDREN AND FAMILY SERVICES          23.8\n",
        "BENCHMARK FAMILY SERVICES                      22.5\n",
        "FAMILY LINK TREATMENT SERVICES INC.            22.1\n",
        "PATHWAYS YOUTH AND FAMILY SERVICES, INC.       21.3\n",
        "LUTHERAN SOCIAL SERVICES OF THE SOUTH, INC.    20.6\n",
        "AGAPE MANOR HOME CPA                           20.2\n",
        "ACH CHILD AND FAMILY SERVICES                  20.0\n",
        "JAMESON CENTER                                 18.4\n",
        "THERAPEUTIC FAMILY LIFE                        17.6\n",
        "CIRCLES OF CARE                                17.4\n",
        "A WORLD FOR CHILDREN                           16.9\n",
        "BUCKNER BAPTIST CHILDRENS HOME                 16.6\n",
        "REFUGE HOUSE                                   16.4\n",
        "INTERNATIONAL EDUCATIONAL SERVICES INC         12.5\n",
        "BAIR FOUNDATION                                10.1\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_inspection_rates(agencies, groupby=\"mentor_affiliation\", rounding=2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 20,
       "text": [
        "mentor_affiliation\n",
        "Mentor                28.98\n",
        "Non-Mentor            21.49\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Here, we include __only the deficiencies that were linked to an monitoring inspection__, rather than the result of, say, an external report:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_inspection_rates(agencies, numerator=\"n_defic_high_from_inspections\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 21,
       "text": [
        "canonical_name\n",
        "DEPELCHIN CHILDREN'S CENTER                    12.7\n",
        "ARROW CHILD AND FAMILY MINISTRIES OF TEXAS     11.7\n",
        "TEXAS MENTOR                                    8.2\n",
        "BENCHMARK FAMILY SERVICES                       7.5\n",
        "PATHWAYS YOUTH AND FAMILY SERVICES, INC.        7.0\n",
        "FAMILY LINK TREATMENT SERVICES INC.             6.6\n",
        "THE GIOCOSA FOUNDATION                          6.6\n",
        "DBA CARING FAMILY NETWORK                       6.5\n",
        "HOPE FOR TOMORROW                               5.6\n",
        "THE BURKE FOUNDATION CHILD PLACING AGENCY       5.5\n",
        "AZLEWAY INC                                     5.1\n",
        "BUCKNER BAPTIST CHILDRENS HOME                  4.8\n",
        "LUTHERAN SOCIAL SERVICES OF THE SOUTH, INC.     4.8\n",
        "A WORLD FOR CHILDREN                            4.2\n",
        "AGAPE MANOR HOME CPA                            3.7\n",
        "JAMESON CENTER                                  3.5\n",
        "BAIR FOUNDATION                                 3.5\n",
        "INTERNATIONAL EDUCATIONAL SERVICES INC          3.1\n",
        "LIFELINE CHILDREN AND FAMILY SERVICES           3.0\n",
        "REFUGE HOUSE                                    2.6\n",
        "THERAPEUTIC FAMILY LIFE                         2.5\n",
        "ACH CHILD AND FAMILY SERVICES                   2.0\n",
        "CIRCLES OF CARE                                 1.9\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 21
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_inspection_rates(agencies, numerator=\"n_defic_high_from_inspections\", groupby=\"mentor_affiliation\", rounding=2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 22,
       "text": [
        "mentor_affiliation\n",
        "Mentor                8.24\n",
        "Non-Mentor            5.32\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 22
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Ranking agencies by report-related deficiencies"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Here, we look at high-level deficiencies that stemmed from what Texas calls \"[Reports](https://www.dfps.state.tx.us/Child_Care/About_Child_Care_Licensing/faq_search_texas.asp#Q15)\" \u2014\u00a0essentially, externally-submitted tips \u2014\u00a0rather than from monitoring inspections. Using those, we calculate and rank the rate of these deficiencies per 100 \"open foster homes\" (as listed on the facility directory pages), focusing on providers with at least 200 open homes."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_report_rates(df, groupby=None, rounding=1):\n",
      "    report_cohort = df[(df[\"n_open_homes\"] >= 200)]\n",
      "    if groupby: report_cohort = report_cohort.fillna(0).groupby(groupby).sum()\n",
      "    metric = (report_cohort[\"n_defic_high_from_reports\"] * 100.0 / report_cohort[\"n_open_homes\"])\n",
      "    return metric.order(ascending=False).apply(lambda x: round(x, rounding))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 23
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_report_rates(agencies)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 24,
       "text": [
        "canonical_name\n",
        "THERAPEUTIC FAMILY LIFE                        28.6\n",
        "TEXAS MENTOR                                   27.6\n",
        "LUTHERAN SOCIAL SERVICES OF THE SOUTH, INC.    13.9\n",
        "BENCHMARK FAMILY SERVICES                      13.0\n",
        "A WORLD FOR CHILDREN                           10.2\n",
        "ARROW CHILD AND FAMILY MINISTRIES OF TEXAS      8.3\n",
        "COVENANT KIDS, INC.                             6.1\n",
        "PATHWAYS YOUTH AND FAMILY SERVICES, INC.        5.8\n",
        "BAIR FOUNDATION                                 5.7\n",
        "BUCKNER BAPTIST CHILDRENS HOME                  3.9\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 24
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_report_rates(agencies, groupby=\"mentor_affiliation\", rounding=2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 25,
       "text": [
        "mentor_affiliation\n",
        "Mentor                27.57\n",
        "Non-Mentor             9.90\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 25
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "---\n",
      "\n",
      "---\n",
      "\n",
      "---"
     ]
    }
   ],
   "metadata": {}
  }
 ]
}