How to Sort your Data with R in Q
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).
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 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).
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:
After 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 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
The order of indexes of the rows from highest to lowest is
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)]
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),]
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?
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!).