caseywhorton / mba Goto Github PK

mba2<-dataset

usePackage <- function(p)
{
if (!is.element(p, installed.packages()[,1]))
install.packages(p, dep = TRUE)
require(p, character.only = TRUE)
}
usePackage("gridExtra")
#install.packages("gridExtra",repos = "http://cran.us.r-project.org")
#library(gridExtra)
attach(mba2)

prod1<-data.frame(unique.matrix(cbind(Invoice,as.character(Product1))))
names(prod1)<-c('Invoice','Product1')
prod1_counter<-function(x){length(which(prod1$Product1==x))}
p1_count<-lapply(prod1$Product1,FUN=prod1_counter)
prod1<-data.frame(cbind(prod1,unlist(p1_count)))
names(prod1)[dim(prod1)[2]]<-'Product1_count'

prod2<-data.frame(unique(cbind(Invoice,as.character(Product2))))
names(prod2)<-c("Invoice","Product2")
prod2_counter<-function(x){length(which(prod2$Product2==x))}
p2_count<-lapply(prod2$Product2,FUN=prod2_counter)
prod2<-data.frame(cbind(prod2,unlist(p2_count)))
names(prod2)[dim(prod2)[2]]<-'Product2_count'

rule_counter<-function(x){length(which(mba2$Rule==x))}
rule_count<-lapply(mba2$Rule,FUN=rule_counter)
mba2<-data.frame(cbind(mba2,unlist(rule_count)))
names(mba2)[dim(mba2)[2]]<-'Rule_count'

market_basket<-data.frame(merge(mba2,prod1,by=c("Invoice","Product1")))
#plot(length(prod1$Product1=="A"))

market_basket2<-data.frame(merge(market_basket,prod2,by=c("Invoice","Product2")))
#grid.table(market_basket2)
numb<-length(unique(mba2$Invoice))
market_basket3<-data.frame(cbind(market_basket2,market_basket2$Product1_count/numb)
,market_basket2$Product2_count/numb
,market_basket2$Rule_count/numb)

names(market_basket3)[dim(market_basket2)[2]+1]<-'Support_Prod1'
names(market_basket3)[dim(market_basket2)[2]+2]<-'Support_Prod2'
names(market_basket3)[dim(market_basket2)[2]+3]<-'Support_Rule'
#grid.table(market_basket3)
market_basket4<-data.frame(cbind(market_basket3,market_basket3$Support_Rule/market_basket3$Support_Prod1,
market_basket3$Support_Rule/(market_basket3$Support_Prod1*market_basket3$Support_Prod2)))
names(market_basket4)[dim(market_basket3)[2]+1]<-'Confidence'
names(market_basket4)[dim(market_basket3)[2]+2]<-'Lift'
#plot(numb)
#grid.table(market_basket4)
grid.table((market_basket4[order(-market_basket4$Lift),]))

IF OBJECT_ID(N'dbo.mba2', N'U') IS NOT NULL DROP TABLE dbo.mba2;
GO

CREATE TABLE dbo.mba2
(
Product1 char(1) NOT NULL,
Product2 char(1) NOT NULL,
[Date] Date NOT NULL,
Invoice INT,
Product char(1) NOT NULL,
Units INT NOT NULL,
Price INT NOT NULL
);

select * from dbo.mba2;

IF OBJECT_ID(N'dbo.allcombos',N'P') IS NOT NULL DROP PROC dbo.allcombos;
GO

CREATE PROC dbo.allcombos
(
@invoiceid AS INT
)
AS
INSERT INTO dbo.mba2(Product1,Product2,[Date],Invoice,Product,Units,Price)
SELECT *
FROM
(SELECT *
from
(SELECT dbo.mba.PRODUCT as Product1 from dbo.mba where dbo.mba.Invoice=@invoiceid) as c1
cross join
(SELECT dbo.mba.PRODUCT as Product2 from dbo.mba where dbo.mba.Invoice=@invoiceid) as c2
where Product1!=Product2
)
AS C3
RIGHT OUTER JOIN
(SELECT * from dbo.mba where dbo.mba.Invoice=@invoiceid) as C4
on
C3.Product1 = C4.Product;

DECLARE int_cursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT(dbo.mba.Invoice)
from dbo.mba;
OPEN int_cursor;

DECLARE @intid as INT;
FETCH NEXT FROM int_cursor INTO @intid;

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.allcombos @invoiceid = @intid;
FETCH NEXT FROM int_cursor INTO @intid;
END;

SELECT * FROM dbo.mba2;

CLOSE int_cursor;
DEALLOCATE int_cursor;