r - merge same row of different Vector and multiplicate afterwards -

i have dataset this:

mq = data.frame(model=c("c150a","b174","dg18"),quantity=c(5000,3800,4000)) 

mq data.frame, shows productionplan week in future. model producing model , 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 models produced...

my first problem here is, belonging on productionplan mq, want open automatically needed models, , multiplicate quantity number, know how many of each component(material) needed.

the output data.frame, needed components ( different models can use same components , different components, amount of needed components caan different) on in production plan noted models 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 last step created data.frame should merged material_master data: in material master data, there important components stock noted.

in example there components needed production noted in material master, can in material_master component missing, ignore component.

the output should like, compare needed amount of components, actual stock of them. give report, if there more need actual stock have.

thank help.

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 models , stacks them, along model name. second line merges quantity, , third aggregates.

i went ahead , made usable example trimming off 1 @ beginning of each number in latter models. also, read model , material columns in 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 required stringsasfactors option, identified @ricardosaporta.