将多组测量列（宽格式）整形为单列（长格式）

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:

我有一个宽格式的数据帧，在不同的日期范围内重复测量。在我的例子中，有三个不同的时期，都有相应的值。例如。第一次测量（Value1）是在从DateRange1Start到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:

我希望将数据重新整形为长格式，以便将DateRangeXStart和DateRangeXEnd列分组。因此，原始表中的1行在新表中变为3行：

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.

我知道必须有一种方法可以用reshape2 / melt / recast / tidyr来做到这一点，但我似乎无法弄清楚如何以这种特殊的方式将多组测量变量映射到单组值列中。

6 个解决方案

#1

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.)

（根据Josh的建议添加了v.names。）

#2

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

data.table的熔化函数可以融化成多列。使用它，我们可以简单地做：

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"))

#3

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

这是使用tidyr解决问题的方法。这是函数extract_numeric（）的一个有趣的用例，我用它从列名中提取组

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

#4

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

另外两个选项（带有多行的示例数据框可以更好地显示代码的工作情况）：

1) with base R:

1）基础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:

2）与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:

3）使用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:

1）基础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:

2）与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:

3）使用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)

#5

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)]))

#6

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

你不需要任何花哨的东西;基本R函数会做。

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)