1

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)
Tom
  • 2,173
  • 1
  • 17
  • 44

1 Answers1

3

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
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 2
    You can also provide `measure.vars` instead of `id.vars`, and the id.vars will automatically be those not in the measure vars. i.e. `melt(DT, measure.vars = c("Event_A", "Event_B"), value.name = 'Event')[, variable := NULL][order(ID)]` – IceCreamToucan Dec 17 '19 at 19:31
  • @akrun I was wondering if it is possible to use a second value name. Let's say I have another two columns which are called `Event_A_Succes` and `Event_B_Succes` and I would want to spread those in the same way? – Tom Dec 18 '19 at 08:29
  • @Tom May be you need `melt(DT, measure = pattterns("Event_A", "Event_B"))` if that doesn't work, please do post as a new question – akrun Dec 18 '19 at 14:58
  • @akrun https://stackoverflow.com/questions/59395052/melting-two-sets-of-two-columns-into-two-rows-one-row-for-each-column-in-the-se – Tom Dec 18 '19 at 15:17
  • @Tom Ok, posted the answer there – akrun Dec 18 '19 at 15:21