Take the 2-minute tour ×

I would like to reshape a data frame of dimensions 103740 x 4 into wide format, i.e., to go from the first block of text below to the second. The following function works well enough when applied to part of the data frame but it exhausts my machine's available memory when applied to the full data frame:

reshape(analysis1[1:8], idvar=c("name", "sire_name"), 
        timevar = "course", direction = "wide")[, -c(1,2)]  

What are some better ways to unstack this data frame?

           name sire_name        course appearance
1      A Beetoo   Bahhare       Kempton          0
2      A Beetoo   Bahhare     Lingfield          9
3      A Beetoo   Bahhare     Southwell          0
4      A Beetoo   Bahhare Wolverhampton          0
5 A Bid In Time  Danetime       Kempton          0
6 A Bid In Time  Danetime     Lingfield          3
7 A Bid In Time  Danetime     Southwell          2
8 A Bid In Time  Danetime Wolverhampton          2

  Kempton Lingfield Southwell Wolverhampton
1       0         9         0             0
5       0         3         2             2

I've tried moving the data frame into a data table, but just doing this alone doesn't seem to help.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

When you want it very simple like this reshaping in base R would be most efficient. I'd just make a matrix out of appearance, cast it to a data.frame type, and name the columns.

newDF <- data.frame( matrix(oldDF$appearance, ncol = 4, byrow = TRUE) )
names(newDF) <- oldDF$course[1:4]

Now, if you don't have the frame sorted just as you've presented it and maybe even have more values in the course column the following is more robust...

oldDF <- oldDF[ order(oldDF$course), ]
s <- sort(unique(oldDF$course))
newDF <- data.frame( matrix(oldDF$appearance, ncol = length(s)) )
names(newDF) <- s

(you can add other columns to the order function to break ties if you wish)

One of the great advantages of the many commands that can be used to reshape your data and is that they are robust to the order of the data, missing cells, etc. However, this also makes them generally quite slow when you've got a straightforward reshape on a very large data.frame object.

share|improve this answer
Probably should mention that this assumes the data frame has been sorted appropriately. –  joran Oct 29 '11 at 0:20
Yes, the first version assumes the data frame is sorted as shown. If it's not then use the second version. –  John Oct 29 '11 at 0:34
This is basically what reshape::melt does. I'd be surprised if it was much slower. And if it was slower, I'd argue that you'll probably still save time in the long wrong, because you don't need to worry about missing values etc. –  hadley Oct 29 '11 at 9:58

Your Answer


