如何整理格式混乱的schools_messy数据集（R语言）

原始混乱数据集 #

schools_messy <- tibble::tribble(
  ~data,
  "state:maryland",
  "location:bowie||name:bowie state university",
  "grade:freshman||count:100",
  "grade:sophomore||count:200",
  "grade:junior||count:300",
  "grade:senior||count:400",
  "location:baltimore||name:coppin state university",
  "grade:freshman||count:100",
  "grade:sophomore||count:200",
  "grade:junior||count:300",
  "grade:senior||count:400",
  
  "state:virginia",
  "location:williamsburg||name:college of william and mary",
  "grade:freshman||count:100",
  "grade:sophomore||count:200",
  "grade:junior||count:300",
  "grade:senior||count:400",
  "location:fairfax||name:george mason university",
  "grade:freshman||count:100",
  "grade:sophomore||count:200",
  "grade:junior||count:300",
  "grade:senior||count:400",
)

期望的整洁结构 #

schools_tidy <- tribble(
  ~state, ~location, ~name, ~grade, ~count,
  "maryland", "bowie", "bowie state university", "freshman", 100,
  "maryland", "bowie", "bowie state university", "sophomore", 200,
  "maryland", "bowie", "bowie state university", "junior", 300,
  "maryland", "bowie", "bowie state university", "senior", 400,
  "maryland", "baltimore", "coppin state university", "freshman", 100,
  "maryland", "baltimore", "coppin state university", "sophomore", 200,
  "maryland", "baltimore", "coppin state university", "junior", 300,
  "maryland", "baltimore", "coppin state university", "senior", 400,
  "virginia", "williamsburg", "college of william and mary", "freshman", 100,
  "virginia", "williamsburg", "college of william and mary", "sophomore", 200,
  "virginia", "williamsburg", "college of william and mary", "junior", 300,
  "virginia", "williamsburg", "college of william and mary", "senior", 400,
  "virginia", "fairfax", "george mason university", "freshman", 100,
  "virginia", "fairfax", "george mason university", "sophomore", 200,
  "virginia", "fairfax", "george mason university", "junior", 300,
  "virginia", "fairfax", "george mason university", "senior", 400,
)

解决方案代码 #

使用tidyverse工具链（dplyr、tidyr、stringr）完成数据清理，核心思路是填充分组上下文+拆分键值对：

library(tidyverse)

schools_tidy <- schools_messy %>%
  # 提取state并向下填充，标记每行所属州
  mutate(state = if_else(str_detect(data, "^state:"), str_remove(data, "^state:"), NA_character_)) %>%
  fill(state, .direction = "down") %>%
  # 提取location和name并向下填充，标记每行所属学校
  mutate(
    location = if_else(str_detect(data, "^location:"), str_extract(data, "(?<=location:)[^\\|]+"), NA_character_),
    name = if_else(str_detect(data, "^location:"), str_extract(data, "(?<=name:).+$"), NA_character_)
  ) %>%
  fill(location, name, .direction = "down") %>%
  # 过滤出仅包含grade和count的行
  filter(str_detect(data, "^grade:")) %>%
  # 拆分grade和count的键值对
  separate_wider_delim(data, delim = "||", names = c("grade_str", "count_str"), cols_remove = TRUE) %>%
  # 提取grade值并转换count为整数
  mutate(
    grade = str_remove(grade_str, "^grade:"),
    count = as.integer(str_remove(count_str, "^count:"))
  ) %>%
  # 清理冗余列并调整顺序
  select(-grade_str, -count_str) %>%
  select(state, location, name, grade, count)

代码说明 #

• fill()：将state、location、name的有效值向下填充，为后续每行补充分组上下文；
• str_detect()+str_remove()/str_extract()：从字符串中精准提取键对应的值；
• separate_wider_delim()：按||分隔符拆分grade和count的键值对；
• 最后过滤冗余行、转换数据类型并调整列顺序，得到目标结构。

内容的提问来源于stack exchange，提问作者David Robie