Combining elements of two datatables into one new table

[GreyMerchant]

Hello,

I have this relatively simple problem I hope I can get a solution for. I want one datatable that's the combination of the two tables below. The values that should show on the table should come from df_value while the conditional formatting for each cell should be based on df_count. If the cell value in df_count is less than 10 then the corresponding cell should be highlted red on the final datatable output while showing the value from the second table.

library(DT)

df_count <- data.frame(
           A = c(27L, 2L, 46L, 30L, 34L, 1L, 48L, 1L, 8L, 17L, 17L, 8L, 25L, 7L),
           B = c(47L,16L,44L,9L,42L,39L,36L,8L,32L,
                 33L,40L,5L,19L,33L),
           C = c(18L,32L,41L,2L,42L,29L,4L,12L,50L,
                 41L,31L,19L,16L,35L),
           D = c(42L,31L,44L,41L,2L,28L,6L,31L,22L,
                 15L,31L,21L,27L,1L),
           E = c(47L,17L,15L,5L,34L,12L,27L,24L,4L,
                 10L,1L,12L,12L,50L)
)

#datatable(df_count)

df_value <- data.frame(
             A = c(95L,69L,4L,68L,3L,90L,47L,50L,
                   72L,61L,23L,74L,40L,27L),
             B = c(47L,17L,54L,3L,79L,9L,34L,20L,
                   32L,56L,48L,44L,54L,80L),
             C = c(69L,96L,79L,56L,73L,5L,45L,25L,
                   57L,38L,60L,30L,18L,79L),
             D = c(37L,41L,49L,61L,41L,7L,52L,99L,
                   18L,27L,100L,91L,46L,19L),
             E = c(42L,18L,41L,15L,53L,49L,3L,34L,
                   85L,78L,10L,44L,70L,91L)
  )
  
#datatable(df_value)

Created on 2020-10-12 by the reprex package (v0.3.0)

[nirgrahamuk]

library(tidyverse)

new_df <- map2_dfc(df_count,df_value,
     ~ifelse(.x<=10,
             paste0('<div style="background-color:#D11;">',.y,'</div>'), # try span as alternative to DIV
             as.character(.y)))

DT::datatable(data = new_df,
              escape = FALSE)

[GreyMerchant]

Thank you for respnding @nirgrahamuk. This actually seems to be it really Will span do the whole cell instead?

[nirgrahamuk]

no, div does the whole cell, span does the text only

[GreyMerchant]

I am perfectly happy with the whole cell. Thank you for this. I have a feeling this will solve my problem. Going to implement it shortly on my existing problem

[nirgrahamuk]

you can try this for a different style.

DT::datatable(data = new_df,
              escape = FALSE,
              class="compact")

[GreyMerchant]

@nirgrahamuk, thanks so much for the help so far.

I just wanted to ask. How can I change the new datatable output back into the original formatting?

This is what I am getting from the datatable

This is what I originally had. I just want to add the total from the bottom and the search bar back unto the top one

[nirgrahamuk]

please share a reprex for the code you used to make your total fields , search bar and your other formatting

[GreyMerchant]

Hello,

See here

library(radiant.data)

table_values <- pivotr(diamonds, 
                     cvars = c("cut","clarity"),
                     nvar = "price",
                     fun = "sum") %>% dtab()
table_values

[nirgrahamuk]

There are two possibilities

1. make your own version of pivotr that builds tables how you want them built.
2. don't use pivotr but use the method we have for making DT and just add more to it, like calculate your own totals and add them in.
   you can use the same HTML styling approach to bold them etc.
   there will be an option to turn on search you could look up in the DT documentation

[GreyMerchant]

Thanks again for your original solution @nirgrahamuk. I have found a mixed solution for the moment which is sufficient. Going to have to play more with DT to see exactly how to get the tables like I want them from scratch.