# ----------------------------------------------------------------------------------------------------------------------------------------------- # # --------------------------------------------------------------------------------------------------------------------------------------------- # # # # File Name : cleanLookerHeaders_after_download.r # # Last Updated Funclist : 25 Nov 2015, 2:34 PM (Wednesday) # # # # Author Name : Rick Saporta # # Author Email : RSaporta@TheOrchard.com # # Author URL : www.github.com/rsaporta # # # # Packages Called : NA # # Packages Used via NS : NA # # # # --------------------------------------------------------------------------------------------------------------------------------------------- # # # # cleanLookerHeaders ( file.fullpath, sep="auto", verbose="No used", force=FALSE, is_applemusic=FALSE ) # # trim ( x ) # # zipperCombine ( x, y ) # # outputCleanFile ( f ) # # combine_looker_exports_to_excel ( folder, pattern=NULL, ext="csv", sep="auto" # # , files=extractFilesFromFolder(folder, pattern=pattern, ext=ext, full=TRUE) # # , basename.f.out=if (!missing(folder)) paste0(basename(folder), "_export") else "Looker_Export", sort=FALSE # # , open=TRUE, zArchiveExisting=TRUE, force=FALSE # # , percCols.thresh.for.values.gt.1=0.8 ) # # clean.capture.output ( ..., pipe_ok=FALSE, file=NULL, append=FALSE, quote=FALSE, envir=parent.frame() ) # # padString ( x, width=mnchar(x), align=c("right", "left") ) # # boxit ( ... ) # # pasteC ( ..., C="" ) # # regOr ( vec ) # # mnchar ( x, ..., na.rm=FALSE ) # # # # # # # # --------------------------------------------------------------------------------------------------------------------------------------------- # # ----------------------------------------------------------------------------------------------------------------------------------------------- # cleanLookerHeaders <- function(file.fullpath, sep="auto", verbose="No used", force=FALSE, propper_headers=TRUE, is_applemusic=FALSE) { require(magrittr, quietly=TRUE) if (!is.character(file.fullpath) || !length(file.fullpath)) stop ("It appears that the file given is not a charcter or name of a file") if (length(file.fullpath) > 1) return(sapply(file.fullpath, cleanLookerHeaders, sep=sep, verbose=verbose, force=force)) raw <- readLines(file.fullpath) if (!length(raw)) { warning("No rows found in file '", file.fullpath, "") return(file.fullpath) } # if (missing(sep)) { # if (!grepl(sep, raw[[1]]) && grepl("\t", raw[[1]])) # sep <- "\t" # } if (identical(sep, "auto")) { sep <- sep_from_ext(file.fullpath) if (!grepl(sep, raw[[1]])) { if (grepl("\v", raw[[1]])) sep <- "\v" else if (grepl("\t", raw[[1]])) sep <- "\t" else if (grepl(",", raw[[1]])) sep <- "," } } headers <- raw[[1]] %>% strsplit(sep) %>% {.[[1]]} headers_orig <- copy(headers) dims_to_remove <- c("Release", "Store", "Label", "Artist", "Genre", "Track", "Transactiontype", "Country") facts_to_remove <- c("Sales", "Analytics") other_to_remove <- c(escapeRegEx("(Yes / No)"), "Ranks & Top-X", "Applemusicraw", "^Rates") to_remove <- c( sprintf("Fact %s", facts_to_remove) , sprintf("Dim %ss?", dims_to_remove) , other_to_remove ) pat <- regOr(to_remove) %>% paste0("\\b\\s?", .) repl <- "" headers %<>% gsub(pat, repl, ., ignore.case=TRUE) %>% trim ## Remove some duplicate words headers %<>% gsub("Product Type Product Type", "Product Type", .) %>% gsub("Date Date", "Date", .) %>% gsub("Subaccount Subaccount", "Subaccount ", .) %>% gsub("Display Month", "Month", .) %>% gsub("Territory USA (or|vs) ROW", "USA vs ROW", .) %>% gsub("Territory Country ", "Country ", .) %>% gsub("Sales( USD| CCur)? ", "", .) %>% gsub("Region (Artist|Label|User)", "\\1", ., ignore.case=TRUE) %>% gsub("for (20\\d\\d)", "\\1", ., ignore.case=TRUE) %>% gsub("date_of_ac", "Ac", .) %>% gsub("Region Code", "Country Code", .) %>% gsub("Region Name", "Country", .) %>% gsub("Region Country", "Country", .) %>% gsub("Staging Raw Spotify v2 ", "", .) %>% gsub("Date Activity ", "Activity ", .) %>% gsub("Date Accounting ", "Accounting ", .) %>% gsub("Date Release ", "Release ", .) %>% gsub("Date Sale Start ", "Sale Start ", .) %>% gsub("Release Imprint", "Imprint", .) %>% gsub("Label Client Manager", "Client Manager", .) %>% gsub("Label Subaccount", "Subaccount", .) %>% gsub("Revshare (Avg |)(Orchard |Spotify )", "\\1\\2", .) %>% gsub("Release UPC", "UPC", .) %>% gsub("Release\\sID", "UPC", ., ignore.case=TRUE) %>% gsub("Release Date Added Date", "Date Added", ., ignore.case=TRUE) %>% gsub("Release( Release)? Date( Date)?", "Release Date", ., ignore.case=TRUE) %>% gsub("Release Sale Start Date( Date)?", "Sale-Start Date", ., ignore.case=TRUE) %>% gsub("Currency Currency( Code)?", "Currency", .) %>% gsub("Release Days From ReleaseDate to Today", "Days From ReleaseDate to Today", .) %>% gsub("Release Months From ReleaseDate to Today", "Months From ReleaseDate to Today", .) %>% gsub("\\bDthree", "D3", ., ignore.case=TRUE) %>% gsub(" Is ", " is ", .) %>% # gsub("\\Ioda\\b", "IODA", .) %>% gsub("(Ad\\-Supported|Subscription) Audio Streams", "\\1", .) %>% gsub("^\\s*zETL\\s*", "", ., ignore.case=TRUE) %>% gsub("^[z|Z] ", "", .) %>% gsub("Download Download Albums( Sales)?( Units)?", "Download Albums", ., ignore.case=TRUE) %>% gsub("Download Download Tracks( Sales)?( Units)?", "Download Tracks", ., ignore.case=TRUE) %>% gsub("Stream Ad-Supported( Sales)?( Units)?", "Ad-Supported Streams", ., ignore.case=TRUE) %>% gsub("Stream Subscription( Sales)?( Units)?", "Subscription Streams", ., ignore.case=TRUE) %>% {.} ## TODO: This may not always be the case headers %<>% gsub("Fx ", "Client ", ., ignore.case=TRUE) headers %<>% gsub("for Selected Months", "for All Time ", ., ignore.case=TRUE) ## JUST APPLE MUSIC if (is_applemusic) { headers %<>% gsub("converted_to_usd_per_play", "USD Rate Per Play", ., ignore.case=TRUE) headers %<>% gsub("2015-07", "2015-Sept (Jul Activity)", ., ignore.case=TRUE) headers %<>% gsub("2015-08", "2015-Oct (Aug Activity)", ., ignore.case=TRUE) headers %<>% gsub("Gross Rev .USD.", "Gross Rev USD", ., ignore.case=TRUE) } if (propper_headers) headers %<>% topropper_keywords() to_caps <- c("UPC", "ISRC", "AISN", "ISBN", "ISRC", "ID", "USD", "EUR", "ARPU", "ETL") %>% regOr %>% sprintf("\\b%s\\b", .) headers %<>% gsub(to_caps, "\\U\\1", ., ignore.case=TRUE, perl=TRUE) to_lowers <- c("Cneutral") %>% regOr %>% sprintf("\\b%s\\b", .) headers %<>% gsub(to_lowers, "\\L\\1", ., ignore.case=TRUE, perl=TRUE) if (all(headers == headers_orig)) { changes_possible <- FALSE force <- TRUE cat("It looks like there are no auto-cleanup changes to perform to the column names\n\n") } else { changes_possible <- TRUE cat("Will make the following changes: \n") } outputCleanFile <- function(f) { f %<>% path.expand if (exists(outDir)) f <- gsub(path.expand(outDir), "[outDir]", f) if (exists(dataDir)) f <- gsub(path.expand(dataDir), "[dataDir]", f) f <- gsub("/Users/rsaporta/Dropbox/The Orchard/Analytics Department/Outbound Reports/", "[Outbound Reports]/", f) return(f) } ## VERBOSE cbind(padString(c("ORIGINAL FIELD NAME", "", headers_orig)), ' | ', " "=padString(c("NEW FIELD NAME", "", headers))) %>% clean.capture.output(quote=FALSE) %>% c(outputCleanFile(file.fullpath), .) %>% paste0("\n") %>% boxit(align="right", min_width=0) %>% cat("", sep="\n") if (force) { confirm <- 'Y' } else { confirm <- readline("OK to Proceed? [Y/N] > ") %>% trim %>% toupper } if (confirm %ni% c("Y", "YE", "YES")) { message("\n\n ~~~~ Got It. NO CHANGES WERE MADE ~~~~\n\n") return(invisible(NULL)) } ## Although we could possibly only write if changes_possible, having the same file-change time is helpful and the files are generally small, so no noticeable time lost raw[[1]] <- pasteC(headers, C=sep) writeLines(text=raw, con=file.fullpath) if (changes_possible) message("OK. Changes completed.\n\n") return(file.fullpath) } if (FALSE) { combine_looker_exports_to_excel(folder, basename="Streams per Top Territories", zArchiveExisting=TRUE) combine_looker_exports_to_excel(folder=folder, zArchiveExisting=TRUE) y } # DEPRECATED 2015-10-01 # ## DEPRECATED NAME # DEPRECATED 2015-10-01 # combine_exports_to_excel <- function(..., DEPRECATED="use combine_looker_exports_to_excel() ") { # DEPRECATED 2015-10-01 # warning ("combine_exports_to_excel has been deprecated. use combine_looker_exports_to_excel()", call.=FALSE) # DEPRECATED 2015-10-01 # combine_looker_exports_to_excel(...) # DEPRECATED 2015-10-01 # } combine_looker_exports_to_excel <- function(folder, pattern=NULL, ext="csv", sep="auto", files=extractFilesFromFolder(folder, pattern=pattern, ext=ext, full=TRUE), basename.f.out=if(!missing(folder)) paste0(basename(folder), "_export") else "Looker_Export", sort=FALSE, propper_headers=TRUE, open=TRUE, zArchiveExisting=TRUE, force=FALSE, percCols.thresh.for.values.gt.1=.8) { if (!missing(folder)) folder <- trim(folder) message("Remember to check if cropping length(31) causes duplicate sheetnames") require(bit64, quietly=TRUE) if (any(!file.exists(folder))) stop(sprintf("folder '%s' does not exist\n", folder[!file.exists(folder)])) if (any(isdir(folder)) && !all(isdir(folder))) stop("'folder' is a mix of directories and non-directories") if (!isdir(folder) && length(folder) > 1) stop ("Do not know how to process multiple files as input. Combine them to one folder and use the folder as input. Alternatively use cleanLookerHeaders()") ## Allow for the first input, folder, to be a single file if (!isdir(folder)) { files <- folder f_sub <- capture.output(substitute(folder)) pat <- "^f(\\.out)?(\\.|_)" if (grepl(pat, f_sub, ignore.case=TRUE)) { basename.f.out <- removeText(pat, f_sub, ignore.case=TRUE) } else { basename.f.out <- basename(files) %>% removeExt() } folder <- dirname(folder) } files %<>% {.[file.exists(.)]} ## This was specific to a certain set of files; dont leave in because it will mess up other files. Especially the "top" part if (FALSE) { pat.ext <- sprintf("\\.%s", regOr(ext)) pat_remove <- sprintf(" by Revenue 2015 2014 2013 2015.*%s", pat.ext) ## THIS IS WRONG -- SHOULD ONLY REMOVE FROM BASENAME, NOT THE FULL FILENAME files_new_name <- gsub(pat_remove, ".\\1", files) %>% gsub("(/|\\b)Top ", "\\1", .) file.rename(files, files_new_name) } else { pat.ext <- sprintf("\\.%s", regOr(ext)) files_new_name <- files } force(basename.f.out) ## Clean up column names for Excel cleanLookerHeaders(files_new_name, force=force, is_applemusic = !missing(folder) && grepl("apple\\s*music", folder, ignore.case=TRUE), propper_headers=propper_headers, sep=sep) pat.tstamp <- "2015-09-04T1725" pat.tstamp <- "\\d{4}\\-\\d{2}\\-\\d{2}T\\d{4}" files_new_name %>% basename(.) %>% gsub(pat.ext, "", .) %>% gsub(pat.tstamp, "", .) %>% trim files_new_name %<>% {setNames(., basename(.) %>% gsub(pat.ext, "", .) %>% gsub(pat.tstamp, "", .) %>% trim)} if (missing(basename.f.out)) { if (!missing(folder)) basename.f.out <- basename(folder) else if (length(files) == 1) basename.f.out <- basename(files) %>% splitOnLast("\\.") %>% .[[1]] %>% head(1) } if (FALSE) { nms <- names(files_new_name) %>% sort nms <- extract("Track", nms) %>% c(extract("Album", nms)) %>% c(extract("Label", nms)) %>% c(nms) %>% unique stopifnot(identical(sort(files_new_name[nms]), sort(files_new_name))) files_new_name <- files_new_name[nms] } if (!missing(folder)) f.out <- as.path(folder, basename.f.out, ext="xlsx") else f.out <- out.p(basename.f.out, ext="xlsx") if (file.exists(f.out)) { if (zArchiveExisting) zArchive(f.out, addTimeStamp=TRUE) else stop("f.out '", f.out, "' already exists\nHINT: use zArchiveExisting()", call.=FALSE) } # sep <- ifelse(identical(ext, "csv"), ",", "auto") ## Remove any starting numbers, used to indicate ordering of slides names(files_new_name) <- gsub("^\\d+[a-z]{,2}( |_)", "", names(files_new_name)) ## Ingest the files into memory DT.list <- lapply(files_new_name, fread, header=TRUE, sep=sep, skip=0) ## Export to a XLSX exportXLS.usingXLConnect(f.out=f.out, DTs.list=DT.list, percCols.thresh.for.values.gt.1=percCols.thresh.for.values.gt.1) if (.Pfm == "Darwin") .o(f.out) return(f.out) } # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # # ----------------------------- FUNCTIONS BELOW THIS LINE EXIST ELSEWHERE --------------------------------- # # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # if (!exists("clean.capture.output")) clean.capture.output <- function (... , pipe_ok=FALSE, file=NULL, append=FALSE, quote=FALSE, envir=parent.frame()) { ## like capture.output, but removes all of the [1] [2] .. etc from the start of the line ## NOTE: (1) This does NOT clean matrix output ## (2) This works poorly on a vector of strings browser(expr=inDebugMode("clean.capture.output"), text="in clean.capture.output, at top") formals(print.default)$quote <- FALSE on.exit(formals(print.default)$quote <- TRUE, add=TRUE) if (!isTRUE(pipe_ok) && any(sapply(sys.calls(), "==", c("eval(lhs, parent, parent)")))) { stop ("capture.output (and hence clean.capture.output) does not work with pipe %>% or %T>%") } last_few_sys.calls <- tail(sys.calls(), 4) ## for some reason tail(sys.calls()) on one machine caputres 'tail()' and on the other does not ## thus, Identify which index to drop. If "tail" is included drop the last one. Otherwise, drop the first one (we grabbed to many) if (last_few_sys.calls[[length(last_few_sys.calls)]][[1L]] == "tail") last_few_sys.calls <- head(last_few_sys.calls, -1) else last_few_sys.calls <- tail(last_few_sys.calls, -1) is_in_sapply <- grepl("X\\[\\[\\d+L\\]\\]", capture.output(substitute(...))) || "lapply" %in% sapply(last_few_sys.calls, function(x) as.character(as.list(x)[[1L]])) # cat("is_in_sapply == ", is_in_sapply, fill=TRUE) if (is_in_sapply) ret <- capture.output(print(...)) else { ## For some reason, calling eval first allows capture.output to work better. ## I have this wrapped in sink() to avoid any output that was not captured sink(tempfile(pattern="capt_output_tmpfile_")) eval(substitute(...), envir=envir) sink() ## removing quotes requires calling print. However, not all expressions woould be appropriate to ## call print() for. There are a few that we know for sure. Add to this list as needed ## This list is only important when using quotes=FALSE use_print_for_funcs <- c("cbind", "rbind") if (isTRUE(as.character(substitute(...)[[1]]) %in% use_print_for_funcs)) ret <- capture.output(print(eval(substitute(...), envir=envir), quote=quote)) else ret <- capture.output(eval(substitute(...), envir=envir)) } ## remove any starting " [1] " ret <- gsub("^\\s*\\[\\d+\\]\\s*", "", ret) ## if there is more than one line, and the output is a function, clear any lastline "NULL" if (length(ret) > 1 && tail(ret, 1) == "NULL" && any(grepl("function \\(", ret[1:2]))) ret <- head(ret, -1) ## if quote is set to FALSE, remove the quote marks if (!isTRUE(quote) & all(grepl("^\\\"", ret)) & all(grepl("\\\"$", ret))) ret <- gsub("^\\\"|\\\"$", "", ret) return(ret) } if (!exists("padString")) padString <- function(x, width=mnchar(x), align=c("right", "left")) { align <- match.arg(align) if (align == "left") width <- -width width %>% paste0("%",.,"s") %>% sprintf(x) } if (!exists("boxit")) boxit <- function(...) { gsub(" \\| ", " ~~~> ", ..1) %>% gsub("\\n$", "", .) %>% gsub("^\\|", "", .) } if (!exists("pasteC")) pasteC <- function(..., C="") paste(..., collapse=C) if (!exists("regOr")) regOr <- function(vec) { pasteC(vec, C="|") %>% pasteC('(', ., ')', C="", sep="") } if (!exists("mnchar")) mnchar <- function(x, ..., na.rm=FALSE) { ## We duplicate the line of code because it is faster than copying x <- x[!is.na(x)] or filtering x[TRUE] if (na.rm) max(nchar(as.character(x[!is.na(x)]), ...)) else max(nchar(as.character(x), ...)) } if (!exists("trim")) trim <- function(x) { ## Remove whitespace, while preserving NAs. if (!length(x)) return(x) if (is.list(x)) return(lapply(x, trim)) nas <- is.na(x) ret <- gsub("^\\s+|\\s+$", "", x) if (any(nas)) ret[nas] <- NA return(ret) } if (!exists("zipperCombine")) zipperCombine <- function(x, y) { ## Combines the elements of x and y one at a time, ie ## [1] "x1" "y1" "x2" "y2" "x3" "y3" "x4" "y4" "x5" "y5" c(x, y) [order(c(seq(x), seq(y)))] } # ------------------------------------------ # if (!interactive()) { # file.fullpath <- dir("~/Downloads", full=TRUE, patter="^SP") # cleanLookerHeaders(file.fullpath) # subl(file.fullpath) args <- commandArgs(trailingOnly = TRUE) cat(" ------------------------------------ \n") print(args) print(names(args)) cat(" ------------------------------------ \n") if (length(args)) { force <- FALSE if (any(tolower(args) == "force")) { args <- args[tolower(args) != "force"] force <- TRUE } if (length(args)) cleanLookerHeaders(file.fullpath=args, force=force) } }