I am trying to determine the Qty Needed for a forecast on an indented bill of materials (BOM) to do some inventory control. The BOM has multiple levels for subassemblies so the quantity needed for the parent item needs to get multiplied by each level. For example:

Is there a way to calculate the qty needed in R for each line of BOM by bringing in the top level quantity per all the way up to the finished item?

CodePudding user response：

Up front: this is fragile: if row order changes at all, it will break ... silently. If you have any better way to identify groups and subgroups, it would be good to use them.

However, try this

dplyr

library(dplyr)
dat %>%
  group_by(Parent.Item) %>%
  group_by(L1 = cumsum(Level == 1), .add = TRUE) %>%
  mutate(
    Qty = Parent.Qty.Forecast * sapply(Level, \(lvl) prod(Quantity.Per[Level <= lvl]))
  ) %>%
  ungroup()
# # A tibble: 6 x 7
#   Item                                  Level Quantity.Per Parent.Item   Parent.Qty.Forecast    L1   Qty
#   <chr>                                 <int>        <int> <chr>                       <int> <int> <dbl>
# 1 Finished Item                             0            1 Finished Item                  10     0    10
# 2 Subassembly 1                             1            2 Finished Item                  10     1    20
# 3 Component 1 Subassembly 1                 2            5 Finished Item                  10     1   100
# 4 Subassembly 2                             1            4 Finished Item                  10     2    40
# 5 Component 1 Subassembly 2                 2            2 Finished Item                  10     2    80
# 6 Component 1 Component 1 Subassembly 2     3            2 Finished Item                  10     2   160

base R

dat$L1 <- with(dat, ave(Level == 1, Parent.Item, FUN = cumsum))
dat$Qty <- with(dat, ave(seq_len(nrow(dat)), list(Parent.Item, L1), FUN = \(rn) {
  Parent.Qty.Forecast[rn] * sapply(Level[rn], \(lvl) prod(Quantity.Per[rn][Level[rn] <= lvl]))
}))

data.table

library(data.table)
# should use setDT(dat) instead
as.data.table(dat
  )[, L1 := cumsum(Level == 1), by = .(Parent.Item) 
    ][, Qty := Parent.Qty.Forecast * sapply(Level, \(lvl) prod(Quantity.Per[Level <= lvl])),
      by = .(Parent.Item, L1) ]

Data

### without 'Qty.Needed'
dat <- structure(list(Item = c("Finished Item", "Subassembly 1", "Component 1 Subassembly 1", "Subassembly 2", "Component 1 Subassembly 2", "Component 1 Component 1 Subassembly 2"), Level = c(0L, 1L, 2L, 1L, 2L, 3L), Quantity.Per = c(1L, 2L, 5L, 4L, 2L, 2L), Parent.Item = c("Finished Item", "Finished Item", "Finished Item", "Finished Item", "Finished Item", "Finished Item"), Parent.Qty.Forecast = c(10L, 10L, 10L, 10L, 10L, 10L)), row.names = c(NA, -6L), class = "data.frame")
### with 'Qty.Needed`
dat <- structure(list(Item = c("Finished Item", "Subassembly 1", "Component 1 Subassembly 1", "Subassembly 2", "Component 1 Subassembly 2", "Component 1 Component 1 Subassembly 2"), Level = c(0L, 1L, 2L, 1L, 2L, 3L), Quantity.Per = c(1L, 2L, 5L, 4L, 2L, 2L), Parent.Item = c("Finished Item", "Finished Item", "Finished Item", "Finished Item", "Finished Item", "Finished Item"), Parent.Qty.Forecast = c(10L, 10L, 10L, 10L, 10L, 10L), Qty.Needed = c("(1x10) = 10", "(1x2x10)=20", "(1 x 2 x 5 x 10) = 100", "(1 x 4 x 10 ) = 40",  "(1 x 4 x 2 x 10 ) = 80", "(1 x 4 x 2 x 2 x 10 ) = 160")), row.names = c(NA, -6L), class = "data.frame")