R语言按ID聚合行：多列自定义规则数据转换需求

需求说明 #

按相同ID聚合行，每个ID仅保留一行，各列按以下规则汇总：

• Year列：保留同一ID行中的最大值；
• Event列：若仅存在Economical或Natural则保留对应值，若两者都存在则显示"Both_present"；
• Wood列：保留NA/0/1，若同时存在0和1则取1；
• Nature列：若仅存在Biotic或Abiotic则保留对应值，若两者都存在则显示"Both_present"。

示例数据 #

ID <- c("1", "1", "2", "2", "2", "3", "3", "3",
        "4", "5", "5", "6", "6", "6")

Year <- c("2001", "2001", "2008", "2009", "2008", "2005", "2005", "2005",
          "2000", "2010", "2010", "2008", "2007", "2006")

Event <- c("Economical", "Economical", "Natural", "Economical", "Natural", "Natural", "Natural", "Natural",
           "Economical", "Economical", "Economical", "Natural", "Natural", "Natural")

Wood <- c("NA", "NA", "0", "1", "1", "1", "0", "0",
          "1", "1", "1", "1", "1", "1")

Nature <- c("Biotic", "Abiotic", "Biotic", "Biotic", "Abiotic", "Biotic", "Biotic", "Biotic",
           "Abiotic", "Abiotic", "Abiotic", "Biotic", "Biotic", "Biotic")

history <- data.frame(ID, Year, Event, Wood, Nature)

原数据预览：

ID Year      Event Wood  Nature
1   1 2001 Economical   NA  Biotic
2   1 2001 Economical   NA Abiotic
3   2 2008    Natural    0  Biotic
4   2 2009 Economical    1  Biotic
5   2 2008    Natural    1 Abiotic
6   3 2005    Natural    1  Biotic
7   3 2005    Natural    0  Biotic
8   3 2005    Natural    0  Biotic
9   4 2000 Economical    1 Abiotic
10  5 2010 Economical    1 Abiotic
11  5 2010 Economical    1 Abiotic
12  6 2008    Natural    1  Biotic
13  6 2007    Natural    1  Biotic
14  6 2006    Natural    1  Biotic

期望输出 #

ID Year       Event Wood      Nature
1   1 2001 Economical   NA Both_present
2   2 2009 Both_present    1 Both_present
3   3 2005      Natural    1       Biotic
4   4 2000 Economical    1      Abiotic
5   5 2010 Economical    1      Abiotic
6   6 2008      Natural    1       Biotic

实现代码 #

使用dplyr包完成聚合操作，逻辑清晰易读，适合入门用户：

# 若未安装dplyr包，先执行安装
# install.packages("dplyr")
library(dplyr)

# 预处理Wood列：将字符型"NA"转为真实缺失值，并转换为数值型
history_cleaned <- history %>%
  mutate(Wood = ifelse(Wood == "NA", NA, as.numeric(Wood)))

# 按ID分组并按规则聚合
result <- history_cleaned %>%
  group_by(ID) %>%
  summarise(
    Year = max(Year),
    Event = case_when(
      all(Event == "Economical") ~ "Economical",
      all(Event == "Natural") ~ "Natural",
      TRUE ~ "Both_present"
    ),
    Wood = case_when(
      any(Wood == 1, na.rm = TRUE) ~ 1,
      all(is.na(Wood)) ~ NA_real_,
      TRUE ~ 0
    ),
    Nature = case_when(
      all(Nature == "Biotic") ~ "Biotic",
      all(Nature == "Abiotic") ~ "Abiotic",
      TRUE ~ "Both_present"
    )
  ) %>%
  ungroup()

# 输出结果
print(result)

代码说明 #

1. 预处理Wood列：原数据中Wood列的"NA"是字符串，不是R的标准缺失值类型，先转换为真实NA并转为数值型，确保后续判断逻辑准确。
2. 分组聚合：
   • Year：直接用max()取最大值，年份字符串的大小比较逻辑和数值一致，无需额外转换。
   • Event/Nature：用case_when判断分组内是否只有单一类型，否则返回Both_present。
   • Wood：优先判断是否存在1，存在则取1；全为NA则返回NA；否则取0。

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