3 Murder Mystery!

🕵️ 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.
  1. str_detect(): Use this inside filter() for finding partial matches on character strings. For example: people %>% filter(str_detect(name, "Amy")) finds everyone named Amy.
  2. 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.
  1. 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)

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

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

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

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

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

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

Classwork and Autograder

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.

Here’s the autograder. It will provide hints and give you a completion pdf to download at the end. Submit one copy per group on Canvas.

Homework on Ggplot2

Before next class, please complete Koans 8-10 on ggplot2 with this autograder. The test files for koans 9 and 10 were broken from a 2025 ggplot update when you originally downloaded them: download the koans again from github to fix this issue.

Here are some notes on using ggplot2: