Solutions_2_dplyr.qmd

---
output:
  html_document:
    df_print: paged
    code_download: TRUE
    toc: true
    toc_depth: 1
editor_options:
  chunk_output_type: console
---

```{r, setup, include=FALSE}
# you don't need to run this when working in RStudio
knitr::opts_chunk$set(eval=FALSE)  # when making the html version of this file, don't execute the code
```

This is an [R Markdown](https://rmarkdown.rstudio.com/) document. Follow the link to learn more about R Markdown and the notebook format used during the workshop.

# Setup

```{r, eval=T}
library(tidyverse)
```

The data is from the [Stanford Open Policing Project](https://openpolicing.stanford.edu/data/) and includes vehicle stops by the Evanston police in 2017. We're reading the data in from a URL directly.

```{r, eval=TRUE}
police <- read_csv("https://raw.githubusercontent.com/nuitrcs/r-tidyverse/main/data/ev_police.csv",
                   col_types=c( "location"="c"))
```

This file goes into more detail on the select and filter functions we talked about before, and adds in mutate for changing variables or making new ones.

# Select: Choose Columns

The previous session covered the basics of `select` but there are many more options for how we can specify which columns to choose.

First, let's remember what the column names are:

```{r, eval=F}
names(police)
```

Recall, the `select` function takes as the first input a data frame, and then we can list one or more columns, names unquoted, that we want to select. The columns will be ordered in the order we specify them. Notice that the order in the output is different than the original order above.

```{r, eval=F}
police %>% select(outcome, date)
select(police, outcome, date)
```

## Ranges

There are a number of select helper functions and special syntax options that allow us to choose multiple columns.

First, we can use : for range, but with names in addition to numbers:

```{r, eval=F}
police %>% select(raw_DriverRace:raw_ResultOfStop)
```

The result is the same if we use numbers.

```{r, eval=F}
police %>% select(26:29)
```

We can select the rightmost columns with `last_col()`. You can check with `names` that it is the "last" column in our dataset.

```{r, eval=F}
police %>% select(last_col())
```

You can use this to count columns from the right:

```{r}
select(police, last_col(offset=3))
```

This is one of the few places R acts like it's counting from 0, that's because it's the number of columns to offset from the end:

```{r}
# these are the same
select(police, last_col(0)) %>% names()
select(police, last_col()) %>% names()
```

### EXERCISE

Select just the first 6 columns of police.

```{r}
police %>% 
  select(1:6)
police %>% 
  select(raw_row_number:subject_age)

select(police, raw_row_number:subject_age)
```

## Excluding columns

We can also say which columns we don't want by putting a `-` (minus) in front of the name. Think about it as subtracting one or more columns. The idea of subtracting makes a lot of sense -- after the command below, we have now 27 rather than the original 29 columns in the data.

```{r, eval=F}
police %>% select(-raw_row_number, -subject_age)
```

When using negated `-` column names, if we start with negations and try to mix in columns we want to keep, it will get messy. Make sure to *avoid* the following code:

```{r, eval=F}
police %>% select(-raw_row_number, -subject_age, time:outcome) 
```

To both specify the columns wanted and exclude some that would otherwise be selected, the exclusions need to come at the end:

```{r, eval=F}
police %>% select(time:outcome, -subject_age) 
```

We don't need to include -raw_row_number -- it is not part of the range between time and outcome. If we do include it, we will still get the same result.

```{r, eval=F}
police %>% select(time:outcome, -subject_age, -raw_row_number) 
```

### EXERCISE

Drop the last 4 columns from `police` (temporarily - write an expression that will give you police with those columns not included).

```{r}
police %>% 
  select(1:25)

police %>% 
  select(raw_row_number:vehicle_year)

police %>% 
  select(-26:-29)

# with last_col:
police %>% select(-last_col(0):-last_col(3)) # 25 columns

police %>% select(1:last_col(4)) # 25 columns

# not a recommended answer, but an answer given in class that also works:
police %>% 
  select(-last_col()) %>% 
  select(-last_col()) %>% 
  select(-last_col()) %>% 
  select(-last_col())
```

## Reordering and renaming

We've already seen that columns will appear in the result in the order that we list the names.

If we list a column name more than once, it will appear in the first position only (it won't be repeated):

```{r}
select(police, date, time, location, date)
```

If you want to pull a few columns over to the left so that they are the ones that show first when you look at the data, the everything() helper function can be useful.

```{r, eval=F}
select(police, outcome, everything())
```

We can also rename columns while using `select()`. The syntax is `new_name = old_name`.

```{r, eval=F}
police %>% select(raw_id=raw_row_number, date, time)
```

or we can use `rename()` to only rename, without affecting which columns are included or their order (all of the columns are kept in the same order):

```{r, eval=F}
police %>% rename(raw_id=raw_row_number)
```

Remember, this doesn't change police because we didn't save the result. So far, we've just been printing the copy of the data frame that is returned by the function. If we want to change our data frame, we'd need to save the result back to the `police` object. We can also save the changes in another object with a different name such as `police_new`.

### EXERCISES

Remember: run the cells above to load tidyverse and import the data.

Using `select` and/or `rename` as needed:

-   Rename subject_age to age, subject_race to race, and subject_sex to sex, but keep the columns in their original order
-   Exclude the department_id and department_name columns

Hint:

-   Remember that you can chain dplyr commands together with `%>%`
-   You don't need to save the results in a new variable

```{r}
names(police)

police %>% 
  rename(age=subject_age, race=subject_race, sex=subject_sex) %>% 
  select(-department_id, -department_name)

police %>% 
  select(age=subject_age, race=subject_race, sex=subject_sex, everything(), -department_id, -department_name)
```

## Matching names

We can also select by matching patterns in the names of the columns. The patterns to match are in quotes because they aren't column names -- just character data.

```{r, eval=F}
police %>% select(starts_with("contraband"))
```

```{r, eval=F}
police %>% select(ends_with("issued"))
```

```{r, eval=F}
police %>% select(contains("vehicle"))
```

We can also put a `-` in front of these helper functions to exclude columns:

```{r, eval=F}
police %>% select(-contains("subject"))
```

And there are even more [select helper functions](https://tidyselect.r-lib.org/reference/language.html).

### EXERCISE

Use `select()` to get a copy of `police` without the columns that start with "raw".

In other words, drop the columns that start with "raw", except 1) don't permanently change `police` and 2) instead of dropping, we're really selecting what we want to keep instead.

```{r, }
police %>% 
  select(-starts_with("raw"))
```

## Selecting with Vectors or Functions

What if we have the names of the columns we want to select in a vector already? For example:

```{r, eval=TRUE}
analysis_vars <- c("search_basis", "reason_for_stop")
```

Perhaps we built this vector programatically (we wrote code to determine the values, instead of typing them ourselves), so we can't just rewrite it to:

```{r, eval=F}
police %>% select(search_basis, reason_for_stop)
```

If we just give the vector to `select`, it looks like we expect "analysis_vars" to be a column name in police. We get a warning:

```{r}
police %>% select(analysis_vars)
```

This warning tells us what we should do instead, which is use `all_of`:

```{r, eval=F}
police %>% select(all_of(analysis_vars))
```

This makes it clearer that "analysis_vars" isn't the name of a column in police.

What if we want to select columns of a certain type -- for example, only the numeric columns?

```{r, eval=F}
police %>% select(where(is.numeric))
```

`is.numeric` is the name of a function. We just use the name without `()`. This function is applied to each column, and if it returns TRUE, then the column is selected. Like above with using a vector, we wrap the function we want to use in `where` to make it clear that we're using a function, not looking for a column named "is.numeric").

`where` can be used with any function that returns a *single* TRUE or FALSE value for each column.

### EXERCISE

Select columns with logical (TRUE/FALSE) data from police:

```{r}
police %>% 
  select(where(is.logical))
```

## Combining multiple select conditions

What if we want to combine multiple select conditions? For example, select columns from police that contain "vehicle" and are character data?

We can use & and \|

```{r}
select(police, contains("vehicle"))
select(police, contains("vehicle") & where(is.character))
```

# Filter: Choose Rows

The `filter()` function lets us choose which rows of data to keep by writing expressions that return TRUE or FALSE for every row in the data frame. Recall:

```{r, eval=F}
police %>%  filter(date == "2017-01-02")
filter(police, date == "2017-01-02")
```

We can do complex conditions as we could do with `[]`

```{r, eval=F}
police %>% filter(subject_race == "hispanic" & subject_sex == "female")
```

If we include multiple comma-separated conditions, they are joined with `&` (and). So this following is equivalent to the above.

```{r, eval=F}
police %>% filter(subject_race == "hispanic", subject_sex == "female")
```

### EXERCISES

1.  Filter `police` to choose the rows where location is 60201 or 60202
2.  Filter `police` to choose the rows where location is 60201 or 60202, and subject_sex is "male"

Hints:

-   The "or" operator is `|`; the "and" operator is `&`

```{r}
police %>% 
  filter(location == "60201" | location == "60202") %>% 
  filter(subject_sex == "male")

police %>% 
  filter((location == "60201" | location == "60202") & subject_sex == "male") 

police %>% 
  filter(location == "60201" | location == "60202", subject_sex == "male")

```

## Including Variable Transformations

When filtering, we can include transformations of variables in our expressions. To see this, we'll use the built-in `mtcars` dataset.

Here's what `mtcars` looks like:

```{r}
mtcars
```

Now, let's filter to see which cars have above average mpg:

```{r, eval=F}
mtcars %>% filter(mpg > mean(mpg))
```

Or which car has the most horsepower (hp):

```{r,  eval=F}
mtcars %>% filter(hp == max(hp))
```

### EXERCISES

Using `mtcars`, find the car with the minimum (`min()`) displacement (disp) value:

```{r}
mtcars %>% 
  filter(disp == min(disp))
```

Find cars that do not have the median number of cylinders.

```{r}
mtcars %>% 
  filter(cyl != median(cyl))
```

## EXERCISE

Using `police`: find observations that are not moving violations. The violation variable has a few categories. Take a look:

```{r}
table(police$violation)
```

Write code to find stops that don't start with "Moving Violation".

str_detect() is a useful function for this. Example:

```{r}
police %>%
  filter(str_detect(subject_race, "w"))
```

Now find the stops that are NOT moving violations:

```{r}
police %>% 
  filter(!str_detect(violation, fixed("Moving Violation", ignore_case=T)))
```

# Bonus: slice variants

Last session, we saw `slice()` briefly as a way to choose which rows we want by their integer index value. But, there are some useful variants on the `slice` function that help us select rows that have the maximum or minimum value of a particular variable:

```{r, eval=TRUE}
mtcars %>% slice_max(hp)
slice_max(mtcars, hp)
```

By default it just gives us one maximum value (all rows that have that value though), but we can ask for more than one highest value by setting the `n` argument:

```{r, eval=TRUE}
slice_max(mtcars, hp, n=3)
```

We got 4 rows above because there was a tie at position 3. There's an option `with_ties` that can change how ties are handled.

There's also a minimum version:

```{r, eval=TRUE}
slice_min(mtcars, disp)
```

Like we did with `slice_max`, you can also specify the `n` argument.

## EXERCISE

Find the 2 cars with the best mpg values in mtcars (high values are better).

Then, looking only at cars with 4 cylinders, find the 2 with the best mpg values.

```{r}
mtcars %>% 
  filter(cyl == 4) %>% 
  slice_max(mpg, n=2)

slice_max(mtcars, mpg, n=2)
```

# Mutate: Change or Create Columns

`mutate()` is used to both change the values of an existing column and make a new column.

We name the column we're mutating and set the value. If the name already exists, it will update the column. If the name doesn't exist, it will create a new variable (column is appended at the end of existing columns).

```{r, eval=TRUE}
police %>% 
  mutate(vehicle_age = 2017 - vehicle_year) %>%
  select(starts_with("vehicle"))  # just to pick a few columns to look at
```

We can put multiple mutations in the same call to mutate, with the expressions separated by commas:

```{r,  eval=TRUE}
mutate(police, 
       vehicle_age = 2017 - vehicle_year,
       old_car = vehicle_year < 2000)
```

Within a call to mutate, we can refer to variables we made or changed earlier in the same call as well. Here, we create vehicle_age, and then use it to create vehicle_age_norm:

```{r, eval=TRUE}
police %>% 
  mutate(vehicle_age = 2017 - vehicle_year, 
         vehicle_age_norm = ifelse(vehicle_age < 0,  # ifelse test condition
                                   0,  # value if true
                                   vehicle_age)  # value if false
         ) %>%  
  # below is just making it easier for us to see what we changed
  select(starts_with("vehicle")) %>%
  filter(vehicle_age < 0)
```

Side note: there is a tidyverse version of `ifelse()` called `if_else()` -- with the underscore or low dash symbol -- that works generally the same except it is stricter about checking data types.

`mutate()` can also change an existing column. It will overwrite it.

```{r, eval=TRUE}
police %>%
  mutate(department_id = "EPD")
```

We didn't save the output, so the column hasn't changed permanently - only in the output.

```{r}
police
```

Remember that when using `mutate()`, you're operating on the entire column at once, so you can't select just a subset of the vector as you would with `[]`. This means more frequently using functions like `ifelse()` or helper functions such as `na_if()`, `replace_na()`, or `recode()`.

`na_if` replaces an existing value with `NA`. `replace_na` does roughly the opposite: replaces `NA` with a new value.

```{r}
police %>% mutate(vehicle_make = na_if(vehicle_make, "UNK"))
```

`na_if()` can only can check and replace one value at a time; it also can't be used with any expressions (`x <= 1`) -- only single values.

### EXERCISE

If the column beat in `police` is "/" or "CHICAGO", set it to `NA` instead using `mutate()`.

Hint: it's ok if you take two steps to do this.

```{r}
police %>% 
  mutate(beat = na_if(beat, "/")) %>% 
  mutate(beat = na_if(beat, "CHICAGO"))

police %>% 
  mutate(beat = na_if(beat, "/"),
         beat = na_if(beat, "CHICAGO"))

```

# Recap

You now can use `select` and `filter` to subset your data in a wide variety of ways, and `mutate` to update variables or create new ones.

Next session: the three other common dplyr "verb" functions for working with data frames: `group_by`, `summarize`, and `arrange`.