我有一个由三列组成的组件列表:产品、组件和使用的组件数量:
a <- structure(list(prodName = c("prod1", "prod1", "prod2", "prod3",
"prod3", "int1", "int1", "int2", "int2"), component = c("a",
"int1", "b", "b", "int2", "a", "b", "int1", "d"), qty = c(1L,
2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L)), row.names = c(NA, -9L), class = c("data.table",
"data.frame"))
prodName component qty
1 prod1 a 1
2 prod1 int1 2
3 prod2 b 3
4 prod3 b 4
5 prod3 int2 5
6 int1 a 6
7 int1 b 7
8 int2 int1 8
9 int2 d 9
名称以prod
开头的产品是最终产品,名称类似于int
的产品是中间产品,而只有字母的产品是原材料。
我需要所有仅由原材料组成的最终产品的完整组件列表。也就是说,我希望将任何int
转换为原材料。
- 中间产品可以由原材料和另一个中间产品组成,因此我称之为“递归”。
- 我无法提前知道中间产品的嵌套/递归级别(在此示例中为2级,在实际数据中超过6级)。
对于此示例,我的预期结果为(我明确说明了所得出的数字计算):
prodName |component |qty
prod1 |a |1+2*6 = 13
prod1 |b |0+2*7 = 14
prod2 |b |3
prod3 |b |4+5*8*7 = 284
prod3 |a |0+5*8*6 = 240
prod3 |d |0+5*9 = 45
我的工作:
我通过使用merge
创建了一系列繁琐的连接来解决这个问题。虽然这种方法对于玩具数据有效,但我无法将其应用于真实数据。
#load data.table
library(data.table)
# split the tables between products and different levels of intermediate
a1 <- a[prodName %like% "prod",]
b1 <- a[prodName %like% "int1",]
c1 <- a[prodName %like% "int2",]
# convert int2 to raw materials
d1 <- merge(c1,
b1,
by.x = "component",
by.y = "prodName",
all.x = TRUE)[
is.na(component.y),
component.y := component][
is.na(qty.y),
qty.y := 1][,
.(prodName, qty = qty.x*qty.y),
by = .(component = component.y)]
# Since int1 is already exploded into raw materials, rbind both tables:
d1 <- rbind(d1, b1)
# convert all final products into raw materials, except that the raw mats that go directly into the product won't appear:
e1 <- merge(a1,
d1,
by.x = "component",
by.y = "prodName",
all.x = TRUE)
# rbind the last calculated raw mats (those coming from intermediate products) with those coming _directly_ into the final product:
result <- rbind(e1[!is.na(qty.y),
.(prodName, qty = qty.x * qty.y),
by = .(component = component.y)],
e1[is.na(qty.y),
.(prodName, component, qty = qty.x)])[,
.(qty = sum(qty)),
keyby = .(prodName, component)]
我知道我可以将数据分成表格,并进行连接,直到每个中间产品都只由原材料组成,但如上所述,由于数据的大小和中间产品的递归级别,这将是最后的选择。有没有更容易/更好的方法来执行这种递归连接?
qty
的例子改成不同的数字,比如1:9
(不确定它们是否都可以不同)? - M--