I am having trouble figuring out the most elegant and flexible way to switch data from long format to wide format when I have more than one measure variable I want to bring along.
当我有多个度量变量时，我很难找到最优雅和灵活的方法来将数据从长格式转换为宽格式。
For example, here's a simple data frame in long format. ID is the subject, TIME is a time variable, and X and Y are measurements made of ID at TIME:
例如，这里有一个长格式的简单数据帧。ID是主语，时间是时间变量，X和Y是对ID在时间上的测量:
> my.df <- data.frame(ID=rep(c("A","B","C"), 5), TIME=rep(1:5, each=3), X=1:15, Y=16:30)
> my.df
ID TIME X Y
1 A 1 1 16
2 B 1 2 17
3 C 1 3 18
4 A 2 4 19
5 B 2 5 20
6 C 2 6 21
7 A 3 7 22
8 B 3 8 23
9 C 3 9 24
10 A 4 10 25
11 B 4 11 26
12 C 4 12 27
13 A 5 13 28
14 B 5 14 29
15 C 5 15 30
If I just wanted to turn the values of TIME into column headers containing the include X, I know I can use cast from the reshape package (or dcast from reshape2):
如果我只是想将时间值转换为包含include X的列标题，我知道我可以使用reshape2中的cast:
> cast(my.df, ID ~ TIME, value="X")
ID 1 2 3 4 5
1 A 1 4 7 10 13
2 B 2 5 8 11 14
3 C 3 6 9 12 15
But what I really want to do is also bring along Y as another measure variable, and have the column names reflect both the measure variable name and the time value:
但我真正想做的是把Y作为另一个度量变量，让列名同时反映度量变量名和时间值:
ID X_1 X_2 X_3 X_4 X_5 Y_1 Y_2 Y_3 Y_4 Y_5
1 A 1 4 7 10 13 16 19 22 25 28
2 B 2 5 8 11 14 17 20 23 26 29
3 C 3 6 9 12 15 18 21 24 27 30
(FWIW, I don't really care if all the X's are first followed by the Y's, or if they are interleaved as X_1, Y_1, X_2, Y_2, etc.)
(FWIW，我不在乎所有的X都是Y的后面，或者它们是交叉的X_1、Y_1、X_2、Y_2等等)
I can get close to this by cast-ing the long data twice and merging the results, though the column names need some work, and I would need to tweak it if I needed to add a 3rd or 4th variable in addition to X and Y:
我可以通过两次插入长数据并合并结果来接近这一点，尽管列名需要做一些工作，如果需要在X和Y之外添加第三或第四个变量，我需要对其进行调整:
merge(
cast(my.df, ID ~ TIME, value="X"),
cast(my.df, ID ~ TIME, value="Y"),
by="ID", suffixes=c("_X","_Y")
)
Seems like some combination of functions in reshape2 and/or plyr should be able to do this more elegantly that my attempt, as well as handling multiple measure variables more cleanly. Something like cast(my.df, ID ~ TIME, value=c("X","Y")), which isn't valid. But I haven't been able to figure it out.
似乎reshape2和/或plyr中的一些功能组合应该能够更优雅地完成这一操作，同时更干净地处理多个度量变量。像(我。df, ID ~ TIME, value=c(“X”，“Y”)，这是无效的。但我还没弄明白。
Can any R-wizards help me out? Thanks.
有r -wizard可以帮助我吗?谢谢。
4 个解决方案
#1
14
In order to handle multiple variables like you want, you need to melt the data you have before casting it.
为了像您希望的那样处理多个变量，您需要在转换数据之前对数据进行熔融。
library("reshape2")
dcast(melt(my.df, id.vars=c("ID", "TIME")), ID~variable+TIME)
which gives
这给了
ID X_1 X_2 X_3 X_4 X_5 Y_1 Y_2 Y_3 Y_4 Y_5
1 A 1 4 7 10 13 16 19 22 25 28
2 B 2 5 8 11 14 17 20 23 26 29
3 C 3 6 9 12 15 18 21 24 27 30
EDIT based on comment:
编辑基于评论:
The data frame
的数据帧
num.id = 10
num.time=10
my.df <- data.frame(ID=rep(LETTERS[1:num.id], num.time),
TIME=rep(1:num.time, each=num.id),
X=1:(num.id*num.time),
Y=(num.id*num.time)+1:(2*length(1:(num.id*num.time))))
gives a different result (all entries are 2) because the ID/TIME combination does not indicate a unique row. In fact, there are two rows with each ID/TIME combinations. reshape2 assumes a single value for each possible combination of the variables and will apply a summary function to create a single variable is there are multiple entries. That is why there is the warning
给出一个不同的结果(所有条目都是2)，因为ID/时间组合并不表示唯一的行。实际上，每个ID/时间组合有两行。reshape2假设每个可能的变量组合都有一个值，如果有多个条目，它会应用一个摘要函数来创建一个单一的变量。这就是为什么会有这样的警告
Aggregation function missing: defaulting to length
You can get something that works if you add another variable which breaks that redundancy.
如果你添加了另一个破坏冗余的变量，你就可以得到有用的东西。
my.df$cycle <- rep(1:2, each=num.id*num.time)
dcast(melt(my.df, id.vars=c("cycle", "ID", "TIME")), cycle+ID~variable+TIME)
This works because cycle/ID/time now uniquely defines a row in my.df.
这是因为循环/ID/时间现在惟一地定义了my.df中的一行。
#2
15
reshape(my.df,
idvar = "ID",
timevar = "TIME",
direction = "wide")
gives
给了
ID X.1 Y.1 X.2 Y.2 X.3 Y.3 X.4 Y.4 X.5 Y.5
1 A 1 16 4 19 7 22 10 25 13 28
2 B 2 17 5 20 8 23 11 26 14 29
3 C 3 18 6 21 9 24 12 27 15 30
#3
11
Using the data.table_1.9.5, this can be done without the melt as it can handle multiple value.var columns. You can install it from here
使用data.table_1.9.5，这可以在没有融化的情况下完成，因为它可以处理多个值。var列。你可以从这里安装
library(data.table)
dcast(setDT(my.df), ID~TIME, value.var=c('X', 'Y'))
# ID 1_X 2_X 3_X 4_X 5_X 1_Y 2_Y 3_Y 4_Y 5_Y
#1: A 1 4 7 10 13 16 19 22 25 28
#2: B 2 5 8 11 14 17 20 23 26 29
#3: C 3 6 9 12 15 18 21 24 27 30
#4
5
Here's a solution with the tidyr package, which has essentially replaced reshape and reshape2. As with those two packages, the strategy it to make the dataset longer first, and then wider.
这是tidyr包的一个解决方案，它实质上已经取代了“重塑”和“重塑”2。和这两个包一样，它的策略是使数据集更早，然后更宽。
library(magrittr); requireNamespace("tidyr"); requireNamespace("dplyr")
my.df %>%
tidyr::gather_(key="variable", value="value", c("X", "Y")) %>% # Make it even longer.
dplyr::mutate( # Create the spread key.
time_by_variable = paste0(variable, "_", TIME)
) %>%
dplyr::select(ID, time_by_variable, value) %>% # Retain these three.
tidyr::spread(key=time_by_variable, value=value) # Spread/widen.
After the tidyr::gather() call, the intermediate dataset is:
在tidyr::gather()调用后，中间数据集为:
ID TIME variable value
1 A 1 X 1
2 B 1 X 2
3 C 1 X 3
...
28 A 5 Y 28
29 B 5 Y 29
30 C 5 Y 30
The eventual result is:
最终的结果是:
ID X_1 X_2 X_3 X_4 X_5 Y_1 Y_2 Y_3 Y_4 Y_5
1 A 1 4 7 10 13 16 19 22 25 28
2 B 2 5 8 11 14 17 20 23 26 29
3 C 3 6 9 12 15 18 21 24 27 30
tidyr::unite() is an alternative, suggested by @JWilliman. This is functionally equivalent to the dplyr::mutate() and dplyr::select() combination above, when the remove parameter is true (which is the default).
unite()是另一种选择，由@JWilliman建议。这在功能上等同于上面的dplyr::mutate()和dplyr:::select()组合，当remove参数为true(默认)时。
If you're not accustomed to this type of manipulation, the tidyr::unite() may be a small obstacle because it's one more function you have to learn & remember. However, it's benefits include (a) more concise code (ie, four lines are replaced by one) and (b) fewer places to repeat variable names (ie, you don't have to repeat/modify variables in the dplyr::select() clause).
如果您不习惯这种操作，那么tidyr::unite()可能是一个小障碍，因为它是您必须学习和记住的另一个功能。但是，它的好处包括(a)更简洁的代码(即，四行被一行替换为一行)和(b)重复变量名的地方更少(即，不必在dplyr:::select()子句中重复/修改变量)。
my.df %>%
tidyr::gather_(key="variable", value="value", c("X", "Y")) %>% # Make it even longer.
tidyr::unite("time_by_variable", variable, TIME, remove=T) %>% # Create the spread key `time_by_variable` while simultaneously dropping `variable` and `TIME`.
tidyr::spread(key=time_by_variable, value=value) # Spread/widen.
#1
14
In order to handle multiple variables like you want, you need to melt the data you have before casting it.
为了像您希望的那样处理多个变量，您需要在转换数据之前对数据进行熔融。
library("reshape2")
dcast(melt(my.df, id.vars=c("ID", "TIME")), ID~variable+TIME)
which gives
这给了
ID X_1 X_2 X_3 X_4 X_5 Y_1 Y_2 Y_3 Y_4 Y_5
1 A 1 4 7 10 13 16 19 22 25 28
2 B 2 5 8 11 14 17 20 23 26 29
3 C 3 6 9 12 15 18 21 24 27 30
EDIT based on comment:
编辑基于评论:
The data frame
的数据帧
num.id = 10
num.time=10
my.df <- data.frame(ID=rep(LETTERS[1:num.id], num.time),
TIME=rep(1:num.time, each=num.id),
X=1:(num.id*num.time),
Y=(num.id*num.time)+1:(2*length(1:(num.id*num.time))))
gives a different result (all entries are 2) because the ID/TIME combination does not indicate a unique row. In fact, there are two rows with each ID/TIME combinations. reshape2 assumes a single value for each possible combination of the variables and will apply a summary function to create a single variable is there are multiple entries. That is why there is the warning
给出一个不同的结果(所有条目都是2)，因为ID/时间组合并不表示唯一的行。实际上，每个ID/时间组合有两行。reshape2假设每个可能的变量组合都有一个值，如果有多个条目，它会应用一个摘要函数来创建一个单一的变量。这就是为什么会有这样的警告
Aggregation function missing: defaulting to length
You can get something that works if you add another variable which breaks that redundancy.
如果你添加了另一个破坏冗余的变量，你就可以得到有用的东西。
my.df$cycle <- rep(1:2, each=num.id*num.time)
dcast(melt(my.df, id.vars=c("cycle", "ID", "TIME")), cycle+ID~variable+TIME)
This works because cycle/ID/time now uniquely defines a row in my.df.
这是因为循环/ID/时间现在惟一地定义了my.df中的一行。
#2
15
reshape(my.df,
idvar = "ID",
timevar = "TIME",
direction = "wide")
gives
给了
ID X.1 Y.1 X.2 Y.2 X.3 Y.3 X.4 Y.4 X.5 Y.5
1 A 1 16 4 19 7 22 10 25 13 28
2 B 2 17 5 20 8 23 11 26 14 29
3 C 3 18 6 21 9 24 12 27 15 30
#3
11
Using the data.table_1.9.5, this can be done without the melt as it can handle multiple value.var columns. You can install it from here
使用data.table_1.9.5，这可以在没有融化的情况下完成，因为它可以处理多个值。var列。你可以从这里安装
library(data.table)
dcast(setDT(my.df), ID~TIME, value.var=c('X', 'Y'))
# ID 1_X 2_X 3_X 4_X 5_X 1_Y 2_Y 3_Y 4_Y 5_Y
#1: A 1 4 7 10 13 16 19 22 25 28
#2: B 2 5 8 11 14 17 20 23 26 29
#3: C 3 6 9 12 15 18 21 24 27 30
#4
5
Here's a solution with the tidyr package, which has essentially replaced reshape and reshape2. As with those two packages, the strategy it to make the dataset longer first, and then wider.
这是tidyr包的一个解决方案，它实质上已经取代了“重塑”和“重塑”2。和这两个包一样，它的策略是使数据集更早，然后更宽。
library(magrittr); requireNamespace("tidyr"); requireNamespace("dplyr")
my.df %>%
tidyr::gather_(key="variable", value="value", c("X", "Y")) %>% # Make it even longer.
dplyr::mutate( # Create the spread key.
time_by_variable = paste0(variable, "_", TIME)
) %>%
dplyr::select(ID, time_by_variable, value) %>% # Retain these three.
tidyr::spread(key=time_by_variable, value=value) # Spread/widen.
After the tidyr::gather() call, the intermediate dataset is:
在tidyr::gather()调用后，中间数据集为:
ID TIME variable value
1 A 1 X 1
2 B 1 X 2
3 C 1 X 3
...
28 A 5 Y 28
29 B 5 Y 29
30 C 5 Y 30
The eventual result is:
最终的结果是:
ID X_1 X_2 X_3 X_4 X_5 Y_1 Y_2 Y_3 Y_4 Y_5
1 A 1 4 7 10 13 16 19 22 25 28
2 B 2 5 8 11 14 17 20 23 26 29
3 C 3 6 9 12 15 18 21 24 27 30
tidyr::unite() is an alternative, suggested by @JWilliman. This is functionally equivalent to the dplyr::mutate() and dplyr::select() combination above, when the remove parameter is true (which is the default).
unite()是另一种选择，由@JWilliman建议。这在功能上等同于上面的dplyr::mutate()和dplyr:::select()组合，当remove参数为true(默认)时。
If you're not accustomed to this type of manipulation, the tidyr::unite() may be a small obstacle because it's one more function you have to learn & remember. However, it's benefits include (a) more concise code (ie, four lines are replaced by one) and (b) fewer places to repeat variable names (ie, you don't have to repeat/modify variables in the dplyr::select() clause).
如果您不习惯这种操作，那么tidyr::unite()可能是一个小障碍，因为它是您必须学习和记住的另一个功能。但是，它的好处包括(a)更简洁的代码(即，四行被一行替换为一行)和(b)重复变量名的地方更少(即，不必在dplyr:::select()子句中重复/修改变量)。
my.df %>%
tidyr::gather_(key="variable", value="value", c("X", "Y")) %>% # Make it even longer.
tidyr::unite("time_by_variable", variable, TIME, remove=T) %>% # Create the spread key `time_by_variable` while simultaneously dropping `variable` and `TIME`.
tidyr::spread(key=time_by_variable, value=value) # Spread/widen.