.us() { if (!exists("projName")) setScience(proj="Lee", create=TRUE, subl=FALSE, load=FALSE) dataDir2 <- as.path(dataDir, "Spotify/Spotify_Rev_Share") ## If working in Accounting dataDir2 <- as.path(gsub("Accounting", "Lee", dataDir), "Spotify/Spotify_Rev_Share") subProj <- "Lee" files <- dir.info(dataDir2, recursive=TRUE, output=FALSE) datePare_fread <- function(f, ...) { # parse out the date info from the filename and append it to the table D <- fread(f, ...) ## grab the date, which is the last hyphen of the file Month <- tail(strsplit(gsub("\\.(txt|csv)$", "", basename(f)), "-")[[1]], 1) ## dates are given as 20141, 20142, ... , 201411, 201412. Split on year, then use sprintf to put to known format Month <- do.call(sprintf, c(list("%4i-%02i-01"), as.integer(unlist(strsplit(Month, "(?<=\\d{4})", perl=TRUE))))) Month <- as.Date(Month) D[, Month := Month] return(D) } raw <- lapply(as.path(dataDir2, files$file), datePare_fread, colClasses=list(character="Product")) DT.Acc.perDCnP <- rbindlist.fill(raw) ## CLEAN setnames(DT.Acc.perDCnP, gsub(" ", "_", names(DT.Acc.perDCnP))) setnames(DT.Acc.perDCnP, gsub("'", "", names(DT.Acc.perDCnP))) setnames(DT.Acc.perDCnP, gsub("Product", "Product_code", names(DT.Acc.perDCnP))) DT.Acc.perDCnP[, Report_start_date := NULL ] DT.Acc.perDCnP[, Report_end_date := NULL ] # DT.Acc.perDCnP[, Report_start_date := as.Date(Report_start_date)] # DT.Acc.perDCnP[, Report_end_date := as.Date(Report_end_date)] # # ## Drop end_date since it is identical to start_date # if (DT.Acc.perDCnP[, all(Report_start_date==Report_end_date, na.rm=TRUE)]) { # DT.Acc.perDCnP[, Report_end_date := NULL] # setnames(DT.Acc.perDCnP, "Report_start_date", "Date") # } else { # stop("Not all Report_end_date are same as Report_start_date") # } ## SO FAR, All Registered Users are Active Users. That does not make sense warning ("The number of registered users ", ifelse(DT.Acc.perDCnP[, all(Registered_users == Active_users, na.rm=TRUE)], "is **the same** across all", "is **different** amongst some"), " groupings\n") keyCols <- c("Country", "Month") setcolorderpt(DT.Acc.perDCnP, keyCols) setkeyv(DT.Acc.perDCnP, keyCols) ## Add Product Tier & Product Description DT.Acc.perDCnP [, Product_Tier := makeFactorUsingDict(vec=Product_code, dict=getDict.SpotifyTier(), missing_to_NA=FALSE)] DT.Acc.perDCnP [, Product := makeFactorUsingDict(vec=Product_code, dict=getDict.SpotifyAccountingProduct(), missing_to_NA=FALSE)] if (length(DT.Acc.perDCnP [is.na(Product) & !is.na(Product_code), unique(Product_code)])) warning("Some Product_code Descriptions are NA") ## If there was no dict for a given product_code, the deafault is to use the product_code. ## However, instead use the Product (description) which would be more useful DT.Acc.perDCnP[as.character(Product_Tier) == as.character(Product_code), Product_Tier := Product] DT.Acc.perDCnP[.("BE", as.Date("2013-10-01")), ] # ## NEEDED : Our user count. Then compute as a fraction of total active users # DT.Acc.perDCnP[, Orchard_User_Share := Rightholders_tracks / Registered_users] ### Group the columns for display and for understanding nms.acc <- names(DT.Acc.perDCnP) cols.acc <- list() cols.acc[["head"]] <- c("Month", "Country", "Product_code", "Product_Tier", "Product", "Fee_per_month") cols.acc[["user"]] <- c( sort(extract("user", nms.acc))) cols.acc[["track"]] <- c( extract("track", nms.acc), "Pro_rata_share") cols.acc[["pay"]] <- sort(c(extract("pay", nms.acc), "Currency")) cols.acc[["reven"]] <- extract("reven", nms.acc) cols.acc[["partn"]] <- c("Partner", "Months_committed_but_not_activated", "Total_bundles") cols.acc[["zOTHERz"]] <- setdiff(nms.acc, unlist(cols.acc)) setcolorderpt(DT.Acc.perDCnP, unlist(cols.acc)) ## Some Verbose output, FYI if (FALSE) { filter.cnt <- DT.Acc.perDCnP$Country %in% c("US", "SE", "AR", "CH", "SI") for (cols in cols.acc) print( DT.Acc.perDCnP[filter.cnt , c("Month", "Product_code", cols), with=FALSE] [c(1:2, 50:51, 100:101, 149:150)] ) } ### CLEAN UP SOME COLUMNS ## The data has some (one?) rows that are 0 Payable with no Currency and nothing in Payable_USD . Remedy this. DT.Acc.perDCnP[Payable==0 & is.na(Payable_USD), Payable_USD := 0] ## No rouding DT.Acc.perDCnP[, Pro_rata_share.exact := Rightholders_tracks / Total_tracks ] DT.Acc.perDCnP[, Revenue_share.effective := Payable /(Net_revenue * Pro_rata_share.exact )*100 ] ###### ---------------- CURRENCY CONVERSION RATE ---------------- ###### ## Back-out to the currency conversion rate DT.CurrConv <- DT.Acc.perDCnP[!is.na(Payable_USD) & !(Payable==0), list(ConvRateUsed = Payable_USD / Payable), keyby=c("Currency","Month")] ## Average the values out, confirming that there are no drastic differences DT.CurrConv[, ConvRate := mean(ConvRateUsed), by=c("Currency","Month")] ## Doublecheck... if (!(DT.CurrConv[, mean(abs(ConvRate - ConvRateUsed))] < .0001)) stop("conversion Rate is not accurate") DT.CurrConv <- unique(DT.CurrConv, by=c("Currency", "Month", "ConvRate")) DT.CurrConv[, ConvRateUsed := NULL] ## We dont have all the needed conversion rates for all of the currencies DT.CurrConv <- merge(DT.CurrConv, setkey(DT.Acc.perDCnP[is.na(Payable_USD), key(DT.CurrConv), with=FALSE]), all=TRUE ) DT.CurrConv <- unique(DT.CurrConv, by=NULL) ## NA currencies are useless DT.CurrConv <- DT.CurrConv[!is.na(Currency)] ## Remove NAs that are of the same month. Not sure how they got in there DT.CurrConv[, ConvRate := {mean(ConvRate, na.rm=sum(!is.na(ConvRate)))}, by=key(DT.CurrConv)] ## Fill in the NA's DT.CurrConv[, ConvRate := zoo::na.locf(ConvRate, fromLast=TRUE), by=c("Currency")] ## Confrim there are no NA's if (nrow(DT.CurrConv[is.na(ConvRate)])) warning ("There are NA Currencies") # ## Need to check if any months are missing for a given currency # .... actually. This won't be needed for now # DT.CurrConv[, lunique(Month), by=Currency] ###### ---------------- CURRENCY CONVERSION RATE ---------------- ###### ###### ---------------- CREATE Payable_Using COLUMN ---------------- ###### ## SET Payable_Using to Payable_USD wheneve it is availble ## otherwise, convert Payable~Currency using DT.CurrConv ## Use Payable_USD whenever available DT.Acc.perDCnP[, Payable_Using := Payable_USD] ## Backup the current key key_bak.DT.Acc.perDCnP <- key(DT.Acc.perDCnP) ## Setkeys for JOIN setkeyIfNot(DT.Acc.perDCnP, key(DT.CurrConv)) ## Join and any NA's convert using ConvRate DT.Acc.perDCnP[DT.CurrConv, Payable_Using := ifelse(is.na(Payable_Using), Payable * ConvRate, Payable_Using)] if (nrow(DT.Acc.perDCnP[is.na(Payable_Using)])) stop("There are NAs in DT.Acc.perDCnP$Payable_Using") ###### ---------------- CREATE Payable_Using COLUMN ---------------- ###### } ## I dont rememver what I was doing here. DT.pay <- DT.Acc.perDCnP[, unlist(cols.acc[c("head", "reven", "pay")]), with=FALSE] DT.pay[, pror_payable := Payable * Pro_rata_share / 100] DT.pay # Country Month Report_start_date Report_end_date Partner Product_code Currency Pro_rata_share Months_committed_but_not_activated Fee_per_month Payable_type Payable Payable_EUR Total_tracks # 1: US 2013-10-01 2013-10-01 2013-10-01 NA U USD 12.03 NA NA NA 23895.70 17591.06 35394256 # 2: US 2013-10-01 2013-10-01 2013-10-01 NA PD USD 7.23 NA NA NA 9317.79 6859.39 15335760 # 3: US 2013-10-01 2013-10-01 2013-10-01 NA A USD 7.49 NA NA NA 166532.30 122594.45 1431214751 # 4: US 2013-10-01 2013-10-01 2013-10-01 NA P USD 8.17 NA NA NA 1081987.28 796515.96 1492659264 # Rightholders_tracks Revenue_share Total_bundles Net_revenue Registered_users Active_users New_Product_code_users Gross_revenue Payable_USD # 1: 4259389 60 NA 330944 64508 64508 0 330944 23895.70 # 2: 1109522 60 NA 214650 21038 21038 0 214650 9317.79 # 3: 107222988 60 NA 3704794 50786497 50786497 0 3719577 166532.30 # 4: 122009580 60 NA 22061633 2153355 2153355 0 22061633 1081987.28 # >