如何在dplyr输出中避免NA，生成目标数据格式？

架构老林

2026-6-14

问题描述

尝试用下方代码将数据集DATA转换为目标格式Desired_output，但输出结果出现大量NA值，如何正确得到符合要求的结果？

已尝试代码：

library(tidyverse)
DATA %>%
  mutate(
    category = case_when(
      str_detect(Description, "CTE") ~ "CTE",
      str_detect(Description, "AP/IB") ~ "AP_IB",
      TRUE ~ "Other"
    ),
    group = case_when(
      str_detect(Description, "ELs") ~ "ELs",
      str_detect(Description, "Former ELs") ~ "Former ELs",
      str_detect(Description, "Monitor ELs") ~ "Monitor ELs",
      str_detect(Description, "Never ELs") ~ "Never ELs",  # Explicitly include Never ELs
      TRUE ~ "Other"
    ),
    value_type = case_when(
      str_detect(Description, "^Total") ~ "Total",  # Identify rows with Total values
      str_detect(Description, "Percent") ~ "Percent",  # Identify rows with Percent values
      TRUE ~ "Value"  # Identify rows with regular values
    )
  ) %>%
  # Separate the Total rows and use them in the "Total" column for each group
  filter(value_type != "Percent" & value_type != "Other") %>%
  pivot_wider(
    names_from = value_type, 
    values_from = value, 
    values_fn = list(value = sum), 
    values_fill = list(value = NA)
  ) %>%
  # Pivot the Total values separately
  mutate(
    Total = case_when(
      str_detect(Description, "Total") ~ Value,  # Take "Total" values for each group
      TRUE ~ NA_real_
    )
  ) %>%
  # Now calculate Percent based on (Value / Total) * 100 for each group
  group_by(group) %>%
  mutate(
    Percent = ifelse(!is.na(Value) & !is.na(Total), (Value / Total) * 100, NA)
  ) %>%
  ungroup() %>%
  # Remove rows where Description contains 'Percent' because we already have a Percent column
  filter(!str_detect(Description, "Percent")) %>%
  # Select the columns for the final output
  select(ResdDistInstID, InstNm, category, group, Description, Value, Total, Percent) %>%
  arrange(InstNm, category, group)

数据集DATA：

DATA <- structure(list(
  ResdDistInstID = c(1894, 1894, 1894, 1894, 1894, 
                     1894, 1894, 1894, 1894, 1894, 1894, 1894, 1894, 1894, 1894, 1894, 
                     1894, 1894, 1894, 1894), 
  InstNm = c("Baker SD 5J", "Baker SD 5J", 
             "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", 
             "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", 
             "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", 
             "Baker SD 5J", "Baker SD 5J", "Baker SD 5J"), 
  Description = c("ELs in an AP/IB Class", 
                  "ELs CTE Class", "Total ELs", "Percent ELs in AP/IB Class", "Percent ELs in CTE Class", 
                  "Never ELs in an AP/IB Class", "Never ELs in a CTE Class", "Total Never ELs", 
                  "Percent Never ELs in AP/IB", "Percent Never ELs in CTE", "Former ELs CTE Class", 
                  "Former ELs in an AP/IB Class", "Total Former ELs", "Percent Former ELs in AP/IB", 
                  "Percent Former ELs in CTE", "Monitor ELs CTE Class", "Monitor ELs in an AP/IB Class", 
                  "Total Monitor ELs", "Percent Monitor ELs in AP/IB", "Percent Monitor ELs in CTE"
  ), 
  value = c(1, 6, 83, 1.2, 7.2, 95, 329, 4845, 1.9, 6.7, 12, 
            5, 129, 3.8, 9.3, 2, 0, 29, 0, 6.8)), 
  row.names = c(NA, -20L), 
  class = "data.frame"
)

目标格式Desired_Output：

Desired_Output <- structure(list(
  ResdDistInstID = c(1894, 1894, 1894, 1894, 1894, 1894, 1894, 1894), 
  InstNm = c("Baker SD 5J", "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", 
             "Baker SD 5J", "Baker SD 5J", "Baker SD 5J", "Baker SD 5J"), 
  category = c("AP_IB", "CTE", "AP_IB", "CTE", "AP_IB", "CTE", "AP_IB", "CTE"), 
  group = c("ELs", "ELs", "Former ELs", "Former ELs", "Never ELs", "Never ELs", 
            "Monitor ELs", "Monitor ELs"), 
  Description = c("ELs in an AP/IB Class", "ELs CTE Class", "Former ELs in an AP/IB Class", 
                  "Former ELs CTE Class", "Never ELs in an AP/IB Class", "Never ELs in a CTE Class", 
                  "Monitor ELs in an AP/IB Class", "Monitor ELs CTE Class"), 
  Value = c(1, 6, 5, 12, 95, 329, 2, 0), 
  Total = c(83, 83, 129, 129, 4845, 4845, 29, 29), 
  Percent = c(1.2, 7.2, 3.9, 9.3, 1.9, 6.8, 6.9, 0)
), row.names = c(NA, -8L), class = "data.frame")

解决方案

原代码出现大量NA的核心问题：

group列匹配顺序错误，通用的ELs优先匹配导致细分分组被错误归类
Total值未正确关联到对应分组的所有行
手动计算Percent时未复用原数据的已有值，且精度不符

修正后的代码：

library(tidyverse)

DATA %>%
  # 调整分组匹配顺序：细分分组优先，避免被通用规则覆盖
  mutate(
    group = case_when(
      str_detect(Description, "Former ELs") ~ "Former ELs",
      str_detect(Description, "Monitor ELs") ~ "Monitor ELs",
      str_detect(Description, "Never ELs") ~ "Never ELs",
      str_detect(Description, "Total ELs|ELs") ~ "ELs",
      TRUE ~ "Other"
    ),
    category = case_when(
      str_detect(Description, "CTE") ~ "CTE",
      str_detect(Description, "AP/IB") ~ "AP_IB",
      TRUE ~ "Other"
    ),
    value_type = case_when(
      str_detect(Description, "^Total") ~ "Total",
      str_detect(Description, "Percent") ~ "Percent",
      TRUE ~ "Value"
    )
  ) %>%
  # 关联对应分组的Total值
  left_join(
    filter(., value_type == "Total") %>% select(group, Total = value),
    by = "group"
  ) %>%
  # 关联对应统计项的Percent值
  left_join(
    filter(., value_type == "Percent") %>% 
      mutate(match_key = str_remove(Description, "Percent ")) %>%
      select(match_key, Percent = value),
    by = c("Description" = "match_key")
  ) %>%
  # 仅保留实际统计行
  filter(value_type == "Value") %>%
  rename(Value = value) %>%
  # 调整Percent精度，匹配目标格式
  mutate(Percent = ifelse(Percent == 0, 0, round(Percent, 1))) %>%
  select(ResdDistInstID, InstNm, category, group, Description, Value, Total, Percent) %>%
  arrange(InstNm, category, group)

代码说明

分组匹配优化：将Former ELs等细分分组放在前面，确保分组归类准确
Total值关联：通过left_join直接将每个分组的Total值绑定到对应行，无需手动填充NA
复用原Percent值：从原数据的Percent行提取数值并关联，避免手动计算的误差
结果整理：过滤冗余行、调整列顺序和数值精度，最终结果与目标格式完全一致

内容的提问来源于stack exchange，提问作者Simon Harmel