# make_DT.transform_wide.r ## This was done using DT.acc_v_anal ## However, I can also use DT.iTunes.acc_v_anal ## all the important parts that are in this first if(FALSE) {..} clause ## are captured in "~rsaporta/git/orch/src/iTunes NEW.r" ### ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ### ### ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ### ## EXAMPLE OF SETUP if (FALSE) { ## NOTE: DT.GL.summaries is what feeds to mbworkbook DB in redshift loadFromJesus("DT.GL.summaries", src=srcOther("MgmtReport"), over=TRUE) cat(sprintf("Max for $-20s is %s\n", "DT.GL.summaries", max(DT.GL.summaries[, accounting_month]))) loadFromJesus("DT.GL", src=srcOther("MgmtReport"), over=TRUE) cat(sprintf("Max for $-20s is %s\n", "DT.GL", max(DT.GL[, accounting_month]))) tmp_DT.StoresUsingRD <- runQry(.m(tbl="bi.analytics", colsToPull=c("storeid", "store_name", "store_uses_royaltydollar"), distinct=TRUE, limit=NULL)) StoresForecasting <- tmp_DT.StoresUsingRD[store_uses_royaltydollar == TRUE][order(storeid), setNames(obj=storeid, nm=store_name)] # StoresForecasting <- structure(c(1L, 153L, 187L, 487L), .Names = c("iTunes", "MusicLoad","Amazon", "Xbox Video")) storedate <- c("storeid", "accounting_month") ## Max date by store tmp_DT.dropping <- DT.GL[accounting_month >= "2014-05-01"] [storeid %in% as.character(StoresForecasting) | store_name %in% names(StoresForecasting)] [, sum(is.na(Statement_ThisMonth))/.N, keyby=storedate] [V1 > .25][, storeid := as.numeric(storeid)] DT.OA_and_GL <- { BackUpOrRestore("DT.OA_and_GL", clear=TRUE) DT2 <- DT.GL.summaries[storeid %in% as.character(StoresForecasting) | store_name %in% names(StoresForecasting)] [, Statement_ThisMonth, keyby=list(storeid=as.numeric(storeid), date=accounting_month)] DT1 <- DT.acc_v_anal[storeid %in% StoresForecasting, lapply(.SD, sumn), .SDcols=revAndUnitsCols, keyby=list(storeid, date)] merge(DT1, DT2, all=TRUE, allow=TRUE)[ # !tmp_DT.dropping][ # -- do not drop random months storeid != 153 & date >= "2012-10-01"][ date < max(date)] # [storeid != 153] # [ # , rev_GL_as_perc_rev_anal := nonFiniteToNA(Statement_ThisMonth / revenue.acc )] [ # , rev_GL_as_perc_units_anal := nonFiniteToNA(Statement_ThisMonth / paidunits.acc)] [] } setInfo(DT.OA_and_GL, "Merge of DT.GL.summaries & DT.acc_v_anal") ## xbox is missing data for "2013-08-01" DT.OA_and_GL[storeid == 487 & date == "2013-08-01" & (revenue.anal == 0 & paidunits.anal == 0), c("paidunits.anal", "revenue.anal") := NA_real_] ## Make sure that each group has the full range of dates DT.OA_and_GL <- DT.OA_and_GL[CJ_allDatesByCols(DT.OA_and_GL, dateCol="date", universal.range=TRUE)] } ### ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ### ### ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ### make_DT.transform_wide <- function(DT, nm_DT, nm_DT.transform_wide, nm_DT.pre_transform, envir=globalenv()) { if (is.character(DT)) { nm_DT <- DT cat("Using nm_DT = '", nm_DT, "'\n") } if ((!missing(nm_DT) && missing(DT)) || is.character(DT)) DT <- get(nm_DT, envir=envir) if (missing(nm_DT)) { nm_DT <- capture.output(substitute(DT)) stopifnot(identical(DT, get(nm_DT, envir=envir)), !grepl("\\[", nm_DT)) } if (missing(nm_DT.pre_transform)) nm_DT.pre_transform <- paste0(nm_DT, ".pre_transform") if (missing(nm_DT.transform_wide)) nm_DT.transform_wide <- paste0(nm_DT, ".transform_wide") ## REVENUE ANALYTICS IS WHAT WE HAVE AHEAD OF TIME. ## L E T : ## - - - - ## Ranal_i := revenue in analytics for month i ## Racct_i := revenue in accounting for month i ## Uanal_i := (paid)units in analytics for month i ## Uacct_i := (paid)units in accounting for month i ## Rasrp_i := revenue as reported for month i ## ## ## 11 13 ## We are predicting November using January Analytics ## We are predicting December using February Analytics ## 12 14 ## 0 2 ## i i+2 ## in January we are predicting ## ## ## If today is month i == 0, our goal is to predict ## Rasrp_1 and Rasrp_2 ## GIVEN ## Ranal_2, Ranal_1, Ranal_0, Ranal_-1, Ranal_-2, ... ## Racct_0, Racct_-1, Racct_-2, ... ## Uanal_2, Uanal_1, Uanal_0, Uanal_-1, Uanal_-2, ... ## Uacct_0, Uacct_-1, Uacct_-2, ... ## ## ## We can speak of Rasrp_0 = f(Racct_0) or Rasrp_0 = f(Racct_2) ## ## The latter does not make much sense since, sesonality, new releases, etc ## ## Y1 = Rasrp_1 / Racct_0 ## Y0 = Rasrp_0 / Racct_0 ## x.T.S_i := T.S_i / T.S_(i-1) (ie, percent increase from last month) ## * WHERE * ## T := "Type" (Rev or Units) ## S := "Source" (Acct or Anal) ## i := "Month" (2, 1, 0, ... -Inf) 0 == "Today" ## Prepare the pre-transform ## ## ------------------------------ ## DT.pre_transform <- copy(DT) ## ---------- "PARAMETERS" ------------- ## stopifnot(haskey(DT.pre_transform)) kCols <- key(DT.pre_transform) kCols_nodate <- setdiff(kCols, "date") ## Dict dict.ColNamesTransform <- { c( Statement_ThisMonth = "Rasrp_0" ## previously, I was leaving revenue.asr as 'Statement_ThisMonth' , revenue.asr = "Rasrp_0" , revenue.acc = "Racct" , revenue.anal = "Ranal" , paidunits.acc = "Uacct" , paidunits.anal = "Uanal" )} yCols <- sprintf("Rasrp_%01i", 0:1) ## ---------- "PARAMETERS" ------------- ## ## Clean up the names of the columns setNamesDict(DT.pre_transform, dict.ColNamesTransform, silent=TRUE) stopifnot(dict.ColNamesTransform %in% names(DT.pre_transform)) ## Addd y1 column DT.pre_transform[, Rasrp_1 := shiftUp(Rasrp_0) , by=kCols_nodate] ## Check how many months available for each store, and take that one. Crop at 15 months max max_months_back <- min(15, max(DT.pre_transform[, .N, keyby=kCols_nodate]$N) - 1) ## Calculate the percentage ## ## ------------------------------ ## DT.transform <- copy(DT.pre_transform) ## Add Column for month name DT.transform[, month_of_year := factor(format(date, "%B"), levels=month.name)] ## Ensure sorted by date first setkeyIfNot(DT.transform, kCols, organize=TRUE, verbose=FALSE) ## Which cols that will be transformed and reshaped colsToTransform <- unname(grep("(acct|anal)$", names(DT.pre_transform), value=TRUE)) ## First transform the Ys DT.transform[, Rasrp_0 := Rasrp_0 / Racct] DT.transform[, Rasrp_1 := Rasrp_1 / Racct] [] ## Next convert the X-columns to percent-of-increase. # DT.transform[, c(colsToTransform) := lapply(.SD, as.numeric), .SDcols=colsToTransform] DT.transform[, c(colsToTransform) := lapply(.SD, percentIncrease), keyby=kCols_nodate, .SD=colsToTransform] DT.transform_wide <- DT.transform[, { # .SD; browser() table_per_col <- lapply(colsToTransform, function(col) { setnames(rbindlist( lapply(rev(seq(.N)), function(i) as.data.table(t( head(tail(rev(get(col)), .N+1-i), n=max_months_back) # head(shiftRight(get(col), i-1), n=max_months_back) )) ) , fill=TRUE), sprintf("%s_%02i", col, seq(max_months_back)-1)) } ) other_cols <- .SD[, c("date", "month_of_year", yCols), with=FALSE] cbind(other_cols, do.call(cbind, table_per_col)) }, by=kCols_nodate] ## NOTES: " Y2, Y1, Ranal_2, Ranal_1, Ranal_0, Ranal_-1, Ranal_-2, ... Racct_0, Racct_-1, Racct_-2, ... Uanal_2, Uanal_1, Uanal_0, Uanal_-1, Uanal_-2, ... Uacct_0, Uacct_-1, Uacct_-2, ... " ## Other notes.... # # GL == 11 # acc == 11 # anal == 13 # # # SCALE ALL BY DIVIDING BY y1 -- nope!! All targers are 1. Incorrect # # Goal: Predict Statement_ThisMonth # Today is end of january (ie, Feb 1 or month 14) # y1 := Statement_ThisMonth, month 12 # y2 := Statement_ThisMonth, month 13 # x0 := paidunits.acc, month 11 # x0 := paidunits.acc, month 11 # # Scale each individualy? cat("Assigning to ", paste(" * ", c(nm_DT.pre_transform, nm_DT.transform_wide), "\n"), "\n") assign(nm_DT.pre_transform, DT.pre_transform, envir=envir) assign(nm_DT.transform_wide, DT.transform_wide, envir=envir) return(invisible(DT.transform_wide)) }