merge same row of different Vector and multiplicate afterwards

Question

I have a dataset like this:

MQ = data.frame(Model=c("C150A","B174","DG18"),Quantity=c(5000,3800,4000))

MQ is a data.frame, it shows the Productionplan for a week in the future. With Model producing Model and Quantity

C150A = data.frame( Material=c("A0015", "A0071", "Z00071", "Z00080","Z00090", 
                   "Z00012","SZ0001"), Number=c(1,1,1,1,1,1,4))
B174= data.frame(Material=c("A0014","A0071","Z00080","Z00091","Z00011","SZ0000"), 
                                      Number=c(1,1,1,1,2,4))
DG18= data.frame( Material=c("A0014","A0075","Z00085","Z00090","Z00010","SZ0005"),
                                       Number=c(1,1,1,2,3,4))
T75A= data.frame(Material=c("A0013","A0075","Z00085","Z00090","Z00012","SZ0005"),
                                       Number=c(1,1,1,2,3,4))
G95= data.frame(Material=c("A0013","A0075","Z00085","Z00090","Z00017","SZ0008"),
                                       Number=c(1,1,1,2,3,4))

These are Models which could be produced...

My first problem here is, that belonging on the Productionplan MQ, i want to open automatically the needed Models, and multiplicate the Quantity with the number, to know how many of each Component(Material) is needed.

The output could be a data.frame, where all needed Components ( different Models can use the same Components and different Components, also the amount of needed Components caan be different) over all in the production plan noted Models are combined.

 Material_Master= data.frame( Material=c( "A0013", "A001","A0015", "A0071", "A0075", 
                                "A0078", "Z00071", "Z00080", "Z00090", "Z00091", 
                                "Z00012","Z00091","Z00010""Z00012","Z00017","SZ0001", 
                                "SZ0005","SZ0005","SZ0000","SZ0008","SZ0009"), 
                              Number=c(20000,180000,250000,480000,250000,170000, 
                                       690000,1800000,17000,45000,12000,5000, 5000, 
                                       8000,16000,17000,45000,88000,7500,12000,45000))

In the last step the created data.frame should be merged with the Material_Master data: in the Material Master data, there are all important Components with the stock noted.

In my example there are all Components which where needed for the production also noted in the Material Master, but it can also be that in Material_Master is a Component missing, then just ignore this Component.

The Output should be something like, Compare the needed amount of Components, with the actual stock of them. Give a report, if there is more need then the actual stock have.

Thank you for your help.

Only the first of your models compiles. You have six materials and seven counts for B174 onwards. — Frank, May 14 '13 at 02:01

Frank · Accepted Answer · 2013-05-14T04:28:18.857

This should work:

mods <- do.call(rbind,lapply(MQ$Model,function(x)cbind(Model=x,get(x))))
full_plan <- merge(mods,MQ,by="Model")
material_plan <- with(full_plan,aggregate(Quantity*Number,by=list(Material),sum))
#    Group.1     x
# 1    A0014  7800
# 2    A0015  5000
# 3    A0071  8800
# 4    A0075  4000
# 5   SZ0000 15200
# 6   SZ0001 20000
# 7   SZ0005 16000
# 8   Z00010 12000
# 9   Z00011  7600
# 10  Z00012  5000
# 11  Z00071  5000
# 12  Z00080  8800
# 13  Z00085  4000
# 14  Z00090 13000
# 15  Z00091  3800

The first line gets each of your models and stacks them, along with the model name. The second line merges back to get the Quantity, and the third aggregates.

I went ahead and made a usable example by trimming off the 1 at the beginning of each Number in your latter models. Also, I read the Model and Material columns in as character instead of factor.

options(stringsAsFactors=FALSE)
MQ = data.frame(Model=c("C150A","B174","DG18"),Quantity=c(5000,3800,4000))

C150A = data.frame(Material=c("A0015","A0071","Z00071","Z00080","Z00090","Z00012","SZ0001"),Number=c(1,1,1,1,1,1,4))
B174= data.frame(Material=c("A0014","A0071","Z00080","Z00091","Z00011","SZ0000"), Number=c(1,1,1,1,2,4))
DG18= data.frame(Material=c("A0014","A0075","Z00085","Z00090","Z00010","SZ0005"),Number=c(1,1,1,2,3,4))
T75A= data.frame(Material=c("A0013","A0075","Z00085","Z00090","Z00012","SZ0005"),Number=c(1,1,1,2,3,4))
G95= data.frame(Material=c("A0013","A0075","Z00085","Z00090","Z00017","SZ0008"),Number=c(1,1,1,2,3,4))

Edit: Added the required stringsAsFactors option, as identified by @RicardoSaporta.

First, Thank you for your answer. i got a report, that the first argument is invalid.. any idea? — Daniel, May 14 '13 at 03:07
@Daniel Hmm, I'm not sure what that is. When did it show up (after which step)? I see that you haven't changed your models above, so maybe that's the problem. For example, if you try to run the code for B174, it will fail because Number is longer than Material in the command you have written. I'll post the lines I used instead. — Frank, May 14 '13 at 03:17
Now i edited. that was not the problem, after the first row of your command, i get this failure message.. Thank you for your help — Daniel, May 14 '13 at 03:56
@Daniel, try breaking it down to see where the error is. Run `lapply(MQ$Model,function(x)cbind(Model=x,get(x)))` Does that give you an error? Try just `MQ$Model`, etc. — Ricardo Saporta, May 14 '13 at 03:58
However, I would guess that `MQ$Model` is a factor and not a character and hence the error is being cause at the `get` statement. Run `is.factor(MQ$Model)` and see if that gives you `TRUE`. If so, the solution is to convert to character. You can do it permanently for the data or just here: `lapply(as.character(MQ$Model),function(x)cbind(Model=x,get(x)))` — Ricardo Saporta, May 14 '13 at 03:59

merge same row of different Vector and multiplicate afterwards

1 Answers1