用时间表示法重新塑造数据

Question

我有一个数据集，其中时间被表示为连续的时段（即从时间1到时间2），就像这样：

d <- data.frame(id = c("A","A","B","B","C","C"),
                t1 = c(1,3,1,3,1,3),
                t2 = c(2,4,2,4,2,4),
                value = 1:6)

我希望把这个数据重构成面板数据集，即每个单位和时间段都对应一行，如下所示：

result <- data.frame(id = c("A","A","A","A","B","B","B","B","C","C","C","C"),
                     t= c(1:4,1:4,1:4),
                     value = c(1,1,2,2,3,3,4,4,5,5,6,6))

我试图使用 tidyr 和 gather 完成这个任务，但未能获得期望的结果。我尝试了以下代码，但显然是错误的：

gather(d, 't1', 't2', key=t)

在实际数据集中，拼写不规则。

- tvg

---

我添加了一些进一步的代码注释，以更好地解释gather()、select()和arrange()函数。 - Roman

Answer 1

因此，目标是融合t1和t2列，并删除作为结果出现的key列。有几个选项。基本R的reshape似乎很繁琐。但我们可以使用melt：

library(reshape2)
melt(d, measure.vars = c("t1", "t2"), value.name = "t")[-3]
#    id value t
# 1   A     1 1
# 2   A     2 3
# 3   B     3 1
# 4   B     4 3
# 5   C     5 1
# 6   C     6 3
# 7   A     1 2
# 8   A     2 4
# 9   B     3 2
# 10  B     4 4
# 11  C     5 2
# 12  C     6 4

在编程中，当我们需要删除“key”列时，可以使用以下代码：-3。我们也可以使用gather方法。

gather(d, "key", "t", t1, t2)[-3]
#    id value t
# 1   A     1 1
# 2   A     2 3
# 3   B     3 1
# 4   B     4 3
# 5   C     5 1
# 6   C     6 3
# 7   A     1 2
# 8   A     2 4
# 9   B     3 2
# 10  B     4 4
# 11  C     5 2
# 12  C     6 4

- Julius Vainora

Answer 2

你差一点就到了。

代码

d %>%
    # Gather the needed variables. Explanation:
    # t_type: How will the call the column where we will put the former
    #         variable names under?
    # t:      How will we call the column where we will put the
    #         values of above variables?
    # -id,
    # -value: Which columns should stay the same and NOT be gathered
    #         under t_type (key) and t (value)?
    # 
    gather(t_type, t, -id, -value) %>%
    # Select the right columns in the right order. 
    # Watch out: We did not select t_type, so it gets dropped.
    select(id, t, value) %>%
    # Arrange / sort the data by the following columns.
    # For a descending order put a "-" in front of the column name.  
    arrange(id, t)

结果

   id t value
1   A 1     1
2   A 2     1
3   A 3     2
4   A 4     2
5   B 1     3
6   B 2     3
7   B 3     4
8   B 4     4
9   C 1     5
10  C 2     5
11  C 3     6
12  C 4     6

- Roman