如何在R中将变量的值替换为最频繁的值

Question

我正在使用R语言处理数据框，其中包含姓名和对应的办公地址。但是地址与姓名并不完全匹配，因此我需要根据最频繁出现的值替换错误的地址。以下是一个示例：

ID   Clients ADDRESS    FIRST_NAME   LAST_NAME
1     A       Cobben      Cui          Z 
2     B       Beerstaat   Cui          Z
3     C       River       Cui          Z
4     D       Cobben      Cui          Z
5     E       Cobben      Cui          Z
6     F       Cobben      Cui          Z
6     F       Hart        Mike         G
7     H       Hart        Mike         G
8     I       Sparren     Mike         G

我希望得到一个类似于此的数据框：

ID   Clients ADDRESS    FIRST_NAME   LAST_NAME
1     A       Cobben      Cui          Z 
2     B       Cobben      Cui          Z
3     C       Cobben      Cui          Z
4     D       Cobben      Cui          Z
5     E       Cobben      Cui          Z
6     F       Cobben      Cui          Z
6     F       Hart        Mike         G
7     H       Hart        Mike         G
8     I       Hart        Mike         G

我认为可以通过创建频率表来完成，我想知道是否有更简单的方法。

非常感谢！

- cara

Answer 1

使用add_count和which.max的dplyr方法可能如下所示:

library(dplyr)

d %>% 
  add_count(LAST_NAME, ADDRESS) %>% 
  group_by(LAST_NAME) %>% 
  mutate(ADDRESS = ADDRESS[which.max(n)]) %>% 
  ungroup() %>% 
  select(-n)
#> # A tibble: 9 × 5
#>      ID Clients ADDRESS FIRST_NAME LAST_NAME
#>   <int> <chr>   <chr>   <chr>      <chr>    
#> 1     1 A       Cobben  Cui        Z        
#> 2     2 B       Cobben  Cui        Z        
#> 3     3 C       Cobben  Cui        Z        
#> 4     4 D       Cobben  Cui        Z        
#> 5     5 E       Cobben  Cui        Z        
#> 6     6 F       Cobben  Cui        Z        
#> 7     6 F       Hart    Mike       G        
#> 8     7 H       Hart    Mike       G        
#> 9     8 I       Hart    Mike       G

数据

d <- structure(list(ID = c(1L, 2L, 3L, 4L, 5L, 6L, 6L, 7L, 8L), Clients = c(
  "A",
  "B", "C", "D", "E", "F", "F", "H", "I"
), ADDRESS = c(
  "Cobben",
  "Beerstaat", "River", "Cobben", "Cobben", "Cobben", "Hart", "Hart",
  "Sparren"
), FIRST_NAME = c(
  "Cui", "Cui", "Cui", "Cui", "Cui",
  "Cui", "Mike", "Mike", "Mike"
), LAST_NAME = c(
  "Z", "Z", "Z",
  "Z", "Z", "Z", "G", "G", "G"
)), class = "data.frame", row.names = c(
  NA,
  -9L
))

- stefan

Answer 2

不是那么容易。我喜欢斯特凡的版本。 这里有一个可能的替代方案：

 df %>% 
    group_by(ADDRESS) %>% 
    summarise(n = n()) %>% 
    left_join(df, by = c("ADDRESS")) %>%  
    group_by(FIRST_NAME, LAST_NAME) %>% 
    arrange(desc(n), .by_group = TRUE) %>% 
    mutate(ADDRESS = first(ADDRESS)) %>% 
    select(-n)

  ADDRESS    ID Clients FIRST_NAME LAST_NAME
  <chr>   <int> <chr>   <chr>      <chr>    
1 Cobben      1 A       Cui        Z        
2 Cobben      4 D       Cui        Z        
3 Cobben      5 E       Cui        Z        
4 Cobben      6 F       Cui        Z        
5 Cobben      2 B       Cui        Z        
6 Cobben      3 C       Cui        Z        
7 Hart        6 F       Mike       G        
8 Hart        7 H       Mike       G        
9 Hart        8 I       Mike       G 

- TarJae

Answer 3

我们可以使用基本的 R - 在 'ADDRESS' 上以 'FIRST_NAME'、'LAST_NAME' 为分组条件使用 ave 中的 Mode。

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

df1$ADDRESS <- with(df1, ave(ADDRESS, FIRST_NAME,LAST_NAME, FUN = Mode))

-输出

> df1
  ID Clients ADDRESS FIRST_NAME LAST_NAME
1  1       A  Cobben        Cui         Z
2  2       B  Cobben        Cui         Z
3  3       C  Cobben        Cui         Z
4  4       D  Cobben        Cui         Z
5  5       E  Cobben        Cui         Z
6  6       F  Cobben        Cui         Z
7  6       F    Hart       Mike         G
8  7       H    Hart       Mike         G
9  8       I    Hart       Mike         G

数据

df1 <- structure(list(ID = c(1L, 2L, 3L, 4L, 5L, 6L, 6L, 7L, 8L), 
Clients = c("A", 
"B", "C", "D", "E", "F", "F", "H", "I"), ADDRESS = c("Cobben", 
"Beerstaat", "River", "Cobben", "Cobben", "Cobben", "Hart", "Hart", 
"Sparren"), FIRST_NAME = c("Cui", "Cui", "Cui", "Cui", "Cui", 
"Cui", "Mike", "Mike", "Mike"), LAST_NAME = c("Z", "Z", "Z", 
"Z", "Z", "Z", "G", "G", "G")), class = "data.frame", row.names = c(NA, 
-9L))

- akrun