R Highcharter heatmap x-axis categories

Question

I need to show all months in the x-axis of a heatmap. You can see from the minimal example there is missing Sales data for Feb-19 but I would like the heatmap to include it anyway as an empty column.

I tried including a dummy variable in the data with Sales value of NA, which includes the missing month but then the tooltips don't work.

library(highcharter)
library(data.table)

# 1. Create data with missing month

heatmap_data <- data.table(Fruit = c('apple', 'banana', 'orange', 'pear'),
                                 Month = c('2019-01-01', '2019-03-01', '2019-04-01', '2019-05-01'),
                                 Sales = c(2, 4, 6, 8))

highcharter::hchart(heatmap_data,
    type = "heatmap",
    highcharter::hcaes(x = Month, y = Fruit, value = Sales))


# 2. Attempt with dummy entry included

heatmap_data <- data.table(Fruit = c('apple', NA, 'banana', 'orange', 'pear'),
                                 Month = c('2019-01-01', '2019-02-01', '2019-03-01', '2019-04-01', '2019-05-01'),
                                 Sales = c(2, NA, 4, 6, 8))

highcharter::hchart(heatmap_data,
    type = "heatmap",
    highcharter::hcaes(x = Month, y = Fruit, value = Sales)) %>%

    highcharter::hc_tooltip(borderWidth = 4)

The second attempt displays all the desired months on the x-axis but then I lose the tooltips. Is there a better way of dealing with these missing months?

Update 2019-08-21 Using @Ben's example with issue in shinymaterial shiny app:

library(highcharter)
library(data.table)
library(shiny)
library(shinymaterial)

heatmap_data <- data.table(
  Fruit = c('apple', 'banana', 'orange', 'pear'),
  Month = c('2019-01-01', '2019-03-01', '2019-04-01', '2019-05-01'),
  Sales = c(2, 4, 6, 8))

heatmap_data$Month <- datetime_to_timestamp(as.Date(heatmap_data$Month, format = "%Y-%m-%d")) 

ui <- material_page(
    title = "Heatmap Example",
    highchartOutput("heatmap")
)

server <- function(input, output) {

    output$heatmap <- renderHighchart({
        highchart() %>%
            hc_chart(
                type = 'heatmap'
            ) %>%
            hc_add_series(
                data = heatmap_data,
                type = 'heatmap',
                hcaes(x = Month, y = Fruit, value = Sales),
                colsize = 24 * 3600 * 1000 * 30,
                tooltip = list(
                    pointFormat = '{point.x:%b, %Y}, {point.y}: {point.value}'
                ),
                showInLegend = FALSE,
                borderWidth = 4
            ) %>%
            hc_xAxis(
                type = 'datetime'
            ) %>%
            hc_yAxis(
                categories = heatmap_data$Fruit
            )
    })
}

shinyApp(ui = ui, server = server)

Answer 1

For your second attempt with NA for 2019-02-01, perhaps add the tooltip formatter explicitly:

hc_tooltip(borderWidth = 4,
           formatter = JS('function (tooltip)
             {return tooltip.defaultFormatter.call(this, tooltip);}
           '), shared = TRUE)

Edit: This solution above threw a javascript error (see comments).

Alternative 1: If the dataset included the month as numeric (and not a date), then the heatmap would plot correctly with missing data for February. Note month indexed at zero for month.abb.

library(highcharter)
library(data.table)

heatmap_data <- data.table(Fruit = c('apple', 'banana', 'orange', 'pear'),
                           Month = c(0, 2, 3, 4),
                           Sales = c(2, 4, 6, 8))

hchart(heatmap_data,
       type = "heatmap",
       hcaes(x = Month, y = Fruit, value = Sales)) %>%
  hc_xAxis(categories = month.abb)

Alternative 2: If the Month variable is left as a date, then it could be converted with datetime_to_timestamp in milliseconds. The xAxis should then be of type 'datetime'. Colsize is also necessary to match time in milliseconds from timestamp for better approximation of width. I hope this might be helpful and closer to what you had in mind.

Edit: This solution includes shiny implementation and does not give any js errors for me:

library(highcharter)
library(data.table)
library(shiny)

heatmap_data <- data.table(Fruit = c('apple', 'banana', 'orange', 'pear'),
                           Month = c('2019-01-01', '2019-03-01', '2019-04-01', '2019-05-01'),
                           Sales = c(2, 4, 6, 8))

heatmap_data$Month <- datetime_to_timestamp(as.Date(heatmap_data$Month, format = "%Y-%m-%d")) 

ui <- fluidPage(
    titlePanel("Heatmap Example"),
    mainPanel(highchartOutput("heatmap"))
)

server <- function(input, output) {

    output$heatmap <- renderHighchart({
        highchart() %>%
            hc_chart(
                type = 'heatmap'
            ) %>%
            hc_add_series(
                data = heatmap_data,
                type = 'heatmap',
                hcaes(x = Month, y = Fruit, value = Sales),
                colsize = 24 * 3600 * 1000 * 30,
                tooltip = list(
                    pointFormat = '{point.x:%b, %Y}, {point.y}: {point.value}'
                ),
                showInLegend = FALSE,
                borderWidth = 4
            ) %>%
            hc_xAxis(
                type = 'datetime'
            ) %>%
            hc_yAxis(
                categories = heatmap_data$Fruit
            )
    })
}

shinyApp(ui = ui, server = server)