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.