# 1.3 Murder Mystery {#sec-murder}

In this assignment, you'll practice a few more `dplyr` functions and then use them to solve a murder mystery.

## Joins

Oftentimes in data analytics, we have multiple data sets from multiple sources and we need to join them together in certain ways to get the insights we're after. This section introduces the concept of joins by studying `bind_rows()`, `bind_cols()`, and `left_join()`, but there are others included in dplyr: `inner_join()`, `right_join()`, `full_join()`, and more. Studying `left_join()` gives you all the insight you need to be able to use any of these joins successfully if you're interested in doing so later on.

### `bind_rows` and `bind_cols`

Sometimes you have two data sets and you just need to stack them to combine them into a single data set. In that case, you can use `bind_rows` or `bind_cols`. `bind_rows` combines two data sets by stacking one on top of the other (think of each data set as being a row, and you bind the two rows). `bind_cols` combines two data sets by putting them side by side (think of each data set as being a column, and you bind the two columns).

![](https://github.com/cobriant/tidyverse_illustrated/raw/main/Learning%20the%20Tidyverse-29.jpg)

![](https://github.com/cobriant/tidyverse_illustrated/raw/main/Learning%20the%20Tidyverse-30.jpg)

### `left_join`

`left_join` does something a little different: it takes two tibbles and makes a wider tibble by cross referencing the data and filling out the variables from the second data set with the variables from the first data set according to a key.

![](https://github.com/cobriant/tidyverse_illustrated/raw/main/Learning%20the%20Tidyverse-31.jpg)
![](https://github.com/cobriant/tidyverse_illustrated/raw/main/Screenshot%202025-01-07%20at%2012.15.24%20PM.png)

Go ahead and complete Koan 7 on `left_join` before going any further.

## Murder Mystery

In this part of the assignment, you'll practice using `dplyr` functions (especially `left_join()` and `str_detect()`) to solve a murder mystery. At the end of this document, you'll find a google survey that you can use to check your final answer. Make sure you don't skip it!

Double check that you have an internet connection, then run this to get started:

```{r, message = F}
library(tidyverse)
people <- read_csv("https://raw.githubusercontent.com/cobriant/dplyrmurdermystery/master/data/person.csv")
drivers_license <- read_csv("https://raw.githubusercontent.com/cobriant/dplyrmurdermystery/master/data/drivers_license.csv")
income <- read_csv("https://raw.githubusercontent.com/cobriant/dplyrmurdermystery/master/data/income.csv")
crime_scene_report <- read_csv("https://raw.githubusercontent.com/cobriant/dplyrmurdermystery/master/data/crime_scene_report.csv")
facebook_event_checkin <- read_csv("https://raw.githubusercontent.com/cobriant/dplyrmurdermystery/master/data/facebook_event_checkin.csv")
get_fit_now_checkin <- read_csv("https://raw.githubusercontent.com/cobriant/dplyrmurdermystery/master/data/get_fit_now_checkin.csv")
get_fit_now_member <- read_csv("https://raw.githubusercontent.com/cobriant/dplyrmurdermystery/master/data/get_fit_now_member.csv")
interview <- read_csv("https://raw.githubusercontent.com/cobriant/dplyrmurdermystery/master/data/interview.csv")
print_all <- function(df, row, col) cat(str_wrap(df[row, col], width = 80))
```

A crime has taken place and the detective needs your help! The detective has given you special access to police data to help them crack the case. All 8 datasets should be in your global environment now. Take a look at each of them.

This diagram will be useful: it shows you how variables in the tables can be linked to connect information from different tables. It's called a database schema: 
![](https://mystery.knightlab.com/schema.png)

The schema is all accurate EXCEPT I had to change the table `person` to be called `people`, because `person` is a reserved name in R.

First, we'll get more comfortable with the data: How many people are in this database?

```{r}
people %>%
  nrow()
```

1. How many crime reports are there?

```{r}
# __ %>%
#  nrow()
```

Did any crimes happen on Christmas of 2017?

```{r}
crime_scene_report %>%
  filter(date == "2017-12-25")
```

2. How long do the crime scene reports go back? (Hint: use `arrange()`)

```{r}
# __
```

3. The detective asks you to start by reading the crime scene report. They tell you it was a *murder* that was filed on *January 15th, 2018* in a place called *"dplyr City"*.

```{r}
# __
```

Note: are you annoyed by not being able to easiy read these long character strings because it truncates them? I made a function for you called `print_all()` to help. It takes a tibble as the first argument, the (integer) row you want to print as the second argument, and the (integer) column you want to print as the third argument. For example, this will 
print the long string in row 10, column 3 of `crime_scene_report`:

```{r}
crime_scene_report %>%
  print_all(10, 3)
```

4. After reading the correct crime scene report in question 3, you should have an idea about how to find the witnesses. Write a query that identifies the first witness.

```{r}
# people %>%
#   __
```

5. Write a query that identifies the second witness.

```{r}
# people %>%
#   __
```

6. Write two queries that show the interview transcripts for our two subjects.

```{r}
# interview %>%
#   __

# interview %>%
#   __
```

7. Go get 'em! Now you know enough to solve the mystery! Show the queries you wrote to solve it. Make sure to check your final answer by filling out this survey: (DON'T SKIP THE SURVEY EVEN IF YOU'RE SURE ABOUT YOUR ANSWER!!):
https://forms.gle/K7rZ5VykcA7Bb1Qf7

```{r}
# __
```

