How to implement a multi-level drilldown?
!! Updating with info from Jan in answer. He has clearly showed how to do this using my first dataset below, df3. Working backwards I tried to apply his method to the three separate datasets in my final example, and the drilldowns cannot reference higher levels unless all the data is in one dataframe. This is paramount as the key aspect approach in some of the links shows dataframes of only 2 columns for each drilldown, BUT they must be coming from an overall larger one where all the links are implicit.
Joining the dataframes in my final example and approaching using Jan's method I've included a working example to see how the data needs to be arranged and how it is working.
I'm trying to do a multiple level drilldown and not sure which approach to take or what way the data should be to start with. Following a number of paths jkunst stackoverflow infoworld I've tried two methods, the first kind of works, but is doubling everything in the stacking: should have 2 in the stacked bar chart: Type; Stationary, Household. The first level is messed up.
df3 <- tibble::tribble(
~Year, ~Full_Total, ~Total, ~Type, ~Sub_Type, ~Count,
1990, 40, 35, "Stationary", "Pencils", 20,
1990, 40, 35, "Stationary", "Rulers", 15,
1990, 40, 5, "Household", "Dishes", 5,
1990, 40, 5, "Household", "Cleaning_products", 0,
1991, 60, 30, "Stationary", "Pencils", 30,
1991, 60, 25, "Stationary", "Rulers", 25,
1991, 60, 0, "Household", "Dishes", 0,
1991, 60, 5, "Household", "Cleaning_products", 5,
1992, 90, 40, "Stationary", "Pencils", 30,
1992, 90, 40, "Stationary", "Rulers", 10,
1992, 90, 50, "Household", "Dishes", 40,
1992, 90, 50, "Household", "Cleaning_products", 10,
)
df3 <- df3 %>%
group_by(Year) %>%
mutate(Year = fct_inorder(as.character(Year)))
# ---- drilldown using df3
drilldown <- df3 %>%
group_nest(Year) %>%
mutate(id = Year,
type = "column",
data = map(data, mutate, name = `Type`, y = `Count`),
data = map(data, list_parse)
)
drilldown2 <- df3 %>%
group_nest(Year) %>%
mutate(id = Year,
type = "column",
data = map(data, mutate, name = "Sub_Type", y = "Count"),
data = map(data, list_parse)
)
# ---- data is ready for drilldown charting ----
# ---- chart ----
hchart(
df3,
"column",
hcaes(x = Year, y = `Total`, name = Type, drilldown = Year),
name = "Full Total",
colorByPoint = TRUE
) |>
hc_plotOptions(column = list(stacking = "normal")
)|>
hc_drilldown(
allowPointDrilldown = TRUE,
series = list_parse(drilldown)
)|>
hc_drilldown(
allowPointDrilldown = TRUE,
series = list_parse(drilldown2)
)
So the code above kind of works/ works incorrectly to first level but adding a list of lists to hc_drilldown e.g. to make more drilldowns doesn't work
hc_drilldown(
allowPointDrilldown = TRUE,
series = list(
list_parse(drilldown),
list_parse(drilldown2)
)
Yet another way as follows, should work and would facilitate further drilldowns but I can't get it to work.
df1 <- tibble::tribble(
~Year, ~Total,~Type,
1990, 450, "Stationary",
1990, 450, "Food",
1990, 450, "Household",
1991, 600, "Stationary",
1991, 600, "Food",
1991, 600, "Household",
1992, 170, "Stationary",
1992, 170, "Food",
1992, 170, "Household",
1993, 253, "Stationary",
1993, 253, "Food",
1993, 253, "Household")
df1 <- df1 %>% group_by(Year) %>% mutate(Year = as.character(Year)) %>% mutate(Year = fct(Year))
df2 <- tibble::tribble(
~Year, ~Sub_Total,~Sub_Type,
1990, 100, "pencils",
1990, 200, "bread",
1990, 150, "bulbs",
1991, 200, "pens",
1991, 200, "milk",
1991, 200, "dishes",
1992, 50, "ruler",
1992, 100, "meat",
1992, 20, "cleaning",
1993, 150, "rubber",
1993, 100, "fruit",
1993, 3, "containers")
df2 <- df2 %>% select(!Year)
# ---- drilldown using df1 and df2
hc <- highchart() %>%
hc_title(text = "Basic Drilldown") %>%
hc_xAxis(type = "category") %>%
hc_legend(enabled = FALSE) %>%
hc_plotOptions(
series = list(
borderWidth = 0,
dataLabels = list(enabled = FALSE)
),
column = list(stacking = "normal")
) %>%
hc_add_series(
data = df1,
type = "column",
hcaes(name = "Year", y = Total/3),
name = "Type",
colorByPoint=TRUE
)
drilldown1 <- df2[c(1,4,7,10),]
drilldown2 <- df2[c(2,5,8,11),]
drilldown3 <- df2[c(3,6,9,12),]
drilldown1 <- list_parse2(drilldown1)
drilldown2 <- list_parse2(drilldown2)
drilldown3 <- list_parse2(drilldown3)
hc <- hc %>%
hc_drilldown(
allowPointDrilldown = TRUE,
series = list(
list(
id = "Stationary",
data = drilldown1),
list(
id = "Food",
data = drilldown2
),
list(
id = "Household",
data = drilldown3
)
)
)
Lastly, trying to ensure key aspect is correct and following this example make_3_level_drilldown_highcharter, but not getting the drilldown working.
main_df <- tibble::tribble(
~Year, ~Type, ~Total,
1990, "Primary", 100,
1991, "Primary", 200,
1992, "Primary", 300,
1993, "Primary", 400
)
sub_type_df <- tibble::tribble(
~Year, ~Sub_Type, ~Sub_Count,
1990, "A", 25,
1990, "B", 15,
1990, "C", 15,
1990, "D", 20,
1990, "E", 30,
1991, "A", 50,
1991, "B", 50,
1991, "C", 25,
1991, "D", 25,
1991, "E", 50,
1992, "A", 20,
1992, "B", 70,
1992, "C", 60,
1992, "D", 100,
1992, "E", 50,
1993, "A", 75,
1993, "B", 25,
1993, "C", 150,
1993, "D", 100,
1993, "E", 50,
)
parts_df <- tibble::tribble(
~Year, ~Sub_Type, ~Part, ~Part_Count,
1990, "A", "part-a", 5,
1990, "A", "part-b", 5,
1990, "A", "part-c", 10,
1990, "A", "part-d", 5,
1990, "B", "part-a", 0,
1990, "B", "part-b", 5,
1990, "B", "part-c", 5,
1990, "B", "part-d", 5,
1990, "C", "part-a", 5,
1990, "C", "part-b", 0,
1990, "C", "part-c", 5,
1990, "C", "part-d", 5,
1990, "D", "part-a", 10,
1990, "D", "part-b", 0,
1990, "D", "part-c", 5,
1990, "D", "part-d", 5,
1990, "E", "part-a", 15,
1990, "E", "part-b", 0,
1990, "E", "part-c", 10,
1990, "E", "part-d", 5,
1991, "A", "part-a", 20,
1991, "A", "part-b", 15,
1991, "A", "part-c", 10,
1991, "A", "part-d", 5,
1991, "B", "part-a", 15,
1991, "B", "part-b", 20,
1991, "B", "part-c", 5,
1991, "B", "part-d", 10,
1991, "C", "part-a", 5,
1991, "C", "part-b", 0,
1991, "C", "part-c", 15,
1991, "C", "part-d", 5,
1991, "D", "part-a", 10,
1991, "D", "part-b", 0,
1991, "D", "part-c", 10,
1991, "D", "part-d", 5,
1991, "E", "part-a", 15,
1991, "E", "part-b", 10,
1991, "E", "part-c", 10,
1991, "E", "part-d", 15,
1992, "A", "part-a", 5,
1992, "A", "part-b", 5,
1992, "A", "part-c", 10,
1992, "A", "part-d", 0,
1992, "B", "part-a", 20,
1992, "B", "part-b", 15,
1992, "B", "part-c", 5,
1992, "B", "part-d", 30,
1992, "C", "part-a", 20,
1992, "C", "part-b", 20,
1992, "C", "part-c", 15,
1992, "C", "part-d", 5,
1992, "D", "part-a", 10,
1992, "D", "part-b", 60,
1992, "D", "part-c", 15,
1992, "D", "part-d", 15,
1992, "E", "part-a", 15,
1992, "E", "part-b", 20,
1992, "E", "part-c", 10,
1992, "E", "part-d", 5,
1993, "A", "part-a", 25,
1993, "A", "part-b", 25,
1993, "A", "part-c", 10,
1993, "A", "part-d", 15,
1993, "B", "part-a", 10,
1993, "B", "part-b", 5,
1993, "B", "part-c", 5,
1993, "B", "part-d", 5,
1993, "C", "part-a", 80,
1993, "C", "part-b", 20,
1993, "C", "part-c", 15,
1993, "C", "part-d", 35,
1993, "D", "part-a", 10,
1993, "D", "part-b", 40,
1993, "D", "part-c", 25,
1993, "D", "part-d", 25,
1993, "E", "part-a", 15,
1993, "E", "part-b", 20,
1993, "E", "part-c", 10,
1993, "E", "part-d", 5,
)
full_df <- left_join(main_df, parts_df)
datSum <- full_df |>
summarize(Quantity = sum(Total), .by = Year)
Lvl1dfStatus <-
tibble(
name = datSum$Year,
y = datSum$Quantity,
drilldown = tolower(name)
)
drilldownLvl2 <- lapply(unique(full_df$Year), function(year_level) {
datSum2 <- full_df |>
filter(Year == year_level) |>
summarise(Quantity = sum(Part_Count), .by = Sub_Type)
Lvl2dfStatus <-
tibble(
name = datSum2$Sub_Type,
y = datSum2$Quantity,
drilldown = tolower(paste(year_level, name, sep = "_"))
)
list(
id = tolower(year_level),
name = "Type",
type = "column",
data = list_parse(Lvl2dfStatus)
)
})
drilldownLvl3 <- lapply(unique(full_df$Year), function(year_level) {
datSum2 <- full_df |>
filter(Year == year_level)
lapply(unique(datSum2$Sub_Type), function(type_level) {
datSum3 <- datSum2 %>%
filter(Sub_Type == type_level) %>%
summarise(Quantity = sum(Part_Count), .by = Part)
Lvl3dfStatus <-
tibble(name = datSum3$Part, y = datSum3$Quantity)
list(
id = tolower(paste(year_level, type_level, sep = "_")),
name = "Part",
type = "column",
data = list_parse2(Lvl3dfStatus)
)
})
}) |> unlist(recursive = FALSE)
highchart() |>
hc_title(text = "Basic multi-level drilldown") |>
hc_xAxis(type = "category") |>
hc_yAxis(title = list(text = "Count")) |>
hc_add_series(Lvl1dfStatus,
"column",
hcaes(
x = name,
y = y,
name = name,
drilldown = drilldown
),
name = "Year") |>
hc_plotOptions(column = list(stacking = "normal")) |>
hc_drilldown(allowPointDrilldown = TRUE,
series = c(drilldownLvl2, drilldownLvl3))
Solution
It's a little bit more complex with multi-level drilldowns. Below is an example based on your first code how you could wrangle your data in order to obtain suitable lists.
However, notice that a three-level-drilldown is not really needed here since your data only has two kind of types. I included it since the code in your question tries to use multiple drilldown levels.
library(dplyr)
library(highcharter)
df3 <- tibble::tribble(
~Year, ~Full_Total, ~Total, ~Type, ~Sub_Type, ~Count,
1990, 40, 35, "Stationary", "Pencils", 20,
1990, 40, 35, "Stationary", "Rulers", 15,
1990, 40, 5, "Household", "Dishes", 5,
1990, 40, 5, "Household", "Cleaning_products", 0,
1991, 60, 30, "Stationary", "Pencils", 30,
1991, 60, 25, "Stationary", "Rulers", 25,
1991, 60, 0, "Household", "Dishes", 0,
1991, 60, 5, "Household", "Cleaning_products", 5,
1992, 90, 40, "Stationary", "Pencils", 30,
1992, 90, 40, "Stationary", "Rulers", 10,
1992, 90, 50, "Household", "Dishes", 40,
1992, 90, 50, "Household", "Cleaning_products", 10,
)
datSum <- df3 |>
summarize(Quantity = sum(Count), .by = Year)
Lvl1dfStatus <-
tibble(
name = datSum$Year,
y = datSum$Quantity,
drilldown = tolower(name)
)
drilldownLvl2 <- lapply(unique(df3$Year), function(year_level) {
datSum2 <- df3 |>
filter(Year == year_level) |>
summarise(Quantity = sum(Count), .by = Type) |>
arrange(desc(Quantity))
Lvl2dfStatus <-
tibble(
name = datSum2$Type,
y = datSum2$Quantity,
drilldown = tolower(paste(year_level, name, sep = "_"))
)
list(
id = tolower(year_level),
name = "Type",
type = "column",
data = list_parse(Lvl2dfStatus)
)
})
drilldownLvl3 <- lapply(unique(df3$Year), function(year_level) {
datSum2 <- df3 |> filter(Year == year_level)
lapply(unique(datSum2$Type), function(type_level) {
datSum3 <- datSum2 |>
filter(Type == type_level) |>
summarise(Quantity = sum(Count), .by = Sub_Type) |>
arrange(desc(Quantity))
Lvl3dfStatus <-
tibble(name = datSum3$Sub_Type, y = datSum3$Quantity)
list(
id = tolower(paste(year_level, type_level, sep = "_")),
name = "Sub_Type",
type = "column",
data = list_parse2(Lvl3dfStatus)
)
})
}) |> unlist(recursive = FALSE)
highchart() |>
hc_title(text = "Basic multi-level drilldown") |>
hc_xAxis(type = "category") |>
hc_yAxis(title = list(text = "Count")) |>
hc_add_series(Lvl1dfStatus,
"column",
hcaes(
x = name,
y = y,
name = name,
drilldown = drilldown
),
name = "Year") |>
hc_plotOptions(column = list(stacking = "normal")) |>
hc_drilldown(allowPointDrilldown = TRUE,
series = c(drilldownLvl2, drilldownLvl3))
- Issues integrating shinychat into a modular R Shiny app
- barplot multiple aggregation
- Start a PowerShell script in R via system2()
- plot from sankeyNetwork in networkD3 does not show output (issue is not number of unique nodes)
- "Target position can only be set for new windows" in chromote in R
- Extract the correct data type in a PDF table
- Time conversion in R
- Comparing the values of a certain number previous rows with the current row
- Run a single test function in R's testthat
- rpart package installation in R
- An efficient way to assign value based on a min-max range and category
- Change output of the `purrr::map` function
- osmdata_sf returns failed to perform HTTP request curl::curl_fetch_memory() error in R?
- Comparing nls() to nls2() - what am I doing wrong
- How to add "variables grid" below ggplot
- How can I use predefined code snippets outside of code chunks in Quarto within RStudio/Posit?
- Wrap text for collapse rows in KableExtra for a long table in R
- Implementation of Breusch-Pagan test for random effects in plm with unbalanced panels
- Finding a value of a dataset in different ones
- Replicate matrix
- Unexpected results after converting raster data from geographic to projected coordinate system using the terra package
- How to remove rows by condition in R?
- How do I add an alias for magrittr pipe from R in vscode
- Package ‘neuralnet’ in R, rectified linear unit (ReLU) activation function?
- Reticulate with pywin32, a dependency of xlwings, not found when sourcing python script from R
- Sub-subtitle in a graph made with `ggplot2`
- How can I execute a statement and ignore warnings with tryCatch?
- Enumerate events where n consecutive values are not NA
- Serialize/deserialize a column with R and DuckDB
- How can I structure my sapflow data to analyze using "TREX" package in R?