Melting two sets of two columns into two rows (one row for each column in the set)

Question

I have a data.table as follows:

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

I want to cast the Event_X AND Event_X_succ columns over the row without summing them, creating new rows.

I learned here, that I can do this for Event_A and Event_B as follows:

library(data.table)
melt(DT, id.var = setdiff(names(DT), c("Event_A", "Event_B")), 
          value.name = 'Event')[, variable := NULL][order(ID)]

or (@IceCreamToucan):

melt(DT, measure.vars = c("Event_A", "Event_B"), value.name = 'Event')[, variable := NULL][order(ID)]

But I would like to add a second value name, Event_succ based on the other two columns called Event_A_Succ and Event_B_Succes and to spread those in the same way .

Desired output:

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

How should I solve this?

look into pivot_longer in the tidyr package. It has functionality to do what you want — Justin Landis, Dec 18 '19 at 17:10

score 2 · Accepted Answer · answered Dec 18 '19 at 15:20

We can use the measure with patterns

melt(DT, measure = patterns("Event_[AB]$", "Event_[AB]_succ"), 
     value.name =  c("Event", "Event_succ"))[, variable :=  NULL][order(ID)]
#    ID country year Event Event_succ
# 1:  4     NLD 2002     0          0
# 2:  4     NLD 2002     1          0
# 3:  5     NLD 2002     0          0
# 4:  5     NLD 2002     1          1
# 5:  6     NLD 2006     1          1
# 6:  6     NLD 2006     1          1
# 7:  7     NLD 2006     1          1
# 8:  7     NLD 2006     0          0
# 9:  8     NLD 2006     1          0
#10:  8     NLD 2006     1          0
#11:  9     GBR 2002     0          0
#12:  9     GBR 2002     1          0
#13: 10     GBR 2002     0          0
#14: 10     GBR 2002     0          0
#15: 11     GBR 2002     0          0
#16: 11     GBR 2002     1          1
#17: 12     GBR 2006     1          1
#18: 12     GBR 2006     1          1
#19: 13     GBR 2006     1          0
#20: 13     GBR 2006     1          1

Had the same solution but with `[, variable := NULL][order(ID)]` as `[order(ID), !"variable"]` — s_baldur, Dec 18 '19 at 15:28

Melting two sets of two columns into two rows (one row for each column in the set)

1 Answers1

Linked