How to Blank or Cap R Table Cells Based on a Different Statistic Using R

Introduction

This article describes how to go from a table with all cells being displayed...

...to one where any cell under a specified threshold has been removed or changed. In this example, the % is blanked based on the Count statistic being below 50:

Requirements

• A table showing multiple statistics with multiple rows/columns.
• A statistic to use for the threshold such as Sample Size or Count.

Method

1. Go to Calculation > Custom Code.

2. Paste the below under Properties > R CODE:

#identify table that has the multiple statistics
table = table.Q5
#create a table of the statistic you want to show in the final result
#below shows the % statistic in the final table
tab1 = table[,,"%"]
#create a table of the statistics used to blank cells
#below uses the Count statistic
tab2 = table[,,"Count"]
#identify where the Count in tab2 is less than 50
#set the final results in tab1 to blank (using =NA)
tab1[tab2 <50] = NA
tab1

• The above code defines the table using the reference name from Properties > GENERAL > Name of your table. Change this as needed for your own document.
• It then defines tab1 as a table with only the percentages and tab2 as a table with only the counts.
• Finally, tab1 is returned whereby any cells where Count is below 50 are made blank. You can change NA to a different value if you wish.

If your statistics are in the columns of your table you can use the code below which has a slightly different syntax for tab1 and tab2:

#identify table that has the multiple statistics
table = table.Q5
#create a table of the statistic you want to show in the final result
#below shows the % statistic in the final table
tab1 = table[,"%", drop=F]
#create a table of the statistics used to blank cells
#below uses the Count statistic
tab2 = table[,"Count", drop=F]
#identify where the Count in tab2 is less than 50
#set the final results in tab1 to blank (using =NA)
tab1[tab2 <50] = NA
tab1

5. Select this output and go to Properties > APPEARANCE > Number format to adjust decimal places and add a percentage sign.

See Also

How to Blank or Cap R Table Cells