[R] ordering rows within CrossTable

[Marc Schwartz]

On Sep 10, 2011, at 4:03 PM, Luca Meyer wrote:

> Hi,
> 
> I am running the following -masked- code:
> 
> set.seed(23)
> city <- sample(c("C1","C2"),size=100,replace=T)
> reason <- sample(c("R1","R2","R3","R4"),size=100,replace=T)
> df <- data.frame(city,reason)
> library(gmodels)
> CrossTable(df$reason,df$city,prop.r=F,prop.c=F,prop.t=F,prop.chisq=F)
> 
> And I get the following output:
> 
>             | df$city 
>   df$reason |        C1 |        C2 | Row Total | 
> -------------|-----------|-----------|-----------|
>          R1 |         4 |        13 |        17 | 
> -------------|-----------|-----------|-----------|
>          R2 |        19 |        10 |        29 | 
> -------------|-----------|-----------|-----------|
>          R3 |        12 |        13 |        25 | 
> -------------|-----------|-----------|-----------|
>          R4 |        11 |        18 |        29 | 
> -------------|-----------|-----------|-----------|
> Column Total |        46 |        54 |       100 | 
> -------------|-----------|-----------|-----------|
> 
> I would like to have the df$reason sorted by decreasing count on the Row Total - that is showing R2, R4, R3 and finally R1 - how can I do that?
> 
> Thanks,
> 
> Luca


Hi,

Two things:

To respond to your specific query, you need to reorder the levels of df$reason, based upon the frequency of each level. CrossTable() is built upon table() and the default ordering of the rows and columns in the table will be in the order of the factor levels:

Use table() to get the counts:

> table(df$reason)

R2 R4 R3 R1 
29 29 25 17 

Now sort the table in decreasing order:

> sort(table(df$reason), decreasing = TRUE)

R2 R4 R3 R1 
29 29 25 17 

Now get the names:

> names(sort(table(df$reason), decreasing = TRUE))
[1] "R2" "R4" "R3" "R1"


Finally, set the levels of df$reason using the above values:

df$reason <- factor(df$reason, 
                    levels = names(sort(table(df$reason), 
                                        decreasing = TRUE)))


> CrossTable(df$reason,df$city,prop.r=F,prop.c=F,prop.t=F,prop.chisq=F)

 
   Cell Contents
|-------------------------|
|                       N |
|-------------------------|

 
Total Observations in Table:  100 

 
             | df$city 
   df$reason |        C1 |        C2 | Row Total | 
-------------|-----------|-----------|-----------|
          R2 |        19 |        10 |        29 | 
-------------|-----------|-----------|-----------|
          R4 |        11 |        18 |        29 | 
-------------|-----------|-----------|-----------|
          R3 |        12 |        13 |        25 | 
-------------|-----------|-----------|-----------|
          R1 |         4 |        13 |        17 | 
-------------|-----------|-----------|-----------|
Column Total |        46 |        54 |       100 | 
-------------|-----------|-----------|-----------|



The second point is that I am no longer maintaining the code for CrossTable(), albeit it has yet to be removed by the gmodels package maintainer. You should consider using the version of CrossTable() in the 'descr' package by Jakson Aquino on CRAN.


HTH,

Marc Schwartz