Filtering dataframes with formattable

Question

Sample Data (amended from formattable github docs):

df <- data.frame(
  id = 1:10,
  name = c("Bob", "Ashley", "James", "David", "Jenny", 
           "Hans", "Leo", "John", "Emily", "Lee"), 
  age = c(48, 47, 40, 28, 29, 29, 27, 27, 31, 30),
  test1_score = c(18.9, 19.5, 19.6, 12.9, 11.1, 7.3, 4.3, 3.9, 2.5, 1.6),
  test2_score = c(9.1, 9.1, 9.2, 11.1, 13.9, 14.5, 19.2, 19.3, 19.1, 18.8),
  stringsAsFactors = FALSE)

You can make a pretty table with extra color formatting like this:

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

Looks like this:

What I want to do is to now filter this table such that I only keep the first n rows - here n=3. The way not to do it is to do the typical subsetting, because the color scales are now only applied to the min/max of the subset of the data, not the original data. i.e.

formattable(df[1:3,], list(
  age = color_tile("white", "orange"),
  test1_score = color_bar("pink", 0.2),
  test2_score = color_bar("pink", 0.2)
))

This looks like this:

This has rescaled the colors obviously.

Looking at the str of the object:

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

Classes ‘formattable’ and 'data.frame': 10 obs. of  5 variables:
 $ id         : int  1 2 3 4 5 6 7 8 9 10
 $ name       : chr  "Bob" "Ashley" "James" "David" ...
 $ age        : num  48 47 40 28 29 29 27 27 31 30
 $ test1_score: num  18.9 19.5 19.6 12.9 11.1 7.3 4.3 3.9 2.5 1.6
 $ test2_score: num  9.1 9.1 9.2 11.1 13.9 14.5 19.2 19.3 19.1 18.8
 - attr(*, "formattable")=List of 4
  ..$ formatter: chr "format_table"
  ..$ format   :List of 1
  .. ..$ :List of 3
  .. .. ..$ age        :function (x)  
  .. .. ..$ test1_score:function (x)  
  .. .. ..$ test2_score:function (x)  
  ..$ preproc  : NULL
  ..$ postproc : NULL

It's not possible to just filter/subset the produced object as the structure contains other elements.

Is there any way to output only the top n rows complete with the color scale that they would have if the whole table/dataframe was used ?

Figures. Thought it was worth a try. Seems like it has to be a parameter in `formattable` because when you subset the data when you input to `formattable` it obviously can't know about the other rows. — Carl, Nov 01 '15 at 00:13

score 9 · Accepted Answer · answered Nov 01 '15 at 00:22

Rescale from a subset of a data frame is a designed feature. If you really need to avoid rescale, you may try a currently available work-around:

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

subset_df(1:5)
subset_df(c(1,3,5,9))
subset_df(df$age <= mean(df$age))

It basically forces the formatter functions of each column to apply to fixed data and filters the the produced formatted values with the same subset.

That's very neat indeed - thanks. I'm using a formattable obj inside of shiny - the original has >2000 rows and want to filter top-10 on each variable - that's why it is nice to keep original scaling — jalapic, Nov 01 '15 at 00:47

Filtering dataframes with formattable

1 Answers1

Linked