transform data.table in r

user_17603068 0
Viewed 35

I have data like this:

data1=data.frame("long_customers"=c(TRUE, FALSE, FALSE, TRUE),
                 "long_partners"=c(FALSE, TRUE, FALSE, FALSE),
                 "short_customers"=c(FALSE, TRUE,TRUE,FALSE),
                 "short_partners"=c(FALSE,FALSE,NA,NA))

I want to transform/reshape this so it looks like:

data2=data.frame("x"=c("long","long","long","long","long","long","long","long", "short", "short", "short", "short", "short", "short", "short", "short"),
                 "y"=c("customers", "customers", "customers", "customer", "partners", "partners", "partners", "partners","customers", "customers", "customers", "customer", "partners", "partners", "partners", "partners"),
                 "event"=c(TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, NA, NA))

How do I get there? I'd appreciate any pointers and would love to get a data.table way of doing this.

Thanks!

reshape transform dataframe r datatable
2 Answers

For a data.table solution using melt then tstrsplit:

setDT(data1)
melt(data1, value.name="event",)[, c("x", "y") := tstrsplit(variable, "_")][,.(x,y,event)]
        x         y event
 1:  long customers  TRUE
 2:  long customers FALSE
 3:  long customers FALSE
 4:  long customers  TRUE
 5:  long  partners FALSE
 6:  long  partners  TRUE
 7:  long  partners FALSE
 8:  long  partners FALSE
 9: short customers FALSE
10: short customers  TRUE
11: short customers  TRUE
12: short customers FALSE
13: short  partners FALSE
14: short  partners FALSE
15: short  partners    NA
16: short  partners    NA
user_121762800

You can use pivot_longer and separate:

library(tidyverse)

data1 %>% 
  pivot_longer(cols = everything()) %>% 
  separate(name, into = c("x", "y")) %>% 
  arrange(x, y)

# A tibble: 16 x 3
   x     y         value
   <chr> <chr>     <lgl>
 1 long  customers TRUE 
 2 long  customers FALSE
 3 long  customers FALSE
 4 long  customers TRUE 
 5 long  partners  FALSE
 6 long  partners  TRUE 
 7 long  partners  FALSE
 8 long  partners  FALSE
 9 short customers FALSE
10 short customers TRUE 
11 short customers TRUE 
12 short customers FALSE
13 short partners  FALSE
14 short partners  FALSE
15 short partners  NA   
16 short partners  NA
user_134606020
Related