graphTV/app.R

library(tidyverse)
library(plotly)

ui <- fluidPage(
  inputPanel(width = "20%", height = "100%",
             textInput("show_search", "Search show"),
             uiOutput("show_choice")
             ),
  plotlyOutput("show_graph", width = "100%", height = "800px")
)

server <- function(input, output, session)
{
  shows <- eventReactive(input$show_search, {

                           if(str_length(input$show_search) > 3)
                           {
                           src_sqlite("im.db") %>%
                             tbl(sql(str_c("select tconst, primaryTitle from basics where titleType == 'tvSeries' and primaryTitle like '%", input$show_search, "%'"))) %>%
                             collect -> res

                           res$tconst %>%
                             as.list %>%
                             setNames(res$primaryTitle)
                           }
  })

  output$show_choice <- renderUI({
    req(shows())

    selectizeInput("show", "Please choose…", shows())
  })

  output$show_graph <- renderPlotly({
    req(input$show)

    show_id <- input$show

    src_sqlite("im.db") %>%
      tbl("episodes") %>%
      filter(parentTconst == show_id) %>%
      select(-parentTconst) %>%
      collect -> episodes

    src_sqlite("im.db") %>%
      tbl("basics") %>%
      filter(tconst %in% episodes$tconst) %>%
      collect -> titles

    src_sqlite("im.db") %>%
      tbl("ratings") %>%
      filter(tconst %in% episodes$tconst) %>%
      collect -> ratings

    episodes %>%
      left_join(titles) %>%
      left_join(ratings) %>%
      select(-tconst, -titleType) %>%
      mutate_at(vars(seasonNumber, episodeNumber, averageRating, numVotes), as.numeric) %>%
      filter(!is.na(numVotes)) -> eps

    eps %>%
      by(.$seasonNumber, function(x){
           fit <- lm(averageRating ~ episodeNumber, data = x)
           x %>%
             plot_ly(hoverinfo = "text",
                     text = ~str_c("S", seasonNumber, "E", episodeNumber, " - ", primaryTitle, "<br>",
                                   "Rating: ", averageRating, " (", numVotes, " votes)<br>")) %>%
           add_trace(data = x, x = ~episodeNumber, y = ~averageRating, mode = "markers") %>%
           add_trace(data = x, x = ~episodeNumber, y = fitted(fit), mode = "lines") %>%
           layout(xaxis = list(tick0 = 0, dtick = 1),
                  yaxis = list(title = "Rating"))
          }) %>%
    subplot(shareY = T, nrows = 1, margin = 0) %>%
    hide_legend

    # eps %>%
    #   arrange(seasonNumber, episodeNumber) %>%
    #   add_rownames(var = "ep") %>%
    #   mutate(ep = ep %>% as.numeric, seasonNumber = seasonNumber %>% as.factor) %>%
    #   ggplot() +
    #   aes(x = ep, y = averageRating, group = seasonNumber) +
    #   geom_point(aes(text = primaryTitle)) +
    #   geom_smooth(method = "lm") +
    #   xlab("Episode") +
    #   ylab("Rating")

  })
}

shinyApp(ui = ui, server = server)