## apple music membership report 20160430.r

setScience("AppleMusic", subProj="Membership")

f.in <- ingest.p("apple_music_membership_report_20160430.csv")

## This item in the report is the line break between the three reports included
report_line_break <- ",,,,,,,"

## READ IN THE RAW DATA
raw <- readLines(f.in)
number_of_expected_reports <- 3

## remove blank lines
raw <- raw[raw != ""]

line_breaks <- grep(report_line_break, raw)
## exactly two linebreaks should exist
stopifnot(length(line_breaks) == number_of_expected_reports - 1)

starts <- c(1, line_breaks + 1)
ends <- c(line_breaks - 1, length(raw))

ll_reports <- emptylist(seq.int(number_of_expected_reports))
for (i in seq.int(number_of_expected_reports)) {
  ll_reports[[i]] <- raw[starts[[i]] : ends[[i]] ]

  ## remove trailing commas for just the headers
  if (i %in% 1:2)
    ll_reports[[i]] %<>% removeText(pat=",+$")

  if (i == 1)
    ll_reports[[i]] %<>% strsplit(",") %>% do.call(cbind, .) %>% apply(1, pasteC, C=",")
  if (i == 2)
    ll_reports[[i]][[1]] %<>% gsub("^\\s*,", "Product,", .)
  if (i == 3)
    ll_reports[[i]][[1]] %<>% gsub("^\\s*,", "Country_Name,", .)
}

## CONVERT TO DATA.TABLES
raw_to_DT <- function(ll) {
  ll %>% pasteC(C="\n") %>% 
          read.csv(text=.) %>% 
          as.data.table %>%
          cleanColNamesForSQL_
}

## Parse out each set to a separate data.table
DT.header_1 <- ll_reports[[1]] %>% raw_to_DT
DT.header_2 <- ll_reports[[2]] %>% raw_to_DT
DT.main     <- ll_reports[[3]] %>% raw_to_DT

## for DT.main
cols_to_conver <- setdiff(names(DT.main), "country_name")
DT.main[, (cols_to_conver) := lapply(.SD, commaToNumeric), .SDcols = cols_to_conver]

DT.main[country_name == "Worldwide", lapply(.SD, sumn), .SDcols = cols_to_conver]
DT.main[country_name != "Worldwide", lapply(.SD, sumn), .SDcols = cols_to_conver]