r-如何为某个细胞中的多个个体制作二进制变量

Question

我正在处理一个有点不稳定的数据帧。我使用的是一个标准的表格数据集/.csv文件，它在大多数情况下都是相当标准的，然而，在一列中，每个观察结果都是个人列表。它看起来是这样的：

Layer       Grade              Players
Top           A         NY 08; NY 27; NY 80
Bottom        D         MA 27; MA 45; MA 65
Middle        B         NY 09; MA 48; NY 66
...

正如您所看到的，除了"玩家"列之外，数据帧是标准的。我如何为每个玩家添加一列，提供他们是否在游戏中的二进制指示符？我希望上面的数据帧变成这样：

Layer       Grade       Players                       NYAL 08     NYAL 27     NYAL 80    MAAC 27
Top           A         NYAL 08; NYAL 27; NYAL 80       1           1           1          0
Bottom        D         MAAC 27; MAAC 45; MAAC 65       0           0           0          1
Middle        B         NYAL 08; MAAC 48; NYAL 66       1           0           0          0
...

等等。

谢谢！

Answer 1

我们可以从splitstackshape使用cSplit_e。它将在单行中以紧凑的方式获得输出

library(splitstackshape)
out <- cSplit_e(df1, 'Players', sep=";", type = "character", fill = 0)
out

-输出

#Layer Grade             Players Players_MA 27 Players_MA 45 Players_MA 48 Players_MA 65 Players_NY 08 Players_NY 09 Players_NY 27
#1    Top     A NY 08; NY 27; NY 80             0             0             0             0             1             0             1
#2 Bottom     D MA 27; MA 45; MA 65             1             1             0             1             0             0             0
#3 Middle     B NY 09; MA 48; NY 66             0             0             1             0             0             1             0
#  Players_NY 66 Players_NY 80
#1             0             1
#2             0             0
#3             1             0

如果我们想删除列名中的前缀

names(out)[-(1:3)] <- sub('Players_', '', names(out)[-(1:3)])

---

或者另一个选项是mtabulate

cbind(df1, mtabulate(strsplit(df1$Player, ";\s+")))

-输出

#   Layer Grade             Players MA 27 MA 45 MA 48 MA 65 NY 08 NY 09 NY 27 NY 66 NY 80
#1    Top     A NY 08; NY 27; NY 80     0     0     0     0     1     0     1     0     1
#2 Bottom     D MA 27; MA 45; MA 65     1     1     0     1     0     0     0     0     0
#3 Middle     B NY 09; MA 48; NY 66     0     0     1     0     0     1     0     1     0

数据

df1 <- structure(list(Layer = c("Top", "Bottom", "Middle"), Grade = c("A", 
"D", "B"), Players = c("NY 08; NY 27; NY 80", "MA 27; MA 45; MA 65", 
"NY 09; MA 48; NY 66")), class = "data.frame", row.names = c(NA, 
-3L))

Answer 2

尝试使用separate_rows()和pivot_wider()的tidyverse方法，这将产生与您想要的结果接近的结果：

library(tidyverse)
#Code
df2 <- df %>% mutate(Dup=Players) %>%
separate_rows(Dup,sep = '; ') %>%
mutate(id=1) %>%
pivot_wider(names_from = Dup,values_from=id) %>%
replace(is.na(.),0)

输出：

# A tibble: 3 x 12
Layer  Grade Players   `NY 08` `NY 27` `NY 80` `MA 27` `MA 45` `MA 65` `NY 09` `MA 48` `NY 66`
<chr>  <chr> <chr>       <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
1 Top    A     NY 08; N~       1       1       1       0       0       0       0       0       0
2 Bottom D     MA 27; M~       0       0       0       1       1       1       0       0       0
3 Middle B     NY 09; M~       0       0       0       0       0       0       1       1       1

使用的一些数据：

#Data
df <- structure(list(Layer = c("Top", "Bottom", "Middle"), Grade = c("A", 
"D", "B"), Players = c("NY 08; NY 27; NY 80", "MA 27; MA 45; MA 65", 
"NY 09; MA 48; NY 66")), class = "data.frame", row.names = c(NA, 
-3L))