I have an R data.table like this:
> old_table
id values
1: 1 A,B,C
2: 2 D,E
3: 3 F
4: 4 G,H,I,J
I want to expand the table by splitting up the values column by commas (,) like so:
> new_table
id value
1: 1 A
2: 1 B
3: 1 C
4: 2 D
5: 2 E
6: 3 F
7: 4 G
7: 4 H
7: 4 I
7: 4 J
Is there an easy way to do this?
I think
new_table <- old_table[, .(value=strsplit(values, ',')[[1]]), id]
Gives what you're after. Although there's a chance I'm wrong based on what the actual data type is of "values" in old_table. Here's how I set it up
old_table <- data.table(id=1:4,
values=c('A,B,C',
'D,E',
'F',
'G,H,I,J'))
new_table <- old_table[, .(value=strsplit(values, ',')[[1]]), id]
This outputs
id value
1: 1 A
2: 1 B
3: 1 C
4: 2 D
5: 2 E
6: 3 F
7: 4 G
8: 4 H
9: 4 I
10: 4 J
This is a way to do it using tidyverse tools separate and gather. You need to check what the longest comma-separated group you have in your values column is though, and leave enough variable names in the into argument for separate.
library(tidyverse)
old_table <- tibble(
id = as.integer(c(1, 2, 3, 4)),
values = c("A,B,C", "D,E", "F", "G,H,I,J")
)
max_commas <- max(str_count(old_table$values, ","))
old_table %>%
separate(values, into = c("v1", "v2", "v3", "v4"), sep = ",") %>%
gather(key = "key", value = "value", starts_with("v"), na.rm = TRUE) %>%
select(-key) %>%
arrange(value)
#> Warning: Too few values at 3 locations: 1, 2, 3
#> # A tibble: 10 x 2
#> id value
#> <int> <chr>
#> 1 1 A
#> 2 1 B
#> 3 1 C
#> 4 2 D
#> 5 2 E
#> 6 3 F
#> 7 4 G
#> 8 4 H
#> 9 4 I
#> 10 4 J
