R Shiny渲染的datatable无数据显示问题排查求助

使用R Shiny结合highcharter包开发仪表盘时，能正常渲染出datatable组件，但表格内无数据显示，而对应的UTF-8格式CSV文件确实包含有效数据。

输出效果截图 #

原始代码 #

# INSERT Libraries HERE
library(shiny)
library(tidyverse)
library(lubridate)
library(highcharter)

# Load the dataset from your CSV file
shefClimateNoNA <- read.csv("C:/Users/Documents/R_files/climate_data/Sheffield_Climate_5year.csv")

# Verify that the TIMESTAMP column is in the correct format (if needed)
shefClimateNoNA$TIMESTAMP <- as.POSIXct(shefClimateNoNA$TIMESTAMP, format = "%Y-%m-%d %H:%M:%S")


##########################
##### User interface #####
##########################
ui <- fluidPage(
  # Title 
  titlePanel(
    h1("Hadfield Green Roof 5-year Dataset", style = "padding-bottom: 20px")
  ),
  
  # Sidebar layout
  sidebarLayout(
    sidebarPanel(
      dateRangeInput(
        'dateRange',
        label = paste('Date range'),
        start = "2011-03-01", end = "2011-12-31",
        min = "2011-03-01", max = "2016-02-28",
        separator = " to ", format = "dd/mm/yyyy",
        startview = 'month', weekstart = 1
      )
    ),
    mainPanel(
      DT::dataTableOutput(outputId = "dataTable"),
      highchartOutput(outputId = "timeSeries"),
    )
  )
)

###########################
##### Server function #####
###########################
server <- function(input, output, session) {
  
  # Filter data based on selections
  output$dataTable <- DT::renderDataTable(DT::datatable({
    shefClimateNoNA %>%
      filter(
        between(
          TIMESTAMP, 
          as_datetime(as.character(input$dateRange[1])), 
          as_datetime(as.character(input$dateRange[2]))
        )
      )
  }))
  
  # Time series for air temperatures
  output$timeSeries <- renderHighchart({
    hchart(
      shefClimateNoNA %>% 
        filter(
          between(
            TIMESTAMP, 
            as_datetime(as.character(input$dateRange[1])), 
            as_datetime(as.character(input$dateRange[2]))
          )
        ) %>% 
        mutate(
          TIMESTAMP = format.Date(TIMESTAMP, "%d %b %y")
        ),
      type = "line",
      hcaes(x = TIMESTAMP, y = AirTC_Avg),
      name = "Air temperature"
    ) %>%
      hc_xAxis(
        tickAmount = 10,
        tickInterval = 30,
        labels = list(format = "{value:%b %y}")
      )
  })
}

##################################
##### Call shinyApp function #####
##################################
shinyApp(ui = ui, server = server)

问题原因及修复方案 #

核心问题 #

代码中日期格式解析不匹配：

• dateRangeInput设置了format = "dd/mm/yyyy"，但服务器端用as_datetime()解析时，lubridate默认采用yyyy-mm-dd格式，导致输入的日期被错误转换，过滤条件无法匹配数据中的TIMESTAMP，最终返回空数据集。
• 将TIMESTAMP格式化为字符串后，丢失了日期的时序特性，导致highchart的x轴无法正确识别时间序列。

修复后的完整代码 #

# 加载依赖包
library(shiny)
library(tidyverse)
library(lubridate)
library(highcharter)

# 读取数据集
shefClimateNoNA <- read.csv("C:/Users/Documents/R_files/climate_data/Sheffield_Climate_5year.csv")

# 转换TIMESTAMP为POSIXct格式
shefClimateNoNA$TIMESTAMP <- as.POSIXct(shefClimateNoNA$TIMESTAMP, format = "%Y-%m-%d %H:%M:%S")

##########################
##### 用户界面 #####
##########################
ui <- fluidPage(
  # 标题
  titlePanel(
    h1("Hadfield Green Roof 5-year Dataset", style = "padding-bottom: 20px")
  ),
  
  # 侧边栏布局
  sidebarLayout(
    sidebarPanel(
      dateRangeInput(
        'dateRange',
        label = '日期范围',
        start = "2011-03-01", end = "2011-12-31",
        min = "2011-03-01", max = "2016-02-28",
        separator = " 至 ", format = "dd/mm/yyyy",
        startview = 'month', weekstart = 1
      )
    ),
    mainPanel(
      DT::dataTableOutput(outputId = "dataTable"),
      highchartOutput(outputId = "timeSeries")
    )
  )
)

###########################
##### 服务器函数 #####
###########################
server <- function(input, output, session) {
  
  # 响应式过滤数据，避免重复逻辑
  filtered_data <- reactive({
    # 按dd/mm/yyyy格式解析用户输入的日期
    start_date <- dmy(input$dateRange[1])
    end_date <- dmy(input$dateRange[2])
    
    shefClimateNoNA %>%
      filter(
        between(
          TIMESTAMP, 
          as.POSIXct(start_date), 
          # 包含结束日期的最后一秒，确保当天所有数据都被选中
          as.POSIXct(end_date) + days(1) - seconds(1)
        )
      )
  })
  
  # 渲染数据表格
  output$dataTable <- DT::renderDataTable(DT::datatable({
    filtered_data()
  }))
  
  # 渲染气温时间序列图
  output$timeSeries <- renderHighchart({
    hchart(
      filtered_data(),
      type = "line",
      hcaes(x = TIMESTAMP, y = AirTC_Avg),
      name = "气温"
    ) %>%
      hc_xAxis(
        type = "datetime",  # 指定x轴为时间类型
        tickAmount = 10,
        labels = list(format = "{value:%b %y}")
      )
  })
}

##################################
##### 启动Shiny应用 #####
##################################
shinyApp(ui = ui, server = server)

关键修复点 #

1. 日期解析匹配：使用dmy()函数解析用户输入的dd/mm/yyyy格式日期，确保和dateRangeInput的输出格式一致。
2. 响应式数据对象：创建filtered_data响应式对象，统一处理数据过滤逻辑，提升代码复用性和效率。
3. 完整日期范围：调整结束日期的过滤边界，加上一天减一秒，确保包含结束日期的所有时间点数据。
4. 保留时序特性：不再将TIMESTAMP格式化为字符串，直接传递POSIXct格式给highchart，并设置x轴为datetime类型，保证时间序列的正确显示。

内容的提问来源于stack exchange，提问作者db2020