使用原始行中的两个列值将一行熔化/拆分为两行，其余部分保持不变 [英] Melting/Splitting a row into two rows, using two column values in the original row, leaving the rest intact

问题描述

我有一个data.table，如下：

I have a data.table as follows:

DT <- fread(
"ID country year Event_A Event_B
4   NLD   2002  0   1
5   NLD   2002  0   1
6   NLD   2006  1   1
7   NLD   2006  1   0
8   NLD   2006  1   1
9   GBR   2002  0   1
10  GBR   2002  0   0
11  GBR   2002  0   1
12  GBR   2006  1   1
13  GBR   2006  1   1",
header = TRUE)

我想将事件列投射到行上而不求和，从而创建新行。我尝试过：

I want to cast the event columns over the row without summing them, creating new rows. I tried:

meltedsessions <- melt(Exp, id.vars = -c(Event_A", "Event_B"), measure.vars = c("Event_A", "Event_B"))

我需要指定 id.vars 为负数，因为实际数据集还有另外240个需要保持不变的变量。但是，如果执行此操作，则会出现错误：

I need to specify id.vars as a negative because the actual dataset has another 240 variables that need to stay intact. However if I do this I get the error:

Error in melt.data.table(Exp, id.vars = c("ID", "country", "year"), measure.vars = c("Event_A",  : 
  One or more values in 'id.vars' is invalid.

我应该如何解决？

所需的输出：

DT <- fread(
"NewID  ID country year Event
1  4   NLD   2002  0 
2  4   NLD   2002  1
3  5   NLD   2002  0
4  5   NLD   2002  1
5  6   NLD   2006  1
6  6   NLD   2006  1
7  7   NLD   2006  1
8  7   NLD   2006  0
9  8   NLD   2006  1
10 8   NLD   2006  0
11 9   GBR   2002  1
12 9   GBR   2002  1
13 10  GBR   2002  0
14 10  GBR   2002  0
15 11  GBR   2002  0
16 12  GBR   2002  1
17 13  GBR   2006  1
18 14  GBR   2006  1
19 15  GBR   2006  1
20 16  GBR   2006  1",
header = TRUE)

推荐答案

代替 id.var <中的- / code>，可以使用 setdiff

Instead of - in id.var, can use setdiff

library(data.table)
melt(DT, id.var = setdiff(names(DT), c("Event_A", "Event_B")), 
          value.name = 'Event')[, variable := NULL][order(ID)]
#     ID country year Event
# 1:  4     NLD 2002     0
# 2:  4     NLD 2002     1
# 3:  5     NLD 2002     0
# 4:  5     NLD 2002     1
# 5:  6     NLD 2006     1
# 6:  6     NLD 2006     1
# 7:  7     NLD 2006     1
# 8:  7     NLD 2006     0
# 9:  8     NLD 2006     1
#10:  8     NLD 2006     1
#11:  9     GBR 2002     0
#12:  9     GBR 2002     1
#13: 10     GBR 2002     0
#14: 10     GBR 2002     0
#15: 11     GBR 2002     0
#16: 11     GBR 2002     1
#17: 12     GBR 2006     1
#18: 12     GBR 2006     1
#19: 13     GBR 2006     1
#20: 13     GBR 2006     1

这篇关于使用原始行中的两个列值将一行熔化/拆分为两行，其余部分保持不变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！