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

by

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.

More Related Contents:

  1. Reshape three column data frame to matrix (“long” to “wide” format) [duplicate]
  2. Fastest way to add rows for missing time steps?
  3. Change value of variable with dplyr
  4. Why is plyr so slow?
  5. Split Data Frame into Rows of Fixed Size
  6. R: Split unbalanced list in data.frame column
  7. Dynamically select data frame columns using $ and a character value
  8. Simultaneously merge multiple data.frames in a list
  9. Drop unused factor levels in a subsetted data frame
  10. Dictionary style replace multiple items
  11. Opposite of %in%: exclude rows with values specified in a vector
  12. Replace all occurrences of a string in a data frame
  13. Convert Named Character Vector to data.frame
  14. How to add a cumulative column to an R dataframe using dplyr?
  15. Get last row of each group in R [duplicate]
  16. Create empty data frame with column names by assigning a string vector? [duplicate]
  17. Add extra level to factors in dataframe
  18. What is about the first column in R’s dataset mtcars?
  19. Determine the data types of a data frame’s columns
  20. finding close match from data frame 1 in data fame 2
  21. Combine several data frames in the global environment by row (rbind)
  22. Changing Million/Billion abbreviations into actual numbers? ie. 5.12M -> 5,120,000 [duplicate]
  23. Extract text after “/” in a data frame column
  24. What is difference between dataframe and list in R?
  25. use multiple columns as variables with sapply
  26. Merge overlapping ranges into unique groups, in dataframe
  27. Deleting specific rows from a data frame
  28. Count number of non-NA values for every column in a dataframe [duplicate]
  29. R how can I calculate difference between rows in a data frame
  30. Color one point and add an annotation in ggplot2?

Leave a Comment