tu018-Pandas-Advanced-WorkingWithData.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas, indexing and other advanced data manipulation features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The past few tutorials were focussed on `Pandas`. We met some of the basic data structures in pandas.\n",
    "\n",
    "Basic pandas objects:\n",
    "\n",
    "* Index\n",
    "* Series\n",
    "* Data Frame\n",
    "\n",
    "We also learned how these three things are related. Namely, we can think of a pandas `DataFrame` as being composed of several *named columns*, each of which is like a `Series`, and a special `Index` column along the left-hand side.\n",
    "\n",
    "This tutorial focuses on more advanced `pandas` options to accessing, addressing (indexing) and manipulating data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Learning goals:\n",
    "\n",
    "* advanced pandas objects methods – the \"verbs\" that make them do useful things\n",
    "* indexing and accessing row/column subsets fo data\n",
    "* grouped data: aggregation and pivot tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make a data frame to play with"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get started this time instead of loading data from file, we will build a little data frame and take look at it to remind ourselves of this structure. We'll build a data frame similar to a data set mentioned in a previosu tutorial."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, import `pandas` because of course, and `numpy` in order to simulate some data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd    \n",
    "import numpy as np     # to make the simulated data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can make the data frame. It will have 4 variables of cardiovascular data for a number of patients (the number of patiencts can be specified):\n",
    "\n",
    "  * systolic blood pressure\n",
    "  * diastolic blood pressure\n",
    "  * blood oxygenation\n",
    "  * pulse rate\n",
    "\n",
    "Given that Pandas `DataFrame`s have a special `index` column, we'll just use the `index` as \"patient ID\" instead of making a fifth variable dedicated to it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_patients = 10    # specify the number of patients"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use `Numpy` to simulate data by choosing a mean for each variable and a standard deviation. More specifically, the systolic blood pressure will have a mean of `125` and a standard deviation of `5`. The diastolic pressure will have a lower mean (`80`) but the same standard deviation, the blood oxygenation will have a mean of `98.5` and a smaller standard deviation of `0.3`. Finally, the pulse rate will have a mean of `65` abd a standard deviation of `2`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sys_bp = np.int64(125  + 5*np.random.randn(num_patients,))\n",
    "dia_bp = np.int64(80   + 5*np.random.randn(num_patients,))\n",
    "b_oxy  = np.round(98.5 + 0.3*np.random.randn(num_patients,), 2)\n",
    "pulse  = np.int64(65   + 2*np.random.randn(num_patients,))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will build the data frame using a dictionary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a dictionary with a \"key\" for each variable name, and \n",
    "# the \"values\" being the num_patients long data vectors\n",
    "df_dict = {'systolic BP' : sys_bp,\n",
    "           'diastolic BP' : dia_bp,\n",
    "           'blood oxygenation' : b_oxy,\n",
    "           'pulse rate' : pulse\n",
    "           }\n",
    "    \n",
    "our_df = pd.DataFrame(df_dict)    # Now make a data frame out of the dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now lets look at it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$\\color{blue}{\\text{Complete the following exercise.}}$\n",
    "\n",
    "  - Use the cell below to create a dataframe with the following data:\n",
    "       - 16 patients\n",
    "       - systolic blood pressure 10% higher than the current\n",
    "       - diastolic blood pressure 5% lower\n",
    "       - blood oxygenation 2% higher\n",
    "       - a 4% higher pulse rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see the nice structure of the `DataFrame` object. We have four columns corresponding to our measurement variables, and each row is an \"observation\" which, in the case, corresponds to an individual patient."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To appreciate some of the features of a pandas `DataFrame`, let's compare it with a numpy `Array` holding the same information. (Which we can do because we're only dealing with numbers here - one of the main features of a pandas data frame is that it can hold non-numeric information too)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_array = np.transpose(np.vstack((sys_bp, dia_bp, b_oxy, pulse)))\n",
    "our_array"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$\\color{blue}{\\text{Complete the following exercise.}}$\n",
    "\n",
    "  - Explore what `.vstack` does, use the `markdown` cell below to explain what it does in your own words  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  - Use the following code cell to show a few examples where you create a numpy array and use vstack to change it, explain why you use chose those operations as examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see here that our array, `our_array`, contains exactly the same information as our dataframe, `our_df`. There are 3 main differences between the two:\n",
    "\n",
    "* they have different verbs – things they know how to do \n",
    "* we have more ways to access the information in a data frame\n",
    "* the data frame could contain non-numeric information (e.g. gender) if we wanted"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Also notice that the data frame is just prettier when printed than the numpy array)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verbs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at some verbs. Intuitively, it seems like both variables should *know* how to take a mean. Let's see."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_array.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So the numpy array does indeed know how to take the mean of itself, but it takes the mean of the entire array by default, which is not very useful in this case. If we want the mean of each variable, we have to specify that we want the means of the columns (i.e. row-wise means)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_array.mean(axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But look what happens if we ask for the mean of our data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Visually, that is much more organized! We have the mean of each of our variables, nicely labled by the variable name."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data frames can also `describe()` themselves."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Gives us a nice summary table of the data in our data frame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Numpy arrays don't know how to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_array.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data frames can also make histograms and boxplots of themselves. They aren't publication quality, but super useful for getting a feel for our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.hist();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.boxplot();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a complete listing of what our data frame knows how to do, we can type `our_df.` and then hit the tab key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$\\color{blue}{\\text{Complete the following exercise.}}$\n",
    "\n",
    "  - Use the next cell to report and describe two methods of `our_df`, explain why you chose those two."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's return to the `mean()` function, and see what, exactly, it is returning. We can do this by assigning the output to a variable and looking at its type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_means = our_df.mean()\n",
    "our_means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(our_means)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So it is a pandas series, but, rather than the index being 0, 1, 2, 3, the *index values are actually the names of our variables*. \n",
    "\n",
    "If we want the mean pulse rate, *we can actually ask for it by name!*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_means['pulse rate']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This introduces another key feature of pandas: **you can access data by name**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$\\color{blue}{\\text{Complete the following exercise.}}$\n",
    "\n",
    "  - Use the cell below to return the diastolic blood pressure from `our_means`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Accessing data by name is kind of a big deal. It makes code more readable and faster and easier to write.\n",
    "\n",
    "So, for example, let's say we wanted the mean pulse rate for our patients. Using numpy, we would have to remember or figure our which column of our numpy array was pulse rate. And we'd have to remember that Python indexes start at 0. *And* we'd have to remember that we have to tell numpy to take the mean down the columns explicitly. Ha. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So our code might look something like..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np_style_means = our_array.mean(axis = 0)\n",
    "pulse_mean = np_style_means[3]\n",
    "pulse_mean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compare that to doing it the pandas way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_means = our_df.mean()\n",
    "our_means['pulse rate']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pandas way makes it very clear what we are doing! People like things to have names and, in pandas, things have names."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$\\color{blue}{\\text{Complete the following exercise.}}$\n",
    "\n",
    "  - Use the cell below to compute the mean of the `diastolic pressure` both using the `numpy` method and the `pandas` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing data using square brackets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look ot our litte data frame again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can grab a column (variable) by name if we want:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df['pulse rate']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Doing this creates another `DataFrame` (or `Series`), so it knows how to do stuff to. This allows us to do things like, for example, compute the mean pulse rate in one step instead of two. Like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df['pulse rate'].mean()   # creates a series, then makes it compute its own mean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can grab as many columns as we want by using a list of column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "needed_cols = ['diastolic BP', 'systolic BP']   # make a list\n",
    "our_df[needed_cols]                             # use the list to grab columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could also do this in one step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df[['diastolic BP', 'systolic BP']]  # the inner brackets define our list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " (although the double brackets might look a little confusing at first)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$\\color{blue}{\\text{Complete the following exercise.}}$\n",
    "\n",
    "  - Use the cell below to extract blood oxygenation and pulse rate using a single line of code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting row and row/column combinations of data: \"indexing\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Terminology Warning!** \"Indexing\" is a general term which means \"accessing data by location\". In pandas, as we have seen, objects like DataFrames also have an \"index\" which is a special column of row identifiers. So, in pandas, we can index data using column names, row names (indexing using the index), or both. (We can also index into pandas data frames as if they were numpy arrays, which sometimes comes in handy.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Changing the index to make (row) indexing more intuitive"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Speaking of indexes, it's a little weird to have our patient IDs start at \"0\". Both because \"patient zero\" has a special meaning and also because it's just not intuitive to number a sequence of actual things starting at \"0\".\n",
    "\n",
    "Fortunately, pandas `DataFrame` (and `Series`) objects allow you to customize their index column fairly easily."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's set the index to start at 1 rather than 0:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_ind = np.linspace(1, 10, 10)  # make a sequence from 1 to 10\n",
    "my_ind = np.int64(my_ind)        # change it from decimal to integer (not really necessary, but...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at this index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(my_ind)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.index = my_ind"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$\\color{blue}{\\text{Complete the following exercise.}}$\n",
    "\n",
    "  - Use the next cell to create a new index variable using numpy the variable should start at 5 and cintinue to 15 with 10 steps in between "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing data using `pd.DataFrame.loc[]`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the section above, we saw that you can get columns of data our of a data frame using square brackets `[]`. Pandas data frames also know how to give you subsets of rows or row/column combinations.  \n",
    "\n",
    "The primary method for accessing specific bits of data from a pandas data frame is with the `loc[]` verb. It provides an easy way to get rows of data based upon the index column. In other words, `loc[]` is the way we use the data frame index as an index!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So this will give us the data for patient number 3:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.loc[3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note!** The above call did **not** behave like a Python or numpy index! If it had, we would have gotten the data for patient number 4 because Python and numpy use *zero based indexing*. \n",
    "\n",
    "But using the `loc[]` function gives us back the row \"named\" 3. We literally get what we asked for! Yay!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also *slice* out rows in chunks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.loc[3:6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which, again, gives us what we asked for without having to worry about the zero-based business."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But `.loc[]` also allows us to get specfic columns too. Like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.loc[3:6, 'blood oxygenation']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a single column, or:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.loc[3:6,'systolic BP':'blood oxygenation']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "for multiple columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In summary, there are 3 main ways to get chunks of data out of a data frame \"by name\".\n",
    "\n",
    "* square brackets (only) gives us columns, e.g. `our_df['systolic BP']`\n",
    "* `loc[]` with one argument gives us rows, e.g. `our_df.loc[3]`\n",
    "* `loc[]` with two arguments gives us row-column combinations, e.g. `our_df.loc[3,'systolic BP']`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Additionally, with `loc[]`, we can specify index ranges for the rows or columns or both, e.g. `new_df.loc[3:6,'systolic BP':'blood oxygenation']`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One final thing about using `loc[]` is that the index column in a `DataFrame` doesn't have to be numbers. It can be date/time strings (as we'll see later on), or just plain strings (as we've seen above with `Series` objects)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$\\color{blue}{\\text{Complete the following exercise.}}$\n",
    "\n",
    "  - Use the next cell to create a data frame of heart measurements where the index is the name of the patients (name and surname, make them up!):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at a summary of our data using the `describe()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_sum = our_df.describe()\n",
    "our_sum"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This looks suspiciously like a data frame except the index column looks like they're... er... not indexes. Let's see."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(our_sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yep, it's a data frame! But let's see if that index column actually works:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_sum.loc['mean']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that, with a `Series` object, we use square brackets (only) to get  rows. With a `DataFrame`, square brackets (only) are used to get columns. It won't work for `DataFrame` objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_sum['mean']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, with a `DataFrame`, we have to use `.loc[]` to get rows."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now we can slice out (get a range of) rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_sum.loc['count':'std']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or rows and columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_sum.loc['count':'std', 'systolic BP':'diastolic BP']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing data using pd.DataFrame.iloc[]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Occasionally, you might want to treat a pandas `DataFrame` as a numpy `Array` and index into it using the *implicit* row and column indexes (which start as zero of course). So support this, pandas `DataFrame` objects also have an `iloc[]`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at our data frame again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And let's check its shape:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At some level, then, Python considers this to be just a 10x4 array (like a numpy array). This is were `iloc[]` comes in; `iloc[]` will treat the data frame as though it were a numpy array – no names!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So let's index into `our–df` using `iloc[]`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.iloc[3]  # get the fourth row"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And compare that to using `loc[]`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.loc[3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And of course you can slice out rows and columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_df.iloc[2:5, 0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Indexing using `iloc[]` is rarely needed on regular data frames (if you're using it, you should probably be working with a numpy `Array`).\n",
    "\n",
    "It is, however, very handy for pulling data out of summary data tables (see below)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Non-numerical information (categories or factors)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the huge benefits of pandas objects is that, unlike numpy arrays, they can contain categorical variables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make another data frame to play with"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's use tools we've learned to make a data frame that has both numerical and categorical variables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we'll make the numerical data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_patients = 20    # specify the number of patients\n",
    "\n",
    "# make some simulated data with realistic numbers.\n",
    "sys_bp = np.int64(125 + 5*np.random.randn(num_patients,))\n",
    "dia_bp = np.int64(80 + 5*np.random.randn(num_patients,))\n",
    "b_oxy = np.round(98.5 + 0.3*np.random.randn(num_patients,), 2)\n",
    "pulse = np.int64(65 + 2*np.random.randn(num_patients,))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Now we'll make them interesting – this will be clear later)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sys_bp[0:10] = sys_bp[0:10] + 15\n",
    "dia_bp[0:10] = dia_bp[0:10] + 15\n",
    "sys_bp[0:5] = sys_bp[0:5] + 5\n",
    "dia_bp[0:5] = dia_bp[0:5] + 5\n",
    "sys_bp[10:15] = sys_bp[10:15] + 5\n",
    "dia_bp[10:15] = dia_bp[10:15] + 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's make a categorical variable indicating whether the patient is diabetic or not. We'll make the first half be diabetic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "diabetic = pd.Series(['yes', 'no'])  # make the short series\n",
    "diabetic = diabetic.repeat(num_patients/2)       # repeat each over two cell's worth of data\n",
    "diabetic = diabetic.reset_index(drop=True)      # reset the series's index value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(diabetic)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now will make an \"inner\" sex variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sex = pd.Series(['male', 'female'])             # make the short series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(sex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sex = sex.repeat(num_patients/4)                 # repeat each over one cell's worth of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(sex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sex = pd.concat([sex]*2, ignore_index=True)   # stack or \"concatenate\" two copies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(sex)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we'll make a dictionary containing all our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a dictionary with a \"key\" for each variable name, and \n",
    "# the \"values\" being the num_patients long data vectors\n",
    "df_dict = {'systolic BP' : sys_bp,\n",
    "           'diastolic BP' : dia_bp,\n",
    "           'blood oxygenation' : b_oxy,\n",
    "           'pulse rate' : pulse,\n",
    "           'sex': sex,\n",
    "           'diabetes': diabetic\n",
    "           }\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And turn it into a data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = pd.DataFrame(df_dict)    # Now make a data frame out of the dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's up our game and make a more descriptive index column!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "basename = 'patient '                      # make a \"base\" row name\n",
    "my_index = []                             # make an empty list\n",
    "for i in range(1, num_patients+1) :       # use a for loop to add\n",
    "    my_index.append(basename + str(i))     # id numbers so the base name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assign our new row names to the index of our data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.index = my_index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at our creation!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Looking at our data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another really nice thing about pandas `DataFrames` is that they naturally lend themselves to interrogation via the visualization library `Seaborn` (we will learn about this library more in future tutorials). \n",
    "\n",
    "So let's peek at some stuff."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "\n",
    "sns.catplot(data=new_df, x='diabetes', y='systolic BP');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, now let's go crazy and do a bunch of plots."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.catplot(data=new_df, x='sex', y='systolic BP', hue='diabetes');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.catplot(data=new_df, x='sex', y='pulse rate', hue='diabetes');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.catplot(data=new_df, x='sex', y='diastolic BP', hue='diabetes', kind='bar');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Computing within groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have an idea of what's going on, let's look at how we could go about computing things like the mean systolic blood pressure in females vs. males, etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using the `groupby()` method"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data frames all have a `group_by()` method that, as the name implies, will group our data by a categorical variable. Let's try it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.groupby('sex')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So this gave us a `DataFrameGroupBy` object which, in and of itself, is very useful. However, *it knows how to do things*!\n",
    "\n",
    "In general, `GroupBy` objects know how to do pretty much anything that regular `DataFrame` objects do. So, if we want the mean by gender, we can ask the `GroupBy` (for short) object to give us the mean:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "new_df.groupby('sex').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using the `groupby()` followed by `aggregate()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More powerfully, we can use a `GroupBy` object's `aggregate()` method to compute many things at once."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.groupby('diabetes').aggregate(['mean', 'std', 'min', 'max'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, what's going on here? First, we got a lot of information out. Second, we got a warning because pandas couldn't compute the mean, etc., on the gender variable, which is perfectly reasonable of course."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can handle this by using our skills to carve out a subset of our data frame – just the columns of interest – and then use `groupby()` and `aggregate()` on that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp_df = new_df[['systolic BP', 'diastolic BP', 'diabetes']]        # make a data frame with only the columns we want\n",
    "our_summary = temp_df.groupby('diabetes').aggregate(['mean', 'std', 'min', 'max'])     # compute stuff on those columns\n",
    "our_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice here that there are *groups of columns*. Like there are two \"meta-columns\", each with four data columns in them. This makes getting the actual values out of the table for further computation, etc., kind of a pain. It's called \"multi-indexing\" or \"hierarchical indexing\". It's a pain.\n",
    "\n",
    "Here are a couple examples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_summary[(\"systolic BP\", \"mean\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_summary.loc[(\"no\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, we could do the blood pressure variables separately and store them for later plotting, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp_df = new_df[['systolic BP', 'diabetes']]        # make a data frame with only the columns we want\n",
    "our_summary = temp_df.groupby('diabetes').aggregate(['mean', 'std', 'min', 'max'])     # compute stuff on those columns\n",
    "our_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But we still have a meta-column label!\n",
    "\n",
    "Here's were `.iloc[]` comes to the rescue!\n",
    "\n",
    "If we look at the shape of the summary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_summary.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that, ultimately, the data is just a 2x4 table. So if we want, say, the standard deviation of non-diabetics, we can just do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_summary.iloc[0, 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we get back a pure number."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also do things \"backwards\", that is, instead of subsetting the data and then doing a `groupby()`, we can do the `groupby()` and then index into it and compute what we want. For example, if we wanted the mean of systolic blood pressure grouped by whether patients had diabetes or not, we could go one of two ways.\n",
    "\n",
    "We could subset and then group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df[['systolic BP', 'diabetes']].groupby('diabetes').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or we could group and then subset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.groupby('diabetes')[['systolic BP']].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, first, it's cool that there are multiple ways to do things. Second – **aarrgghh!** – things are starting to get complicated and code is getting hard to read!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using pivot tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"Pivot tables\" (so named because allow you to look at data along different dimensions or directions) provide a handy solution for summarizing data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, pivot tables tabulate the mean of data. So if we wish to compute the average systolic blood pressure broken out by diabetes status, all we have to do is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.pivot_table('systolic BP', index='diabetes')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, `index` is used in the \"row names\" sense of the word."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also have another grouping variables map to the columns of the output if we wish:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.pivot_table('systolic BP', index='diabetes', columns='sex')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we can specify pretty much any other summary function we want to \"aggregate\" by:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.pivot_table('systolic BP', index='diabetes', columns='sex', aggfunc='median')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to customize the column names using the aggregate function, you can (Though it is somewhat limited)! Look at the example down below for an explanation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.groupby('diabetes').aggregate(Mean=('systolic BP',\"mean\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The \"Mean\" is your new title, while inside the second set of parantheses is where/what you wantthe aggregate function to calculate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, as you might have noticed, this is fairly limited. It removes the meta column titles, replacing them with the title of your choice. This can make it somewhat dificult to interpret your tables. Additionally, you can't have any spaces in the new title of your choice. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " new_df.groupby('diabetes').aggregate(Mean=('systolic BP',\"mean\"),\n",
    "                                      Standard_Deviation = ('systolic BP',\"std\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## vs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.groupby('diabetes').aggregate( Mean=('systolic BP',\"mean\"), STD = ('systolic BP',\"std\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Where `aggfunc` can me 'min', 'sum', 'std', etc., etc.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this tutorial, we have covered some key aspects of working with data using pandas data frames. These were:\n",
    "\n",
    "* doing things with data using the methods – the verbs – of pandas objects\n",
    "* accessing subsets of the data with \n",
    "     - square brackets\n",
    "     - the `.loc[]` method\n",
    "     - the `.iloc[]` method\n",
    "* assembling data frames and customizing the index\n",
    "* grouping data and computing summaries using\n",
    "     - `groupby()` and `aggregate()`\n",
    "     - pivot tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## $\\color{blue}{\\text{Complete the following exercise.}}$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Make a data frame that has\n",
    "    * one categorical variable, \"bilingual\", that splits the data in half (\"yes\" and \"no\")\n",
    "    * two numerical variables, verbal GRE and quant GRE \n",
    "    * (you can build in, or not, whatever effect of bilingual you wish)\n",
    "    * (GRE scores have a mean of about 151 and a std. dev. of about 8.5)\n",
    "2. Set the index to be \"Student 1\", \"Student 2\", etc.\n",
    "3. Do a seaborn plot of verbal GRE vs. bilinguality (is that a word?)\n",
    "4. Make another one of quant GRE vs. bilingual status\n",
    "5. Compute the mean and standard *error* of each score separated by bilingual status (using any method you wish!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "cd78fef2128015050713e82ca51c6520b11aee7c9ee8df750520bbbc7384cbaa"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}