如何为二元分类变量Grazing按调查维度聚合取值？

我有一个大型数据集，每个调查包含12个样方，多数是数值变量，还有一个二元分类变量Grazing（取值为Present或Absent）。已经通过对12个样方的数值变量取平均生成了每个调查一行的汇总数据，但不知道如何对Grazing做对应聚合。

聚合规则：若某一调查的12个样方中至少有一个Grazing为Present，则该调查的Grazing取值为Present；否则为Absent。

示例数据 #

CL.SGN.Data.Sample <- structure(list(Species = c("Z. marina", "Z. marina", "Z. marina", 
"Z. marina", "Z. marina", "Z. marina", "Z. marina", "Z. marina", 
"Z. marina", "Z. marina", "Z. marina", "Z. marina", "Z. japonica", 
"Z. japonica", "Z. japonica", "Z. japonica", "Z. japonica", "Z. japonica", 
"Z. japonica", "Z. japonica", "Z. japonica", "Z. japonica", "Z. japonica", 
"Z. japonica", "Z. marina", "Z. marina", "Z. marina", "Z. marina", 
"Z. marina", "Z. marina", "Z. marina", "Z. marina", "Z. marina", 
"Z. marina", "Z. marina", "Z. marina", "Z. japonica", "Z. japonica", 
"Z. japonica", "Z. japonica"), Survey_Date = structure(c(1350518400, 
1350518400, 1350518400, 1350518400, 1350518400, 1350518400, 1350518400, 
1350518400, 1350518400, 1350518400, 1350518400, 1350518400, 1350518400, 
1350518400, 1350518400, 1350518400, 1350518400, 1350518400, 1350518400, 
1350518400, 1350518400, 1350518400, 1350518400, 1350518400, 1357689600, 
1357689600, 1357689600, 1357689600, 1357689600, 1357689600, 1357689600, 
1357689600, 1357689600, 1357689600, 1357689600, 1357689600, 1357689600, 
1357689600, 1357689600, 1357689600), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), Year = c("2012", "2012", "2012", "2012", "2012", 
"2012", "2012", "2012", "2012", "2012", "2012", "2012", "2012", 
"2012", "2012", "2012", "2012", "2012", "2012", "2012", "2012", 
"2012", "2012", "2012", "2013", "2013", "2013", "2013", "2013", 
"2013", "2013", "2013", "2013", "2013", "2013", "2013", "2013", 
"2013", "2013", "2013"), Quarter = c("4", "4", "4", "4", "4", 
"4", "4", "4", "4", "4", "4", "4", "4", "4", "4", "4", "4", "4", 
"4", "4", "4", "4", "4", "4", "1", "1", "1", "1", "1", "1", "1", 
"1", "1", "1", "1", "1", "1", "1", "1", "1"), Site_Name = c("Birch Bay", 
"Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", 
"Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", 
"Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", 
"Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", 
"Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", 
"Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", 
"Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay", 
"Birch Bay", "Birch Bay", "Birch Bay", "Birch Bay"), Quad = c("Q1", 
"Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", 
"Q12", "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", 
"Q10", "Q11", "Q12", "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", 
"Q8", "Q9", "Q10", "Q11", "Q12", "Q1", "Q2", "Q3", "Q4"), Cover = c(0.75, 
0.75, 0.45, 0.65, 0.1, 0.05, 0.1, 0.05, 0.05, 0.01, 0.02, 0.01, 
0, 0, 0.05, 0.05, 0, 0, 0, 0, 0, 0, 0, 0, 0.75, 0.65, 0.3, 0.75, 
0.15, 0.15, 0.3, 0.1, 0.1, 0.1, 0.15, 0.05, 0, 0, 0.05, 0), Calculated_Density = c(65, 
60, 26, 20, 3.75, 9, 9, 16, 8, 5, 5, 2, 0, 0, NA, NA, 0, 0, 0, 
0, 0, 0, 0, 0, 9, 7.75, 3.75, 9.25, 1.5, 1, 2.5, 0.75, 6, 0.25, 
1, 4, 0, 0, NA, 0), Average_Height = c(46.6666666666667, 39.9, 
42, 53.5, 41.6666666666667, 40, 48.8333333333333, 54.6666666666667, 
66, 30.5, 47.5, 50, 0, 0, NA, NA, 0, 0, 0, 0, 0, 0, 0, 0, 22, 
29.8333333333333, 23.2333333333333, 30, 34.1666666666667, 37.3333333333333, 
39.5, 33.1666666666667, 43.6666666666667, 33.6666666666667, 45.8333333333333, 
28.1666666666667, 0, 0, NA, 0), Grazing = c("Absent", "Absent", 
"Absent", "Absent", "Absent", "Absent", "Absent", "Absent", "Absent", 
"Absent", "Absent", "Absent", "Absent", "Absent", NA, NA, "Absent", 
"Absent", "Absent", "Absent", "Absent", "Absent", "Absent", "Absent", 
"Present", "Present", "Absent", "Present", "Present", "Absent", 
"Present", "Present", "Absent", "Present", "Absent", "Absent", 
"Absent", "Absent", NA, "Absent"), Year.Quarter = structure(c(2012.75, 
2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 
2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 
2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 2012.75, 
2012.75, 2012.75, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 
2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013), class = "yearqtr")), row.names = c(NA, 
-40L), class = c("tbl_df", "tbl", "data.frame"))

CL.SGN.Data.Sample

当前数值变量汇总代码 #

已实现对数值变量按调查（Site_Name+Survey_Date）取均值，代码如下：

SGN.ZM.Survey_Means <- CL.SGN.DATA %>% 
  filter(str_detect(Species, "Z. marina")) %>% 
  filter(!is.na(Calculated_Density),
         !is.na(Average_Height),
         !is.na(Cover)) %>%
  summarize(across(c(Cover, Average_Height, Calculated_Density), mean), .by = c(Site_Name, Survey_Date))
SGN.ZM.Survey_Means

失败的尝试 #

曾尝试将Grazing加入across函数，但因分类变量无法取均值，结果Grazing列全为NA：

SGN.ZM.Survey_Means <- CL.SGN.DATA %>% 
  filter(str_detect(Species, "Z. marina")) %>% 
  filter(!is.na(Calculated_Density),
         !is.na(Average_Height),
         !is.na(Cover)) %>%
  summarize(across(c(Cover, Average_Height, Calculated_Density, Grazing), mean), .by = c(Site_Name, Survey_Date))
SGN.ZM.Survey_Means

解决方案 #

不需要把Grazing放到across里，单独在summarize中处理即可。核心逻辑是判断每个分组中是否存在Grazing == "Present"，如果有则返回"Present"，否则返回"Absent"，同时要忽略NA值：

SGN.ZM.Survey_Means <- CL.SGN.DATA %>% 
  filter(str_detect(Species, "Z. marina")) %>% 
  filter(!is.na(Calculated_Density),
         !is.na(Average_Height),
         !is.na(Cover)) %>%
  summarize(
    # 数值变量取均值
    across(c(Cover, Average_Height, Calculated_Density), mean),
    # 处理Grazing变量：只要有一个Present就返回Present，否则Absent
    Grazing = ifelse(any(Grazing == "Present", na.rm = TRUE), "Present", "Absent"),
    .by = c(Site_Name, Survey_Date)
  )
SGN.ZM.Survey_Means

说明 #

• any(Grazing == "Present", na.rm = TRUE)：检查当前分组中是否存在至少一个Grazing为Present，na.rm = TRUE会忽略NA值，避免NA干扰判断。
• ifelse()根据判断结果返回对应的分类值，完美匹配需求中的聚合规则。

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