Two-Way, Sorted Textual Frequency Table in R

Question

How would one create a two-way, sorted tabulation table in R? I have two variables. The first takes on just 4 values. The second takes on many string values. I would like to end up with a table where the columns are the 4 values and the each row is a string. The frequencies occur in the body of the table. I also want to sort this - not alphabetically by string name, but by frequency - hopefully by overall frequency, how often a word occurs (not considering the 4 categories), but I would also be happy if it were just sorted by one the frequencies in one column. Any tips on how to create this? The end goal is to create a LaTeX table.

I have this kind of data in mind.

data <- data.frame(cat = sample(1:4, 100, replace=TRUE), string = sample(c("dog", "cat", "horse", "frog", "liger", "ligon", "tigon"), 100, replace = TRUE))

Sounds like `table`/`xtabs`, but you need [an MRE](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) — alistaire, Jun 07 '16 at 21:43
Updated to include one. I also clarified what I meant by sorting. — bill999, Jun 07 '16 at 22:46

Andrew Haynes · Answer 1 · 2017-01-26T14:51:35.063

Without seeing your data I can will assume you can use the table() function:

with some toy data:

> head(df,10)
   value    string
1      1 fruitcake
2      2     lemon
3      4   almonds
4      2     lemon
5      2     lemon
6      4  biscuits
7      1 fruitcake
8      2  biscuits
9      1  biscuits
10     3   almonds

Apply table():

> table(df$string,df$value)

            1 2 3 4
  almonds   1 2 2 2
  biscuits  2 1 1 3
  fruitcake 3 1 1 3
  lemon     2 5 5 1
  peanuts   4 2 5 4

The you can use the xtable() function from the xtable package to give the corresponding LaTeX code:

> xtable(table(df$string,df$value))

score 3 · Accepted Answer · answered Jun 07 '16 at 23:08

You can order a table:

Set seed for reproducible results:

set.seed(47)
# sample data
df <- data.frame(cat = sample(1:4, 100, replace=TRUE), 
                 string = sample(c("dog", "cat", "horse", "frog", "liger", "ligon", "tigon"), 100, replace = TRUE))

Make a table:

tab <- table(df$string, df$cat)
tab

#       1 2 3 4
# cat   7 4 2 4
# dog   2 1 4 2
# frog  2 3 5 1
# horse 4 3 3 1
# liger 4 3 2 2
# ligon 5 4 5 5
# tigon 5 4 7 6

Order it by indexing rows by their sum (negative for descending order):

ordered_tab <- tab[order(-rowSums(tab)),]
ordered_tab

#       1 2 3 4
# tigon 5 4 7 6
# ligon 5 4 5 5
# cat   7 4 2 4
# frog  2 3 5 1
# horse 4 3 3 1
# liger 4 3 2 2
# dog   2 1 4 2

Get latex code for ordered table:

knitr::kable(ordered_tab, 'latex')

# \begin{tabular}{l|r|r|r|r}
# \hline
#   & 1 & 2 & 3 & 4\\
# \hline
# tigon & 5 & 4 & 7 & 6\\
# \hline
# ligon & 5 & 4 & 5 & 5\\
# \hline
# cat & 7 & 4 & 2 & 4\\
# \hline
# frog & 2 & 3 & 5 & 1\\
# \hline
# horse & 4 & 3 & 3 & 1\\
# \hline
# liger & 4 & 3 & 2 & 2\\
# \hline
# dog & 2 & 1 & 4 & 2\\
# \hline
# \end{tabular}

Basic question, but is it possible to get modify the `knitr::kable(ordered_tab, 'latex') command to export this to a .tex file instead of displaying on the screen? — bill999, Jun 08 '16 at 03:10
It's designed to be used within `knitr`, so it doesn't have a save parameter, but you can wrap it in `write` easily: `write(knitr::kable(ordered_tab, 'latex'), file = 'table.tex')` — alistaire, Jun 08 '16 at 03:47

Two-Way, Sorted Textual Frequency Table in R

2 Answers2