This article describes how to change colors on a table based on values in a column (or columns) using two methods:
Method - Customizing Background Colors
Method - Customizing Font Colors
In our example we will go from an R table with a column of differences...
...to a CreateCustomTable R table (similar to a table with Autofit checked) with background color formatting using the FlipFormat package :
...and also a CreateCustomTable R table with custom font colors:
Requirements
- A table with values you'd like to shade - our table 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.
- For a full list of customizations available for CreateCustomTable click 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 box paste the following:
####Get your data
# reference the table you'd like to format
tab = diff.table
# remove last row for the NET
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 table default the color to white
cell.colors=matrix("white",nrow=NROW(tab),ncol=NCOL(tab))
#specify the column(s) that you'd like to shade
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, font color and define the cell colors based on our color matrix.
Method - Customizing Font Colors
1. Select your R table made by a custom Calculation.
2. Below the existing R code in the R CODE box paste the following:
tab = diff.table # reference the source table
tab = tab[-NROW(tab),] # remove last row
#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
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 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 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 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.
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
How to Use Different Types of Data in R (if-else statements)
How to Customize Fonts in a CreateCustomTable R Table