CVS example

pos01 <- read.csv("pos_items.csv")

pos_items.csv

# modify the original dataset with some trick additives

pos01$trseq <- c(1:149639) + (rnorm(1)*1000) - (rnorm(1)*300)

pos01$trseq <- as.numeric( substr(as.character(pos01$trseq),1,3))+1000000

pos01$trseq <- substr(as.character(pos01$trseq),5,6)

q01 <- sort(sample(unique(pos01$itemid), 50 ))

q02 <- c("1","2", "3","4","5", "6")

q03 <- c( "ramenpasta","snack", "drink", "cigarette", "stuff", "stationary")

itemclassmast <- data.frame(q02,q03)

names(itemclassmast) <- c("classid", "classname")

itemmast <- sqldf('select distinct itemid 

   from pos01')

itemmast$classid <- substr(itemmast$itemid, 1,1)

itemclassmast <- sqldf('select a.*, b.classname

  from itemmast as a left join itemclassmast as b

  on a.classid=b.classid')

q04 <- sqldf('select a.itemid, b.classid,

 b.classname, 

 count(distinct trid) as cnttr,

 count(distinct trseq) as cnttrdate

 from pos01 a left join itemclassmast as b

 on a.itemid = b.itemid

 group by b.classid ')

### pos01 (trx) and itemclassmast (sku )

boxplot(q04$cnttr ~ q04$cnttrdate,data=q04, main="CVS transaction over days")

pos_items.csv

3.15MB