Spain Playlist Analysis.r 


# setGitBranchToSystem()
# .g()

setScience("Spotify_Stream_Count", subProj="SpainPlaylists", create=FALSE, load=FALSE, subl=FALSE)
sourceSupportFns(srcOther("Spotify Playlist Webscrape"), fail.on.missing=TRUE)

options("snowflake_inuse" = TRUE)

if (FALSE)
sfWarehouseOn("SCIENCE", size="m")


uri.electro_latino <- "spotify:user:114125244:playlist:6GsmUPGe7kK5KyhAOLGLBf"
# tbl.streams <- "STREAMCOUNTS_BY_SOURCE_USER_TRACK"

## SNOWFLAKE -- but not setup yet
if (FALSE)
{
  tbl.streams <- "user_timestamped_streams"
  schema <- "spotify"


  runQry
  headDB(tbl=tbl.streams, schema=schema)

  dateCol <- "timestamp_utc"
  minDate <- monthFloor(today()-2)
  whereIn=list(sprintf("%s >= '%s'", dateCol, minDate), source_uri=c(uri.electro_latino))
  # colsToAgg=c("total_streams", )


  makeQry(tbl=tbl.streams, schema=schema, dateCol=dateCol, aggFunc="max", whereIn=whereIn) %>% sfQry
  makeQry(tbl=tbl.streams, schema=schema, dateCol=dateCol, aggFunc="max") %>% sfQry
}


## REDSHIFT
options("snowflake_inuse" = FALSE)
cluster <- 4

tbl.streams <- "staging_raw_spotify_v2"
schema <- "production"

headDB(tbl.streams, schema=schema, cluster=cluster)
dateCol <- "tmstamp"
minDate <- monthFloor(today()-2)
whereIn=list(sprintf("%s >= '%s'", dateCol, minDate), source_uri=c(uri.electro_latino))
whereIn=list(source_uri=c(uri.electro_latino))

## Show the max date in the table
maxDateInTable <- makeQry(tbl=tbl.streams, schema=schema, dateCol=dateCol, aggFunc="max") %>% runQry
cat("the maxDateInTable is ", clean.capture.output(maxDateInTable[[1]]), "\n")

colsToAgg <- c()
colsToPull <- c("user_id", "tmstamp", "track_uri", "source", "source_uri", "length", "device_type", "os", "product", "user_country", "user_access", "gender", "user_type")

{
DT.electro_latino <- makeQry(tbl=tbl.streams, schema=schema, dateCol=dateCol, colsToPull=colsToPull, aggFunc="count", colsToAgg=c(streams="1"), whereIn=whereIn, minDate=minDate) %>%
                      runQry(cluster=cluster, allow.large.groupby=TRUE)
print(head(DT.electro_latino))
jesusForData(DT.electro_latino)
}

DT.electro_latino_meta <- get_playlist_data(uri=uri.electro_latino, jesus=FALSE, gitcommit=FALSE) %>% setnames_removePreface("playlist")
DT.electro_latino_meta[, track.datetimeadded := as.POSIXct(track.datetimeadded, format="%Y-%m-%dT%H:%M:%SZ")]

DT.electro_latino_meta[, range(track.datetimeadded)] %>% as.Date
DT.electro_latino_meta[, unique(as.Date(track.datetimeadded)), keyby=list(track.name, track.uri)][, .N, keyby=V1]