UPCOMING WEBINAR: The complete guide to analyzing NPS Displayr's CreateCustomTable R function allows you to create tables with very specific formatting and calculations in R. This is the same underlying function that is used in Autofit tables, but allows you more flexibility for formatting if you use it in a custom R Calculation directly. See How to Select the Type of Table that is Right for You for more information about the functionality of all the types of tables in Displayr.
This article describes how to change colors on a table based on values in a column and How the code works at a high level, see:
Method - Customizing Background Colors
Method - Customizing Font Colors
Method - Customizing Banded Colors
Method - Customizing Banded Colors with Conditional Formatting
In our example, we will go from an R table with a column of differences...
...to a CreateCustomTable R table with background color formatting using the FlipFormat package :
...a CreateCustomTable R table with custom font colors:
..a CreateCustomTable R table with custom banded row and font colors:
..and also a CreateCustomTable R table with custom banded row and font colors, and background color formatting:
Requirements
- A table with values you'd like to shade. Note, some of our specialty tables such as Table with Custom Formatting (Autofit) tables, Table of Differences, and Regression outputs are not straight data tables under the hood, so you will need to access the data a different way, see How to Extract Information from an Item using R.
Our table for this example has time periods in the columns and a final column of differences between the latest time periods as per the final step in How to Calculate the Difference between Two Columns Using R.
- Knowledge of How to Work with Data in R and How to Work with Conditional R Formulas.
- For a full list of customizations available for CreateCustomTable click here.
How the code works at a high level
To set custom colors for cell fonts or shading, you will need to provide a table of colors to map to each cell in the data table (excluding row and column headers). This is most easily done by creating a matrix the same size as your data with the default color you want to use. Then you will use a series of conditions to change to a specific color for specific cells. You can also set the row and column header colors in a similar way except it will be a vector (i.e. c("white","blue","white","green")) the same number as your rows/columns and you will use a different argument inside CreateCustomTable() (see list here).
Method - Customizing Background Colors
1. Select your R table made by a custom Calculation.
2. Below the existing R code in the R code editor paste the following:
####Get your data
# reference the table you'd like to format
tab = diff.table
# remove last row for the NET IF PRESENT
# you can use [,-NCOL(tab)] if you need to remove the last NET column
tab = tab[-NROW(tab),]
####Setup table of colors for each cell and edit it per values
#make matrix of cell.colors the same shape as your data table, this default color is white
cell.colors=matrix("white",nrow=NROW(tab),ncol=NCOL(tab))
#specify the column(s) that you'd like to shade
#you can use specific numbers c(1,2,3) or 1:NCOL(tab) to get all columns
#we are only shading the last column so we've provided the last column number only
shade=NCOL(tab)
#shade the cells green above 0%
cell.colors[,shade]=ifelse(tab[,shade] > 0,"green",cell.colors[,shade])
#shade cells red below 0%
cell.colors[,shade]=ifelse(tab[,shade] < 0,"red",cell.colors[,shade])
####Create your custom table
flipFormat::CreateCustomTable(tab,
row.header.fill = "#636363",
row.header.font.color = "white",
row.header.pad = 5,
col.header.fill = "#636363",
col.header.font.color = "white",
corner.fill = "#636363",
corner.font.color = "white",
cell.fill = cell.colors) #pass your table of colors from above to cell.fill
- This code first removes the NET row, then it creates a matrix of colors with the same dimensions as the source table.
- Next, we add conditions to this matrix to change the cells in the last column green or red based on the cell value.
- Finally, we use the CreateCustomTable function to create our AutoFit style table by setting background colors, padding, and font colors.
- We define the font colors based on our color matrix on the last line of code.
Method - Customizing Font Colors
1. Select your R table made by a custom Calculation.
2. Below the existing R code in the R code editor paste the following:
####Get your data
# reference the table you'd like to format
tab = diff.table # reference the source table
# remove last row for the NET IF PRESENT
# you can use [,-NCOL(tab)] if you need to remove the last NET column
tab = tab[-NROW(tab),] # remove last row
####Setup table of colors for each cell and edit it per values
#make matrix of font.colors the same shape as your table
#default the font color to black
font.colors=matrix("black",nrow=NROW(tab),ncol=NCOL(tab))
#specify the column(s) that you'd like to change color on
#you can use specific numbers c(1,2,3) or 1:NCOL(tab) to get all columns
#we are only shading the last column so we've provided the last column number only
colorcol=NCOL(tab)
#color the font green above 0%
font.colors[,colorcol]=ifelse(tab[,colorcol] > 0,"green",font.colors[,colorcol])
#color the font below 0%
font.colors[,colorcol]=ifelse(tab[,colorcol] < 0,"red",font.colors[,colorcol])
####Create the final custom table
flipFormat::CreateCustomTable(tab,
row.header.fill = "#636363",
row.header.font.color = "white",
row.header.pad = 5,
col.header.fill = "#636363",
col.header.font.color = "white",
corner.fill = "#636363",
corner.font.color = "white",
cell.font.color = font.colors) ###this line specifies your custom font colors
- This code first removes the NET row, then it creates a matrix of font colors with the same dimensions as the source table.
- Next, we add conditions to this matrix to change the font colors in the last column to green or red based on the cell value.
- Finally, we use the CreateCustomTable function to create our AutoFit style table by setting background colors, padding, and font colors.
- We define the font colors based on our color matrix on the last line of code.
Method - Customizing Banded Colors
1. Select your R table made by a custom Calculation.
2. Below the existing R code in the R code editor paste the following:
####Get your data
# reference the table you'd like to format
tab = diff.table
# remove last row for the NET IF PRESENT
# you can use [,-NCOL(tab)] if you need to remove the last NET column
tab = tab[-NROW(tab),]
####Setup table of colors for each cell and edit it per values
#make matrix of cell.fonts the same shape as your data table, this default color is white
cell.fonts=matrix("white",nrow=NROW(tab),ncol=NCOL(tab))
#specify banded font colors
odd.font="black"
even.font="white"
#make the different row fonts be the color of the appropriate band
cell.fonts[seq(1,nrow(tab),2),] = odd.font
cell.fonts[seq(2,nrow(tab),2),] = even.font
#make the banded font for the row headers
row.fonts = rep(c(odd.font, even.font), length.out = nrow(tab))
####Create your custom table
flipFormat::CreateCustomTable(tab,
row.header.pad = 5,
col.header.fill = "#0F52BA",
col.header.font.color = "white",
corner.fill = "#0F52BA",
banded.rows = TRUE,
banded.odd.fill = "#A7C7E7",
banded.even.fill = "#0F52BA",
row.header.font.color = row.fonts, #pass your table of row fonts from above to row.header.font.color
cell.font.color = cell.fonts) #pass your table of cell fonts from above to cell.font.color
- This code first removes the NET row, then it creates a matrix of font colors with the same dimensions as the source table.
- Next, we set the odd and even font colors to alternate per row. In this example, we need to do this for both cells and row headers.
- Finally, we use the CreateCustomTable function to create our AutoFit style table by setting background colors, padding, and font colors.
- We define the font colors based on our color matrices on the last two lines of code.
- We define the banded row fill colors in the preceding two rows.
Method - Customizing Banded Colors with Conditional Formatting
1. Select your R table made by a custom Calculation.
2. Below the existing R code in the R code editor paste the following:
####Get your data
# reference the table you'd like to format
tab = diff.table
# remove last row for the NET IF PRESENT
# you can use [,-NCOL(tab)] if you need to remove the last NET column
tab = tab[-NROW(tab),]
####Setup table of colors for each cell and edit it per values
#make matrix of cell.fonts the same shape as your data table, this default color is white
cell.fonts=matrix("white",nrow=NROW(tab),ncol=NCOL(tab))
#specify banded font colors
odd.font="black"
even.font="white"
#make the different fonts be the color of the appropriate band
cell.fonts[seq(1,nrow(tab),2),] = odd.font
cell.fonts[seq(2,nrow(tab),2),] = even.font
#make the banded font for the row headers
row.fonts = rep(c(odd.font, even.font), length.out = nrow(tab))
#make matrix of cell.colors the same shape as your data table, this default color is white
cell.colors=matrix("white",nrow=NROW(tab),ncol=NCOL(tab))
#specify banded row colors
odd.cell="#A7C7E7"
even.cell="#0F52BA"
#make the different rows be the color of the appropriate band
cell.colors[seq(1,nrow(tab),2),] = odd.cell
cell.colors[seq(2,nrow(tab),2),] = even.cell
#make the banded fills for the row headers
row.colors = rep(c(odd.cell, even.cell), length.out = nrow(tab))
#specify the column(s) that you'd like to conditionally change color on
#you can use specific numbers c(1,2,3) or 1:NCOL(tab) to get all columns
#we are only conditionally shading the last column so we've provided the last column number only
shade=NCOL(tab)
#shade the cells green above 0%
cell.colors[,shade]=ifelse(tab[,shade] > 0,"green",cell.colors[,shade])
#shade cells red below 0%
cell.colors[,shade]=ifelse(tab[,shade] < 0,"red",cell.colors[,shade])
#make shaded column have white font color
cell.fonts[,shade]="white"
####Create your custom table
flipFormat::CreateCustomTable(tab,
row.header.pad = 5,
col.header.fill = "#0F52BA",
col.header.font.color = "white",
corner.fill = "#0F52BA",
row.header.font.color = row.fonts, #pass your table of row fonts from above to row.header.font.color
row.header.fill = row.colors, #pass your table of row colors from above to row.header.fill
cell.font.color = cell.fonts, #pass your table of cell fonts from above to cell.font.color
cell.fill = cell.colors) #pass your table of colors from above to cell.fill
- This code first removes the NET row, then it creates a matrix of font colors with the same dimensions as the source table.
- Next, we set the odd and even font colors to alternate per row, followed by the odd and even cell colors. In this example, we need to do this for both cells and row headers.
- Then we add conditions to the cell color matrix to change the cells in the last column to green or red based on the cell value.
- Finally, we use the CreateCustomTable function to create our AutoFit style table by setting background colors, padding, and font colors.
- We define the font and cell colors based on our color matrices on the last four lines of code.
- Unlike in the previous example, it's not possible to use the banded rows functions together with conditional formatting, so we need to set the banded colors within the appropriate row header and cell function arguments.
See Also
How to Calculate the Difference Between Two Columns Using R
How to Add Statistical Significance to CreateCustomTable R Tables
How to Create an AutoFit Table
How to Work with Conditional R Formulas