R - Aggregate and rearrange

Question

I have the following data frame "myData"

  NAME GAME    STATUS
1 aaa  poker   PASSED
2 bbb  poker   FAILED
3 aaa  tennis  PASSED
4 bbb  tennis  PASSED

I'd like the following output

  NAME  FAILED PASSED TOTAL
1 aaa     0      2      2
2 bbb     1      1      2

I tried the following code

myResult <- as.data.frame(table(myData[,c("NAME","STATUS")]))

The above code gives me

  NAME  STATUS   Freq
1 aaa   FAILED   0
2 aaa   PASSED   2
3 bbb   FAILED   1
4 bbb   PASSED   1

At this point, I am unsure of how to rotate "myResult" get it in the format I need. Any ideas?

score 2 · Accepted Answer · answered Nov 21 '12 at 20:02

2

Here is a solution with basic R functions:

myResult <- as.data.frame(unclass(xtabs( ~ NAME + STATUS, myData)))
myResult$TOTAL <- rowSums(myResult)


    FAILED PASSED TOTAL
aaa      0      2     2
bbb      1      1     2

answered Nov 21 '12 at 20:02

Sven Hohenstein

80,497
17
145
168

score 1 · Answer 2 · answered Nov 21 '12 at 19:42

1

For this sort of thing, I often use dcast from the reshape2 package:

dat <- read.table(text = "  NAME GAME    STATUS
1 aaa  poker   PASSED
2 bbb  poker   FAILED
3 aaa  tennis  PASSED
4 bbb  tennis  PASSED",header = TRUE,sep = "",row.names = 1)

library(reshape2)
dcast(dat,NAME~STATUS,
        fun.aggregate = length,
        value.var = "STATUS",
        margins = "STATUS")

answered Nov 21 '12 at 19:42

joran

169,992
32
429
468

Your solution works too. Thanks for the help. I picked Sven's solution as the answer because it does not require me to install a package (I intend to distribute my script and not all my users will have the necessary packages installed) – Nov 21 '12 at 21:00
1

@user1 No worries! Always pick the answer that helped you the most... – joran Nov 21 '12 at 21:07

R - Aggregate and rearrange

2 Answers2