r - id被分配给多个类别如何删除行以便id在r中只有一个类别

Question

我正在使用 R，需要一些帮助。我有一个巨大的数据集，其中 id 被分配给多个类别。我想删除行所以 id 只有一个类别。逻辑如下:如果 id 仅分配给“已分配”列中的一个类别，则不删除任何行(参见 id 2)，但是当 id 分配给“已分配”列中的多个类别时，删除它具有的行“Banana”，除非带有“Banana”的行数多于删除带有“Apple”的行数(参见 id 3)。如果我能在单独的数据框中获取我删除的记录，那就太好了。

这是我的

df <- data.frame(ID=c(1,1,1,2,2,3,3,3,4,4), 
                 Date=c("04-01-2020", "05-01-2020", "06-01-2020", "04-01-2020",
                 "05-01-2020", "04-01-2020", "05-01-2020", "06-01-2020",
                 "05-01-2020", "06-01-20202"),
                 Assigned = c("Apple", "Banana","Apple","Apple", "Apple",
                             "Apple","Banana","Banana", "Apple", "Banana"))

这是我想要的

df1 <-  data.frame(ID=c(1,1,2,2,3,3,4), 
                   Date=c("04-01-2020", "06-01-2020", "04-01-2020",
                          "05-01-2020", "05-01-2020", "06-01-2020",
                          "05-01-2020"),
                   Assigned = c("Apple", "Apple","Apple", "Apple",
                                "Banana","Banana", "Apple"))
                           

此外，如何轻松获取 ID 具有多个类别的行(获取除 id 2 之外的所有行)。提前致谢。

Answer 1

我们可以使用 Mode 函数并通过 filter 进行分组

library(dplyr)
Mode <- function(x) {
   ux <- unique(x)
   ux[which.max(tabulate(match(x, ux)))]
  }

df %>% 
    group_by(ID) %>% 
    filter(Assigned == Mode(Assigned))
# A tibble: 7 x 3
# Groups:   ID [4]
#     ID Date       Assigned
#  <dbl> <chr>      <chr>   
#1     1 04-01-2020 Apple   
#2     1 06-01-2020 Apple   
#3     2 04-01-2020 Apple   
#4     2 05-01-2020 Apple   
#5     3 05-01-2020 Banana  
#6     3 06-01-2020 Banana  
#7     4 05-01-2020 Apple

---

或者以更紧凑的方式

library(tibble)
df %>%
    # // creates a frequency column based on ID, Assigned
    add_count(ID, Assigned) %>% 
    # // arrange by ID, Assigned
    arrange(ID, Assigned) %>%
    # // grouped by ID
    group_by(ID) %>% 
    # // create a logical vector where the first 'Assigned' value 
    # // with the max frequency
    filter(Assigned == Assigned[which.max(n)])   %>%
    select(-n) 
# A tibble: 7 x 3
# Groups:   ID [4]
#     ID Date       Assigned
#  <dbl> <chr>      <chr>   
#1     1 04-01-2020 Apple   
#2     1 06-01-2020 Apple   
#3     2 04-01-2020 Apple   
#4     2 05-01-2020 Apple   
#5     3 05-01-2020 Banana  
#6     3 06-01-2020 Banana  
#7     4 05-01-2020 Apple