Filter rows in rhandsontable in R Shiny

Question

I want to display and edit an rhandsontable within a Shiny app. Since my data frame is fairly large, I want the user to be able to filter a specific row instead of displaying the whole 1000 rows (see example below). I can create a reactive value to subset hot based on input$row, but then only DF[input$row,] is assigned to input$hot and, thus, next time I get the value of input$hot it returns a data frame with only one row.

library(shiny)
library(rhandsontable)

ui <- shinyUI(fluidPage(
  numericInput("rows","row to filter",value = 1),
  rHandsontableOutput("hot")
))

server <- shinyServer(function(input, output, session) {

  # render handsontable output
  output$hot <- renderRHandsontable({
    if (!is.null(input$hot)) {
      DF <- hot_to_r(input$hot)
    } else {
      set.seed(42)
      DF <- data.frame(a=1:1000, b= rnorm(1000))
    }
    rhandsontable(DF) 
  })

})

runApp(list(ui=ui, server=server))

Is there a filtering paramenter that I can apply to rhandsontable() that would allow me to render a filtered version of my data frame without actually subsetting it, so that the linked input$hot wouldn't be affected (except, of course, for any edits made by the user)?

I want the user to write the row to filter in the textInput box row and then the table to be filtered accordingly. It is imperative that nrow(hot_to_r(input$hot)) == 1000 continues to be TRUE:

What do you mean by a "filtered version without subsetting" ? What do you want to render ? — Stéphane Laurent, Dec 15 '16 at 18:10
@StéphaneLaurent, added expected rendering. The problem I have is that if I choose to subset DF by something like `DF[rows,]` (in a reactive way, of course) then the subset DF is saved back to `input$hot` and I lose the other 999 rows. — InspectorSands, Dec 16 '16 at 12:27

Mike Wise · Accepted Answer · 2016-12-17T13:05:43.690

You can't do it like that, with a filter, but you can cache a row, and put the data back when things change.

This is one way to do it in Shiny. Way harder to get right than I thought, I tried quite a few other ways that did not work, so it was a learning experience for me too.

library(shiny)
library(rhandsontable)

set.seed(1234)

# Data and a couple utility functions
nrow <- 1000
DF <- data.frame(a = 1:nrow,b = abs(rnorm(nrow)))
lastrow <- 1

getrowfromDF <- function(idx) {
  return(DF[idx,])
}

putrowintoDF <- function(rowdf,idx) {
  for (ic in 1:ncol(DF)) {
    DF[idx,ic] <<- rowdf[1,ic]
  }
}

u <- shinyUI(fluidPage(
  numericInput("row","row to filter",value = lastrow,min = 1,max = nrow(DF)),
  verbatimTextOutput("rowstat"),
  rHandsontableOutput("hot")
))

s <- shinyServer(function(input,output,session) {

  # reactive row status
  rs <- reactiveValues()
  rs$lstrow <- rs$currow <- 1

  # record changes from user editing here
  observeEvent(input$hot, {
    if (!is.null(input$hot)) {
      ndf <- data.frame(input$hot$data)  # convert from list
      #putrowintoDF(ndf,rs$currow)   # original - has inconsistency issue when 
                                     # you change rows without closing edit
                                     # with enter key in rhandsontable grid input$hot
      putrowintoDF(ndf,ndf[1,1])     # new, consistent, but relies on editable data for state
    }
  })

  # slide the row to the new position here
  observeEvent(input$row, {
    rs$lstrow <<- rs$currow
    rs$currow <<- input$row
  })


  # render handsontable output
  output$hot <- renderRHandsontable({
    ndf <- getrowfromDF(rs$currow)
    rhandsontable(ndf)
  })

  # just for debug
  output$rowstat <- renderPrint({ sprintf("rowstat: cur:%d last:%d",rs$currow,rs$lstrow) })

})
shinyApp(ui = u,server = s)

I would have liked a solution without global variable assignment and pure reactives instead of observes, but I don't think it is possible.

Update

The orginal I posed had a consistency error I missed because I was using a version of Shiny without the numeric control increment arrows. It happened when you changed the row with the numeric control input$row, without closing the edit in the rhandsontable input$hot with an enter key or change in focus, and caused the wrong row to be updated in the dataframe DF.

The fix is using data in input$hot to maintain this state, but that is perhaps dangerous as the user could edit that. Or maybe that is a feature...

Any way, here is a screen shot, but you really have to play with the values to see that it works and has no bugs:

score 2 · Answer 2 · answered Apr 29 '19 at 13:15

2

You can do it by conditionally hiding rows: https://github.com/jrowen/rhandsontable/issues/219#issuecomment-487574898

answered Apr 29 '19 at 13:15

Steve Powell

1,646
16
26

Filter rows in rhandsontable in R Shiny

2 Answers2

Update