如何在R Studio中基于ID与Quarterly列去除重复数据？

需求 #

需要对dataframe2按ID与Quarterly的组合去除重复行，每个组合仅保留一行数据，且保留每组中最先出现的Status值。

原始数据 #

数据结构定义：

dataframe2 = structure(list(ID = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), 
                            Quarterly = c(2006.1, 2006.1, 2006.1, 2006.2, 2006.3, 2006.4, 2006.1, 2006.2, 2006.3, 
                                          2006.3, 2006.4, 2006.1, 2006.1, 2006.1, 2006.2, 2006.2, 2006.3, 2006.4), 
                            Status = c("Employed", "Employed", "Employed", "Employed", "Null", "Employed", "Employed",
                                       "Employed", "Employed", "Employed", "Employed", "Null", "Null", "Employed", 
                                       "Employed", "Employed", "Employed", "Employed")), 
                       class = "data.frame", row.names = c(NA, -18L))

数据预览：

ID   Quarterly    Status 
1    2006.1     Employed 
1    2006.1     Employed 
1    2006.1     Employed 
1    2006.2     Employed 
1    2006.3     Null 
1    2006.4     Employed 
2    2006.1     Employed 
2    2006.2     Employed 
2    2006.3     Employed 
2    2006.3     Employed 
2    2006.4     Employed 
3    2006.1     Null 
3    2006.1     Null 
3    2006.1     Employed 
3    2006.2     Employed 
3    2006.2     Employed 
3    2006.3     Employed 
3    2006.4     Employed 

期望输出 #

ID   Quarterly    Status 
1    2006.1     Employed 
1    2006.2     Employed 
1    2006.3     Null 
1    2006.4     Employed 
2    2006.1     Employed 
2    2006.2     Employed 
2    2006.3     Employed 
2    2006.4     Employed 
3    2006.1     Null 
3    2006.2     Employed 
3    2006.3     Employed 
3    2006.4     Employed 

现有方法的问题 #

• 方法(a)：

group_by(ID,Quarterly) %>% filer(n()>1)

存在两个问题：一是filer拼写错误（应为filter），二是该代码的作用是筛选出重复次数大于1的分组，而非保留唯一行。

• 方法(b)：

group_by(ID,Quarterly) %>% distinct(ID, keep.all = TRUE)

参数使用错误：仅指定ID作为去重依据，且分组后使用distinct的逻辑不符合需求，导致结果偏差。

正确解决方案 #

方案1：用distinct直接指定去重列 #

通过distinct同时指定ID和Quarterly作为去重键，保留所有列的同时仅保留每组第一行：

library(dplyr)

dataframe_cleaned <- dataframe2 %>%
  distinct(ID, Quarterly, .keep_all = TRUE)

方案2：分组后取每组第一行 #

先按ID和Quarterly分组，再提取每组的第一行数据，最后取消分组：

dataframe_cleaned <- dataframe2 %>%
  group_by(ID, Quarterly) %>%
  slice(1) %>%
  ungroup()

两种方案均可得到符合预期的结果，默认保留每组中最先出现的行（对应Status为该组合首次出现的值）。

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