How to Sort your Data with R in Displayr
Automatically sorting your tables and charts (typically highest to lowest) is a key thing researchers like to do. Displayr has lots of ways to do this for you automatically (so that you don’t have to keep selecting a column of a table and telling it sort from the Ribbon).
But there may be situations when custom automatic sorts will require you to fiddle with the underlying R CODE. Below, we discuss a couple of examples showing how you can 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. Make sure you check out "How to do Simple Table Manipulations with R in Displayr" if you haven't yet, as this post assumes some knowledge from that post.
How you can sort data in Displayr without touching any code
For many of the R-based features in the Insert menu (mainly Visualizations), we’ve actually got the option to sort rows within the Inputs panel of the Object Inspector. So, the R Output interprets the source table as though it’s being sorted before the output is actually 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 custom table in an R Output. Examples might include a table that’s a KPI summary, a brand index matrix or any calculation/compilation. You only need to add a line of code at the end to keep the table sorted automatically. 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 the table, it will sort automatically.
Another scenario is that you’ve used one of Displayr's built-in tools for joining tables (such as Home > Tables > Merge Two Tables), and you want to sort the final output. You can do that by going to Properties > R CODE in the Object Inspector of the output. For example, the table below was created using the menu item Insert > 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 use as a template) for a crosstab 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.
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
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. 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, 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).
The above example is with a single-column table, so it's one dimensional. If you have two dimensions, then you need an extra comma when you reference the table (if that doesn't make sense, then check out this introductory post). The below sorts a crosstab of Preferred Cola (rows) by Age (columns) on the first age category. The first line of the code is simply to store the reference as an object called '
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 the 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 this 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 Displayr document, I’ve got the worked examples from above. So you can go in and have a look (and a play!)