R_Bootcamp_Tidyverse.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Wrangling and Tidyverse  \n",
    "\n",
    "In this notebook you'll learn principles behind data wrangling and management, including tidying and transforming data to answer questions you might want to ask. Much of this workshop is adapted from `R for data science` (https://r4ds.hadley.nz/).\n",
    "\n",
    "## Some useful notes\n",
    "\n",
    "With Jupyter Notebook you can get a nice popup of function definitions just like you can in RStudio. Simply navigate to a cell or start a new one, and enter in ?function like you would normally. A popup will appear.\n",
    "\n",
    "You should see an Insert dropdown menu and Run button at the top which lets you add cells as well as run code or render Markdown in the cells, but these are very useful keyboard shortcuts for the same functions: \n",
    "\n",
    "- Shift+Enter: Run code or render Markdown in the current cell you're on\n",
    "- Esc+a: Add a cell above\n",
    "- Esc+b: Add a cell below\n",
    "- Esc+dd: Delete a cell"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Package prerequisites \n",
    "\n",
    "Packages that are required in this workshop are tidyverse (which includes the packages ggplot2, dplyr, purrr, and others), gridExtra, which helps with arranging plots next to each other, ggrepel, which helps with plotting labels, and maps, which is for map data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyr)\n",
    "library(dplyr)\n",
    "library(gridExtra)\n",
    "library(ggrepel)\n",
    "library(maps)\n",
    "library(pillar)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Frames\n",
    "\n",
    "- A data frame is another way to organize a collection of rows and columns.\n",
    "- It is a collection of lists organized into columns.\n",
    "- It is similar to a matrix, except data frames allow different data types in different columns.\n",
    "- We can use the `data.frame()` function to create a data frame from vectors using the following format:\n",
    "\n",
    "```\n",
    "dataframe <- data.frame(column_1, column_2, column_3)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "example_df <- data.frame(\n",
    "    c('a','b','c'), \n",
    "    c(1, 3, 5), \n",
    "    c(TRUE, TRUE, FALSE))\n",
    "\n",
    "print(example_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `names()` or `colnames()` to name columns,  `rownames()` to name rows, or `dimnames()` to assign both column and row names to the data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "colnames(example_df) <- c('letters', 'numbers', 'boolean')\n",
    "rownames(example_df) <- c('first', 'second', '')\n",
    "print(example_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "names(example_df) <- c('_letters_', '_numbers_', '_boolean_')\n",
    "print(example_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dimnames(example_df) <- list(c('__first', '__second', '__third'), c('__letters', '__numbers', '__boolean'))\n",
    "print(example_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `attributes()` and `str()` functions to get some information about our data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "attributes(example_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "str(example_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data frames can be classified into two broad categories: wide format and long format. All data frames shown so far have been presented in wide format. A wide format data frame has each row describe a sample and each column describe a feature. Here is a short example of a data frame in wide format, tabulating counts for three genes in three patients:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wide_df <- data.frame(c(\"A\", \"B\", \"C\"), c(1, 1, 2), c(5, 6, 7), c(0, 1, 0))\n",
    "colnames(wide_df) <- c(\"id\", \"gene.1\", \"gene.2\", \"gene.3\")\n",
    "wide_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Long format stacks features on top of one another; each row is the combination of a sample and a feature.  One column exists to denote the feature in question, and another column exists to denote that feature' value:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "long_df <- data.frame(c(\"A\", \"A\", \"A\", \"B\", \"B\", \"B\", \"C\", \"C\", \"C\"), c(\"gene.1\", \"gene.2\", \"gene.3\", \"gene.1\", \"gene.2\", \"gene.3\", \"gene.1\", \"gene.2\", \"gene.3\"), c(1, 5, 0, 1, 6, 1, 2, 7, 0))\n",
    "colnames(long_df) <- c(\"id\", \"gene\", \"count\")\n",
    "long_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These formats both contain the exact same data but represent it in different ways. Various functions exist to convert between wide and long format and we'll get into this a bit more shortly when discussing the `tidyr` package.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Adding columns to a data frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make a new example dataframe to work with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patients_1 <- data.frame(\n",
    "    c('Boo','Rex','Chuckles'), \n",
    "    c(1, 3, 5), \n",
    "    c('dog', 'dog', 'dog'))\n",
    "print(patients_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `names()` or `colnames()` to name columns,  `rownames()` to name rows, or `dimnames()` to assign both column and row names to the data frame.\n",
    "Here we will use `names()` to name the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "names(patients_1) <- c('name', 'number_of_visits', 'type')\n",
    "print(patients_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the column names to extract a single column using the notation `dataframe$column`, e.g.:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(patients_1$name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `cbind()` function can be used to add more columns to a dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_4 <- c(4, 2, 6)\n",
    "patients_1 <- cbind(patients_1, column_4)\n",
    "print(patients_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also rename individual columns of the dataframe using index notation, lets rename the 4th column we just added:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "colnames(patients_1)[4] <- 'age_in_years'\n",
    "print(patients_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use the `dataframe$column` notation to add a new column and name it at the same time:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patients_1$weight_in_pounds <- c(35, 75, 15)\n",
    "print(patients_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's use `str()` and `attributes()` functions to look at the structure and attributes of this data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "str(patients_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "attributes(patients_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data frame merging\n",
    "- Data is often spread across more than one file, reading each file into R will result in more than one data frame. \n",
    "- If the data frames have some common identifying column, we can use that common ID to combine the data frames. \n",
    "\n",
    "For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(patients_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make another data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patients_2 <- data.frame(\n",
    "    c('Fluffy', 'Smokey', 'Kitty'), \n",
    "    c(1, 1, 2), \n",
    "    c('cat', 'dog', 'cat'),\n",
    "    c(1, 3, 5))\n",
    "colnames(patients_2) <- c('name', 'number_of_visits', 'type', 'age_in_years')\n",
    "print(patients_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `merge()` function to combine them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patients_df <- merge(patients_1, patients_2, all = TRUE)\n",
    "print(patients_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Using `all = TRUE` will fill in blank values if needed (for example, the weight of any of the animals in `patients_2`).\n",
    "- Using the `all.x = TRUE` argument will return all values in the `patients_1` dataframe, as well as any entries with the same ID column(s) from `patients_2`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patients_df <- merge(patients_1, patients_2, all.x = TRUE)\n",
    "print(patients_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Using the `all.y = TRUE` argument will return all values in the `patients_2` dataframe, as well as any entries with the same ID column(s) from `patients_1`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patients_df <- merge(patients_1, patients_2, all.y = TRUE)\n",
    "print(patients_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also specify which columns to join on:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patients_df <- merge(patients_1, patients_2, by = c('name', 'type', 'number_of_visits', 'age_in_years'), all = TRUE)\n",
    "print(patients_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tidying Data\n",
    "\n",
    "Most datasets are data frames made up of rows and columns. However, talking about data frames just in terms of what rows and columns it has is not enough.\n",
    "\n",
    " * **Variable:** quantity, quality, property that can be measured.\n",
    " * **Value:** State of variable when measured.\n",
    " * **Observation:** Set of measurements made under similar conditions\n",
    " * **Tabular data:** Set of values, each associated with a variable and an observation.\n",
    "\n",
    "Tidy data:\n",
    " * Each variable is its own column\n",
    " * Each observation is its own row\n",
    " * Each value is in a single cell\n",
    " \n",
    "Benefits:\n",
    " * Easy to manipulate\n",
    " * Easy to model\n",
    " * Easy to visualize\n",
    " * Has a specific and consistent structure\n",
    " * Stucture makes it easy to tidy other data\n",
    " \n",
    "Cons:\n",
    " * Data frame is not as easy to look at\n",
    "\n",
    "Consider the following tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table1 <- data.frame(makemodel=c(\"audi a4\",\"audi a4\",\"chevrolet corvette\",\"chevrolet corvette\",\"honda civic\",\"honda civic\"),\n",
    "                    year=rep(c(1999,2008),3),\n",
    "                    cty=c(18,21,15,15,24,25),\n",
    "                    hwy=c(29,30,23,25,32,36))\n",
    "table1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is tidy data, because each column is a variable, each observation is a row, and each value is in a single cell\n",
    "\n",
    "Next we will look at some non-tidy data and operations from the **tidyr** package (part of **tidyverse**) to make the data tidy. Note that many of you might be more used to using operations from **reshape2**, like melting and casting. It's a very useful package with more functionality including aggregating data, but syntax with **tidyr** commands is simpler and more intuitive for the purposes of tidying data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## pivot_wider"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `pivot_wider` function to get our data in wide format (less rows, more columns). It accepts the following arguments:\n",
    "`id_cols` - a set of columns that uniquely identified each observation, defaults to all values in your data except what you specify in `names_from` and `values_from`. `names_from` and `values_from` describe which columns will be used to name the output column (`names_from`) and which column will be used to populate the call values (`values_from`). To see the rest of the optional arguments, use `?pivot_wider`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?tidyr::pivot_wider"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table2 <- tidyr::pivot_wider(table1,\n",
    "            names_from = year,\n",
    "            values_from = c(cty, hwy))\n",
    "table2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## pivot_longer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pivot_longer` is the inverse transformation of `pivot_wider`. It'll make your data longer (more rows, less columns). It accepts the following arguments:`cols` are the columns to pivot into longer format, `names_to` a string specifying the name of the column to create from the data stored in the column names of the input data, `values_to` a string specifying the name of the column to create from the data stored in the cell values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tidyr::pivot_longer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table3 <- tidyr::pivot_longer(table2,\n",
    "                       cols = !makemodel,\n",
    "                       names_to = 'mpg_year',\n",
    "                      values_to = 'value')\n",
    "table3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Separating"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`table3` has a `mpg_year` column that actually contains two variables, which we can separate into two columns. There are three flavors of `separate`. Each of these functions takes a string column and splits it into multiple new columns:\n",
    "\n",
    "    separate_wider_delim() splits by delimiter.\n",
    "\n",
    "    separate_wider_position() splits at fixed widths.\n",
    "\n",
    "    separate_wider_regex() splits with regular expression matches.\n",
    "\n",
    "We can split the new `mpg_year` column we just made using `separate_wider_delim`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table4 <- tidyr::separate_wider_delim(data = table3, cols = mpg_year, names = c(\"mpg\", \"year\"), delim=\"_\")\n",
    "table4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pivot_wider` to get things tidy again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table5 <- tidyr::pivot_wider(table4,\n",
    "            names_from = mpg,\n",
    "            values_from = value)\n",
    "table5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uniting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's say we want to unite `cty` and `hwy` to be one column. We can do this using the `unite` function.\n",
    "\n",
    "`Unite` will accept the following arguments: `col` the name of the new column, `sep` as the separator between values, and the columns to unite are indicated using `:` or listing them out as below.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?tidyr::unite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table6 <- tidyr::unite(table5, col = cty_hwy, cty, hwy, sep='_')\n",
    "table6"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Piping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "dplyr from tidyverse contains the 'pipe' (`%>%`) which allows you to combine multiple operations, directly taking output from a funtion as input to the next. Can save time and memory as well as make code easier to read. Can think of it this way: x %>% f(y) becomes f(x,y), and x %>% f(y) %>% g(z) becomes g(f(x,y),z), etc. As of R 4.1.0, base R also has pipe functions (`|>`). Except for some edge cases, they function very similarly to the pipes from tidyverse/magrittr, but this blog post highlights some differences (https://www.tidyverse.org/blog/2023/04/base-vs-magrittr-pipe/), which we also list here:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "+ By default, the pipe passes the object on its left-hand side to the first argument of the function on the right-hand side. `%>%` allows you to change the placement with a `.` placeholder. For example, `x %>% f(1)` is equivalent to `f(x, 1)` but `x %>% f(1, .)` is equivalent to `f(1, x)`. R 4.2.0 added a `_` placeholder to the base pipe, with one additional restriction: the argument has to be named. For example, `x |> f(1, y = _)` is equivalent to `f(1, y = x)`.\n",
    "\n",
    "+ The `|>` placeholder is deliberately simple and can’t replicate many features of the `%>%` placeholder: you can’t pass it to multiple arguments, and it doesn’t have any special behavior when the placeholder is used inside another function. For example, `df %>% split(.$var)` is equivalent to `split(df, df$var)`, and `df %>% {split(.$x, .$y)}` is equivalent to `split(df$x, df$y)`.\n",
    "\n",
    "+ With `%>%`, you can use `.` on the left-hand side of operators like `$`, `[[`, `[` , so you can extract a single column from a data frame with (e.g.) `mtcars %>% .$cyl`. R added support for this feature in R 4.3.0. For the special case of extracting a column out of a data frame, you can also use `dplyr::pull()`: `mtcars |> pull(cyl)`\n",
    "\n",
    "+ `%>%` allows you to drop the parentheses when calling a function with no other arguments; `|>` always requires the parentheses.\n",
    "\n",
    "+ `%>%` allows you to start a pipe with `.` to create a function rather than immediately executing the pipe; this is not supported by the base pipe.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the magrittr pipe or the base R pipe and they will usally do the same thing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_1999 <- tidyr::unite(table5, col = cty_hwy, cty, hwy, sep='_') %>% filter(year == 1999)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_2008 <- tidyr::unite(table5, col = cty_hwy, cty, hwy, sep='_') |> filter(year == 2008)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use the `.` placeholder to place the information on the left side of the pipe into another spot other than the first function argument, like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mtcars %>% \n",
    "   lm(mpg ~ disp, data = .)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But this will not work with base R pipes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mtcars |>\n",
    "   lm(mpg ~ disp, data = .)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'd have to do something like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mtcars |> \n",
    "   (function(x) lm(mpg ~ cyl, data = x))()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can merge tables using join function -- https://dplyr.tidyverse.org/reference/mutate-joins.html). \n",
    "\n",
    "    inner_join(): includes all rows in x and y.\n",
    "\n",
    "    left_join(): includes all rows in x.\n",
    "\n",
    "    right_join(): includes all rows in y.\n",
    "\n",
    "    full_join(): includes all rows in x or y.\n",
    "\n",
    "This will combine dataframes based on what column you specify in the `by` argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dplyr::left_join(table_1999, table_2008, by = 'makemodel')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**join() vs merge()**\n",
    "\n",
    "1. join() is faster than merge(), particularly if data  is large.\n",
    "\n",
    "2. join() preserves the original order of rows, merge() function automatically sorts the rows alphabetically based on the column you used to perform the join."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## bind_rows()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bind any number of data frames by row, making a longer result. This is similar to do.call(rbind, dfs), but the output will contain all columns that appear in any of the inputs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bind_rows(a=table_1999, b=table_2008)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "If there are different columns in the two tables, the resulting table will have some `NA` values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_1999$is_1999 <- 'yes'\n",
    "bind_rows(a=table_1999, b=table_2008)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can supply the `.id` argument to keep track of which data source each row comes from (based on the order the arguments are supplied to `bind_rows`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bind_rows(a=table_1999, b=table_2008, .id = \"ID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Not all data should be tidy\n",
    "\n",
    "Matrices, phylogenetic trees (although `ggtree` and `treeio` have tidy representations that help with annotating trees), etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transforming (Tidy) Data\n",
    "\n",
    "Now we know how to get tidy data. At this point we can already start visualizing our data. However in many cases we will need to further transform our data to narrow down variables and observations we are really interested in or to create new variables that are functions of our existing variables and data. This is known as **transforming** data.\n",
    "\n",
    " * `filter()` to pick observations (rows) by their values\n",
    " * `arrange()` to reorder rows, default is by ascending value\n",
    " * `select()` to pick variables (columns) by their names\n",
    " * `mutate()` to create new variables with functions of existing variables\n",
    " * `summarise()` to collapes many values down to a single summary\n",
    " * `group_by()` to set up functions to operate on groups rather than the whole data set\n",
    " * `%>%` propagates the output from a function as input to another. eg: x %>% f(y) becomes f(x,y), and x %>% f(y) %>% g(z) becomes g(f(x,y),z).\n",
    " \n",
    "All functions have similar structure:\n",
    " 1. First argument is data frame\n",
    " 2. Next arguments describe what to do with data frame using variable names\n",
    " 3. Result is new data frame\n",
    " \n",
    "We will be working with the **mpg** data frame for the rest of workshop which comes with the **tidyverse** library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data(mpg)\n",
    "head(mpg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `filter()` rows/observations\n",
    "\n",
    "As name suggests filters out rows. First argument is name of data frame, next arguments are expressions that filter the data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?dplyr::filter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter out 2 seater cars\n",
    "table(mpg$class)\n",
    "no_2seaters <- dplyr::filter(mpg, class != \"2seater\")\n",
    "head(no_2seaters)\n",
    "table(no_2seaters$class)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter out audis, chevys, and hondas\n",
    "mpg %>% dplyr::filter(!manufacturer %in% c(\"audi\",\"chevrolet\",\"honda\")) %>% head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `arrange()` rows/observations\n",
    "\n",
    "Changes order of rows. First argument is name of data frame, next arguments are column names (or more complicated expressions) to order by. Default column ordering is by ascending order, can use `desc()` to do descending order. Missing values get sorted at the end regardless of what column ordering is chosen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?dplyr::arrange"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# arrange/reorder mpg by class\n",
    "dplyr::arrange(mpg, class) %>% head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# arrange/reorder data frame with 2seaters filtered out by class\n",
    "# 2seaters does not appear which is as it should be\n",
    "## `arrange()` rows/observations\n",
    "\n",
    "dplyr::arrange(no_2seaters, class) %>% head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What kinds of cars have the best highway and city gas mileage?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# arrange mpg so that first hwy mileage is by descending order, then cty mileage is by descending order\n",
    "dplyr::arrange(mpg, desc(hwy), desc(cty)) %>% head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example of missing data getting placed at bottom."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df <- data.frame(x=c(5,2,NA,6))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# arrange df by ascending order, NA will be at bottom\n",
    "dplyr::arrange(df, x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# arrange df by descending order, NA will be at bottom\n",
    "dplyr::arrange(df, desc(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rest of the values are unsorted because they are all T for !is.na(x)\n",
    "dplyr::arrange(df,!is.na(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# can arrange by x again to get ascending order\n",
    "dplyr::arrange(df,!is.na(x),desc(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `select()` columns/variables\n",
    "\n",
    "Selects columns, which can be useful when you have hundreds or thousands of variables in order to narrow down to what variables you're actually interested in. First argument is name of data frame, subsequent arguments are columns to select. Can use `a:b` to select all columns between `a` and `b`, or use `-a` to select all columns *except* a."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?dplyr::select"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select manufacturer, model, year, cty, hwy\n",
    "dplyr::select(mpg, manufacturer, model, year, cty, hwy) %>% head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select all columns model thru hwy\n",
    "dplyr::select(mpg, model:hwy) %>% head\n",
    "head(mpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select all columns except cyl thru drv and class\n",
    "dplyr::select(mpg, -(cyl:drv), -class) %>% head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `mutate()` to add new variables or `transmute()` to keep only new variables\n",
    "\n",
    "Adds new columns that are functions of existing columns. First argument is name of data frame, next arguments are of the form `new_column_name = f(existing columns)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?dplyr::mutate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add a new column that takes average mileage between city and highway\n",
    "dplyr::mutate(mpg, avg_mileage = (cty+hwy)/2) %>% head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# keep only average mileage between city and highway\n",
    "dplyr::transmute(mpg,cty,avg_mileage=(cty+hwy)/2) %>% head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `summarise()` and `group_by()` for grouped summaries\n",
    "\n",
    "`summarise()` collapses a data frame into a single row, and `group_by()` changes analysis from entire data frame into individual groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?dplyr::summarise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?dplyr::group_by"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get average mileage grouped by engine cylinder\n",
    "m <- dplyr::mutate(mpg, avg_mileage=(cty+hwy)/2)\n",
    "# behavior is actually different in R/RStudio compared to notebooks\n",
    "m %>% dplyr::group_by(cyl) %>%\n",
    "    dplyr::summarise(avg=mean(avg_mileage)) %>%\n",
    "    head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:** If you look at the output of `group_by` in R/RStudio you will actually be able to see what your groupings are as well as how many of them you have. For example if we did `group_by(mpg, cyl)` the output would include `cyl [4]` which shows that our grouping is by `cyl` and there are 4 groups. Jupyter notebook doesn't display this for reasons having to do with [how data frames are outputted](https://github.com/IRkernel/repr/issues/113). Some other differences exist between how certain objects from **tidyverse** are displayed as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dplyr::group_by(m, drv) %>%\n",
    "    dplyr::summarise(avg=mean(avg_mileage))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df after group_by would show that we have 9 groups\n",
    "drv_cyl <- dplyr::group_by(m, drv, cyl) %>%\n",
    "    dplyr::summarise(avg=mean(avg_mileage)) %>%\n",
    "    dplyr::arrange(desc(avg))\n",
    "drv_cyl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Can also run `ungroup` to ungroup your observations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drv_cyl %>% dplyr::summarise(max=max(avg))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ungroup(drv_cyl) %>% dplyr::summarise(max=max(avg))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `across()` to apply a function (or functions) across multiple columns\n",
    "`across()` makes it easy to apply the same transformation to multiple columns, allowing you to use `select() semantics inside in \"data-masking\" functions like `summarise()` and `mutate()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf <-\n",
    "  tibble(g = c(1, 1, 2, 3), v1 = 10:13, v2 = 20:23) %>%\n",
    "  group_by(g)\n",
    "\n",
    "set.seed(1)\n",
    "gdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "n <- rnorm(1)\n",
    "n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf %>% mutate(across(v1:v2, ~ .x + n))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `apply()` to apply a function (or functions) across elements of a vector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df <- tibble(a = 1, b = 2, c = \"a\", d = \"b\", e = 4)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find numeric columns using `lapply()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_cols <- lapply(df, is.numeric)\n",
    "num_cols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or find numeric columns using `sapply()`, which is similar to `lapply()` but will try to simplify the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_cols_s <- sapply(df, is.numeric)\n",
    "num_cols_s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Transform each column with `lapply()` and replace the original values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[, num_cols_s] <- lapply(df[, num_cols_s, drop = FALSE], \\(x) x * 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Base R provides a stricter version of `sapply()` called `vapply()`, short for vector apply. It takes an additional argument that specifies the expected type, ensuring that simplification occurs the same way regardless of the input. For example, we could replace the `sapply()` call above with this `vapply()` where we specify that we expect `is.numeric()` to return a logical vector of length 1:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vapply(df, is.numeric, logical(1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The distinction between `sapply()` and `vapply()` is really important when they’re inside a function (because it makes a big difference to the function’s robustness to unusual inputs), but it doesn’t usually matter in data analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another important member of the apply family is tapply() which computes a single grouped summary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "diamonds %>% \n",
    "  group_by(cut) %>% \n",
    "  summarize(price = mean(price))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use the pipe functions from base R, which were introduced in R 4.1.0. They function very similarly to the pipes from tidyverse/magrittr, but this blog post highlights some differences (https://www.tidyverse.org/blog/2023/04/base-vs-magrittr-pipe/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "diamonds |> \n",
    "  group_by(cut) |> \n",
    "  summarize(price = mean(price))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.2.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}