R shiny datatable with numericinput and reactive column

Question

I am building a shiny datatable with one column for numericinput and the other reactive to the input. It is similar to what you can do in excel with equation embedded in cells. I am giving iris as example below and trying to calculate petal area. So peal area equals Petal.Length*Petal.Width/Adjust. The adjust parameter will be typed in by user to adjust area. A similar question was asked here but with no direct answer. Insert a numeric input for each row - R Shiny

library(shiny)
library(DT)
library(dplyr)

ui <- basicPage(

  DT::dataTableOutput('x1')
)

server <- function(input, output, session) { 
  # create a character vector of shiny inputs 
  shinyInput = function(FUN, len, id, ...) { 
    inputs = character(len) 
    for (i in seq_len(len)) { 
      inputs[i] = as.character(FUN(paste0(id, i), label = NULL, ...))
    } 
    inputs 
  } 

  # obtain the values of inputs 
  shinyValue = function(id, len) { 
    unlist(lapply(seq_len(len), function(i) { 
      value = input[[paste0(id, i)]] 
      if (is.null(value)) 1 else value 
    })) 
  } 

  # a sample data frame 
  DF <- reactive(
    iris <- iris %>%
      mutate(
        adjust = shinyInput(numericInput, nrow(iris), 'adj', value = 1),
        Petal_Area = Petal.Length*Petal.Width/shinyValue('adj', nrow(iris)) 
      )
  )

  # render the table containing shiny inputs 
  output$x1 = DT::renderDataTable(

    # use slider to control number of rows shown in table
    DF(), server = FALSE, escape = FALSE, options = list( 
      preDrawCallback = JS('function() { 
                           Shiny.unbindAll(this.api().table().node()); }'), 
      drawCallback = JS('function() { 
                        Shiny.bindAll(this.api().table().node()); } ') 
      ) 
      ) 
} 

shinyApp(ui, server)

Not sure whether this is solvable. Since you are not very clear why you need the interactivity I will post my trial of debugging your code below — 5th
The interactivity is the Area will be based on the user input Adj, and I am hoping to put everything in one table. According to the comment on the other post, "The input are created at the same time as the output so it's pretty complicated to catch them without regenerating the output" probably means it has to be on two DT tables, unless try rhandsontable package. — Jian
yeah I think that is pretty much your answer. You can try whether rhandsontable solves that problem, I would be surprised if it did. But then again the idea of rhandsontable is to manipulate a table like in Excel. I just don't know how that package deals with bigger tables than on github tutorial. — 5th

5th 5th · Accepted Answer · 2018-05-27T11:17:44

This is an incomplete answer. In the hope that another user may be able to improve upon it. If not then this is the best possible answer.

I tried now for about half an hour but for some reason (maybe cause DT is not meant to be used like Excel), I can't get the result of the calculation in the DT-table. However reactivity and calculation work if I do not display the result in the DT-table!

I only made two changes:

I added a debugging table
I replaced your dplyr-code and removed the calculation

The code looks like this:

library(shiny)
library(DT)

ui <- basicPage(
  DT::dataTableOutput('x1'),
  tableOutput('debug')
)

server <- function(input, output, session) { 
  # create a character vector of shiny inputs 
  shinyInput = function(FUN, len, id, ...) { 
    inputs = character(len) 
    for (i in seq_len(len)) { 
      inputs[i] = as.character(FUN(paste0(id, i), label = NULL, ...))
    } 
    inputs 
  } 

  # obtain the values of inputs 
  shinyValue = function(id, len) { 
    unlist(lapply(seq_len(len), function(i) { 
      value = input[[paste0(id, i)]] 
      if (is.null(value)) 1 else value 
    })) 
  } 

  # a sample data frame 
  DF <- reactive({
    iris_adj <- iris
    iris_adj$adjust<-shinyInput(numericInput, nrow(iris), 'adj', value = 1)
    # iris_adj$Petal_area<-iris$Petal.Length*iris$Petal.Width/shinyValue('adj',nrow(iris))
    return(iris_adj)
  })


  # render the table containing shiny inputs 
  output$x1 <- DT::renderDataTable(

    # use slider to control number of rows shown in table
    DF(), server = FALSE, escape = FALSE, options = list( 
      preDrawCallback = JS('function() { 
                           Shiny.unbindAll(this.api().table().node()); }'), 
      drawCallback = JS('function() { 
                        Shiny.bindAll(this.api().table().node()); } ') 
      ) 
      )
  output$debug<-renderTable({
    cbind(DF()[,-6],shinyValue('adj',nrow(iris)),Petal_Area=iris$Petal.Length*iris$Petal.Width/shinyValue('adj',nrow(iris)))
  })
      } 

shinyApp(ui, server)

As soon as I add the calculation, output from the DT does not find it's way into the debugging table.

On a personal note: I remember using this shinyInput and shinyValue trick about half a year ago. I also remember that it was very very unstable. I would not recommend it to anyone who wants to develop a stable app

I also remember that there was another R-package for complete interactive tables. It is called rhandsontables. Maybe try that: https://jrowen.github.io/rhandsontable/

R shiny datatable with numericinput and reactive column

1 Answers