65

I have huge dataframe like this:

SN = c(1:100, 1:100, 1:100, 1:100)  
class = c(rep("A1", 100), rep("B2", 100), rep("C3", 100), rep("D4", 100)) # total 6000 levels 
myvar = rnorm(400)
mydf = data.frame(SN, class, myvar)

I want to "unmelt" to a table with each level as single column and myvar in filled:

SN          A1            B2          C3         D4       .............and so on for all 6000

How can I achieve this, I know it is simple question, but I could not figure out.

joran
  • 169,992
  • 32
  • 429
  • 468
jon
  • 11,186
  • 19
  • 80
  • 132
  • 4
    In package `reshape2` the opposite of `melt` is `cast` – Andrie Oct 19 '11 at 20:41
  • thanks for suggestions, require(reshape2); acast(mydf, myvar ~ class) produced awaful result – jon Oct 19 '11 at 20:54
  • See answer below for correct syntax. It's really quite natural after you use it a couple times. Much better than built-in `reshape()`. – John Colby Oct 19 '11 at 20:56
  • 2
    Think of your desired table output like a plot: We want `SN` varying in the vertical direction (like `y`), and `class` varying in the horizontal direction (like `x`). So instead of `y ~ x`, we have `SN ~ class` – John Colby Oct 19 '11 at 21:01
  • 1
    See http://stackoverflow.com/a/9617424/210673 for a similar question with a list of ways to do this. – Aaron left Stack Overflow Mar 23 '12 at 16:21

4 Answers4

65
> dcast(mydf, SN ~ class)

  SN         A1         B2          C3          D4
1  1  0.1461258  0.8325014  0.33562088 -0.07294576
2  2  0.5964182  0.4593710 -0.23652803 -1.52539568
3  3  2.0247742 -1.1235963  1.79875447 -1.87462227
4  4  0.8184004  1.3486721  0.76076486 -1.18311991
5  5 -0.6577212  0.3666741 -0.06057506  1.38825487
6  6  0.1590443  0.2043661  0.08161778  0.10421797
...
John Colby
  • 22,169
  • 4
  • 57
  • 69
8
molten = melt( mydf , id.vars = c( "SN" , "class" ) , measure.vars = "myvar" )
casted = dcast( molten , SN~class )
Suraj
  • 35,905
  • 47
  • 139
  • 250
2

Another approach with split:

mydfSplit <- split(mydf[,-2], mydf$class, drop=TRUE)

The result is a list which can be easily converted to a data.frame if the components have the same dimensions (which is true in this example):

mydf2 <- do.call(cbind, mydfSplit)

The problem with this solution is that the names of the final result need a cleaning. However, for a more general data, this can be useful if SN is different for each class.

Oscar Perpiñán
  • 4,491
  • 17
  • 28
1

In base R you could do it like this...

# get it sorted so that all you need to do is make a matrix out of it
mydf <- mydf[order(mydf$class, mydf$SN),]
# save the unique values of SN
SNu <- unique(mydf$SN)
# combine a matrix with SN
mydfw <- data.frame(SNu, matrix(mydf$myvar, nrow = length(SNu)))
# name your columns    
colnames(mydfw) <- c('SN', levels(mydf$class))

Or, for a more concise expression using aggregate

aggregate(myvar~SN, mydf, 'c')
# column names don't come out great
colnames(mydfw) <- c('SN', levels(mydf$class))
John
  • 23,360
  • 7
  • 57
  • 83
  • 1
    That assumes that all combinations of levels are present, which, in general, is a dangerous assumption to make. – hadley Oct 20 '11 at 01:34
  • It works for the example. You just don't like the first one because it's very fast. :) But kidding aside, questioner, Hadley is correct, if you don't have a complete matrix then the this won't work correctly. You could just merge with a complete factorial design using expand.grid for the factors of interest. – John Oct 20 '11 at 02:36