Class 6: Murder Mystery

In this classwork, we’ll take a break from discussing probability and statistics to review the dplyr functions you’ve learned, along with introducing joins.

Source: knight lab

🕵️ A Crime Has Been Committed – Can You Help Solve It?

A crime has taken place, and the detective needs your help! You’ve been given special access to police data to crack the case. Let’s dive in and see if you can uncover the truth!

To help you navigate the data, here’s a database schema – a diagram that shows how the tables are connected. It’s like a map to guide you through the investigation:

👉 Tip: Click this link to open the schema in a new tab for easy reference as you work through the mystery.

Function toolbox:

1. pipe |>: takes the data from the left and passes it into the function on the right. Think of it as saying “and then”.
2. filter(): Use this to find rows that match specific conditions. For example:

• income |> filter(annual_income > 100000) finds people earning over $100K.
• people |> filter(name == "Christoper Peteuil") finds the person with that exact name.

3. str_detect(): Use this inside filter() for finding partial matches on character strings. For example: people |> filter(str_detect(name, "Amy")) finds everyone with the first name Amy.
4. arrange(): Sorts data from lowest to highest. For example:

• income |> arrange(annual_income) sorts people by income (lowest first).
• Add desc() to reverse the order: income |> arrange(desc(annual_income)) sorts from highest to lowest.

5. Chaining Steps: Combine multiple steps using pipes. For example: crime_scene_report |> filter(city == "Duluth") |> arrange(desc(date)) This means: “Find crimes in Duluth, and then sort them by date (most recent first).”

# Review Examples

# filter: find observations that fit any logical condition
income |> 
  filter(annual_income > 100000)

# A tibble: 91 × 2
         ssn annual_income
       <dbl>         <dbl>
 1 102503205        324800
 2 118015315        486600
 3 121635236        489800
 4 124096135        175300
 5 130276880        407100
 6 136934763        162300
 7 138161179        393000
 8 146822498        325200
 9 149045552        431400
10 184294205        393000
# ℹ 81 more rows

people |> 
  filter(name == "Christoper Peteuil")

# A tibble: 1 × 6
     id name               license_id address_number address_street_name     ssn
  <dbl> <chr>                   <dbl>          <dbl> <chr>                 <dbl>
1 10000 Christoper Peteuil     993845            624 Bankhall Ave         7.48e8

# filter + str_detect: for partial matches
people |> 
  filter(str_detect(name, "Amy"))

# A tibble: 2 × 6
     id name         license_id address_number address_street_name         ssn
  <dbl> <chr>             <dbl>          <dbl> <chr>                     <dbl>
1 38995 Amy Coltrain     537719           3664 Bad Munstereifel Blvd 132545140
2 57354 Amy Stthomas     238318            208 Bush Lake Dr          398252773

# arrange: sorts the data set from lowest to highest by the variable of your choice
income |> 
  arrange(annual_income)

# A tibble: 7,514 × 2
         ssn annual_income
       <dbl>         <dbl>
 1 112822004         10000
 2 258840243         10000
 3 285824022         10000
 4 311660049         10000
 5 339886216         10000
 6 474993315         10000
 7 564147321         10000
 8 567277062         10000
 9 575722419         10000
10 922854068         10000
# ℹ 7,504 more rows

# arrange + desc: sort highest to lowest
income |> 
  arrange(desc(annual_income))

# A tibble: 7,514 × 2
         ssn annual_income
       <dbl>         <dbl>
 1 361660921        498500
 2 121635236        489800
 3 118015315        486600
 4 541217354        476300
 5 313890530        475700
 6 311494850        473100
 7 800582909        449400
 8 990250718        448000
 9 208676025        446200
10 625660122        446000
# ℹ 7,504 more rows

# chain together multiple steps
crime_scene_report |> 
  filter(city == "Duluth") |> 
  arrange(desc(date))

# A tibble: 8 × 4
  date       type      description                                         city 
  <date>     <chr>     <chr>                                               <chr>
1 2018-03-29 murder    "Waiting in a hot tureen!\n"                        Dulu…
2 2018-02-06 assault   "best to climb up one of the legs of the table, bu… Dulu…
3 2017-09-08 smuggling "‘Oh, don’t bother ME,’ said the Duchess; ‘I never… Dulu…
4 2017-07-23 blackmail "\n"                                                Dulu…
5 2017-07-06 smuggling "a mouse--to a mouse--a mouse--O mouse!’) The Mous… Dulu…
6 2017-06-04 bribery   "‘Thank you, it’s a very interesting dance to watc… Dulu…
7 2017-05-06 arson     "\n"                                                Dulu…
8 2017-02-07 arson     "“There’s a porpoise close behind us, and he’s tre… Dulu…

👉 Tip: Click this link to open the examples in a new tab for easy reference.

Join Notes

Download to Solve on Your Own

Here’s the classwork (download R script). There are no test files for this assignment, so hitting Cmd/Ctrl+Shift+T will do nothing here.

Check Your Understanding

Filtering Rows

Sorting and Chaining

Joins