dashboard.Rmd

---
title: "VR-Banken Dashboard"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
runtime: shiny
---

```{css custom-heading-size}
/* Change size for all level 3 headings (###) */
h3 {
  font-size: 24px; /* Adjust this value to your desired size (e.g., 20px, 28px, 2em) */
  color: #004d99; /* Optional: Change color for better visibility */
}

/* Optional: Change the size of the main title (Page Title) */
h1 {
  font-size: 32px;
}
```



```{r setup, include=FALSE}
library(flexdashboard)

# Load data. 

## Helper functions.
source(file = "helper_fcts.R")

## Base statistics from BVR.
bvr_csv <- readr::read_csv(file = "tabula-Liste_AlleBanken_2024.csv")

## Georeferenced sites.
load(file = "geolocations.RData")

## Polygons for German states (--> Bundeslaender).
geopackage_file <- "DE_VG5000.gpkg"

germany_states <- sf::st_read(
  dsn = geopackage_file
  , layer = "v_vg5000_lan" 
) |>
  dplyr::filter(Geofaktor_GF == "mit Struktur") |>
  dplyr::rename(Bundesland = GeografischerName_GEN)

## Official statistics. 
destatis <- readr::read_csv(file = "Destatis_Deutschland.csv")


# Data preparation. 

## Convert georeferenced sites to sf object for being conform with leaflet.
sf_geo <- sf::st_as_sf(
  df_geo |> dplyr::filter(!is.na(latitude)),
  coords = c("longitude", "latitude"),
  crs = 4326 # WGS 84 coordinate system
)

## Convert states polygons to WGS84 for being conform with leaflet.
germany_states <- sf::st_transform(
  germany_states
  , crs = 4326 # EPSG code for WGS84
)

## Join states polygons with statistics data. 
germany_states <- germany_states |>
  dplyr::select(Bundesland) |>
  dplyr::left_join(destatis, by = c("Bundesland" = "Verwaltungseinheit")) 

## Perform a spatial join (st_join) to assign the Bundesland name 
## from the polygon data to each bank point.
city_state <- sf::st_join(
  sf_geo |> dplyr::select(Ort),
  germany_states |> dplyr::select(Bundesland), # Select and rename state name column
  join = sf::st_intersects
) 

# Create color ramp for population data. 
# That is used later-on within the leaflet map.
pop_pal <- leaflet::colorNumeric(
  palette = "Blues"
  , domain = germany_states$Bev_gesamt 
)

germany_states <- germany_states |>
  dplyr::mutate(
    my_col = pop_pal(Bev_gesamt)
  )

## Add spatial info to BVR data.
bvr_geo <- city_state |>
  #dplyr::filter(!is.na(Bundesland)) |>
  dplyr::left_join(bvr_csv, by = "Ort")

```

Column 
-----------------------------------------------------------------------

### Räumliche Verteilung der VR-Banken

```{r}

df_map <- bvr_geo |> 
  # Select key columns and create formatted character strings
  dplyr::select(Rang, Name, Ort, Bundesland, Bilanzsumme, Einlagen, Spareinlagen, Kundenforderungen) |>
  dplyr::arrange(Rang) 

#   dplyr::mutate(
#     # Format columns to remove the DT formatting conflict
#     # Using scales::label_number for consistency and localization
#     Bilanzsumme = scales::label_number(
#       big.mark = ".", decimal.mark = ",", style_positive = "none"
#     )(Bilanzsumme),
#     Einlagen = scales::label_number(
#       big.mark = ".", decimal.mark = ",", style_positive = "none"
#     )(Einlagen),
#     Spareinlagen = scales::label_number(
#       big.mark = ".", decimal.mark = ",", style_positive = "none"
#     )(Spareinlagen),
#     Kundenforderungen = scales::label_number(
#       big.mark = ".", decimal.mark = ",", style_positive = "none"
#     )(Kundenforderungen)
#   )

# Display the map.
#bank_map

```

```{r map_output, fig.width=5, fig.heigth=4}
# Ensure 'data_table' is a reactive data frame if using other filters, 
# otherwise, just use your base data frame.

leaflet::leafletOutput(outputId = "bank_map")
```


```{r map_render}

# Since the user now has filtering AND controls, the reactive update must NOT clear all markers. Instead, it must clear and redraw only the markers that are currently visible after filtering, and maintain the layers control status.
# 
# The Conflict: The filtering (df_filtered()) should only apply to the visible markers. However, if the user turns off "Bayern," the proxy will immediately redraw all "Bayern" markers if they are present in df_filtered().

output$bank_map <- leaflet::renderLeaflet({
  
  # 1. Start the leaflet map
  bank_map <- leaflet::leaflet(
    data = df_map
    , width = "50%"
    , height = "50%"
  ) |>
    leaflet::addTiles()

  # 2. Get unique Bundesländer
  bundeslaender <- sort(unique(df_map$Bundesland))
  
  # 3. Loop through all Bundesländer and add a separate, clustered marker layer for each
  for (bl in bundeslaender) {
    
    # Filter data for the current group
    data_subset <- df_map[df_map$Bundesland == bl, ]
    
    bank_map <- bank_map |>
      leaflet::addMarkers(
        data = data_subset
        , group = bl        # Assigns the group name (used in Layers Control)
        , popup = ~paste0(
            "Bank: ", Name, "<br/>"
          , "Ort: ", Ort, "<br/>"
          , "Rang: ", Rang, "<br/>"
          #, "Bilanzsumme: ", format(Bilanzsumme, big.mark = ".", decimal.mark = ",")
          , "Bilanzsumme: ", Bilanzsumme
        ),
        # Apply clustering to THIS specific layer/group
        clusterOptions = leaflet::markerClusterOptions() 
      )
  }

  # Add polygons for Bundeslaender. 
  bank_map <- bank_map |>
    # Add the polygon layers
    leaflet::addPolygons(
        data = germany_states      # Your spatial data object (SF or GeoJSON loaded into R)
      , layerId = ~Bundesland        # Use the name attribute (e.g., "Bayern") as a unique ID
      , group = "Bundesländer (Polygone)"
      , weight = 1             # Thickness of the border line
      , color = "black"        # Color of the border line
      , fillColor = ~my_col # Make the interior transparent (so markers show)
      , fillOpacity = 0.75        # Ensure the interior is invisible
      , highlightOptions = leaflet::highlightOptions(
          weight = 2           # Border thickness when hovering
        , color = "#608C34"        # Border color when hovering
        , fillOpacity = 0.5
        , bringToFront = TRUE
      )
      , popup = ~paste0("Bundesland: ", Bundesland, "<br/>", "Bevölkerung: ", format(Bev_gesamt, big.mark = ".", decimal.mark = ","))           # Show the Bundesland name on hover
    )
  
  # Add the Layers Control to manage the *clustered* groups
  bank_map <- bank_map |>
    leaflet::addLayersControl(
      overlayGroups = c("Bundesländer (Polygone)") # bundeslaender,
      , options = leaflet::layersControlOptions(
        collapsed = FALSE
        , groupCheckboxes = TRUE
      )
    )

  # Split-up layers controle pane into two columns. 
  bank_map <- bank_map |>
    htmlwidgets::onRender("
      function(el, x) {
        // Target the 'overlays' div inside the layers control
        var overlay_div = document.getElementsByClassName('leaflet-control-layers-overlays')[0];
        
        // Apply CSS for two columns
        overlay_div.style.columnCount = '2';
        overlay_div.style.columnGap = '15px'; // Adjust spacing between columns
        overlay_div.style.height = 'auto'; // Remove fixed height if any
      }
    ")

  # Add legend. 
  bank_map <- bank_map |>
    leaflet::addLegend(
      pal = pop_pal
      , values = ~germany_states$Bev_gesamt
      , opacity = 0.7
      , title = "Bevölkerung"
      , position = "topleft"
    )
  
})

```




<!-- ###   {data-height=100} -->

### Erläuterung {data-height=100}

Die Karte ist interaktiv. 

* Bundesländer können über die Checkboxen an- und abgewählt werden. 
* Je nach Zoom-Level werden Institute in Cluster zusammengefasst. 
* Statistische Informationen werden per Popup (Click auf Marker) angezeigt. 


---


Werte für Bilanzsumme etc. in Tausend Euro. 




Column {.tabset}
-----------------------------------------------------------------------



### (Roh-) Daten

```{r table_output}
# Ensure 'data_table' is a reactive data frame if using other filters, 
# otherwise, just use your base data frame.

DT::dataTableOutput(outputId = "data_table") 
```

```{r table_render}
# Assume your base data is stored in a data frame named 'df_base'
# This reactive expression prepares the data table to be rendered.

# output$data_table <- DT::renderDataTable({
#   df_map |>
#     sf::st_drop_geometry() 
# } , filter = "top"
#   , rownames = FALSE
#   , options = list(pageLength = 10, searchable = TRUE)
# )

output$data_table <- DT::renderDataTable({
  
  # 1. Create the base datatable object
  dt_base <- df_map |>
    sf::st_drop_geometry() |>
    DT::datatable(
      # Place global options here
      filter = "top", 
      rownames = FALSE, 
      options = list(pageLength = 10, searchable = TRUE)
    )
    
  # 2. Apply formatting functions to the datatable object (dt_base)
  dt_base |>
    DT::formatCurrency(
      columns = c("Bilanzsumme", "Einlagen", "Spareinlagen", "Kundenforderungen"),
      currency = "",
      interval = 3,
      mark = ".",
      digits = 0
    )
    
})


```

```{r reactive_filter}
# This reactive expression holds the data frame currently visible in the DT table.
df_filtered <- reactive({
  # Ensure the data table has been initialized and filtered
  if (!is.null(input$data_table_rows_all)) {
    # If the user has applied a filter/search, subset the map data (df_map) 
    # using the indices of the currently visible rows.
    
    # We use df_map[input$data_table_rows_current, ] to apply the filter.
    df_map[input$data_table_rows_all, ]
  } else {
    # If no filtering has occurred yet, return the full data frame.
    df_map
  }
})
```


```{r map_updater}
# This observe block monitors the filtered data and updates the map accordingly.
observe({
  
  # Get the current filtered data
  data_to_plot <- df_filtered()
  
  # Ensure data is not empty before updating
  req(data_to_plot) 
  
  # Define labels for the filtered set (must match the filtered data order)
  # labels_filtered <- sprintf(
  #     "<strong>%s</strong><br/>%s",
  #     data_to_plot$Bundesland
  #     #, format(data_to_plot$Bev_gesamt, big.mark = ",", trim = TRUE)
  # ) |>
  #     lapply(htmltools::HTML)

  # Use leafletProxy to efficiently clear and redraw layers
  leaflet::leafletProxy("bank_map", data = data_to_plot) |>
    
    # 1. Clear all previous Polygons and Legends
    leaflet::clearMarkers() |>
    leaflet::clearMarkerClusters() |>
    
    # 2. Add the Polygons using the filtered data
    leaflet::addMarkers(
      data = data_to_plot,
      popup = ~paste0(
        "Bank: ", Name, "<br/>",
        "Ort: ", Ort, "<br/>",
        "Rang: ", Rang, "<br/>",
        "Bilanzsumme: ", format(Bilanzsumme, big.mark = ".", decimal.mark = ",")
      ),
      clusterOptions = leaflet::markerClusterOptions()
    )
})
```






### Interaktive Treemap

```{r treemap}
treemap_data <- bvr_geo |>
  sf::st_drop_geometry() |>
  dplyr::ungroup() |>
  dplyr::group_by(Bundesland, Ort, Name) |>
  dplyr::summarise(
    #area = mean(area_m2, na.rm = TRUE)
    #, biome_area = mean(biome_area, na.rm = TRUE)
    #, 
    #n = round(sum(ecoregion_area, na.rm = TRUE)/(10^6), digits = 2)
    n = sum(Bilanzsumme)
    #n = part_on_fl
  ) 

treemap_ly <- treemap_data |>
  #count_to_sunburst(width = NULL, height = NULL)  #700, 700
  count_to_treemap()

treemap_ly
```

### Test 

#### Chart A

#### Chart B