---
title: Efficient data management 
author: YOUR NAME HERE
date: today
format: pdf
execute:
  fig-height: 4
  message: false
  warning: false
  error: true
---

When working with data you must:

1. Figure out what you want to do.
2. Precisely describe what you want in the form of a computer program.
3. Execute the code.

The dplyr package makes each of these steps as fast and easy as possible by:

1. Elucidating the most common data manipulation operations, so that your
   options are helpfully constrained when thinking about how to tackle a problem.
2. Providing simple functions that correspond to the most common data 
   manipulation verbs, so that you can easily translate your thoughts into code.
3. Using efficient data storage back ends, so that you spend as little time 
   waiting for the computer as possible.

```{r}
library(_________)
_________ <- nycflights13::flights
```

## Tibbles

The `flights` data set, and any data set created with `dplyr`, has a specific data type called a `tibble`. These are not as furry and prolific as their cousins the `tribbles`. `tibbles` behaves for all intents and purposes as a `data.frame`, just gets displayed differently. For example, the `flights` data set contains data on 19 characteristics (variables) from 336,776 flights. There's no way I would want to print out a data set that large. But I'm gonna....  

```{r}
flights
```

The output has been trimmed to something more reasonable for our viewing pleasure. This may not seem such a big deal because R Studio already provides some level of truncation for our viewing pleasure, but when you accidentally print out 400 pages of data set in your homework assignment, your instructor (and your wallet if you print it without looking) won't be happy. 

## Basic verbs

The `dplyr` package contains many functions to perform data manipulation "actions", also called verbs. We will look at the following verbs: 

* `filter()`: Returns a subset of the rows
* `select()`: Returns only the listed columns
* `rename()`: Renames the variables listed
* `mutate()`: Adds columns from existing data
* `relocate()`: Moves the order of the columns 
* `case_when()`: A useful expansion of `ifelse`
* `summarise()`: Reduces each group to a single row by calculating aggregate measures
* `group_by()`: Groups a data set on a factor variable, such that all functions performed are then done on each level of the factor


### Filter

`filter()` allows you to select a subset of the rows of a data frame. 
The first argument is the name of the data frame, and the second and subsequent are filtering expressions evaluated in the context of that data frame. For example, we can select all flights on January 1st with 

```{r}
_________(flights, month == 1, day == 1)
```

`filter()` works similarly to `subset()` except that you can give it any number of filtering conditions which are joined together with &. You can use other Boolean operators explicitly. Here we select flights in January or February.

```{r}
_________(_________, month == 1 _________ month == 2)
```

### Select

Often you work with large data sets with many columns where only a few are actually of interest to you. `select()` allows you to rapidly zoom in on a useful subset using operations that usually only work on numeric variable positions.

```{r}
_________(flights, _________)
```

You can use a colon (:) to select all columns physically located between two variables. 
```{r}
_________(flights, _________)
```

To exclude specific columns you use the minus sign (-)
```{r}
_________(_________, -_________)
```

This also works to exclude all columns EXCEPT the ones between two variables. 
```{r}
_________(flights, -(_________))
```

### Rename

Sometimes variables come to you in really obscure naming conventions. What the heck is SBA641? The `rename()` function converts an `old name` to a `new name`. The generic syntax is `rename(new = old)`. 

So to rename `dep_time` to `departure_time` we would type

```{r}
_________(flights, _________ = _________)
```

The variable name on the 3rd column now says `departure_time` instead of `dep_time`. 

### Mutate

As well as selecting from the set of existing columns, it's often useful to add new columns that are functions of existing columns. This is the job of `mutate()`!

Here we create two variables: gain (as arrival delay minus departure delay) and speed (as distance divided by time, converted to hours). 
```{r}
a <- _________(flights,
                _________ = arr_delay - dep_delay, 
                _________ = distance / air_time * 60)
select(a, gain, distance, air_time, speed)
```


One key advantage of mutate is that you can refer to the columns you just created. In one mutate call we can create the `gain` variable, and then use it to create `gain_per_hour`. 

```{r}
_________(flights, _________ = arr_delay - dep_delay, 
                _________ = _________ / (_________ / 60 ))
```  


### Summarize

The last verb is `summarise()`, which is most often used to calculate summary statistics. 
```{r}
_________(flights, 
          ave_departure_delay = _________(dep_delay, na.rm = TRUE), 
          n_flights_with_delay = _________(!is.na(dep_delay)>0), 
          max_depart_delay = _________(dep_delay, na.rm=TRUE)
)
```

It may seem like a lot of code compared to base R functions such as `mean()`, `sum()` and `max()`, but we'll see how this is used in combination with grouping in the next section to easily create grouped summary statistics.  

### Arrange

The `arrange()` function changes the order of the rows for the whole data set based on the value of the variable being sorted. 

Lets use a smaller data set with fewer variables so it's easier to visualize. 

```{r}
________ <- select(flights, distance, origin, dep_delay)
```

#### Sort by one variable

Sort flights based on distance, from shortest to longest. This is Ascending order.
```{r}
________(flights2, ________)
```

To reverse the sort order, use `desc()`.  This is Decending order. 

```{r}
________(________, ________(distance))
```

### Relocate

Use `relocate()` to change column positions, using the same syntax as select() to make it easy to move blocks of columns at once.

**Current order of names** for reference purposes. 
```{r}
names(flights)
```

**Bring `origin` to the front**

```{r}
flights <- flights %>% 
  _________(_________)
names(flights)
```

**Move `dest` right after `origin`** using the `.after` argument. 

```{r}
flights <- flights %>% 
  _________(_________, _________)
names(flights)
```

**Move multiple variables to the end** 
```{r}
flights <- flights %>% 
  _________
names(flights)
```

### Case when

This function allows you to vectorise multiple `if_else()` statements. Each case is evaluated sequentially and the first match for each element determines the corresponding value in the output vector. If no cases match, the `.default` is used as a final "else" statment.

The general syntax is `logical statment` ~ `value`.
On the left hand side you specify a logical statement to identify a certain "case", then a tilde `~`, then on the right hand side you specify what you want the value of the new variable to be when this case is true. 
```{r}
flights <- flights %>%  
  _________(distance_cat = _________(  
    _________ ~ "short",  
    distance > 400 & distance <= 1000 ~ "_________", 
   _________)
  )
  
```

:::{.callout-tip title = "👉 Trust but verify" icon=false}
Create a grouped boxplot of `distance` against `distance_cat` to confirm that this recode worked. 

```{r}
_________(flights, y="distance", fill = "distance_cat")
```

:::

This `case_when` function can be used with complex logical statements involving multiple variables. Additionally, you don't have to specify every single possible combiantion. If none of the statments specified evaluate as TRUE, the `.default` value will be used. 

```{r}
_________ <- _________ %>% # <1> 
  _________(_________ = _________( # <2> 
             dep_delay > 0 & arr_delay > 0 ~ "two delays", # <2> 
             dep_delay <= 0 & arr_delay <= 0 ~ "no delays", # <2> 
             _________ ~ "one delay" # <3> 
           )
  )
table(flights$delay_type, useNA="always")
```

1. Take the `flights` data set, and then, 
2. Create a new variable called `delay_type` based on whether or not there was a delay at both sides (departure and arrival), if there was no delays either departing or arriving
3. If neither of these two statments are true, there is a delay at either the departure or arrival and so we set `delay_type` to "one delay". 


## Grouped Operations  

The above verbs are useful, but they become really powerful when you combine them with the idea of "group by", repeating the operation individually on groups of observations within the dataset. In dplyr, you use the `group_by()` function to describe how to break a dataset down into groups of rows. You can then use the resulting object in exactly the same functions as above; they'll automatically work "by group" when the input is a grouped.

Let's demonstrate how some of these functions work after grouping the flights data set by month. First we'll create a new data set that is grouped by month. 

```{r}
by_month <- _________(flights, month)
```

The `summarise()` verb allows you to calculate summary statistics for each group. This is probably the most common function that is used in conjunction with `group_by`. For example, the average distance flown per month. 
```{r}
summarise(_________, avg_airtime = _________)
```

Or simply the total number of flights per month, 
```{r}
_________(by_month, _________)
``` 

Or even more interstingly, the average departure delay, number of flights with delays, and the maximum length of delay at each of the three origin airports. 

```{r}
by_airport <- _________
summarise(_________, 
          ave_departure_delay = mean(dep_delay, na.rm = TRUE), 
          n_flights_with_delay = sum(!is.na(dep_delay)>0), 
          max_depart_delay = max(dep_delay, na.rm=TRUE)
)
```

Seems like Laguardia (LGA) is the better airport for the least delays. 

## Chaining Operations 


:::{.callout-important title = "Same pipe, different symbol"}
We've seen chaining before using the base R pipe `|>`. The `%>%` symbol is also called a pipe, but is only accessible after you load the `dplyr` package. They function the same. We show both here because you will see both used out in the wild and need to know they are interchangeable. 
:::

Consider the following group of operations that take the data set `flights`, and produce a final data set (`a4`) that contains only the flights where the daily average delay is greater than a half hour. 
```{r}
_________ <- group_by(_________, year, month, day)
_________ <- select(_________, arr_delay, dep_delay)
_________ <- summarise(_________,
                arr = mean(arr_delay, na.rm = TRUE),
                dep = mean(dep_delay, na.rm = TRUE))
_________ <- filter(_________, arr > 30 | dep > 30)
head(_________)
```

It does the trick, but what if you don't want to save all the intermediate results (`a1` - `a3`)? Well these verbs are `function`, so they can be wrapped inside other functions to create a nesting type structure.  
```{r}
#| eval: false
filter(
  summarise(
    select(
      group_by(flights, year, month, day),
      arr_delay, dep_delay
    ),
    arr = mean(arr_delay, na.rm = TRUE),
    dep = mean(dep_delay, na.rm = TRUE)
  ),
  arr > 30 | dep > 30
)
```

Woah, that is HARD to read! This is difficult to read because the order of the operations is from inside to out, and the arguments are a long way away from the function. To get around this problem, dplyr provides the `%>%` operator. `x %>% f(y)` turns into `f(x, y)` so you can use it to rewrite multiple operations so you can read from left-to-right, top-to-bottom:

```{r}

```

1. Take the `flights` data set and then, 
2. `group` it by date and then, 
3. `select`s the necessary variables, and then, 
4. calculate the mean arrival and departure delays, and then, 
5. `filter` to only keep rows with a daily average delay over half hour. 

The same 4 steps that resulted in the `a4` data set, but without all the intermediate data saved! This can be **very important** when dealing with Big Data. `R` stores all data in memory, so if your little computer only has 2G of RAM and you're working with a data set that is 500M in size, your computers memory will be used up fast. `a1` takes 500M, `a2` another 500M, by now your computer is getting slow. Make another copy at `a3` and it gets worse, `a4` now likely won't even be able to be created because you'll be out of memory. 



