API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 29th, 2015
230
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 138.84 KB | None | 0 0
raw download clone embed print report
  1. # --------------------------------------------------------------------------------------------------------------
  2. # --------------------------------------------------------------------------------------------------------------
  3. # --------------------------------------------------------------------------------------------------------------
  4. # || Exploratory Data Analysis ||
  5. # --------------------------------------------------------------------------------------------------------------
  6. # --------------------------------------------------------------------------------------------------------------
  7. # --------------------------------------------------------------------------------------------------------------
  8.  
  9. setwd("C:/Yashwanth/Pricing/1.OfficeDepot/4.EDA/")
  10.  
  11. OD.Commercial <- read.table("Commercial.csv",
  12. header = TRUE, sep = ",", quote = "\"", dec = "." ,fill=TRUE, comment.char="", as.is=TRUE)
  13.  
  14. OD.Commercial <- read.table("C:/Yashwanth/Pricing/1.OfficeDepot/8.Data Output/1.Commercial/Commercial_Output.csv",
  15. header = TRUE, sep = ",", quote = "\"", dec = "." ,fill=TRUE, comment.char="", as.is=TRUE)
  16.  
  17. str(OD.Commercial)
  18. OD.Commercial$SKU <- as.factor(OD.Commercial$SKU);OD.Commercial$weekno <- as.factor(OD.Commercial$weekno)
  19.  
  20. # --------------------------------------------------------------------------------------------------------------
  21. # || Remove -ve Sales ||
  22. # --------------------------------------------------------------------------------------------------------------
  23.  
  24. OD.Commercial <- data.frame(subset(OD.Commercial,sales_units>0,names(OD.Commercial)),row.names=NULL)
  25.  
  26. # --------------------------------------------------------------------------------------------------------------
  27. # || Sales Trend ||
  28. # --------------------------------------------------------------------------------------------------------------
  29.  
  30. plot(OD.Commercial$weekno,OD.Commercial$sales_units,type="l")
  31.  
  32. #---------------------------------------------------------------------------------------------------------------
  33. # Descriptive Statistics
  34. #---------------------------------------------------------------------------------------------------------------
  35.  
  36. summary(OD.Commercial[sapply(OD.Commercial,is.numeric)]) # Select only numeric columns
  37.  
  38. #---------------------------------------------------------------------------------------------------------------
  39. # Distribution of Variables : Histogram
  40. #---------------------------------------------------------------------------------------------------------------
  41. # Office Depot
  42. par(mfrow=c(1,2))
  43. hist(OD.Commercial$sales_units,prob=T,xlab="Sales Units",main="Sales Units distribution")
  44. curve(dnorm(x,mean=mean(OD.Commercial$sales_units),sd=sd(OD.Commercial$sales_units)),add=TRUE)
  45. hist(log(OD.Commercial$sales_units),prob=T,xlab="log(Sales Units)",main="Sales Units distribution")
  46. curve(dnorm(x,mean=mean(log(OD.Commercial$sales_units)),sd=sd(log(OD.Commercial$sales_units))),add=TRUE)
  47.  
  48. par(mfrow=c(1,2))
  49. hist(OD.Commercial$od_reg_price,prob=T,xlab="Regular Price",main="Regular Price distribution")
  50. curve(dnorm(x,mean=mean(OD.Commercial$od_reg_price),sd=sd(OD.Commercial$od_reg_price)),add=TRUE)
  51. hist(log(OD.Commercial$od_reg_price),prob=T,xlab="log(Regular Price)",main="Regular Price distribution")
  52. curve(dnorm(x,mean=mean(log(OD.Commercial$od_reg_price)),sd=sd(log(OD.Commercial$od_reg_price))),add=TRUE)
  53.  
  54. par(mfrow=c(1,2))
  55. hist(OD.Commercial$od_ship_price,prob=T,xlab="Shipping Price",main="Shipping Price distribution")
  56. curve(dnorm(x,mean=mean(OD.Commercial$od_ship_price),sd=sd(OD.Commercial$od_ship_price)),add=TRUE)
  57. hist(log(OD.Commercial$od_ship_price),prob=T,xlab="log(Shipping Price)",main="Shipping Price distribution")
  58. curve(dnorm(x,mean=mean(log(OD.Commercial$od_ship_price)),sd=sd(log(OD.Commercial$od_ship_price))),add=TRUE)
  59.  
  60. par(mfrow=c(1,2))
  61. hist(OD.Commercial$od_discount,prob=T,xlab="Discount",main="Discount distribution")
  62. curve(dnorm(x,mean=mean(OD.Commercial$od_discount),sd=sd(OD.Commercial$od_discount)),add=TRUE)
  63. hist(log(subset(OD.Commercial,od_discount>0,od_discount))[,1],prob=T,
  64. xlab="log(Discount)",main="Discount distribution")
  65. curve(dnorm(x,mean=mean(log(subset(OD.Commercial,od_discount>0,od_discount))[,1]),
  66. sd=sd(log(subset(OD.Commercial,od_discount>0,od_discount))[,1])),add=TRUE)
  67.  
  68. par(mfrow=c(1,2))
  69. hist(OD.Commercial$od_final_price,prob=T,xlab="Final Price",main="OD Final price distribution")
  70. curve(dnorm(x,mean=mean(OD.Commercial$od_final_price),sd=sd(OD.Commercial$od_final_price)),add=TRUE)
  71. hist(log(OD.Commercial$od_final_price),prob=T,xlab="log(Final Price)",main="OD Final price distribution")
  72. curve(dnorm(x,mean=mean(log(OD.Commercial$od_final_price)),sd=sd(log(OD.Commercial$od_final_price))),add=TRUE)
  73.  
  74.  
  75. # Amazon
  76. par(mfrow=c(1,2))
  77. hist(OD.Commercial$Amz_reg_price,prob=T,xlab="Regular Price",main="Regular Price distribution")
  78. curve(dnorm(x,mean=mean(OD.Commercial$Amz_reg_price,na.rm=T),sd=sd(OD.Commercial$Amz_reg_price,na.rm=T)),add=TRUE)
  79. hist(log(OD.Commercial$Amz_reg_price),prob=T,xlab="log(Regular Price)",main="Regular Price distribution")
  80. curve(dnorm(x,mean=mean(log(OD.Commercial$Amz_reg_price),na.rm=T),sd=sd(log(OD.Commercial$Amz_reg_price),na.rm=T)),add=TRUE)
  81.  
  82. par(mfrow=c(1,2))
  83. hist(OD.Commercial$Amz_ship_price,prob=T,xlab="Shipping Price",main="Shipping Price distribution")
  84. curve(dnorm(x,mean=mean(OD.Commercial$Amz_ship_price,na.rm=T),sd=sd(OD.Commercial$Amz_ship_price,na.rm=T)),add=TRUE)
  85. hist(log(subset(OD.Commercial,Amz_ship_price>0,Amz_ship_price))[,1],prob=T,xlab="log(Shipping Price)",
  86. main="Shipping Price distribution")
  87. curve(dnorm(x,mean=mean(log(subset(OD.Commercial,Amz_ship_price>0,Amz_ship_price))[,1],na.rm=T),
  88. sd=sd(log(subset(OD.Commercial,Amz_ship_price>0,Amz_ship_price))[,1],na.rm=T)),add=TRUE)
  89.  
  90. par(mfrow=c(1,2))
  91. hist(OD.Commercial$Amz_discount,prob=T,xlab="Discount",main="Discount distribution")
  92. curve(dnorm(x,mean=mean(OD.Commercial$Amz_discount,na.rm=T),sd=sd(OD.Commercial$Amz_discount,na.rm=T)),add=TRUE)
  93. hist(log(subset(OD.Commercial,Amz_discount>0,Amz_discount))[,1],prob=T,
  94. xlab="log(Discount)",main="Discount distribution")
  95. curve(dnorm(x,mean=mean(log(subset(OD.Commercial,Amz_discount>0,Amz_discount))[,1],na.rm=T),
  96. sd=sd(log(subset(OD.Commercial,Amz_discount>0,Amz_discount))[,1],na.rm=T)),add=TRUE)
  97.  
  98. par(mfrow=c(1,2))
  99. hist(OD.Commercial$Amz_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  100. curve(dnorm(x,mean=mean(OD.Commercial$Amz_final_price,na.rm=T),sd=sd(OD.Commercial$Amz_final_price,na.rm=T)),add=TRUE)
  101. hist(log(OD.Commercial$Amz_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  102. curve(dnorm(x,mean=mean(log(OD.Commercial$Amz_final_price),na.rm=T),sd=sd(log(OD.Commercial$Amz_final_price),na.rm=T)),add=TRUE)
  103.  
  104.  
  105. # Amazon Marketplace
  106. par(mfrow=c(1,2))
  107. hist(OD.Commercial$Amz_mkt_reg_price,prob=T,xlab="Regular Price",main="Regular Price distribution")
  108. curve(dnorm(x,mean=mean(OD.Commercial$Amz_mkt_reg_price,na.rm=T),sd=sd(OD.Commercial$Amz_mkt_reg_price,na.rm=T)),add=TRUE)
  109. hist(log(OD.Commercial$Amz_mkt_reg_price),prob=T,xlab="log(Regular Price)",main="Regular Price distribution")
  110. curve(dnorm(x,mean=mean(log(OD.Commercial$Amz_mkt_reg_price),na.rm=T),sd=sd(log(OD.Commercial$Amz_mkt_reg_price),na.rm=T)),add=TRUE)
  111.  
  112. par(mfrow=c(1,2))
  113. hist(OD.Commercial$Amz_mkt_ship_price,prob=T,xlab="Shipping Price",main="Shipping Price distribution")
  114. curve(dnorm(x,mean=mean(OD.Commercial$Amz_mkt_ship_price,na.rm=T),sd=sd(OD.Commercial$Amz_mkt_ship_price,na.rm=T)),add=TRUE)
  115. hist(log(subset(OD.Commercial,Amz_mkt_ship_price>0,Amz_mkt_ship_price))[,1],prob=T,xlab="log(Shipping Price)",
  116. main="Shipping Price distribution")
  117. curve(dnorm(x,mean=mean(log(subset(OD.Commercial,Amz_mkt_ship_price>0,Amz_mkt_ship_price))[,1],na.rm=T),
  118. sd=sd(log(subset(OD.Commercial,Amz_mkt_ship_price>0,Amz_mkt_ship_price))[,1],na.rm=T)),add=TRUE)
  119.  
  120. par(mfrow=c(1,2))
  121. hist(OD.Commercial$Amz_mkt_discount,prob=T,xlab="Discount",main="Discount distribution")
  122. curve(dnorm(x,mean=mean(OD.Commercial$Amz_mkt_discount,na.rm=T),sd=sd(OD.Commercial$Amz_mkt_discount,na.rm=T)),add=TRUE)
  123. hist(log(subset(OD.Commercial,Amz_mkt_discount>0,Amz_mkt_discount))[,1],prob=T,
  124. xlab="log(Discount)",main="Discount distribution")
  125. curve(dnorm(x,mean=mean(log(subset(OD.Commercial,Amz_mkt_discount>0,Amz_mkt_discount))[,1],na.rm=T),
  126. sd=sd(log(subset(OD.Commercial,Amz_mkt_discount>0,Amz_mkt_discount))[,1],na.rm=T)),add=TRUE)
  127.  
  128. par(mfrow=c(1,2))
  129. hist(OD.Commercial$Amz_mkt_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  130. curve(dnorm(x,mean=mean(OD.Commercial$Amz_mkt_final_price,na.rm=T),sd=sd(OD.Commercial$Amz_mkt_final_price,na.rm=T)),add=TRUE)
  131. hist(log(OD.Commercial$Amz_mkt_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  132. curve(dnorm(x,mean=mean(log(OD.Commercial$Amz_mkt_final_price),na.rm=T),sd=sd(log(OD.Commercial$Amz_mkt_final_price),na.rm=T)),add=TRUE)
  133.  
  134. # Staples
  135. par(mfrow=c(1,2))
  136. hist(OD.Commercial$staples_reg_price,prob=T,xlab="Regular Price",main="Regular Price distribution")
  137. curve(dnorm(x,mean=mean(OD.Commercial$staples_reg_price,na.rm=T),sd=sd(OD.Commercial$staples_reg_price,na.rm=T)),add=TRUE)
  138. hist(log(OD.Commercial$staples_reg_price),prob=T,xlab="log(Regular Price)",main="Regular Price distribution")
  139. curve(dnorm(x,mean=mean(log(OD.Commercial$staples_reg_price),na.rm=T),sd=sd(log(OD.Commercial$staples_reg_price),na.rm=T)),add=TRUE)
  140.  
  141. par(mfrow=c(1,2))
  142. hist(OD.Commercial$staples_ship_price,prob=T,xlab="Shipping Price",main="Shipping Price distribution")
  143. curve(dnorm(x,mean=mean(OD.Commercial$staples_ship_price,na.rm=T),sd=sd(OD.Commercial$staples_ship_price,na.rm=T)),add=TRUE)
  144. hist(log(subset(OD.Commercial,staples_ship_price>0,staples_ship_price))[,1],prob=T,xlab="log(Shipping Price)",
  145. main="Shipping Price distribution")
  146. curve(dnorm(x,mean=mean(log(subset(OD.Commercial,staples_ship_price>0,staples_ship_price))[,1],na.rm=T),
  147. sd=sd(log(subset(OD.Commercial,staples_ship_price>0,staples_ship_price))[,1],na.rm=T)),add=TRUE)
  148.  
  149. par(mfrow=c(1,2))
  150. hist(OD.Commercial$staples_discount,prob=T,xlab="Discount",main="Discount distribution")
  151. curve(dnorm(x,mean=mean(OD.Commercial$staples_discount,na.rm=T),sd=sd(OD.Commercial$staples_discount,na.rm=T)),add=TRUE)
  152. hist(log(subset(OD.Commercial,staples_discount>0,staples_discount))[,1],prob=T,
  153. xlab="log(Discount)",main="Discount distribution")
  154. curve(dnorm(x,mean=mean(log(subset(OD.Commercial,staples_discount>0,staples_discount))[,1],na.rm=T),
  155. sd=sd(log(subset(OD.Commercial,staples_discount>0,staples_discount))[,1],na.rm=T)),add=TRUE)
  156.  
  157. par(mfrow=c(1,2))
  158. hist(OD.Commercial$staples_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  159. curve(dnorm(x,mean=mean(OD.Commercial$staples_final_price,na.rm=T),sd=sd(OD.Commercial$staples_final_price,na.rm=T)),add=TRUE)
  160. hist(log(OD.Commercial$staples_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  161. curve(dnorm(x,mean=mean(log(OD.Commercial$staples_final_price),na.rm=T),sd=sd(log(OD.Commercial$staples_final_price),na.rm=T)),add=TRUE)
  162.  
  163. # Min Competitor Price
  164. par(mfrow=c(1,2))
  165. hist(OD.Commercial$min_comp_price,prob=T,xlab="Min Competitor Price",main="Min Competitor price distribution")
  166. curve(dnorm(x,mean=mean(OD.Commercial$min_comp_price,na.rm=T),sd=sd(OD.Commercial$min_comp_price,na.rm=T)),add=TRUE)
  167. hist(log(OD.Commercial$min_comp_price),prob=T,xlab="log(Min Competitor Price)",main="Min Competitor price distribution")
  168. curve(dnorm(x,mean=mean(log(OD.Commercial$min_comp_price),na.rm=T),sd=sd(log(OD.Commercial$min_comp_price),na.rm=T)),add=TRUE)
  169.  
  170.  
  171. #---------------------------------------------------------------------------------------------------------------
  172. # Correlataion Matrix
  173. #---------------------------------------------------------------------------------------------------------------
  174.  
  175. toMatch_Com <- c("units","final_price","reg_price","pr_diff","pr_rat","min_comp_price","ln")
  176. OD.Commercial[,grep(paste(toMatch_Com,collapse="|"),names(OD.Commercial),value=T)]
  177. OD.Commercial_new <- OD.Commercial[,grep(paste(toMatch_Com,collapse="|"),names(OD.Commercial),value=T)]
  178. OD.Commercial_new <- OD.Commercial_new[,-c(10,11,15,19,25)]
  179.  
  180. cor(OD.Commercial_new,use="na.or.complete")
  181. write.csv(cor(OD.Commercial_new,use="na.or.complete"),
  182. "C:/Yashwanth/Pricing/1.OfficeDepot/4.EDA/1.Correlation/Cor_Commercial.csv")
  183.  
  184. #---------------------------------------------------------------------------------------------------------------
  185. # Frequency Distribution
  186. #---------------------------------------------------------------------------------------------------------------
  187.  
  188. attach(OD.Commercial)
  189. range(sales_units)
  190. OD_Sales_Breaks <- seq(1,120,10)
  191. OD_Sales <- data.frame(table(cut(sales_units,OD_Sales_Breaks,right=TRUE)))
  192. colnames(OD_Sales) <- c("OD_Sales","Freq")
  193. OD_Sales$Cum_Freq <- cumsum(OD_Sales$Freq)
  194. OD_Sales$Percentage <- (OD_Sales$Freq/sum(OD_Sales$Freq))*100
  195. OD_Sales$Cum_Percentage <- cumsum((OD_Sales$Freq/sum(OD_Sales$Freq))*100)
  196.  
  197. range(od_final_price)
  198. OD_FP_Breaks <- seq(90,4900,300)
  199. OD_FP <- data.frame(table(cut(od_final_price,OD_FP_Breaks,right=TRUE)))
  200. colnames(OD_FP) <- c("OD_FP","Freq")
  201. OD_FP$Cum_Freq <- cumsum(OD_FP$Freq)
  202. OD_FP$Percentage <- (OD_FP$Freq/sum(OD_FP$Freq))*100
  203. OD_FP$Cum_Percentage <- cumsum((OD_FP$Freq/sum(OD_FP$Freq))*100)
  204.  
  205.  
  206. range(Amz_final_price,na.rm=T)
  207. AMZ_FP_Breaks <- seq(340,2500,300)
  208. AMZ_FP <- data.frame(table(cut(Amz_final_price,AMZ_FP_Breaks,right=TRUE)))
  209. colnames(AMZ_FP) <- c("AMZ_FP","Freq")
  210. AMZ_FP$Cum_Freq <- cumsum(AMZ_FP$Freq)
  211. AMZ_FP$Percentage <- (AMZ_FP$Freq/sum(AMZ_FP$Freq))*100
  212. AMZ_FP$Cum_Percentage <- cumsum((AMZ_FP$Freq/sum(AMZ_FP$Freq))*100)
  213.  
  214.  
  215. range(Amz_mkt_final_price,na.rm=T)
  216. AMZ_MP_FP_Breaks <- seq(100,4500,300)
  217. AMZ_MP_FP <- data.frame(table(cut(Amz_mkt_final_price,AMZ_MP_FP_Breaks,right=TRUE)))
  218. colnames(AMZ_MP_FP) <- c("AMZ_MP_FP","Freq")
  219. AMZ_MP_FP$Cum_Freq <- cumsum(AMZ_MP_FP$Freq)
  220. AMZ_MP_FP$Percentage <- (AMZ_MP_FP$Freq/sum(AMZ_MP_FP$Freq))*100
  221. AMZ_MP_FP$Cum_Percentage <- cumsum((AMZ_MP_FP$Freq/sum(AMZ_MP_FP$Freq))*100)
  222.  
  223.  
  224. range(staples_final_price,na.rm=T)
  225. Staples_FP_Breaks <- seq(100,4600,400)
  226. Staples_FP <- data.frame(table(cut(staples_final_price,Staples_FP_Breaks,right=TRUE)))
  227. colnames(Staples_FP) <- c("Staples_FP","Freq")
  228. Staples_FP$Cum_Freq <- cumsum(Staples_FP$Freq)
  229. Staples_FP$Percentage <- (Staples_FP$Freq/sum(Staples_FP$Freq))*100
  230. Staples_FP$Cum_Percentage <- cumsum((Staples_FP$Freq/sum(Staples_FP$Freq))*100)
  231. detach(OD.Commercial)
  232.  
  233. write.csv(OD_Sales,"OD_Sales_Tab.csv",row.names=F)
  234. write.csv(OD_FP,"OD_FP_Tab.csv",row.names=F);write.csv(AMZ_FP,"AMZ_FP_Tab.csv",row.names=F)
  235. write.csv(AMZ_MP_FP,"AMZ_MP_FP_Tab.csv",row.names=F);write.csv(Staples_FP,"Staples_FP_Tab.csv",row.names=F)
  236.  
  237. #---------------------------------------------------------------------------------------------------------------
  238. # Scatter Plot
  239. #---------------------------------------------------------------------------------------------------------------
  240.  
  241. for(i in 1:nrow(OD.Commercial)){
  242. OD.Commercial$pr_diff_OD_final_pr[i] <- OD.Commercial$od_final_price[i]-OD.Commercial$od_final_price[i+1]
  243. }
  244. OD.Commercial$pr_diff_OD_final_pr[is.na(OD.Commercial$pr_diff_OD_final_pr)] <- OD.Commercial$pr_diff_OD_final_pr[i-1]
  245.  
  246. attach(OD.Commercial)
  247. par(mfrow=c(1,2),cex.main=1.2)
  248. plot(od_final_price,sales_units,xlab="OD Final Price",ylab="OD Sales",main="OD Sales v/s Price")
  249. abline(lm(sales_units ~ od_final_price))
  250. plot(pr_diff_OD_final_pr,sales_units,xlab="OD Final Price Diff",ylab="OD Sales ",main="OD Sales v/s Price Diff")
  251. abline(lm(sales_units ~ pr_diff_OD_final_pr))
  252.  
  253. par(mfrow=c(1,2),cex.main=1.2)
  254. plot(Amz_final_price,sales_units,xlab="OD Final Price",ylab="OD Sales",main="OD Sales v/s Price")
  255. abline(lm(sales_units ~ Amz_final_price))
  256. plot(pr_diff_amz_final_pr,sales_units,xlab="Amzazon Final Price Diff",ylab="OD Sales ",main="OD Sales v/s Amazon Price Diff")
  257. abline(lm(sales_units ~ pr_diff_amz_final_pr))
  258.  
  259. par(mfrow=c(1,2),cex.main=1.2)
  260. plot(Amz_mkt_final_price,sales_units,xlab="Amazon Marketplace Final Price",ylab="OD Sales",main="OD Sales v/s Amazon Marketplace Price")
  261. abline(lm(sales_units ~ Amz_mkt_final_price))
  262. plot(pr_diff_amz_mkt_final_pr,sales_units,xlab="Amazon Marketplace Final Price Diff",ylab="OD Sales ",main="OD Sales v/s Amazon Marketplace Price Diff")
  263. abline(lm(sales_units ~ pr_diff_amz_mkt_final_pr))
  264.  
  265. par(mfrow=c(1,2),cex.main=1.2)
  266. plot(staples_final_price,sales_units,xlab="Staples Final Price",ylab="OD Sales",main="OD Sales v/s Staples Price")
  267. abline(lm(sales_units ~ staples_final_price))
  268. plot(pr_diff_stpl_final_pr,sales_units,xlab="Staples Final Price Diff",ylab="OD Sales ",main="OD Sales v/s Staples Price Diff")
  269. abline(lm(sales_units ~ pr_diff_stpl_final_pr))
  270.  
  271.  
  272.  
  273. dev.off()
  274. par(mfrow=c(1,2),cex.main=1.2)
  275. plot(Amz_final_price,min_comp_price,xlab="Amazon Final Price",ylab="Min Competitor Price",main="Final Price v/s Min Competitor price")
  276. abline(lm(Amz_final_price ~ min_comp_price))
  277. plot(pr_rat_amz_final_pr,min_comp_price,xlab="Amazon Price Ratio",ylab="Min Competitor Price",main="Price Ratio v/s Min Competitor price")
  278. abline(lm(pr_rat_amz_final_pr ~ min_comp_price))
  279.  
  280. par(mfrow=c(1,2),cex.main=1.2)
  281. plot(Amz_mkt_final_price,min_comp_price,xlab="AmazonMP Final Price",ylab="Min Competitor Price",main="Final Price v/s Min Competitor price")
  282. abline(lm(Amz_mkt_final_price ~ min_comp_price))
  283. plot(pr_rat_amz_mkt_final_pr,min_comp_price,xlab="AmazonMP Price Ratio",ylab="Min Competitor Price",main="Price Ratio v/s Min Competitor price")
  284. abline(lm(pr_rat_amz_mkt_final_pr ~ min_comp_price))
  285.  
  286. par(mfrow=c(1,2),cex.main=1.2)
  287. plot(staples_final_price,min_comp_price,xlab="Staples Final Price",ylab="Min Competitor Price",main="Final Price v/s Min Competitor price")
  288. abline(lm(staples_final_price ~ min_comp_price))
  289. plot(pr_rat_stpl_final_pr,min_comp_price,xlab="AmazonMP Price Ratio",ylab="Min Competitor Price",main="Price Ratio v/s Min Competitor price")
  290. abline(lm(pr_rat_stpl_final_pr ~ min_comp_price))
  291.  
  292.  
  293. dev.off()
  294. plot(min_comp_price,sales_units,xlab="Min Competitor Price",ylab="OD Sales",main="OD Sales v/s Min Competitor Price")
  295. abline(lm(sales_units ~ min_comp_price))
  296.  
  297. plot(min_comp_price,sales_units,xlab="Min Competitor Price",ylab="OD Sales",main="OD Sales v/s Min Competitor Price")
  298. abline(lm(sales_units ~ min_comp_price))
  299.  
  300. detach(OD.Commercial)
  301.  
  302. #---------------------------------------------------------------------------------------------------------------
  303. # Box Plot
  304. #---------------------------------------------------------------------------------------------------------------
  305.  
  306. dev.off()
  307. attach(OD.Commercial)
  308. boxplot(sales_units ~ as.factor(weekno),xlab="Weeks",ylab="OD Sales Units",main="Office Depot")
  309. boxplot(od_final_price ~ as.factor(weekno),xlab="Weeks",ylab="OD Final Price",main="Office Depot")
  310. boxplot(Amz_final_price ~ as.factor(weekno),xlab="Weeks",ylab="Amazon Final Price",main="Amazon")
  311. boxplot(Amz_mkt_final_price ~ as.factor(weekno),xlab="Weeks",ylab="Amazon Marketplace Final Price",main="Amazon Marketplace")
  312. boxplot(staples_final_price ~ as.factor(weekno),xlab="Weeks",ylab="Staples Final Price",main="Staples")
  313.  
  314. detach(OD.Commercial)
  315.  
  316. #---------------------------------------------------------------------------------------------------------------
  317. # Aggregate Data : Price
  318. #---------------------------------------------------------------------------------------------------------------
  319.  
  320. OD.SKU_Summ_OD_FP <- as.data.frame(as.list(aggregate(OD.Commercial$od_final_price ~ OD.Commercial$SKU,
  321. FUN=function(x)c(min=min(x),max=max(x),len=length(x)))))
  322. names(OD.SKU_Summ_OD_FP) <- c("SKU","OD Min Finalprice","OD Max Finalprice","SKU frequency")
  323.  
  324. OD.SKU_Summ_Amz_FP <- as.data.frame(as.list(aggregate(OD.Commercial$Amz_final_price ~ OD.Commercial$SKU,
  325. FUN=function(x)c(min=min(x),max=max(x),len=length(x)))))
  326. names(OD.SKU_Summ_Amz_FP) <- c("SKU","Amazon Min Finalprice","Amazon Max Finalprice","SKU frequency")
  327.  
  328. OD.SKU_Summ_Amz_MP_FP <- as.data.frame(as.list(aggregate(OD.Commercial$Amz_mkt_final_price ~ OD.Commercial$SKU,
  329. FUN=function(x)c(min=min(x),max=max(x),len=length(x)))))
  330. names(OD.SKU_Summ_Amz_MP_FP) <- c("SKU","AmzMP Min Finalprice","AmzMP Max Finalprice","SKU frequency")
  331.  
  332. OD.SKU_Summ_Stap_FP <- as.data.frame(as.list(aggregate(OD.Commercial$staples_final_price ~ OD.Commercial$SKU,
  333. FUN=function(x)c(min=min(x),max=max(x),len=length(x)))))
  334. names(OD.SKU_Summ_Stap_FP) <- c("SKU","Staples Min Finalprice","Staples Max Finalprice","SKU frequency")
  335.  
  336. write.csv(OD.SKU_Summ_OD_FP,".csv",row.names=F);write.csv(,".csv",row.names=F)
  337. write.csv(,".csv",row.names=F);write.csv(,".csv",row.names=F)
  338. write.csv(,".csv",row.names=F)
  339. #---------------------------------------------------------------------------------------------------------------
  340. # Aggregate Data : Sales
  341. #---------------------------------------------------------------------------------------------------------------
  342.  
  343. OD.SKU_Summ_OD_SU <- as.data.frame(as.list(aggregate(OD.Commercial$sales_units ~ OD.Commercial$SKU,
  344. FUN=function(x)c(min=min(x),max=max(x),len=length(x)))))
  345. names(OD.SKU_Summ_OD_SU) <- c("SKU","OD Min Sales Units","OD Max Sales Units","SKU frequency")
  346.  
  347. OD.SKU_Summ_Amz_SU <- as.data.frame(as.list(aggregate(OD.Commercial$sales_units ~ OD.Commercial$SKU,
  348. FUN=function(x)c(min=min(x),max=max(x),len=length(x)))))
  349. names(OD.SKU_Summ_Amz_SU) <- c("SKU","Amazon Min Sales Units","Amazon Max Sales Units","SKU frequency")
  350.  
  351. OD.SKU_Summ_Amz_MP_SU <- as.data.frame(as.list(aggregate(OD.Commercial$sales_units ~ OD.Commercial$SKU,
  352. FUN=function(x)c(min=min(x),max=max(x),len=length(x)))))
  353. names(OD.SKU_Summ_Amz_MP_SU) <- c("SKU","AmzMP Min Sales Units","AmzMP Max Sales Units","SKU frequency")
  354.  
  355. OD.SKU_Summ_Stap_SU <- as.data.frame(as.list(aggregate(OD.Commercial$sales_units ~ OD.Commercial$SKU,
  356. FUN=function(x)c(min=min(x),max=max(x),len=length(x)))))
  357. names(OD.SKU_Summ_Stap_SU) <- c("SKU","Staples Min Sales Units","Staples Max Sales Units","SKU frequency")
  358.  
  359. # --------------------------------------------------------------------------------------------------------------
  360. # --------------------------------------------------------------------------------------------------------------
  361. # || Missing Value Imputation ||
  362. # --------------------------------------------------------------------------------------------------------------
  363. # --------------------------------------------------------------------------------------------------------------
  364.  
  365. write.csv(OD.Commercial[,c(1:3,grep("final_price",names(OD.Commercial)))],
  366. "C:/Yashwanth/Pricing/1.OfficeDepot/8.Data Output/OD.Commecial_Output2.csv",row.names=FALSE)
  367.  
  368. OD.Commercial_IP <- read.table("C:/Yashwanth/Pricing/1.OfficeDepot/8.Data Output/1.Commercial/OD.Commecial_Output2.csv",
  369. header = TRUE, sep = ",", quote = "\"", dec = "." ,fill=TRUE, comment.char="", as.is=TRUE)
  370. OD.Commercial_IP <- data.frame(subset(OD.Commercial_IP,sales_units>0,names(OD.Commercial_IP)),row.names=NULL)
  371.  
  372. OD.Commercial_IP$pr_diff_amz_final_pr <- round(OD.Commercial_IP$od_final_price-OD.Commercial_IP$Amz_final_price,2)
  373. OD.Commercial_IP$pr_diff_amz_mkt_final_pr <- round(OD.Commercial_IP$od_final_price-OD.Commercial_IP$Amz_mkt_final_price,2)
  374. OD.Commercial_IP$pr_diff_stpl_final_pr <- round(OD.Commercial_IP$od_final_price-OD.Commercial_IP$staples_final_price,2)
  375.  
  376. OD.Commercial_IP$pr_rat_amz_final_pr <- round(OD.Commercial_IP$od_final_price/OD.Commercial_IP$Amz_final_price,2)
  377. OD.Commercial_IP$pr_rat_amz_mkt_final_pr <- round(OD.Commercial_IP$od_final_price/OD.Commercial_IP$Amz_final_price,2)
  378. OD.Commercial_IP$pr_rat_stpl_final_pr <- round(OD.Commercial_IP$od_final_price/OD.Commercial_IP$Amz_final_price,2)
  379.  
  380. OD.Commercial_IP$ln_sales_units <- round(log(OD.Commercial_IP$sales_units),2)
  381. OD.Commercial_IP$ln_od_final_pr <- round(log(OD.Commercial_IP$od_final_price),2)
  382. OD.Commercial_IP$ln_amz_final_pr <- round(log(OD.Commercial_IP$Amz_final_price),2)
  383. OD.Commercial_IP$ln_amz_mkt_final_pr <- round(log(OD.Commercial_IP$Amz_mkt_final_price),2)
  384. OD.Commercial_IP$ln_stpl_final_pr <- round(log(OD.Commercial_IP$staples_final_price),2)
  385.  
  386. OD.Commercial_MVI <- data.frame(subset(OD.Commercial,sales_units>0,
  387. names(OD.Commercial[,c(1:4,6:10,12:16,18:22,24:27,48,49)])),row.names=NULL)
  388. write.csv(OD.Commercial_MVI,"C:/Yashwanth/Pricing/1.OfficeDepot/8.Data Output/OD.Commecial_Output3.csv",row.names=FALSE)
  389.  
  390.  
  391. #---------------------------------------------------------------------------------------------------------------
  392. # Distribution of Variables : Histogram
  393. #---------------------------------------------------------------------------------------------------------------
  394. # Office Depot
  395. par(mfrow=c(1,2))
  396. hist(OD.Commercial_IP$sales_units,prob=T,xlab="Sales Units",main="Sales Units distribution")
  397. curve(dnorm(x,mean=mean(OD.Commercial_IP$sales_units),sd=sd(OD.Commercial_IP$sales_units)),add=TRUE)
  398. hist(log(OD.Commercial_IP$sales_units),prob=T,xlab="log(Sales Units)",main="Sales Units distribution")
  399. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$sales_units)),sd=sd(log(OD.Commercial_IP$sales_units))),add=TRUE)
  400.  
  401. par(mfrow=c(1,2))
  402. hist(OD.Commercial_IP$od_reg_price,prob=T,xlab="Regular Price",main="Regular Price distribution")
  403. curve(dnorm(x,mean=mean(OD.Commercial_IP$od_reg_price),sd=sd(OD.Commercial_IP$od_reg_price)),add=TRUE)
  404. hist(log(OD.Commercial_IP$od_reg_price),prob=T,xlab="log(Regular Price)",main="Regular Price distribution")
  405. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$od_reg_price)),sd=sd(log(OD.Commercial_IP$od_reg_price))),add=TRUE)
  406.  
  407. par(mfrow=c(1,2))
  408. hist(OD.Commercial_IP$od_final_price,prob=T,xlab="Final Price",main="OD Final price distribution")
  409. curve(dnorm(x,mean=mean(OD.Commercial_IP$od_final_price),sd=sd(OD.Commercial_IP$od_final_price)),add=TRUE)
  410. hist(log(OD.Commercial_IP$od_final_price),prob=T,xlab="log(Final Price)",main="OD Final price distribution")
  411. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$od_final_price)),sd=sd(log(OD.Commercial_IP$od_final_price))),add=TRUE)
  412.  
  413.  
  414. # Amazon
  415. par(mfrow=c(1,2))
  416. hist(OD.Commercial_IP$Amz_reg_price,prob=T,xlab="Regular Price",main="Regular Price distribution")
  417. curve(dnorm(x,mean=mean(OD.Commercial_IP$Amz_reg_price,na.rm=T),sd=sd(OD.Commercial_IP$Amz_reg_price,na.rm=T)),add=TRUE)
  418. hist(log(OD.Commercial_IP$Amz_reg_price),prob=T,xlab="log(Regular Price)",main="Regular Price distribution")
  419. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$Amz_reg_price),na.rm=T),sd=sd(log(OD.Commercial_IP$Amz_reg_price),na.rm=T)),add=TRUE)
  420.  
  421. par(mfrow=c(1,2))
  422. hist(OD.Commercial_IP$Amz_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  423. curve(dnorm(x,mean=mean(OD.Commercial_IP$Amz_final_price,na.rm=T),sd=sd(OD.Commercial_IP$Amz_final_price,na.rm=T)),add=TRUE)
  424. hist(log(OD.Commercial_IP$Amz_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  425. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$Amz_final_price),na.rm=T),sd=sd(log(OD.Commercial_IP$Amz_final_price),na.rm=T)),add=TRUE)
  426.  
  427.  
  428. # Amazon Marketplace
  429. par(mfrow=c(1,2))
  430. hist(OD.Commercial_IP$Amz_mkt_reg_price,prob=T,xlab="Regular Price",main="Regular Price distribution")
  431. curve(dnorm(x,mean=mean(OD.Commercial_IP$Amz_mkt_reg_price,na.rm=T),sd=sd(OD.Commercial_IP$Amz_mkt_reg_price,na.rm=T)),add=TRUE)
  432. hist(log(OD.Commercial_IP$Amz_mkt_reg_price),prob=T,xlab="log(Regular Price)",main="Regular Price distribution")
  433. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$Amz_mkt_reg_price),na.rm=T),sd=sd(log(OD.Commercial_IP$Amz_mkt_reg_price),na.rm=T)),add=TRUE)
  434.  
  435. par(mfrow=c(1,2))
  436. hist(OD.Commercial_IP$Amz_mkt_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  437. curve(dnorm(x,mean=mean(OD.Commercial_IP$Amz_mkt_final_price,na.rm=T),sd=sd(OD.Commercial_IP$Amz_mkt_final_price,na.rm=T)),add=TRUE)
  438. hist(log(OD.Commercial_IP$Amz_mkt_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  439. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$Amz_mkt_final_price),na.rm=T),sd=sd(log(OD.Commercial_IP$Amz_mkt_final_price),na.rm=T)),add=TRUE)
  440.  
  441. # Staples
  442. par(mfrow=c(1,2))
  443. hist(OD.Commercial_IP$staples_reg_price,prob=T,xlab="Regular Price",main="Regular Price distribution")
  444. curve(dnorm(x,mean=mean(OD.Commercial_IP$staples_reg_price,na.rm=T),sd=sd(OD.Commercial_IP$staples_reg_price,na.rm=T)),add=TRUE)
  445. hist(log(OD.Commercial_IP$staples_reg_price),prob=T,xlab="log(Regular Price)",main="Regular Price distribution")
  446. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$staples_reg_price),na.rm=T),sd=sd(log(OD.Commercial_IP$staples_reg_price),na.rm=T)),add=TRUE)
  447.  
  448. par(mfrow=c(1,2))
  449. hist(OD.Commercial_IP$staples_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  450. curve(dnorm(x,mean=mean(OD.Commercial_IP$staples_final_price,na.rm=T),sd=sd(OD.Commercial_IP$staples_final_price,na.rm=T)),add=TRUE)
  451. hist(log(OD.Commercial_IP$staples_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  452. curve(dnorm(x,mean=mean(log(OD.Commercial_IP$staples_final_price),na.rm=T),sd=sd(log(OD.Commercial_IP$staples_final_price),na.rm=T)),add=TRUE)
  453.  
  454.  
  455. #---------------------------------------------------------------------------------------------------------------
  456. #---------------------------------------------------------------------------------------------------------------
  457. #---------------------------------------------------------------------------------------------------------------
  458. #---------------------------------------------------------------------------------------------------------------
  459. #---------------------------------------------------------------------------------------------------------------
  460. #---------------------------------------------------------------------------------------------------------------
  461. #---------------------------------------------------------------------------------------------------------------
  462. #---------------------------------------------------------------------------------------------------------------
  463. #---------------------------------------------------------------------------------------------------------------
  464.  
  465. # --------------------------------------------------------------------------------------------------------------
  466. # --------------------------------------------------------------------------------------------------------------
  467. # || Rough Work ||
  468. # --------------------------------------------------------------------------------------------------------------
  469. # --------------------------------------------------------------------------------------------------------------
  470.  
  471. ddply(OD.Commercial[,grep("final_pr",names(OD.Commercial))])
  472. OD.Commercial_aggr <- OD.Commercial[,c(1,grep("final_price",names(OD.Commercial)))]
  473. OD.Commercial_aggr_DT <- data.table(OD.Commercial_aggr)
  474.  
  475. OD.Commercial_FT <- read.table("C:/Yashwanth/Pricing/1.OfficeDepot/8.Data Output/Commercial_freq_table.csv",
  476. header = TRUE, sep = ",", quote = "\"", dec = "." ,fill=TRUE, comment.char="", as.is=TRUE)
  477. OD.Commercial_FT$id <- seq(1:nrow(OD.Commercial_FT))
  478. OD.Commercial_FT <- OD.Commercial_FT[,c(6,1:5)]
  479.  
  480. OD.Commercial_FT_KM <- kmeans(OD.Commercial_FT[3:ncol(OD.Commercial_FT)],5)
  481.  
  482. OD.Commercial_Clusters <- data.frame(id=OD.Commercial_FT$id,clusters=OD.Commercial_FT_KM$cluster,OD.Commercial_FT[2:ncol(OD.Commercial_FT)])
  483. write.csv(OD.Commercial_Clusters,"C:/Yashwanth/Pricing/1.OfficeDepot/8.Data Output/OD.Commecial_Output2.csv",row.names=NULL)
  484.  
  485. OD.Commercial_Clusters_Interpol <- read.table("C:/Yashwanth/Pricing/1.OfficeDepot/8.Data Output/OD.Commecial_Output2.csv",
  486. header = TRUE, sep = ",", quote = "\"", dec = "." ,fill=TRUE, comment.char="", as.is=TRUE)
  487.  
  488. cluster_1 <- subset(OD.Commercial_Clusters_Interpol,clusters==1,names(OD.Commercial_Clusters_Interpol))
  489. dist(t(cluster_1[4:7]),method="manhattan")
  490.  
  491. dist <- data.frame(as.matrix(dist(OD.Commercial_Clusters_Interpol[,4:7],method="manhattan",upper=FALSE)))
  492.  
  493. dist <- dist[,1:65]
  494.  
  495. SD <- NA
  496. for(i in c(1:nrow(dist))){
  497. SD[i] <- minnz(as.integer(dist[i,]))
  498. }
  499. dist <- cbind(dist,SD)
  500.  
  501. OD.Comm_Model <- subset(OD.Commercial_Clusters_Interpol,Amz_final_price!=0,names(OD.Commercial_Clusters_Interpol))
  502.  
  503.  
  504.  
  505. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  506. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  507. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  508. # || Rules Implementation : Pre Processing Rules ||
  509. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  510. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  511. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  512.  
  513. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  514. # || Data Preparation ||
  515. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  516. install.packages("plyr",dependencies=T)
  517. install.packages("xlsx",dependencies=T)
  518.  
  519. rm(list=ls())
  520. setwd("C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/1.Rules_data_files/5.Data_v2.3/1.Handsoaps/")
  521.  
  522. # ------------------------------------------------------------------------------------------------------------------------------------------#
  523. # || Rule 1 : Raw data || # #
  524. # ------------------------------------------------------------------------------------------------------------------------------------------#
  525.  
  526. OD.Handsoap_Rule1 <- read.table("Rule_1.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  527. fill=TRUE, comment.char="", as.is=TRUE)
  528.  
  529. # ------------------------------------------------------------------------------------------------------------------------------------------#
  530. # || Price Previous Week || #
  531. # ------------------------------------------------------------------------------------------------------------------------------------------#
  532.  
  533. OD.Handsoap_Price_Prev_Week <- read.table("Price_Prev_Week.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  534. fill=TRUE, comment.char="", as.is=TRUE)
  535.  
  536. OD.Handsoap_Price_Prev_Week$Min_Competitor_Regular_Price <- NA;
  537. attach(OD.Handsoap_Price_Prev_Week)
  538. for(i in 1:nrow(OD.Handsoap_Price_Prev_Week)){
  539. OD.Handsoap_Price_Prev_Week$Min_Competitor_Regular_Price[i] <- ifelse(min(amz_mkt_regular_price[i],amz_regular_price[i],staples_regular_price[i],
  540. Walmart_regular_price[i],BestBuy_regular_price[i],na.rm=T)=="Inf",0,
  541. min(amz_mkt_regular_price[i],amz_regular_price[i],staples_regular_price[i],
  542. Walmart_regular_price[i],BestBuy_regular_price[i],na.rm=T))
  543. }
  544. detach(OD.Handsoap_Price_Prev_Week)
  545.  
  546. OD.Handsoap_Price_Prev_Week$Max_Competitor_Regular_Price <- NA
  547. attach(OD.Handsoap_Price_Prev_Week)
  548. for(i in 1:nrow(OD.Handsoap_Price_Prev_Week)){
  549. OD.Handsoap_Price_Prev_Week$Max_Competitor_Regular_Price[i] <- ifelse(max(amz_mkt_regular_price[i],amz_regular_price[i],staples_regular_price[i],
  550. Walmart_regular_price[i],BestBuy_regular_price[i],na.rm=T)=="-Inf",0,
  551. max(amz_mkt_regular_price[i],amz_regular_price[i],staples_regular_price[i],
  552. Walmart_regular_price[i],BestBuy_regular_price[i],na.rm=T))
  553. }
  554. detach(OD.Handsoap_Price_Prev_Week)
  555.  
  556. library(plyr)
  557. OD.Handsoap_Rule1 <- merge(OD.Handsoap_Rule1,OD.Handsoap_Price_Prev_Week[,c("SKU","od_final_price")],by="SKU",all.x=T)
  558. OD.Handsoap_Rule1 <- rename(OD.Handsoap_Rule1,c("od_final_price"="Last_Weeks_OD_Price"))
  559.  
  560. # ------------------------------------------------------------------------------------------------------------------------------------------#
  561. # || Price Latest || #
  562. # ------------------------------------------------------------------------------------------------------------------------------------------#
  563. OD.Handsoap_Price_Latest <- read.table("Price_Latest.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  564. fill=TRUE, comment.char="", as.is=TRUE)
  565.  
  566. OD.Handsoap_Price_Latest$Min_Competitor_Regular_Price <- NA;
  567. attach(OD.Handsoap_Price_Latest)
  568. for(i in 1:nrow(OD.Handsoap_Price_Latest)){
  569. OD.Handsoap_Price_Latest$Min_Competitor_Regular_Price[i] <- ifelse(min(amz_mkt_regular_price[i],amz_regular_price[i],staples_regular_price[i],
  570. Walmart_regular_price[i],BestBuy_regular_price[i],na.rm=T)=="Inf",0,
  571. min(amz_mkt_regular_price[i],amz_regular_price[i],staples_regular_price[i],
  572. Walmart_regular_price[i],BestBuy_regular_price[i],na.rm=T))
  573. }
  574. detach(OD.Handsoap_Price_Latest)
  575.  
  576. OD.Handsoap_Price_Latest$Max_Competitor_Regular_Price <- NA
  577. attach(OD.Handsoap_Price_Latest)
  578. for(i in 1:nrow(OD.Handsoap_Price_Latest)){
  579. OD.Handsoap_Price_Latest$Max_Competitor_Regular_Price[i] <- ifelse(max(amz_mkt_regular_price[i],amz_regular_price[i],staples_regular_price[i],
  580. Walmart_regular_price[i],BestBuy_regular_price[i],na.rm=T)=="-Inf",0,
  581. max(amz_mkt_regular_price[i],amz_regular_price[i],staples_regular_price[i],
  582. Walmart_regular_price[i],BestBuy_regular_price[i],na.rm=T))
  583. }
  584. detach(OD.Handsoap_Price_Latest)
  585.  
  586. OD.Handsoap_Rule1 <- merge(OD.Handsoap_Rule1,OD.Handsoap_Price_Latest[,c("SKU","od_final_price","Min_Competitor_Regular_Price",
  587. "Max_Competitor_Regular_Price")],by="SKU",all.x=T)
  588. OD.Handsoap_Rule1 <- rename(OD.Handsoap_Rule1,c("od_final_price"="Latest_OD_Price","Min_Competitor_Regular_Price"="Min_Competitor_Price",
  589. "Max_Competitor_Regular_Price"="Max_Competitor_Price"))
  590.  
  591. # ------------------------------------------------------------------------------------------------------------------------------------------#
  592. # || Cost data || #
  593. # ------------------------------------------------------------------------------------------------------------------------------------------#
  594.  
  595. OD.Handsoap_Cost <- read.table("Cost.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  596. fill=TRUE, comment.char="", as.is=TRUE)
  597.  
  598. OD.Handsoap_Rule1 <- merge(OD.Handsoap_Rule1,OD.Handsoap_Cost[,c("SKU","WTD_UNIT_COST")],by="SKU",all.x=T)
  599. OD.Handsoap_Rule1 <- rename(OD.Handsoap_Rule1,c("WTD_UNIT_COST"="Cost"))
  600.  
  601. # ------------------------------------------------------------------------------------------------------------------------------------------#
  602. # || Threshold data || #
  603. # ------------------------------------------------------------------------------------------------------------------------------------------#
  604.  
  605. OD.Handsoap_Threshold <- read.table("Threshold.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  606. fill=TRUE, comment.char="", as.is=TRUE)
  607.  
  608. OD.Handsoap_Rule1 <- merge(OD.Handsoap_Rule1,OD.Handsoap_Threshold[,c("SKU","Sales_Lower_Threshold")],by="SKU",all.x=T)
  609. OD.Handsoap_Rule1 <- rename(OD.Handsoap_Rule1,c("Sales_Lower_Threshold"="Lower_Threshold"))
  610.  
  611. # ------------------------------------------------------------------------------------------------------------------------------------------#
  612. # || Sales data || #
  613. #-------------------------------------------------------------------------------------------------------------------------------------------#
  614.  
  615. OD.Handsoap_Sales <- read.table("Sales.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  616. fill=TRUE, comment.char="", as.is=TRUE)
  617.  
  618. # Average of Last two Weeks
  619. OD.Handsoap_Sales$Average_of_last_2_weeks <- NA
  620. attach(OD.Handsoap_Sales)
  621. for(i in 1:nrow(OD.Handsoap_Sales)){
  622. OD.Handsoap_Sales$Average_of_last_2_weeks[i] <- ifelse(is.na(FISCAL_WEEK_13_OF_2015[i] & is.na(FISCAL_WEEK_14_OF_2015[i])),0,
  623. ifelse(is.na(FISCAL_WEEK_13_OF_2015[i]) | is.na(FISCAL_WEEK_14_OF_2015[i]),
  624. sum(FISCAL_WEEK_13_OF_2015[i],FISCAL_WEEK_14_OF_2015[i],na.rm=T),mean(c(FISCAL_WEEK_13_OF_2015[i],FISCAL_WEEK_14_OF_2015[i]))))
  625. }
  626. detach(OD.Handsoap_Sales)
  627.  
  628. # Sum of Last 10 Weeks
  629. OD.Handsoap_Sales$Sum_of_Last_10_weeks <- NA
  630. attach(OD.Handsoap_Sales)
  631. for(i in 1:nrow(OD.Handsoap_Sales)){
  632. j <- c(2:10,ncol(OD.Handsoap_Sales)-2);
  633. OD.Handsoap_Sales$Sum_of_Last_10_weeks[i] <- ifelse(sum(OD.Handsoap_Sales[i,j],na.rm=T)=="NA",0,
  634. sum(OD.Handsoap_Sales[i,j],na.rm=T))
  635. }
  636. detach(OD.Handsoap_Sales)
  637.  
  638. # Lower Threshold & Last 2 week Average Sales
  639. library(plyr)
  640. OD.Handsoap_Rule1 <- merge(OD.Handsoap_Rule1,OD.Handsoap_Sales[,c("SKU","Average_of_last_2_weeks")],by="SKU",all.x=T)
  641. OD.Handsoap_Rule1 <- rename(OD.Handsoap_Rule1,c("Average_of_last_2_weeks"="Last_2_week_Average_Sales"))
  642.  
  643. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  644. # || Rule #1 ||
  645. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  646.  
  647. # Create Dummy varaible(Iferror)
  648. OD.Handsoap_Rule1$New_Lower_Threshold <- as.numeric(OD.Handsoap_Rule1$Lower_Threshold)
  649. OD.Handsoap_Rule1$New_Lower_Threshold[is.na(OD.Handsoap_Rule1$New_Lower_Threshold)] <- 1
  650.  
  651. OD.Handsoap_Rule1$New_Last_2_week_Average_Sales <- as.numeric(OD.Handsoap_Rule1$Last_2_week_Average_Sales)
  652. OD.Handsoap_Rule1$New_Last_2_week_Average_Sales[is.na(OD.Handsoap_Rule1$New_Last_2_week_Average_Sales)] <- 0
  653.  
  654. #------------------------------------------------------------ Rule One Condition ----------------------------------------------------#
  655. # =IF(Last_2_week_Average_Sales1<>"",IF(Last_2_week_Average_Sales1<IFERROR(New_Lower_Threshold,1),TRUE,FALSE),FALSE) #
  656. #------------------------------------------------------------ Rule One Condition ----------------------------------------------------#
  657.  
  658. attach(OD.Handsoap_Rule1)
  659. OD.Handsoap_Rule1$Rule1 <- NA
  660. for(i in 1:nrow(OD.Handsoap_Rule1)){
  661. OD.Handsoap_Rule1$Rule1[i] <- ifelse(New_Last_2_week_Average_Sales[i]<New_Lower_Threshold[i],TRUE,FALSE)
  662. }
  663.  
  664. OD.Handsoap_Rule1$Action1 <- as.factor(ifelse(OD.Handsoap_Rule1$Rule1==TRUE,"R"," "))
  665. detach(OD.Handsoap_Rule1)
  666.  
  667. # Define Floor attribute
  668. attach(OD.Handsoap_Rule1)
  669. OD.Handsoap_Rule1$Floor1 <- NA
  670. for(i in c(1:nrow(OD.Handsoap_Rule1))){
  671. OD.Handsoap_Rule1$Floor1[i] <- ifelse(Action1[i]=="R",
  672. ifelse(min(0.9*Latest_OD_Price[i],max(0,1.11*Cost[i],0.8*Min_Competitor_Price[i]))=="Inf","NA",
  673. min(0.9*Latest_OD_Price[i],max(0,1.11*Cost[i],0.8*Min_Competitor_Price[i])))," ")
  674. }
  675.  
  676. # Define CAP
  677. OD.Handsoap_Rule1$CAP1 <- NA
  678. for(i in c(1:nrow(OD.Handsoap_Rule1))){
  679. OD.Handsoap_Rule1$CAP1[i] <- ifelse(Action1[i]=="R",0.9*Latest_OD_Price[i]," ")
  680. }
  681.  
  682. detach(OD.Handsoap_Rule1)
  683. OD.Handsoap_Rule1 <- OD.Handsoap_Rule1[,grep("New",names(OD.Handsoap_Rule1),value=T,invert=T)]
  684.  
  685. # ------------------------------------------------------------------------------------------------------------------------------------------#
  686. # || Rule #2 || #
  687. # ------------------------------------------------------------------------------------------------------------------------------------------#
  688.  
  689. # Price Latest
  690. OD.Handsoap_Rule_12 <- merge(OD.Handsoap_Rule1,OD.Handsoap_Price_Latest[,c("SKU","staples_final_price")],by="SKU",all.x=TRUE)
  691. OD.Handsoap_Rule_12 <- rename(OD.Handsoap_Rule_12,c("staples_final_price"="Latest_Staples_Price"))
  692.  
  693. # Threshold data
  694. OD.Handsoap_Rule_12 <- merge(OD.Handsoap_Rule_12,OD.Handsoap_Threshold[,c("SKU","Staples_Price_Change_Threshold")],by="SKU",all.x=T)
  695. OD.Handsoap_Rule_12 <- rename(OD.Handsoap_Rule_12,c("Staples_Price_Change_Threshold"="Staples_Threshold"))
  696. OD.Handsoap_Rule_12$Staples_Threshold <- as.numeric(gsub("%","",OD.Handsoap_Rule_12$Staples_Threshold))/100
  697.  
  698. # Price Previous data
  699. OD.Handsoap_Rule_12 <- merge(OD.Handsoap_Rule_12,OD.Handsoap_Price_Prev_Week[,c("SKU","staples_final_price")],by="SKU",all.x=T)
  700. OD.Handsoap_Rule_12 <- rename(OD.Handsoap_Rule_12,c("staples_final_price"="Last_Week_Staples_Price"))
  701.  
  702. #------------------------------------------------------------ Rule Two Condition ----------------------------------------------------#
  703. # # =IF(AND(IFERROR(Latest Staples Price,0)>0,IFERROR(Last Week Staples Price,0)>0), #
  704. # # IF(Latest Staples Price<=(IFERROR(Staples Threshold,0)*Last Week Staples Price),TRUE,FALSE),FALSE) #
  705. #------------------------------------------------------------ Rule Two Condition ----------------------------------------------------#
  706.  
  707. # Create Dummy varaible(Iferror)
  708. OD.Handsoap_Rule_12$Latest_Staples_Price[is.na(OD.Handsoap_Rule_12$Latest_Staples_Price )] <- 0
  709. OD.Handsoap_Rule_12$Staples_Threshold[is.na(OD.Handsoap_Rule_12$Staples_Threshold )] <- 0
  710. OD.Handsoap_Rule_12$Last_Week_Staples_Price[is.na(OD.Handsoap_Rule_12$Last_Week_Staples_Price )] <- 0
  711.  
  712. attach(OD.Handsoap_Rule_12)
  713. Rule2 <- NA
  714. for(i in 1:nrow(OD.Handsoap_Rule_12)){
  715. Rule2[i] <- ifelse(Latest_Staples_Price[i]>0 & Last_Week_Staples_Price[i]>0,
  716. ifelse(Latest_Staples_Price[i]<=Staples_Threshold[i]*Last_Week_Staples_Price[i],TRUE,FALSE),FALSE)
  717. }
  718. Rule2[is.na(Rule2)] <- FALSE
  719.  
  720. OD.Handsoap_Rule_12 <- cbind(OD.Handsoap_Rule_12,Rule2)
  721. OD.Handsoap_Rule_12$Action2 <- as.factor(ifelse(OD.Handsoap_Rule_12$Rule2==TRUE,"R"," "))
  722. detach(OD.Handsoap_Rule_12)
  723.  
  724. # Define Floor attribute
  725. attach(OD.Handsoap_Rule_12)
  726. Floor2 <- NA
  727. for(i in c(1:nrow(OD.Handsoap_Rule_12))){
  728. Floor2[i] <- ifelse(Action2[i]=="R",
  729. min(0.9*Latest_OD_Price,max(0,1.11*Cost[i],0.8*Min_Competitor_Price[i]))," ")
  730. }
  731.  
  732. # Define CAP attribute
  733. CAP2 <- NA
  734. for(i in c(1:nrow(OD.Handsoap_Rule_12))){
  735. CAP2[i] <- ifelse(Action2[i]=="R",0.9*Latest_OD_Price[i]," ")
  736. }
  737.  
  738. detach(OD.Handsoap_Rule_12)
  739. OD.Handsoap_Rule_12 <- cbind(OD.Handsoap_Rule_12,Floor2,CAP2)
  740.  
  741. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  742. # || Rule #3 ||
  743. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  744.  
  745. OD.Handsoap_Rule_123 <- merge(OD.Handsoap_Rule_12,OD.Handsoap_Threshold[,c("SKU","Sales_Upper_Threshold")],by="SKU",all.x=T)
  746. OD.Handsoap_Rule_123 <- rename(OD.Handsoap_Rule_123,c("Sales_Upper_Threshold"="Upper_Threshold"))
  747.  
  748. OD.Handsoap_Rule_123 <- merge(OD.Handsoap_Rule_123,OD.Handsoap_Sales[,c("SKU","Average_of_last_2_weeks")],by="SKU",all.x=T)
  749. OD.Handsoap_Rule_123 <- rename(OD.Handsoap_Rule_123,c("Average_of_last_2_weeks"="Last_2_week_Average_Sales1"))
  750.  
  751. #------------------------------------------------------------ Rule Three Condition ----------------------------------------------------#
  752. # =IF(Last 2 week Average Sales<>"",IF(Last 2 week Average Sales>IFERROR(Upper Threshold,1000),TRUE,FALSE),FALSE), #
  753. #------------------------------------------------------------ Rule Three Condition ----------------------------------------------------#
  754.  
  755. # Create Dummy varaible(Iferror)
  756. OD.Handsoap_Rule_123$New_Upper_Threshold <- as.numeric(OD.Handsoap_Rule_123$Upper_Threshold)
  757. OD.Handsoap_Rule_123$New_Upper_Threshold[is.na(OD.Handsoap_Rule_123$New_Upper_Threshold)] <- 1000
  758.  
  759. OD.Handsoap_Rule_123$New_Last_2_week_Average_Sales1 <- as.numeric(OD.Handsoap_Rule_123$Last_2_week_Average_Sales1)
  760. OD.Handsoap_Rule_123$New_Last_2_week_Average_Sales1[is.na(OD.Handsoap_Rule_123$New_Last_2_week_Average_Sales1)] <- 0
  761.  
  762. attach(OD.Handsoap_Rule_123)
  763. Rule3 <- NA
  764. for(i in 1:nrow(OD.Handsoap_Rule_123)){
  765. Rule3[i] <- ifelse(New_Last_2_week_Average_Sales1[i]>New_Upper_Threshold[i],TRUE,FALSE)
  766. }
  767. Rule3[is.na(Rule3)] <- FALSE
  768.  
  769. OD.Handsoap_Rule_123 <- cbind(OD.Handsoap_Rule_123,Rule3)
  770. OD.Handsoap_Rule_123$Action3 <- as.factor(ifelse(OD.Handsoap_Rule_123$Rule3==TRUE,"I"," "))
  771. detach(OD.Handsoap_Rule_123)
  772.  
  773. # Define Floor attribute
  774. attach(OD.Handsoap_Rule_123)
  775. Floor3 <- NA
  776. for(i in c(1:nrow(OD.Handsoap_Rule_123))){
  777. Floor3[i] <- ifelse(OD.Handsoap_Rule_123$Action3[i]=="I",Latest_OD_Price[i]," ")
  778. }
  779.  
  780. # Define CAP attribute
  781. CAP3 <- NA
  782. for(i in c(1:nrow(OD.Handsoap_Rule_123))){
  783. CAP3[i] <- ifelse(Action3[i]=="I",max(Latest_OD_Price[i],min(1.2*Max_Competitor_Price[i],1.3*Latest_OD_Price[i]))," ")
  784. }
  785.  
  786. detach(OD.Handsoap_Rule_123)
  787. OD.Handsoap_Rule_123 <- cbind(OD.Handsoap_Rule_123,Floor3,CAP3)
  788. OD.Handsoap_Rule_123 <- OD.Handsoap_Rule_123[,grep("New",names(OD.Handsoap_Rule_123),value=T,invert=T)]
  789.  
  790. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  791. # || Rule #4 ||
  792. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  793.  
  794. OD.Handsoap_Rule_1234 <- OD.Handsoap_Rule_123
  795.  
  796. #------------------------------------------------------------ Rule Four Condition ----------------------------------------------------#
  797. # # =IF(Latest OD Price<0.8*Min Competitor Price,TRUE,FALSE) #
  798. #------------------------------------------------------------ Rule Four Condition ----------------------------------------------------#
  799.  
  800. OD.Handsoap_Rule_1234$Rule4 <- ifelse(OD.Handsoap_Rule_1234$Latest_OD_Price<0.8*OD.Handsoap_Rule_1234$Min_Competitor_Price,TRUE,FALSE)
  801. OD.Handsoap_Rule_1234$Action4 <- ifelse(OD.Handsoap_Rule_1234$Rule4=="TRUE","I"," ")
  802.  
  803. # Define Floor attribute
  804. attach(OD.Handsoap_Rule_1234)
  805. Floor4 <- NA
  806. for(i in 1:nrow(OD.Handsoap_Rule_1234)){
  807. Floor4[i] <- ifelse(Action4[i]=="I",Latest_OD_Price[i]," ")
  808. }
  809.  
  810. # Define CAP attribute
  811. CAP4 <- NA
  812. for(i in 1:nrow(OD.Handsoap_Rule_1234)){
  813. CAP4[i] <- ifelse(Action4[i]=="I",max(Latest_OD_Price[i],min(1.2*Max_Competitor_Price[i],1.3*Latest_OD_Price[i]))," ")
  814. }
  815.  
  816. detach(OD.Handsoap_Rule_1234)
  817. OD.Handsoap_Rule_1234 <- cbind(OD.Handsoap_Rule_1234,Floor4,CAP4)
  818.  
  819. # -----------------------------------------------------------------------------------------------------------------------------------#
  820. # || Final Action, Floor & CAP || #
  821. # = IF(OR(Action1="R",Action2="R"),"R",IF(OR(Action3="I",Action4="I"),"I","")) #
  822. # -----------------------------------------------------------------------------------------------------------------------------------#
  823.  
  824. OD.Handsoap_Rule_1234$Final_Action <- ifelse(OD.Handsoap_Rule_1234$Action1=="R" | OD.Handsoap_Rule_1234$Action2=="R","R",
  825. ifelse(OD.Handsoap_Rule_1234$Action3=="I" | OD.Handsoap_Rule_1234$Action4=="I","I"," "))
  826.  
  827. # Define Floor attribute
  828. attach(OD.Handsoap_Rule_1234)
  829. Final_Floor <- NA
  830. for(i in 1:nrow(OD.Handsoap_Rule_1234)){
  831. Final_Floor[i] <- ifelse(Final_Action[i]=="R",max(Floor1[i],Floor2[i]),
  832. ifelse(Final_Action[i]=="I",max(Floor3[i],Floor4[i])," "))
  833. }
  834.  
  835. # Define CAP attribute
  836. Final_CAP <- NA
  837. for(i in 1:nrow(OD.Handsoap_Rule_1234)){
  838. Final_CAP[i] <- ifelse(Final_Action[i]=="R",max(CAP1[i],CAP2[i]),
  839. ifelse(Final_Action[i]=="I",max(CAP3[i],CAP4[i])," "))
  840. }
  841.  
  842. detach(OD.Handsoap_Rule_1234)
  843. OD.Handsoap_Rule_1234 <- cbind(OD.Handsoap_Rule_1234,Final_Floor,Final_CAP)
  844.  
  845.  
  846. # Rules Triggered
  847. OD.Handsoap_Rule_1234$Rules_Triggered <- NA
  848. attach(OD.Handsoap_Rule_1234)
  849. for(i in 1:nrow(OD.Handsoap_Rule_1234)){
  850. OD.Handsoap_Rule_1234$Rules_Triggered[i] <- ifelse(Final_Action[i]==" "," ",ifelse(Rule1[i]=="TRUE","Sales too low",ifelse(Rule2[i]=="TRUE","Comp dropped price",
  851. ifelse(Rule3[i]=="TRUE","Sales high",ifelse(Rule4[i]=="TRUE","OD price lower than market")))))
  852. }
  853. detach(OD.Handsoap_Rule_1234)
  854.  
  855. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  856. # || Export to Excel ||
  857. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  858.  
  859. save.image("~/OD Dynamic Pricing/OD_DynamicPricing/2.Rules/1.Handsoaps/Rules_Implemetation_v2.3.RData")
  860.  
  861. library(xlsx)
  862. write.xlsx(OD.Handsoap_Rule_1234,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  863. row.names=FALSE,sheetName="Pricing_Rules")
  864.  
  865.  
  866. #------------------------------------------------------------------------------------------------------------------------------------------------
  867. #------------------------------------------------------------------------------------------------------------------------------------------------
  868. #------------------------------------------------------------------------------------------------------------------------------------------------
  869. #------------------------------- END ------------------------ Rules Implementation ---------------------------- END -----------------------------
  870. #------------------------------------------------------------------------------------------------------------------------------------------------
  871. #------------------------------------------------------------------------------------------------------------------------------------------------
  872.  
  873. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  874. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  875. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  876. # || Rules Implementation : Optimizer ||
  877. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  878. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  879. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  880.  
  881. # Model Ln Sales = Int -0.03 (OD Final Price - Staples Final Price) + 0.0013 * Sales of Previous Week
  882. # Ln S2 - Ln S1 = 0.03 (ODP 1 - STP 1 - ODP2 + STP2) + 0.0013 * (S1 - S0)
  883. # Ln S2 = ln S1 + 0.03(ODP 1 + STP2 - STP 1 - ODP2) + 0.0013 * (S1 - S0)
  884. # S2 = exp(ln S1 + 0.03(ODP1 + STP 2 - STP 1 - ODP2) + 0.0013 * (S1 - S0) )
  885. #
  886. # Margin2 = S2 (ODP2 - Cost)
  887. # Margin 2 = exp[ln S1 + 0.03 (ODP 1 + STP 2 - STP 1 - ODP2) + 0.0013 * (S1 - S0) ]*(ODP2-Cost)
  888.  
  889. rm(list=ls())
  890. load("~/OD Dynamic Pricing/OD_DynamicPricing/2.Rules/1.Handsoaps/Rules_Implemetation_v2.3.RData")
  891. rm(list=setdiff(ls(),c("OD.Handsoap_Rule_1234","OD.Handsoap_Sales","OD.Handsoap_Price_Prev_Week","OD.Handsoap_Price_Latest",
  892. "OD.Handsoap_Cost","OD.Handsoap_Threshold")))
  893.  
  894. setwd("C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/1.Rules_data_files/5.Data_v2.3/1.Handsoaps/")
  895. OD.Handsoap_Optimizer <- read.table("Optimizer.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  896. fill=TRUE, comment.char="", as.is=TRUE)
  897.  
  898. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Sales[,c("SKU","FISCAL_WEEK_14_OF_2015","FISCAL_WEEK_13_OF_2015")],by="SKU",all.x=T)
  899. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Price_Prev_Week[,c("SKU","od_final_price")],by="SKU",all.x=T)
  900. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Price_Latest[,c("SKU","staples_final_price")],by="SKU",all.x=T)
  901. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Price_Prev_Week[,c("SKU","staples_final_price")],by="SKU",all.x=T)
  902. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Cost[,c("SKU","WTD_UNIT_COST")],by="SKU",all.x=T)
  903. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Rule_1234[c("SKU","Final_Floor","Final_CAP")],by="SKU",all.x=T)
  904.  
  905. library(plyr)
  906. OD.Handsoap_Optimizer <- rename(OD.Handsoap_Optimizer,c("FISCAL_WEEK_14_OF_2015"="S1_Last_weeks_sales",
  907. "FISCAL_WEEK_13_OF_2015"="S0_Last_to_last_weeks_sales","od_final_price"="ODP_1_Last_weeks_OD_Price",
  908. "staples_final_price.x"="STP_2_Latest_Staples_Price","staples_final_price.y"="STP_1_Last_weeks_Staples_price",
  909. "WTD_UNIT_COST"="Cost","Final_Floor"="ODP_2_Floor","Final_CAP"="ODP_2_Cap"))
  910.  
  911. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  912. # || Data Preparation ||
  913. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  914.  
  915. OD.Handsoap_Optimizer$ODP_1_Last_weeks_OD_Price[is.na(OD.Handsoap_Optimizer$ODP_1_Last_weeks_OD_Price)] <- "N/A"
  916. OD.Handsoap_Optimizer$STP_2_Latest_Staples_Price[is.na(OD.Handsoap_Optimizer$STP_2_Latest_Staples_Price)] <- "N/A"
  917. OD.Handsoap_Optimizer$STP_1_Last_weeks_Staples_price[is.na(OD.Handsoap_Optimizer$STP_1_Last_weeks_Staples_price)] <- "N/A"
  918.  
  919. attach(OD.Handsoap_Optimizer)
  920. OD.Handsoap_Optimizer$STP_2_Latest_Staples_Price <- ifelse(ODP_1_Last_weeks_OD_Price=="N/A",STP_2_Latest_Staples_Price=="N/A",
  921. ifelse(STP_2_Latest_Staples_Price=="N/A",0,STP_2_Latest_Staples_Price))
  922. OD.Handsoap_Optimizer$STP_1_Last_weeks_Staples_price <- ifelse(ODP_1_Last_weeks_OD_Price=="N/A",STP_1_Last_weeks_Staples_price=="N/A",
  923. ifelse(STP_1_Last_weeks_Staples_price=="N/A",0,STP_1_Last_weeks_Staples_price))
  924. detach(OD.Handsoap_Optimizer)
  925.  
  926. OD.Handsoap_Optimizer$STP_2_Latest_Staples_Price <- as.numeric(OD.Handsoap_Optimizer$STP_2_Latest_Staples_Price)
  927. OD.Handsoap_Optimizer$STP_1_Last_weeks_Staples_price <- as.numeric(OD.Handsoap_Optimizer$STP_1_Last_weeks_Staples_price)
  928. OD.Handsoap_Optimizer$ODP_1_Last_weeks_OD_Price <- as.numeric(OD.Handsoap_Optimizer$ODP_1_Last_weeks_OD_Price)
  929.  
  930. # rm(list=setdiff(ls(),c("OD.Handsoap_Rule_1234","OD.Handsoap_Sales","OD.Handsoap_Price_Prev_Week","OD.Handsoap_Price_Latest",
  931. # "OD.Handsoap_Cost","OD.Handsoap_Threshold","OD.Handsoap_Optimizer")))
  932. OD.Handsoap_Optimizer$ODP_2_Floor <- as.numeric(as.character(OD.Handsoap_Optimizer$ODP_2_Floor))
  933. OD.Handsoap_Optimizer$ODP_2_Cap <- as.numeric(as.character(OD.Handsoap_Optimizer$ODP_2_Cap))
  934.  
  935. OD.Handsoap_Optimizer$S1_Last_weeks_sales[is.na(OD.Handsoap_Optimizer$S1_Last_weeks_sales)] <- 0
  936. OD.Handsoap_Optimizer$S0_Last_to_last_weeks_sales[is.na(OD.Handsoap_Optimizer$S0_Last_to_last_weeks_sales)] <- 0
  937.  
  938. # ----------------------------------------------------------------------------------------------------------------------------------#
  939. # || ODP values : 0,1,2,3,4,5,6,7,8,9 ||
  940. # = ODP 2 Floor+ODP_value*($ODP 2 Cap-ODP 2 Floor)/9
  941. # ---------------------------------------------------------------------------------------------------------------------------------#
  942.  
  943. OD_ODP2_Value_seq <- NA
  944. for(j in seq(0,10,by=1)){
  945. OD_ODP2_Value_seq[j] <- j-1
  946. }
  947.  
  948. OD.Handsoap_Optimizer$OD_ODP2_Value1 <- NA;OD.Handsoap_Optimizer$OD_ODP2_Value2<-NA;OD.Handsoap_Optimizer$OD_ODP2_Value3 <- NA;
  949. OD.Handsoap_Optimizer$OD_ODP2_Value4 <- NA;OD.Handsoap_Optimizer$OD_ODP2_Value5 <- NA;OD.Handsoap_Optimizer$OD_ODP2_Value6 <- NA;
  950. OD.Handsoap_Optimizer$OD_ODP2_Value7 <- NA;OD.Handsoap_Optimizer$OD_ODP2_Value8 <- NA;OD.Handsoap_Optimizer$OD_ODP2_Value9 <- NA;
  951. OD.Handsoap_Optimizer$OD_ODP2_Value10 <- NA;
  952.  
  953. attach(OD.Handsoap_Optimizer)
  954. for(i in 1:nrow(OD.Handsoap_Optimizer)){
  955. OD.Handsoap_Optimizer$OD_ODP2_Value1[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[1]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  956. OD.Handsoap_Optimizer$OD_ODP2_Value2[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[2]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  957. OD.Handsoap_Optimizer$OD_ODP2_Value3[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[3]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  958. OD.Handsoap_Optimizer$OD_ODP2_Value4[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[4]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  959. OD.Handsoap_Optimizer$OD_ODP2_Value5[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[5]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  960. OD.Handsoap_Optimizer$OD_ODP2_Value6[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[6]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  961. OD.Handsoap_Optimizer$OD_ODP2_Value7[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[7]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  962. OD.Handsoap_Optimizer$OD_ODP2_Value8[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[8]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  963. OD.Handsoap_Optimizer$OD_ODP2_Value9[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[9]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  964. OD.Handsoap_Optimizer$OD_ODP2_Value10[i] <- (ODP_2_Floor[i]+OD_ODP2_Value_seq[10]*(ODP_2_Cap[i]-ODP_2_Floor[i])/9);
  965. }
  966. detach(OD.Handsoap_Optimizer)
  967.  
  968. # ------------------------------------------------------------------------------------------------------------------------------------------#
  969. # || OD Margin values : 1,2,3,4,5,6,7,8,9,10 ||
  970. # =IF(S1_Last_weeks_sales<=0,(EXP(Intercept2-0.03*(ODP_2_Value_1-STP_2_Latest_Staples_Price)+
  971. # 0.0013*S1_Last_weeks_sales)*(ODP_2_Value_1-Cost)),
  972. # (EXP(LN(S1_Last_weeks_sales)+0.03*(ODP_1_Last_weeks_OD_Price+STP_2_Latest_Staples_Price-STP_1_Last_weeks_Staples_price-ODP_2_Value_1)+
  973. # 0.0013*(S1_Last_weeks_sales-S0_Last_to_last_weeks_sales))*(ODP_2_Value_1-Cost)))
  974. # ------------------------------------------------------------------------------------------------------------------------------------------#
  975.  
  976. OD.Handsoap_Intercept <- read.table("Intercept.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  977. fill=TRUE, comment.char="", as.is=TRUE)
  978. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Intercept[,c("Full_List_of_SKU","Intercept2")],
  979. by.x="SKU",by.y=,"Full_List_of_SKU",all.x=T)
  980.  
  981. OD_ODP2_Margin_seq <- NA
  982. for(j in seq(1,10,by=1)){
  983. OD_ODP2_Margin_seq[j] <- j
  984. }
  985.  
  986. OD.Handsoap_Optimizer$OD_Margin2_Value1 <- NA;OD.Handsoap_Optimizer$OD_Margin2_Value2 <- NA;OD.Handsoap_Optimizer$OD_Margin2_Value3 <- NA
  987. OD.Handsoap_Optimizer$OD_Margin2_Value4 <- NA;OD.Handsoap_Optimizer$OD_Margin2_Value5 <- NA;OD.Handsoap_Optimizer$OD_Margin2_Value6 <- NA
  988. OD.Handsoap_Optimizer$OD_Margin2_Value7 <- NA;OD.Handsoap_Optimizer$OD_Margin2_Value8 <- NA;OD.Handsoap_Optimizer$OD_Margin2_Value9 <- NA
  989. OD.Handsoap_Optimizer$OD_Margin2_Value10 <- NA
  990.  
  991.  
  992. attach(OD.Handsoap_Optimizer)
  993. for(i in 1:nrow(OD.Handsoap_Optimizer)){
  994. OD.Handsoap_Optimizer$OD_Margin2_Value1[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  995. 0.03*(OD_ODP2_Value1[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value1[i]-Cost[i])),
  996. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value1[i])+
  997. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value1[i]-Cost[i])))
  998.  
  999. OD.Handsoap_Optimizer$OD_Margin2_Value2[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1000. 0.03*(OD_ODP2_Value2[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value2[i]-Cost[i])),
  1001. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value2[i])+
  1002. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value2[i]-Cost[i])))
  1003.  
  1004. OD.Handsoap_Optimizer$OD_Margin2_Value3[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1005. 0.03*(OD_ODP2_Value3[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value3[i]-Cost[i])),
  1006. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value3[i])+
  1007. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value3[i]-Cost[i])))
  1008.  
  1009. OD.Handsoap_Optimizer$OD_Margin2_Value4[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1010. 0.03*(OD_ODP2_Value4[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value4[i]-Cost[i])),
  1011. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value4[i])+
  1012. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value4[i]-Cost[i])))
  1013.  
  1014. OD.Handsoap_Optimizer$OD_Margin2_Value5[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1015. 0.03*(OD_ODP2_Value5[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value5[i]-Cost[i])),
  1016. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value5[i])+
  1017. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value5[i]-Cost[i])))
  1018.  
  1019. OD.Handsoap_Optimizer$OD_Margin2_Value6[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1020. 0.03*(OD_ODP2_Value6[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value6[i]-Cost[i])),
  1021. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value6[i])+
  1022. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value6[i]-Cost[i])))
  1023.  
  1024. OD.Handsoap_Optimizer$OD_Margin2_Value7[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1025. 0.03*(OD_ODP2_Value7[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value7[i]-Cost[i])),
  1026. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value7[i])+
  1027. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value7[i]-Cost[i])))
  1028.  
  1029. OD.Handsoap_Optimizer$OD_Margin2_Value8[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1030. 0.03*(OD_ODP2_Value8[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value8[i]-Cost[i])),
  1031. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value8[i])+
  1032. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value8[i]-Cost[i])))
  1033.  
  1034. OD.Handsoap_Optimizer$OD_Margin2_Value9[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1035. 0.03*(OD_ODP2_Value9[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value9[i]-Cost[i])),
  1036. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value9[i])+
  1037. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value9[i]-Cost[i])))
  1038.  
  1039. OD.Handsoap_Optimizer$OD_Margin2_Value10[i] <-ifelse(S1_Last_weeks_sales[i]<=0,(exp(Intercept2[i]-
  1040. 0.03*(OD_ODP2_Value10[i]-STP_2_Latest_Staples_Price[i])+0.0013*S1_Last_weeks_sales[i])*(OD_ODP2_Value10[i]-Cost[i])),
  1041. (exp(log(S1_Last_weeks_sales[i])+0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-OD_ODP2_Value10[i])+
  1042. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))*(OD_ODP2_Value10[i]-Cost[i])))
  1043. }
  1044. detach(OD.Handsoap_Optimizer)
  1045.  
  1046. OD.Handsoap_Optimizer <- OD.Handsoap_Optimizer[,grep("Intercept",names(OD.Handsoap_Optimizer),value=T,invert=T)]
  1047.  
  1048. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1049. # || Margins ||
  1050. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1051.  
  1052. # Max margin
  1053. OD.Handsoap_Optimizer$Max_Margin2 <- NA
  1054. for(i in 1:nrow(OD.Handsoap_Optimizer)){
  1055. OD.Handsoap_Optimizer$Max_Margin2[i] <- max(OD.Handsoap_Optimizer[i,grep("Margin",names(OD.Handsoap_Optimizer),value=T)],na.rm=T)
  1056. }
  1057.  
  1058. OD.Handsoap_Optimizer$Max_Margin2 <- as.numeric(ifelse(OD.Handsoap_Optimizer$Max_Margin2=="-Inf","NA",
  1059. OD.Handsoap_Optimizer$Max_Margin2))
  1060.  
  1061. # ODP2 for Max Margin
  1062. OD.Handsoap_Optimizer_MM <- OD.Handsoap_Optimizer[,grep(paste(toMatch <- c("OD_Margin","Max_Margin2"),collapse="|"),
  1063. names(OD.Handsoap_Optimizer),value=T)]
  1064. OD.Handsoap_Optimizer_Value <- OD.Handsoap_Optimizer[,grep("OD_ODP2",names(OD.Handsoap_Optimizer),value=T)]
  1065. rownames(OD.Handsoap_Optimizer_MM) <- NULL
  1066.  
  1067. ODP_2_for_Max_Margin_2 <- NA
  1068.  
  1069. for(j in 1:nrow(OD.Handsoap_Optimizer_MM)){
  1070. ODP_2_for_Max_Margin_2[j] <- list(OD.Handsoap_Optimizer_Value[j,match(OD.Handsoap_Optimizer_MM$Max_Margin2[j],
  1071. OD.Handsoap_Optimizer_MM[j,grep("OD_Margin",names(OD.Handsoap_Optimizer_MM),value=T)])])
  1072. }
  1073.  
  1074. ODP_2_for_Max_Margin_2 <- as.numeric(ifelse(ODP_2_for_Max_Margin_2=="NULL",NA,ODP_2_for_Max_Margin_2))
  1075.  
  1076. OD.Handsoap_Optimizer <- cbind(OD.Handsoap_Optimizer,ODP_2_for_Max_Margin_2)
  1077. rm(OD.Handsoap_Optimizer_MM,OD.Handsoap_Optimizer_Value)
  1078.  
  1079. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1080. # || Initial Recommended Price ||
  1081. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1082.  
  1083. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Rule_1234[,c("SKU","Latest_OD_Price")],by="SKU",all.x=T)
  1084.  
  1085. OD.Handsoap_Optimizer$ODP_2_Floor2 <- OD.Handsoap_Optimizer$ODP_2_Floor;OD.Handsoap_Optimizer$ODP_2_Cap2 <- OD.Handsoap_Optimizer$ODP_2_Cap
  1086.  
  1087. OD.Handsoap_Optimizer <- merge(OD.Handsoap_Optimizer,OD.Handsoap_Rule_1234[,c("SKU","Final_Action")],by="SKU",all.x=T)
  1088. OD.Handsoap_Optimizer$Final_Action[is.na(OD.Handsoap_Optimizer$Final_Action)] <- " "
  1089.  
  1090. OD.Handsoap_Optimizer$Initial_Rec_Price <- NA
  1091. for(i in 1:nrow(OD.Handsoap_Optimizer)){
  1092. OD.Handsoap_Optimizer$Initial_Rec_Price[i] <- ifelse(is.na(OD.Handsoap_Optimizer$ODP_2_for_Max_Margin_2[i]),
  1093. OD.Handsoap_Optimizer$Latest_OD_Price[i],OD.Handsoap_Optimizer$ODP_2_for_Max_Margin_2[i])
  1094. }
  1095. # Initial_Rec_Price <- round(ifelse(Initial_Rec_Price=="-Inf",NA,Initial_Rec_Price),3)
  1096.  
  1097. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1098. # || Flag, Volume change & Price change ||
  1099. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1100.  
  1101. OD.Handsoap_Optimizer$New_Cost <- OD.Handsoap_Optimizer$Cost
  1102. OD.Handsoap_Optimizer$New_Cost[is.na(OD.Handsoap_Optimizer$New_Cost)] <- 1000
  1103. OD.Handsoap_Optimizer$New_ODP_2_Floor2 <- OD.Handsoap_Optimizer$ODP_2_Floor2
  1104. OD.Handsoap_Optimizer$New_ODP_2_Floor2[is.na(OD.Handsoap_Optimizer$New_ODP_2_Floor2)] <- 1000
  1105. OD.Handsoap_Optimizer$New_ODP_2_Cap2 <- OD.Handsoap_Optimizer$ODP_2_Cap2
  1106. OD.Handsoap_Optimizer$New_ODP_2_Cap2[is.na(OD.Handsoap_Optimizer$New_ODP_2_Cap2)] <- 1000
  1107. OD.Handsoap_Optimizer$New_Max_Margin2 <- OD.Handsoap_Optimizer$Max_Margin2
  1108. OD.Handsoap_Optimizer$New_Max_Margin2[is.na(OD.Handsoap_Optimizer$New_Max_Margin2)] <- 1000
  1109.  
  1110. # Flag
  1111. OD.Handsoap_Optimizer$ODP_1_Last_weeks_OD_Price[is.na(OD.Handsoap_Optimizer$ODP_1_Last_weeks_OD_Price)] <- "N/A"
  1112.  
  1113. OD.Handsoap_Optimizer$Flag <- NA
  1114. attach(OD.Handsoap_Optimizer)
  1115. for(i in 1:nrow(OD.Handsoap_Optimizer)){
  1116. OD.Handsoap_Optimizer$Flag[i] <-
  1117. ifelse(New_Cost[i]==1000,"Cost not available",ifelse(ODP_1_Last_weeks_OD_Price[i]=="N/A" & New_ODP_2_Floor2[i]==1000,"Error in Floor / Cap",
  1118. ifelse(New_ODP_2_Floor2[i]==1000,"No optimization done",ifelse(New_Max_Margin2[i]==1000,"Margin calculation could not be done",
  1119. ifelse(New_ODP_2_Floor2[i]==Initial_Rec_Price[i],"Initial Reco = Floor",
  1120. ifelse(New_ODP_2_Cap2[i]==Initial_Rec_Price[i],"Initial Reco = Cap","Disctinct Initial Reco"))))))
  1121. }
  1122. detach(OD.Handsoap_Optimizer)
  1123.  
  1124. OD.Handsoap_Optimizer$ODP_1_Last_weeks_OD_Price <- as.numeric(OD.Handsoap_Optimizer$ODP_1_Last_weeks_OD_Price)
  1125. OD.Handsoap_Optimizer <- OD.Handsoap_Optimizer[,grep("New",names(OD.Handsoap_Optimizer),value=T,invert=TRUE)]
  1126.  
  1127. # Volume Change
  1128. OD.Handsoap_Optimizer$Volume_Change <- NA
  1129. attach(OD.Handsoap_Optimizer)
  1130. for(i in 1:nrow(OD.Handsoap_Optimizer)){
  1131. OD.Handsoap_Optimizer$Volume_Change[i] <- ifelse(Initial_Rec_Price[i]!=ODP_1_Last_weeks_OD_Price[i],
  1132. (Max_Margin2[i]/(ODP_2_for_Max_Margin_2[i]-Cost[i])/S1_Last_weeks_sales[i]),1)
  1133. }
  1134. detach(OD.Handsoap_Optimizer)
  1135. OD.Handsoap_Optimizer$Volume_Change <- paste(round(100*OD.Handsoap_Optimizer$Volume_Change,0),"%",sep="")
  1136. OD.Handsoap_Optimizer$Volume_Change <- ifelse(OD.Handsoap_Optimizer$Volume_Change=="NA%","#N/A",
  1137. ifelse(OD.Handsoap_Optimizer$Volume_Change=="Inf%","#DIV/0!",OD.Handsoap_Optimizer$Volume_Change))
  1138.  
  1139. # Price Change
  1140. OD.Handsoap_Optimizer$Price_Change <- OD.Handsoap_Optimizer$Initial_Rec_Price/OD.Handsoap_Optimizer$Latest_OD_Price
  1141. OD.Handsoap_Optimizer$Price_Change <- paste(round(100*OD.Handsoap_Optimizer$Price_Change,0),"%",sep="")
  1142. OD.Handsoap_Optimizer$Price_Change <- ifelse(OD.Handsoap_Optimizer$Price_Change=="NA%","#N/A",OD.Handsoap_Optimizer$Price_Change)
  1143.  
  1144. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1145. # || Export to Excel ||
  1146. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1147.  
  1148. save.image("~/OD Dynamic Pricing/OD_DynamicPricing/2.Rules/1.Handsoaps/Optimizer_v2.3.RData")
  1149.  
  1150. library(xlsx)
  1151. write.xlsx(OD.Handsoap_Optimizer,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1152. row.names=FALSE,sheetName="Optimizer",append=TRUE)
  1153.  
  1154.  
  1155. #------------------------------------------------------------------------------------------------------------------------------------------------
  1156. #------------------------------------------------------------------------------------------------------------------------------------------------
  1157. #------------------------------------------------------------------------------------------------------------------------------------------------
  1158. #------------------------- END ------------------------ Rules Implementation : Optimizer ---------------------------- END -----------------------
  1159. #------------------------------------------------------------------------------------------------------------------------------------------------
  1160. #------------------------------------------------------------------------------------------------------------------------------------------------
  1161. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1162. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1163. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1164. # || Rules Implementation : Post Processing Rules ||
  1165. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1166. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1167. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1168.  
  1169. rm(list=ls())
  1170. load("~/OD Dynamic Pricing/OD_DynamicPricing/2.Rules/1.Handsoaps/Optimizer_v2.3.RData")
  1171. rm(list=setdiff(ls(),c("OD.Handsoap_Rule_1234","OD.Handsoap_Sales","OD.Handsoap_Price_Prev_Week","OD.Handsoap_Price_Latest",
  1172. "OD.Handsoap_Cost","OD.Handsoap_Threshold","OD.Handsoap_Optimizer","OD.Handsoap_Intercept")))
  1173.  
  1174. library(plyr)
  1175.  
  1176. # Line rule
  1177.  
  1178. setwd("C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/1.Rules_data_files/5.Data_v2.3/1.Handsoaps/")
  1179.  
  1180. OD.Line_Family2 <- read.table("Post_Proc_Line_Family2.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1181. fill=TRUE, comment.char="", as.is=TRUE)
  1182.  
  1183. OD.Line_Family3 <- read.table("Post_Proc_Line_Family3.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1184. fill=TRUE, comment.char="", as.is=TRUE)
  1185.  
  1186. OD.Line_Family4 <- read.table("Post_Proc_Line_Family4.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1187. fill=TRUE, comment.char="", as.is=TRUE)
  1188.  
  1189. OD.Line_Family6 <- read.table("Post_Proc_Line_Family6.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1190. fill=TRUE, comment.char="", as.is=TRUE)
  1191.  
  1192. OD.Line_Family7 <- read.table("Post_Proc_Line_Family7.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1193. fill=TRUE, comment.char="", as.is=TRUE)
  1194.  
  1195. OD.Line_Family8 <- read.table("Post_Proc_Line_Family8.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1196. fill=TRUE, comment.char="", as.is=TRUE)
  1197.  
  1198. OD.Line_Family9 <- read.table("Post_Proc_Line_Family9.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1199. fill=TRUE, comment.char="", as.is=TRUE)
  1200.  
  1201. OD.Line_Brand_Family_12 <- read.table("Post_Proc_Brand_Family_12.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1202. fill=TRUE, comment.char="", as.is=TRUE)
  1203.  
  1204. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1205. # || Initial Recommended Price & Final Recommendation ||
  1206. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1207.  
  1208. # Family 2
  1209. OD.Line_Family2 <- merge(OD.Line_Family2,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1210. OD.Line_Family2$Number_SKU <- OD.Line_Family2$SKU
  1211.  
  1212. OD.Line_Family2$Final_Recommended_Price <- NA
  1213. for(i in 1:nrow(OD.Line_Family2)){
  1214. OD.Line_Family2$Final_Recommended_Price[i] <- ifelse(OD.Line_Family2$Conflict_Resolution[i]=="Primary",
  1215. OD.Line_Family2$Initial_Rec_Price[3],OD.Line_Family2$Initial_Rec_Price[3])
  1216. }
  1217.  
  1218. # Family 3
  1219. OD.Line_Family3 <- merge(OD.Line_Family3,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1220. OD.Line_Family3$Number_SKU <- OD.Line_Family3$SKU
  1221.  
  1222. OD.Line_Family3$Final_Recommended_Price <- NA
  1223. for(i in 1:nrow(OD.Line_Family3)){
  1224. OD.Line_Family3$Final_Recommended_Price[i] <- ifelse(OD.Line_Family3$Conflict_Resolution[i]=="Primary",
  1225. OD.Line_Family3$Initial_Rec_Price[2],OD.Line_Family3$Initial_Rec_Price[2])
  1226. }
  1227.  
  1228. # Family 4
  1229. OD.Line_Family4 <- merge(OD.Line_Family4,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1230. OD.Line_Family4$Number_SKU <- OD.Line_Family4$SKU
  1231.  
  1232. OD.Line_Family4$Final_Recommended_Price <- NA
  1233. for(i in 1:nrow(OD.Line_Family4)){
  1234. OD.Line_Family4$Final_Recommended_Price[i] <- ifelse(OD.Line_Family4$Conflict_Resolution[i]=="Primary",
  1235. OD.Line_Family4$Initial_Rec_Price[2],OD.Line_Family4$Initial_Rec_Price[2])
  1236. }
  1237.  
  1238. # Family 6
  1239. OD.Line_Family6 <- merge(OD.Line_Family6,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1240. OD.Line_Family6$Number_SKU <- OD.Line_Family6$SKU
  1241.  
  1242. OD.Line_Family6$Final_Recommended_Price <- NA
  1243. for(i in 1:nrow(OD.Line_Family6)){
  1244. OD.Line_Family6$Final_Recommended_Price[i] <- ifelse(OD.Line_Family6$Conflict_Resolution[i]=="Primary",
  1245. OD.Line_Family6$Initial_Rec_Price[2],OD.Line_Family6$Initial_Rec_Price[2])
  1246. }
  1247.  
  1248. # Family 7
  1249. OD.Line_Family7 <- merge(OD.Line_Family7,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1250. OD.Line_Family7$Number_SKU <- OD.Line_Family7$SKU
  1251.  
  1252. OD.Line_Family7$Final_Recommended_Price <- NA
  1253. for(i in 1:nrow(OD.Line_Family7)){
  1254. OD.Line_Family7$Final_Recommended_Price[i] <- ifelse(OD.Line_Family7$Conflict_Resolution[i]=="Primary",
  1255. OD.Line_Family7$Initial_Rec_Price[1],OD.Line_Family7$Initial_Rec_Price[1])
  1256. }
  1257.  
  1258. # Family 8
  1259. OD.Line_Family8 <- merge(OD.Line_Family8,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1260. OD.Line_Family8$Number_SKU <- OD.Line_Family8$SKU
  1261.  
  1262. OD.Line_Family8$Final_Recommended_Price <- NA
  1263. for(i in 1:nrow(OD.Line_Family8)){
  1264. OD.Line_Family8$Final_Recommended_Price[i] <- ifelse(OD.Line_Family8$Conflict_Resolution[i]=="Primary",
  1265. OD.Line_Family8$Initial_Rec_Price[3],OD.Line_Family8$Initial_Rec_Price[3])
  1266. }
  1267.  
  1268. # Family 9 : Ignore Error
  1269. OD.Line_Family9 <- merge(OD.Line_Family9,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1270. OD.Line_Family9$Number_SKU <- OD.Line_Family9$SKU
  1271.  
  1272. OD.Line_Family9$Final_Recommended_Price <- NA
  1273. for(i in 1:nrow(OD.Line_Family9)){
  1274. OD.Line_Family9$Final_Recommended_Price[i] <- ifelse(OD.Line_Family9$Conflict_Resolution[i]=="Primary",
  1275. OD.Line_Family9$Initial_Rec_Price[2],OD.Line_Family9$Initial_Rec_Price[2])
  1276. }
  1277.  
  1278. # Brand Family1 & Family 2
  1279. OD.Line_Brand_Family_12 <- merge(OD.Line_Brand_Family_12,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1280. OD.Line_Brand_Family_12$Number_SKU <- OD.Line_Brand_Family_12$SKU
  1281.  
  1282. attach(OD.Line_Brand_Family_12)
  1283. OD.Line_Brand_Family_12$Final_Recommended_Price <- ifelse(Brand_Class=="PRIVATE",
  1284. ifelse(Initial_Rec_Price<Initial_Rec_Price[2] & Initial_Rec_Price<Initial_Rec_Price[3],Initial_Rec_Price,
  1285. 0.99*min(Initial_Rec_Price[2],Initial_Rec_Price[3])),Initial_Rec_Price)
  1286. detach(OD.Line_Brand_Family_12)
  1287.  
  1288. OD.Line_Brand_Family_12 <- rename(OD.Line_Brand_Family_12,c("Brand_Class"="Conflict_Resolution"))
  1289.  
  1290. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1291. # || Export to Excel ||
  1292. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1293.  
  1294. library(xlsx)
  1295.  
  1296. OD.DP_Post_Proc <- rbind(OD.Line_Family2,OD.Line_Family3,OD.Line_Family4,OD.Line_Family6,OD.Line_Family7,OD.Line_Family8,
  1297. OD.Line_Family9,OD.Line_Brand_Family_12)
  1298. OD.DP_Post_Proc$Initial_Rec_Price <- round(OD.DP_Post_Proc$Initial_Rec_Price,2)
  1299. OD.DP_Post_Proc$Final_Recommended_Price <- round(OD.DP_Post_Proc$Final_Recommended_Price,2)
  1300.  
  1301. save.image("~/OD Dynamic Pricing/OD_DynamicPricing/2.Rules/1.Handsoaps/Post_Proc_Rules_v2.3.RData")
  1302.  
  1303. save.xlsx <- function (file, ...)
  1304. {
  1305. require(xlsx, quietly = TRUE)
  1306. objects <- list(...)
  1307. fargs <- as.list(match.call(expand.dots = TRUE))
  1308. objnames <- as.character(fargs)[-c(1, 2)]
  1309. nobjects <- length(objects)
  1310. for (i in 1:nobjects) {
  1311. if (i == 1)
  1312. write.xlsx(objects[[i]], file, sheetName = objnames[i],row.names=FALSE)
  1313. else write.xlsx(objects[[i]], file, sheetName = objnames[i],row.names=FALSE,append = TRUE)
  1314. }
  1315. print(paste("Workbook", file, "has", nobjects, "worksheets."))
  1316. }
  1317.  
  1318. save.xlsx("C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/1.Rules_data_files/5.Data_v2.3/1.Handsoaps/Handsoaps_Post_Proc2.3.xlsx",
  1319. OD.Line_Family2,OD.Line_Family3,OD.Line_Family4,OD.Line_Family6,OD.Line_Family7,OD.Line_Family8,OD.Line_Family9,
  1320. OD.Line_Brand_Family_12,OD.DP_Post_Proc)
  1321.  
  1322. write.xlsx(OD.DP_Post_Proc,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1323. row.names=FALSE,sheetName="Post Processing",append=TRUE)
  1324.  
  1325. #------------------------------------------------------------------------------------------------------------------------------------------------
  1326. #------------------------------------------------------------------------------------------------------------------------------------------------
  1327. #------------------------------------------------------------------------------------------------------------------------------------------------
  1328. #-------------------- END ------------------------ Rules Implementation : Post Processing Rules ---------------------------- END ----------------
  1329. #------------------------------------------------------------------------------------------------------------------------------------------------
  1330. #------------------------------------------------------------------------------------------------------------------------------------------------
  1331. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1332. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1333. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1334. # || Rules Implementation : Final Delivery ||
  1335. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1336. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1337. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1338.  
  1339. rm(list=ls())
  1340. load("~/OD Dynamic Pricing/OD_DynamicPricing/2.Rules/1.Handsoaps/Post_Proc_Rules_v2.3.RData")
  1341. rm(list=setdiff(ls(),c("OD.Handsoap_Rule_1234","OD.Handsoap_Sales","OD.Handsoap_Price_Prev_Week","OD.Handsoap_Price_Latest",
  1342. "OD.Handsoap_Cost","OD.Handsoap_Threshold","OD.Handsoap_Optimizer","OD.DP_Post_Proc","OD.Handsoap_Intercept")))
  1343.  
  1344. setwd("C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/1.Rules_data_files/5.Data_v2.3/1.Handsoaps/")
  1345. OD.Handsoap_Promotions <- read.table("Promotions.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1346. fill=TRUE, comment.char="", as.is=TRUE)
  1347. OD.Handsoap_Final_Del <- read.table("Final_Delivery.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1348. fill=TRUE, comment.char="", as.is=TRUE)
  1349. OD.Handsoap_Final_Del$Date_changed[is.na(OD.Handsoap_Final_Del$Date_changed)] <- " "
  1350.  
  1351. # Current OD Price & Final Action
  1352. library(plyr)
  1353. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Price_Latest[,c("SKU","od_final_price")],by="SKU",all.x=T)
  1354. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Optimizer[,c("SKU","Final_Action")],by="SKU",all.x=T)
  1355. OD.Handsoap_Final_Del <- rename(OD.Handsoap_Final_Del,c("od_final_price"="Current_OD_Price"))
  1356. # OD.Handsoap_Final_Del$Final_Action <- ifelse(OD.Handsoap_Final_Del$SKU=="521845",OD.Handsoap_Final_Del$Final_Action==NA,
  1357. # OD.Handsoap_Final_Del$Final_Action)
  1358.  
  1359. # Post Processing Price
  1360. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.DP_Post_Proc[,c("SKU","Final_Recommended_Price")],by="SKU",all.x=T)
  1361. OD.Handsoap_Final_Del <- rename(OD.Handsoap_Final_Del,c("Final_Recommended_Price"="Post_Processing_Price"))
  1362. OD.Handsoap_Final_Del$Post_Processing_Price[is.na(OD.Handsoap_Final_Del$Post_Processing_Price)] <- " "
  1363.  
  1364. # Final Recommendation Price
  1365. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1366. OD.Handsoap_Final_Del$Final_Recommendation <- as.numeric(ifelse(OD.Handsoap_Final_Del$Post_Processing_Price!=" ",
  1367. OD.Handsoap_Final_Del$Post_Processing_Price,OD.Handsoap_Final_Del$Initial_Rec_Price))
  1368.  
  1369. OD.Handsoap_Final_Del <- OD.Handsoap_Final_Del[,grep("Initial",names(OD.Handsoap_Final_Del),value=T,invert=T)]
  1370.  
  1371. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1372. # || Output ||
  1373. # =IF(ISERROR(B2),"Current OD Price Not Avaiable",IF(OR(Final Action="R",Final Action="I"),
  1374. # IF(Final Recommendation=Current OD Price,"Rule Triggered, No Price Change","Rule Triggered, Price Change"),"Rule Not Triggered"))
  1375. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1376.  
  1377. OD.Handsoap_Final_Del$New_Current_OD_Price <- as.numeric(OD.Handsoap_Final_Del$Current_OD_Price)
  1378. OD.Handsoap_Final_Del$New_Current_OD_Price[is.na(OD.Handsoap_Final_Del$New_Current_OD_Price)] <- 1000
  1379.  
  1380. # IF(ISERROR(B2),"Current OD Price Not Avaiable",IF(OR(C2="R",C2="I"),
  1381. # IF(E2=B2,"Rule Triggered, No Price Change","Rule Triggered, Price Change"),"Rule Not Triggered"))
  1382. OD.Handsoap_Final_Del$Output <- NA
  1383. attach(OD.Handsoap_Final_Del)
  1384. for(i in 1:nrow(OD.Handsoap_Final_Del)){
  1385. OD.Handsoap_Final_Del$Output[i] <-ifelse(New_Current_OD_Price[i]==1000,"Current OD Price Not Avaiable",
  1386. ifelse(Final_Action[i]=="R" | Final_Action[i]=="I",
  1387. ifelse(Final_Recommendation[i]==New_Current_OD_Price[i],"Rule Triggered, No Price Change","Rule Triggered, Price Change"),
  1388. "Rule Not Triggered"))
  1389. }
  1390. detach(OD.Handsoap_Final_Del)
  1391.  
  1392. OD.Handsoap_Final_Del <- OD.Handsoap_Final_Del[,grep("New",names(OD.Handsoap_Final_Del),value=T,invert=T)]
  1393. OD.Handsoap_Final_Del$Final_Recommendation <- round(OD.Handsoap_Final_Del$Final_Recommendation,1)-0.01
  1394.  
  1395. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1396. # || Other Attributes ||
  1397. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1398.  
  1399. # Promotions Flag
  1400. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Promotions,all.x=T)
  1401. OD.Handsoap_Final_Del <- rename(OD.Handsoap_Final_Del,c("Max_Week"="Promotion_Flag"))
  1402. OD.Handsoap_Final_Del$Promotion_Flag[is.na(OD.Handsoap_Final_Del$Promotion_Flag)] <- " "
  1403.  
  1404. # Fiscal Week
  1405. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Promotions[,c("SKU","Max_Week")],by="SKU",all.x=T)
  1406. OD.Handsoap_Final_Del <- rename(OD.Handsoap_Final_Del,c("Max_Week"="Fiscal_Week"))
  1407.  
  1408. for(i in 1:nrow(OD.Handsoap_Final_Del)){
  1409. OD.Handsoap_Final_Del$Fiscal_Week[i] <-ifelse(OD.Handsoap_Final_Del$Promotion_Flag[i]!=" ",OD.Handsoap_Final_Del$Fiscal_Week[i]," ")
  1410. }
  1411.  
  1412. # Price given to office depot
  1413. OD.Handsoap_Final_Del$Price_given_to_Office_Depot <- NA
  1414. attach(OD.Handsoap_Final_Del)
  1415. for(i in 1:nrow(OD.Handsoap_Final_Del)){
  1416. OD.Handsoap_Final_Del$Price_given_to_Office_Depot[i] <- ifelse(Test_Control_Flag[i]=="C",Current_OD_Price[i],
  1417. ifelse(Fiscal_Week[i]!=" ",Current_OD_Price[i],Final_Recommendation[i]))
  1418. }
  1419. detach(OD.Handsoap_Final_Del)
  1420.  
  1421. # Intial Recommendation
  1422. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Optimizer[,c("SKU","Initial_Rec_Price")],by="SKU",all.x=T)
  1423. OD.Handsoap_Final_Del$Initial_Rec_Price <- round(OD.Handsoap_Final_Del$Initial_Rec_Price,3)
  1424.  
  1425. # Volume Change
  1426. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Optimizer[,c("SKU","S1_Last_weeks_sales","S0_Last_to_last_weeks_sales",
  1427. "ODP_1_Last_weeks_OD_Price","STP_2_Latest_Staples_Price","STP_1_Last_weeks_Staples_price")],by="SKU",all.x=T)
  1428.  
  1429. OD.Handsoap_Final_Del$Volume_Change <- NA
  1430. attach(OD.Handsoap_Final_Del)
  1431. for(i in 1:nrow(OD.Handsoap_Final_Del)){
  1432. OD.Handsoap_Final_Del$Volume_Change[i] <- ifelse(Current_OD_Price[i]==Price_given_to_Office_Depot[i],0,(exp(log(S1_Last_weeks_sales[i])+
  1433. 0.03*(ODP_1_Last_weeks_OD_Price[i]+STP_2_Latest_Staples_Price[i]-STP_1_Last_weeks_Staples_price[i]-Price_given_to_Office_Depot[i])+
  1434. 0.0013*(S1_Last_weeks_sales[i]-S0_Last_to_last_weeks_sales[i]))/S1_Last_weeks_sales[i]))
  1435. }
  1436. detach(OD.Handsoap_Final_Del)
  1437. OD.Handsoap_Final_Del$Volume_Change <- paste(round(100*OD.Handsoap_Final_Del$Volume_Change,0),"%",sep="")
  1438. OD.Handsoap_Final_Del$Volume_Change <- ifelse(OD.Handsoap_Final_Del$Volume_Change=="NA%" | OD.Handsoap_Final_Del$Volume_Change=="NaN%",
  1439. "#N/A",OD.Handsoap_Final_Del$Volume_Change)
  1440.  
  1441. # Price Change
  1442. OD.Handsoap_Final_Del$Price_Change <- OD.Handsoap_Final_Del$Price_given_to_Office_Depot/OD.Handsoap_Final_Del$Current_OD_Price
  1443. OD.Handsoap_Final_Del$Price_Change <- paste(round(100*OD.Handsoap_Final_Del$Price_Change,0),"%",sep="")
  1444. OD.Handsoap_Final_Del$Price_Change <- ifelse(OD.Handsoap_Final_Del$Price_Change=="NA%"| OD.Handsoap_Final_Del$Price_Change=="NaN%",
  1445. "#N/A",OD.Handsoap_Final_Del$Price_Change)
  1446.  
  1447. # Cost & Rule Triggered
  1448. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Cost[,c("SKU","WTD_UNIT_COST")],by="SKU",all.x=T)
  1449. OD.Handsoap_Final_Del <- merge(OD.Handsoap_Final_Del,OD.Handsoap_Rule_1234[,c("SKU","Rules_Triggered")],by="SKU",all.x=T)
  1450. OD.Handsoap_Final_Del <- rename(OD.Handsoap_Final_Del,c("WTD_UNIT_COST"="Cost"))
  1451. OD.Handsoap_Final_Del$Cost <- round(OD.Handsoap_Final_Del$Cost,2)
  1452.  
  1453. # Weekwise Volume
  1454. OD.Handsoap_Final_Del$Last_Week_Volume <- OD.Handsoap_Final_Del$S1_Last_weeks_sales
  1455.  
  1456. OD.Handsoap_Final_Del$This_Week_Volume <- NA
  1457. attach(OD.Handsoap_Final_Del)
  1458. for(i in 1:nrow(OD.Handsoap_Final_Del)){
  1459. OD.Handsoap_Final_Del$This_Week_Volume[i] <-
  1460. ifelse(is.na(exp(log(S1_Last_weeks_sales[i])+
  1461. 0.03*(as.numeric(ODP_1_Last_weeks_OD_Price)[i]+as.numeric(STP_2_Latest_Staples_Price)[i]-as.numeric(STP_1_Last_weeks_Staples_price)[i]-as.numeric(Price_given_to_Office_Depot)[i])+
  1462. 0.0013*(as.numeric(S1_Last_weeks_sales)[i]-as.numeric(S0_Last_to_last_weeks_sales)[i]))),S1_Last_weeks_sales[i],exp(log(as.numeric(S1_Last_weeks_sales)[i])+
  1463. 0.03*(as.numeric(ODP_1_Last_weeks_OD_Price)[i]+as.numeric(STP_2_Latest_Staples_Price)[i]-as.numeric(STP_1_Last_weeks_Staples_price)[i]-as.numeric(Price_given_to_Office_Depot)[i])+
  1464. 0.0013*(as.numeric(S1_Last_weeks_sales)[i]-as.numeric(S0_Last_to_last_weeks_sales)[i])))
  1465. }
  1466. detach(OD.Handsoap_Final_Del)
  1467.  
  1468. # Weekwise Revenue
  1469. OD.Handsoap_Final_Del$Last_Week_Revenue <- OD.Handsoap_Final_Del$Last_Week_Volume*OD.Handsoap_Final_Del$ODP_1_Last_weeks_OD_Price
  1470. OD.Handsoap_Final_Del$Last_Week_Revenue[is.na(OD.Handsoap_Final_Del$Last_Week_Revenue)] <- 0
  1471. OD.Handsoap_Final_Del$This_Week_Revenue <- OD.Handsoap_Final_Del$This_Week_Volume*OD.Handsoap_Final_Del$Price_given_to_Office_Depot
  1472. OD.Handsoap_Final_Del$This_Week_Revenue[is.na(OD.Handsoap_Final_Del$This_Week_Revenue)] <- 0
  1473.  
  1474. # Weekwise Margin
  1475. OD.Handsoap_Final_Del$Last_Week_Margin <- OD.Handsoap_Final_Del$Last_Week_Volume*(OD.Handsoap_Final_Del$ODP_1_Last_weeks_OD_Price-OD.Handsoap_Final_Del$Cost)
  1476. OD.Handsoap_Final_Del$Last_Week_Margin[is.na(OD.Handsoap_Final_Del$Last_Week_Margin)] <- 0
  1477. OD.Handsoap_Final_Del$This_Week_Margin <- OD.Handsoap_Final_Del$This_Week_Volume*(OD.Handsoap_Final_Del$Price_given_to_Office_Depot-OD.Handsoap_Final_Del$Cost)
  1478. OD.Handsoap_Final_Del$This_Week_Margin[is.na(OD.Handsoap_Final_Del$This_Week_Margin)] <- 0
  1479.  
  1480. # Weekwise Price
  1481. OD.Handsoap_Final_Del$Last_Week_Price <- ifelse(is.na(OD.Handsoap_Final_Del$ODP_1_Last_weeks_OD_Price)," ",
  1482. OD.Handsoap_Final_Del$ODP_1_Last_weeks_OD_Price)
  1483. OD.Handsoap_Final_Del$This_Week_Price <- ifelse(is.na(OD.Handsoap_Final_Del$Price_given_to_Office_Depot)," ",
  1484. OD.Handsoap_Final_Del$Price_given_to_Office_Depot)
  1485.  
  1486.  
  1487. # Arrange Columns
  1488.  
  1489. OD.Handsoap_Final_Del <- OD.Handsoap_Final_Del[,c("SKU","Current_OD_Price","Final_Action","Post_Processing_Price","Final_Recommendation",
  1490. "Output","Promotion_Flag","Fiscal_Week","Test_Control_Flag","Price_given_to_Office_Depot",
  1491. "Date_given","Date_changed","Initial_Rec_Price","Volume_Change","Price_Change",
  1492. "S1_Last_weeks_sales","S0_Last_to_last_weeks_sales","ODP_1_Last_weeks_OD_Price",
  1493. "STP_2_Latest_Staples_Price","STP_1_Last_weeks_Staples_price","Cost","Rules_Triggered",
  1494. "Last_Week_Volume","This_Week_Volume","Last_Week_Revenue","This_Week_Revenue",
  1495. "Last_Week_Margin","This_Week_Margin","Last_Week_Price","This_Week_Price")]
  1496.  
  1497. OD.Handsoap_Final_Del[is.na(OD.Handsoap_Final_Del)] <- "#N/A"
  1498.  
  1499. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1500. # || Frequency MIS ||
  1501. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1502.  
  1503. OD.Handsoap_Freq_MIS <- aggregate(OD.Handsoap_Final_Del$Output,
  1504. list(OD.Handsoap_Final_Del$Output,OD.Handsoap_Final_Del$Final_Action),length)
  1505. names(OD.Handsoap_Freq_MIS) <- c("Output","Final_Action","Count")
  1506.  
  1507. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1508. # || Export to Excel ||
  1509. # -----------------------------------------------------------------------------------------------------------------------------------------------#
  1510.  
  1511. OD.Handsoap_Last_Fiscal_Week <- read.table("Last_Fiscal_Week.csv",header = TRUE, sep = ",", quote = "\"", dec = ".",
  1512. fill=TRUE, comment.char="", as.is=TRUE)
  1513.  
  1514. save.image("~/OD Dynamic Pricing/OD_DynamicPricing/2.Rules/1.Handsoaps/Final_Delivery_Freq_MIS_v2.3.RData")
  1515.  
  1516. library(xlsx)
  1517. write.xlsx(OD.Handsoap_Final_Del,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1518. row.names=FALSE,sheetName="Final Delivery",append=TRUE)
  1519.  
  1520. write.xlsx(OD.Handsoap_Last_Fiscal_Week,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1521. row.names=FALSE,sheetName="Last Fiscal Week",append=TRUE)
  1522.  
  1523. write.xlsx(OD.Handsoap_Sales,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1524. row.names=FALSE,sheetName="Sales",append=TRUE)
  1525.  
  1526. write.xlsx(OD.Handsoap_Cost,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1527. row.names=FALSE,sheetName="Cost",append=TRUE)
  1528.  
  1529. write.xlsx(OD.Handsoap_Price_Latest,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1530. row.names=FALSE,sheetName="Price Latest",append=TRUE)
  1531.  
  1532. write.xlsx(OD.Handsoap_Price_Prev_Week,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1533. row.names=FALSE,sheetName="Price Prev Week",append=TRUE)
  1534.  
  1535. write.xlsx(OD.Handsoap_Threshold,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1536. row.names=FALSE,sheetName="Thresholds",append=TRUE)
  1537.  
  1538. write.xlsx(OD.Handsoap_Promotions,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1539. row.names=FALSE,sheetName="Promotions",append=TRUE)
  1540.  
  1541. write.xlsx(OD.Handsoap_Intercept,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1542. row.names=FALSE,sheetName="Intercept",append=TRUE)
  1543.  
  1544. write.xlsx(OD.Handsoap_Freq_MIS,"C:/Yashwanth/Pricing/1.OfficeDepot/10.Rules/3.Execution/1.Handsoaps/Handsoap_execution_R_v2.3.xlsx",
  1545. row.names=FALSE,sheetName="Handsoap Process MIS",append=TRUE)
  1546.  
  1547. #------------------------------------------------------------------------------------------------------------------------------------------------
  1548. #------------------------------------------------------------------------------------------------------------------------------------------------
  1549. #------------------------------------------------------------------------------------------------------------------------------------------------
  1550. #------------------------- END ------------------------ Rules Implementation : Final Delivery---------------------------- END -------------------
  1551. #------------------------------------------------------------------------------------------------------------------------------------------------
  1552. #------------------------------------------------------------------------------------------------------------------------------------------------
  1553.  
  1554. # -----------------------------------------------------------------------------------------------------------------------------------------#
  1555. # -----------------------------------------------------------------------------------------------------------------------------------------#
  1556. # || Rules Implementation ||
  1557. # -----------------------------------------------------------------------------------------------------------------------------------------#
  1558. # -----------------------------------------------------------------------------------------------------------------------------------------#
  1559.  
  1560. cat("\014")
  1561. rm(list=ls())
  1562.  
  1563. setwd("C:/Yashwanth/Pricing/1.OfficeDepot/5.Scripts/3.Rules/1.HandSoaps/Handsoaps_Final_Scripts_09042015/")
  1564.  
  1565. source("1.OD_DP_Rules_HandSoaps_v2.3.R")
  1566. source("2.OD_DP_Optimizer_v2.3.R")
  1567. source("3.OD_DP_Post_Proc_Rules_v2.3.R")
  1568. source("4.OD_DP_Final_Delivery_Freq_MIS_v2.3.R")
  1569.  
  1570. rm(list=ls())
  1571.  
  1572. source("5.OD_DP_All_Scripts_v2.3.R")
  1573.  
  1574. rm(list=ls());cat("\014")
  1575.  
  1576. # ------------------------------------------------------------------------------------------------------------------------------------------#
  1577. #---------------------- END ----------------------------- Rules Implementation -------------------------- END ------------------------------#
  1578. # ------------------------------------------------------------------------------------------------------------------------------------------#
  1579.  
  1580. # --------------------------------------------------------------------------------------------------------------
  1581. # --------------------------------------------------------------------------------------------------------------
  1582. # --------------------------------------------------------------------------------------------------------------
  1583. # || Price Elasticity ||
  1584. # --------------------------------------------------------------------------------------------------------------
  1585. # --------------------------------------------------------------------------------------------------------------
  1586. # --------------------------------------------------------------------------------------------------------------
  1587.  
  1588. # --------------------------------------------------------------------------------------------------------------
  1589. # --------------------------------------------------------------------------------------------------------------
  1590. # Linear Regression
  1591. # --------------------------------------------------------------------------------------------------------------
  1592. # --------------------------------------------------------------------------------------------------------------
  1593.  
  1594. rm(list=ls());cat("\014")
  1595. OD.DP_Comm <- read.table("C:/Yashwanth/Pricing/1.OfficeDepot/7.Data to Import/Commercial.csv",
  1596. header = TRUE, sep = ",", quote = "\"", dec = "." ,fill=TRUE, comment.char="", as.is=TRUE)
  1597.  
  1598. toMatch <- c("SKU","sales","final_price","pr_diff","min","week")
  1599. OD.DP_Comm <- OD.DP_Comm[,grep(paste(toMatch,collapse="|"),names(OD.DP_Comm),value=T)]
  1600.  
  1601. # --------------------------------------------------------------------------------------------------------------
  1602. # || Remove -ve Sales ||
  1603. # --------------------------------------------------------------------------------------------------------------
  1604.  
  1605. OD.DP_Comm <- data.frame(subset(OD.DP_Comm,sales_units>0,names(OD.DP_Comm)),row.names=NULL)
  1606.  
  1607. # --------------------------------------------------------------------------------------------------------------
  1608. # || Sales Trend ||
  1609. # --------------------------------------------------------------------------------------------------------------
  1610.  
  1611. plot(OD.DP_Comm$weekno,OD.DP_Comm$sales_units,type="l")
  1612.  
  1613. #---------------------------------------------------------------------------------------------------------------
  1614. # Descriptive Statistics
  1615. #---------------------------------------------------------------------------------------------------------------
  1616.  
  1617. summary(OD.DP_Comm[sapply(OD.DP_Comm,is.numeric)]) # Select only numeric columns
  1618.  
  1619. #---------------------------------------------------------------------------------------------------------------
  1620. # Correlataion Matrix
  1621. #---------------------------------------------------------------------------------------------------------------
  1622.  
  1623. toMatch_Com <- c("SKU","weekno","ln_")
  1624. names(OD.DP_Comm[,grep(paste(toMatch_Com,collapse="|"),names(OD.DP_Comm),value=T,invert=T)])
  1625. OD.DP_Comm_Cor <- OD.DP_Comm[,grep(paste(toMatch_Com,collapse="|"),names(OD.DP_Comm),value=T,invert=T)]
  1626.  
  1627. View(cor(OD.DP_Comm_Cor,use="na.or.complete"))
  1628. write.csv(cor(OD.DP_Comm_Cor,use="na.or.complete"),
  1629. "C:/Yashwanth/Pricing/1.OfficeDepot/8.Data Output/1.Commercial/Cor_Commercial.csv")
  1630.  
  1631. #---------------------------------------------------------------------------------------------------------------
  1632. # Transform Variables
  1633. #---------------------------------------------------------------------------------------------------------------
  1634.  
  1635. OD.DP_Comm_Trans <- OD.DP_Comm
  1636. OD.DP_Comm_Trans$sales_units <- sqrt(OD.DP_Comm_Trans$sales_units)
  1637. OD.DP_Comm_Trans$Amz_final_price <- (1/OD.DP_Comm_Trans$Amz_final_price)
  1638. OD.DP_Comm_Trans$Amz_mkt_final_price <- (1/OD.DP_Comm_Trans$Amz_mkt_final_price)
  1639. OD.DP_Comm_Trans$staples_final_price <- (1/OD.DP_Comm_Trans$staples_final_price)
  1640. OD.DP_Comm_Trans$min_comp_price <- (1/OD.DP_Comm_Trans$min_comp_price)
  1641.  
  1642. toMatch_Com <- c("SKU","weekno","ln_")
  1643. names(OD.DP_Comm_Trans[,grep(paste(toMatch_Com,collapse="|"),names(OD.DP_Comm_Trans),value=T,invert=T)])
  1644. OD.DP_Comm_Trans <- OD.DP_Comm_Trans[,grep(paste(toMatch_Com,collapse="|"),names(OD.DP_Comm_Trans),value=T,invert=T)]
  1645.  
  1646. View(cor(OD.DP_Comm_Trans,use="na.or.complete"))
  1647.  
  1648. #---------------------------------------------------------------------------------------------------------------
  1649. # Distribution of Variables : Histogram
  1650. #---------------------------------------------------------------------------------------------------------------
  1651.  
  1652. # Office Depot
  1653. dev.off()
  1654. par(mfrow=c(1,2))
  1655. hist(OD.DP_Comm_Trans$sales_units,prob=T,xlab="Sales Units",main="Sales Units distribution")
  1656. curve(dnorm(x,mean=mean(OD.DP_Comm_Trans$sales_units),sd=sd(OD.DP_Comm_Trans$sales_units)),add=TRUE)
  1657. hist(log(OD.DP_Comm_Trans$sales_units),prob=T,xlab="log(Sales Units)",main="Sales Units distribution")
  1658. curve(dnorm(x,mean=mean(log(OD.DP_Comm_Trans$sales_units)),sd=sd(log(OD.DP_Comm_Trans$sales_units))),add=TRUE)
  1659.  
  1660. par(mfrow=c(1,2))
  1661. hist(OD.DP_Comm_Trans$od_final_price,prob=T,xlab="Final Price",main="OD Final price distribution")
  1662. curve(dnorm(x,mean=mean(OD.DP_Comm_Trans$od_final_price),sd=sd(OD.DP_Comm_Trans$od_final_price)),add=TRUE)
  1663. hist(log(OD.DP_Comm_Trans$od_final_price),prob=T,xlab="log(Final Price)",main="OD Final price distribution")
  1664. curve(dnorm(x,mean=mean(log(OD.DP_Comm_Trans$od_final_price)),sd=sd(log(OD.DP_Comm_Trans$od_final_price))),add=TRUE)
  1665.  
  1666.  
  1667. # Amazon
  1668. par(mfrow=c(1,2))
  1669. hist(OD.DP_Comm_Trans$Amz_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  1670. curve(dnorm(x,mean=mean(OD.DP_Comm_Trans$Amz_final_price,na.rm=T),sd=sd(OD.DP_Comm_Trans$Amz_final_price,na.rm=T)),add=TRUE)
  1671. hist(log(OD.DP_Comm_Trans$Amz_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  1672. curve(dnorm(x,mean=mean(log(OD.DP_Comm_Trans$Amz_final_price),na.rm=T),sd=sd(log(OD.DP_Comm_Trans$Amz_final_price),na.rm=T)),add=TRUE)
  1673.  
  1674. # Amazon Marketplace
  1675. par(mfrow=c(1,2))
  1676. hist(OD.DP_Comm_Trans$Amz_mkt_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  1677. curve(dnorm(x,mean=mean(OD.DP_Comm_Trans$Amz_mkt_final_price,na.rm=T),sd=sd(OD.DP_Comm_Trans$Amz_mkt_final_price,na.rm=T)),add=TRUE)
  1678. hist(log(OD.DP_Comm_Trans$Amz_mkt_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  1679. curve(dnorm(x,mean=mean(log(OD.DP_Comm_Trans$Amz_mkt_final_price),na.rm=T),sd=sd(log(OD.DP_Comm_Trans$Amz_mkt_final_price),na.rm=T)),add=TRUE)
  1680.  
  1681. # Staples
  1682. par(mfrow=c(1,2))
  1683. hist(OD.DP_Comm_Trans$staples_final_price,prob=T,xlab="Final Price",main="Amazon Final price distribution")
  1684. curve(dnorm(x,mean=mean(OD.DP_Comm_Trans$staples_final_price,na.rm=T),sd=sd(OD.DP_Comm_Trans$staples_final_price,na.rm=T)),add=TRUE)
  1685. hist(log(OD.DP_Comm_Trans$staples_final_price),prob=T,xlab="log(Final Price)",main="Amazon Final price distribution")
  1686. curve(dnorm(x,mean=mean(log(OD.DP_Comm_Trans$staples_final_price),na.rm=T),sd=sd(log(OD.DP_Comm_Trans$staples_final_price),na.rm=T)),add=TRUE)
  1687.  
  1688. # Min Competitor Price
  1689. par(mfrow=c(1,2))
  1690. hist(OD.DP_Comm_Trans$min_comp_price,prob=T,xlab="Min Competitor Price",main="Min Competitor price distribution")
  1691. curve(dnorm(x,mean=mean(OD.DP_Comm_Trans$min_comp_price,na.rm=T),sd=sd(OD.DP_Comm_Trans$min_comp_price,na.rm=T)),add=TRUE)
  1692. hist(log(OD.DP_Comm_Trans$min_comp_price),prob=T,xlab="log(Min Competitor Price)",main="Min Competitor price distribution")
  1693. curve(dnorm(x,mean=mean(log(OD.DP_Comm_Trans$min_comp_price),na.rm=T),sd=sd(log(OD.DP_Comm_Trans$min_comp_price),na.rm=T)),add=TRUE)
  1694.  
  1695. # --------------------------------------------------------------------------------------------------------------
  1696. # --------------------------------------------------------------------------------------------------------------
  1697. # Normality Check : Shapiro-Wilk test
  1698. # --------------------------------------------------------------------------------------------------------------
  1699. # --------------------------------------------------------------------------------------------------------------
  1700.  
  1701. shapiro.test(OD.DP_Comm_Trans$sales_units)
  1702. shapiro.test(OD.DP_Comm_Trans$od_final_price)
  1703. shapiro.test(OD.DP_Comm_Trans$Amz_final_price)
  1704. shapiro.test(OD.DP_Comm_Trans$Amz_mkt_final_price)
  1705. shapiro.test(OD.DP_Comm_Trans$staples_final_price)
  1706.  
  1707. # --------------------------------------------------------------------------------------------------------------
  1708. # --------------------------------------------------------------------------------------------------------------
  1709. # T-test
  1710. # --------------------------------------------------------------------------------------------------------------
  1711. # --------------------------------------------------------------------------------------------------------------
  1712.  
  1713. t.test(OD.DP_Comm_Trans$od_final_price,OD.DP_Comm_Trans$Amz_final_price)
  1714. t.test(OD.DP_Comm_Trans$od_final_price,OD.DP_Comm_Trans$Amz_mkt_final_price)
  1715. t.test(OD.DP_Comm_Trans$od_final_price,OD.DP_Comm_Trans$staples_final_price)
  1716. t.test(OD.DP_Comm_Trans$od_final_price,OD.DP_Comm_Trans$min_comp_price)
  1717.  
  1718. # --------------------------------------------------------------------------------------------------------------
  1719. # --------------------------------------------------------------------------------------------------------------
  1720. # Sales Driver Analysis and Price Elasticity Analysis
  1721. # --------------------------------------------------------------------------------------------------------------
  1722. # --------------------------------------------------------------------------------------------------------------
  1723.  
  1724. pairs(OD.DP_Comm_Trans,col="blue",pch=20)
  1725. View(cor(OD.DP_Comm_Trans,use="na.or.complete"))
  1726.  
  1727. # Inference : Since Correlation is very high, variables might have high influence over the sales & hence include it in the model
  1728.  
  1729. #---------------------------------------------------------------------------------------------------------------
  1730. # Transform Variables
  1731. #---------------------------------------------------------------------------------------------------------------
  1732.  
  1733. OD.DP_Comm_Trans <- OD.DP_Comm
  1734. OD.DP_Comm_Trans$sales_units <- (1/OD.DP_Comm_Trans$sales_units)
  1735. OD.DP_Comm_Trans$Amz_final_price <- (1/OD.DP_Comm_Trans$Amz_final_price)
  1736. OD.DP_Comm_Trans$Amz_mkt_final_price <- (1/OD.DP_Comm_Trans$Amz_mkt_final_price)
  1737. OD.DP_Comm_Trans$staples_final_price <- (1/OD.DP_Comm_Trans$staples_final_price)
  1738. OD.DP_Comm_Trans$min_comp_price <- (1/OD.DP_Comm_Trans$min_comp_price)
  1739.  
  1740. toMatch_Com <- c("SKU","weekno","ln_")
  1741. names(OD.DP_Comm_Trans[,grep(paste(toMatch_Com,collapse="|"),names(OD.DP_Comm_Trans),value=T,invert=T)])
  1742. OD.DP_Comm_Trans <- OD.DP_Comm_Trans[,grep(paste(toMatch_Com,collapse="|"),names(OD.DP_Comm_Trans),value=T,invert=T)]
  1743.  
  1744. View(cor(OD.DP_Comm_Trans,use="na.or.complete"))
  1745.  
  1746. #---------------------------------------------------------------------------------------------------------------
  1747. # Linear Model
  1748. #---------------------------------------------------------------------------------------------------------------
  1749.  
  1750. # Linear Model
  1751. Model_1 <- lm(log(sales_units) ~ log(od_final_price)+log(Amz_final_price)+log(Amz_mkt_final_price)+log(staples_final_price),
  1752. data=OD.DP_Comm_Trans)
  1753. summary(Model_1)
  1754.  
  1755. # Inference : 1. Since p < 0.05, we can include price varaible into the model. In other words, Price has strong evidence in explaining
  1756. # the Sales.
  1757. # 2. 86% of the variations in sales can be explained by Price alone & the remaining 14% can be attributed to other factors
  1758. # or inherent variability.
  1759.  
  1760. # The assumptions for the regression to be true. are that data are random and independent;
  1761. # residuals are normally distributed and have constant variance. Let's check the residuals assumptions visually.
  1762. # plotting the residuals vs. other key model metrics
  1763. par(mfrow=c(1,2))
  1764. plot(Model_1)
  1765.  
  1766. # Inference : The Residuals vs Fitted graph shows that, the residuals scatter around the fitted line with no obvious pattern,
  1767. # and the Normal Q-Q graph shows that basically the residuals are normally distributed. The assumptions are met.
  1768.  
  1769. # --------------------------------------------------------------------------------------------------------------
  1770. # --------------------------------------------------------------------------------------------------------------
  1771. # --------------------------------------------------------------------------------------------------------------
  1772. # --------------------------------------------------------------------------------------------------------------
  1773. # --------------------------------------------------------------------------------------------------------------
  1774. # --------------------------------------------------------------------------------------------------------------
  1775. # --------------------------------------------------------------------------------------------------------------
  1776. # --------------------------------------------------------------------------------------------------------------
  1777.  
  1778. #################################################################################################################################################################################################################
  1779. #################################################################################################################################################################################################################
  1780.  
  1781. library(RODBC)
  1782. # Connect to ODBC ##
  1783. # Connect to the DataSource defined under d/b ##
  1784.  
  1785. CHANNEL <- odbcConnect("OD_DP","Platform","ugam@1234")
  1786.  
  1787. ########### View list of all tables ###############################################################################################################################
  1788.  
  1789. sqlTables(CHANNEL)
  1790.  
  1791. ############### Select particular table ###########################################################################################################################################
  1792.  
  1793. TB_Retailer_Product_History <- sqlTables(CHANNEL, errors = FALSE, as.is = TRUE,
  1794. catalog = "Platform_Officedepot_UAT", schema = "dbo",
  1795. tableName = "tb_Retailer_Product_History",
  1796. tableType = "TABLE", literal = FALSE)
  1797.  
  1798. TB_fin_data <- sqlTables(CHANNEL, errors = FALSE, as.is = TRUE,
  1799. catalog = "Platform_Officedepot_UAT", schema = "dbo",
  1800. tableName = "tb_fin_data",
  1801. tableType = "TABLE", literal = FALSE)
  1802.  
  1803. ############# Reading tables from ODBC d/b #########################################################################################################################################
  1804.  
  1805. system.time(TB_Retailer_Product_History_DF <- as.data.frame(sqlFetch(CHANNEL,"tb_Retailer_Product_History")))
  1806. system.time(TB_fin_data_DF <- as.data.frame(sqlFetch(CHANNEL,"TB_fin_data")))
  1807.  
  1808. class(TB_Retailer_Product_History_DF);dim(TB_Retailer_Product_History_DF)
  1809. names(TB_Retailer_Product_History_DF)
  1810.  
  1811. #---------------------------------------------------------------------------------------------------------------
  1812. #---------------------------------------------------------------------------------------------------------------
  1813. #---------------------------------------------------------------------------------------------------------------
  1814. #---------------------------------------------------------------------------------------------------------------
  1815. #---------------------------------------------------------------------------------------------------------------
  1816. #---------------------------------------------------------------------------------------------------------------
  1817.  
  1818. # --------------------------------------------------------------------------------------------------------------
  1819. # --------------------------------------------------------------------------------------------------------------
  1820. # --------------------------------------------------------------------------------------------------------------
  1821.  
  1822. # || Price Elasticity ||
  1823. # Price indices and implicit quantities of farm output and inputs for the United States, 1948-2011
  1824. # --------------------------------------------------------------------------------------------------------------
  1825. # --------------------------------------------------------------------------------------------------------------
  1826. # --------------------------------------------------------------------------------------------------------------
  1827.  
  1828. # --------------------------------------------------------------------------------------------------------------
  1829. # --------------------------------------------------------------------------------------------------------------
  1830. # Linear Regression
  1831. # --------------------------------------------------------------------------------------------------------------
  1832. # --------------------------------------------------------------------------------------------------------------
  1833.  
  1834. rm(list=ls())
  1835. Farm_data <- read.table("C:/Yashwanth/Data Analysis/Farm_data.csv",
  1836. header = TRUE, sep = ",", quote = "\"", dec = "." ,fill=TRUE, comment.char="", as.is=TRUE)
  1837. set.seed(100);
  1838. Farm_data$SKU <- ceiling(runif(nrow(Farm_data),54321,76843))
  1839. Farm_data <- Farm_data[,c("SKU","Price","Quantity","Sales")]
  1840.  
  1841. # --------------------------------------------------------------------------------------------------------------
  1842. # --------------------------------------------------------------------------------------------------------------
  1843. # Exploratory Data Analysis
  1844. # --------------------------------------------------------------------------------------------------------------
  1845. # --------------------------------------------------------------------------------------------------------------
  1846.  
  1847. # set the 1 by 2 layout plot window
  1848. par(mfrow = c(1,2))
  1849.  
  1850. # boxplot to check if there are outliers
  1851. boxplot(Farm_data$Quantity,horizontal = TRUE, xlab = "Sales",main="Boxplot")
  1852.  
  1853. # histogram to explore the data distribution shape
  1854. hist(Farm_data$Quantity,main= "Histogram", xlab = "", prob = T)
  1855. lines(density(Farm_data$Quantity),lty="dashed",lwd=2.5,col="red")
  1856. dev.off()
  1857.  
  1858. # set the 1 by 2 layout plot window
  1859. par(mfrow = c(1,2))
  1860.  
  1861. # boxplot to check if there are outliers
  1862. boxplot(Farm_data$Price,horizontal = TRUE, xlab = "Price",main="Boxplot")
  1863.  
  1864. # histogram to explore the data distribution shape
  1865. hist(Farm_data$Price,main= "Histogram", xlab = "", prob = T)
  1866. lines(density(Farm_data$Price),lty="dashed",lwd=2.5,col="red")
  1867.  
  1868. # histogram to explore the data distribution shape
  1869. hist(Farm_data$Price,prob=T,xlab="Price",main="Price distribution")
  1870. curve(dnorm(x,mean=mean(Farm_data$Price),sd=sd(Farm_data$Price)),add=TRUE)
  1871.  
  1872. # --------------------------------------------------------------------------------------------------------------
  1873. # --------------------------------------------------------------------------------------------------------------
  1874. # Relationship b/n Mean & SD : Chebyshev's Theorem
  1875. # --------------------------------------------------------------------------------------------------------------
  1876. # --------------------------------------------------------------------------------------------------------------
  1877.  
  1878. # library(psych)
  1879. # describe(Farm_data1$Quantity) # mean = 192502.2 & SD = 58104.71
  1880. # mean(Farm_data1$Quantity)+sd(Farm_data1$Quantity);mean(Farm_data1$Quantity)-sd(Farm_data1$Quantity)
  1881. # mean(Farm_data1$Quantity)+2*sd(Farm_data1$Quantity);mean(Farm_data1$Quantity)-2*sd(Farm_data1$Quantity)
  1882. #
  1883. # # What proportion of data that have sales units between 0 & 40
  1884. # K <- 1.5 # (30-mean(Farm_data$Sales_Units))/sd(Farm_data$Sales_Units)
  1885. #
  1886. # # Chebyshev's inequality : 1-(1/K^2)
  1887. # Cheb.In <- paste0((1-(1/K^2))*100,"%")
  1888.  
  1889. # Interpretation : ~56% of the data have sales units between 0 to 40. (OR)
  1890. # Approximately 56% of observations will lie within 1.5*standard deviation of the mean.
  1891. # Note : Since sd is very high, it is not relevant to identify the proportion of data.
  1892.  
  1893. # --------------------------------------------------------------------------------------------------------------
  1894. # --------------------------------------------------------------------------------------------------------------
  1895. # Normality Check : Shapiro-Wilk test
  1896. # --------------------------------------------------------------------------------------------------------------
  1897. # --------------------------------------------------------------------------------------------------------------
  1898.  
  1899. Farm_data1 <- data.frame(subset(Farm_data,Price <0.75,names(Farm_data)),row.names=NULL)
  1900. Farm_data2 <- data.frame(subset(Farm_data,Price >=0.75,names(Farm_data)),row.names=NULL)
  1901.  
  1902. shapiro.test(Farm_data1$Quantity)
  1903. shapiro.test(Farm_data2$Quantity)
  1904.  
  1905. # Inference : Since p<0.05, there is no strong evidence to reject NH that two groups of sales data are normally distibuted
  1906.  
  1907. # --------------------------------------------------------------------------------------------------------------
  1908. # --------------------------------------------------------------------------------------------------------------
  1909. # T-test
  1910. # --------------------------------------------------------------------------------------------------------------
  1911. # --------------------------------------------------------------------------------------------------------------
  1912.  
  1913. t.test(Farm_data1$Quantity,Farm_data2$Quantity)
  1914.  
  1915. # Inference : 1. Since p < 0.05, we reject NH & conclude that means of two groups of sales are significantly different.
  1916. # 2. With 95% CI, we can estimate means of sales with Price < 0.75 is somewhere in 85962 & 114631 units less than that of
  1917. # Price > = 0.75
  1918. # 3. We conclude that, Price >= 0.75 had better sales than Price < 0.75
  1919.  
  1920. # --------------------------------------------------------------------------------------------------------------
  1921. # --------------------------------------------------------------------------------------------------------------
  1922. # Sales Driver Analysis and Price Elasticity Analysis
  1923. # --------------------------------------------------------------------------------------------------------------
  1924. # --------------------------------------------------------------------------------------------------------------
  1925.  
  1926. pairs(Farm_data2[,2:3],col="blue",pch=20)
  1927. cor(Farm_data[,2:3])
  1928.  
  1929. # Inference : Since Correlation is very high(-vely), it might have high influence over the sales & hence include it in the model
  1930.  
  1931. #---------------------------------------------------------------------------------------------------------------
  1932. # Linear Model
  1933. #---------------------------------------------------------------------------------------------------------------
  1934.  
  1935. # Linear Model
  1936. Model_1 <- lm(log(Quantity) ~ log(Price), data=Farm_data)
  1937. summary(Model_1)
  1938.  
  1939. # Inference : 1. Since p < 0.05, we can include price varaible into the model. In other words, Price has strong evidence in explaining
  1940. # the Sales.
  1941. # 2. 86% of the variations in sales can be explained by Price alone & the remaining 14% can be attributed to other factors
  1942. # or inherent variability.
  1943.  
  1944. # The assumptions for the regression to be true. are that data are random and independent;
  1945. # residuals are normally distributed and have constant variance. Let's check the residuals assumptions visually.
  1946. # plotting the residuals vs. other key model metrics
  1947. plot(Model_1)
  1948.  
  1949. # Inference : The Residuals vs Fitted graph shows that, the residuals scatter around the fitted line with no obvious pattern,
  1950. # and the Normal Q-Q graph shows that basically the residuals are normally distributed. The assumptions are met.
  1951.  
  1952. #---------------------------------------------------------------------------------------------------------------
  1953. # BootStrapping/Data Sampling
  1954. #---------------------------------------------------------------------------------------------------------------
  1955.  
  1956. library(boot)
  1957. Farm_data_boot <- Farm_data[,2:3]
  1958. lm.bootstrap <- function(data, indices) {
  1959. d <- data[indices, ]
  1960. fit <- lm(log(Quantity) ~ log(Price), data = d)
  1961. return(coef(fit))
  1962. }
  1963.  
  1964. set.seed(12345) # seed for the RNG to ensure that you get exactly the same results as here
  1965. lm.boot <- boot(data=Farm_data_boot, statistic=lm.bootstrap, R=10000) # 10'000 samples
  1966. print(lm.boot)
  1967.  
  1968. # Final equation
  1969. # Sales ~ (70829.97-516.0930)+(176137.12+865.7666)*Price # Actual
  1970. # Sales ~ (-12.410785-7.795004e-06)+(-0.609974-3.820490e-04)*Price # Log
  1971.  
  1972. #---------------------------------------------------------------------------------------------------------------
  1973. # Simulation
  1974. #---------------------------------------------------------------------------------------------------------------
  1975.  
  1976. # Decrease price by 10%
  1977. Farm_data_New_Price_10 <- data.frame(cbind(Quantity=Farm_data$Quantity, Price=Farm_data$Price-((Farm_data$Price)/100)*10))
  1978. Farm_data_New_Price_10$New_sales_1 <- ceiling(1/exp(predict(Model_1,Farm_data_New_Price_10)))
  1979. colnames(Farm_data_New_Price_10)[2] <- "New_Price_1"
  1980. Farm_data <- cbind(Farm_data,Farm_data_New_Price_10[,2:3])
  1981. Farm_data$Dec_Price <- ifelse(Farm_data$New_sales > Farm_data$Sales, "Increase", " ")
  1982.  
  1983. # Increase price by 10%
  1984. attach(Farm_data_New_Price_10)
  1985. Farm_data_New_Price_10$New_Price_2 <- ifelse(Farm_data$Dec_Price=="Increase",New_Price_1,(Farm_data$Price+((Farm_data$Price)/100)*36))
  1986. detach(Farm_data_New_Price_10)
  1987. colnames(Farm_data_New_Price_10)[4] <- "Price"
  1988. Farm_data_New_Price_10$New_sales_2 <- ceiling(1/exp(predict(Model_1,Farm_data_New_Price_10)))
  1989. colnames(Farm_data_New_Price_10)[4] <- "New_Price_2"
  1990. Farm_data <- cbind(Farm_data,Farm_data_New_Price_10[,4:5])
  1991. Farm_data$Inc_Price <- ifelse(Farm_data$Dec_Price=="Increase","",ifelse(Farm_data$New_sales_2 > Farm_data$Sales, "Increase", " "))
  1992. rm(list=setdiff(ls(),c("Farm_data","Model_1","lm.boot")))
  1993.  
  1994. #---------------------------------------------------------------------------------------------------------------
  1995. # Initial Recommended Price
  1996. #---------------------------------------------------------------------------------------------------------------
  1997.  
  1998. attach(Farm_data)
  1999. Farm_data$Ini_Rec_Price <- round(ifelse(Dec_Price=="Increase",New_Price_1,New_Price_2),2)
  2000. Farm_data$Pred_Sales <- New_sales_2
  2001. detach(Farm_data)
  2002.  
  2003. Farm_data$Final_Action <- ifelse(Farm_data$Pred_Sales > Farm_data$Sales,"Increase"," ")
  2004.  
  2005. #---------------------------------------------------------------------------------------------------------------
  2006. # Optimization
  2007. #---------------------------------------------------------------------------------------------------------------
  2008.  
  2009. # Cost & Profit
  2010. Farm_data$Cost <- round(((Farm_data$Price)/100)*80,2)
  2011. Farm_data$Profit <- NA
  2012. attach(Farm_data)
  2013. for (i in 1:nrow(Farm_data)){
  2014. Farm_data$Profit[i] <- round((Ini_Rec_Price[i]-Cost[i])*(1/(exp(as.numeric(coef(Model_1))[1]-as.numeric(coef(Model_1))[2]*log(Ini_Rec_Price[i])))),3)
  2015. }
  2016. detach(Farm_data)
  2017.  
  2018. toMatch <- c("New","Dec","Inc")
  2019. Farm_data_New <- Farm_data[,grep(paste(toMatch,collapse="|"),names(Farm_data),value=T,invert=TRUE)]
  2020.  
  2021. # Floor & CAP
  2022. Farm_data_New$Floor <- 0
  2023. Farm_data_New$CAP <- NA
  2024. attach(Farm_data_New)
  2025. for(i in 1:nrow(Farm_data_New)){
  2026. Farm_data_New$CAP[i] <- round(min(Farm_data_New$Price[i],Farm_data_New$Ini_Rec_Price[i]),2)
  2027. }
  2028. detach(Farm_data_New)
  2029.  
  2030. # Profit Optimization function
  2031. Profit_Opti_fun <- function(Price,Cost)
  2032. (round((Price-Cost)*(1/(exp(as.numeric(coef(Model_1))[1]-as.numeric(coef(Model_1))[2]*log(Price)))),3))
  2033.  
  2034. # Optimal Price & Max Profit
  2035. Opti <- list();
  2036. attach(Farm_data_New)
  2037. for(i in 1:nrow(Farm_data)){
  2038. Opti[[i]] <- optimize(Profit_Opti_fun,lower=Floor[i],upper=Ini_Rec_Price[i],Cost=Cost[i],maximum=TRUE)
  2039. }
  2040. detach(Farm_data_New)
  2041.  
  2042. # Unlist Optimal Price & Max profit
  2043. Farm_data_New$Opti_Price <- NA;Farm_data_New$Max_Profit <- NA
  2044. for(i in 1:nrow(Farm_data_New)){
  2045. Farm_data_New$Opti_Price[i] <- round(unlist(lapply(Opti[[i]]$maximum,unlist)),2)
  2046. Farm_data_New$Max_Profit[i] <- unlist(lapply(Opti[[i]]$objective,unlist))
  2047. }
  2048.  
  2049. Farm_data_New <- Farm_data_New[,c("SKU","Price","Quantity","Cost","Final_Action","Floor","CAP","Ini_Rec_Price","Profit","Sales",
  2050. "Pred_Sales","Opti_Price","Max_Profit")]
  2051.  
  2052. #---------------------------------------------------------------------------------------------------------------
  2053. # Export output to Excel
  2054. #---------------------------------------------------------------------------------------------------------------
  2055.  
  2056. library(xlsx)
  2057. write.xlsx(Farm_data_New,"C:/Yashwanth/Data Analysis/Farm_Output.xlsx",row.names=FALSE)
  2058.  
  2059.  
  2060. # --------------------------------------------------------------------------------------------------------------
  2061. # --------------------------------------------------------------------------------------------------------------
  2062. # --------------------------------------------------------------------------------------------------------------
  2063. # --------------------------------------------------------------------------------------------------------------
  2064. # --------------------------------------------------------------------------------------------------------------
  2065. # --------------------------------------------------------------------------------------------------------------
  2066. # --------------------------------------------------------------------------------------------------------------
  2067. # --------------------------------------------------------------------------------------------------------------
  2068.  
  2069. # Inference : 1. Since p < 0.05, we can include price varaible into the model. In other words, Price has strong evidence in explaining
  2070. # the Sales.
  2071. # 2. 84% of the variations in sales can be explained by Price alone & the remaining 16% can be attributed to other factors
  2072. # or inherent variability.
  2073.  
  2074. # The assumptions for the regression to be true. are that data are random and independent;
  2075. # residuals are normally distributed and have constant variance. Let's check the residuals assumptions visually.
  2076. # plotting the residuals vs. other key model metrics
  2077.  
  2078.  
  2079. # Inference : The Residuals vs Fitted graph shows that, the residuals scatter around the fitted line with no obvious pattern,
  2080. # and the Normal Q-Q graph shows that basically the residuals are normally distributed. The assumptions are met.
  2081.  
  2082.  
  2083. # Profit_Opti_fun <- function(Price,Cost)
  2084. # (round((Price-Cost)*(1/(exp(as.numeric(coef(Model_1))[1]-as.numeric(coef(Model_1))[2]*log(Price)))),3))
  2085. #
  2086. #
  2087. #
  2088. # # Optimal Price & Max Profit
  2089. # Opti <- list();
  2090. # attach(Farm_data_New)
  2091. # for(i in 1:nrow(Farm_data)){
  2092. # Opti[[i]] <- optimize(Profit_Opti_fun,lower=Floor[i],upper=CAP[i],Cost=Cost[i],maximum=TRUE)
  2093. # }
  2094. # detach(Farm_data_New)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!