Geek Answers Handbook

How to reformat an R data frame with multiple lines into one line

I have data frames, such as the following, that I need to reformat into a single row so that I can create a new data frame that is a collection of many simpler data frames, with one row in a new data frame representing all the data in one of the more simple source 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 I want it to look after reformatting. Each column indicates rXsY, where "rX" indicates the row number of the original data frame, and "sY" indicates the column "s1" or "s2" of the original data frame. The Second column is omitted in the new data frame because 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

, , , reshape(), melt() / cast(), . , , , , , , ? - , , .

- , . , , - ( , , s1 s2)? , , ...

> 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
+4
4

R-

#prepare data
myDf1 = data.frame(Seconds=seq(0,1,.25), s1=seq(0,8,2), s2=seq(1,9,2))
myDf2 = data.frame(Seconds=seq(0,1,.25), s1=seq(10,18,2), s2=seq(11,19,2))

myDfList=list(myDf1,myDf2)

#allocate memory
myCombinedNewDf=data.frame(matrix(NA_integer_,nrow=length(myDfList),ncol=(ncol(myDf1)-1)*nrow(myDf1)))

#reformat
for (idx in 1:length(myDfList))  myCombinedNewDf[idx,]=c(t(myDfList[[idx]][,-1]))

#set colnames
colnames(myCombinedNewDf)=paste0("r",sort(rep.int(1:nrow(myDf1),2)),colnames(myDf1)[-1])

, :

#allocate memory
#the first column should ultimately be a factor
#I would use a character column first and later change it to type factor
#note the stringsAsFactors option!
myCombinedNewDf=data.frame(rep(NA_character_,length(myDfList)),
                       matrix(NA_integer_,
                              nrow=length(myDfList),
                              ncol=(ncol(myDf1)-1)*nrow(myDf1)),
                       stringsAsFactors=FALSE)

#reformat
for (idx in 1:length(myDfList))  {
  myCombinedNewDf[idx,-1]=c(t(myDfList[[idx]][,-1]))
  #I have just made up some criterion to get one "yes" and one "no"
  #"yes" if the sum of all values is below 100, "no" otherwise
  myCombinedNewDf[idx,1]=if (sum(myDfList[[idx]][,-1])<100) "yes" else "no"
}

#set colnames
colnames(myCombinedNewDf)=c("flag",
                        paste0("r",
                               sort(rep.int(1:nrow(myDf1),2)),
                               colnames(myDf1)[-1])
                        )
myCombinedNewDf$flag=factor(myCombinedNewDf$flag)
myCombinedNewDf
+1

melt() reshape2, :

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

, ( ) ( ) .

, :

# 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
+3

dcast devel data.table i.e. v1.9.5, value.var. row number ('rn'), - ('grp') dcast. here

library(data.table)#v1.9.5+
dcast(setDT(myDf[-1])[, c('rn1', 'grp') := list(paste0('r', 1:.N), 1)],
                   grp~rn1, value.var=c('s1', 's2'))
#   grp r1_s1 r2_s1 r3_s1 r4_s1 r5_s1 r1_s2 r2_s2 r3_s2 r4_s2 r5_s2
#1:   1     0     2     4     6     8     1     3     5     7     9

reshape base R

 reshape(transform(myDf, rn1=paste0('r', 1:nrow(myDf)), grp=1)[-1], 
         idvar='grp', timevar='rn1', direction='wide')
 #  grp s1.r1 s2.r1 s1.r2 s2.r2 s1.r3 s2.r3 s1.r4 s2.r4 s1.r5 s2.r5
 #1   1     0     1     2     3     4     5     6     7     8     9

Update

, , lapply dcast rbind rbindlist , dcast .

'myOtherDF` @Alex A. post

 myDFList <- list(myDf, myOtherDF)
 dcast(rbindlist(Map(cbind, myDFList, gr=seq_along(myDFList)))[,-1,
       with=FALSE][, rn1:= paste0('r', 1:.N), by=gr],
          gr~rn1, value.var=c('s1', 's2'))
 #   gr r1_s1 r2_s1 r3_s1 r4_s1 r5_s1 r1_s2 r2_s2 r3_s2 r4_s2 r5_s2
 #1:  1     0     2     4     6     8     1     3     5     7     9
 #2:  2     1     3     5     7     9     0     2     4     6     8
+2

c(t(therelevantdata)).

:

Values <- c(t(myDf[-1]))

, :

Names <- sprintf("r%ss%s", rep(1:5, each = 2), 1:2)

:

setNames(Values, Names)
# r1s1 r1s2 r2s1 r2s2 r3s1 r3s2 r4s1 r4s2 r5s1 r5s2 
#    0    1    2    3    4    5    6    7    8    9

data.frame :

setNames(data.frame(t(Values)), Names)
#   r1s1 r1s2 r2s1 r2s2 r3s1 r3s2 r4s1 r4s2 r5s1 r5s2
# 1    0    1    2    3    4    5    6    7    8    9

list data.frame s, @cyro111, :

do.call(rbind, lapply(myDfList, function(x) c(t(x[-1]))))
#      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10]
# [1,]    0    1    2    3    4    5    6    7    8     9
# [2,]   10   11   12   13   14   15   16   17   18    19

Convert to data.framewith as.data.frameand add names with names <-or setNames.

Generalized as a function:

myFun <- function(indf, asVec = TRUE) {
  values <- c(t(indf[-1]))
  Names <- sprintf("r%ss%s", rep(1:nrow(indf), each = ncol(indf[-1])),
                   1:ncol(indf[-1]))
  out <- setNames(values, Names)
  if (isTRUE(asVec)) out
  else (as.data.frame(as.matrix(t(out))))
}

Try:

myFun(myDf)        # Vector
myFun(myDf, FALSE) # data.frame

This is even more convenient on listof data.frames .... many options :-)

dfList1 <- list(
  data.frame(s = 1:2, a1 = 1:2, a2 = 3:4, a3 = 5:6),
  data.frame(s = 1:2, a1 = 11:12, a2 = 31:32, a3 = 51:52)
)

lapply(dfList1, myFun)
do.call(rbind, lapply(dfList1, myFun))
t(sapply(dfList1, myFun))
as.data.frame(do.call(rbind, lapply(dfList1, myFun)))
+2
source

More articles:


All Articles