R 闪亮：sqldf 不适用于观察，显示：result_create错误：接近 "&"：语法错误

Question

我试图让分区统计图在用户使用滑块更改输入时做出反应。我正在使用sqldf库来查询结果并将其绘制在地图上。但是，目前，我得到的结果是"result_create中的错误：接近"&"：语法错误"。不知道如何解决它，或者也许我从一开始就错了。

library(magrittr)
library(shiny)
library(shinythemes)
library(dplyr)
library(readr)
library(ggplot2)
library(leaflet)
library(evaluate)
library(ggmap)
library(rgdal)
library(tmap)
library(tmaptools)
library(sf)
library(geojsonio)
library(sqldf)
library(DBI)
library(gsubfn)
library(RH2)
library(RSQLite)
library(rJava)
library(shinydashboard)

# Define UI for application that draws a histogram
ui <- dashboardPage(
# Application title
dashboardHeader(title="Vic Car Accidents"),
dashboardSidebar(
sliderInput("range", "Year:",
min = 2013, max = 2018,
value = c(2013,2018))),
dashboardBody(
box(title = "Choropleth Map",width = 12, status = "primary",leafletOutput("young_driver",width = "100%", height = 400)
),
box(tableOutput("values"))
))

# Define server logic required to draw a histogram
options(scipen = 999)
#read LGA geojson file from local file
LGA<-st_read("Data/LGA.geojson",stringsAsFactors = FALSE)
#read car crashes data from local file
carCrashes<- read_csv("Data/Car.csv")
server <- shinyServer(function(input, output, session) {
sliderValues <- reactive({
data.frame(name = "range", value= (paste(input$range[1],input$range[2])))})
output$values <- renderTable({
sliderValues()
})
temp <- observe({
year_1 <- input$range[1]
year_2 <- input$range[2]
#data.frame(name = "range", value= (paste(input$range[1],input$range[2])))})
read.csv.sql(
"Data/Car.csv",
sql = ("select distinct LGA_NAME, count(LGA_NAME) as 'number of young driver'
from file
where driver_type = 'young driver' and
year_period >= year_1 && year_period <= year_2
group by LGA_NAME; ")
)
sub_and_car <- left_join(LGA,temp,by = c("VIC_LGA__3" = "LGA_NAME"))
output$young_driver<-renderLeaflet({
tm<-tm_shape(sub_and_car)+tm_polygons(col="number of young driver", border.col="grey")
tmap_leaflet(tm)
})

# output$young_driver<-renderLeaflet({

# tm<-tm_shape(sub_and_car)+tm_polygons(col="number of young driver", border.col="grey")
#tmap_leaflet(tm)
})
})

# Run the application 
shinyApp(ui = ui, server = server)

Answer 1

好的，我知道了...您在代码中犯了多个错误：

observeEvent应该仅用于检查 sideeffekts 永远不会创建 data.frame。始终使用eventReactive来执行此操作。在您的sqldf代码中，有许多错误我无法弄清楚。无论如何，我建议学习dplyr，这是其他程序员也使用的标准。

将创建data.frames与渲染控件分开。由于 Map 需要很长时间才能渲染，因此我使用shinycssloaders向用户显示一些反馈。否则，他或她可能会关闭应用程序。

这是代码，只需更改路径：

library(magrittr)
library(shiny)
library(shinythemes)
library(dplyr)
library(readr)
library(ggplot2)
library(leaflet)
library(evaluate)
library(ggmap)
library(rgdal)
library(tmap)
library(tmaptools)
library(sf)
library(geojsonio)
library(sqldf)
library(DBI)
library(gsubfn)
library(RH2)
library(RSQLite)
library(rJava)
library(shinydashboard)
library(shinycssloaders)

# Define UI for application that draws a histogram
ui <- dashboardPage(
# Application title
dashboardHeader(title="Vic Car Accidents"),
dashboardSidebar(
sliderInput("range", "Year:",
min = 2013, max = 2018,
value = c(2013,2018)),
actionButton("change", "Change")),
dashboardBody(
box(title = "Choropleth Map",width = 12, status = "primary",withSpinner(leafletOutput("young_driver",width = "100%", height = 400))
),
box(tableOutput("values"))
))

# Define server logic required to draw a histogram
options(scipen = 999)
#read LGA geojson file from local file
LGA<-st_read("C:/Users/User/Downloads/R-project-master/R-project-master/LGA.geojson",stringsAsFactors = FALSE)
#read car crashes data from local file
carCrashes<- read_csv("C:/Users/User/Downloads/R-project-master/R-project-master/Car.csv")
server <- shinyServer(function(input, output, session) {
sliderValues <- reactive({
data.frame(name = "range", value= (paste(input$range[1],input$range[2])))})
output$values <- renderTable({
sliderValues()
})
cmap <- eventReactive(input$change,{
range_1 <- as.numeric(input$range[1])
range_2 <- as.numeric(input$range[2])
temp <- carCrashes %>%
filter(driver_type == 'young driver' & year_period >= range_1 & year_period <= range_2) %>%
group_by(LGA_NAME) %>%
mutate(`Number of young drivers` = n())

# temp<- sqldf(
# "C:/Users/User/Downloads/R-project-master/R-project-master/Car.csv",
# sql = paste("select distinct LGA_NAME, count(LGA_NAME) as 'number of young driver' 
# from file
# where driver_type = 'young driver' and
# year_period between range_1 and range_2
# group by LGA_NAME; ")
sub_and_car <- left_join(LGA,temp,by = c("VIC_LGA__3" = "LGA_NAME"))
})
output$young_driver <- renderLeaflet({
tm <- tm_shape(cmap())+tm_polygons(col="Number of young drivers", border.col="grey")
tmap_leaflet(tm)
})
})

# Run the application 
shinyApp(ui = ui, server = server)