Introduction
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-click 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 so to do custom automatic sorts. Below we discuss through a couple of examples how you add a line of R code to your R Outputs to get them sorting automatically. 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.
Method
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 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.
When you may like to sort it 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).
And then by including line 7 in the code used to build it, the table will sort by main brand:
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:
And then by going into Properties > R CODE in the Object Inspector, I added line 5 below. Notice what happens to the output:
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 pinch as a template) for a cross-tab looks like this:
table[order(table[,column], decreasing = TRUE),]
Noting “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
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 use the c() combining function to put the numbers together.
table = tabQ3
table[c(7,1,3,6,2,4,5)]
So how then do we get that list of indexes without doing it manually as I did above? With the order() function. So c(7,1,3,6,2,4,5) is the same as writing order(table, decreasing = TRUE) and then putting that into the table subset, then it 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),]
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 pinch 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)]
Next
How to Automatically Sort Table Rows
How to Sort Rows and Columns in Tables in Q
How to Sort Table Categories Within Each NET