## TODO: In code below, instead of using, say, music_vs_video to identify rows where the meta cols need to be fixed, ## use a more generic get(cols.to.spliton[[1]]) or m.col <- cols.to.spliton[[1]]; get(m.col) ## THESE NOTES ARE FROM "OLD" VERSION -- some still applicable, but not all ### ~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~ ### ### IMPORTANT NOTE ### ### ~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~ ### ### Recall that the GL gives us the TOTAL GROSS (and some time stamps) ### ### while OA gives us the meta and split percentages ### ### Ideally, we would like to just paste the TOTAL GROSS & time stamps from GL into OA ### ### but cannot since the missing meta in GL means that a simple join will fail ### ### ie, DT.mgmtr_summary_tall[DT.split_percs] does NOT work ### ### (also, there is the issue of stores in GL that are not in OA, but ### ### those should not be unmatched) ### ### Instead, we merge the two and create DT.merged ### ### This has the side effect of creating an extra row per each date-store: namely, ### ### a row from GL where the meta columns are all NA. ### ### More specifically, since the meta columns dont line up (meaning, GL does not have meta), ### ### when we merge the two DT's with 'all=TRUE', what we get is a large DT with ### ### * NA in the meta columns & data in the TOTAL GROSS columns ### ### * NA in the TOTAL GROSS columns and data in the meta columns ### ### The cleanup for this is to: ### ### step 1) Merge the two DTs ### ### step 2) Permeate data through the NAs, by key ### ### step 3) Delete the duplicate rows ### ### Step 2 and 3 are made easier by the fact that GL DT only contributes ONE row per key, ### ### so the easiest approach is to permeate only its columns, then delete the rows it had ### ### contributed -- with the big caveat of not to delete any rows that ### ### are unique to GL and NOT in OA. ### ### ~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~ ### ## NOTE: ## DT.cleaned is DT.mexpanded with the 'delete.this.row' rows dropped and some columns dropped ## NOTE TO SELF: DT.split_percs does NOT have data for RED and SelectO for "2013-01-01" ## They do not begin until "2013-02-01" ## This introduces NAs further down the line. # # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # # (1) TODO: I believe `use.avg` is redundant to Split_is_known # namely, use.avg == !Split_is_known, except that use.avg needs to permeate. # Not sure why use.avg is needed # # (2) Thought:: What constitutes the average split percentage? # opt 1: Take the average percent of each group, and try to normalize # opt 2: Calculate the subtotals per group and divide by the ultimate total # # # x,y, etc are dates # 1, 2,.. N are group IDs # # xperc1 == x1 / (x1+x2+..+xN); yperc1 == y1 / (y1+y2+..+yN) # xperc2 == x2 / (x1+x2+..+xN); yperc2 == y2 / (y1+y2+..+yN) # xpercN == xN / (x1+x2+..+xN); ypercN == yN / (y1+y2+..+yN) # # opt 1 : # mean1 == (x1 / (x1+x2+..+xN) + y1 / (y1+y2+..+yN)) / length(date) * normalizing_constant # mean2 == (x2 / (x1+x2+..+xN) + y2 / (y1+y2+..+yN)) / length(date) * normalizing_constant # meanN == (xN / (x1+x2+..+xN) + yN / (y1+y2+..+yN)) / length(date) * normalizing_constant # # # opt 2 : # mean1 == (x1 + y1) / {(x1+x2+..+xN) (y1+y2+..+yN)} # mean2 == (x2 + y2) / {(x1+x2+..+xN) (y1+y2+..+yN)} # meanN == (xN + yN) / {(x1+x2+..+xN) (y1+y2+..+yN)} # # # # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ if (FALSE) { .g(); fresh() .us() setScience("MgmtReport", subProj="Before_2c", subl=FALSE) lsosdt() } ## setting the info for .D() if (FALSE) { .setDTcols(c(kCols.splitgroup, "music_vs_video_by_dmv", "total_netsc_by_DSO", "GL_gross_forDS", "GL_gross_forDSO", "OA_gross_forDSOM", "perc_split", "delete.this.row", "row_in_OA", "row_in_GL", "id_is_in_GL", "id_is_in_OA")) .setDT("DT.merged") .D(storeid==580) .d <- function(...) .D(...)[storeid == 580] } # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ## Take backup for dev'ing { if (!exists("DT.mgmtr_summary_tall.bak")) DT.mgmtr_summary_tall.bak <- copy(DT.mgmtr_summary_tall) else DT.mgmtr_summary_tall <- copy(DT.mgmtr_summary_tall.bak) } if (!exists("minDateToKeep")) minDateToKeep <- "2013-01-01" ## For dates not yet closed, we can expect some NAs .. Identify what the last date closed was lastDateClosed <- max(DT.mgmtr_summary_tall$date) firstSCdate <- min(DT.mgmtr_summary_tall[ label_sc_group != "Orchard" & ASR_percof_DS_forDSO != 0]$date) # dates_not_in_GL is defined AFTER the split percs are calculated Whats the problem? Historically, Finance has applied SC splits across the entire monthly gross. ie, if RED split is 21%, that is applied to all stores equally. However, some stores sold NO RED at all. SOLUTION: Instead of applying the mexpanding to DT.merged, apply it to DT.split_percs ### CREATE DT.split_percs { ## DT.split_percs is the rbound of DT.acc and DT.anal, with appropriate dates. We use a tmp DT and then delete it. { ## start with Accounting tmp_DT.split_percs.acc <- DT.acc[, list(OA_gross_forDSOM = sum(gross)), keyby=kCols.splitgroup] ## take what's missing from analytics tmp_DT.split_percs.anal <- DT.anal[date %ni% unique(tmp_DT.split_percs.acc$date), list(OA_gross_forDSOM = sum(gest, na.rm=TRUE)), keyby=kCols.splitgroup] ## rbind the two DT.split_percs <- rbind(tmp_DT.split_percs.acc, tmp_DT.split_percs.anal) ## cleanup rm(tmp_DT.split_percs.acc, tmp_DT.split_percs.anal) } ## IDENTIFY WHICH DATES ARE IN OA but not in GL dates_not_in_GL <- unique(DT.split_percs[date > lastDateClosed, date]) ## Calculate the SC split per each DT.split_percs[, perc_split_ofOA_byDS := OA_gross_forDSOM / sum(OA_gross_forDSOM), by=kCols.datestore] DT.split_percs[, perc_split_ofOA_byDSO := OA_gross_forDSOM / sum(OA_gross_forDSOM), by=kCols.main] ## DO NOT DO THIS ... ## This section is now old (and should be deleted after confirmed not needed) ## The 'as-reported' values were already merged into DT.mgmtr_summary_tall (in file ..2_b) ## OLD: { ## OLD: ## Prepare for merge ## OLD: suppressWarnings(DT.split_percs[, c("perc_split_as_reported", "margin_for_perc_split") := NULL]) ## OLD: matchKey(DT.supplychain_percentages, DT.split_percs, kCols.SC_date) ## OLD: ## merge in the values ## OLD: DT.split_percs[DT.supplychain_percentages[type == "as-reported estimate"] , `:=`(perc_split_as_reported=i.ASR_percof_DS_forDSO, margin_for_perc_split=i.margin) ] ## OLD: } ## OLD: ## OLD: ## Add in margin for dates not yet calculated ## OLD: for (.osc in names(oscMargins)) { ## OLD: DT.split_percs[label_sc_group == .osc & date > lastDateClosed & is.na(margin_for_perc_split), margin_for_perc_split := oscMargins[[.osc]] ] ## OLD: rm(.osc) ## OLD: } ## OLD: ## OLD: ## CONFIRM: for dates greater than minDateToKeep, the only NAs for as-reported perc split should be for dates not in yet in the GL ## OLD: stopifnot(DT.split_percs[date >= minDateToKeep & is.na(perc_split_as_reported), (date %in% dates_not_in_GL)]) ## OLD: ## The perc_split to be used will be the historic one when available, otherwise the acutal one ## OLD: DT.split_percs[, perc_split := ifelse(is.na(perc_split_as_reported), perc_split_as_reported, perc_split_ofOA_byDSO) ] ## OLD: ## TODO: ADDRESS THIS ## OLD: ## NOTE: Just for dates less than minDateToKeep or higher than whats not in the GL ## OLD: ## < SEE > ## OLD: DT.split_percs[is.na(perc_split), .N, keyby=date] ## This extra column is not needed, but allows for changing the perc_split being used, without much code manipulation DT.split_percs[, perc_split := perc_split_ofOA_byDSO ] ## CONFIRM: There should be no NAs in the splits (in previous methods there were) stopifnot(DT.split_percs[, !is.na(perc_split)]) ............................................................... ## Ideally, every store that is in OA should also be in the GL. ## However, this may not always be true -- generally the reason for this not being true ## can be that a store is going by a different name, or some other purpose. ## We will eventually add another mapping. ## In the interim, we identify these stores, output it to a file that someone can then investigate DT.split_percs[, id_is_in_GL := storeid %in% DT.mgmtr_summary_tall$storeid ] if (any(DT.split_percs[, !id_is_in_GL])) { cat("There are", DT.split_percs[(!id_is_in_GL), lunique(storeid)], "OA IDs missing from the GL\n") f.missing_from_GL <- out.p("present_in_OA_but_missing_in_GL", ext="csv") write.csv(DT.split_percs[(!id_is_in_GL), list(Total_Gross = sum(OA_gross_forDSOM), first_appear=min(date), last_appear=max(date)), keyby=list(storeid, store_name)] , f.missing_from_GL, row.names=FALSE) message("Missing OA IDs have been outputed to \n\t ", f.missing_from_GL, "\n") } } ## DT.split_percs has been created ## Prep DT.mgmtr_summary_tall { ## OLD: ## We want to add in blank columns to DT.mgmtr_summary_tall BUT they must be of the right type, so take the type from the split column ## OLD: for (col.split in cols.to.spliton) { ## OLD: if (col.split %in% names(DT.mgmtr_summary_tall)) ## OLD: warning ("'", col.split, "' is already in DT.mgmtr_summary_tall") ## OLD: DT.mgmtr_summary_tall[, (col.split) := as(NA, class(DT.split_percs[[col.split]] ) )] ## OLD: ## clean up the local var ## OLD: rm (col.split) ## OLD: } invisible() } cat("reminder: perc_split is the perc_split of the meta groups, where the total is taken from OA and computed by DSO group\n") ###### ------------------------------------ ######## ################################ ### CREATE DT.merged ### ################################ ## Recall ## ## step 1) Merge the two DTs ## step 2) Permeate data through the NAs, by key ## step 3) Delete the duplicate rows ## ## Step 2 and 3 are made easier by the fact that GL DT only contributes ONE row per key, ## so the easiest approach is to permeate only its columns, then delete the rows it had ## contributed -- with the big caveat of not to delete any rows that ## are unique to GL and NOT in OA. if (FALSE) May <- as.Date("2014-05-01") { ## Match the keys for merging matchKey(DT.mgmtr_summary_tall, DT.split_percs, key=kCols.main, superset.ok=FALSE, verbose=FALSE) cols.not.merging <- c("ASR_percof_DS_forDSO", "margin") ## Allow cartesian specifically for those stores with dates not in the GL (either too early or too late) DT.merged <- merge(DT.mgmtr_summary_tall[, !cols.not.merging, with=FALSE], DT.split_percs, all=TRUE, allow=TRUE) [date >= minDateToKeep] setcolorderpt(DT.merged, kCols.splitgroup, end=c("id_is_in_GL", "id_is_in_OA")) ## ------------------------------------------------------------ ## ## NOTE: There are stores where SC labels have sales through those stores only on occasion. (eg, see MediaNet in 2014-02-01 to 2014-04-01 ) ## At some point in the future, we can get more preceise. For now, disregard, and just permeate the necessary values ## We can identify these rows using the fact (i think) that they will have NAs in the meta cols -- we would need to account for NAs in DT.split_percs ## ------------------------------------------------------------ ## message("Beginning permeates .. Can take about 20~30 seconds.") na_count_before <- colSums(is.na(DT.merged)) { ## List of columns that will need permeating and how the groups are grouped by colsToPermeate.ll <- list( 'kCols.store' = list(by=kCols.store, cols=c("id_is_in_GL", "id_is_in_OA", "music_vs_video_by_dmv", "storeid_char", "GL_firstDate_forS")) , 'kCols.datestore' = list(by=kCols.datestore, cols=c("GL_gross_forDS", "gross_last_modified")) , 'kCols.main' = list(by=kCols.main, cols=c("total_netsc_by_DSO", "GL_gross_forDSO")) # , list(by=kCols.date_SC, cols=c("perc_split_as_reported", "margin_for_perc_split")) ) cat("Permeating group ") for (i in names(colsToPermeate.ll)) { cat(i, ".... ") for (col in colsToPermeate.ll[[i]][["cols"]] ) permeate_ (DT.merged, col, by=colsToPermeate.ll[[i]][["by"]], na.rm=TRUE) } cat(" done.\n") ## What NAs are there? { na_count_after <- colSums(is.na(DT.merged)) nas_expected <- { c( "--" = 0 ## Inside and Outisde does NOT include 'Rows of stores not in GL' , "InsideDateRange" = nrow(DT.merged[(date <= lastDateClosed) & (date >= GL_firstDate_forS)]) , "OutsideDateRange" = nrow(DT.merged[(date > lastDateClosed) | (date < GL_firstDate_forS)]) , "All_Rows NOT in GL" = nrow(DT.merged[!DT.mgmtr_summary_tall]) , "All_Rows NOT in OA" = nrow(DT.merged[!DT.split_percs]) , "All_Rows" = nrow(DT.merged) , "Rows of stores not in GL" = nrow(DT.merged[!(id_is_in_GL)]) , "Rows of stores not in OA" = nrow(DT.merged[!(id_is_in_OA)]) ) } nas_count <- cbind(col=names(na_count_before), na_count_before, na_count_after, nas_removed=na_count_before - na_count_after) for (nas.nm in names(nas_expected)) nas_count[nas_count == nas_expected[[nas.nm]]] <- nas.nm nas_count <- as.data.table(nas_count) nms.GL <- names(DT.mgmtr_summary_tall) nms.OA <- names(DT.split_percs) nas_count[, source := ifelse (col %in% nms.GL & col %in% nms.OA, "BOTH", ifelse(col %in% nms.GL, "GL", ifelse(col %in% nms.OA, "OA", NA_character_)))] setkeyIfNot(nas_count, source, na_count_after, organize=FALSE, verbose=FALSE) setcolorderpt(nas_count, c("col", "source")) nas_count } ## The NAs in id_is_in_GL correspond to stores that are in GL but not in OA for that given month ## CONFIRM: stopifnot(DT.merged[is.na(id_is_in_GL), all(storeid %in% DT.mgmtr_summary_tall$storeid)]) DT.merged[is.na(id_is_in_GL), id_is_in_GL := TRUE] ## The NAs in id_is_in_OA correspond to stores that are in OA but in GL ## CONFIRM: stopifnot(DT.merged[is.na(id_is_in_OA), all(storeid %in% DT.split_percs$storeid)]) stopifnot(DT.merged[is.na(id_is_in_OA), !id_is_in_GL]) DT.merged[is.na(id_is_in_OA), id_is_in_OA := TRUE] } } &&& LEFT OFF HERE ... print(DT.merged[storeid == 404 & date <= "2013-02-01"]) DT.merged[ (id_is_in_OA) & is.na(music_vs_video)] [.(May)] .D()[ (id_is_in_OA) & is.na(music_vs_video)] [.(May)] DT.merged[!(id_is_in_OA) & is.na(music_vs_video)] [.(May)] ## We need to clean up the meta for those that are NA. ## Two groups: Those where the ID is NOT in OA, and those were ID IS in OA DT.merged[ (id_is_in_OA) & is.na(music_vs_video)] [.(May)] DT.merged[!(id_is_in_OA) & is.na(music_vs_video)] [.(May)] DT.merged[ is.na(music_vs_video)] DT.merged[unique(DT.merged[is.na(music_vs_video) & id_is_in_OA, kCols.datestore, with=FALSE]), .N][N > 4] DT.merged[.(May, c(463, 509))] DT.mgmtr_summary_tall[.(May, c(463, 509))] DT.merged[is.na(music_vs_video) & id_is_in_OA & GL_gross_forDS != 0] [label_sc_group == "Orchard"] DT.merged[.(May, c(538))] DT.mgmtr_summary_tall[.(May, c(538))] DT.split_percs[storeid == 538] ## The NAs in meta will be filled by key (ie kCols.main) ## The fillers will be chosen in this order, moving on to each subsequent if the NA persists ## ties within a choice are broken by highest perc_split First, any other instance in key +- 3 months, Second, for allegro DT.merged[is.na(music_vs_video)][, .N, by=kCols.datestore][N > 3] DT.merged[.(May, 572)] colSums(is.na(DT.merged[(id_is_in_OA)] )) DT.merged[(id_is_in_OA)][storeid == 1][date < "2013-02-02"] DT.merged[(id_is_in_OA)][is.na(music_vs_video)][.(May)] DT.merged[(id_is_in_OA)][is.na(music_vs_video)][.(May, 3)] DT.mgmtr_summary_tall[.(May, 3)] DT.split_percs[.(May, 3)] DT.merged[.(May, 1)] DT.merged[.(May, 3)] DT.merged[.(May, NA_integer_)] ## CONFIRM: No more than one NA in any of the cols.to.spliton ## Logic: NAs in cols.to.spliton can come from a row that exists ## in OA (DT.split_percs) but not in GL (DT.mgmtr_summary_tall) ## However, if there is more tha one per date-store, that came ## from somewhere else, and needs to be investigated (most likely bad data or a bug) stopifnot( DT.merged[, lapply(.SD, function(x) sum(is.na(x)) %in% 0:1), by=kCols.main, .SDcols=cols.to.spliton][, sapply(.SD, all), .SDcols=cols.to.spliton] ) ## Mark which rows are from GL and which from OA (Due to the meta, there will be no overlap) DT.merged[ , row_in_OA := FALSE] # initiate to FALSE DT.merged[ , row_in_GL := FALSE] # initiate to FALSE DT.merged[DT.split_percs , row_in_OA := TRUE] # splits come from OA DT.merged[DT.mgmtr_summary_tall, row_in_GL := TRUE] # tall comes from GL ## Confirm that there are no blanks and no duplicates. All rows are EITHER from GL or from OA. There is no overlap. Check and double-check { stopifnot(DT.merged[, (sum(row_in_GL) + sum(!row_in_GL) == .N) & (sum(row_in_OA) + sum(!row_in_OA) == .N)]) stopifnot(DT.merged[, xor(row_in_GL, row_in_OA)]) stopifnot(DT.merged[, !any(is.na(row_in_GL)) & !any(is.na(row_in_OA))]) } ## any rows that came from the GL, obviously the 'id_is_in_GL' should be TRUE DT.merged[(row_in_GL), id_is_in_GL := TRUE] ## CONFIRM: No NAs in id_is_in_GL. Also, it is unique per store stopifnot(DT.merged[, !is.na(id_is_in_GL)]) # shouldnt have any NAs stopifnot(DT.merged[, lunique(id_is_in_GL)==1, by=kCols.store][, V1]) # should be unique per store ## -------------------------------------------------- ## ## IDENTIFY WHICH ROWS WILL BE DELETED AFTER permeate ## ## -------------------------------------------------- ## ## A row will be deleted if: (1) it came from DT.mgmtr_summary_tall and (2) there is more than one row in its group DT.merged[, delete.this.row := (row_in_GL & (.N > 1) ), by=kCols.main] ## CONFIRM: No rows should be dropped for stores that are not in DT.split_percs stopifnot(0 == nrow(DT.merged[(storeid %ni% DT.split_percs$storeid) & (delete.this.row) ])) stopifnot(DT.merged[, sum(delete.this.row) %in% 0:1, by=kCols.main][, V1]) # at most one per group ## CONFIRM: Also, if we are deleting it, there should be no NAs in the cols from DT.split_percs and vice versa stopifnot(DT.merged[(delete.this.row), is.na(music_vs_video)]) stopifnot(DT.merged[!is.na(music_vs_video), !(delete.this.row)]) # I dont think use.avg is needed ... skipping this part for now if (FALSE) { ## Indicate if we will use an average ASR_percof_DS_forDSO or not. ## Namely, those rows where we do not have data for GL_netOfSC_forDS rev for the entire month-store DT.merged[, use.avg := all(is.na(perc_split)), by=kCols.main] ## use.avg | delete.this.row | represents ## --------+------------------+----------------------------------- ## FALSE FALSE Good data: We have a split perc ## FALSE TRUE Good data: row came from tall (so delete it) and the store was also in split (so we have a percentage) ## TRUE FALSE Mediocore data: we dont have a split perc ie, row NOT in DT.split_percs but yes in DT.mgmtr_summary_tall ## TRUE TRUE Bad Data: ERROR (This should not happen) ## ## CONFIRM: There should be rows where both are TRUE stopifnot(0 == nrow(DT.merged[(use.avg & delete.this.row)])) } ## there should always be more dates in OA than in GL. I cant imagine how this will not be the case. Just in case... if (!length(dates_not_in_GL)) { warning("\n\n ***** EMAIL RICK TELL HIM: ****** \"dates_not_in_GL is empty\" **********") } ## CONFIRM: For each store, there should be * NO DATES MISSING * from GL between the first time ## the store appears in the store and the last time it does. ## Check this for both, DT.merged and DT.mgmtr_summary_tall DT.merged[date >= GL_firstDate_forS, all(seq(min(date), max(date), by="+1 month") %in% date), by=kCols.store][, V1] DT.mgmtr_summary_tall[date >= GL_firstDate_forS, all(seq(min(date), max(date), by="+1 month") %in% date), by=kCols.store][, V1] ## NOTE: We do NOT expect all stores to have the same max reporting date, since some stores ## can drop, lose business etc. We only expect (as above) that there not be a gap in ## the GL. (recall that if a store does report, we still accrue a revenue, so we expect that to be there) DT.mgmtr_summary_tall[date < min(dates_not_in_GL), max(date), by=kCols.store][V1 != V1[1] ] ## Not exepcted to be all the same #### -----~~~~==== DEEP COPY OF DT.merged FOLLOWS ====~~~~------- #### #### -----~~~~==== DEEP COPY OF DT.merged FOLLOWS ====~~~~------- #### #### -----~~~~==== DEEP COPY OF DT.merged FOLLOWS ====~~~~------- #### ## ADD IN 'dates_not_in_GL' FOR (almost) ALL STORES. ## ## Explanation: ## The GL will fall short (date wise) of the data in OA. We need to add rows to the mgmtr DT. ## However, adding the missing dates to ALL stores would be wrong.. namely, we do NOT want to add said dates ## for stores with which we no longer conduct business. Those will have a last-date in the past. ## All other stores should have a presence up until the most recent month -- if not, this is an accounting issue and finance needs to be notified. ## ## In other words: ## For the most part, all stores will appear on each month of the GL, unless that store no longer does business with us. We confirmed above that ## there is no gaps between EACH STORE's min and max date and below we grab all the stores that reach the max(date < dates_not_in_GL) . ## It is unlikely that a store falls short by one month and that it SHOULD appear in the current month. ## That would most likely be an error in finance, and should NOT be corrected here (since the goal here is to match finance) { ## CONFIRM: taking rbind( !dates_not_in_GL , dates_not_in_GL) yields back the same DT stopifnot(identical(DT.merged, setkeyIfNot(rbind(DT.merged[!.(dates_not_in_GL)], DT.merged[.(dates_not_in_GL)]), key(DT.merged), verbose=FALSE))) ## identify which stores will have date added ## This will be almost all stores .. except those we no longer do business with. tmp_DT.stores_needing_dates <- DT.mgmtr_summary_tall[date < min(dates_not_in_GL), max(date), keyby=kCols.store][V1 == V1[1], kCols.store, with=FALSE ] ## add in the date column. This will create one row for each date in dates_not_in_GL tmp_DT.stores_needing_dates <- tmp_DT.stores_needing_dates[, list(date=dates_not_in_GL), keyby=kCols.store] setkeyIfNot(tmp_DT.stores_needing_dates, kCols.datestore, organize=TRUE, verbose=FALSE) dim(DT.merged) # [1] "5,460 rows X 22 cols" #### -----~~~~==== DEEP COPY OF DT.merged TAKEN HERE ====~~~~------- #### DT.merged <- rbind( DT.merged[!tmp_DT.stores_needing_dates], DT.merged[ tmp_DT.stores_needing_dates] ) ## Note that this only creates one row per date for each store missing. It does ## NOT add in data for the meta columns. Not a problem, this will be added ## when we expand to create DT.mexpanded ### THIS WAS A THOUGHT ABOUT needs_gest ... but that does not get applied til later # DT.merged <- rbind( DT.merged[!tmp_DT.stores_needing_dates] [, needs_gest := NA], # DT.merged[ tmp_DT.stores_needing_dates] [, needs_gest := TRUE] ) ## All the new merged setkeyIfNot(DT.merged, kCols.datestore, verbose=FALSE) ## cleanup suppressWarnings(rm(tmp_DT.stores_needing_dates, .mxDate)) } } ## ----------------------------------- ## NOTE TO SELF REGARDING ## GL_gross_forDS == 0 ## ----------------------------------- ## ## I originally had code in here to check if the GL total was 0 for any first month of the store, if so convert to NA ## However, leaving it as 0 is correct. ## If we convert to NA, then we will put in an estimate ## In the GL, however, the value for that $0 month will show up ## in the trueup for the next month. ## The estimate is AFTER trueup, so will be innaccurate ## ----------------------------------- .~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~. DT.mexpanded was cropped from here .~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~. ### TODO: This code works fine. ### Simply clean up the sections so ### that it is easier to follow ## All that remains is estimating the GL_netOfSC_forDS for those groups missing ## ################ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #################### ## total GL_netOfSC_forDS known | splits known | meaning | ## ----------------+----------------+-------------+ ## 1 TRUE TRUE do nothing (all is good) ## 2 TRUE FALSE estimate split ## 3* FALSE TRUE estimate the GL_netOfSC_forDS ## 4 FALSE FALSE BAD DATA - should not happen ## ------- ## * Note: for 3, we will only estimate the GL_netOfSC_forDS if it is NOT BEFORE the first revenue booked for that store. ## ################ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #################### ## ----------------------------- ## ## (4) ## ----------------------------- ## { ## There should not be any groups (or rows) where ## both the total GL_netOfSC_forDS and the split are UNKNOWN -- except MAYBE for max(date) or dates_not_in_GL stopifnot(0 == nrow(DT.mexpanded[!.(dates_not_in_GL)] [!Split_is_known & !DS_gross_is_known])) # stopifnot(0 == nrow(DT.mexpanded[!.(max(date))] [!Split_is_known & !DS_gross_is_known])) # stopifnot(0 == nrow(DT.mexpanded[ ] [!Split_is_known & !DS_gross_is_known])) } ## ----------------------------- ## ## (3) ## ESTIMATE THE GROSS ## ## ----------------------------- ## { ## Identify which rows need a GL_netOfSC_forDS estimate ## Namely those with no GL_netOfSC_forDS where the date is NOT BEFORE the first date in GL ## Note that most of these will simply be the last month for the store DT.mexpanded[, needs_gest := is.na(GL_gross_forDS) & (date > GL_firstDate_forS)] ## no need to reference 'lastDateClosed' since the dates beyond are specifically the ones we WANT to modify ### --------------------------------------------------------- #### ## UPDATE: It appears that my comment immediately following here only held true after ## re-running this from mid-run. ## If ran from the beginning it holds. I have commented out the if clause # no need :: ## TODO: This next confirm * used * to be true before I updated the data. # no need :: ## now it fails. Specificailly, there are no is.na(needs_gest) # no need :: ## (I added the if clause so tha tthe script wont fail) # no need :: ## # no need :: if (nrow(DT.mexpanded[is.na(needs_gest)])) { # no need :: message("There ARE NAs in needs_gest") ## needs_gest is NA ifff ID is NOT in GL stopifnot(DT.mexpanded[, (!id_is_in_GL == is.na(needs_gest)) ]) # no need :: } else # no need :: message("There are *not* NAs in needs_gest") ## The rows that needs_gest are rows for months not closed yet (ie, not in GL) ## All those where needs_gest, the date should be after lastDateClosed stopifnot(DT.mexpanded[(needs_gest), date > lastDateClosed ]) ### --------------------------------------------------------- #### ## IN OTHER WORDS: ## There are NO rows (other than the current working month, ## ie other than dates after lastDateClosed from GL) where we need a gest. ## If there is, this means that there is a missing value in the GL, and this in turn means that accounting needs to be notified of a potential human error. ### --------------------------------------------------------- #### # ## Two Scenerios: # 3.a: estimate the GL_netOfSC_forDS for the last month for a given store (use historic GL) # 3.b: estimate the GL_netOfSC_forDS for the WHOLE store for the whole time (use OA) ## FIRST ESTIMATE A GROSS FOR ALL STORES NOT IN GL # (3.b) ## We have to forecast for all those IDs not in the GL DT.mexpanded[!(id_is_in_GL), GL_gross_forDS := sumn(OA_gross_forDSOM), by=kCols.main] ## CONFIRM: After this, there should not be any NA's in GL_gross_forDS for those dates WITHIN the GL dates ## (ie, all NAs should be either date < GL_firstDate_forS) stopifnot(DT.mexpanded[date >= GL_firstDate_forS & date <= lastDateClosed, !any( is.na(GL_gross_forDS) )]) # (3.a) ## make sure key is by date and store setkeyIfNot(DT.mexpanded, kCols.main, verbose=FALSE) ## CONFIRM: the date seq for each store should be exactly a month apart (days in 28:31) stopifnot(DT.mexpanded[(date >= GL_firstDate_forS), unique(removeNA(GL_gross_forDS)), by=kCols.main][, diff(date) %in% 28:31, by=kCols.store][, V1 ]) ## ABOUT 4 MINUTES ## WHEN DEV'ING, you can skip the forecast.. notice the 'else' is wrapped inside s.t() if (exists("skip_forecast") && isTRUE(skip_forecast) && exists("DT.forecasts_mexpanded")) { message("Skipping Forecast") } else s.t(title="calculate DT.forecasts using various methods", { message("Beginning forecasts .. Can take about 1~4 minutes.") method_used_for_forecasting <- "all" forecasts_mexpanded.ll <- emptylist(dates_not_in_GL, nm=as.character(dates_not_in_GL)) ## We need to create forecasts FOR EACH DATE in dates_not_in_GL for (date.target in dates_not_in_GL) { date.target <- as.Date(date.target) ## We will test which is the better forecast forecasts_mexpanded.ll[[as.character(date.target)]] <- mkForecast( DT=DT.mexpanded[date >= GL_firstDate_forS] ## GL_firstDate_forS will be NA when !(id_is_in_GL) , valueCol="GL_gross_forDS" , byCols=kCols.store , date_diff="month" , date.target=date.target , months.to.test=4 , method=method_used_for_forecasting , verbose=TRUE , fail.on.duplicates=TRUE ) ## bank the forecast method ## Also, Apply the same method to all future dates, after the first test method_used_for_forecasting <- attr(forecasts_mexpanded.ll[[as.character(date.target)]], "method") } #// end for loop ## Flatten the list DT.forecasts_mexpanded <- rbindlist(forecasts_mexpanded.ll) setkeyIfNot(DT.forecasts_mexpanded, kCols.main) }) #// end s.t() ## We wil replace the empty GL_gross_forDS ## CONFIRM: When joining, all of the GL_gross_forDS should be NA or they already have the same value as the forecast .... ## NOTE.. I think this only works because using ma3 for now .... yet, I dont think it is important that they have the same value stopifnot(DT.mexpanded[DT.forecasts_mexpanded][!is.na(GL_gross_forDS), all(forecastedValue == GL_gross_forDS)]) ## NOTE: DT.forecasts_mexpanded has forecasts for ALL stores, including those that ## are no longer in the GL after some date ## So that the join DT.mexpanded[DT.forecasts_mexpanded] ## will produce all columns with NA including needs_gest which is otherwise ## TRUE for all of these rows ## ## see: DT.mexpanded[DT.forecasts_mexpanded] [is.na(needs_gest)] ## ## Thus, if we want the real join, use: ## DT.mexpanded[DT.forecasts_mexpanded] [!is.na(needs_gest)] ## CONFIRM: The GL_gross_forDS should be NA for all values being joined. ## (Otherwise, we might be overwriting data) stopifnot(DT.mexpanded[DT.forecasts_mexpanded] [, is.na(GL_gross_forDS)]) DT.mexpanded[DT.forecasts_mexpanded, GL_gross_forDS := i.forecastedValue] ## CONFIRM: There should be no NAs in GL_gross_forDS (except for maybe before store was in GL) stopifnot(DT.mexpanded[date > GL_firstDate_forS, !is.na(GL_gross_forDS)]) } ## ~~~~~~~~~ ## TODO: (1) Clean up 'use.avg' and 'Split_is_known' ## (2) This will ess up some of the stopifnot() ## ~~~~~~~~~ ## ----------------------------- ## ## (2) CALCULATE THE SPLITS ## ## ----------------------------- ## { ## For each store, identify which months need splits, namely those where Split_is_known == FALSE *AND* date > GL_firstDate_forS ## Namely, those with non-NA, non-zero GL_gross_forDS BUT with NA perc_split ## ## NOTE: We will only keep those rows with { date >= GL_firstDate_forS | is.na(GL_firstDate_forS) } ## But for now, we will use all the OA info to help calculate the splits per each store ## CONFIRM: Split_is_known is unique by date-store stopifnot(DT.mexpanded[, (sum(Split_is_known)/.N) %in% 0:1, by=kCols.main] [, V1]) ## CONFIRM: by date-source, the sum of perc_split should be 0 when Split_is_known is FALSE and 1 when Split_is_known is TRUE. In otherwords, sum(perc_split) == Split_is_known (No need for unique() thanks to recycling) stopifnot(DT.mexpanded[, equals(sum(perc_split), Split_is_known), by=kCols.main] [, V1]) ## For verbose: Count the number of stores needing splits count.stores_needing_splits.before <- DT.mexpanded[date >= GL_firstDate_forS, sum(perc_split)==0, by=kCols.main][(V1), nrow(unique(.SD, by=kCols.store))] ## FILL THE perc_split BY REFERENCE calculateSplitPercentages_(DT.mexpanded, grossCol="OA_gross_forDSOM", grpCols=kCols.main, splitCols=kCols.splitgroup) ## For verbose: Count the number of stores needing splits count.stores_needing_splits.after <- DT.mexpanded[date >= GL_firstDate_forS, sum(perc_split)==0, by=kCols.main][(V1), nrow(unique(.SD, by=kCols.store))] ## Inform user of progress message(sprintf("[BEFORE] Stores that needed split estimates: %i\n[AFTER] Stores that remain without split estimates: %s", count.stores_needing_splits.before, ifelse(count.stores_needing_splits.after, count.stores_needing_splits.after, "0 (None!)"))) } # (1) CONFIRM, DROP ROWS, MULTIPLY GROSS, CONFIRM { ## CONFIRM: perc_split sums to 1 stopifnot(DT.mexpanded[date> GL_firstDate_forS, equals1(sum(perc_split)), by=kCols.main][, V1]) ## CONFIRM: all stores have GL_gross_forDS or estimate stopifnot(DT.mexpanded[date> GL_firstDate_forS, !is.na(GL_gross_forDS)]) ## ------------------------------------------------------------------------------------------- ## ## IDENTIFY *CLEARLY* WHICH ROWS TO DROP (notice that DT[!(NA)] does NOT get kept by default) ## ## ------------------------------------------------------------------------------------------- ## ## KEEP NAs, by default DT.mexpanded[is.na(delete.this.row), delete.this.row := FALSE] ## DROP any row prior to the first date it was in the GL DT.mexpanded[date < GL_firstDate_forS, delete.this.row := TRUE] ## If all of the cols.to.spliton are NA *AND* GL_netOfSC_forDS is $0, drop that row DT.mexpanded[ (GL_netOfSC_forDS == 0) & {rowSums(DT.mexpanded[, lapply(.SD, is.na), .SDcols=cols.to.spliton]) == length(cols.to.spliton)} , delete.this.row := TRUE] ## CONFIRM: All delete.this.row & !delete.this.row == .N stopifnot(DT.mexpanded[, sum(delete.this.row) + sum(!delete.this.row) == .N ]) ## DROP THE delete.this.row ROWS DT.cleaned <- DT.mexpanded[!(delete.this.row)] ## TODO: Why did these rows remain? DT.cleaned[, GL_netOfSC_forDS := GL_gross_forDS * perc_split] ## CONFIRM: The GL_netOfSC_forDS usms up to the GL_gross_forDS stopifnot(DT.cleaned[, equals(sum(GL_netOfSC_forDS), unique(GL_gross_forDS)), by=kCols.main][, V1]) ## Drop uneeded columns colsDropping <- c("row_in_OA", "row_in_GL", "delete.this.row", "Split_is_known", "DS_gross_is_known", "needs_gest", if ("use.avg" %in% names(DT.cleaned)) "use.avg") DT.cleaned[, (colsDropping) := NULL] invisible() } ## Final cleaning { ## Add musicbucket/videobucket columns matchKey(DT.cleaned, DT.stores, kCols.store, organize=TRUE, verbose=FALSE) DT.cleaned[DT.stores, `:=`(store_musicbucket=i.store_musicbucket, store_videobucket=i.store_videobucket)] DT.cleaned[store_musicbucket %in% c("zOTHER STORESz", "") | is.na(store_musicbucket), store_musicbucket := "zUncategorized"] DT.cleaned[store_videobucket %in% c("zOTHER STORESz", "") | is.na(store_videobucket), store_videobucket := "zUncategorized"] ## CONFIRM: Exactly one value per store stopifnot(DT.cleaned[, 1==lunique(store_videobucket) & 1==lunique(store_musicbucket), keyby=kCols.store][, V1]) invisible() } ### CALCULATING ALLEGRO / SELECTO SPLIT FOR EXCEL WORKSHEET DT.OSC <- DT.cleaned[, list(perc_split_per_OSC=sumn(perc_split)), keyby=c("label_sc_group", kCols.main)][.(c("Allegro", "SelectO"))] DT.OSC[, total_perc_split_per_DS := sum(perc_split_per_OSC), by=kCols.main] DT.OSC[total_perc_split_per_DS != 0, mean(perc_split_per_OSC / total_perc_split_per_DS), by=label_sc_group] message("Done with Part 2_b ... next up create wide table")