Unclass x tabs on data frame

Question

I have the following data frame in R

  LOCATION COLOR  STATE
1 A        green  fresh
2 A        red    rotten
3 B        green  fresh
4 B        green  fresh
5 C        red    fresh
6 C        green  rotten

The above data frame can be created in R by using the following script

dat <- read.table(text = "LOCATION COLOR STATE
1 A green fresh
2 A red rotten
3 B green fresh
4 B green fresh
5 C red fresh
6 C green rotten",header = TRUE,sep = "",row.names = 1)

I am trying to rearrange my data frame to get the following output

  LOCATION COLOR ROTTEN FRESH
1 A        red    1      0
2 A        green  0      1
3 B        red    0      0
4 B        green  0      2
5 C        red    0      1
6 C        greed  1      0

I am trying to do this using the following code

dat <- as.data.frame(unclass(xtabs(~ LOCATION  + COLOR + STATE,dat)))

which gives me

            green.fresh   red.fresh  green.rotten red.rotten
1  A            1           0            0          1
2  B            2           0            0          0
3  C            0           1            1          0

As you can see, I am very close to the desired format but I am not sure how to make my current output match my desired output. Any ideas or hints would be appreciated.

score 6 · Accepted Answer · answered Nov 29 '12 at 17:00

6

I'm sure there's a way to do this with xtabs but I tend to use dcast instead:

library(reshape2)
dcast(dat,LOCATION+COLOR~STATE,
      fun.aggregate = length,value.var = "STATE",drop = FALSE)
  LOCATION COLOR fresh rotten
1        A green     1      0
2        A   red     0      1
3        B green     2      0
4        B   red     0      0
5        C green     0      1
6        C   red     1      0

Updated to include missing variable combinations as indicated in desired output.

answered Nov 29 '12 at 17:00

joran

169,992
32
429
468

I [took a stab](http://stackoverflow.com/a/13632743/1270695) at an answer with `xtabs` (and that almost rhymes). Not very pretty. – A5C1D2H2I1M1N2O1R2T1 Nov 29 '12 at 18:58
@mrdwab - +1 for the effort though. :) – Nov 29 '12 at 22:23

score 2 · Answer 2 · answered Nov 29 '12 at 18:57

WAY less efficient, I would guess, than @joran's solution--and certainly a lot less readable (but still interesting in a "so that's how things work" kind of way) is a solution like the following if you wanted to go from xtabs to a data.frame:

temp <- xtabs(~ LOCATION  + COLOR + STATE, dat)
data.frame(expand.grid(rev(attr(ftable(temp), "row.vars"))),
           setNames(as.data.frame.matrix(ftable(temp)), 
                    rev(attr(ftable(temp), "col.vars")$STATE)))
#   COLOR LOCATION rotten fresh
# 1 green        A      1     0
# 2   red        A      0     1
# 3 green        B      2     0
# 4   red        B      0     0
# 5 green        C      0     1
# 6   red        C      1     0

Ouch.

Holy cow. If it took several attempts, you could say that you took several stabs at an answer with `xtabs`...? — joran, Nov 29 '12 at 19:15

score 1 · Answer 3 · answered Nov 29 '12 at 20:31

1

aggregate(cbind(FRESH,ROTTEN)~LOCATION+COLOR,
  data=transform(dat,FRESH=1L*(STATE=="fresh"),ROTTEN=1L*(STATE=="rotten")),
  FUN=sum)


  LOCATION COLOR FRESH ROTTEN
1        A green     1      0
2        B green     2      0
3        C green     0      1
4        A   red     0      1
5        C   red     1      0

answered Nov 29 '12 at 20:31

Wojciech Sobala

7,431
2
21
27

@Wojeciech Sobala - +1 for a nice alternative way to do it! – Nov 29 '12 at 22:23

Unclass x tabs on data frame

3 Answers3