使用if-else设置单元格的条件格式?或者在R中隐藏DT包中的列

Question

我有一个这样的表。

data_wide <-data.frame(id=c(1,2,3,4),book1=c("","","","age"),book2=c("covid","alive","",""),book3=c("","income","","location"))

我正在尝试使用R中的DT包以html形式呈现此表，以便所有值都被隐藏，并且可以通过鼠标悬停来查看。为了进一步澄清，我的目标是根据值保持单元格的背景不同，例如，没有值的单元格可以是浅蓝色，而有值的单元格则可以是绿色，当我将鼠标悬停在绿色单元格上时，我可以查看此值。以下是我实现目标的天真尝试：

library(DT)
library(stringr)
data_wide <- data_wide %>% 
mutate(b1 = as.numeric(str_detect(string = book1, pattern = ""))) %>% 
mutate(b2 = as.numeric(str_detect(string = book2, pattern = ""))) %>% 
mutate(b3 = as.numeric(str_detect(string = book3, pattern = ""))) 

datatable(data_wide, options = list(columnDefs = list(list(
targets = 2:4,
render = JS(
"function(data, type, row, meta) {",
"return type === 'display' && data.length > 6?",
"'<span title="' + data + '">' + data.substr(0, 20) + '...</span>' : data;",
"}")
))), callback = JS('table.page(3).draw(false);'))%>%
formatStyle(
'book1',"b1",
color = "lightgreen",
border = '2px solid #FFFFFF',
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
formatStyle(
'book2',"b2",
border = '2px solid #FFFFFF',
color = "lightgreen",
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
formatStyle(
'book3',"b3",
border = '2px solid #FFFFFF',
color = "lightgreen",
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) 

为了解释，我添加了b1、b2、b3列，这样我就可以借用它们的值来对book1、book2和book3的单元格进行颜色编码。但是，我无法丢弃它们(b1、b2、b3(，因为不再需要它们。我试着在选项中添加list(visible=FALSE，target=c(5,6,7(，但这似乎不起作用，也许是因为该选项已经被用来启用鼠标悬停了？我在这个论坛和其他资源中也研究过条件格式，但看起来这只适用于整数(0,1..等(，我在这个表中有字符串。我不知所措，希望能找到解决办法。

非常感谢在这方面的任何帮助，并对其他想法持开放态度。

谢谢。

Answer 1

你可以试试这个吗：

data_wide <-data.frame(id=c(1,2,3,4),book1=c("","","","age"),book2=c("covid","alive","",""),book3=c("","income","","location"))
library(DT)
library(stringr)
library(dplyr)
data_wide <- data_wide %>% 
mutate(b1 = as.numeric(str_detect(string = book1, pattern = ""))) %>% 
mutate(b2 = as.numeric(str_detect(string = book2, pattern = ""))) %>% 
mutate(b3 = as.numeric(str_detect(string = book3, pattern = ""))) 

datatable(data_wide, options = list(columnDefs = list(list(
visible=FALSE, targets=c(5,6,7),
render = JS(
"function(data, type, row, meta) {",
"return type === 'display' && data.length > 6?",
"'<span title="' + data + '">' + data.substr(0, 20) + '...</span>' : data;",
"}")
))), callback = JS('table.page(3).draw(false);'))%>%
formatStyle(
'book1',"b1",
color = "lightgreen",
border = '2px solid #FFFFFF',
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
formatStyle(
'book2',"b2",
border = '2px solid #FFFFFF',
color = "lightgreen",
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
formatStyle(
'book3',"b3",
border = '2px solid #FFFFFF',
color = "lightgreen",
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen')) )

Answer 2

好的，我将在这里回答我自己的问题，代码需要使用"visible＝FALSE"；正如@LocoGris所指出的。如果不在列表中添加列表，省略号就会受到影响。

datatable(
data_wide, 
plugins = "ellipsis",
options = list(
columnDefs = list(list(
targets = c(2,3,4),
render = JS("$.fn.dataTable.render.ellipsis( 17, false )")
),list(visible=FALSE,targets = c(5,6,7)))
)
)%>%
formatStyle(
'book1',"b1",
color = "lightgreen",
border = '2px solid #FFFFFF',
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
formatStyle(
'book2',"b2",
border = '2px solid #FFFFFF',
color = "lightgreen",
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
formatStyle(
'book3',"b3",
border = '2px solid #FFFFFF',
color = "lightgreen",
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen')) )