lib(ggplot2)

setScience("misc", subProj="source_andi")
cluster <- 7

Qry.streams_by_source <- "
-- SELECT tmstamp::Date as date, to_char(tmstamp, 'HH24') as hour, source
SELECT tmstamp, source
      , count(*) AS streams
FROM staging_raw_spotify_V2 
WHERE tmstamp >= '2015-01-05 00:00:00'
  AND tmstamp < '2015-07-12 00:00:00'
  AND user_country = 'US'
GROUP BY 1, 2
"

## Pull the raw data
DT.usa_streams <- runQry(Qry.streams_by_source, cluster=4)

## Set the key, in R.
setkey(DT.usa_streams, date, hour)

## Save our work (The data is already "grouped by". we want to come back to it)
jesusForData(DT.usa_streams, info="Jan052015toJul122015")

DT.usa_streams[, tmstamp]

DT.usa_streams[, perc.streams_per_day := streams / sum(streams), by=date]
DT.usa_streams[, source := factor(source)]


DT.usa_streams.source2 <- DT.usa_streams[source %in% c("album", "others_playlist")]
addWeekday_(DT.usa_streams.source2, start=1)

## Add the ratio
DT.usa_streams.source2[, ratio_of_streams := 
        streams[source == "album"] / 
        streams[source == "others_playlist"]
, by=date]

## Add Midweek date to source2
DT.usa_streams.source2[, date_midweek := date[(as.numeric(date) %% 7) == 3], by=list(as.numeric(date) %/% 7)]

## Aggregate total streams, by week and source
DT.usa_streams.source_weekly <- DT.usa_streams.source2[, list(weekly_streams =  sum(streams)), keyby=list(date_midweek, source)]

## Add the ratio, again
DT.usa_streams.source_weekly[, ratio_of_streams := 
        weekly_streams[source == "album"] / 
        weekly_streams[source == "others_playlist"]
, by=list(date_midweek)]


{
  P.barchart_album_to_others_playlists <- ggplot(data = DT.usa_streams[source %in% c("album", "others_playlist") ][order(source)], aes(x=date, y=perc.streams_per_day, fill=source)) + 
    geom_bar(stat="identity", position="stack") + ggtitle("Album and Others_Playlist Streams\nJan to June 2015 all USA Spotify Streams")

  DT.plot2 <- DT.usa_streams.source2[source %in% c("album", "others_playlist") ][order(source)]

  P.album_to_others_playlists <- 

  ggplot(data = DT.plot2, aes(x=date)) + 
    geom_line(aes(y=ratio_of_streams), alpha=.8) + geom_point(aes(y=ratio_of_streams), alpha=.5, size=.5) + ggtitle("Ratio of Album to Others_Playlist Streams\nJan to June 2015 all USA Spotify Streams") + 

    geom_line(data=DT.usa_streams.source_weekly, aes(x=date_midweek, y=ratio_of_streams), color="blue", size=1.2) + 
    geom_point(data=DT.usa_streams.source_weekly, aes(x=date_midweek, y=ratio_of_streams), color="red", size=1.8)

  ggsave.out(P.barchart_album_to_others_playlists, open=TRUE)
  ggsave.out(P.album_to_others_playlists, open=TRUE)
}


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

DT.streams_per_wday <- DT.usa_streams.source2[, list(total_daily_streams = sum(streams), avg_daily_streams = mean(streams)), keyby=list(source, weekday)]
# DT.streams_per_wday[, daily_perc_of_total_for_source := sum(daily_streams) / ]

ggplot(DT.streams_per_wday, aes(x=weekday, y=daily_streams, fill=source)) + geom_bar(position="dodge", stat="identity")

library(reshape2)
DT.streams_per_wday.molten <- melt.data.table(DT.streams_per_wday, id.vars=c("source", "weekday"), value.name="streams", variable.name="measure")

ggplot(DT.streams_per_wday.molten, aes(x=weekday, y=streams, fill=source)) + geom_bar(position="dodge", stat="identity") + facet_grid(measure ~. , scale="free")

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

DT.streams_per_hour <- DT.usa_streams.source2[, list(avg_hourly_streams = mean(streams)), keyby=list(source, weekday, hour)]
# DT.streams_per_wday[, daily_perc_of_total_for_source := sum(daily_streams) / ]

ggplot(DT.streams_per_hour, aes(x="1", y=hour, fill=avg_hourly_streams)) + geom_tile(stat="identity") + facet_grid(weekday ~ source)

library(reshape2)
DT.streams_per_wday.molten <- melt.data.table(DT.streams_per_wday, id.vars=c("source", "weekday"), value.name="streams", variable.name="measure")

ggplot(DT.streams_per_wday.molten, aes(x=weekday, y=streams, fill=source)) + geom_bar(position="dodge", stat="identity") + facet_grid(measure ~. , scale="free")

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