使用（从唯一列中提取唯一值）将一列添加到数据框

wyatt 发表于 Dev

怀亚特

我是R的新手，无法为我遇到的特定问题寻找答案。

如果我的数据框如下所示：

d <- data.frame(Name = c("Jon", "Jon", "Jon", "Kel", "Kel", "Kel", "Don", "Don", "Don"),
             No1 = c(1,2,3,1,1,1,3,3,3),
             No2 = c(1,1,1,2,2,2,3,3,3))

Name No1 No2
Jon   1   1
Jon   2   1
Jon   3   1
Kel   1   2
Kel   1   2
Kel   1   2
Don   3   3
Don   3   3
Don   3   3 
...

我如何添加能够向数据框添加新列，其中这些列将指示columnNo1和中的唯一值No2：对于John，Kelly，分别为（1,2,3），（1,2），（3），唐

因此，如果将新列命名为ID#，则预期结果应为

d2 <- data.frame(Name = c("Jon", "Jon", "Jon", "Kel", "Kel", "Kel", "Don", "Don", "Don"),
          No1 = c(1,2,3,1,1,1,3,3,3),
          No2 = c(1,1,1,2,2,2,3,3,3),
          ID1 = c(1,1,1,1,1,1,3,3,3),
          ID2 = c(2,2,2,2,2,2,NA,NA,NA),
          ID3 = c(3,3,3,NA,NA,NA,NA,NA,NA))

Name No1 No2 ID1 ID2 ID3
Jon   1   1   1   2   3 
Jon   2   1   1   2   3
Jon   3   1   1   2   3 
Kel   1   2   1   2   NA
Kel   1   2   1   2   NA
Kel   1   2   1   2   NA
Don   3   3   3   NA  NA
Don   3   3   3   NA  NA
Don   3   3   3   NA  NA

利斯特尔

整洁的方法：

library(dplyr)
library(tidyr)

      # evaluate separately for each name
d %>% group_by(Name) %>% 
    # add a column of the unique values pasted together into a string
    mutate(ID = paste(unique(c(No1, No2)), collapse = ' ')) %>% 
    # separate the string into individual columns, filling with NA and converting to numbers
    separate(ID, into = paste0('ID', 1:3), fill = 'right', convert = TRUE)

## Source: local data frame [9 x 6]
## Groups: Name [3]
## 
##     Name   No1   No2   ID1   ID2   ID3
## * <fctr> <dbl> <dbl> <int> <int> <int>
## 1    Jon     1     1     1     2     3
## 2    Jon     2     1     1     2     3
## 3    Jon     3     1     1     2     3
## 4    Kel     1     2     1     2    NA
## 5    Kel     1     2     1     2    NA
## 6    Kel     1     2     1     2    NA
## 7    Don     3     3     3    NA    NA
## 8    Don     3     3     3    NA    NA
## 9    Don     3     3     3    NA    NA

这是一个很好的基本版本，带有基本的“拆分应用”组合方法：

# store distinct values in No1 and No2
cols <- unique(unlist(d[,-1]))
                           # split No1 and No2 by Name,
ids <- data.frame(t(sapply(split(d[,-1], d$Name), 
                           # find unique values for each split,
                           function(x){y <- unique(unlist(x))
                                       # pad with NAs,
                                       c(y, rep(NA, length(cols) - length(y)))
                           # and return a data.frame
                           }))) 
# fix column names
names(ids) <- paste0('ID', cols)
# turn rownames into column
ids$Name <- rownames(ids)
# join two data.frames on Name columns
merge(d, ids, sort = FALSE)

##   Name No1 No2 ID1 ID2 ID3
## 1  Jon   1   1   1   2   3
## 2  Jon   2   1   1   2   3
## 3  Jon   3   1   1   2   3
## 4  Kel   1   2   1   2  NA
## 5  Kel   1   2   1   2  NA
## 6  Kel   1   2   1   2  NA
## 7  Don   3   3   3  NA  NA
## 8  Don   3   3   3  NA  NA
## 9  Don   3   3   3  NA  NA

仅此而已，这是一个创造性的替代基本版本，该版本利用table而不是拆分/分组：

# copy d so as not to distort original with factor columns
d_f <- d
# make No* columns factors to ensure similar table structure
d_f[, -1] <- lapply(d[,-1], factor, levels = unique(unlist(d[, -1])))
# make tables of cols, sum to aggregate occurrences, and set as boolean mask for > 0
tab <- Reduce(`+`, lapply(d_f[, -1], table, d_f$Name)) > 0
# replace all TRUE values with values they tabulated
tab <- tab * matrix(as.integer(rownames(tab)), nrow = nrow(tab), ncol = ncol(tab))
# replace 0s with NAs
tab[tab == 0] <- NA
# store column names
cols <- paste0('ID', rownames(tab))
# sort each row, keeping NAs
tab <- data.frame(t(apply(tab, 2, sort, na.last = T)))
# apply stored column names
names(tab) <- cols
# turn rownames into column
tab$Name <- rownames(tab)
# join two data.frames on Name columns
merge(d, tab, sort = FALSE)