r - R 熔化重塑数据

Question

这是我的数据：

Day        Morning_1_id     Var1        Morning_2_id     Var2      Afternoon_1_id     Var3      Afternoon_2_id     Var4
1     20180501-033-000001 3.156667 20180501-033-000002 2.866667 20180501-033-000008 2.946667 20180501-033-000009 3.133333
2     20180502-033-000001 2.986667 20180502-033-000002 2.930000 20180502-033-000020 3.076667 20180502-033-000021 3.013333
3     20180503-033-000001 3.073333 20180503-033-000002 3.070000 20180503-033-000011 3.106667 20180503-033-000012 2.900000
4     20180507-033-000001 3.236667 20180507-033-000002 2.990000 20180507-033-000015 3.043333 20180507-033-000016 3.116667
5     20180508-033-000001 3.030000 20180508-033-000002 3.150000 20180508-033-000015 3.156667 20180508-033-000017 3.343333
6     20180509-033-000001 3.010000 20180509-033-000002 3.020000 20180509-033-000007 3.000000 20180509-033-000008 3.156667
7     20180510-033-000001 2.916667 20180510-033-000002 3.103333 20180510-033-000007 3.336667 20180510-033-000008 3.066667
8     20180511-033-000001 3.293333 20180511-033-000002 3.163333 20180511-033-000013 2.980000 20180511-033-000014 2.940000
9     20180514-033-000001 3.136667 20180514-033-000002 3.186667 20180514-033-000007 2.766667 20180514-033-000008 3.100000
10    20180516-033-000001 3.116667 20180516-033-000002 3.283333 20180516-033-000008 3.133333 20180516-033-000009 3.040000
11    20180517-033-000003 2.843333 20180517-033-000004 3.120000 20180517-033-000008 3.060000 20180517-033-000009 3.033333
12    20180518-033-000001 3.033333 20180518-033-000002 3.290000 20180518-033-000007 3.006667 20180518-033-000008 2.973333
13    20180521-033-000002 3.173333 20180521-033-000003 2.993333 20180521-033-000008 2.983333 20180521-033-000009 3.020000
14    20180523-033-000001 3.336667 20180523-033-000002 3.026667 20180523-033-000007 3.300000 20180523-033-000008 3.210000

可复制形式：

structure(list(Day = 1:14, Morning_1_id = structure(1:14, .Label = c("20180501-033-000001", 
"20180502-033-000001", "20180503-033-000001", "20180507-033-000001", 
"20180508-033-000001", "20180509-033-000001", "20180510-033-000001", 
"20180511-033-000001", "20180514-033-000001", "20180516-033-000001", 
"20180517-033-000003", "20180518-033-000001", "20180521-033-000002", 
"20180523-033-000001"), class = "factor"), Var1 = c(3.156666667, 
2.986666667, 3.073333333, 3.236666667, 3.03, 3.01, 2.916666667, 
3.293333333, 3.136666667, 3.116666667, 2.843333333, 3.033333333, 
3.173333333, 3.336666667), Morning_2_id = structure(1:14, .Label = c("20180501-033-000002", 
"20180502-033-000002", "20180503-033-000002", "20180507-033-000002", 
"20180508-033-000002", "20180509-033-000002", "20180510-033-000002", 
"20180511-033-000002", "20180514-033-000002", "20180516-033-000002", 
"20180517-033-000004", "20180518-033-000002", "20180521-033-000003", 
"20180523-033-000002"), class = "factor"), Var2 = c(2.866666667, 
2.93, 3.07, 2.99, 3.15, 3.02, 3.103333333, 3.163333333, 3.186666667, 
3.283333333, 3.12, 3.29, 2.993333333, 3.026666667), Afternoon_1_id = structure(1:14, .Label = c("20180501-033-000008", 
"20180502-033-000020", "20180503-033-000011", "20180507-033-000015", 
"20180508-033-000015", "20180509-033-000007", "20180510-033-000007", 
"20180511-033-000013", "20180514-033-000007", "20180516-033-000008", 
"20180517-033-000008", "20180518-033-000007", "20180521-033-000008", 
"20180523-033-000007"), class = "factor"), Var3 = c(2.946666667, 
3.076666667, 3.106666667, 3.043333333, 3.156666667, 3, 3.336666667, 
2.98, 2.766666667, 3.133333333, 3.06, 3.006666667, 2.983333333, 
3.3), Afternoon_2_id = structure(1:14, .Label = c("20180501-033-000009", 
"20180502-033-000021", "20180503-033-000012", "20180507-033-000016", 
"20180508-033-000017", "20180509-033-000008", "20180510-033-000008", 
"20180511-033-000014", "20180514-033-000008", "20180516-033-000009", 
"20180517-033-000009", "20180518-033-000008", "20180521-033-000009", 
"20180523-033-000008"), class = "factor"), Var4 = c(3.133333333, 
3.013333333, 2.9, 3.116666667, 3.343333333, 3.156666667, 3.066666667, 
2.94, 3.1, 3.04, 3.033333333, 2.973333333, 3.02, 3.21)), class = "data.frame", row.names = c(NA, 
-14L))

这就是我想要的：

Day Id                  Var         Time
1   20180501-033-000001 3.156666667 Morning1
2   20180502-033-000001 2.986666667 Morning1
3   20180503-033-000001 3.073333333 Morning1
4   20180507-033-000001 3.236666667 Morning1
5   20180508-033-000001 3.03        Morning1
6   20180509-033-000001 3.01        Morning1
7   20180510-033-000001 2.916666667 Morning1
8   20180511-033-000001 3.293333333 Morning1
9   20180514-033-000001 3.136666667 Morning1
10  20180516-033-000001 3.116666667 Morning1
11  20180517-033-000003 2.843333333 Morning1
12  20180518-033-000001 3.033333333 Morning1
13  20180521-033-000002 3.173333333 Morning1
14  20180523-033-000001 3.336666667 Morning1
1   20180501-033-000002 2.866666667 Morning2
2   20180502-033-000002 2.93        Morning2
3   20180503-033-000002 3.07        Morning2
4   20180507-033-000002 2.99        Morning2
5   20180508-033-000002 3.15        Morning2
6   20180509-033-000002 3.02        Morning2
7   20180510-033-000002 3.103333333 Morning2
8   20180511-033-000002 3.163333333 Morning2
9   20180514-033-000002 3.186666667 Morning2
10  20180516-033-000002 3.283333333 Morning2
11  20180517-033-000004 3.12        Morning2
12  20180518-033-000002 3.29        Morning2
13  20180521-033-000003 2.993333333 Morning2
14  20180523-033-000002 3.026666667 Morning2
1   20180501-033-000008 2.946666667 Afternoon1
2   20180502-033-000020 3.076666667 Afternoon1
3   20180503-033-000011 3.106666667 Afternoon1
4   20180507-033-000015 3.043333333 Afternoon1
5   20180508-033-000015 3.156666667 Afternoon1
6   20180509-033-000007 3           Afternoon1
7   20180510-033-000007 3.336666667 Afternoon1
8   20180511-033-000013 2.98        Afternoon1
9   20180514-033-000007 2.766666667 Afternoon1
10  20180516-033-000008 3.133333333 Afternoon1
11  20180517-033-000008 3.06        Afternoon1
12  20180518-033-000007 3.006666667 Afternoon1
13  20180521-033-000008 2.983333333 Afternoon1
14  20180523-033-000007 3.3         Afternoon1
1   20180501-033-000009 3.133333333 Afternoon2
2   20180502-033-000021 3.013333333 Afternoon2
3   20180503-033-000012 2.9         Afternoon2
4   20180507-033-000016 3.116666667 Afternoon2
5   20180508-033-000017 3.343333333 Afternoon2
6   20180509-033-000008 3.156666667 Afternoon2
7   20180510-033-000008 3.066666667 Afternoon2
8   20180511-033-000014 2.94        Afternoon2
9   20180514-033-000008 3.1         Afternoon2
10  20180516-033-000009 3.04        Afternoon2
11  20180517-033-000009 3.033333333 Afternoon2
12  20180518-033-000008 2.973333333 Afternoon2
13  20180521-033-000009 3.02        Afternoon2
14  20180523-033-000008 3.21        Afternoon2

我想做从宽到长的转换，这样 Id 和 'Var' 的值每天都会堆积起来。我还想要一个名为“时间”的附加列，它取决于初始 ID，即“Morning_1_id”、“Morning_2_id”、“Afternoon_1_id”和“Afternoon_2_id”。这该怎么做？我尝试使用 reshape2 中的 melt 但无法完成。

标签： rreshapereshape2

Answer 1

这是一个dplyr用于将表格转换为请求格式的解决方案：

library(dplyr)

mydata<- reshape(mydata, direction='long', 
                varying=c('Morning_1_id', 'Var1', 'Morning_2_id', 'Var2', 'Afternoon_1_id', 'Var3', 'Afternoon_2_id', 'Var4'), 
                timevar='Var',
                times=c('Morning1', 'Morning2', 'Afternoon1', 'Afternoon2'),
                v.names=c('Id', 'Var'),
                idvar='Day')

mydata<- tibble::rownames_to_column(mydata)
mydata$rowname<- gsub("^.*\\.","", mydata$rowname)
names(mydata)<- c("Time", "Day", "Var", "Id")
mydata<- mydata[,c(2,4,3,1)]