如何在分组内将Fettucine的mean与其他食物的lb/ub比较生成stat_sig列？

需求说明 #

现有tibble数据框，需在每个country分组内，为每个非Fettucine的food生成stat_sig列：

• 当同组Fettucine的mean大于该食物的ub时，标记为"positive"
• 当同组Fettucine的mean小于该食物的lb时，标记为"negative"
• 否则标记为"no difference"

用户已实现单个食物（Apple）与Fettucine的比较，需扩展至所有非Fettucine食物。

原始数据 #

d <- tibble::tribble(
        ~food,    ~country,      ~mean,        ~lb,        ~ub,
      "Apple",    "Belize", 39.9776055, 29.0792484,  55.206794,
  "Fettucine",    "Belize", 61.0975745, 51.5185944, 71.5280344,
     "Gateau",    "Belize", 49.5463782,  30.639024, 48.8724726,
  "Ice_cream",    "Belize", 29.8140732, 32.1812985,  57.351144,
    "Lentils",    "Belize", 48.9155095, 25.3083386, 43.3615929,
     "Sorbet",    "Belize", 41.3195049, 22.0809335,   46.27327,
     "Tomato",    "Belize",  36.578878, 28.5499326, 52.5901827,
  "Tangerine",    "Belize", 47.6379792,  20.171187, 42.8531992,
      "Apple", "Nicaragua", 46.5045615, 34.7335467,  51.910866,
  "Fettucine", "Nicaragua",  49.154144, 25.9897836, 50.9296656,
     "Gateau", "Nicaragua",  42.712395,  24.681436, 54.0169434,
  "Ice_cream", "Nicaragua", 50.5182907, 33.8316215,  46.545856,
    "Lentils", "Nicaragua", 31.6069446, 23.0752499, 43.3615929,
     "Sorbet", "Nicaragua", 48.3360246,  19.796699,   49.46453,
     "Tomato", "Nicaragua",  44.361618, 22.3769742, 47.8806141,
  "Tangerine", "Nicaragua", 31.0024944,  31.377402, 57.3926775
  )

用户初始代码（单食物比较） #

library(dplyr)

d %>% 
    group_by(country) %>% 
    mutate(stat_sig = case_when(
        mean[food=="Fettucine"] > ub[food=="Apple"] ~ "positive",
        mean[food=="Fettucine"] < lb[food=="Apple"] ~ "negative", 
        TRUE ~ "no difference")
    )

批量处理解决方案 #

核心思路是在分组后，先提取当前组内Fettucine的均值作为共享变量，再对所有非Fettucine食物批量应用判断规则：

library(dplyr)

d %>%
  group_by(country) %>%
  # 提取当前分组中Fettucine的均值，作为组内统一参考值
  mutate(fettucine_mean = mean[food == "Fettucine"]) %>%
  # 为非Fettucine食物生成stat_sig标记，Fettucine本身标记为NA
  mutate(stat_sig = case_when(
    food != "Fettucine" & fettucine_mean > ub ~ "positive",
    food != "Fettucine" & fettucine_mean < lb ~ "negative",
    food != "Fettucine" ~ "no difference",
    TRUE ~ NA_character_
  )) %>%
  # 可选：移除临时变量fettucine_mean
  select(-fettucine_mean) %>%
  ungroup() # 可选：取消分组，根据需求决定

代码说明 #

1. group_by(country)：按国家分组，确保每组内的比较独立
2. mutate(fettucine_mean = mean[food == "Fettucine"])：在每个分组内提取Fettucine的均值，生成组内共享的临时变量
3. case_when：针对每一行（非Fettucine），基于组内Fettucine的均值和当前食物的lb/ub进行判断，批量生成标记
4. 可选的select(-fettucine_mean)和ungroup()：根据后续数据处理需求，移除临时变量或取消分组

内容的提问来源于stack exchange，提问作者C.Robin