srcOther <- function(new.src, old.src=getProjName(), path=srcDir) { pat <- paste0("/", old.src, "(/|$)") repl <- paste0("/", new.src, "\\1") gsub(pat, repl, path) } loadFromJesus(objToLoad="DT.stores", src=srcOther("MGMTReport")) ----- Will create TWO summary tables. mgmtr_summary_tall := summary of monthly numbers, tall. This one will be "immutable" in that it will track all historical values for budget and gest for any given month. mgmtr_summary := summary of monthly numbers, wide. This one will select the lastest estimates/budgets for any given month (ie, where modifiedDate = (select max(modifieddate) group by groupings) ) This will also have gross and gross_yago and budget all side by side. mgmtr_summary_tall c("accounting_month, store, music_vs_video, musicbucket, videobucket, ancillaryline, gross, budget, gross_is_estimate, gross_last_modified, budget_last_modified") mgmtr_summary c("accounting_month, store, music_vs_video, musicbucket, videobucket, ancillaryline, gross, gross_yago, budget, budget_yago, gross_is_estimate, gross_yago_is_estimate, gross_last_modified, budget_last_modified, budget_yago_last_modified") [,1] [1,] "accounting_month" [2,] "store" [3,] "music_vs_video" [4,] "musicbucket" [5,] "videobucket" [6,] "gross" [7,] "budget" [8,] "gross_is_estimate" [9,] "gross_last_modified" [10,] "budget_last_modified" kCols.acc <- c("") DT.acc date storeid release_is_compilation label_sc_group stream_vs_download store_name store_musicbucket store_videobucket music_vs_video_by_product stream_vs_download music_vs_video_by_transac gross units source DT.acc[, table(stream_vs_download, store_musicbucket)] # GIVEN THESE FOUR DT.acc DT.anal DT.GL.summaries # DT.splits DT.stores ## Begin creating the TALL DT.mgmtr_summary_tall <- { DT.GL.summaries[, list( date = accounting_month , storeid = suppressWarnings(as.integer(storeid)) , storeid_char = storeid , store_name = store_name , music_vs_video = Music_vs_Video , musicbucket = NA_character_ , videobucket = NA_character_ # , is_compilation = FALSE # , label_sc_group = NA_character_ , ancillaryline = NA_character_ , gross = Total_ThisMonth , budget = NA_real_ , gross_is_estimate = FALSE , gross_last_modified = last_modified , budget_last_modified = as.POSIXct(NA_real_, origin=.origin.utc) , store_is_in_analytics = NA , store_is_in_accounting = NA ) ] } ## THESE ARE MISSING. ADDRESS # DT.anal[store_name %ni% DT.mgmtr_summary_tall$store_name] ###### ------------------------------------ ######## kColsAll <- c("date", "storeid", "store_name", "music_vs_video") insert.these.columns <- c("label_sc_group", "release_is_compilation") values.foroverflows <- list(label_sc_group="Orchard", release_is_compilation=FALSE) NA.defaultpercs <- list(label_sc_group=0, release_is_compilation=1) ## Take backup copy of gross DT.mgmtr_summary_tall[, gross_total_by_DS := gross] for (col.tosplit in insert.these.columns) DT.mgmtr_summary_tall <- splitOutTheGross_(DT.mgmtr_summary_tall, col.tosplit , kColsAll , value.getsoverflow=values.foroverflows[[col.tosplit]] , NA.defaultperc=NA.defaultpercs[[col.tosplit]]) THIS DOES NOT WORK splitOutTheGross_ <- function(DT.mgmtr_summary_tall, col.tosplit, kColsAll, value.getsoverflow=NULL, NA.defaultperc=NA) { stop("\n\nThis function does not work") ## Take a deep copy right from the start. (Note that we will loose the connection at the lapply() below.) DT.mgmtr_summary_tall <- copy(DT.mgmtr_summary_tall) if (!exists("DT.acc")) stop ("DT.acc is not yet loaded. Please talk to jesus") if (!exists("DT.anal")) stop ("DT.anal is not yet loaded. Please talk to jesus") ## the current keygroup is date-store-col.tosplit kCols.nogroup <- c("date", "storeid", "store_name") kCols.group <- c(kCols.nogroup, col.tosplit) ## 'for gross summing' is by all other groups that are in kCols and also in the names of DT. ## Note that at first, not all cols are gonna be in the DT since we are adding them via this function. kCols.for_gross_summing <- intersect(setdiff(kColsAll, col.tosplit), names(DT.mgmtr_summary_tall)) DT.splits <- DT.acc[, list(OA_gross_forDSOM = sum(gross)), keyby=kCols.group] DT.splits.anal <- DT.anal[date %ni% unique(DT.splits$date), list(OA_gross_forDSOM = sum(gest)), keyby=kCols.group] DT.splits <- rbind(DT.splits, DT.splits.anal) ## Make sure all col.tosplit are represented per date-store and fill with the default NA value setkeyIfNot(DT.splits, kCols.group) DT.splits <- DT.splits[setkey(DT.splits[, unique(DT.splits[[col.tosplit]]), keyby=kCols.nogroup])] DT.splits[is.na(OA_gross_forDSOM), OA_gross_forDSOM := NA.defaultperc] ## Calculate the SC split per each DT.splits[, ._split := OA_gross_forDSOM / sum(OA_gross_forDSOM), by=kCols.nogroup] ## Split up the gross by col.tosplit. DT.mgmtr_summary_tall <- rbindlist(lapply(unique(DT.acc[[col.tosplit]]), function(x) cbind(DT.mgmtr_summary_tall, ._new_col = x))) setnames(DT.mgmtr_summary_tall, "._new_col", col.tosplit) ## Take the average permonth of each SC group. Then Confirm all sum to a whole DT.avgsplits <- DT.splits[, sum(OA_gross_forDSOM), keyby=c("date", col.tosplit)][, list(get(col.tosplit), avg_split_by_month = V1 / sum(V1)), by=date] # <~~~ CHECK HERE setnames(DT.avgsplits, "V1", col.tosplit) setkeyIfNot(DT.avgsplits, c("date", col.tosplit), verbose=FALSE) stopifnot(sumsToOne(DT.avgsplits, "avg_split_by_month", by="date")) ## match the keys to add the splits ## Load in the splits matchKey(DT.mgmtr_summary_tall, DT.splits, key(DT.splits)) DT.mgmtr_summary_tall[DT.splits, ._split := i.._split] ## There should not be any ._split 's that are NA other than those wher ethe whole store-month are NA (meaning they were not in OA, and thus we will take the monthly average) ## Note that if there are some offenders here (meaning only some NA per store-month) then when we fill in with averages ## those NAs will be given a value, which will in turn throw off the total Gross stopifnot(DT.mgmtr_summary_tall[, is.na(._split) & !all(is.na(._split)), by=kCols.nogroup][, !any(V1)]) ## Fill with monthly averages matchKey(DT.mgmtr_summary_tall, DT.avgsplits, c("date", col.tosplit)) DT.mgmtr_summary_tall[DT.avgsplits, ._split := ifelse(is.na(._split), round(i.avg_split_by_month, 4), ._split)] ## There should not be any NA's in ._split stopifnot(DT.mgmtr_summary_tall[, !is.na(._split)]) ## Next, multiply the gross by the ._split ## Take back any rounding error and assign it to "Orchard" DT.mgmtr_summary_tall[, gross_splat := gross * ._split] DT.mgmtr_summary_tall[, gross_splat := ifelse(get(col.tosplit)==value.getsoverflow, gross - sum(gross_splat[get(col.tosplit) != value.getsoverflow], na.rm=TRUE), gross_splat), by=kCols.for_gross_summing] ## Confirm all sum browser(expr=!all(DT.mgmtr_summary_tall[, sum(gross_splat) - sum(unique(gross), na.rm=TRUE), by=kCols.for_gross_summing][, abs(V1) < 1e-4]), text="Not All Sum back up to total") stopifnot(DT.mgmtr_summary_tall[, sum(gross_splat) - sum(unique(gross), na.rm=TRUE), by=kCols.for_gross_summing][, abs(V1) < 1e-4]) ## CHECKING: DT.mgmtr_summary_tall[storeid==573 & date == "2014-05-01"]#, sum(gross_splat) - sum(unique(gross), na.rm=TRUE), by=kCols.for_gross_summing] DT.mgmtr_summary_tall[storeid==573 & date == "2014-05-01", sum(gross_splat) - sum(unique(gross), na.rm=TRUE), by=kCols.for_gross_summing] setnames(DT.mgmtr_summary_tall, "._split", paste0(col.tosplit, "_perc_by_DS")) return(DT.mgmtr_summary_tall) }