当我想要携带多个度量变量时,我无法找出将数据从长格式切换到宽格式的最优雅、最灵活的方法.
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.
例如,这是一个长格式的简单数据框.ID 是主体,TIME 是时间变量,X 和 Y 是 的测量值在 TIME 的 ID:
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:
> 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如果我只想将 TIME 的值转换为包含包含 X 的列标题,我知道我可以使用 cast()来自 reshape 包(或来自 reshape2 的 dcast()):
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):
> 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但我真正想做的是将 Y 作为另一个度量变量,并让列名同时反映度量变量名称和时间值:
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:
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,我真的不在乎所有 X 是否首先跟在 Y 之后,或者它们是否作为 交错X_1、Y_1、X_2、Y_2 等)
(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.)
我可以通过 cast - 两次长数据并合并结果来接近这一点,尽管列名需要一些工作,如果我需要添加一个,我需要调整它除了 X 和 Y 之外的第三个或第四个变量:
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:
merge( cast(my.df, ID ~ TIME, value="X"), cast(my.df, ID ~ TIME, value="Y"), by="ID", suffixes=c("_X","_Y") )似乎 reshape2 和/或 plyr 中的一些函数组合应该能够比我的尝试更优雅地做到这一点,以及更干净地处理多个度量变量.类似于 cast(my.df, ID ~ TIME, value=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.
推荐答案为了像你想要的那样处理多个变量,你需要在转换之前melt你拥有的数据.
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)给出
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:
数据框
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))))给出不同的结果(所有条目均为 2),因为 ID/TIME 组合并不表示唯一的行.实际上,每个ID/TIME 组合都有两行.reshape2 假设变量的每个可能组合都有一个值,如果有多个条目,将应用汇总函数来创建单个变量.这就是为什么有警告
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
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)这是有效的,因为 cycle/ID/time 现在唯一地定义了 my.df 中的一行.
This works because cycle/ID/time now uniquely defines a row in my.df.
更多推荐
使用多个度量列将数据从长格式转换为宽格式
发布评论