## this reads in the .TXT files, looks for "Data point" as an indicator of a new report ## and splits pu the reports according to that line ## It then merges report from different months by "Data point" library(XLConnect) library(bit) library(bit64) library(data.table) library(reshape2) .us() setScience(proj="Spotify Data", subl=FALSE) ## File where to save results f.out <- as.path(outDir, "Spotify_New_Data_Fields.xlsx") f.out.data <- as.path(outDir, "Spotify_New_Data.xlsx") ## Folder containing the input TSV files folder.in <- as.path(dataDir, "New Spotify Data") ## Load them in files <- dir(folder.in, full=TRUE) setattr(files, "names", basename(files)) # raw <- lapply(files, readLines) raw <- vector(mode="list", length=length(files)) rinfo <- vector(mode="list", length=length(files)) for (i in seq(files)) { rr <- readLines(files[[i]]) rr <- gsub("\\#", "Numb", rr) rr <- gsub("\\%", "Perc", rr) ## Grab the starting points for each section dps <- grep("^Data point", rr) ## The ending point is 1-less from each starting point, plus the last line ends <- c(dps[-1] -1, length(rr)) ## The few lines before the first start point is the header info info.inds <- seq(1, dps[[1]] - 1) rinfo[[i]] <- as.data.table(read.csv(text=rr[info.inds], sep="\t", header=TRUE, stringsAsFactors=FALSE)) start.date <- rinfo[[i]] [, as.Date(Start.date)] end.date <- rinfo[[i]] [, as.Date(End.date)] Month <- format(start.date, "%B") raw[[i]] <- mapply(function(s, e) { dat <- as.data.table(read.table(text=rr[s:e], sep="\t", header=TRUE, stringsAsFactors=FALSE)) setnames(dat, strsplit(rr[[s]], "\t")[[1]]) dat[, c("Month", "Start Date", "End Date") := list(Month, start.date, end.date)] }, dps, ends) setattr(raw[[i]], "names", sapply(raw[[i]], function(D) D[["Data point"]][[1]]) ) } ## collapse the header into a single table rinfo <- rbindlist(rinfo) ## set the names of the raw tables to the dates setattr(raw, "names", rinfo[["Start.date"]]) ## grab all report names nms <- unique(unlist(sapply(raw, names), use.names=FALSE)) ## set names to itself, for the final output selfname_(nms) ## iterate over the report dates for (i in seq(raw)) { missing.reports <- setdiff(nms, names(raw[[i]])) if (length(missing.reports)) raw[[i]] [missing.reports] <- vector(mode="list", length=length(missing.reports)) } final <- lapply(nms, function(nm) { rbindlist(lapply(raw, "[[", nm)) }) # ---------------------------------------------------------------------------- countrygrps <- fread(as.path(dataDir, "Country Groups.csv")) ## Clean Names setnames(countrygrps, gsub(" ", "_", topropper(names(countrygrps)))) setkey(countrygrps, Country_Code) final <- lapply(final, function(D) { ## Some reports may not have country breakdowns, specifically 'Streams per platform' if ("Country" %in% names(D)) setkey(D, Country)[countrygrps, `:=`(region_group=i.region_group, Country_Broad_Group=i.Country_Broad_Group)] setnames(D, gsub("(\\d) sec", "\\1sec", names(D))) ## Replace Spaces with Underscores spaceInNamesToUnderscore_(D) }) final # ---------------------------------------------------------------------------- folder.out <- as.path(outDir, "CSVs") dir.create(folder.out, showWarnings=FALSE) for (nm in names(final)) { DD <- final[[nm]] nm2 <- gsub(".*\\((.*).tsv\\)", "\\1.csv", nm) nm2 <- paste0(nm, ".csv") write.table(DD[, !"Data_point", with=FALSE], file=as.path(folder.out, nm2), sep=",", row.names=FALSE, col.names=TRUE, fileEncoding="UTF-8") } .o(folder.out) # ---------------------------------------------------------------------------- printS <- function(X, n=7, topn=15, byCols=c("Country_Broad_Group", "Month"), noPrint=c("Data point", "Country", "region_group", "Start Date", "End Date"), countriesUsing=c("USA", "United Kingdom", "Sweden", "Brazil", "Uruguay")) { if (is.list(X) && is.data.table(X[[1]])) { cat("\n\n\n") return(invisible(lapply(X, printS, noPrint=noPrint))) } DP <- c("Data point", "Data_point") DP <- DP[DP %in% names(X)] if (!is.null(DP)) cat("\n", pasteR(" ", 11), sprintf("----====|[ '%s' ]|====----\n", topropper_preopositions(X[[DP[[1]] ]][[1]])), pasteR(" ", 8), pasteR(55), "\n") ## Clean up spaces in names, which bugs out .SDcol spaceInNamesToUnderscore_ (X) noPrint <- spaceToUnderscore(noPrint) ## Only keep byCols which are in names(X) nms <- names(X) byCols <- byCols[byCols %in% nms] colsUsing <- setdiff(nms, c(byCols, noPrint)) nh <- floor(n/2) nt <- ceiling(n/2) H <- X[, {inds <- c(1:nh, (.N-nt-1):.N) inds <- sort(unique(inds[inds>0 & inds <= .N])) .SD[inds] } , .SDcols=colsUsing, keyby=byCols] if ("Country_Broad_Group" %in% names(H)) { H <- H[.(intersect(countriesUsing, Country_Broad_Group) )] } setnames(H, gsub("Numb_of_active", "Active", names(H))) setnames(H, gsub("average_over", "Avg_over", names(H))) setnames(H, gsub("Country_Broad_Group", "Country", names(H))) print(H , nrows=60, topn=topn) return(invisible(H)) } printS(final) # ---------------------------------------------------------------------------- P <- list() Current <- "Breakage Rate" DD <- final[[Current]] DD country_sizes <- DD[, .N, keyby=region_group][.("zOTHER"), N:=0L][, setNames(obj=scaleunif(N, 2, 5), region_group)] lib(ggplot2) dp <- DD[["Data_point"]][[1]] { P[[Current]] <- ggplot(DD, aes(x=factor(Month), y=`Breakage_Rate_(Perc)`/100)) + geom_boxplot(outlier.size=0) + geom_jitter(aes(color=region_group, size=region_group), width=.3) + percent.y() + labs(x="", y=Current) + scale_size_manual(breaks=names(country_sizes), values=country_sizes, guide=FALSE) + ggtitle(paste(dp, "\n")) + legendbottom(TRUE) } Current <- "Conversion" DD <- final[[Current]] lib(ggplot2) country_sizes <- DD[, .N, keyby=region_group][.("zOTHER"), N:=0L][, setNames(obj=scaleunif(N, 2, 5), region_group)] dp <- DD[["Data_point"]][[1]] { P[[Current]] <- ggplot(DD, aes(x=factor(Month), y=`Conversion_(Perc)`/100)) + geom_boxplot(outlier.size=0) + geom_jitter(aes(color=region_group, size=region_group), width=.3) + percent.y() + labs(x="", y=Current) + scale_size_manual(breaks=names(country_sizes), values=country_sizes, guide=FALSE) + ggtitle(paste(dp, "\n")) + legendbottom(TRUE) # + geom_text(data=DD[`Conversion_(Perc)`> 40], aes(label=Country, y=`Conversion_(Perc)`/ 100)) P } printS(final)