CODEWITHSUNDEEP
rdata.tabletransposemelt
Tom
Tom

Reputation: 2351

Melting/Splitting a row into two rows, using two column values in the original row, leaving the rest intact

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"))

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.

How should I solve this?

Desired output:

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)

Upvotes: 1

Views: 147

Answers (1)

akrun
akrun

Reputation: 887691

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

Upvotes: 3

Related Questions