Reshaping data from long to wide format in R

Question

I have a data set as shown below:

Frame  | X.axis | Y.axis | Z.axis
-------|--------|--------|--------
  1    |  0.2   |  0.215 |  0.965
-------|--------|--------|--------
  2    |  0.54  |  1.25  | 2.219
-------|--------|--------|--------
  1    |  2.124 |  2.418 |  1.35
-------|--------|--------|--------
  2    |  -1.2  |  0.49  | 1.87
-------|--------|--------|--------
  1    |  6.42  |  -1.28 |  7.1
-------|--------|--------|--------
  2    |  6.45  |  -2.5  | 8.5

I want to reshape the above table into something like as shown here:

frame1.X.axis  | frame1.Y.axis | frame1.Z.axis | frame2.X.axis  | frame2.Y.axis | frame2.Z.axis
--------|--------|--------|--------|--------|--------
  0.2   |  0.215 |  0.965 |  0.54  |  1.25  | 2.219
--------|--------|--------|--------|--------|--------
  2.124 |  2.418 |  1.35  |  -1.2  |  0.49  | 1.87
--------|--------|--------|--------|--------|--------
  6.42  |  -1.28 |  7.1   |  6.45  |  -2.5  | 8.5

How can the above task be achieved ?

Important Note:

The real data set has 16 frames instead of 2. And the columns to be spread are 90 instead of 3. So I don't want a function which requires me to mention the new column names manually. I want the column names to be automatically named by the function somehow.

I have tried using tidyr package's spread function but I couldn't do it using that. I then tried the reshape function but it also asks for the new column names.

Please see [this Q/A](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) to provide reproducible data. — Tensibai, Jan 25 '17 at 15:03

Roman · Accepted Answer · 2017-01-26T15:15:49.483

You can try:

# some data
set.seed(123)
df <- data.frame(matrix(c(rep(1:2,3), runif(18)), byrow = F,6,4))
colnames(df) <- c("Frame", "X.axis", "Y.axis", "Z.axis")
df
Frame    X.axis    Y.axis     Z.axis
1     1 0.2875775 0.5281055 0.67757064
2     2 0.7883051 0.8924190 0.57263340
3     1 0.4089769 0.5514350 0.10292468
4     2 0.8830174 0.4566147 0.89982497
5     1 0.9404673 0.9568333 0.24608773
6     2 0.0455565 0.4533342 0.04205953

library(reshape2)
# transform to long
df1 <- melt(df, measure.vars = colnames(df)[-1])
# order
df1 <- df1[order(df1$Frame), ]
# add suitable columns for transformation
# Following code adds a continuous number per "Frame" level
df1$New <- ave(as.numeric(df1$variable), interaction(df1$variable,  df1$Frame), FUN = seq_along)
# The new column name
df1$New2 <- paste0("Frame", df1$Frame, ".", df1$variable)
# long format
dcast(df1, New ~ New2, value.var = "value")
New Frame1.X.axis Frame1.Y.axis Frame1.Z.axis Frame2.X.axis Frame2.Y.axis Frame2.Z.axis
1   1     0.2875775     0.5281055     0.6775706     0.7883051     0.8924190    0.57263340
2   2     0.4089769     0.5514350     0.1029247     0.8830174     0.4566147    0.89982497
3   3     0.9404673     0.9568333     0.2460877     0.0455565     0.4533342    0.04205953

what does df$New code do here ? Please explain the whole code line. I understood all the other code. — Amandeep Rathee, Jan 25 '17 at 16:16
In this instance, a kind of simple split-apply-combine method will work as well: `do.call(cbind, split(df[-1], df$Frame))`. — lmo, Jan 25 '17 at 17:21

Reshaping data from long to wide format in R

1 Answers1