library(tidyverse)
ct_school_attendance <- read.csv("https://data.ct.gov/resource/t4hx-jd4c.csv?$limit=3000") %>%
filter(studentgroup == "All Students" & reportingdistrictname != "Connecticut")
ct_school_learning_model <-
read.csv("https://data.ct.gov/resource/5q7h-u2ac.csv?$where=update_date=%272020-09-17%27") |>
rename(fully_remote = percent_students_fully_remote) Joining Datasets
SDS 192: Introduction to Data Science
Lindsay Poirier
Statistical & Data Sciences, Smith College
Fall 2022
For Today
- Reminder: Quiz 1 and Group Projects!
- MSA Recap
- Joining Datasets
- Mosaic Effect
Load the following two data frames into RStudio.
What do you notice about them? Why might they be useful to consider together?
Joining Datasets
- Up until this point, we have been working with data in single tables.
- Sometimes related data can be spread across multiple tables that we wish to bring together for different kinds of analysis.
Keys
- A column shared across the tables that we can join on.
- What might be an issue with joining on this key?
| ABCDEFGHIJ0123456789 |
reportingdistrictcode <chr> | |
|---|---|
| 1 | 0010011 |
| 2 | 0020011 |
| 3 | 0030011 |
| 4 | 0040011 |
| 5 | 0050011 |
| 6 | 0070011 |
6 rows
| ABCDEFGHIJ0123456789 |
district_code <int> | |
|---|---|
| 1 | 2190012 |
| 2 | 80011 |
| 3 | 10011 |
| 4 | 20011 |
| 5 | 30011 |
| 6 | 40011 |
6 rows
Keys
| ABCDEFGHIJ0123456789 |
reportingdistrictcode <int> | |
|---|---|
| 1 | 10011 |
| 2 | 20011 |
| 3 | 30011 |
| 4 | 40011 |
| 5 | 50011 |
| 6 | 70011 |
6 rows
| ABCDEFGHIJ0123456789 |
district_code <int> | |
|---|---|
| 1 | 2190012 |
| 2 | 80011 |
| 3 | 10011 |
| 4 | 20011 |
| 5 | 30011 |
| 6 | 40011 |
6 rows
Join
ct_school_attendance |>
inner_join(ct_school_learning_model,
by = c("reportingdistrictcode" = "district_code")) |>
select(reportingdistrictcode, attrate_202021, fully_remote) |>
head()| ABCDEFGHIJ0123456789 |
reportingdistrictcode <int> | attrate_202021 <dbl> | fully_remote <chr> | |
|---|---|---|---|
| 1 | 10011 | 0.9680 | 13% |
| 2 | 20011 | 0.8923 | 32% |
| 3 | 30011 | 0.9550 | 30% |
| 4 | 40011 | 0.9621 | 19% |
| 5 | 50011 | 0.9698 | 12% |
| 6 | 70011 | 0.9542 | 13% |
6 rows
Different Kinds of Joins
inner_join()left_join()right_join()full_join()anti_join()
Inner Join
Outer Joins
One-to-many relationships
Many-to-many relationships
Anti-joins
Joining Datasets SDS 192: Introduction to Data Science Lindsay Poirier Statistical & Data Sciences , Smith College Fall 2022