Automatically sorting your tables and charts is a key thing researchers like to do. Q has lots of ways to do this for you automatically (so that you don’t have to keep right-clicking on a column of a table and telling the table to sort).

But there may be situations when you need to fiddle with the R CODE to do custom automatic sorts. Below we discuss how you add a line of R code to your R Outputs to get them sorting automatically by demonstrating with a couple of examples. We hope to shed light on the one line of code needed, so you can then adapt it to your needs. It assumes the knowledge of the post How to do Simple Table Manipulations with R in Q.

How you can do it in Q without touching any code

The easiest way in Q to automatically sort your tables is to apply this rule designed to… *drumroll*… automatically sort your tables. The rule is extra good because it automatically excludes categories like “Don’t know” from the sort (with option to force their inclusion).

But Rules (which use JavaScript in the background) can’t be applied to R Outputs directly. There are ways around this though. The obvious first step, is that you sort your source tables using the above rule. That way, any R Output that references it maintains the ordering.

For many of the visualizations in the Create menu, we’ve actually got the option to sort rows within the Inputs panel of the Object Inspector. So, the visualization interprets the source table as though it’s being sorted before the output is drawn.

Sorting rows

When you may like to sort data via R code

One scenario where you may need to get into the R CODE to do the sorting is when you’re making your own table in an R Output. For example, if you’re making a table that’s a KPI summary, a brand index matrix or any calculation/compilation, you'll need a line of code at the end that keeps the table automatically sorted. For example, consider the table below, which is the brand funnel built by R Code (as explained in this post).

Brand funnel

By including line 7 in the code used to build it, the table will sort by main brand:

Sorting rows

Another scenario is that you’ve used one of Q's built-in tools for joining tables, and you want to sort the final output. You can do that by going into the Properties > R CODE in the Object Inspector of the output. For example, the table below was created using the menu item Create > Tables > Merge Two or More Tables:

MergedTables

After going into Properties > R CODE in the Object Inspector, I added line 5 below. Notice what happens to the output:

Sorting

Understanding the magic line of R Code

The R Code looks complicated, but once you break it down, the logic of it isn’t that hard to get your head around. It just looks convoluted. The basic example (which you can source as a template) for a cross-tab looks like this:

table[order(table[,column], decreasing = TRUE),]

Note that  “table” is the name of the table (data frame or matrix in R lingo) you wish to sort within the R Output and “column” is the column you’re referencing. I put them in blue so it stands out that these are the key bits you need to adapt in using it. 

The first bit to understand is that you can give an array of indexes to R via the square brackets and it will sort the table for you. Let’s say, I had the following which is from a table with a reference name of tabQ3:

Table Q3

The order of indexes of the rows from highest to lowest is 7,1,3,6,2,4,5

So if we feed that as an array in a table subset (with square brackets), I can use the c() combining function to put the numbers together.

table = tabQ3
table[c(7,1,3,6,2,4,5)]

Table3 sorted

How then do we get that list of indexes without doing it manually as I did above? With the order() function. The combining function  c(7,1,3,6,2,4,5) is the same as writing order(table, decreasing = TRUE). Putting that into the table subset, then it then becomes: table[order(table, decreasing = TRUE)]. Yes, I know there are brackets within brackets of different types.  You need the decreasing = TRUE bit otherwise R will sort in ascending order (which you may want).

In the example above, I used a single-column table meaning that it only involved one dimension. If you have two dimensions, then you'll definitely need an extra comma when you reference the table (if that doesn't make sense, then please read this introductory post). For example, the code below sorts a crosstab of Preferred Cola (rows) by Age (columns) by the first age category. The first line of the code is simply to store the long reference as a neat object table within the R Output.

table = table.Q3.Preferred.cola.by.D1.Age
table[order(table[,"18 - 29"], decreasing = TRUE),]

Crosstab sorted

As I mentioned earlier, to someone new at R, line 2 of code seems convoluted. But hopefully, my step-by-step explanation of subsetting a table by means of an array of indices untangles this for you. Remember, you can source the line of code and adapt it to your context.

Test yourself: how would you sort the same crosstab above by rows instead? Say by Coca-Cola?

(Answer = table[,order(table["Coca-Cola",], decreasing = TRUE)]

Have a look for yourself

In this QPack, I’ve got the worked examples from above. So you can go in and have a look (and a play!).