Split two columns simultaneously and make data in long format with data.table

Question

Given the following data

d.df <- read.table(header=T, text="V1 | V2 | V3
A + C | Cat + Dog | Type 1
B + D | Bird | Type 1
A + D | Cat + Fish | Type 2" ,stringsAsFactors=F, sep="|", strip.white = TRUE)
require(data.table)
setDT(d.df)

I want to make this data in long format spliting the two variables simultaneously, so the desired output is like this

A Cat Type 1
C Dog Type 1
B Bird Type 1
D Bird Type 1
A Cat Type 2
D Fish Type 2

This way I could split based in one variable

output <- d.df[, list(V2 = unlist(str_split(V2, " \\+ "))), by = V1]

but if I try both together I get an error recycled with remainder.

Ronak Shah · Accepted Answer · 2020-01-16 11:12:57Z

2

We can use cSplit from splitstackshape

splitstackshape::cSplit(d.df, c("V1", "V2"), sep = "+", direction = "long")

#   V1   V2     V3
#1:  A  Cat Type 1
#2:  C  Dog Type 1
#3:  B Bird Type 1
#4:  D Fish Type 1
#5:  A  Cat Type 2
#6:  D Fish Type 2

separate_rows from tidyr also works in this case

tidyr::separate_rows(d.df, V1, V2, sep = "\\s+\\+\\s+")

answered Jan 16, 2020 at 11:12

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Roland Over a year ago

Nice, is there any way around the case I edited now in the data?

Ronak Shah Over a year ago

@Roland separate_rows gives the expected output with cSplit you can do dt <- cSplit(d.df, c("V1", "V2"), sep = "+", direction = "long"); dt[, V2 := zoo::na.locf(V2)]

Ronak Shah Over a year ago

Or probably this is better : dt[, c('V1', 'V2') := lapply(.SD, zoo::na.locf), .SDcols = c('V1', 'V2')]

chinsoon12 · Accepted Answer · 2020-01-17 00:57:47Z

1

Another option is to fall back on the base R recycling:

setDT(d.df)
ans <- d.df[, as.data.frame(lapply(.SD, function(x) trimws(strsplit(x, "\\+")[[1L]]))), 
    .(rn=d.df[,seq(.N)]), .SDcols=V1:V2][, 
        TYPE := d.df$V3[rn]]
ans

output:

   rn V1   V2   TYPE
1:  1  A  Cat Type 1
2:  1  C  Dog Type 1
3:  2  B Bird Type 1
4:  2  D Bird Type 1
5:  3  A  Cat Type 2
6:  3  D Fish Type 2

answered Jan 17, 2020 at 0:57

chinsoon12

25.2k4 gold badges27 silver badges35 bronze badges

Collectives™ on Stack Overflow

Split two columns simultaneously and make data in long format with data.table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related