11-pandas.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7oWvpzlHeVVe"
      },
      "source": [
        "# 7PAVITPR: Introduction to Statistical Programming\n",
        "# Python practical 11\n",
        "\n",
        "\n",
        "_Department of Biostatistics and Health Informatics<br/>\n",
        "Institute of Psychiatry, Psychology and Neuroscience<br/>\n",
        "King's College London<br/>_\n",
        "\n",
        "\n",
        "_Acknowledgment: based on parts of the course \"Big Data Analytics in Python\", written by Saga Jilka, KCL_"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_Ke-Iuj7eVVg"
      },
      "source": [
        "# Pandas\n",
        "\n",
        "__NOTE__ This notebook assumes the files brain_clinic_measures.csv and brain_participant_info.csv are in the same directory as the notebook. If you are using colab, you will need to upload the files to your colab session. If you are using Jupyter Notebook, you will need to make sure the files are in the same directory as the notebook.\n",
        "\n",
        "Python has many powerful packages to help us analyse data. Pandas is one of the most commonly used.\n",
        "\n",
        "Pandas stands for “Python Data Analysis Library”. It's great for dealing with spreadsheet-style data – by creating ‘Dataframes’. You can then easily select columns from datasets and apply functions to them.\n",
        "\n",
        "We will show how you can use pandas to manipulate and prepare data, and do some simple procesisng, using a brain volume and IQ data set. The plan is to:\n",
        "\n",
        "1. Read in two csv files for analysis\n",
        "\n",
        "2. Explore the data with pandas dataframes\n",
        "\n",
        "3. Clean and pre-process data (e.g. fill missing values)\n",
        "\n",
        "4. Compute some basic stats\n",
        "\n",
        "5. Merge datasets\n",
        "\n",
        "6. Aggregate data\n",
        "\n",
        "\n",
        "\n",
        "## <font color=green>❓ Question</font>\n",
        "Import pandas.\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "by7YWpQ_eVVh"
      },
      "outputs": [],
      "source": [
        "# Your Anaconda Python distribution comes with Pandas ready to use.\n",
        "# All you have to do to use it is ‘import’ pandas.\n",
        "# A commonly used abbreviation for Pandas is ‘pd’, and we will use this.\n",
        "\n",
        "# Import pandas...\n",
        "# YOU WRITE THIS\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "r_BiMeIaeVVm"
      },
      "source": [
        "## Data\n",
        "\n",
        "Pandas can import all sorts of files.\n",
        "\n",
        "\n",
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "Take a look at the [Pandas user guide](https://pandas.pydata.org/pandas-docs/stable/user_guide/index.html). What file formats are available?\n",
        "\n",
        "\n",
        "### DataFrames\n",
        "Files are read in to a `DataFrame` object. You can think of this as a table, with column headings and row numbers. Each row is a `Series` object. Much of Pandas useage is about manipulating `DataFrames` and `Series` objects.\n",
        "\n",
        "\n",
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "The method to read a csv file is `read_csv()`. How do you think it works?\n",
        "\n",
        "## Reading in data\n",
        "\n",
        "We will read in our fist file, a set of clinic measurements from participants in a brain study.\n",
        "\n",
        "## <font color=green>❓ Question</font>\n",
        "Using the method `read_csv()`, read the file `brain_clinic_measures.csv` in to a DataFrame called `df_m`. Apart from the file name, you shouldn't need any arguments.\n",
        "\n",
        "What object is the method going to be applied to?\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7FzjLDK1eVVo"
      },
      "outputs": [],
      "source": [
        "# Complete the code below, to read in the CSV file\n",
        "# df_m = MODULE.METHOD(FILE)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qjr3FtLVeVVr"
      },
      "source": [
        "## Viewing data\n",
        "\n",
        "There are a few ways to look at the data. For example, for a `DataFrame` called `df`:\n",
        "\n",
        "- `print(df)` - prints the  whole lot\n",
        "- `df.ndim` -  the number of dimensions (N.B. no brackets - an attribute, not a method)\n",
        "- `df.shape` - the size of each dimension (N.B. no brackets - an attribute, not a method)\n",
        "- `df.head()` - first 5 rows\n",
        "- `df.head(3)` - first 3 rows\n",
        "- `df.tail()` - last 5 rows\n",
        "- `df.tail(3)` - last 3 rows\n",
        "- `df.dtypes` - data types of the columns (N.B. no brackets - an attribute, not a method)\n",
        "- `df.describe()` - some stats\n",
        "\n",
        "## <font color=green>❓ Question</font>\n",
        "Using these methods and attributes:\n",
        "\n",
        "- find the size of the dataset\n",
        "- find the names of the columns\n",
        "- look at some examples of the data\n",
        "- get some summary statistics\n",
        "\n",
        "In order that you see all of your results in the notebook, you will find it easier to split your code across several cells. e.g. have a cell looking at the head of the data, then a cell with other display commands. You might want to do this other parts of the exercise, too.\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bwQ4FerPeVVr"
      },
      "outputs": [],
      "source": [
        "# Explore the data, writing your own code in the folowing cells,\n",
        "# to print out some of its features\n",
        "df_m.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vK_K331teVVu"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "k3uj1KDReVVx",
        "scrolled": true
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "01tZzyMceVV0"
      },
      "source": [
        "## Selecting data\n",
        "\n",
        "There are multiple ways to select data. The simplest uses slices or column names. Look at the following two examples and try them:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5vYnLXH6eVV1"
      },
      "outputs": [],
      "source": [
        "# Some examples of selecting data\n",
        "\n",
        "# select some rows: a slice\n",
        "df_m[0:10:2]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d8ZmGW7WeVV3"
      },
      "outputs": [],
      "source": [
        "# Some examples of selecting data\n",
        "\n",
        "# select a column: using an attribute for a column name\n",
        "# (we've added head(3) for compact output)\n",
        "df_m.PIQ.head(3)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OLw9iEoAeVV6"
      },
      "outputs": [],
      "source": [
        "# Some examples of selecting data\n",
        "\n",
        "# select several columns: using a list of column names\n",
        "# (we've added head(3) for compact output)\n",
        "df_m[['ppt_id', 'PIQ', 'VIQ']].head(3)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X-HH3DtHeVV9"
      },
      "source": [
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "- What does the first of the above examples do?\n",
        "- What type of object does the first and third examples return?\n",
        "\n",
        "\n",
        "The second example returns a single column, which is one dimensional."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CtQ0e1H-eVV9"
      },
      "source": [
        "### Series\n",
        "\n",
        "One dimensional data objects in Pandas are modelled by class `Series`. Examples are:\n",
        "\n",
        "- single columns from DataFrames\n",
        "- single rows from DataFrames\n",
        "\n",
        "The `Series` class supports a large number of methods. Examples include:\n",
        "\n",
        "- `sum()`\n",
        "- `mean()`\n",
        "- `count()`\n",
        "- `max()`\n",
        "- `min()`\n",
        "- `nunique()`\n",
        "- `fillna()`\n",
        "\n",
        "See [the documentation](https://pandas.pydata.org/pandas-docs/stable/reference/series.html) for a full list.\n",
        "\n",
        "## <font color=green>💬 Discussion point</font>\n",
        "Most of the above `Series` methods are self-explanatory. But what to the last two, `nunique()` and `fillna()` do?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Sby27GgyeVV-"
      },
      "source": [
        "## Combining selection operators\n",
        "\n",
        "We can combine the above operators, for example:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ea3oDLqMeVV-"
      },
      "outputs": [],
      "source": [
        "# Get a slice of one column\n",
        "\n",
        "df_m.PIQ[0:10:2]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nyDNf75deVWB"
      },
      "outputs": [],
      "source": [
        "# Get one column of a slice\n",
        "\n",
        "df_m[0:10:2].PIQ"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-iXBmDiteVWD"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "Write a statement that selects every other row from the 1st to the 9th, for just columns ppt_id, PIQ and VIQ\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "JSVrEOzceVWE"
      },
      "outputs": [],
      "source": [
        "# Write your answer below\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "twM5IekieVWG"
      },
      "source": [
        "## Better selection\n",
        "\n",
        "Although the above selection methods are intuitive to use in a notebook, they are also inneficient, and so best not used for production code. Pandas data objects also have attributes that give access to faster indexes - known as _location based indexes_. These hold additional information, and optimised access to the underlying data.\n",
        "\n",
        "The two attributes are:\n",
        "\n",
        "- `loc` - use this for selection by label\n",
        "- `iloc` - use this for selection by index number\n",
        "\n",
        "These are used with _indexers_, which are placed in square brackets and look very much like slice operators:\n",
        "\n",
        "- `Series`\t- `s.loc[indexer]`\n",
        "- `DataFrame`\t- `df.loc[row_indexer,column_indexer]`\n",
        "\n",
        "(the same works for iloc)\n",
        "\n",
        "Examples of indexers are:\n",
        "\n",
        "- explicit index of a row - `2`\n",
        "- lists of explicit column indices - `[1, 4, 5]`\n",
        "- column names - `ppt_id`\n",
        "- lists of column names - `['ppt_id', 'PIQ']`\n",
        "- slices of rows - `2:10:2`\n",
        "\n",
        "Below are a couple of examples: try them.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gtn1mv_eeVWH"
      },
      "outputs": [],
      "source": [
        "# selecting just columns - select everything with the row indexer\n",
        "df_m.iloc[:,[1,2,3]].head(3)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Dq4UbYSveVWJ"
      },
      "outputs": [],
      "source": [
        "# selecting just rows\n",
        "df_m.iloc[4:7]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QI0zSSSDeVWM"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "Write a statement that uses loc to select the 10th to 15th rows for columns ppt_id and MRI_Count\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HufFAvE5eVWM"
      },
      "outputs": [],
      "source": [
        "# Write your answer here\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ONb_2sp9eVWO"
      },
      "source": [
        "## <font color=green>💬 Discussion point</font>\n",
        "Can you notice a difference between the way index numbers apply in slices and in Pandas indexers? Try a few code examlpes to see if you can spot it."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UqPlMFkSeVWP"
      },
      "source": [
        "## Logical selectors\n",
        "\n",
        "`loc` indexers can also be logical selectors - selectors that return a truth value. The example below illustrates this:\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d6Wd1tHUeVWP"
      },
      "outputs": [],
      "source": [
        "# Select all rows where PIQ is above 140\n",
        "\n",
        "df_m.loc[df_m.PIQ > 140]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uEmF7ksWeVWT"
      },
      "source": [
        "### Interlude: understanding truth values in DataFrames\n",
        "\n",
        "\n",
        "- Up to now, we have used `and`, `or` etc. to combine truth values.\n",
        "- `and`, `or` test whether things are _logically true_\n",
        "- Python defines anything that is not empty to be _logically true_\n",
        "\n",
        "Try the following code:\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "acW2VvwyeVWT"
      },
      "outputs": [],
      "source": [
        "print( False and True )\n",
        "print( [False] and True )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Vjn4QQemeVWV"
      },
      "source": [
        "This behaviour is problematic when we want to do vector operations. Pandas uses NumPy vectors under the hood, which do not support the notion of logical truth. This is because it makes no sense when trying to combine two vectors of truth values, as both vectors are True. Instead, NumPy and Pandas override the bitwise operators such as &, |, to do element by element comparison of vector values.\n",
        "\n",
        "\n",
        "- `&` - bitwise and\n",
        "- `|` - bitwise or\n",
        "\n",
        "(there are other bitwise operators - see the [Python docs](https://docs.python.org/3/library/stdtypes.html#bitwise-operations-on-integer-types) if needed)\n",
        "\n",
        "\n",
        "We also have to account for the fact that bitwise operators are higher precedence than comparison operators. So if we have:\n",
        "\n",
        "`10 < 2 & 10 < 100`\n",
        "\n",
        "the `2 & 10` is evaluated first.\n",
        "\n",
        "To get the correct evaluation, we use parentheses, as these have a higher priority than `&`, `|`:\n",
        "\n",
        "`(10 < 2) & (10 < 100)`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gaaE22ZMeVWW"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "Write a statement that uses loc to select ppt_id, FSIQ and PIQ where PIQ is greater than 130, and FSIQ less than 140.\n",
        "\n",
        "__Hint:__ you will need to combine two truth expressions with `&`. When you do, place parentheses around each of the two truth expressions\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "X_7ytQtyeVWW"
      },
      "outputs": [],
      "source": [
        "# Your answer here - like the one below:\n",
        "# df_m.loc[(EXPRESSON 1) & (EXPRESSION 2), [COLUMN INDEXER HERE]]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ovpNKVZ7eVWZ"
      },
      "source": [
        "## <font color=green>💬 Discussion point</font>\n",
        "Why did you have to put parentheses around the two logical expressions?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-3kMD9OkeVWa"
      },
      "source": [
        "## Setting values in Pandas data objects\n",
        "\n",
        "Selectors - including slices, `loc` and `iloc` can also be used to set the values in `DataFrames` and `Series`. For example,\n",
        "\n",
        "`df.loc[df.some_column < 1, 'some_column'] = 0`\n",
        "\n",
        "will set all values less than 1 in _some_column_ to zero.\n",
        "\n",
        "You can also combine slice selectors with slice setting of values. For example,\n",
        "\n",
        "`df[['B', 'A']] = df[['A', 'B']]`\n",
        "\n",
        "will swap two columns.\n",
        "\n",
        "This last technique does not work with `loc` and `iloc` - see the [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html) for details of how to do this with `loc` and `iloc`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MBBNkaHheVWa"
      },
      "source": [
        "## Deleting data\n",
        "\n",
        "Now that we can select bits of our data objects, we can start to do things with the data. First, we will see how to delete bits.\n",
        "\n",
        "### Deleting columns\n",
        "\n",
        "\n",
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "Our DataFrame has a strange column \"Unnmamed: 0\". What do you think it is?\n",
        "\n",
        "There are a few ways we could delete this column. For example, using the selector operators we looked at above, we could keep just the columns we want, like this:\n",
        "\n",
        "`df_m = df_m[['ppt_id', 'FSIQ', 'VIQ', 'PIQ', 'MRI_Count']]`\n",
        "\n",
        "We are instead going to use the `drop` method. This takes as arguments:\n",
        "\n",
        "- column name\n",
        "- axis=n, where n=1 denotes a column, and n=0 denotes a row\n",
        "\n",
        "Run the example below to see this:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "UZUtE6sreVWb"
      },
      "outputs": [],
      "source": [
        "# remove the column \"Unnamed: 0\" - an artefact from some earlier\n",
        "# data processing\n",
        "df_m = df_m.drop('Unnamed: 0', axis=1)\n",
        "df_m.head(3)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qvdndBDkeVWd"
      },
      "source": [
        "### Deleting rows\n",
        "\n",
        "We can also use `drop()` to remove rows. If we know the index(es) of our rows, we can use `drop()`, as above (with axis=0).\n",
        "\n",
        "If we have a condition, we can select what we want to keep. Let's say we need to remove participant with ppt_id=17 (perhaps they have withdrawn from the study). We could use the followig - run it:\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qdDXqJD-eVWd"
      },
      "outputs": [],
      "source": [
        "df_m = df_m[df_m.ppt_id != 17]\n",
        "df_m[15:18]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EIyp0OcDeVWl"
      },
      "source": [
        "## Reading in a second dataset\n",
        "\n",
        "\n",
        "The code above has imported a set of clinic measurements. Now we need to read in further  information for the same participants. This is in a file called `brain_participant_info.csv`\n",
        "\n",
        "## <font color=green>❓ Question</font>\n",
        "Do the following:\n",
        "\n",
        "1. Read in the csv file `brain_participant_info.csv` into a dataframe called `df_p`\n",
        "1. Take a look at it using some of the `DataFrame` methods given above\n",
        "1. Remove the artefact column, 'Unnamed: 0'\n",
        "1. Remove participant ppt_id=17\n",
        "1. Take a look at the DataFrame again, to check all is well\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5SZeogCueVWl"
      },
      "outputs": [],
      "source": [
        "# Put your answer in this cell and the next one\n",
        "\n",
        "# Read in the csv file\n",
        "# YOU WRITE THIS\n",
        "\n",
        "\n",
        "# Take a look\n",
        "# YOU WRITE THIS\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "iry8iVGQeVWn"
      },
      "outputs": [],
      "source": [
        "# Drop the 'Unnamed: 0' column\n",
        "# YOU WRITE THIS\n",
        "\n",
        "\n",
        "# Remove ppt_id=17\n",
        "# YOU WRITE THIS\n",
        "\n",
        "\n",
        "# Take a look\n",
        "# YOU WRITE THIS\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vFxiNA73eVWq"
      },
      "source": [
        "## Merging Datasets\n",
        "\n",
        "Our two dataframes contain data about the same study participants. We can merge them.\n",
        "\n",
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "What identifies the rows in df_m that should be merged with those in df_p?\n",
        "\n",
        "The code to do the merge is given below. Run it:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "QcYMnouweVWq"
      },
      "outputs": [],
      "source": [
        "# create one merged DataFrame\n",
        "df_all = df_m.merge(df_p, on = ['ppt_id'], how = 'left')\n",
        "df_all.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jWfThrbIeVWs"
      },
      "source": [
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "\n",
        "Take a look at the [DataFrame.merge() documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html#pandas-dataframe-merge).\n",
        "\n",
        "Can you explain the three arguments in the merge above?\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZTfpgip8eVWt"
      },
      "source": [
        "## Aggregating data\n",
        "\n",
        "- We use the `DataFrame.groupby()` method to aggregate data.\n",
        "- It returns a `GroupBy` object\n",
        "- There is also a `GroupBy` for `Series`.\n",
        "- `GroupBy` has many useful methods - take a look at the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/groupby.html#groupby)\n",
        "\n",
        "Below is an exmaple of using `groupby()` to group be gender. Note that you nee the `numeric_only` argument to stop it trying to calculate the mean for non-numeric columns. Try it:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pQwsvNGbeVWt"
      },
      "outputs": [],
      "source": [
        "# Group our merged dataset by gender, and examine the mean\n",
        "\n",
        "df_all.groupby(\"Gender\").mean(numeric_only=True)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_0JG4-SUeVWv"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "\n",
        "How do you think you aggregate by two columns? Try it below,\n",
        "\n",
        "- aggregate by the gender and diagnosed columns\n",
        "- compute the medians\n",
        "- display only Height and Weight in your table\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NTgm_jcCeVWv"
      },
      "outputs": [],
      "source": [
        "# What if you want to investigate two categorical variables?\n",
        "# Group by gender and diagnosed, and show the medians of\n",
        "# the four categories\n",
        "# YOUR ANSWER HERE\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iewhxkBOeVWx",
        "scrolled": true
      },
      "source": [
        "## Transposing DataFrames and GroupBys\n",
        "\n",
        "- _Transposing_ a two dimensional data object reflects it about its diagonal\n",
        "- Rows become columns, and columns become rows\n",
        "- This is useful for getting data in the right form for presenting\n",
        "- Examples of objects that cab be reflected\n",
        "  - DataFrame\n",
        "  - The display output of DataFrame operations, e.g. mean(), median(), describe()\n",
        "- Use the method `transpose()`\n",
        "- (the attribute `T` also works but is not so readable)\n",
        "  \n",
        "Run the example below, which flips the output of `describe()` for our dataset."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "t8Ojjzu5eVWx",
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "# Transpose summary statistics of the merged dataset\n",
        "df_all.describe().transpose()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n5Hv_o2leVWz"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "\n",
        "`transpose()` will also work with the output of `groupby()`. Try this:\n",
        "\n",
        "- Group `df_all` by gender and diagnosed\n",
        "- Select just the MRI_Count column\n",
        "- Get summary statistics for this column\n",
        "- Transpose the summary statistics\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7L5X-MJjeVW0"
      },
      "outputs": [],
      "source": [
        "# Your answer below - you should be able to do this on one line\n",
        "# Group by gender and diagnosed\n",
        "# Select MRI_Count\n",
        "# Get summary stats\n",
        "# Transpose them\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "g6dfhX5UeVW2"
      },
      "source": [
        "## Working with missing data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZWZTwvKpeVW2"
      },
      "source": [
        "### Finding missing data\n",
        "\n",
        "Let's take another look at the summary stats for our aggregated data - run this:\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uV3mx_1VeVW3"
      },
      "outputs": [],
      "source": [
        "# Get summary stats\n",
        "df_all.describe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5i2w6JZseVW4"
      },
      "source": [
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "\n",
        "Take a look at the counts of our columns.\n",
        " - Can you see anything out of place? What could be the problem?\n",
        " - Looking at the [DataFrame documentation](https://pandas.pydata.org/pandas-docs/stable/reference/frame.html#conversion) can you see a method that might help detect this problem?\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SYgN2hvfeVW5"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "\n",
        "Using the method you found above,\n",
        "\n",
        "1. create a new `DataFrame` which records whether or not each value in `df_all` is missing\n",
        "1. Use `loc` with this new `DataFrame`, and a logical selector, display all rows where either Height or Weight are missing\n",
        "\n",
        "__Hint:__ Remember to use the correct logical operator (e.g. &, |) and remember to use parentheses around the parts of your logical expression.\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rs12j613eVW5",
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "# Which rows have mssing Heights or Weights?\n",
        "# Complete the code below\n",
        "\n",
        "# First make a DataFrame of True / False for all missing values\n",
        "missing = df_all.PUT_THE_METHOD_HERE()\n",
        "\n",
        "# Now use .loc to select those rows where Height is missing or Weight is missing\n",
        "missing.loc[CONDITION]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Yh55XbJgeVW7"
      },
      "source": [
        "### How are we going to deal with the missing data?\n",
        "\n",
        "We could:\n",
        "\n",
        "- delete (method `DataFrame.dropna()` does this)\n",
        "- interpolate with new values (Pandas supports this)\n",
        "- impute with mean values\n",
        "\n",
        "We will do the latter. We will make use of the `DataFrame.fillna()` method.\n",
        "\n",
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "Take a look at the [documentation for DataFrame.fillna()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html#pandas.DataFrame.fillna)\n",
        "\n",
        "We will use this argument - what does it do?\n",
        "  - `value`\n",
        "\n",
        "The below code does a `fillna()` on Weight, filling with the mean of Weight. Try it, and note that the previous missing weight in the second row now takes the mean value:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gwYya34SeVW7"
      },
      "outputs": [],
      "source": [
        "# Fill NAs and display\n",
        "\n",
        "# Before...\n",
        "print( df_all.loc[:,['Weight']].head(3) )\n",
        "\n",
        "# Let's take a look at the mean first, so we know what to expect\n",
        "mean = df_all.loc[:,['Weight']].mean()\n",
        "print('\\nmean:', mean)\n",
        "\n",
        "# After..\n",
        "df_all.loc[:,['Weight']].fillna(mean).head(3)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wPPJds3ueVW9",
        "scrolled": true
      },
      "source": [
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "- Anyone see a problem with this approach?\n",
        "- How can we overcome this?\n",
        "\n",
        "### `transform()`\n",
        "\n",
        "We will use the `transform` method, which works on `Series` and `DataFrames`\n",
        "\n",
        "- `transform()` takes as an argument the name of a method on the object it is transforming\n",
        "- the method name is given as a string\n",
        "- it returns the result of applying that method to the DataFrame.\n",
        "\n",
        "Take a look at the example below:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "p7eU6moleVW9"
      },
      "outputs": [],
      "source": [
        "# Replace all values with the mean for the gender\n",
        "\n",
        "df_all.groupby(\"Gender\").transform(\"mean\", numeric_only=True).head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IaLjHh36eVXA"
      },
      "source": [
        "### Putting it all together\n",
        "\n",
        "Now that we can transform all of our values to means, let's use `fillna()` to change just those where we have no value.\n",
        "\n",
        "Take a look at the answer below.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "exZPuCmVeVXA"
      },
      "outputs": [],
      "source": [
        "# Get means for gender\n",
        "means_for_gender = df_all.groupby(\"Gender\").transform(\"mean\", numeric_only=True)\n",
        "\n",
        "# Use this to fill in the NAs - this time inplace is True\n",
        "df_all.loc[:,[\"Weight\"]] = df_all.loc[:,[\"Weight\"]].fillna(means_for_gender)\n",
        "\n",
        "df_all.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ECkhtuOJeVXC"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "\n",
        "Fill in all empty Heights with the mean for participant gender.\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XiwQGPd3eVXD"
      },
      "outputs": [],
      "source": [
        "# Fill missing Height values with the mean for the Gender\n",
        "# YOUR ANSWER HERE\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EqN-m8J5eVXE"
      },
      "source": [
        "## <font color=green>💬 Discussion point</font>\n",
        "\n",
        "Can you see how both Height and Weight empty values could have been filled at the same time?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xIWVOv__eVXF"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "\n",
        "Check to see if there are any empty values in the aggregated dataset.\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wx-FS0jceVXF"
      },
      "outputs": [],
      "source": [
        "# Now let's check to see if we have any missing values\n",
        "# YOUR ANSWER HERE\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LOwC6NYCeVXG"
      },
      "source": [
        "## Exporting data\n",
        "\n",
        "Now we've done our transofrmations and analysis, how do we get the data out, e.g. in to another program, or for publication?\n",
        "\n",
        "Pandas has support for export to many formats, as described in [the documentation](https://pandas.pydata.org/pandas-docs/stable/reference/frame.html#serialization-io-conversion).\n",
        "\n",
        "Here's one useful idea, sending a transposed summary of gender and diagnosed to the clipboard, so that you can paste it in to another program. Try it:\n",
        "\n",
        "__NOTE__ This might not work with all operating systems"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "acWq5tCpeVXH"
      },
      "outputs": [],
      "source": [
        "# Prepare your tables for publication?\n",
        "# Copy to clipboard and then format in Excel\n",
        "\n",
        "df_all.groupby(['Gender','Diagnosed']).describe().transpose().to_clipboard()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_RnUIbXQeVXK"
      },
      "source": [
        "## <font color=green>❓ Question</font>\n",
        "\n",
        "Referring to [the documentation](https://pandas.pydata.org/pandas-docs/stable/reference/frame.html#serialization-io-conversion), export a transposed summary of gender and diganosed to an Excel, Stata or Latex file (depending on your preference), and open in another program (e.g. Excel, Stata, or your favourite editor).\n",
        "\n",
        "## <font color=green>⌨️ Your answer</font>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vuoqvMIGeVXK"
      },
      "outputs": [],
      "source": [
        "# Export some data to a file\n",
        "# df_all.groupby(['Gender','Diagnosed']).describe().transpose().COMPLETE_THIS"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "11-pandas.ipynb",
      "provenance": []
    },
    "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.12"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}