midwife-led-units_calculate_transferrals.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analysing data on Midwife Led Units (MLUs) \n",
    "\n",
    "This notebook outlines a basic process for analysing a dataset based on FOI requests to hospital trusts, resulting in [this story](http://www.bbc.co.uk/news/uk-england-37471091). [The GitHub repo for this story can be found here](https://github.com/BBC-Data-Unit/midwife-led-units).\n",
    "\n",
    "First, import the `pandas` library (with an alias of `pd`), and import the data into a variable called `mludata`. Use the `info()` method to find out what columns it has:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 52 entries, 0 to 51\n",
      "Data columns (total 11 columns):\n",
      "NHS Trust                                              52 non-null object\n",
      "When the midwife-led unit opened                       45 non-null object\n",
      "Midwife-unit                                           52 non-null object\n",
      "Nearest consultant-led unit                            48 non-null object\n",
      "Distance to consultant in miles                        52 non-null object\n",
      "Alternative shorter distance in miles                  1 non-null float64\n",
      "Deliveries commenced at midwife-led unit 2015-16       52 non-null int64\n",
      "Deliveries completed at midwife-led unit 2015-16       52 non-null int64\n",
      "Transferred from midwife unit to consultant 2015-16    52 non-null int64\n",
      "% transferred 2015-16                                  52 non-null object\n",
      "Deliveries commenced in consultant-led unit 2015-16    36 non-null float64\n",
      "dtypes: float64(2), int64(3), object(6)\n",
      "memory usage: 4.5+ KB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "mludata = pd.read_csv(\"https://raw.githubusercontent.com/BBC-Data-Unit/midwife-led-units/master/Midwife-led%20units%20BBC%20investigation.csv\")\n",
    "mludata.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The key question is how many patients end up being transferred from the MLU to a consultant. That information is stored in the \"Transferred from midwife unit to consultant 2015-16\" column as a whole number, and \"% transferred 2015-16\" as a percentage. \n",
    "\n",
    "Adding up or averaging percentages isn't a good idea, unless you're going to weight those percentages by the size of the MLU that it represents (you don't want to treat one MLU with a 90% transferral rate but only 10 patients as just the same as one with 10% transferral rate but 100 patients)\n",
    "\n",
    "Instead it's best to calculate new overall percentages based on the numbers.\n",
    "\n",
    "Let's get just the column names using the `.columns` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'NHS Trust', u'When the midwife-led unit opened', u'Midwife-unit',\n",
       "       u'Nearest consultant-led unit ', u'Distance to consultant in miles',\n",
       "       u'Alternative shorter distance in miles',\n",
       "       u'Deliveries commenced at midwife-led unit 2015-16',\n",
       "       u'Deliveries completed at midwife-led unit 2015-16',\n",
       "       u'Transferred from midwife unit to consultant 2015-16',\n",
       "       u'% transferred 2015-16',\n",
       "       u'Deliveries commenced in consultant-led unit 2015-16'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mludata.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because the names have spaces, they are best accessed using square brackets like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    230\n",
       "1    569\n",
       "2     78\n",
       "3    560\n",
       "4     74\n",
       "Name: Transferred from midwife unit to consultant 2015-16, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mludata['Transferred from midwife unit to consultant 2015-16'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But it's easier to [rename using `.rename()`](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rename.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'NHS Trust', u'When the midwife-led unit opened', u'Midwife-unit',\n",
       "       u'Nearest consultant-led unit ', u'Distance to consultant in miles',\n",
       "       u'Alternative shorter distance in miles',\n",
       "       u'Deliveries commenced at midwife-led unit 2015-16',\n",
       "       u'Deliveries completed at midwife-led unit 2015-16', u'transferred1516',\n",
       "       u'% transferred 2015-16',\n",
       "       u'Deliveries commenced in consultant-led unit 2015-16'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mludata = mludata.rename(columns={'Transferred from midwife unit to consultant 2015-16':'transferred1516'})\n",
    "mludata.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now to find out the total number of patients transferred in 2015/16, using `.sum()` on that column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10489"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mludata.transferred1516.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To work out what that is as a percentage of all deliveries, we need to divide it by the total. First, let's rename the column showing completed deliveries in each MLU and then calculate a total of all completed deliveries in all trusts:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28060"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mludata = mludata.rename(columns={'Deliveries completed at midwife-led unit 2015-16':'deliveriescompleted1516'})\n",
    "mludata.deliveriescompleted1516.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on those two figures, then - 10,489 transferrals to consultant led units against 28,060 deliveries completed in MLUs - we can guess that the proportion is going to be somewhere around a quarter. But a simple division doesn't give us that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mludata.transferred1516.sum() / (mludata.transferred1516.sum()+mludata.deliveriescompleted1516.sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why is that? Well, it's to do with the *type* of data which is being stored in that column: it's an *integer*, or whole number.\n",
    "\n",
    "A percentage, on the other hand, almost always has decimal places: it is a *float* (50%, for example, is expressed as 0.5, 75% would be 0.75 and so on. Only 100% - 1 - would be an integer).\n",
    "\n",
    "You can see this in action by trying to divide 2 by 4. 2 out of 4 should come out as 50%, but instead we get 0 again:\n",
    "\n",
    "*Note: this isn't a problem in Python 3, where two integers divided by each other can produce a float. In Python 2, however, we need to solve it*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "2/4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding a decimal place to those two numbers, however, changes the result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "2.0/4.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have two options to solve our problem: add decimal places to the column values (in other words, change the data type for those numbers from integers to floats); or add decimal places to the totals themselves. Here's how to do the latter by using the `float()` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.27209525538924484"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "float(mludata.transferred1516.sum()) / (float(mludata.transferred1516.sum())+float(mludata.deliveriescompleted1516.sum()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could also divide the transferrals by all deliveries *commenced* at the MLU (in other words, what proportion of those deliveries which began in an MLU ended up transferring to a consultant led unit) - which gives us the same result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.27209525538924484"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "float(mludata.transferred1516.sum()) / float(mludata['Deliveries commenced at midwife-led unit 2015-16'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we can work out what the split is between pregnancies that commence in a consultant led unit, and those which begin in an MLU. This time I've added a couple of print commands so you can see the parts of the calculation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total Deliveries commenced at midwife-led unit 2015-16: 38549\n",
      "total Deliveries commenced at consultant-led unit 2015-16: 141961.0\n",
      "total Deliveries commenced in either unit 2015-16: 180510.0\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.21355603567669382"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print 'total Deliveries commenced at midwife-led unit 2015-16:', mludata['Deliveries commenced at midwife-led unit 2015-16'].sum()\n",
    "print 'total Deliveries commenced at consultant-led unit 2015-16:', mludata['Deliveries commenced in consultant-led unit 2015-16'].sum()\n",
    "print 'total Deliveries commenced in either unit 2015-16:', mludata['Deliveries commenced at midwife-led unit 2015-16'].sum()+mludata['Deliveries commenced in consultant-led unit 2015-16'].sum()\n",
    "float(mludata['Deliveries commenced at midwife-led unit 2015-16'].sum())/(float(mludata['Deliveries commenced at midwife-led unit 2015-16'].sum())+float(mludata['Deliveries commenced in consultant-led unit 2015-16'].sum()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that `float` is applied to the *results* of the `sum` *before* it is then divided by the other number. If you try to apply `float` *after* the division you still get zero, only with a decimal place added, because the result is still zero *before* the conversion to float is applied."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "float(mludata.transferred1516.sum() / mludata.deliveriescompleted1516.sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While we're on data types, notice that the percentages column actually contains text, not numbers, because the percentage symbol is interpreted as a text character rather than a number. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 52 entries, 0 to 51\n",
      "Data columns (total 11 columns):\n",
      "NHS Trust                                              52 non-null object\n",
      "When the midwife-led unit opened                       45 non-null object\n",
      "Midwife-unit                                           52 non-null object\n",
      "Nearest consultant-led unit                            48 non-null object\n",
      "Distance to consultant in miles                        52 non-null object\n",
      "Alternative shorter distance in miles                  1 non-null float64\n",
      "Deliveries commenced at midwife-led unit 2015-16       52 non-null int64\n",
      "deliveriescompleted1516                                52 non-null int64\n",
      "transferred1516                                        52 non-null int64\n",
      "% transferred 2015-16                                  52 non-null object\n",
      "Deliveries commenced in consultant-led unit 2015-16    36 non-null float64\n",
      "dtypes: float64(2), int64(3), object(6)\n",
      "memory usage: 4.5+ KB\n"
     ]
    }
   ],
   "source": [
    "mludata.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So if you try to add those numbers you'll get a result like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'27%34%17%58%22%29%35%20%18%28%21%30%37%23%30%37%20%19%17%20%21%32%31%23%17%21%25%31%22%16%22%23%25%23%22%19%29%12%20%17%19%0%27%40%34%19%27%12%32%31%32%24%'"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mludata['% transferred 2015-16'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning a percentages column from strings to numbers\n",
    "\n",
    "Here's how to sort that column. First, we need to replace all the % symbols (which is what causes Python to interpret the column as a series of strings) using `.str.replace()` - and create a new column in the dataset containing the results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original column looks like this:\n",
      "0    27%\n",
      "1    34%\n",
      "2    17%\n",
      "3    58%\n",
      "4    22%\n",
      "Name: % transferred 2015-16, dtype: object\n",
      "---\n",
      "After replacing all % symbols it looks like this:\n",
      "0    27\n",
      "1    34\n",
      "2    17\n",
      "3    58\n",
      "4    22\n",
      "Name: %transferred1516, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print 'Original column looks like this:\\n', mludata['% transferred 2015-16'].head()\n",
    "mludata['%transferred1516'] = mludata['% transferred 2015-16'].str.replace('%','')\n",
    "print '---'\n",
    "print 'After replacing all % symbols it looks like this:\\n', mludata['%transferred1516'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To convert a column to a number, use `pd.to_numeric()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 52 entries, 0 to 51\n",
      "Data columns (total 12 columns):\n",
      "NHS Trust                                              52 non-null object\n",
      "When the midwife-led unit opened                       45 non-null object\n",
      "Midwife-unit                                           52 non-null object\n",
      "Nearest consultant-led unit                            48 non-null object\n",
      "Distance to consultant in miles                        52 non-null object\n",
      "Alternative shorter distance in miles                  1 non-null float64\n",
      "Deliveries commenced at midwife-led unit 2015-16       52 non-null int64\n",
      "deliveriescompleted1516                                52 non-null int64\n",
      "transferred1516                                        52 non-null int64\n",
      "% transferred 2015-16                                  52 non-null object\n",
      "Deliveries commenced in consultant-led unit 2015-16    36 non-null float64\n",
      "%transferred1516                                       52 non-null int64\n",
      "dtypes: float64(2), int64(4), object(6)\n",
      "memory usage: 4.9+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "mludata['%transferred1516'] = pd.to_numeric(mludata['%transferred1516'])\n",
    "#Check if it's worked - among the columns you should see that %transferred1516 is \"52 non-null int64\", meaning an integer\n",
    "mludata.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that that column is numeric, we can calculate the average percentage of transferrals across all units"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24.807692307692307"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mludata['%transferred1516'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that this is different from the percent we got when we divided the total transferrals by the total deliveries. This is because some units will be bigger than others, so as explained earlier, this figure is less reliable and should not be used. If we were to describe it we might say something like \"The average midwife led unit transferred 25% of deliveries to a consultant led unit\", whereas the earlier figure can be described as \"27% of patients in midwife led units were transferred to a consultant led unit\" - much simpler and clearer."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}