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))
- R visualization for Text survey data
- Apply string split into list
- Displaying multiple tables using Package officer and flextable
- Powerpoint distorting flextable when using {officer}
- Add secondary x-axis at the bottom in ggplot
- Alternatives to if_all and if_any for Data Manipulation in R
- Extracting row names from heatmap
- Exclude column in sapply(dat_clean, FUN = function(x){x / sum(x)}) in R
- From many RData within different subfolders, list only data.frames, select some columns, then rbind them all and unlist
- With agricolae, how to add spatial constraints in experimental designs to prevent adjacent treatments?
- ggplot2: How to force the number of facets with too few plots?
- Contrast between label and background: determine if color is light or dark
- att_gt() from did drops control group due to NA — how to include never-treated units?
- Run external program inside a conda environment in R
- How to prevent row names when creating a data frame from a vector
- Getting namespace from a obo-formatted text file
- Variogram per group
- How to fix the warning in ggplot2: Ignoring unknown aesthetics: label
- Proper syntax for 'curly' brackets after the pipe operator in R
- How do I use ggplot2 to manually assign hexadecimal colors to each data point in a plot or bar graph?
- How do I add counts to a stacked bar graph?
- Relative frequencies of multiple variables in R
- Exploratory plot for topic analysis in R
- Is there a way in R to specify the space between to make my X Axes fit my map?
- ggplot2 geom_textpath spiral with datetime axis
- Arrange decomposition plot vertically
- Rcompanion CldList showing the wrong letter assignments to what my pairwise dunntest results are
- system shows two paths concurrently in > .libPaths()-Windows OS, R 4.3.2
- Need specific graphic output of years and percentages
- Error - R 4.2.2 for macOS (ARM64) can’t be installed on this computer