在R中按UNIQUEID去重，保留最新季度的LAT/LONG并合并季度列

我有一个约300万行的大型数据集，其中UNIQUEID列存在重复值。需求如下：

• 按UNIQUEID分组
• 提取最新季度（优先级：Q4 2023 > Q3 2023 > Q2 2023，对应列值为1的行）对应的LAT和LONG值
• 合并每个分组下三个季度列的非NA值

示例数据 #

UNIQUEID   LAT LONG   Q4 2023   Q3 2023   Q2 2023
1: 8F5         51   -8      1       NA         NA
2: W7T         53   -6      1       NA         NA
3: 9F2         51   -8      NA      1          NA
4: 8F5         51   -9      NA      1          1
5: 9F2         58   -8      NA      1          1
6: W7T         53   -6      NA      1          NA

期望结果 #

UNIQUEID   LAT LONG   Q4 2023   Q3 2023   Q2 2023
1: 8F5         51   -8      1       1          1
2: W7T         53   -6      1       1         NA
3: 9F2         51   -8      NA      1          1

现有尝试代码 #

df <- df %>% 
  group_by(UNIQUEID) %>% 
  summarise(across(everything(), ~ paste(unique(.x[!is.na(.x)]), collapse = ", ")))

问题：无法确保LAT/LONG取到最新季度对应的值，且因数据集规模大，不想对季度列做unpivot操作。

附数据集结构 #

structure(list(UNIQUEID = c("8F5", "W7T", "9F2", "8F5", "9F2", 
"W7T"), LAT = c(51L, 53L, 51L, 51L, 58L, 53L), LONG = c(-8L, 
-6L, -8L, -9L, -8L, -6L), Q4.2023 = c(1L, 1L, NA, NA, NA, NA), 
    Q3.2023 = c(NA, NA, 1L, 1L, 1L, 1L), Q2.2023 = c(NA, NA, 
    NA, 1L, 1L, NA)), class = "data.frame", row.names = c(NA, 
-6L))

高效解决方案（无需unpivot） #

核心思路：先为每行标记优先级，取每个分组内优先级最高的行的LAT/LONG，再合并季度列的非NA值。

代码实现 #

library(dplyr)

# 为每行标记季度优先级，Q4优先级最高
df <- df %>%
  mutate(
    priority = case_when(
      Q4.2023 == 1 ~ 3,
      Q3.2023 == 1 ~ 2,
      Q2.2023 == 1 ~ 1,
      TRUE ~ 0
    )
  ) %>%
  group_by(UNIQUEID) %>%
  summarise(
    # 取优先级最高行的LAT和LONG
    LAT = LAT[which.max(priority)],
    LONG = LONG[which.max(priority)],
    # 合并季度列的非NA值（用max保留1，忽略NA）
    Q4.2023 = max(Q4.2023, na.rm = TRUE),
    Q3.2023 = max(Q3.2023, na.rm = TRUE),
    Q2.2023 = max(Q2.2023, na.rm = TRUE)
  ) %>%
  # 将全NA季度列的Inf结果替换回NA
  mutate(across(starts_with("Q"), ~ ifelse(is.infinite(.), NA, .))) %>%
  ungroup()

代码解释 #

1. 优先级标记：通过case_when给每行分配优先级分数，确保Q4标记的行拥有最高优先级，保证后续提取的坐标来自最新季度。
2. 分组聚合：
   • which.max(priority)定位分组内优先级最高的行，直接提取对应坐标，避免复杂排序
   • 季度列用max(..., na.rm=TRUE)合并非NA值，因为列值只有1和NA，max会自动保留有效标记；若某季度列全为NA，max返回的Inf会被替换回NA
3. 性能优化：全程避免unpivot操作（如pivot_longer），减少内存占用，适合百万级数据集的高效处理。

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