# screen -xRR HiddenContent # screen -xRR H3 # source("~/git/orch/src/ContentAnalysis/01 Hidden Content Modelling.r") setScience(proj="ContentAnalysis", subProj="Hidden", subl=FALSE, load=FALSE) if (.Pfm == "Darwin") { try(move_files_from_dl_folder_to_ingest()) syncIngestDir() } if (.Pfm != "Darwin") { if (!exists("wh") || !sfIsWarehouseOn(wh, verbose=FALSE)) wh <- "SCIENCE" dbname <- "prod" setSnowflake(wh=wh, dbname=dbname, start=FALSE) if (!sfIsWarehouseOn(wh)) sfWarehouseOn(wh=wh, size="M") } setGitBranchToSystem(); .g(); ## Old method was to manually download the large processed files from the google Doc and save as a TSV ## New method is to take the weekly XLSX files and use those instead use_TSVs <- FALSE ## Update ... back to old method use_TSVs <- TRUE ## ------------------------------------------------------------------- ## NOT USING APPLE ID MATCHING FOR NOW ## ------------------------------------------------------------------- ## if (!exists("DT.tracks")) loadFromJesus("DT.tracks", projName="BunnyLee") ## ## ## Trying to match apple_id to what we have in our apple_id_mapping table ## ## will not work, since that is based on what we get back from apple. None of the hidden tracks match up ## if (FALSE) { ## if (!exists("DT.apple_ids")) loadFromJesus("DT.apple_ids", projName="DeNormalizing") ## setIDCols(DT.apple_ids) ## DT.hidden[, c("orchard_track_id", "album_or_track") := NULL] ## addColsFrom_(DT.hidden, DT.apple_ids, colsToBring=c("orchard_track_id", "album_or_track"), joinCols="apple_id") ## } ## ------------------------------------------------------------------- # source("~/git/orch/src/ContentAnalysis/pull DT.releases_with_metadata.r") ## This upc is strange -- it is not in our system upc_strange_infrac_in_hidden_file <- 842914096881 if ("upc" %ni% names(DT.releases_with_metadata)) DT.releases_with_metadata[, upc := as.idcol(releaseid)] if (!is.idcol(DT.releases_with_metadata$upc)) DT.releases_with_metadata[, upc := as.idcol(upc)] ## --------------------------------------------------------------------------- ## ## ----------------------- READ IN and CLEAN DT.hidden ---------------------- ## ## --------------------------------------------------------------------------- ## if (use_TSVs) { files.hidden_content <- c( IODA = ingest.p("iTunes Hidden Music Trends (IODA) - editorials-refusals.tsv") , IRIS = ingest.p("iTunes Hidden Music Trends (IRIS) - editorials-refusals.tsv") , ORCH = ingest.p("iTunes Hidden Music Trends 2015 (ORCH) - editorial-refusal-cleanup.tsv") , ORCH16 = ingest.p("iTunes Hidden Music Trends 2016 (ORCH) - editorial-refusal-cleanup.tsv")) stopifnot(file.exists(files.hidden_content)) # DT.hidden <- suppressWarnings(lapply(files.hidden_content, fread, integer64="numeric")) DT.hidden <- lapply(names(files.hidden_content), function(nm) suppressWarnings(setnamestolower(fread(files.hidden_content[[nm]])))[, content_owner := nm] ) %>% rbindlist(fill=TRUE, use.names=TRUE) DT.hidden[content_owner == "ORCH16", content_owner := "ORCH"] } else { DT.hidden <- get_DT.hidden_raw() BackUpOrRestore("DT.hidden", postfix=".raw.bak", force=TRUE) } dict.nms <- c( "upc/ean/jan" = "upc" , "album" = "release_name" , "artist"="artist_name" , "label name" = "imprint" , "master label" = "label_name" , "within window?" = "occurred_first_90_days" , "client manager" = "client_manager_at_the_time" ) setnamesByDict(DT.hidden, dict=dict.nms, showWarnings=FALSE, warn_for_new_return=FALSE) DT.hidden %>% cleanColNamesForSQL_ %>% setIDCols ## UPDATE: This is true only if using TSVs ## There are 1097 rows where there is no data, only a date. Drop these. DT.hidden <- DT.hidden[!(apple_id == "none" & upc == "none" & grid == "none")] DT.hidden <- DT.hidden[!(apple_id == "" & upc == "" & grid == "")] ## Convert integers intCols <- whichColsCanBeInteger(DT.hidden, ignoreIDCols=TRUE) if (length(intCols)) DT.hidden[, (intCols) := lapply(.SD, as.integer), .SDcols=intCols] ## is_complete needs some cleanup ... but for now, just treat it as a factor DT.hidden[, is_complete := factor(is_complete)] DT.hidden[, content_owner := toFactorWithExpectedLevels(content_owner, c("ORCH", "IODA", "IRIS"))] ## Add orchard ID from apple id ## ---- CODE REMOVED FOR NOW ## ---- none of the tracks from the HIDDEN report are in the apple id mapping, so this is of no use ## Drop that strange row DT.hidden <- DT.hidden[(upc %ni% upc_strange_infrac_in_hidden_file)] ## DROP BLANKS DT.hidden <- DT.hidden[upc %ni% c("", "none")] ## DROP "Clay Pasternack" UPCs from the IRIS set ## These were then sent through IODA a year later. ## They create a duplicate infrac count upcs.clay_pasternack.IRIS <- c("609788707783", "609788707790", "609788707806", "609788707813", "609788707820", "609788707837", "609788707844") DT.hidden <- DT.hidden[!(upc %in% upcs.clay_pasternack.IRIS & content_owner == "IRIS")] if (use_TSVs) { ## IRIS upcs are unmatched DT.hidden[content_owner == "IRIS" & is.na(label_name), label_name := "IRIS Acquired Labels"] ## Clean up these NAs DT.hidden[client_manager_at_the_time == "#N/A", client_manager_at_the_time := NA] DT.hidden[occurred_first_90_days == "#N/A", occurred_first_90_days := NA] if (nrow(DT.hidden[occurred_first_90_days == "#VALUE!"]) == 1) DT.hidden[occurred_first_90_days == "#VALUE!", occurred_first_90_days := "yes"] ## Convert occurred_first_90_days from yes/no to TRUE/FALSE; also cleaning up NAs stopifnot(DT.hidden[!is.na(occurred_first_90_days), all(occurred_first_90_days %in% c("no", "yes"))]) DT.hidden[, occurred_first_90_days := (occurred_first_90_days == "yes")] ## the "occurred_first_90_days" field is only present for Orchard, rest is IODA/IRIS stopifnot(DT.hidden[is.na(occurred_first_90_days) & content_owner == "ORCH", !.N]) DT.hidden[is.na(occurred_first_90_days), occurred_first_90_days := FALSE] ## clean up date DT.hidden[, date := as.Date(date, format="%m/%d/%Y")] ## Provider column is just a duplicate of content_owner column; Drop it DT.hidden[, provider := NULL] } else { addColsFrom_(DT.hidden, DT.releases_with_metadata, colsToBring=c("release_date_added"), joinCols="upc") DT.hidden[, occurred_first_90_days := (date - release_date_added) <= 100] catn("This is how many releases do not have a release_date_added") print(unique(DT.hidden, by=c("upc", "apple_id", "content_owner"))[, fwp(round(percTrue(is.na(release_date_added)), 2)), keyby=list(content_owner, year=year(date))]) } ## audio_must_be_redelivered_for_itunes_plus NOT USED DT.hidden[, audio_must_be_redelivered_for_itunes_plus := NULL] ## Clean up reason text DT.hidden[, reason := removeText("Hidden: ", reason) %>% tolower %>% spaceToUnderscore] DT.hidden[, year_of_latest_infraction_by_upc := max(year(date)), by=upc] ## identify first infrac by upc DT.hidden[, `:=`(first_infrac_by_upc=min(date) ,latest_infrac_by_upc=max(date)) , by=upc] ## KEY setkeyIfNot(DT.hidden, upc, apple_id, date, organize=TRUE, verbose=FALSE) ## Count how many events occurred for any given ID ## Note that for the most part, there is only one infrancting apple_id per upc, but not always verboseMsg(verbose, "Counting reasons by aid") DT.hidden[ , distinct_reasons_by_apid := lunique(reason), by=list(apple_id)] DT.hidden[ , lifetime_infracs_by_apid := (.N), by=list(apple_id)] DT.hidden[ , any_event_in_first90days_by_apid := any(occurred_first_90_days), by=list(apple_id)] verboseMsg(verbose, "Counting reasons by upc") DT.hidden[ , distinct_reasons_by_upc := lunique(reason), by=list(upc)] DT.hidden[ , lifetime_infracs_by_upc := (.N), by=list(upc)] DT.hidden[ , any_event_in_first90days_by_upc := any(occurred_first_90_days), by=list(upc)] ## Give each reason-per-aid a unique serial. This is necessary so that there is exactly one date per reasonCols DT.hidden[order(date, decreasing=FALSE), reason_occurrance_by_apid := seq.int(.N), by=list(apple_id, upc, reason)] ## KEY setkeyIfNot(DT.hidden, upc, apple_id, date, organize=TRUE, verbose=FALSE) ## ADD labelid # metaCols <- c("labelid", "label_name", "label_country_code", "imprint", "artistid", "artist_name", "artist_country_code") metaCols <- c("labelid", "imprint", "artistid") # setnames(DT.hidden, c("vendor_id", "label_name", "artist_name"), c("vendor_id_old", "label_name_old", "artist_name_old")) suppressWarnings(DT.hidden[, c("vendor_id", "label_name", "artist_name", metaCols) := NULL]) addColsFrom_(DT.hidden, DT.releases_with_metadata, joinCols="upc", colsToBring=metaCols) "## WORK IN PROGRESS" ## ------------------------------------------------ ## ## Trying to match IRIS UPCs ## ## ------------------------------------------------ ## if (FALSE) { DT.mupcs[, manufacturer_upc.numb := as.numeric(manufacturer_upc)] DT.hidden[, iris_upc := NA_character_] DT.hidden[(upc %in% DT.releases_with_metadata$upc), iris_upc := ""] iris_upcs <- DT.hidden[content_owner == "IRIS" & is.na(iris_upc), unique(upc)] tmp_DT.iris <- DT.mupcs[manufacturer_upc.numb %in% iris_upcs, list(id=manufacturer_upc.numb, upc)] DT.hidden } ## ------------------------------------------------ ## ## Identify which release have more than one reason DT.hidden[lifetime_infracs_by_apid > 1] #x moved to a separate file ... # ## Upload to Snowflake #x moved to a separate file ... # if (FALSE) { #x moved to a separate file ... # ## We dont want this running everytime #x moved to a separate file ... # ingestIntoSQL(DT=DT.hidden, tbl="hidden_content_itunes", schema="bi", snowflake=TRUE, append=FALSE, drop=TRUE, add.ingestDate=TRUE, wh=wh, dbname=dbname) #x moved to a separate file ... # sourceSupportFns(proj="Looker") #x moved to a separate file ... # create_lookml_from_tbl(tbl="hidden_content_itunes", schema="bi", snowflake=TRUE, wh=wh, wh=wh, dbname=dbname) #x moved to a separate file ... # #x moved to a separate file ... # ## Copy the model locally #x moved to a separate file ... # if (.Pfm=="Darwin") #x moved to a separate file ... # bringAndCopyLookerFiles(out.p("auto_generated_lookmls", proj="ContentAnalysis")) #x moved to a separate file ... # } func <- function(x) { d <- unique(x) L <- length(d) if (!L) return(as.Date(NA)) if (L == 1) return(d) warning("dates has length ", L, " --- investigate using DT.casted calls in the file (Approx line 185 in src.p('Hidden Content Modelling.r')") return (as.Date(-30000, origin=.origin)) } ## CREATE DT.casted { verboseMsg(verbose, "Creating DT.casted") reasonCols <- DT.hidden[, unique(reason)] %>% removeNA fmla <- makeFormula(DT=DT.hidden, Right="reason", except=c("date", "client_manager_at_the_time", "occurred_first_90_days", "any_event_in_first90days_by_apid", "any_event_in_first90days_by_upc")) DT.casted <- dcast.data.table(DT.hidden, fmla, fun.aggregate=func, value.var="date") cat("\n ", pasteR(88), "\n\t\tThe lastest infraction was on ", DT.casted[, reasonCols, with=FALSE] %>% unlistDateTime(use.names=FALSE) %>% maxn %>% as.character, "\n\n The min/max date for each infranction reason is as follows:\n\n") print(DT.casted[, c(list('min/max'=c("min", "max")), lapply(.SD, rangen)), .SDcols=reasonCols]) cat("\n ", pasteR(88), "\n") } ## Show which data from DT.hidden was bad. (Should be 0 rows each) if (FALSE) { ## These next three tables should all have 0 rows --- if not, investigate DT.casted[artist_cleanup == as.Date(-30000, origin=.origin)] DT.casted[editorial_discretion == as.Date(-30000, origin=.origin)] DT.casted[refusal == as.Date(-30000, origin=.origin)] } { ## In Each row of the following, you should see more than one reason column having a non-NA value DT.casted[distinct_reasons_by_apid>1] %>% head(8) ## show min / max date per reasonCol DT.casted[, c(list(c("min", "max")), lapply(.SD, rangen)), .SDcols=reasonCols] DT.casted[, rbindlist(lapply(names(.SD), function(nm) data.table(reason=nm, min=minn(.SD[[nm]]), max=maxn(.SD[[nm]])))), .SDcols=reasonCols][order(max)] DT.casted[, lapply(.SD, function(X) fwp(percTrue(!is.na(X)))), .SDcols=reasonCols] DT.casted[!is.na(refusal), .N, keyby=list(refusal, labelid)][order(refusal)] DT.casted[!is.na(refusal), .N, keyby=list(refusal, labelid)][refusal >= "2015-01-01"][N>2] } { verboseMsg(verbose, "Creating DT.infractions_by_label (NOTE This is imperfect since label names are less than accurate. Once in DT.merge, the label names are based off the UPC and hence more accurate.)\n") DT.infractions_by_label <- DT.casted[year_of_latest_infraction_by_upc >= 2015][, lapply(.SD, function(x) sum(!is.na(x))), .SDcols=reasonCols, by=labelid] DT.infractions_by_label[, lifetime_infractions := rowSums(.SD), .SDcols=reasonCols] DT.infractions_by_label[order(lifetime_infractions, decreasing=TRUE)][1:50] } if (FALSE) jesusForData(DT.hidden, DT.casted, DT.infractions_by_label)