Reshaping multiple sets of measurement columns (wi

Question

I have a dataframe in a wide format, with repeated measurements taken within different date ranges. In my example there are three different periods, all with their corresponding values. E.g. the first measurement (Value1) was measured in the period from DateRange1Start to DateRange1End:

ID DateRange1Start DateRange1End Value1 DateRange2Start DateRange2End Value2 DateRange3Start DateRange3End Value3
1 1/1/90 3/1/90 4.4 4/5/91 6/7/91 6.2 5/5/95 6/6/96 3.3 

I'm looking to reshape the data to a long format such that the DateRangeXStart and DateRangeXEnd columns are grouped,. Thus, what was 1 row in the original table becomes 3 rows in the new table:

ID DateRangeStart DateRangeEnd Value
1 1/1/90 3/1/90 4.4
1 4/5/91 6/7/91 6.2
1 5/5/95 6/6/96 3.3

I know there must be a way to do this with reshape2/melt/recast/tidyr, but I can't seem to figure it out how to map the multiple sets of measure variables into single sets of value columns in this particular way.

Answer 1

Using recycling:

data.frame(ID = d[, 1],
           DateRangeStart = unlist(d[, -1][, c(TRUE, FALSE, FALSE)]),
           DateRangeEnd  = unlist(d[, -1][, c(FALSE, TRUE, FALSE)]),
           Value =  unlist(d[, -1][, c(FALSE, FALSE, TRUE)]))

Answer 2

You don't need anything fancy; base R functions will do.

a <- read.table(textConnection("
ID DateRange1Start DateRange1End Value1 DateRange2Start DateRange2End Value2 DateRange3Start DateRange3End Value3
1 1/1/90 3/1/90 4.4 4/5/91 6/7/91 6.2 5/5/95 6/6/96 3.3 
"),header=TRUE)
b1 <- a[,c(1:4)]; b2 <- a[,c(1,5:7)]; b3 <- a[,c(1,8:10)]
colnames(b1) <- colnames(b2) <- colnames(b3) <- c("ID","DateRangeStart","DateRangeEnd","Value")
b <- rbind(b1,b2,b3)

Answer 3

Two additional options (with an example dataframe with more than one row to better show the working of the code):

1) with base R:

l <- lapply(split.default(d[-1], cumsum(grepl('Start$', names(d)[-1]))),
            setNames, c('DateRangeStart','DateRangeEnd','Value'))
data.frame(ID = d[,1], do.call(rbind, l), row.names = NULL)

which gives:

  ID DateRangeStart DateRangeEnd Value
1  1         1/1/90       3/1/90   4.4
2  2         1/2/90       3/2/90   6.1
3  1         4/5/91       6/7/91   6.2
4  2         4/6/91       6/8/91   3.2
5  1         5/5/95       6/6/96   3.3
6  2         5/5/97       6/6/98   1.3

2) with the tidyverse:

library(dplyr)
library(purrr)

split.default(d[-1], cumsum(grepl('Start$', names(d)[-1]))) %>%
  map_dfr(~set_names(., c('DateRangeStart','DateRangeEnd','Value'))) %>% 
  bind_cols(ID = rep(d$ID, nrow(.)/nrow(d)), .)

3) with the sjmisc-package:

library(sjmisc)
to_long(d, keys = 'group',
        values = c('DateRangeStart','DateRangeEnd','Value'), 
        c('DateRange1Start','DateRange2Start','DateRange3Start'),
        c('DateRange1End','DateRange2End','DateRange3End'),
        c('Value1','Value2','Value3'))[,-2]

---

If you also want a group/time column, you can adapt the approaches above to:

1) with base R:

l <- lapply(split.default(d[-1], cumsum(grepl('Start$', names(d)[-1]))),
            setNames, c('DateRangeStart','DateRangeEnd','Value'))
data.frame(ID = d[,1],
           group = rep(seq_along(l), each = nrow(d)),
           do.call(rbind, l), row.names = NULL)

which gives:

  ID group DateRangeStart DateRangeEnd Value
1  1     1         1/1/90       3/1/90   4.4
2  2     1         1/2/90       3/2/90   6.1
3  1     2         4/5/91       6/7/91   6.2
4  2     2         4/6/91       6/8/91   3.2
5  1     3         5/5/95       6/6/96   3.3
6  2     3         5/5/97       6/6/98   1.3

2) with the tidyverse:

split.default(d[-1], cumsum(grepl('Start$', names(d)[-1]))) %>%
  map_dfr(~set_names(., c('DateRangeStart','DateRangeEnd','Value'))) %>% 
  bind_cols(ID = rep(d$ID, nrow(.)/nrow(d)),
            group = rep(1:(nrow(.)/nrow(d)), each = nrow(d)), .)

3) with the sjmisc-package:

library(sjmisc)
to_long(d, keys = 'group', recode.key = TRUE,
        values = c('DateRangeStart','DateRangeEnd','Value'), 
        c('DateRange1Start','DateRange2Start','DateRange3Start'),
        c('DateRange1End','DateRange2End','DateRange3End'),
        c('Value1','Value2','Value3'))

---

Used data:

d <- read.table(text = "ID DateRange1Start DateRange1End Value1 DateRange2Start DateRange2End Value2 DateRange3Start DateRange3End Value3
1 1/1/90 3/1/90 4.4 4/5/91 6/7/91 6.2 5/5/95 6/6/96 3.3
2 1/2/90 3/2/90 6.1 4/6/91 6/8/91 3.2 5/5/97 6/6/98 1.3", header = TRUE, stringsAsFactors = FALSE)

Answer 4

data.table's melt function can melt into multiple columns. Using that, we can simply do:

require(data.table)
melt(setDT(dat), id=1L,
     measure=patterns("Start$", "End$", "^Value"), 
     value.name=c("DateRangeStart", "DateRangeEnd", "Value"))

#    ID variable DateRangeStart DateRangeEnd Value
# 1:  1        1         1/1/90       3/1/90   4.4
# 2:  1        2         4/5/91       6/7/91   6.2
# 3:  1        3         5/5/95       6/6/96   3.3

---

Alternatively, you can also reference the three sets of measure columns by the column position:

melt(setDT(dat), id = 1L, 
     measure = list(c(2,5,8), c(3,6,9), c(4,7,10)), 
     value.name = c("DateRangeStart", "DateRangeEnd", "Value"))

Answer 5

Here is an approach to the problem using tidyr. This is an interesting use case for its function extract_numeric(), which I used to pull out the group from the column names

library(dplyr)
library(tidyr)

a <- read.table(textConnection("
ID DateRange1Start DateRange1End Value1 DateRange2Start DateRange2End Value2 DateRange3Start DateRange3End Value3
1 1/1/90 3/1/90 4.4 4/5/91 6/7/91 6.2 5/5/95 6/6/96 3.3 
"),header=TRUE)

a %>%
  gather(variable,value,-ID) %>%
  mutate(group = extract_numeric(variable)) %>%
  mutate(variable =  gsub("\\d","",x = variable)) %>%
  spread(variable,value)

  ID group DateRangeEnd DateRangeStart Value
1  1     1       3/1/90         1/1/90   4.4
2  1     2       6/7/91         4/5/91   6.2
3  1     3       6/6/96         5/5/95   3.3

Answer 6

reshape(dat, idvar="ID", direction="long", 
             varying=list(Start=c(2,5,8), End=c(3,6,9), Value=c(4,7,10)),
             v.names = c("DateRangeStart", "DateRangeEnd", "Value") )
#-------------
    ID time DateRangeStart DateRangeEnd Value
1.1  1    1          1/1/90        3/1/90    4.4
1.2  1    2          4/5/91        6/7/91    6.2
1.3  1    3          5/5/95        6/6/96    3.3

(Added the v.names per Josh's suggestion.)