# DT.Total_usage
lib(reshape2)
lib(ggplot2)


## ---- These are the DTs needed --------- ##
loadIfNotExists("DT.Total_usage")

# -------------------------------
# OLD : kCols.DUS <- c("month", "Units", "CutOff")
# OLD : kCols.DCgUS <- c("month", "region_group", "Units", "CutOff")
# OLD : kCols.DOUS <- c("month", "Orchard_vs_Spotify", "Units", "CutOff")
# OLD : kCols.ts <- c("region_group", "CutOff", "Units")
# OLD : kCols.US <- c("Units", "CutOff")

## NEW kCols
kCols.DS   <- c("month",          "StreamType")
kCols.US   <- c(         "Units", "StreamType")
kCols.DUS  <- c("month", "Units", "StreamType")
kCols.DO   <- c("month", "Orchard_vs_Spotify" )
kCols.DOC   <- c("month", "Orchard_vs_Spotify", "region_group")
kCols.DOUS  <- c("month", "Orchard_vs_Spotify", "Units", "StreamType")
kCols.DCgUS <- c("month", "region_group", "Units", "StreamType")

OrchSpotColors <- c(Orchard="#EB7028", Spotify="#007A40")

&& REMINDER TO RICK :: the EU version needs region_group

## MELTED DATA (reshaped in the future)
numericCols <- nwhich(canBeNumeric(DT.Total_usage))
DT.Total_usage[, (numericCols) := lapply(.SD, as.numeric), .SDcols = numericCols]
DT.Total_usage.melt <- melt(DT.Total_usage, id.vars=c("month", "region_group"), value.name="count")

## Shorthand 
sDT <- DT.Total_usage.melt

## Add in region_group
DT.EU <- get_DT.EU()
stopifnot(DT.EU$country_code %in% sDT$country_code)
addColsFrom_(sDT, DT.EU, joinCols.g="country_code", joinCols.r="country_code")

## Splice out the column types from the original, unmelted column name
sDT[, Orchard_vs_Spotify := ifelse(grepl("orchard", variable), "Orchard", "Spotify")]
sDT[, Units := ifelse(grepl("minutes", variable), "Minutes", "Streams")]
sDT[Units == "Streams", CutOff := ifelse(grepl("15", variable), 15,  ifelse(grepl("30", variable), 30, 0))]

## Calculate the ABSOLUTE stream count (whereas, the orignal had cumsum counts)
byCols.abs <- c("month", "region_group", "Units", "Orchard_vs_Spotify")
sDT[Units != "Minutes", absolute_count := c(-diff(count), count[CutOff == 30]), by=byCols.abs]
# sDT[Units != "Minutes", absolute_count := {
#   c(
#     t.00_to_15 = count[CutOff == 0] - count[CutOff == 15] # The latter value includes 30_plus
#   , t.15_to_30 = count[CutOff == 15] - count[CutOff == 30]
#   , t.30_plus  = count[CutOff == 30] - 0 # no change
# )} , by=byCols.abs]
## CONFIRM
stopifnot(sDT[Units != "Minutes", absolute_count[CutOff == 0] + count[CutOff == 15] == count[CutOff == 0], by=byCols.abs][, V1])
stopifnot(sDT[Units != "Minutes", absolute_count[CutOff == 15] + absolute_count[CutOff == 30] == count[CutOff == 15], by=byCols.abs][, V1])
stopifnot(sDT[Units != "Minutes", sum(absolute_count) == count[CutOff==0], by=byCols.abs][, V1])

sDT[CutOff == 00, StreamType := "Streams 00-15 Secs"]
sDT[CutOff == 15, StreamType := "Streams 15-30 Secs"]
sDT[CutOff == 30, StreamType := "Streams 30+   Secs"]

sDT[Units == "Minutes", absolute_count := count]
sDT[Units == "Minutes", StreamType := "Total Mins Streamed"]

## Remove shorthand 
{
  if (identical(DT.Total_usage.melt, sDT))
    rm (sDT)
  else 
    stop ("DT.Total_usage.melt is NOT identical to sDT")
}

## Add in "Total Streams" 
if ("TOTAL STREAMS 0+ Secs" %ni% DT.Total_usage.melt$StreamType) {
  DT.Total_usage.melt <- 
    rbind(  DT.Total_usage.melt
          , DT.Total_usage.melt[Units != "Minutes", list(StreamType = "TOTAL STREAMS 0+ Secs", CutOff = (-1), absolute_count = count[CutOff==0]), keyby=c(setdiff(names(DT.Total_usage.melt), c("StreamType", "CutOff", "count", "variable", "absolute_count")))]
          , fill = TRUE)
}



## 0 Seconds vs 15 seconds vs 30 Seconds
DT.Total_usage.melt.aggdByC <- sumTheTable(DT.Total_usage.melt, byCols=c(kCols.DOUS, "CutOff"), colsUsing="absolute_count")
DT.Total_usage.melt.aggdByC[, What := ifelse(grepl("Total", StreamType, ignore.case=TRUE), "Total", "Absolute Counts")]
DT.Total_usage.melt.aggdByC[, Units := factor(Units, levels=c("Streams", "Minutes"))]
# DT.Total_usage.melt.aggdByC[, Counting := ifelse(CutOff == 0, "00 ~ 15 Seconds [Unpaid]", ifelse(CutOff == 15, " ~ 15 Seconds [Unpaid]",  )
P <- 
{
  ggplot(DT.Total_usage.melt.aggdByC, aes(x=month, y=absolute_count, color=StreamType)) +
    geom_line(aes(linetype=Units), size=1.4) + 
    facet_grid(What + Orchard_vs_Spotify ~ ., scale="free_y") + 
    millions.y() + ylab("Count in Millions") + 
    ggtitle("Usage by Month (Minutes or Streams) -- Orchard vs Spotify")
}

P2 <- colorByGroup(P, OrchSpotColors, showWarnings=FALSE)
plot(P2)


-------------------------------
DT.ratio_counts2 <-DT.Total_usage[, list(Orchard_Ratio_00To30 = orchard_streams_above_30_sec / orchard_streams
                                              , Spotify_Ratio_00To30 = all_streams_above_30_sec / all_streams)
                                      , keyby=list(month, region_group)]
DT.ratio_counts2 <- melt(DT.ratio_counts2, id.vars=key(DT.ratio_counts2))
DT.ratio_counts2[, Orchard_vs_Spotify := ifelse(grepl("Orchard", variable), "Orchard", "Spotify")]
DT.ratio_counts2[, variable := gsub(".*\\.", "", variable)]
setnames(DT.ratio_counts2, "variable", "RatioType")
setnames(DT.ratio_counts2, "value", "Value")
matchKey(DT.ratio_counts, DT.ratio_counts2, kCols.DOC, organize=TRUE)
identical(DT.ratio_counts[RatioType == "Ratio_00To30"]$Value, DT.ratio_counts2$Value)


# upside down -- ### Question 1:  Is the ratio (00-15) / (30+) Constant? 
# upside down -- DT.ratio_counts <- {
# upside down --           DT.Total_usage.melt[!is.na(CutOff), {
# upside down --                ret <- c( Ratio_00To30 = count[CutOff==00] / count[CutOff==30]
# upside down --                        , Ratio_15To30 = count[CutOff==15] / count[CutOff==30]
# upside down --                        , Ratio_00To15 = count[CutOff==00] / count[CutOff==15])
# upside down --                list(RatioType=names(ret), Value=ret)
# upside down --              } , keyby=kCols.DOC]
# upside down -- }

### Question 1:  Is the ratio (00-15) / (30+) Constant? 
DT.ratio_counts <- {
          DT.Total_usage.melt[!is.na(CutOff), {
               ret <- c( Ratio_00To30 = count[CutOff==30] / count[CutOff==00]
                       , Ratio_15To30 = count[CutOff==30] / count[CutOff==15]
                       , Ratio_00To15 = count[CutOff==15] / count[CutOff==00])
               list(RatioType=names(ret), Value=ret)
             } , keyby=kCols.DOC]
}

DT.ratio_counts[, mm := factor(month(month), levels=1:12, labels=month.abb)]
DT.ratio_counts[, mgroup := ifelse(month <= "2014-05-31", "Feb-May", sprintf("Jun-%s", mm[month==max(month)][[1]]))]
RatioType.using <- "Ratio_00To15"

tmp.avgs <- DT.ratio_counts[, median(Value), by=list(Orchard_vs_Spotify, mgroup)]
tmp.avgs[, c("xmin", "xmax") := as.data.table(do.call(rbind, strsplit(mgroup, "-")))]


setkeyIfNot(DT.ratio_counts, "Orchard_vs_Spotify", "RatioType", verbose=FALSE)
TITLE <-  subtitle("                                STREAM CONVERSION\n from unpaid stream to paid stream  (ie, longer than 30 seconds)", "NOTE: Orchard includes RED")
ylab  <- "Ratio of   [# of streams lasting longer than 30 Seconds] /\n        [# of streamss initiated]\n"
xlab  <- "Month (2014)\n(horizontal line represents average for the group)"
P.stream_conversion <- 
{
  ## Both Increased, but Spotify Increased more
  ggplot() + 
    geom_boxplot(data=DT.ratio_counts[.("Orchard", RatioType.using)], aes(x=mm, y=Value, fill=Orchard_vs_Spotify, color=Orchard_vs_Spotify), alpha=.40, notch=TRUE) + 
    geom_boxplot(data=DT.ratio_counts[.("Spotify", RatioType.using)], aes(x=mm, y=Value, fill=Orchard_vs_Spotify, color=Orchard_vs_Spotify), alpha=.40, notch=TRUE)  + 
    labs(title=TITLE, y=ylab, x=xlab)  +

    # geom_point(data=DT.ratio_counts[RatioType == RatioType.using & (Value > .79 | Value < .71)], aes(x=mm, y=Value, color=Orchard_vs_Spotify)) + 
    # geom_point(data=DT.ratio_counts[RatioType == RatioType.using & (Value > .79 | Value < .71)], aes(x=mm, y=Value, color=Orchard_vs_Spotify)) + 
    geom_point(data=DT.ratio_counts[RatioType == RatioType.using][, list(Value, quantile(Value, probs=.75) + 1.5 * IQR(Value)), keyby=c("mm", "Orchard_vs_Spotify")][Value > V2], aes(x=mm, y=Value, color=Orchard_vs_Spotify)) + 
    geom_point(data=DT.ratio_counts[RatioType == RatioType.using][, list(Value, quantile(Value, probs=.25) - 1.5 * IQR(Value)), keyby=c("mm", "Orchard_vs_Spotify")][Value < V2], aes(x=mm, y=Value, color=Orchard_vs_Spotify)) + 

    geom_segment(data=tmp.avgs, aes(x = xmin, xend=xmax, y = V1, yend=V1), color="black", size=1.5, alpha=.5) +
    geom_segment(data=tmp.avgs, aes(x = xmin, xend=xmax, y = V1, yend=V1, color=Orchard_vs_Spotify), size=1, alpha=1)
}

ggsave.out(P.stream_conversion, open=TRUE)

#   facet_grid(Orchard_vs_Spotify ~ RatioType, scales="free")



## Average for Feb - May
## Average for Jun - Sep
## FACET

  # facet_grid(Orchard_vs_Spotify ~ RatioType, scales="free")


debugonce(showkcols)


-------------------------------
### -------- PAID VS UNPAID -------------------- ###
## START BY dcasting
DT.Total_usage.dcast <- dcast.data.table(DT.Total_usage.melt[TRUE][,StreamType := ifelse(grepl("15", StreamType), "Streams 00-30 Secs", StreamType)], month + Units + StreamType ~ Orchard_vs_Spotify, value.var="absolute_count", fun.aggregate=sum)
DT.Total_usage.dcast <- DT.Total_usage.dcast[!grepl("Total Mins", StreamType)]
DT.Total_usage.dcast[, StreamType := ifelse(grepl("00", StreamType), "Unpaid Streams", ifelse(grepl("TOTAL", StreamType), "TOTAL Streams", "Paid Streams"))]
DT.Total_usage.dcast[, StreamType := factor(StreamType, levels=c("Unpaid Streams", "Paid Streams", "TOTAL Streams"))]
setkeyIfNot(DT.Total_usage.dcast, kCols.DUS, verbose=FALSE, organize=TRUE)

## Add ratio column
DT.Total_usage.dcast[, orch_to_spot_ratio := Orchard / Spotify]

### -------------------
TITLE <- "Number of Stream (or Total Minutes)\nComparing Orchard to Spotify, by Month and Stream cut-off" 
P.orchard_to_spotify_ratio_paid_unpaid <-  
ggplot(DT.Total_usage.dcast, aes(x=month, y=orch_to_spot_ratio, color=StreamType)) + geom_line(size=1, aes(linetype=StreamType)) + geom_point(alpha=.3, size=2.5) + facet_grid(Units ~ ., scale="free_y") + percent.y() + labs(y="Ratio of Orchard to Spotify, by group\n", title=subtitle("Orchard Streams as a Percentage of Spotify Streams", "NOTE: Orchard includes RED")) + scale_linetype_manual(values=c(1, 1, 2))
ggsave.out(P.orchard_to_spotify_ratio_paid_unpaid, open=TRUE)
### -----------------------------------------------------------
-------------------------------


## START BY dcasting
DT.Total_usage.dcast <- dcast.data.table(DT.Total_usage.melt, month + Units + StreamType ~ Orchard_vs_Spotify, value.var="absolute_count", fun.aggregate=sum)

setkeyIfNot(DT.Total_usage.dcast, kCols.DUS, verbose=FALSE, organize=TRUE)

## Add ratio column
DT.Total_usage.dcast[, orch_to_spot_ratio := Orchard / Spotify]
DT.Total_usage.dcast[, DELTA_orch_to_spot_ratio := diffNA(orch_to_spot_ratio), by=kCols.US]

## Percent of increase
DT.Total_usage.dcast[, Orch.perc_change := percentIncrease(Orchard), by=kCols.US]
DT.Total_usage.dcast[, Spot.perc_change := percentIncrease(Spotify), by=kCols.US]
DT.Total_usage.dcast[, DELTA_Orch.perc_change := diffNA(Orch.perc_change), by=kCols.US]
DT.Total_usage.dcast[, DELTA_Spot.perc_change := diffNA(Spot.perc_change), by=kCols.US]

## SEE 
DT.Total_usage.dcast[.(as.Date(c("2014-04-01", "2014-05-01")))][order(StreamType)]
DT.Total_usage.dcast[.(as.Date(c("2014-04-01", "2014-05-01")))][order(StreamType)]


TITLE <- "Number of Stream (or Total Minutes)\nComparing Orchard to Spotify, by Month and Stream cut-off" 
## OLD
ggplot(DT.Total_usage.dcast, aes(x=month, y=orch_to_spot_ratio, color=StreamType)) + geom_line(size=1) + geom_point(alpha=.3, size=2.5) + facet_grid(Units ~ ., scale="free_y") + percent.y()

## same, no minutes
### -------------------
P.orchard_to_spotify_ratio <-  ggplot(DT.Total_usage.dcast[Units != "Minutes"], aes(x=month, y=orch_to_spot_ratio, color=StreamType)) + geom_line(size=1) + geom_point(alpha=.3, size=2.5) + facet_grid(Units ~ ., scale="free_y") + percent.y() + labs(y="Ratio of Orchard to Spotify, by group\n", title=subtitle("Orchard Streams as a Percentage of Spotify Streams", "NOTE: Orchard includes RED"))
ggsave.out(P.orchard_to_spotify_ratio)
### -------------------


ggplot(DT.Total_usage.dcast[Units != "Minutes"], aes(x=month, y=DELTA_orch_to_spot_ratio, color=StreamType)) + geom_line(size=1) + geom_point(alpha=.3, size=2.5) + percent.y() + geom_hline(y=0, color="white", linetype=1, size=1.2, alpha=.8) + geom_hline(y=0, color="black", size=.75, linetype=4, alpha=.6)

## NEW 20141020
ggplot(DT.Total_usage.dcast, aes(x=month, y=orch_to_spot_ratio, color=StreamType)) + geom_line(size=1) + geom_point(alpha=.3, size=2.5) + percent.y() + labs(title=TITLE, y="Orchard as a percent of Spotify")

ggplot(DT.Total_usage.dcast, aes(x=month, y=DELTA_orch_to_spot_ratio, color=StreamType)) + geom_line(size=1) + geom_point(alpha=.3, size=2.5) + percent.y() + labs(title=paste0("CHANGE IN ", TITLE), y="CHANGE IN Orchard as a percent of Spotify")

ggplot(DT.Total_usage.dcast, aes(x=month, y=DELTA_Orch.perc_change, color=StreamType)) + geom_line(size=1) + geom_point(alpha=.3, size=2.5) + percent.y() + labs(title=paste0("CHANGE IN ", TITLE), y="CHANGE IN Orchard as a percent of Spotify")
dev.new()

---------------------------------------
## Same as above but with region_group added
## START BY dcasting
DT.Total_usage.dcast <- dcast.data.table(DT.Total_usage.melt, month + region_group + Units + CutOff ~ Orchard_vs_Spotify, value.var="count", fun.aggregate=sum)

setkeyIfNot(DT.Total_usage.dcast, kCols.DCgUS, verbose=FALSE, organize=TRUE)

## Add ratio column
DT.Total_usage.dcast[, orch_to_spot_ratio := Orchard / Spotify]

## SEE 
DT.Total_usage.dcast[.(as.Date("2014-07-01"), "US")]

## Scale Uniform to [0, 1]
# DT.Total_usage.dcast[, Orch.scaled_01 := scaleunif(Orchard), by=kCols.ts]
# DT.Total_usage.dcast[, Spot.scaled_01 := scaleunif(Spotify), by=kCols.ts]

## Scale to SD
DT.Total_usage.dcast[, Orch.scaled := scale(Orchard), by=kCols.ts]
DT.Total_usage.dcast[, Spot.scaled := scale(Spotify), by=kCols.ts]

## Percent of increase
DT.Total_usage.dcast[, Orch.perc_change := percentIncrease(Orchard), by=kCols.ts]
DT.Total_usage.dcast[, Spot.perc_change := percentIncrease(Spotify), by=kCols.ts]

## SEE 
DT.Total_usage.dcast[.(as.Date(c("2014-04-01", "2014-05-01")), "US")][order(CutOff)]
DT.Total_usage.dcast[.(as.Date(c("2014-04-01", "2014-05-01")), "UY")][order(CutOff)]


ggplot(DT.Total_usage.dcast, aes(x=month, y=orch_to_spot_ratio, color=factor(CutOff))) + geom_line() + facet_grid(Units ~ ., scale="free_y") 

---------------------------------------
kCols.region_group <- gsub("^region_group$", "region_group", kCols.DCgUS)
DT.plot.agg <- melt(DT.Total_usage.dcast[!is.na(CutOff) & CutOff == 30], id.vars=kCols.DCgUS)[, variable := as.character(variable)]
DT.plot.agg[, Orchard_vs_Spotify := ifelse(grepl("orch", as.character(variable), ignore.case=TRUE), "Orchard", "Spotify")]
addColsFrom_(DT.plot.agg, DT.country, joinCols="region_group")
DT.plot.agg <- DT.plot.agg[region_group != "Canada"]
DT.plot.agg <- DT.plot.agg[grepl("perc_change$", variable), lapply(.SD, sumn), keyby=c(kCols.region_group, "Orchard_vs_Spotify"), .SDcols="value"]

DT.plot.agg[.(as.Date("2014-04-01"))]

y.var <- "value"
ggplot(data = DT.plot.agg[month > min(month)], aes_string(x="month", y=y.var, color="region_group", linetype="Orchard_vs_Spotify")) + geom_line() + geom_point(alpha=.5) +  ylab(paste0(gsub("_", " ", y.var), " (percent)"))

+ facet_grid(type_of_trial ~ ., scales="free_y") 
---------------------------------------

DT.plot.agg <- melt(DT.Total_usage.dcast[!is.na(CutOff) & CutOff == 30], id.vars=kCols.DCgUS)[, variable := as.character(variable)]
DT.plot.agg[, Orchard_vs_Spotify := ifelse(grepl("orch", as.character(variable), ignore.case=TRUE), "Orchard", "Spotify")]
addColsFrom_(DT.plot.agg, DT.country, joinCols="region_group")

y.var <- "value"
ggplot(data = DT.plot.agg[grepl("scaled_01", variable)], aes_string(x="month", y=y.var, color="region_group", linetype="Orchard_vs_Spotify")) + geom_line() + geom_point(alpha=.5) +  ylab(paste0(gsub("_", " ", y.var), " (percent)")) 

+ facet_grid(type_of_trial ~ ., scales="free_y") 
---------------------------------------


whose <- c(orchard="orchard_streams", all="all_streams")
length <- c(touched="", short="_above_15_sec", full="_above_30_sec")

## ------------------------------------ ##
##      STILL TODO                      ## 
## ------------------------------------ ##
    for (l in length)
      DT.Total_usage[, paste0("orch_to_all_ratio", l) := get(paste0("orchard_streams", l)) / get(paste0("all_streams", l))]
    DT.Total_usage[, orch_to_all_ratio_minutes := orchard_minutes_streamed / minutes_streamed]
    ##
    DT.Total_usage[, perc.songs_skipped := 1 - all_streams_above_30_sec/all_streams]
    DT.Total_usage[, perc.orchard_songs_skipped := 1 - orchard_streams_above_30_sec/orchard_streams]
## ------------------------------------

addColsFrom_(DT.Total_usage, DT.country, joinCols="region_group")

------------------------------------------------


plotSpotifyData <- function(DT, y.vars=c("perc.songs_skipped"))
## Look at USA vs ROW
DT.plot <- copy(DT.Trial_uptake)
y.var = "No._of_trials_starting_during_the_month"

addColsFrom_(DT.plot, DT.country, joinCols="region_group")

byCols <- c(setdiff(key(DT.plot), "region_group"), "region_group")
DT.plot[, region_group := NULL]


## Aggregate
DT.plot.agg <- DT.plot[, lapply(.SD, sumn), keyby=byCols]
## Drop any type that only has one or two data points
DT.plot.agg <- DT.plot.agg[type_of_trial %in% DT.plot.agg[, .N, by=type_of_trial][N > 2, type_of_trial]]

lib(ggplot2)
ggplot(data = DT.plot.agg, aes_string(x="month", y=y.var, color="region_group")) + geom_line() + facet_grid(type_of_trial ~ ., scales="free_y") + millions.y() + ylab(paste0(gsub("_", " ", y.var), " (in millions)"))
----------------------------