Search code examples
rformattable

printing blanks instead of NA's when using formattable in R

Consider the example data.frame

df <- data.frame(
  id = 1:4,
  name = c("Bob", "Ashley", "James", "David"), 
  age = c(48, NA, 40, 28),
  test1_score = c(18.9, 19.5, NA, 12.9),
  stringsAsFactors = FALSE)

I'm using the R package formattable to make a pretty table.

library(formattable)
formattable(df, list(
age = color_tile("white", "orange"),
test1_score = color_bar("pink", 'proportion', 0.2)
))

It used to be that NA's were automatically not printed, and a blank was printed instead. It seems like this is no longer the default, but I would still like to print a blank for the NA. Replacing NA like this works:

df[is.na(df)]=''
formattable(df, list(
  age = color_tile("white", "orange"),
  test1_score = color_bar("pink", 'proportion', 0.2)
))

enter image description here

However if I try to format one of the columns to force it to have 2 decimal places, the pesky NA's return:

df$age = digits(df$age, digits=2)
formattable(df, list(
age = color_tile("white", "orange"),
test1_score = color_bar("pink", 'proportion', 0.2)
))

enter image description here

If I remove the NA again, the NA goes away, but so do the decimal places

df[is.na(df)] = ''
formattable(df, list(
age = color_tile("white", "orange"),
test1_score = color_bar("pink", 'proportion', 0.2)
))

enter image description here

I believe the reason is that digits converts df$age to a formattable numeric object and creates the NA, and df[is.na(df)] = '' converts df$age to a formattable character object:

> df$age = digits(df$age, digits=2)
> df$age
[1] 48.00  NA   40.00 28.00
> class(df$age)
[1] "formattable" "numeric"    
> df[is.na(df)] = ''
> df$age
[1] "48" "  " "40" "28"
> class(df$age)
[1] "formattable" "character"

Any ideas on a solution?

Ultimately I'd also like to use this with a filtered data.frame, where I use the code from Filtering dataframes with formattable to ensure that the color scale stays the same when filtering the data.frame:

df$age = digits(df$age, digits=2)
  subset_df <- function(m) {
    formattable(df[m, ], list(
      age = x ~ color_tile("white", "orange")(df$age)[m],
      test1_score = x ~ color_bar("pink", 'proportion', 0.2)(df$test1_score)[m],
      test2_score = x ~ color_bar("pink", 'proportion', 0.2)(df$test2_score)[m]
    ))
  }

subset_df(1:3)

enter image description here

The problem doesn't seem to be with this code though.

Solution

  • You could use the sprintf function to format the numeric columns as strings with the desired number of decimal places. In the code below, sprintf converts NA to the string "NA", which we then convert to an empty string. 

    # Function to convert numeric values to strings with a given number of 
#  decimal places, and convert NA to empty string
fnc = function(var, decimal.places) {
  var = sprintf(paste0("%1.",decimal.places,"f"), var)
  var[var=="NA"] = ""
  var
}

# Select the columns we want to reformat
vars = c('age', 'test1_score')

# Apply the function to the desired columns with the desired number of decimal places
df[ , vars] = mapply(fnc, df[ ,vars], 2:3)

formattable(df, list(
  age = color_tile("white", "orange"),
  test1_score = color_bar("pink", 'proportion', 0.2)
))

    enter image description here