{"action":"opened","issue":{"url":"https://api.github.com/repos/SunitaKosaraju/Caterpillar-tube-pricing-strategy1/issues/1","labels_url":"https://api.github.com/repos/SunitaKosaraju/Caterpillar-tube-pricing-strategy1/issues/1/labels{/name}","comments_url":"https://api.github.com/repos/SunitaKosaraju/Caterpillar-tube-pricing-strategy1/issues/1/comments","events_url":"https://api.github.com/repos/SunitaKosaraju/Caterpillar-tube-pricing-strategy1/issues/1/events","html_url":"https://github.com/SunitaKosaraju/Caterpillar-tube-pricing-strategy1/issues/1","id":104357312,"number":1,"title":"Code!","user":{"login":"SunitaKosaraju","id":13601459,"avatar_url":"https://avatars.githubusercontent.com/u/13601459?v=3","gravatar_id":"","url":"https://api.github.com/users/SunitaKosaraju","html_url":"https://github.com/SunitaKosaraju","followers_url":"https://api.github.com/users/SunitaKosaraju/followers","following_url":"https://api.github.com/users/SunitaKosaraju/following{/other_user}","gists_url":"https://api.github.com/users/SunitaKosaraju/gists{/gist_id}","starred_url":"https://api.github.com/users/SunitaKosaraju/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SunitaKosaraju/subscriptions","organizations_url":"https://api.github.com/users/SunitaKosaraju/orgs","repos_url":"https://api.github.com/users/SunitaKosaraju/repos","events_url":"https://api.github.com/users/SunitaKosaraju/events{/privacy}","received_events_url":"https://api.github.com/users/SunitaKosaraju/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-09-01T21:22:38Z","updated_at":"2015-09-01T21:22:38Z","closed_at":null,"body":"#Sunita/Cesar- CATERPILLAR 28-08-2015\r\n\r\ntraincat <- read.csv(\"train_set.csv\", header=TRUE)\r\ntestcat <- read.csv(\"test_set.csv\", header=TRUE)\r\ntube <- read.csv(\"tube.csv\", header=TRUE)\r\n\r\nmergetubetrain <- merge(traincat, tube, by=\"tube_assembly_id\")\r\nmergetubetest <- merge(testcat,tube,by=\"tube_assembly_id\")\r\n\r\nstr(mergetubetest)\r\n\r\n\r\n\r\n#feature adjustment\r\nmergetubetrain$log.quantity<- log(mergetubetrain$quantity)\r\nmergetubetrain$log.cost<- log(mergetubetrain$cost)\r\n\r\n\r\n#split data into train and test set so that we can calculate RMSE for our supplier analysis.\r\ninstall.packages(\"caTools\")\r\nlibrary(\"caTools\")\r\nset.seed(100)\r\nsplit = sample.split(mergetubetrain$log.cost, SplitRatio = 0.75) \r\ntraining = subset(mergetubetrain, split==TRUE)\r\ntesting= subset(mergetubetrain, split==FALSE)\r\n\r\n#subset suppliers\r\n#merge by major suppliers\r\n#From a previous exploration, we saw that the R2 improves when we build the linear regression model to correspond to a given supplier.  But we must be careful not to overfit. From the data we see that there are XX major suppliers.  Let us match a linear model to these suppliers and then make an average model for the other suppliers.\r\n\r\ntable(mergetubetrain$supplier)\r\n\r\n#We see that Suppliers 66, 41, 72 are the top three suppliers, accounting for just over 85% of the data with (S-66 accounting for 68%, S-41 accounting for 11%, and S-72 accounting for 7.6% of the data.) \r\n#Thus we will create individual linear regression models for these \"major suppliers.\"\r\n#For the other suppliers, we will allow these tube costs to be predicted by a general linear regression model. This group of \"non-major suppliers\" accounts for 4020 records (or just under 15% of the data)\r\n#Note for Caesar, Sunita did this analysis in Excel.\r\n\r\n#Major suppliers\r\n## S-066\r\nmergetubetrain_66<-subset(training,supplier==\"S-0066\")\r\nmergetubetest_66<-subset(testing,supplier==\"S-0066\")\r\n\r\n#this model uses a log of cost\r\nmodel66<-lm(log.cost~annual_usage +min_order_quantity +log.quantity +diameter +wall +length +num_bends +num_boss +other, data=mergetubetrain_66)\r\n\r\nsummary(model66)\r\n\r\n##This results in a model that is R2, 70%\r\n#Calculate RSME\r\npredictTest66<-predict(model66,newdata=mergetubetest_66,type=\"response\")\r\nstr(predictTest66)\r\nRMSE=sqrt(mean((predictTest66-mergetubetest_66$log.cost)^2))\r\nRMSE\r\nexp(RMSE)\r\n#this results in an RMSE of 0.4 (for log.cost), which is 1.49 for cost.  (i.e.$1.49)  This could be considered pretty high given that some tubes are only $2.50 (when bought in bulk)\r\n\r\n#I did similar calculations for S-041 and S-72.  I found R2 of 45% and 29% respectively.  On this basis, I decided not to further segment the data.\r\n#Instead, I think we will be better off making a general model for non-S-066 suppliers.  See below!\r\n\r\n##General LM model for all suppliers.\r\nmergetubetrain_OtherSuppliersOnly<-subset(training,!(supplier%in%\"S-0066\"))\r\nmergetubetest_OtherSuppliersOnly<-subset(testing,!(supplier%in%\"S-0066\"))\r\n\r\n\r\ngeneral_model<-lm(log.cost~annual_usage +min_order_quantity +log.quantity +diameter +wall +length  + num_bracket +num_boss +other, data=mergetubetrain_OtherSuppliersOnly)\r\n\r\nsummary(general_model)\r\n\r\npredictTest<-predict(general_model,newdata=mergetubetest_OtherSuppliersOnly,type=\"response\")\r\nsqrt(mean((predictTest-mergetubetest_OtherSuppliersOnly$log.cost)^2))\r\nexp(sqrt(mean((predictTest-mergetubetest_OtherSuppliersOnly$log.cost)^2)))\r\n##results in an R2 of 35%, and RMSE of $1.96 for cost\r\n\r\n#CREATE A PREDICTION FOR SUBMISSION\r\n#This prediction includes use of a unique model for the largest supplier, and a general model for the others\r\nmergetubetest <- merge(testcat,tube,by=\"tube_assembly_id\")\r\nmergetubetest$log.quantity<- log(mergetubetest$quantity)\r\n\r\ntestcat_Supplier66only<-subset(mergetubetest,supplier==\"S-0066\") #this is 20547 obs\r\ntestcat_AllOtherSuppliers<-subset(mergetubetest,!(supplier%in%\"S-0066\")) #this is 9688 obs\r\n\r\n#here are the relevant predictions for each of our subsets of data (S-66 and non-S-66).  \r\n#But I don't know how to take care of the log manipulations that we've done to the data...\r\n#I also don't know how to combine the predictions to make a set for submission...\r\npredict_forSupplier66only<-predict(model66,newdata=testcat_Supplier66only,type=\"response\")\r\npredict_forAllOtherSuppliers<-predict(general_model,newdata=testcat_AllOtherSuppliers,type=\"response\")\r\n\r\npredict_forSupplier66only<-predict(model66,newdata=testcat_Supplier66only,type=\"response\")\r\npredict_forAllOtherSuppliers<-predict(general_model,newdata=testcat_AllOtherSuppliers,type=\"response\")\r\n\r\n\r\nfinal <-cbind(predict_forSupplier66only,predict_forAllOtherSuppliers)\r\n\r\nfinal <-rbind(predict_forSupplier66only,predict_forAllOtherSuppliers)\r\n\r\npredict_forSupplier66only[20548:30235] <- predict_forAllOtherSuppliers\r\nnames(predict_forSupplier66only[20548:30235]) <- names(predict_forAllOtherSuppliers)\r\nAllpredictions<-c(predict_forSupplier66only, predict_forAllOtherSuppliers)\r\nAllpredictions<-exp(Allpredictions)\r\nwrite.csv(Allpredictions, \"predictf2.csv\")\r\n\r\n"}}