1

THE ‘R’

MURDER MYSTERY

2

intro/murder_mystery.R

3

A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on January 15, 2018 and that it took place in SQL City.

​

All the clues to this mystery are buried in a huge database, and you need to use your R skills to navigate through this vast network of information. Your first step to solving the mystery is to retrieve the corresponding crime scene report from the police department’s database.

4

data model

5

person

drivers_license

income

get_fit_now_member

interview

get_fit_now_check_in

facebook_event_checkin

6

person

crime_scene_report

drivers_license

income

get_fit_now_member

interview

get_fit_now_check_in

facebook_event_checkin

submit the name of the murderer and attach your .R-script:�

n.meseth@hs-osnabrueck.de

8

Congrats, you found the murderer! But wait, there's more... If you think you're up for a challenge, try querying the interview transcript of the murderer to find the real villain behind this crime.

debriefing

colnames()

the pipe operator |>

filter()

13

filter()

A

B

C

A

B

C

select()

15

select()

A

B

C

A

pull()

arrange()

18

arrange()

A

B

3

1

2

Z

X

Y

A

B

1

2

3

X

Y

Z

str_starts()

str_ends()

str_detect()

“Annabel Miller”

“Annabel Miller”

str_starts(txt, "Anna")

“Annabel Miller”

str_ends(txt, "Miller")

“Annabel Miller”

str_detect(txt, "Mill")

%in% with c()

distinct()

between()

left_join()

ID

A

1

2

3

X

Y

Z

ID

B

1

2

4

T

F

T

ID

A

1

2

3

X

Y

Z

B

T

F

NA

inner_join()

ID

A

1

2

3

X

Y

Z

ID

B

1

2

4

T

F

T

ID

A

1

2

X

Y

B

T

F

full_join()

ID

A

1

2

3

X

Y

Z

ID

B

1

2

4

T

F

T

ID

A

1

2

3

X

Y

Z

B

T

F

NA

NA

4

T