使用多个测量列将数据从长格式转换为宽格式 [英] Convert data from long format to wide format with multiple measure columns

问题描述

当我有不止一个测量变量时，我无法找出最优雅灵活的方式将数据从长格式切换为宽格式。例如，这是一个长格式的简单数据框。 ID是主题，TIME是时间变量，X和Y是在TIME进行的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 XY 
 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的列标题，我知道我可以使用reshape包中的cast（或从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作为另一个度量变量，并且列名称反映了度量变量名称和时间值：

  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都是第一个或者如果它们被交错为X_1，Y_1，X_2，Y_2等）。

我可以靠近这个长时间的数据两次，尽管列名需要一些工作，但如果需要添加除X和Y之外的第3或第4个变量，则需要进行调整：

  merge（
 cast（my.df， ID = TIME，value =X），
 cast（my.df，ID〜TIME，value =Y），
 by =ID，后缀= c（_ X _Y）
）
  

看起来像reshape2和/或plyr中的一些功能组合应该能够更加优雅地做到这一点，我的尝试，以及更加干净地处理多个度量变量。像cast（my.df，ID〜TIME，value = c（X，Y）），这是无效的。但是我一直没能想出来。

任何R向导可以帮助我吗？谢谢。

解决方案

为了处理多个变量，您需要 melt 您投入之前的数据。

 库（reshape2）
 
 dcast（melt（my.df，id.vars = c（ID，TIME）），ID〜变量+ 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 
  





---

根据评论编辑：

数据框

  num.id = 10 
 num.time = 10 
我的.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 为变量的每个可能组合假定单个值，并将应用汇总函数创建单个变量，因为有多个条目。这就是为什么有警告

 缺少的汇总功能：默认为
  / pre> 
 
 
如果您添加了另一个打破冗余的变量，您可以获得一些有用的功能。
  my.df $ cycle<  -  rep（1：2，each = num.id * num.time）
 dcast（melt（my.df，id.vars = c（循环，ID，TIME）），循环+ ID〜变量+ TIME）
  
 
 
 <这是因为循环 /  ID  /  time 现在唯一定义 my.df 中的一行。
 
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:
> 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):
> 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:
  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.)

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

Can any R-wizards help me out?  Thanks.
解决方案
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
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.

                        
这篇关于使用多个测量列将数据从长格式转换为宽格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！