按Site和Year合并R数据框行并整合观测数据

现有R语言数据框df，记录了不同站点（Site）、年份（Year）下的个体观测信息，包含观测状态（Status：New Capture/Retrap）、站点个体总数最大值（Maxcount_site），以及blutinew等按类型和状态拆分的观测列。需完成以下处理：

• 将相同Site和Year的行合并为一行
• 移除Name、Status、maxcount列
• 将所有NA值替换为0
• 得到指定格式的结果

原始数据 #

df <- structure(list(Site = c("2B", "2B", "2B", "2B", "2B", "2C", "2C", 
"2C", "2C", "2C", "FS", "FS", "FS", "FS", "HE", "HE", "HE"), 
    Year = c(2014, 2014, 2014, 2014, 2014, 2014, 2014, 2014, 
    2014, 2014, 2014, 2014, 2014, 2014, 2014, 2014, 2014), Maxcount_site = c(46L, 
    46L, 46L, 46L, 46L, 25L, 25L, 25L, 25L, 25L, 19L, 19L, 19L, 
    19L, 10L, 10L, 10L), Status = c("New Capture", "New Capture", 
    "Retrap", "Retrap", "Retrap", "New Capture", "New Capture", 
    "Retrap", "Retrap", "Retrap", "New Capture", "New Capture", 
    "Retrap", "Retrap", "New Capture", "New Capture", "Retrap"
    ), Name = c("bluti", "greti", "bluti", "greti", "marti", 
    "bluti", "greti", "bluti", "greti", "marti", "bluti", "greti", 
    "bluti", "greti", "bluti", "greti", "bluti"), maxcount = c(17L, 
    3L, 14L, 11L, 1L, 2L, 2L, 13L, 5L, 3L, 7L, 1L, 9L, 2L, 5L, 
    1L, 4L), blutinew = c(17L, NA, NA, NA, NA, 2L, NA, NA, NA, 
    NA, 7L, NA, NA, NA, 5L, NA, NA), blutiretrap = c(NA, NA, 
    14L, NA, NA, NA, NA, 13L, NA, NA, NA, NA, 9L, NA, NA, NA, 
    4L), gretinew = c(NA, 3L, NA, NA, NA, NA, 2L, NA, NA, NA, 
    NA, 1L, NA, NA, NA, 1L, NA), gretiretrap = c(NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_), martinew = c(NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_), martiretrap = c(NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_)), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -17L), groups = structure(list(Site = c("2B", 
"2C", "FS", "HE"), .rows = structure(list(1:5, 6:10, 11:14, 15:17), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -4L), .drop = TRUE))

head(df)

期望结果 #

Site Year Maxcount_site blutinew blutiretrap gretinew gretiretrap martinew martiretrap
1   2B 2014            46       17          14        3          11        0           1
2   2C 2014            25        2          13        2           5        0           3
3   FS 2014            19        7           9        1           2        0           0
4   HE 2014            10        5           4        1           0        0           0

解决方案 #

可以使用dplyr包完成分组聚合、列筛选和NA替换操作，步骤如下：

1. 取消数据框的现有分组（原数据为grouped_df类型）
2. 移除不需要的列
3. 按Site和Year分组，对剩余列取最大值（同一分组内非NA值唯一，取最大值可实现行聚合）
4. 将所有NA值替换为0

代码实现：

library(dplyr)

result_df <- df %>%
  ungroup() %>%
  select(-Name, -Status, -maxcount) %>%
  group_by(Site, Year) %>%
  summarise(across(everything(), ~max(.x, na.rm = TRUE)), .groups = "drop") %>%
  mutate(across(everything(), ~replace_na(.x, 0)))

print(result_df, row.names = TRUE)

运行上述代码后，即可得到与期望格式一致的结果。

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