DT.mgmtr_summary_tall <- copy(DT.bak) if (!exists("minDateToKeep")) minDateToKeep <- "2013-01-01" ###### ------------------------------------ ######## # { if (!exists("DT.bak")) DT.bak <- copy(DT.mgmtr_summary_tall) cols.to.spliton <- c("music_vs_video", "label_sc_group", "release_product_type", "release_is_compilation") # NO: "music_vs_video" (it's part of the GL stores) ## NEED TO FIGURE OUT NON-ALLOWABLE LOGIC kCols.splitgroup <- c(kCols.main, cols.to.spliton) kCols.splitgroup_nodate <- setdiff(kCols.splitgroup, "date") ## CREATE DT.split_percs -- Calculate the split-percentages from OA accounting and OA analytics { ## start with Accounting tmpDT.split_percs.acc <- DT.acc[, list(OA_gross_forDSOM = sum(gross)), keyby=kCols.splitgroup] ## take what's missing from analytics tmpDT.split_percs.anal <- DT.anal[date %ni% unique(tmpDT.split_percs.acc$date), list(OA_gross_forDSOM = sum(gest, na.rm=TRUE)), keyby=kCols.splitgroup] ## rbind the two DT.split_percs <- rbind(tmpDT.split_percs.acc, tmpDT.split_percs.anal) ## cleanup rm(tmpDT.split_percs.acc, tmpDT.split_percs.anal) } ## NOT SURE IF THIS IS STILL NEED IF DOING ALL COLUMNS AT ONCE if (FALSE) { # message(" Um, but why? ") # for (col.split in cols.to.spliton) { # kCols.col.split <- c(kCols.main, col.split) # setkeyIfNot(DT.split_percs, kCols.col.split, superset.ok=FALSE, verbose=FALSE) # DT.split_percs <- DT.split_percs[setkey(DT.split_percs[, unique(DT.split_percs[[col.split]]), keyby=kCols.main]), allow.cartesian=TRUE] # # ## cleanup # rm(kCols.col.split, col.split) # } # DT.split_percs[is.na(OA_gross_forDSOM), OA_gross_forDSOM := 0] } ## Calculate the SC split per each DT.split_percs[, perc_split := OA_gross_forDSOM / sum(OA_gross_forDSOM), by=kCols.main] ###### ------------------------------------ ######## ## Following this section, we will create DT.merged which we will merge on all of the meta data ## 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 for (col.split in cols.to.spliton) { DT.mgmtr_summary_tall[, (col.split) := as(NA, class(DT.split_percs[[col.split]] ) )] ## clean up the local var rm (col.split) } ################################ ### DEEP COPY TAKEN HERE ### ################################ ## IMPORTANTLY, we will delete the original # Ideally, we want to use # DT.mgmtr_summary_tall[DT.split_percs] # But, the metaCols in .._tall do not come through, so we merge, clean, then drop the extra rows. ## CREATE DT.merged { ## Match the keys for merging matchKey(DT.mgmtr_summary_tall, DT.split_percs, key=kCols.splitgroup, superset.ok=FALSE, verbose=FALSE) DT.merged <- merge(DT.mgmtr_summary_tall, DT.split_percs, all=TRUE) [date >= minDateToKeep] ## Mark which rows are in GL and which in 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.mgmtr_summary_tall, row_in_OA := TRUE] DT.merged[DT.split_percs , row_in_GL := TRUE] ## Confirm that there are no blanks stopifnot(DT.merged[, sum(row_in_GL) + sum(!row_in_GL) == .N], DT.merged[, sum(row_in_OA) + sum(!row_in_OA) == .N]) ## Currently, all rows are EITHER from GL or from OA. There is no overlap stopifnot(DT.merged[, xor(row_in_GL, row_in_OA)]) ## We will delete a row if it came from DT.mgmtr_summary_tall AND there are more than one rows. ## Thus, first search for those with more than one row, and we can use a column unique to DT.mgmtr_summary_tall as an NA indicator DT.merged[, delete.this.row := (row_in_OA & (.N > 1) ), by=kCols.main] ## Same as: DT.merged[DT.merged[, .N, keyby=kCols.main][N>1], delete.this.row := row_in_OA] ## 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) ])) ## Indicate if we will use an average ASR_percof_DS_forDSO or not. ## Namely, those rows where we do not have data for gross 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)])) ## 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)]) } ## CLEAN DT.merged { ### NOTE TO SELF ### ### As an example, Have a look at this individual itunes month. ### The merge will have NAs in all the meta and numbers in the data of the original summary rows, ### and it will have values filled in for the meta from DT.split_percs but NA for many of the numbers. ### We need to get these to share with each other. DT.merged[storeid == 1 & date == "2014-01-01"] DT.merged[storeid==478 & date == "2013-04-01"] ## Fill meta cols. The stopifnot checks that there is exactly one value per group, removing NAs that were inserted ## from the merge (which should be NA in the same rows across all metaCols, thats why we can use just the one ## column to remove NAs) metaCols <- setdiff(names(DT.mgmtr_summary_tall), names(DT.split_percs)) stopifnot(isUniqueByGroup(DT.merged[!is.na(GL_gross_forDS)], colsToCheck=metaCols, byCols=kCols.main)) ## Permeate all Meta Cols for (col in metaCols) { DT.merged[, (col) := removeNA(get(col)), by=kCols.main] # DT.merged[, (col) := {.SD; browser(expr=!delete.this.row); removeNA(get(col))}, by=kCols.main] # DT.mgmtr_summary_tall[date == as.Date(.BY[[1]], origin=.origin) & storeid == .BY[[2]]] # DT.mgmtr_summary_tall[.BY] # DT.merged[.BY] rm(col) } matchKey(DT.mgmtr_summary_tall, DT.merged, key(DT.mgmtr_summary_tall)) ## Identify which rows need an estimate for gross. ## Two main reasons: ## (1) OA is FUTRE and not yet in GL ## (2) OA is past and GL not yet booked ... these will eventually be dropped.. keeping in the meantime for averaging ## (3) OA is in the middle -- this is erroor and should not occur DT.merged[, needs_gest := all(is.na(GL_gross_forDS)), by=kCols.main] # # NOT sure if we care about max date per store... just the last month otr two, but even then, we already flagged which need gest, so this might be useless # DT.merged[, row_is_max_date_by_store := date == max(date), by=list(storeid, store_name)] ## why? stopifnot(DT.merged[!use.avg & is.na(GL_gross_forDS) & store_name %in% DT.mgmtr_summary_tall$store_name, all(needs_gest)]) ## eg (x2) of what needs a gest from no GL DT.merged[storeid==538 & date == "2013-11-01"] DT.mgmtr_summary_tall[storeid==538 & date == "2013-11-01"] ## not present DT.merged[storeid == 497 & date == "2014-06-01"] DT.mgmtr_summary_tall[storeid == 497 & date == "2014-06-01"] ## not present ## eg of store appearing in OA prior to appearing in GL DT.mgmtr_summary_tall[DT.merged[!use.avg & is.na(GL_gross_forDS) & store_name %in% DT.mgmtr_summary_tall$store_name]] [store_name == "Boomkat"] DT.mgmtr_summary_tall[store_name == "Boomkat"] ## There should not be any NAs in GL_gross_forDS except in those rows that we will filling with avgs or already determined needing gest stopifnot(DT.merged[!(use.avg) & !(needs_gest), !is.na(GL_gross_forDS)]) ## why? stopifnot( DT.merged[, !any( delete.this.row & needs_gest )] ) ## After we've permeated the metaCols, there shouldnt be any ## NAs in GL_gross_forDS that arent flagged for either deleting or gest stopifnot( DT.merged[(!delete.this.row & !needs_gest), !is.na(GL_gross_forDS)] ) } ## CALCULATE gest for DT.merged { ## CONFIRM: Following this line we will apply unique(.. , by=..) ## It is important that we do not accidentally drop any values of gross rev ## Thus we want to confirm that there is exactly one value per group stopifnot(DT.merged[, lunique(GL_gross_forDS), by=kCols.main][, V1==1]) DT.gest <- unique(DT.merged, by=kCols.main)[ ][allDatesByCols(DT.merged, dateCol="date", by=kCols.main) ## close off the brackets, because of by-without-by (we want to use diff column) ][ , list(GL_gross_forDS=unique(GL_gross_forDS)), by=kCols.main ][ , list(date, gest = ma.fill(GL_gross_forDS, order=3, warn=FALSE)), by=kCols.store ] ## merge in the gest matchKey(DT.merged, DT.gest, kCols.main, superset.ok=TRUE, organize=TRUE) DT.merged[DT.gest, total_gest_by_DS := i.gest] ## REMINDER: each row either needs a gest or the value for GL_gross_forDS is NOT NA stopifnot(DT.merged[, xor(needs_gest, !is.na(GL_gross_forDS))]) DT.merged[, GL_gross_forDS2 := ifelse(is.na(GL_gross_forDS), total_gest_by_DS, GL_gross_forDS)] ## EXPLORING: DT.merged[store_name == "DMX"] DT.merged[store_name %like% "Cricket" & label_sc_group == "Orchard"][, !names(DT.merged)[c(2,3,4,5,9, 11, 13)], with=FALSE][, dup := duplicated(perc_split)][] ## What to do about ASR_percof_DS_forDSO is NA, but total gross is not ## We can fill averages either by the whole business or by store. Use by store whenever we have sufficient data ## clear invisible(suppressWarnings(DT.merged[, use.avg.juststore := NULL])) kCols.store <- c("storeid", "store_name") setkeyIfNot(DT.merged, kCols.store, organize=TRUE) DT.merged[unique(DT.merged[(use.avg)]) , use.avg.juststore := sum(use.avg) <= (.N-2) ## at least two blanks needed # , by=kCols.store .. by-without-by, not necessary ] ## CONFIRM: There shouldnt be any NAs when use.avg is TRUE and ...? stopifnot(DT.merged[(use.avg), !is.na(use.avg.juststore)]) ## (1) fill by store avg # By group: Total up the 'OA_gross_forDSOM' (which is the gross from OA) and divide by the total revenue DT.merged[, .__total_OA_gross_by_store := sum(OA_gross_forDSOM, na.rm=TRUE), by=kCols.store] # DT.merged[, .__total_gross_by_meta := sum(OA_gross_forDSOM, na.rm=TRUE), by=c(setdiff(kCols.splitgroup, "date"))] DT.merged[store_name=="DMX"] DT.merged[storeid==580] Q setkeyIfNot(DT.merged, kCols.splitgroup_nodate, organize=TRUE) DT.calc_avgsplit <- DT.merged[(use.avg.juststore & !is.na(OA_gross_forDSOM)), c(kCols.splitgroup_nodate, "OA_gross_forDSOM"), with=FALSE] DT.calc_avgsplit[, ._sum_by_grp := sum(OA_gross_forDSOM), by=kCols.splitgroup_nodate] DT.calc_avgsplit[, ._sum_by_store := sum(OA_gross_forDSOM), by=kCols.store] ## CONFIRM: Exactly one value of each (sum by grp, sum by store) per group stopifnot(DT.calc_avgsplit[, list(lunique(._sum_by_grp), lunique(._sum_by_store)), by=kCols.splitgroup_nodate][, c(V1, V2) == 1]) ## Calculate DT.avgsplit <- DT.calc_avgsplit[, list(est_perc_split = ._sum_by_grp[[1]] / ._sum_by_store[[1]]), keyby=kCols.splitgroup_nodate] ## Put them back in matchKey(DT.merged, DT.avgsplit, key=kCols.splitgroup_nodate, superset.ok=FALSE, verbose=TRUE, organize=TRUE) DT.merged[DT.avgsplit, est_perc_split_store := i.est_perc_split] DT.merged[(use.avg.juststore), perc_split_avg_withinstore := removeNA(OA_gross_forDSOM) / .__total_OA_gross_by_store[[1]], by=kCols.splitgroup_nodate] DT.merged[(use.avg.juststore), perc_split_avg_withinstore := {browser(expr=(.BY[[1]]==580)); removeNA(OA_gross_forDSOM) / .__total_OA_gross_by_store[[1]]}, by=kCols.splitgroup_nodate] DT.merged[.BY[1:2]] ## CONFIRM: exactly one unique value per group stopifnot(DT.merged[(use.avg), lunique(removeNA(perc_split_avg_withinstore)), by=kCols.splitgroup_nodate][V1 %in% 0:1]) ## CONFIRM: Within each date-store , the sum of the gross_split_by_grp should equal the (unique) GL_gross_forDS2 (ignoring NA splits) stopifnot(DT.merged[!is.na(gross_split_by_grp), abs(GL_gross_forDS2[[1]] - sum(gross_split_by_grp)) < 1e-6 , by=kCols.main][, V1]) DT.merged[] DT.merged[, GL_gross_forDS2[[1]] == sum(gross_split_by_grp) , by=kCols.main][(!V1)] DT.merged[, rolled_up_gross_for_grp := sum(gross_split_by_grp, na.rm=TRUE), by=kCols.main] DT.merged[(needs_gest & !is.na(GL_gross_forDS))] DT.merged[(is.na(GL_gross_forDS) & !needs_gest)] DT.merged[is.na(gest)] } DT.merged[!(delete.this.row)][ !is.na(storeid_char)] rm(DT.merged2) <- DT.mgmtr_summary_tall[DT.split_percs] howManyNAs(DT.merged2[, metaCols, with=FALSE], perc=TRUE) DT.merged2[is.na(delete.this.row), delete.this.row := FALSE] DT.merged[, GL_gross_forDS2 := GL_gross_forDS[!is.na(GL_gross_forDS)], by=kCols.main] DT.merged[, GL_gross_forDS2 := NULL] DT.merged[, lunique(GL_gross_forDS[!is.na(GL_gross_forDS)]), by=kCols.main] [V1 ==2] DT.merged[storeid==478 & date=="2013-04-01"] unique(DT.mgmtr_summary_tall[storeid==478 & date=="2013-04-01"], by=setdiff(names(DT.mgmtr_summary_tall), c("gross", "GL_gross_forDS"))) DT.merged[, lunique(GL_gross_forDS[!is.na(GL_gross_forDS)]), by=kCols.main] [V1 ==1] ## Spread the GL_gross_forDS across the whole store DT.mgmtr_summary_tall[, GL_gross_forDS, by=kCols.main] [!is.na(GL_gross_forDS)] DT.mgmtr_summary_tall <- rbindlist(lapply(unique(DT.split_percs[[col.split]] ), function(x) ## logic: It cannot be a compilation and NOT be (Orchard AND Music) cbind(DT.mgmtr_summary_tall, col.split = x)[!(col.split & !(label_sc_group == "Orchard" & music_vs_video == "Music" & musicbucket %in% c("Download", "zUncategorized Store")))] )) kCols.for_gross_summing <- c(kCols.main, "label_sc_group", "music_vs_video", "musicbucket") ## Take the average permonth of each SC group. Then Confirm all sum to a whole DT.avg_perc_splits <- DT.split_percs[, sum(OA_gross_forDSOM), keyby=c("date", "col.split")][, list(col.split, avg_perc_split = V1 / sum(V1)), by=date] setkeyIfNot(DT.avg_perc_splits, c("date", "col.split"), verbose=FALSE) stopifnot(sumsToOne(DT.avg_perc_splits, "avg_perc_split", by="date")) ## set those groups that have only one unique "col.split" value to a prelim split percentage of 100%. (To avoid monthly avverages) setkeyIfNot(DT.mgmtr_summary_tall, kCols.for_gross_summing, verbose=FALSE) DT.mgmtr_summary_tall[DT.mgmtr_summary_tall[, lunique(col.split), by=kCols.for_gross_summing][(V1 == 1)], perc_split := 1] ## match the keys to add the splits ## Load in the splits matchKey(DT.mgmtr_summary_tall, DT.split_percs, key(DT.split_percs)) DT.mgmtr_summary_tall[DT.split_percs, perc_split := ifelse(is.na(perc_split), i.perc_split, perc_split)] ## Manually view on screen DT.mgmtr_summary_tall[!is.na(perc_split)] [, lunique(col.split), by=kCols.for_gross_summing][(V1 == 1)] DT.mgmtr_summary_tall[is.na(perc_split)] [, lunique(col.split), by=kCols.for_gross_summing][(V1 != 1)] ## Fill with monthly averages matchKey(DT.mgmtr_summary_tall, DT.avg_perc_splits, c("date", "col.split")) DT.mgmtr_summary_tall[DT.avg_perc_splits, perc_split := ifelse(is.na(perc_split), round(i.avg_perc_split, 4), perc_split)] ## There should not be any perc_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(perc_split) & !all(is.na(perc_split)), by=kCols.main][, !any(V1)]) ## ****************** ## ## There should not be any compilations in non-orchard, non-music, non-download. Check that all others are "1" cor perc_split stopifnot(sumsToOne(DT.mgmtr_summary_tall, col="perc_split", by=kCols.for_gross_summing), fail=TRUE) stopifnot(DT.mgmtr_summary_tall[!(label_sc_group == "Orchard" & music_vs_video == "Music" & musicbucket %in% c("Download", "zUncategorized Store"))][, perc_split == 1]) ## There should not be any NA's in perc_split stopifnot(DT.mgmtr_summary_tall[, !is.na(perc_split)]) ## Next, multiply the gross by the perc_split ## Take back any rounding error and assign it to "Orchard" DT.mgmtr_summary_tall[, gross_comp_splat := gross * perc_split] DT.mgmtr_summary_tall[, gross_comp_splat := ifelse(col.split==TRUE, gross - sum(gross_comp_splat[col.split != TRUE]), gross_comp_splat), by=kCols.for_gross_summing] ## Confirm all sum stopifnot(DT.mgmtr_summary_tall[, sum(gross_comp_splat) - sum(unique(GL_gross_forDS), na.rm=TRUE), by=kCols.main][, abs(V1) < 1e-4]) ## move the gross_x_splat over to gross, and drop the splat column DT.mgmtr_summary_tall[, gross := NULL] setnames(DT.mgmtr_summary_tall, "gross_comp_splat", "gross") ## clear the backup rm(DT.bak) DT.mgmtr_summary_tall } ###### ------------------------------------ ########