Data Merging

This lesson is called Data Merging, part of the Going Deeper with R course. This lesson is called Data Merging, part of the Going Deeper with R course.

Transcript

Click on the transcript to go to that point in the video. Please note that transcripts are auto generated and may contain minor inaccuracies.

View code shown in video

# Load Packages -----------------------------------------------------------

library(tidyverse)
library(fs)
library(readxl)
library(janitor)

# Create Directories ------------------------------------------------------

dir_create("data-raw")

# Download Data -----------------------------------------------------------

# https://www.oregon.gov/ode/educator-resources/assessment/Pages/Assessment-Group-Reports.aspx

# download.file("https://www.oregon.gov/ode/educator-resources/assessment/Documents/TestResults2122/pagr_schools_math_tot_raceethnicity_2122.xlsx",
#               mode = "wb",
#               destfile = "data-raw/pagr_schools_math_tot_raceethnicity_2122.xlsx")
#
# download.file("https://www.oregon.gov/ode/educator-resources/assessment/Documents/TestResults2122/TestResults2019/pagr_schools_math_tot_raceethnicity_1819.xlsx",
#               mode = "wb",
#               destfile = "data-raw/pagr_schools_math_tot_raceethnicity_1819.xlsx")
#
# download.file("https://www.oregon.gov/ode/educator-resources/assessment/TestResults2018/pagr_schools_math_raceethnicity_1718.xlsx",
#               mode = "wb",
#               destfile = "data-raw/pagr_schools_math_raceethnicity_1718.xlsx")
#
# download.file("https://www.oregon.gov/ode/educator-resources/assessment/TestResults2017/pagr_schools_math_raceethnicity_1617.xlsx",
#               mode = "wb",
#               destfile = "data-raw/pagr_schools_math_raceethnicity_1617.xlsx")
#
# download.file("https://www.oregon.gov/ode/educator-resources/assessment/TestResults2016/pagr_schools_math_raceethnicity_1516.xlsx",
#               mode = "wb",
#               destfile = "data-raw/pagr_schools_math_raceethnicity_1516.xlsx")
#
# download.file(
#   "https://github.com/rfortherestofus/going-deeper-v2/raw/main/data-raw/oregon-districts-and-schools.xlsx",
#   mode = "wb",
#   destfile = "data-raw/oregon-districts-and-schools.xlsx"
# )

# Import, Tidy, and Clean Data -------------------------------------------

clean_math_proficiency_data <- function(raw_data) {
  read_excel(
    path = raw_data
  ) |>
    clean_names() |>
    filter(student_group == "Total Population (All Students)") |>
    filter(grade_level == "Grade 3") |>
    select(academic_year, school_id, contains("number_level")) |>
    pivot_longer(
      cols = starts_with("number_level"),
      names_to = "proficiency_level",
      values_to = "number_of_students"
    ) |>
    mutate(proficiency_level = parse_number(proficiency_level)) |>
    mutate(number_of_students = parse_number(number_of_students)) |>
    mutate(
      pct = number_of_students / sum(number_of_students, na.rm = TRUE),
      .by = school_id
    )
}


third_grade_math_proficiency_2021_2022 <-
  clean_math_proficiency_data(
    raw_data = "data-raw/pagr_schools_math_tot_raceethnicity_2122.xlsx"
  )

third_grade_math_proficiency_2018_2019 <-
  clean_math_proficiency_data(
    raw_data = "data-raw/pagr_schools_math_tot_raceethnicity_1819.xlsx"
  )

third_grade_math_proficiency <-
  bind_rows(
    third_grade_math_proficiency_2021_2022,
    third_grade_math_proficiency_2018_2019
  )

third_grade_math_proficiency

oregon_districts_and_schools <-
  read_excel("data-raw/oregon-districts-and-schools.xlsx") |>
  clean_names() |>
  rename(school_id = attending_school_institutional_id) |>
  glimpse()

left_join(
  third_grade_math_proficiency,
  oregon_districts_and_schools
) |>
  glimpse()

Your Turn

Solution

# Load Packages ----------------------------------------------------------

library(tidyverse)
library(fs)
library(readxl)
library(janitor)

# Create Directory -------------------------------------------------------

dir_create("data-raw")

# Download Data ----------------------------------------------------------

# https://www.oregon.gov/ode/reports-and-data/students/Pages/Student-Enrollment-Reports.aspx

# download.file(
#   url = "https://www.oregon.gov/ode/reports-and-data/students/Documents/fallmembershipreport_20222023.xlsx",
#   mode = "wb",
#   destfile = "data-raw/fallmembershipreport_20222023.xlsx"
# )

# download.file(
#   "https://www.oregon.gov/ode/reports-and-data/students/Documents/fallmembershipreport_20212022.xlsx",
#   mode = "wb",
#   destfile = "data-raw/fallmembershipreport_20212022.xlsx"
# )

# download.file(
#   "https://www.oregon.gov/ode/reports-and-data/students/Documents/fallmembershipreport_20202021.xlsx",
#   mode = "wb",
#   destfile = "data-raw/fallmembershipreport_20202021.xlsx"
# )

# download.file(
#   "https://www.oregon.gov/ode/reports-and-data/students/Documents/fallmembershipreport_20192020.xlsx",
#   mode = "wb",
#   destfile = "data-raw/fallmembershipreport_20192020.xlsx"
# )

# download.file(
#   "https://www.oregon.gov/ode/reports-and-data/students/Documents/fallmembershipreport_20182019.xlsx",
#   mode = "wb",
#   destfile = "data-raw/fallmembershipreport_20182019.xlsx"
# )
#
# download.file(
#   url = "https://github.com/rfortherestofus/going-deeper-v2/raw/main/data-raw/oregon-districts.xlsx",
#   mode = "wb",
#   destfile = "data-raw/oregon-districts.xlsx"
# )

# Tidy and Clean Function ------------------------------------------------

clean_enrollment_data <- function(excel_file, sheet_name) {
  read_excel(
    path = excel_file,
    sheet = sheet_name
  ) |>
    clean_names() |>
    select(
      1,
      3,
      7:19
    ) |>
    select(-contains("percent")) |>
    set_names(
      "district_institution_id",
      "school_institution_id",
      "american_indian_alaska_native",
      "asian",
      "native_hawaiian_pacific_islander",
      "black_african_american",
      "hispanic_latino",
      "white",
      "multi_racial"
    ) |>
    pivot_longer(
      cols = -c(district_institution_id, school_institution_id),
      names_to = "race_ethnicity",
      values_to = "number_of_students"
    ) |>
    mutate(
      race_ethnicity = recode_values(
        race_ethnicity,
        "american_indian_alaska_native" ~ "American Indian Alaska Native",
        "asian" ~ "Asian",
        "black_african_american" ~ "Black/African American",
        "hispanic_latino" ~ "Hispanic/Latino",
        "multiracial" ~ "Multi-Racial",
        "native_hawaiian_pacific_islander" ~ "Native Hawaiian Pacific Islander",
        "white" ~ "White",
        "multi_racial" ~ "Multiracial"
      )
    ) |>
    mutate(number_of_students = parse_number(number_of_students)) |>
    group_by(district_institution_id, race_ethnicity) |>
    summarize(number_of_students = sum(number_of_students, na.rm = TRUE)) |>
    ungroup() |>
    group_by(district_institution_id) |>
    mutate(pct = number_of_students / sum(number_of_students)) |>
    ungroup() |>
    mutate(year = sheet_name) |>
    mutate(year = str_remove(year, "School "))
}

# Tidy and Clean Data ----------------------------------------------------

enrollment_by_race_ethnicity_2022_2023 <-
  clean_enrollment_data(
    excel_file = "data-raw/fallmembershipreport_20222023.xlsx",
    sheet_name = "School 2022-23"
  )

enrollment_by_race_ethnicity_2021_2022 <-
  clean_enrollment_data(
    excel_file = "data-raw/fallmembershipreport_20212022.xlsx",
    sheet_name = "School 2021-22"
  )

enrollment_by_race_ethnicity <-
  bind_rows(
    enrollment_by_race_ethnicity_2021_2022,
    enrollment_by_race_ethnicity_2022_2023
  )


# Data Merging -----------------------------------------------------------

oregon_districts <-
  read_excel("data-raw/oregon-districts.xlsx") |>
  clean_names() |>
  rename(district_institution_id = attending_district_institutional_id)

left_join(
  enrollment_by_race_ethnicity,
  oregon_districts,
  join_by(district_institution_id)
) |>
  view()

Download the oregon-districts.xlsx file into the data-raw folder. You can download it from this URL: https://github.com/rfortherestofus/going-deeper-v2/raw/main/data-raw/oregon-districts.xlsx
Import a new data frame called oregon_districts from oregon-districts.xlsx.
Merge the oregon_districts data frame into the enrollment_by_race_ethnicity data frame so you can see the names of the districts.

Learn More

R for Data Science (2e) - 20 Joins

Garrick Aden-Buie - 🤹 tidyexplain

Have any questions? Put them below and we will help you out!

You need to be signed-in to comment on this post. Login.

Eda Akpek • April 28, 2026

Which enrollment_by_race_ethnicity dataframe are we using? I have the ones from 2021-2022 and 2022-2023

Gracielle Higino Coach • May 1, 2026

Hi Eda! I don't think it matters in this case, but in the last exercise both 2021-2022 and 2022-2023 were combined together. So maybe create a enrollment_by_race_ethnicity object with these combined datasets, and you should be able to complete the exercise!