Convert data from long format to wide format with multiple measure columns

Question

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.

Shape

Answer 1

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.

Historiography

I was trying to assess which solution is fastest, but found a problem with your code if the data frame is as follow: 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.idnum.time), Y=(num.idnum.time)+1:(2*length(1:(num.id*num.time)))) – Involutional

Perfect, thanks Brian. Since cast seemed to be working, I didn't realize melt was still necessary. – Shape

Answer 2

This answer is pretty old, maybe find some newer info.

   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

Hotshot

Answer 3

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

 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

Circumscribe

Thank you for the above solution. When I run this code, I get the following warning message: any advice on it, please? Aggregate function missing, defaulting to 'length' @Circumscribe This relates to one of my questions in the post: <#67830782> – Unwary

@Unwary taht is because you have duplicate elements, so dcast default to use length. Perhaps you want dcast(setDT(my.df), ID + rowid(ID) ~ TIME, value.var = c("X", "Y")) – Circumscribe

Answer 4

The pivot_wider() function is tidyr's 2nd generation approach (released in tidyr 1.0.0).

library(magrittr); requireNamespace("tidyr");

my.df %>%
  tidyr::pivot_wider(
    names_from  = c(TIME), # Can accommodate more variables, if needed.
    values_from = c(X, Y)
  )

Result:

# A tibble: 3 x 11
  ID      X_1   X_2   X_3   X_4   X_5   Y_1   Y_2   Y_3   Y_4   Y_5
  <fct> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
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

This is probably preferable to the previous tidyr approach (that uses a combination of gather() and spread()).

More capabilities are described in the pivoting vignette. This example is particularly concise because your desired specifications match the defaults of the id_cols and names_sep.

Annalisaannalise

Surprised this one isn't upvoted more. If you are dealing with a really large data frame and/or a lot of columns this is the only one that will work. All of the other solutions run out of memory. Not quite sure what the threshold is but I was processing a data frame with 6M records and 24 columns. – Lund

Answer 5

Note -Sept 2019: within tidyr, the gather()+spread() approach (described in this answer) has more or less been replaced by the pivot_wider() approach (described in `this newer tidyr answer). For current info about the transition, see the pivoting vignette.

---

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.

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:

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

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

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.

Annalisaannalise

The line dplyr::mutate(time_by_variable = paste0(variable, "_", TIME)) can be replaced with tidyr::unite("time_by_variable", variable, TIME). – Ofeliaofella

I agree, @JWilliman. I thought the explicit mutate() and paste0() calls made the intention clearer, and avoided introducing a new function. But I am seeing tidyr::unite() used more in the past year. I'll append the response to reflect your advice. – Annalisaannalise

Answer 6

It's a bit late, but the following solution may also work.

# Input
my.df <- data.frame(ID=rep(c("A","B","C"), 5), TIME=rep(1:5, each=3), X=1:15, Y=16:30)

# Conversion into wide format
my.df %>%
  pivot_wider(ID, c(TIME), 
              values_from = c(X, Y),
              names_glue = '{.value}_{TIME}')

1- With this solution, you can adjust the column names by setting them in the names_glue option either as

names_glue = '{.value}_{TIME}' or
names_glue = '{TIME}_{.value}'

2- Similarly, with this solution, you can join two or more fields e.g., c(TIME, DAY, MONTH) and then correspondingly update the names_glue option:

names_glue = '{TIME}_{DAY}_{MONTH}_{.value}'

Unwary