Creating Shiny table from R code

ggplot2
shiny
rstudio

#1

I am using the below code in R studio to pull in Facebook insights. This pulls in the data fine into R Studio but I want to be able to use Shiny to make the sum_by_day dataset output in the browser: http://shiny.rstudio.com/gallery/basic-datatable.html How can I structure my code to get to that point, I have tried a few things and have hit a wall.

Any help appreciated.

# Loading packages
library(httr)
library(tidyverse)
library(lubridate)

# Setting the FB access token as a variable
access_token <- "TOKEN GOES HERE"

# Getting the last 30days of Facebook Insights and assiging into an object 
called 'report'
report <- GET('https://graph.facebook.com/v2.10/URLHERE/insights',
              query = list(
                access_token = access_token,
                level="ad",
                fields = "campaign_id, adset_id, adset_name, ad_id, ad_name, 
impressions, cpm, reach, clicks, unique_clicks, ctr, cpc, unique_ctr, 
cost_per_unique_click, estimated_ad_recall_rate, 
cost_per_estimated_ad_recallers, spend, canvas_avg_view_time, 
canvas_avg_view_percent",
                limit = "5000",
                encode = "json",
                date_preset="last_7d",
                breakdowns = "age",
                time_increment="1"),
              verbose())

# Getting the output into a tidy dataframe
content_result <- content(report)
content_result[["paging"]] <- NULL
result_data <- content_result$data
result_data <- result_data %>% reduce(bind_rows)


# Setting classes of variables - numerical, data, etc and putting into a 
frame called 'import'
result_data$impressions <- as.numeric(result_data$impressions)
result_data$unique_clicks <- as.numeric(result_data$unique_clicks)
result_data$clicks <- as.numeric(result_data$clicks)
result_data$spend <- as.numeric(result_data$spend)
result_data$date_start <- as.Date(result_data$date_start)
result_data$date_stop <- as.Date(result_data$date_stop)

import <- result_data


# Subset and summarise by day
sum_by_day <- import  %>%
  group_by(date_start) %>%
  summarise(clicks = sum(clicks), impressions = sum(impressions), 
spend=sum(spend)) %>%
  mutate(CPC_new=spend/clicks) %>%
  mutate(CTR_new=clicks / impressions)

#2

Can you post some examples of what you've tried so far? I don't see any Shiny code here, so it's not clear where you have "hit a wall."


#3

This is as far I have got. Basically comes back saying sum_by_day is not a function. Looking at the examples I thought that I could output the table that gets output in Rstudio that way. Am I miles off?

library(httr)
library(tidyverse)
library(lubridate)
library(shiny)

# Define UI for dataset viewer app ----
ui <- fluidPage(
  
  # App title ----
  titlePanel("Widgets"),
  
  # Sidebar layout with input and output definitions ----
  sidebarLayout(
    
    # Sidebar panel for inputs ----
    sidebarPanel(
      
      
      h4("Sidebar Content")
      
    ),
    
    # Main panel for displaying outputs ----
    mainPanel(
      
      
      # Output: Header + table ----
      h4("Observations"),
      tableOutput("view")
    )
    
  )
)

# Define server logic to summarize and view selected dataset ----
server <- function(input, output) {
  
  
  # THIS IS THE R CODE
  
  # Setting the FB access token as a variable
  access_token <- "ACCESS TOKEN HERE"
  
  # Getting the last 30days of Facebook Insights and assiging into an object called 'report'
  report <- GET('https://graph.facebook.com/v2.10/act_781789658666312/insights',
                query = list(
                  access_token = access_token,
                  level="ad",
                  fields = "campaign_id, adset_id, adset_name, ad_id, ad_name, impressions, cpm, reach, clicks, unique_clicks, ctr, cpc, unique_ctr, cost_per_unique_click, estimated_ad_recall_rate, cost_per_estimated_ad_recallers, spend, canvas_avg_view_time, canvas_avg_view_percent",
                  limit = "5000",
                  encode = "json",
                  date_preset="last_7d",
                  breakdowns = "age",
                  time_increment="1"),
                verbose())
  
  # Getting the output into a tidy dataframe
  content_result <- content(report)
  content_result[["paging"]] <- NULL
  result_data <- content_result$data
  result_data <- result_data %>% reduce(bind_rows)
  
  
  # Setting classes of variables - numerical, data, etc and putting into a frame called 'import'
  result_data$impressions <- as.numeric(result_data$impressions)
  result_data$unique_clicks <- as.numeric(result_data$unique_clicks)
  result_data$clicks <- as.numeric(result_data$clicks)
  result_data$spend <- as.numeric(result_data$spend)
  result_data$date_start <- as.Date(result_data$date_start)
  result_data$date_stop <- as.Date(result_data$date_stop)
  
  import <- result_data
  
  
  
  # Subset and summarise by day
  sum_by_day <- import  %>%
    group_by(date_start) %>%
    summarise(clicks = sum(clicks), impressions = sum(impressions), spend=sum(spend)) %>%
    mutate(CPC_new=spend/clicks) %>%
    mutate(CTR_new=clicks / impressions)
  
  
  # END OF R CODE
  
  
  
  
  output$view <- renderTable({
    sum_by_day()
  })
  
}

# Create Shiny app ----
shinyApp(ui, server)

#4

I dont know if this helps at all but this is the table that gets outputted in R that I want to be displayed in shiny


#5

In the code chuck above, you refer to the table sum_by_day as a function call (that is, you call it sum_by_day()).

Were you maybe thinking to put sum_by_day in a reactive expression - one that is called by your shiny app to create a table or plot?


#6

Thank you for your reply! I removed the () and just left it as sum_by_day and it pulled through...totally didnt occur to me that that made it into a function, not sure why.

If I was to let the use change data in the table I would have to use a reactive expression instead?


#7

Yes, currently your app is only creating a static table. If you want the user to be able to interact with the data then you will have to move your code into reactive expressions.


#8

Dont suppose you know of any simple tutorials I can view as all of the examples of reactive expressions have blown my mind a bit. All I think that I want to do is change the output of the sub_by_day table by using different dropdown lists to alter the items in brackets below:


#9

LESSON 6 Use reactive expressions

This lesson walks through an example in which you build a shiny app that let's you pick a stock from a drop-down, select a price range and a few other options, and then displays a plot. Seems close to what you're looking for.


#10

Ok so I have changed my code to the following:

selectInput("filter",
                 label = "Filter me:",
                 choices = c ("date_start", "Sum By Day", "Sum By Age"), selected = "All")
            

  output$maindata <- DT::renderDataTable({DT::datatable(import())})
  output$sumbyday <- DT::renderDataTable({DT::datatable(sum_by_day())})
  output$sumbyage <- DT::renderDataTable({DT::datatable(sum_by_age())})
    
      output$sumbyday <- renderTable({
        # Subset and summarise by day
          sum_by_day <- import  %>%
          group_by(input$filter) %>%
          summarise(clicks = sum(clicks), impressions = sum(impressions), spend=sum(spend)) %>%
          mutate(CPC_new=spend/clicks) %>%
          mutate(CTR_new=clicks / impressions)
      })

So now input$filter pulls in the value into the brackets but as you can see from the 2 screen grabs Ive uploaded it shows the value input$filter in the column name and puts the value of the filter in the first row cell, which is not what I was after. I just want it to output the date_start in the code and in the column name. Is there anyway to change what Ive done so the filter just outputs that value?

new


#11

working%20origi


#12

I find this text confusing (sorry!). Particularly the line "I just want it to output the date_start in the code and in the column name."
Could you explain a bit more? There are only three choices in filter, and at least your first image seems to show what you've asked for in renderTable.

Perhaps offer an example what it should look like?


#13

sorry for the confusion. I added 3 items in my filter just to see if the output changed in my table when my selct item changed and it does which is good. So When you select date_start in the drop down I was hoping that the value "date_start" would be added to group_by(date_start) %>% and that would make the form look like the last screen grab I uploaded.. instead when I select that filter option it looks like the other screen grab I uploaded, with the row 1 outputted.


#14

I guess I'm now confused why you'd want to have a select input that changes the variables you're grouping by?


To explain a bit, and I strongly suspect I am still confused as to your ultimate goal, but note that your input$filter is a character string (eg "date_start") but group_by accepts as input Variables to group by. These are not character strings.

For example,

library(dplyr)
iris %>% group_by(Species) %>% tally()
#> # A tibble: 3 x 2
#>   Species        n
#>   <fct>      <int>
#> 1 setosa        50
#> 2 versicolor    50
#> 3 virginica     50
iris %>% group_by("Species") %>% tally()
#> # A tibble: 1 x 2
#>   `"Species"`     n
#>   <chr>       <int>
#> 1 Species       150

Created on 2018-04-18 by the reprex package (v0.2.0).



#15

ok thank you I understand what you are saying. I just want the (date_start) value to change depending on what the user selects from a drop down list, so they can choose to goup_by date_start or age or gender. Will a drop down output always put it as a text string like "date_start" or is there a way of making it not do this?

Much appreciate the help btw


#16

For age, gender and date, I'd use dplyr::filter, and then filter based on it.

For gender you could use text (maybe checkboxes so you can select one gender or both), ints for age, and for start date use dates.

Check out UI Inputs for options -- shiny reference


#17

Thats great thank you, I got it working from that.

Created a function:

  sum_fun <- function(x){
    import  %>%
      group_by_(x) %>%
      summarise(clicks = sum(clicks), impressions = sum(impressions), spend=sum(spend)) %>%
      mutate(CPC_new=spend/clicks) %>%
      mutate(CTR_new=clicks / impressions)
  }

Then rendered the table and changed the value using a filter:

    output$filteredtable <- renderTable({
      sum_by_day_age <- sum_fun(input$filtersumfun)
    })

Once again thanks a lot.