0

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:

Item Level Quantity.Per Parent Item Parent Qty Forecast Qty Needed
Finished Item 0 1 Finished Item 10 (1x10) = 10
Subassembly 1 1 2 Finished Item 10 (1x2x10)=20
Component 1 Subassembly 1 2 5 Finished Item 10 (1 x 2 x 5 x 10) = 100
Subassembly 2 1 4 Finished Item 10 (1 x 4 x 10 ) = 40
Component 1 Subassembly 2 2 2 Finished Item 10 (1 x 4 x 2 x 10 ) = 80
Component 1 Component 1 Subassembly 2 3 2 Finished Item 10 (1 x 4 x 2 x 2 x 10 ) = 160

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?

Deez
  • 25
  • 4
  • I can see how to tie each row to the top-level parent row (via `Parent Item`), but how do I know concretely that row 5's immediate parent is row 4? – r2evans Jan 07 '22 at 17:38
  • I agree, that is a complex issue. The only way to tie it up to the parent is to link it to the level up, so the calculation would stop when level above < level below. – Deez Jan 07 '22 at 18:02
  • A [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) would be helpful. – A. S. K. Jan 07 '22 at 18:18

1 Answers1

0

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")
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • Thank you r2evans! This is beautiful. I think in dplyr I can add a column for the BOM using seq() and arrange to preserve order of rows for the BOM. – Deez Jan 07 '22 at 23:23