rhandsontable
rhandsontable copied to clipboard
jrowen
Show decimal point values in table
Hi @jrowen,
Great package!
I am just have problem getting decimal points to be displayed in the table when they are entered by the user - all values are being rounded to the nearest integer even with format="0.00". Any help would be greatly appreciated.
library(rhandsontable)
library(shiny)
mat <- diag(x=100, ncol = 4, nrow = 4)
RData <- data.frame(mat)
colnames(RData) <- c("A","B","C","D")
rownames(RData) <- colnames(mat)
ui <- fluidPage(
titlePanel("Test"),
mainPanel(
rHandsontableOutput("hot")
)
)
server = function(input, output, session){
values <- reactiveValues()
observe({
if(!is.null(input$hot)){
values$new <- hot_to_r(input$hot)
}else{
values$new <- RData
}
print(values$new)
})
output$hot <- renderRHandsontable({
rhandsontable(values$new,readOnly = FALSE) %>% hot_cols(format = "0.00")
})
}
shinyApp(ui, server)
Kind regards,
Dillon
I still have not found a solution but I do have an idea of why it may be happening. If the supplied data.frame has entries with 2 decimal places in them then any alterations to 2 decimal places are stored. Not sure if rhandsontable is expecting an integer input if the other values appear to be not numeric?
If you try the below code you will see that the values are stored with diagonal value = 100.23 (does not work for 100.00):
library(rhandsontable)
library(shiny)
mat <- diag(x=100.23, ncol = 4, nrow = 4)
RData <- data.frame(mat)
colnames(RData) <- c("A","B","C","D")
rownames(RData) <- colnames(mat)
ui <- fluidPage(
titlePanel("Test"),
mainPanel(
rHandsontableOutput("hot")
)
)
server = function(input, output, session){
values <- reactiveValues()
observe({
if(!is.null(input$hot)){
values$new <- hot_to_r(input$hot)
}else{
values$new <- RData
}
print(values$new)
})
output$hot <- renderRHandsontable({
rhandsontable(values$new,readOnly = FALSE) %>% hot_cols(format = "0.00")
})
}
shinyApp(ui, server)
Maybe a solution would be to explicitly display 2 decimal places in the original data.frame? I am not sure if this is possible?
I have also posted this issue to RStudio Community here: https://community.rstudio.com/t/rhandsontable-decimal-places-rounded-even-with-format-0-00/9344
I think this is resolved with the latest version of rhandsontable. Please re-open this issue if you are still running into problems.
Hi @jrowen,
Unfortunately the problem persists with the devel version installed.
Thanks for looking into this.
Dillon
I may not be understanding the issue. This is what I see when I run the shiny code above, and if I change the format parameter, I'm seeing the table update as expected.
If you run the original app with the 100 diagonal and try to edit any of the zero entries to 2 decimal places - these values are automatically rounded to the nearest integer.
For example if I edit the bottom left entry to be 5.58 this is automatically converted to a 5 as below:
If you then try to do the same thing in the second app with 100.23 diagonal you will see that the decimal places are retained as expected.
Now I understand the issue. This looks to be an issues with handsontable, so I'd suggest filing a bug with that project, referencing this issue as an example. There does appear to be a "rounding" issue if the diagonal number ends with a .00. Thanks for your persistence.
I'm actually going to take back my suggestion, as I think there is an issue with how the data set is being handled when it goes from JS into R. It looks like the numeric values are being converted to integers.
Any news regarding when this bug is expected to be fixed @jrowen ? Thank you!
We've pushed a possible fix to the 'devel' branch. If you have time, please test it out and let us know if things are working better.
devtools::install_github('jrowen/rhandsontable@devel')
It is working much better now. However, after testing out on my shiny app I found strange formatting issue. In attached pic, all the green cells are supposed to be user inputs and in percentage format. It works well for first two green cells (input 0.9 results in 90%, input of 1 results in 100%) but input of 1.1 results in 110.00000). All cells are formatted to be in % format in the same way. Do you think it might be related to the fix of the bug?
I'd suggest trying to set the decimal format for that column and then let us know if the problem persists.
On Sat, Feb 23, 2019 at 3:28 PM martin1007 [email protected] wrote:
It is working much better now. However, after testing out on my shiny app I found strange formatting issue. In attached pic, all the green cells are supposed to be user inputs and in percentage format. It works well for first two green cells (input 0.9 results in 90%, input of 1 results in 100%) but input of 1.1 results in 110.00000). Do you think it might be related to the fix of the bug? [image: image] https://user-images.githubusercontent.com/40647104/53291873-4339ad00-3777-11e9-8628-af909ca0114c.png
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/jrowen/rhandsontable/issues/256#issuecomment-466697716, or mute the thread https://github.com/notifications/unsubscribe-auth/ABdYrnnXkkCCG4eScNf49CIw_sIr6h8hks5vQbKGgaJpZM4UYPya .
Looks like this is now resolved @jrowen. Happy for you to close this issue now. Thanks for looking into this!
I am having issues and I've already tried devtools::install_github('jrowen/rhandsontable@devel')
I have a blank column that I wish to use as user input and calculate another column from. I can use format to specify the number of decimal places. When I click the calculate button, the number would be rounded to 4 decimal places and the first click would calculate from the actual entered value, the second click would use the rounded value. If I use format="0." Then for very small numbers (e.g. 1.23e-7) I can get a scientific notation with 3 significant figures and the calculation works properly, but a larger number would exhibit all different behavior (e.g., 1.23e-4 --> 0.0001, 1.23e-5 --> 0, 1.23e-6 --> 0.0000012). This seems very bizarre. @jrowen Can I ask for your help?
Looks as if handsontable only displays up to 4 decimal places. Everything else is truncated even if the underlying calculations go to 10 decimal places. Is this a bug in handsontable in general or something that is taking place in Rhandsontable?
Same problem here, numbers seem to get rounded to 4 decimal places. value: 0.123456789 format: 0.000000 output: 0.123500
A bit of a hack, but i've got around this issue by retrieving the maximum number of decimal places from a given dataframe using the following code adapted from here.
For my use case I need to display the column sums from one rhandsontable in another rhandsontable.
# Retrieve the maximum number of decimal places from a data frame where numeric columns have the suffix ".Area"
get_max_decimalPlaces <- function(.df){
max_N_digits <- .df |>
dplyr::select(stringr::str_subset(string = colnames(landcover_df_check), pattern = stringr::fixed(".Area"))) |>
tidyr::pivot_longer(cols = dplyr::everything(), names_to = "variable", values_to = "value") |>
dplyr::mutate(N_digits = nchar(gsub(".*\\.|^[^.]+$", "", as.character(value)))) |>
dplyr::summarise(max_N_digits = max(N_digits)) |>
dplyr::pull(max_N_digits)
return(max_N_digits)
}
In my case, retrieving this value in rhandsontable::renderRHandsontable() as follows:
maxDP <- get_max_decimalPlaces(.df = <DF>)
Then constructing a custom format string to pass to rhandsontable::hot_col() as follows:
rhandsontable::hot_col(col = stringr::str_subset(string = colnames(<DF>), pattern = stringr::fixed(".Area")),
format = paste0("[.]", paste(rep(0, maxDP), sep = "", collapse = ""))
)
Before:
After:
There is probably a much better way to handle this, if you know of one please let me know!
