How to reformat an R data frame with multiple rows into one row

Question

I have data frames like the following that I need to reformat into a single row, so that I can create a new data frame that's a collection of many of the simpler data frames, with one row in the new data frame representing all of the data of one of the simpler original data frames.

Here's a trivial example of the format of the original data frames:

> myDf = data.frame(Seconds=seq(0,1,.25), s1=seq(0,8,2), s2=seq(1,9,2))
> 
> myDf
  Seconds s1 s2
1    0.00  0  1
2    0.25  2  3
3    0.50  4  5
4    0.75  6  7
5    1.00  8  9

And below is what I want it to look like after being reformatted. Each column indicates rXsY, where "rX" indicates the row number of the original data frame, and "sY" indicates the "s1" or "s2" column of the original data frame. The "Seconds" column is omitted in the new data frame, as its information is implicit in the row number.

> myNewDf
  r1s1 r1s2 r2s1 r2s2 r3s1 r3s2 r4s1 r4s2 r5s1 r5s2
1    0    1    2    3    4    5    6    7    8    9

I suspect this is really simple and probably involves some combination of reshape(), melt(), and/or cast(), but the proper incantations are escaping me. I could post what I've tried, but I think it would just distract from what's probably a simple question? If anyone would like me to do so, just ask in the comments.

The ideal solution would also somehow programmatically generate the new column names based on the original data frame's column names, since the column names won't always be the same. Also, if it's not difficult, can I somehow simultaneously do this same operation to a list of similar data frames (all the same number of rows, all the same column names, but with differing values in their s1 & s2 columns)? Ultimately I need a single data frame that contains the data from multiple simpler data frames, like this...

> myCombinedNewDf # data combined from 4 separate original data frames
  r1s1 r1s2 r2s1 r2s2 r3s1 r3s2 r4s1 r4s2 r5s1 r5s2
1    0    1    2    3    4    5    6    7    8    9
2   10   11   12   13   14   15   16   17   18   19
3   20   21   22   23   24   25   26   27   28   29
4   30   31   32   33   34   35   36   37   38   39

Answer 1

Using melt() from reshape2, you can do it like this:

library(reshape2)

# Melt the data, omitting `Seconds`
df.melted <- melt(myDF[, -1], id.vars = NULL)

# Transpose the values into a single row
myNewDF <- t(df.melted[, 2])

# Assign new variable names
colnames(myNewDF) <- paste0("r", rownames(myDF), df.melted[, 1])

#   r1s1 r2s1 r3s1 r4s1 r5s1 r1s2 r2s2 r3s2 r4s2 r5s2
# 1    0    2    4    6    8    1    3    5    7    9

This melts the data frame, uses the first column (the variable names from the original dataset) to construct the variable names for the new dataset, and uses the transpose of the second column (the data values) as the row of data.

If you want an automated approach to combining your datasets, you can take this a step further:

# Another data frame
myOtherDF <- data.frame(Seconds = seq(0, 1, 0.25),
                        s1 = seq(1, 9, 2),
                        s2 = seq(0, 8, 2))

# Turn the above steps into a function
colToRow <- function(x) {
    melted <- melt(x[, -1], id.vars = NULL)
    row <- t(melted[, 2])
    colnames(row) <- paste0("r", rownames(x), melted[, 1])
    row
}

# Create a list of the data frames to process
myDFList <- list(myDF, myOtherDF)

# Apply our function to each data frame in the list and append
myNewDF <- data.frame(do.call(rbind, lapply(myDFList, colToRow)))

#   r1s1 r2s1 r3s1 r4s1 r5s1 r1s2 r2s2 r3s2 r4s2 r5s2
# 1    0    2    4    6    8    1    3    5    7    9
# 2    1    3    5    7    9    0    2    4    6    8