r - 生成一个字符变量作为ID变量

Question

我有以下结构的数据框:

structure(list(DateTime = structure(c(1578009600, 1578096000, 
1578182400, 1578268800, 1578268800, 1578441600, 1578528000, 1578700800, 
1578873600, 1578960000, 1579046400, 1579219200, 1579305600, 1579651200, 
1579737600, 1579910400, 1579996800, 1580083200, 1580256000, 1580342400
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), Y = c(0.398128487169134, 
0.943205112163668, 0.28252751156162, 0.249960153693576, 0.795387767024049, 
0.944143005087556, 0.158092709898581, 0.0939977195370584, 0.363535430584373, 
0.44838029451066, 0.693205040632161, 0.552676175195101, 0.985732436206492, 
0.496368199684004, 0.534787647236829, 0.327063363194893, 0.790654871992939, 
0.568471157855241, 0.837558574291766, 0.643802685572038), Var1 = c(0, 
0, 0, 0, 0, 0, 0.94290146, 0, 0, 0, 0, 0, 0, 0, 0.684747396078389, 
0, 0, 0, 0, 0), Var2 = c(0, 0.554797180422304, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), Var3 = c(0, 0, 0, 0.815909158957364, 
0, 0, 0, 0, 0, 0.648069655007399, 0, 0.501910457604968, 0, 0, 
0, 0, 0, 0.356728763364177, 0, 0), ID = c("0", "Var2", "0", "Var3", 
"0", "0", "0", "0", "0", "Var3", "0", "Var3", "0", "0", "0", 
"0", "0", "Var3", "0", "0")), row.names = c(NA, -20L), class = c("tbl_df", 
"tbl", "data.frame"))

我希望在我现有的数据框中创建/添加一个字符/字符串变量， ID1 ，包含变量的名称( Var1 、 Var2 或 Var3 )，它采用非零变量和数字 ID 变量 ID2 ，它根据具有非零值的变量分配一个“数字 ID”。任何知道如何有效计算 ID 的人和 ID2变量？

#1  DateTime    Y   Var1    Var2    Var3    
#2  3/1/20  0.67    0.00    0.00    0.00    
#3  4/1/20  0.31    0.00    0.11    0.00    
#4  5/1/20  0.96    0.00    0.00    0.00    
#5  6/1/20  0.28    0.00    0.00    0.40    
#6  6/1/20  0.28    0.00    0.00    0.00    
#7  8/1/20  0.48    0.00    0.00    0.00    
#8  9/1/20  0.07    0.94    0.00    0.00    
#9  11/1/20 0.94    0.00    0.00    0.00    
#10 13/1/20 0.05    0.00    0.00    0.00    
#11 14/1/20 0.04    0.00    0.00    0.53    
#12 15/1/20 0.93    0.00    0.00    0.00    
#13 17/1/20 0.30    0.00    0.00    0.32    
#14 18/1/20 0.75    0.00    0.00    0.00    
#15 22/1/20 0.99    0.00    0.00    0.00    
#16 23/1/20 0.32    0.15    0.00    0.00    
#17 25/1/20 0.51    0.00    0.00    0.00    
#18 26/1/20 0.15    0.00    0.00    0.00    
#19 27/1/20 0.78    0.00    0.00    0.15    
#20 29/1/20 0.83    0.00    0.00    0.00    
#21 30/1/20 0.10    0.00    0.00    0.00

预期输出(即 ID 和 ID2 添加到数据框中的变量)

#1  DateTime    Y   Var1    Var2    Var3       ID     ID2
    #2  3/1/20  0.67    0.00    0.00    0.00    0      0
    #3  4/1/20  0.31    0.00    0.11    0.00    Var2   1
    #4  5/1/20  0.96    0.00    0.00    0.00    0      0
    #5  6/1/20  0.28    0.00    0.00    0.40    Var3   2
    #6  6/1/20  0.28    0.00    0.00    0.00    0      0
    #7  8/1/20  0.48    0.00    0.00    0.00    0      0
    #8  9/1/20  0.07    0.94    0.00    0.00    Var1   3
    #9  11/1/20 0.94    0.00    0.00    0.00    0      0
    #10 13/1/20 0.05    0.00    0.00    0.00    0      0
    #11 14/1/20 0.04    0.00    0.00    0.53    Var3   2
    #12 15/1/20 0.93    0.00    0.00    0.00    0      0
    #13 17/1/20 0.30    0.00    0.00    0.32    Var3   2
    #14 18/1/20 0.75    0.00    0.00    0.00    0      0 
    #15 22/1/20 0.99    0.00    0.00    0.00    0      0
    #16 23/1/20 0.32    0.15    0.00    0.00    Var1   3
    #17 25/1/20 0.51    0.00    0.00    0.00    0      0
    #18 26/1/20 0.15    0.00    0.00    0.00    0      0
    #19 27/1/20 0.78    0.00    0.00    0.15    Var3   2
    #20 29/1/20 0.83    0.00    0.00    0.00    0      0
    #21 30/1/20 0.10    0.00    0.00    0.00    0      0

Answer 1

获取 var1、var2、var3 不为零的索引，然后根据该索引分配匹配的列名:

d$ID <- NULL # dropping this column, as we need to re-create it.

ix <- which(d[, 3:5] != 0, arr.ind = TRUE)
d[ ix[ , 1], "ID"] <- colnames(d[, 3:5])[ ix[, 2] ] 
d$ID2 <- as.numeric(as.factor(d$ID))

d
##  A tibble: 20 x 7
#    DateTime                 Y  Var1  Var2  Var3 ID      ID2
#    <dttm>               <dbl> <dbl> <dbl> <dbl> <chr> <dbl>
#  1 2020-01-03 00:00:00 0.398  0     0     0     NA       NA
#  2 2020-01-04 00:00:00 0.943  0     0.555 0     Var2      2
#  3 2020-01-05 00:00:00 0.283  0     0     0     NA       NA
#  4 2020-01-06 00:00:00 0.250  0     0     0.816 Var3      3
#  5 2020-01-06 00:00:00 0.795  0     0     0     NA       NA
#  6 2020-01-08 00:00:00 0.944  0     0     0     NA       NA
#  7 2020-01-09 00:00:00 0.158  0.943 0     0     Var1      1
#  8 2020-01-11 00:00:00 0.0940 0     0     0     NA       NA
#  9 2020-01-13 00:00:00 0.364  0     0     0     NA       NA
# 10 2020-01-14 00:00:00 0.448  0     0     0.648 Var3      3
# 11 2020-01-15 00:00:00 0.693  0     0     0     NA       NA
# 12 2020-01-17 00:00:00 0.553  0     0     0.502 Var3      3
# 13 2020-01-18 00:00:00 0.986  0     0     0     NA       NA
# 14 2020-01-22 00:00:00 0.496  0     0     0     NA       NA
# 15 2020-01-23 00:00:00 0.535  0.685 0     0     Var1      1
# 16 2020-01-25 00:00:00 0.327  0     0     0     NA       NA
# 17 2020-01-26 00:00:00 0.791  0     0     0     NA       NA
# 18 2020-01-27 00:00:00 0.568  0     0     0.357 Var3      3
# 19 2020-01-29 00:00:00 0.838  0     0     0     NA       NA
# 20 2020-01-30 00:00:00 0.644  0     0     0     NA       NA