Requesting help to progressively update an hierarchical table

[jovenden]

library(tidyverse)
library(dplyr)

# Input test data

Item_coster <- tibble::tribble(
                 ~Manufactured_item, ~Components, ~Quantity, ~Cost,
                         "Widget_1",    "Comp_1",        2L,  5.02,
                         "Widget_1",    "Comp_2",        3L,  6.02,
                         "Widget_1",    "Comp_3",        4L,  7.02,
                         "Widget_1",    "Comp_4",        5L,  8.02,
                         "Widget_1",    "Comp_5",        6L,  9.02,
                         "Widget_1",    "Comp_6",        7L, 10.02,
                         "Widget_1",    "Comp_7",        8L, 11.02,
                           "Comp_1",    "Comp_8",        9L, 12.02,
                           "Comp_1",    "Comp_9",       10L, 13.02,
                           "Comp_1",   "Comp_10",       11L, 14.02,
                           "Comp_1",   "Comp_11",       12L, 15.02,
                           "Comp_2",   "Comp_12",       13L, 16.02,
                           "Comp_2",   "Comp_13",       14L, 17.02,
                           "Comp_2",   "Comp_14",       15L, 18.02,
                           "Comp_2",   "Comp_11",       12L, 15.02,
                          "Comp_11",    "Comp_3",        4L,  7.02,
                          "Comp_11",    "Comp_4",        5L,  8.02,
                          "Comp_11",   "Comp_15",        6L,  9.02,
                          "Comp_15",   "Comp_16",        8L,  8.02,
                          "Comp_15",   "Comp_17",        9L,  9.02,
                          "Comp_15",   "Comp_18",       10L, 10.02,
                          "Comp_15",   "Comp_19",       11L, 11.02,
                          "Comp_15",   "Comp_20",       12L, 12.02
                 )

# The input data is an hierarchical bill of materials. In the example data, Widget 1 is made from Components 1 to 7 where
# the quantity and cost of components 1 to 7 is specified in last two columns.
# However, Widget 1 includes Component 1 that is made from Components 8 to 10. Thus, the
# cost of Component 1 needs to be calculated and its cost updated in the Widget 1 component list. Likewise, Widget 1 includes 
# Component 2, whose cost needs to be calculated and updated. However, Component 1 and 2 includes Component 11, whose cost needs
# to be updated. Component 15 is not composed of other components, so its cost can be calculated directly.
# Component 15 is the bottom tier of the hierarchy and Widget 1 is the top tier. The tier levels of other components are not known.
# The aim is to calculate the cost of Widget 1 and the costs of all components.
# My approach is to find the bottom tier and roll up the costs from there.

# Step One - separate dataframe into tibbles (thanks to andresrcs for help)

# Making tibbles of Widgets and Components
Item_coster %>%                                                 
  nest_by(Manufactured_item) %>% 
  mutate(data = set_names(list(data), Manufactured_item)) %>%
  list2env(x = .$data, envir = globalenv())

# Step Two - make a character vector of tibble names in the global environment, after removing original data.
rm("Item_coster")
tibble_names <- ls()[sapply(ls(), function(x) any(is.data.frame(get(x))))]

# Step Three - find the bottom level tibbles (ie. not composed of other components).
# So far, I can do this per tibble. Here, I'm starting with the first tibble.
Widget_1_list <- Widget_1 %>% pull("Components")          
# Then I can either find components in Widget_1 that are elsewhere in the dataframe.
Reduce(intersect, list(Widget_1_list, tibble_names))
# Or, I can report against the Widget_1_list
is.element(Widget_1_list, tibble_names) 
# To find bottom-level tibbles, I need tibbles where intersection is nil or report is FALSE.
# To find all bottom-level tibbles I need to search through all tibbles in the global environment.

# Step Four - once found, calculate cost of bottom level tibble, then add into other tibbles where needed.
# Code not included - but not relevant until I can sort out the hierarchy problem.

# I'm requesting help to find the bottom-level tibbles and advice about rolling-up the costs to higher level tibbles. Many thanks for your help in advance.

[nirgrahamuk]

@jovenden

Component 15 is not composed of other components, so its cost can be calculated directly.

In the provided data Comp_15 is composed of Comps 16 through 20 though ?

[jovenden]

Yes, Comp_15 is composed of of Comp_16 to 20 and its cost can be calculated from that. Sorry, my wording was confusing. I meant to say that Comp_15 is not composed of other components whose cost needs to be calculated first. Best, Jenny

[nirgrahamuk]

I think this is step 3 that you wanted help with.

map_lgl(tibble_names,
  ~! any(get(.x) %>% pull("Components")  %in% tibble_names)
) %>% set_names(tibble_names)

Comp_1  Comp_11  Comp_15   Comp_2 Widget_1 
FALSE    FALSE     TRUE    FALSE    FALSE

[jovenden]

Thanks very much Nir. I really appreciate the time you put into helping me with this. Much appreciated. Best wishes, Jenny