I’m trying to create a Leaflet map in a Shiny app that clusters points and displays the total number of sales in each cluster. I want to avoid using JavaScript and handle all calculations in R.

Here’s a simplified example with dummy data that demonstrates the problem:

Data:

library(data.table)

# Sample data
data <- data.table(
  store_id = c("S001", "S002", "S003", "S004", "S005"),
  sales = c(100, 200, 150, 300, 250),
  lng = c(5.90920, 5.91592, 5.91893, 5.92591, 5.94153),
  lat = c(46.2839, 46.2928, 46.3093, 46.3138, 46.3093)
)

R Shiny App:

library(shiny)
library(leaflet)
library(dplyr)
library(data.table)

# Function to create the map with clustering by number of sales
create_sales_map <- function(data) {
  data_dt <- as.data.table(data)
  
  # Group by longitude and latitude to calculate the total sales per location
  sales_per_location <- data_dt[, .(total_sales = sum(sales)), by = .(lng, lat)]
  
  # Check the aggregated data
  print(head(sales_per_location))
  
  # Create the map
  leaflet(options = leafletOptions(gestureHandling = TRUE)) %>%
    addProviderTiles("OpenStreetMap.Mapnik") %>%
    addCircleMarkers(
      data = sales_per_location,
      lng = ~lng,
      lat = ~lat,
      popup = ~paste("Total Sales:", total_sales),
      clusterOptions = markerClusterOptions(
        spiderfyOnMaxZoom = TRUE
      )
    )
}

# Shiny UI
ui <- fluidPage(
  leafletOutput("map")
)

# Shiny Server
server <- function(input, output, session) {
  output$map <- renderLeaflet({
    create_sales_map(data)
  })
}

# Run the Shiny app
shinyApp(ui = ui, server = server)

Problem:
The map displays the clusters, but the number of sales shown in each cluster does not reflect the total sales correctly. Each marker seems to be clustered by its location without summing up the sales.

Expected Outcome:
I want each cluster to show the total number of sales for the grouped points. For example, if two points with 100 and 200 sales are in the same cluster, the cluster should display 300 sales.

I attempted to group the data by lng and lat and sum the sales before creating the map, but it doesn’t seem to work as expected.

How can I properly aggregate the data so that each cluster displays the correct total number of sales?

I am using data.table for efficient data manipulation.
I want to avoid using custom JavaScript functions and handle everything in R.