How to reshape a vector with comma separated records into a longitudinal dataframe?

Question

This is the dataframe I have:

data_frame(id= c(1,2,3), 
           x=c('19,22,77', '49,67', '28,19,45,23'),
           y=c('19,22,77', '49,67', '28,19,45,23'),
           t=c('10,20,30', '49,67', '28,19,45,23'))

The comma separated values are different observations over time for the same id thus I would like to strsplit on comma and reshape in longitudinal format but preserving the association with id

For example, the output for the id=1 only should be:

# A tibble: 3 × 4
     id     x     y     t
  <dbl> <dbl> <dbl> <dbl>
1     1    19    19    10
2     1    22    22    20
3     1    77    77    30

score 2 · Answer 1 · answered May 04 '17 at 12:12

Here is one method with data.table.

library(data.table)

setDT(df)[, lapply(.SD, tstrsplit, split=","), by=id]
   id  x  y  t
1:  1 19 19 10
2:  1 22 22 20
3:  1 77 77 30
4:  2 49 49 49
5:  2 67 67 67
6:  3 28 28 28
7:  3 19 19 19
8:  3 45 45 45
9:  3 23 23 23

for each id, we lapply through the variables and apply a trsplit (transpose string split), splitting on the comma.

data

df <- data.frame(id= c(1,2,3), 
                 x=c('19,22,77', '49,67', '28,19,45,23'),
                 y=c('19,22,77', '49,67', '28,19,45,23'),
                 t=c('10,20,30', '49,67', '28,19,45,23'))

score 2 · Accepted Answer · answered May 04 '17 at 12:21

2

in addition, you can use tidyr:

library(tidyr)
separate_rows(df,x,y,t, sep = ",")

answered May 04 '17 at 12:21

Janna Maas

1,124
10
15

i had never seen the `separate_rows` function. way more succinct than my solution – Adam Spannbauer May 04 '17 at 12:23

score 1 · Answer 3 · answered May 04 '17 at 12:20

alternate solution using the hadleyverse

library(magrittr)

dplyr::data_frame(id= c(1,2,3), 
                  x=c('19,22,77', '49,67', '28,19,45,23'),
                  y=c('19,22,77', '49,67', '28,19,45,23'),
                  t=c('10,20,30', '49,67', '28,19,45,23')) %>% 
dplyr::mutate_if(is.character, stringr::str_split, pattern=',') %>% 
tidyr::unnest()


# A tibble: 9 × 4
     id     x     y     t
  <dbl> <chr> <chr> <chr>
1     1    19    19    10
2     1    22    22    20
3     1    77    77    30
4     2    49    49    49
5     2    67    67    67
6     3    28    28    28
7     3    19    19    19
8     3    45    45    45
9     3    23    23    23

How to reshape a vector with comma separated records into a longitudinal dataframe?

3 Answers3