R-如何使用各种共享相同级别的列创建虚拟变量

Question

我正在尝试获取下表的虚拟变量：

df:
Value1       var1       var2      var3      var4
9.330154398  HomeATL    AwayHOU   HomeEast  AwayWest
32.43881489  AwaySDN    HomeATL   HomeWest  AwayWest
54.77178387  AwayLAN    HomeATL   AwayEast  HomeSame
54.77178387  AwayLAN    HomeATL   AwayWest  HomeEast

列var1和var2共享相同的级别。另一方面，列var3和var4也将其水平降低。因此，我需要在创建虚拟变量期间，创建的新列不应重复级别。我的意思是，在VAR3和VAR4的示例中，对于第一行和第三行，都具有AwayWest，因此我只需要填充名为AwayWest的1列，每行中有一个数字1。

我所需的输出是：

Value1  HomeEast    HomeWest    AwayEast    AwayWest    HomeSame    HomeATL AwayHOU AwaySDN AwayLAN
9.330154398 1   0   0   1   0   1   1   0   0
32.43881489 0   1   0   1   0   1   0   1   0
54.77178387 0   0   1   0   1   1   0   0   1
54.77178387 1   0   0   1   0   1   0   0   1

我尝试为要转换的每列创建一个新列(col1(：

spread(df,var1, col1) %>%
spread(var2, col1)%>%
spread(var3, col1)%>%
spread(var1, col1)

但是它不起作用。

谢谢

Answer 1

基本r选项是使用 model.matrix

df <- cbind(df[, "Value1", drop = F], model.matrix(Value1 ~ . - 1, data = df))
df
#     Value1 var1AwayLAN var1AwaySDN var1HomeATL var2HomeATL var3AwayWest
#1  9.330154           0           0           1           0            0
#2 32.438815           0           1           0           1            0
#3 54.771784           1           0           0           1            0
#4 54.771784           1           0           0           1            1
#  var3HomeEast var3HomeWest var4HomeEast var4HomeSame
#1            1            0            0            0
#2            0            1            0            0
#3            0            0            0            1
#4            0            0            1            0

如有必要，我们可以使用

修复列名称

names(df) <- sub("var\d", "", names(df))

重现您的预期输出。

---

样本数据

df <- read.table(text =
    "Value1       var1       var2      var3      var4
9.330154398  HomeATL    AwayHOU   HomeEast  AwayWest
32.43881489  AwaySDN    HomeATL   HomeWest  AwayWest
54.77178387  AwayLAN    HomeATL   AwayEast  HomeSame
54.77178387  AwayLAN    HomeATL   AwayWest  HomeEast", header = T)

Answer 2

base R选项将是 table

tbl <- +(table(c(col(df1[-1])), unlist(df1[-1]) ) > 0)
cbind(df1[1], as.data.frame.matrix(tbl))

---

或使用tidyverse

library(tidyverse)
rownames_to_column(df1, 'rn') %>%
    gather(key, val, var1:var4) %>% 
    count(rn, val) %>%
    spread(val, n, fill = 0)  %>%
    select(-rn) %>%
    bind_cols(df1[1], .)

数据

df1 <- structure(list(Value1 = c(9.330154398, 32.43881489, 54.77178387, 
54.77178387), var1 = c("HomeATL", "AwaySDN", "AwayLAN", "AwayLAN"
), var2 = c("AwayHOU", "HomeATL", "HomeATL", "HomeATL"), var3 = c("HomeEast", 
"HomeWest", "AwayEast", "AwayWest"), var4 = c("AwayWest", "AwayWest", 
"HomeSame", "HomeEast")), class = "data.frame", row.names = c(NA, 
-4L))

Answer 3

您也可以做类似的事情 -

   > data.table::setDT(df)[,id:=1:.N]
   > cbind(df[,.(Value1)],dcast(
      melt(setDT(df)[, c(.(id=id), lapply(c("var1","var2","var3","var4"), function(x) paste0(x, get(x))))], id.vars="id"),
      id ~ value,
      length))

输出 -

     Value1  id var1AwayLAN var1AwaySDN var1HomeATL var2AwayHOU var2HomeATL var3AwayEast var3AwayWest var3HomeEast var3HomeWest
1:  9.330154  1           0           0           1           1           0            0            0            1            0
2: 32.438815  2           0           1           0           0           1            0            0            0            1
3: 54.771784  3           1           0           0           0           1            1            0            0            0
4: 54.771784  4           1           0           0           0           1            0            1            0            0