How to Blank and Cap Cells of Tables Using R in Displayr
So you've made a table in Displayr and now you want to modify the contents (without changing the variables involved). With a bit of R code, this can be easier than you think.
There are various reasons why you might want to blank and cap cells of tables. You might want to make your table clearer to read by removing some of the small values (which you might consider ‘noise’). Perhaps, you want to ‘cap’ numbers over a certain amount. In this post, I explain how you can automatically modify the contents of tables in Displayr. In doing so, we give you a template for some simple R code that you can leverage.
I assume you are familiar with the content in this introductory post on R. This post serves as a part of the training program for those wanting to learn basic and practical R.
A general piece of code to modify cells of table
In Simple Table Manipulations with R Using Displayr, we covered the concept of table sub-setting. Within the square brackets , you specify the parts of the table you want to extract (i.e. rows and columns).
Now, suppose instead of specifying a list of row/column indices within the brackets, you could instead specify a condition. A condition, for example, might be
table < x which means “all the cells in the table which have a value less than x”. Whenever that evaluates to
TRUE, we are now working with only a subset of the table. You then ‘set’ that subset to be equal to new value (using the equals sign).
table[condition] = value
So in the above general piece of R code, the table is the name of the table you are specifying. It can either be:
- another table in the document (in which case it will need to be highlighted blue)
- a matrix or data frame earlier on within the same R Output (in which case it won’t be highlighted blue)
Note: In either case, you need to put in an extra line of code, which is just ‘
table’. That returns the final table with the substituted values (and not just the value). This line is included as the line of code in the examples below.
How to blank cells with small values
Consider the table below, which is a grid question with lots of numbers.
The table has the name (tab.Q5) in the document. With the following code in an R Output (Insert> R Output), it’s going to blank all the cells with a value under 50. In the language of R,
NaN. Also note, you don’t necessarily need the first line, I just include it to make line 2 look neater. I could equally have written:
tab.Q5[tab.Q5 < 50] = NA
table = tab.Q5 table[table “” not found /]
= NA table
The result of the code is below. In a separate table (as an R Ouput) we now have the table from before with certain cells blanked. If you put
0 (zero) instead of
NA in the code above, it would have made them all zero.
How to cap cells in a table
Here’s another example. Say you have a calculation that’s come about and you need to cap the values in a table. In the example below, some cells are estimated to be over 100%, but you want to cap it at 100.
This table was created as a Multiway Table (i.e. via R) using Insert > More > Tables > Multiway Table. It’s actually already an R Output, so therefore you don’t need to make a new R Output to modify it. In this case, you can add a couple of lines of code to the existing output.
Just go into the Properties > R CODE of the Object Inspector for the multiway table and tweak it, as I have below (on the right). I’ve just added two lines of code on lines 11 and 12. The key here is identifying that all the calculations from line 2 through 9 are being stored in an object called
multiway on line 2.
Try for yourself
The above two examples are stored in this example Displayr document.
About Matt Steele
Matt has over 14 years of experience in the marketing research arena, with a combination of research experience (qualitative and quantitative), marketing training, academic psychology (cognitive), creative leadership, geekiness and artistic flair. He currently works for Displayr (the home of Q and Displayr) and is based in London: supporting, selling, marketing and training for Q research software and associated software packages (eg: Displayr). He holds a Honours degree in Psychology from UNSW, a Grad Cert. in Marketing from UTS, and a Grad Dip in Directing from NIDA (all based in Sydney, Australia).