How to Blank and Cap Cells of Tables Using R in Q

Introduction

After you make a table, you might like to modify the contents of the cells. Perhaps you want to make the table easier to read by removing some of the small values (which you might consider ‘noise’). Perhaps you want to ‘cap’ numbers over a certain threshold. In this post, I explain how you can modify the cells of tables made with R Outputs.

Requirements

A table with small values you want to remove
A table with values you want to cap at a certain threshold

Method

How to blank cells with small values

Consider the table below, which is a grid question with lots of numbers.

Brand Image Table Q5

You would like to blank all cells with a value under 50. To do so:

From the toolbar, select Create > R Output
Copy the following code to the R Code window on the right:

table = table.q5
table[table < 50] = NA
table

The result of the code is below. In a separate table (as an R Ouput) we now have the table form before with certain cells blanked. If you put 0 (zero) instead of NA in the code above, it would have made them all zero.

Q5 with cells blanked

How to cap cells in a table

Here’s another example. Say you have a calculation 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.

Multiway table

This table was created as a Multiway Table (ie: via R) using Create > 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 to 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.

Capping cells