Requesting help to progressively update an hierarchical table


# 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.
tibble_names <- ls()[sapply(ls(), function(x) any(]

# 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.

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

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

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

  ~! any(get(.x) %>% pull("Components")  %in% tibble_names)
) %>% set_names(tibble_names)
Comp_1  Comp_11  Comp_15   Comp_2 Widget_1 

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

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.