为分组数据框添加汇总行后填充组内唯一值的NA

需求说明 #

在数据框中添加分组汇总行时，需实现以下规则：

• 若组内某变量的所有行值一致，则填充该组汇总行对应变量的NA
• 若组内变量值存在差异，则保留NA
• 仅对analysis列值为5%和10%的数据进行汇总计算

示例代码与初始实现 #

library(tidyverse)
df.in <- 
  tibble::tribble(
    ~fullsite,      ~sample, ~replicate, ~Count, ~analysis, ~percent_area,
    "D-01-06M-EA", "D-01-06m-E",        "a",   480L,      "5%",         3.783,
    "D-01-06M-EA", "D-01-06m-E",        "a",   576L,     "10%",         9.124,
    "D-01-06M-EA", "D-01-06m-E",        "a",   136L,   "total",        24.822,
    "D-03-02M-EA", "D-03-02m_E",        "a",   543L,      "5%",         3.422,
    "D-03-02M-EA", "D-03-02m_E",        "a",   728L,     "10%",         8.053,
    "D-03-02M-EA", "D-03-02m_E",        "a",   380L,   "total",        27.851
  )

# 生成分组汇总数据（仅计算5%和10%的Count均值）
df.tots <- df.in %>% 
  filter(analysis != 'total') %>% 
  group_by(fullsite) %>% 
  summarise(Count = mean(Count))

# 合并原始数据与汇总数据，处理analysis列的NA
out <- add_row(.data = df.in, df.tots) %>% 
  arrange(fullsite) %>% 
  mutate(analysis = ifelse(is.na(analysis), "meanof_5+10", analysis))

遇到的问题 #

目前已完成analysis列的NA填充，但对于sample、replicate这类组内值一致但组间不同的列，汇总行的NA需要按组填充对应值；而percent_area列则需保留NA。

曾尝试用unique()在分组中处理，但未达到预期效果：

out2 <- add_row(.data = df.in, df.tots) %>% 
  arrange(fullsite) %>% 
  mutate(analysis = ifelse(is.na(analysis), "meanof_5+10", analysis),
         sample = ifelse(is.na(sample), unique(sample), unique(sample))) %>% 
  arrange(fullsite)

预期输出 #

out_expected <- out
out_expected$sample[is.na(out_expected$sample) & out_expected$fullsite == 'D-01-06M-EA'] <- 'D-01-06M-E'
out_expected$sample[is.na(out_expected$sample) & out_expected$fullsite == 'D-03-02M-EA'] <- 'D-03-02M-E'
out_expected$replicate[is.na(out_expected$replicate) & out_expected$fullsite == 'D-01-06M-EA'] <- 'a'
out_expected$replicate[is.na(out_expected$replicate) & out_expected$fullsite == 'D-03-02M-EA'] <- 'a'
out_expected

优化解决方案 #

方案1：手动指定列批量填充 #

利用dplyr的group_by+across批量处理需要填充的列，无需逐列编写判断逻辑：

out_optimized <- add_row(.data = df.in, df.tots) %>% 
  arrange(fullsite) %>% 
  mutate(analysis = ifelse(is.na(analysis), "meanof_5+10", analysis)) %>% 
  # 按fullsite分组
  group_by(fullsite) %>% 
  # 批量处理sample、replicate列的NA
  across(c(sample, replicate), ~ifelse(is.na(.), first(na.omit(.)), .)) %>% 
  ungroup()

out_optimized

方案2：自动识别组内值唯一的列 #

若存在大量需要处理的列，可先自动识别所有组内值唯一的列，再批量填充：

# 自动筛选组内值唯一的列（排除Count、analysis、percent_area）
cols_to_fill <- df.in %>% 
  group_by(fullsite) %>% 
  summarise(across(-c(Count, analysis, percent_area), ~n_distinct(.) == 1)) %>% 
  ungroup() %>% 
  summarise(across(everything(), all)) %>% 
  select(where(isTRUE)) %>% 
  names()

# 应用填充逻辑
out_optimized_auto <- add_row(.data = df.in, df.tots) %>% 
  arrange(fullsite) %>% 
  mutate(analysis = ifelse(is.na(analysis), "meanof_5+10", analysis)) %>% 
  group_by(fullsite) %>% 
  across(all_of(cols_to_fill), ~ifelse(is.na(.), first(na.omit(.)), .)) %>% 
  ungroup()

out_optimized_auto

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