Sorting tabyl output with Ns and proportions

user_14419912 0
Viewed 641

my first SO question!

I am trying to order the result of a call to tabyl from the janitor package. I can't work out how to sort the numbers appended in adorn_ns().

Using tabyl, I managed to create a table with frequencies, proportions and totals with the below code. What I would like to achieve is to sort the table in descending frequency of the "Total" column. Ultimately, I would like to pass the table on to knitr's kable() for reporting.

After I've called arrange on the tabyl, adorn_ns() pastes the N's in the incorrect "original" position, instead of the sorted position. This has been noted in Github, and (as I understand it) is caused by the 'core' not changing when the tabyl is sorted. See: https://github.com/sfirke/janitor/issues/352

The comment on Github states: "This is not a critical problem, you can feed custom Ns to the adorn_ns() call where you do the sort there too." Unfortunately I can't figure out how to place these custom Ns.

Alternatively, I thought about changing the order by using factor, however I'm hoping for a more robust solution as this variable contains many categories in my real data, and I would like to be able to apply this (or an alternative) method of table-rendering to different variables in the future, without having to laboriously type out the levels by frequency.

SO, any help on custom Ns, alternative sorting methods or (if turns out to be necessary) alternative table methods is very much appreciated.

Here is some toy data and where I got stuck.

library(dplyr)
library(janitor)

# some toy data
var1 <- c("aaa", "bbb", "ccc", "ccc", "ddd", "ddd", "ddd", "ddd", "aaa", "ddd", "ddd", "bbb", "bbb", "ddd")
sex <- c("f", "f", "m", "f", "m", "m", "f", "f", "m", "m", "f", "m", "f", "f")
df <- data.frame(var1,sex)


# First a tabyl with proportions, Ns and totals
tabyl(df, var1, sex) %>%
  adorn_totals(where = c("col", "row")) %>%
  adorn_percentages("col") %>%
  adorn_pct_formatting(digits = 0) %>%
  adorn_ns(position = "front")

# Results in (as expected)

|var1  |f        |m        |Total     |
|:-----|:--------|:--------|:---------|
|aaa   |1  (12%) |1  (17%) |2  (14%)  |
|bbb   |2  (25%) |1  (17%) |3  (21%)  |
|ccc   |1  (12%) |1  (17%) |2  (14%)  |
|ddd   |4  (50%) |3  (50%) |7  (50%)  |
|Total |8 (100%) |6 (100%) |14 (100%) |

What I would like to achieve:

# descending order of frequency
|var1  |f        |m        |Total     |
|:-----|:--------|:--------|:---------|
|ddd   |4  (50%) |3  (50%) |7  (50%)  |
|bbb   |2  (25%) |1  (17%) |3  (21%)  |
|aaa   |1  (12%) |1  (17%) |2  (14%)  |
|ccc   |1  (12%) |1  (17%) |2  (14%)  |
|Total |8 (100%) |6 (100%) |14 (100%) |

What I tried:

# Order by the Total column in descending frequency

df %>% tabyl(var1,sex) %>%
  adorn_totals(where = "col") %>%   # split col and row totals 
  arrange(desc(Total)) %>%
  adorn_totals(where = "row") %>%   # prevents total-row appearing at top)
  adorn_percentages("col") %>%
  adorn_pct_formatting(digits = 0) %>%
  adorn_ns(position = "front") 

# Results in (not what I expected)
|var1  |f        |m        |Total     |
|:-----|:--------|:--------|:---------|
|ddd   |1  (50%) |1  (50%) |2  (50%)  |
|bbb   |2  (25%) |1  (17%) |3  (21%)  |
|aaa   |1  (12%) |1  (17%) |2  (14%)  |
|ccc   |4  (12%) |3  (17%) |7  (14%)  |
|Total |8 (100%) |6 (100%) |14 (100%) |

# The categories have changed order, the N's have not (are in original position in table),
# and the % have been recalculated...
janitor r
2 Answers

Update to OP's request: see comments:

This is not as elegant but it brings you to your desired output:

df1 <- df %>% tabyl(var1,sex) %>%
    adorn_totals(where = "col") %>%   # split col and row totals 
    adorn_totals(where = "row") %>%   # prevents total-row appearing at top)
    adorn_percentages("col") %>%
    adorn_pct_formatting(digits = 0) %>%
    adorn_ns(position = "front") %>% 
    arrange(desc(Total))

df2 <- df1[1,]
df3 <- df1[-1,]   

bind_rows(df3, df2)

output:

  var1        f        m     Total
   ddd 4  (50%) 3  (50%)  7  (50%)
   bbb 2  (25%) 1  (17%)  3  (21%)
   aaa 1  (12%) 1  (17%)  2  (14%)
   ccc 1  (12%) 1  (17%)  2  (14%)
 Total 8 (100%) 6 (100%) 14 (100%)

First answer: Use sort = TRUE

df %>% tabyl(var1,sex, sort = TRUE) %>%
  adorn_totals(where = "col") %>%   # split col and row totals 
  #arrange(desc(Total)) %>%
  adorn_totals(where = "row") %>%   # prevents total-row appearing at top)
  adorn_percentages("col") %>%
  adorn_pct_formatting(digits = 0) %>%
  adorn_ns(position = "front")

Output:

  var1        f        m     Total
   aaa 1  (12%) 1  (17%)  2  (14%)
   bbb 2  (25%) 1  (17%)  3  (21%)
   ccc 1  (12%) 1  (17%)  2  (14%)
   ddd 4  (50%) 3  (50%)  7  (50%)
 Total 8 (100%) 6 (100%) 14 (100%)
user_133216470

Here's what it looks like to supply custom Ns that are sorted to match the tabyl sort. I'm saving the sorted tabyl as an object to avoid repeating code.

main <- tabyl(df, var1, sex) %>%
  adorn_totals(where = "col") %>%
  arrange(desc(Total)) %>%
  adorn_totals(where = "row")

main %>%
  adorn_percentages("col") %>%
  adorn_pct_formatting(digits = 0) %>%
  adorn_ns(position = "front", ns = main)

  var1        f        m     Total
   ddd 4  (50%) 3  (50%)  7  (50%)
   bbb 2  (25%) 1  (17%)  3  (21%)
   aaa 1  (12%) 1  (17%)  2  (14%)
   ccc 1  (12%) 1  (17%)  2  (14%)
 Total 8 (100%) 6 (100%) 14 (100%)

I've added a link on that GitHub issue to point back here, so there's an example.

If you'd prefer a longer block of code that doesn't save any objects, here's the same thing a different way:

tabyl(df, var1, sex) %>%
  adorn_totals(where = "col") %>%
  arrange(desc(Total)) %>%
  adorn_totals(where = "row") %>%
  adorn_percentages("col") %>%
  adorn_pct_formatting(digits = 0) %>%
  adorn_ns(position = "front",
           ns = tabyl(df, var1, sex) %>%
             adorn_totals(where = "col") %>%
             arrange(desc(Total)) %>%
             adorn_totals(where = "row"))
user_44703650
Related