-
Need help... I had created a pie graph comparing count of car makes of US industry and Autongin(internal purpose). Also I had created a bar chart comparing Car Model counts of each car makes in US industry and Autongin.I want to get this bar chart when clicking on a tab or something displayed in the pie chart window .I created the graph using plotly package and the database is connected .I want to get the bar chart created when clicking on tab displayed in pie chart window.
The sample dataset used to plot pie chart is attached here
AutonginMake USMakename
1 Acura Acura
2 Aston Martin Aston Martin
3 Audi Audi
4 Bentley Bentley
5 BMW BMW
6 Buick Buick
7 Cadillac Cadillac
8 Chevrolet Chevrolet
9 Chrysler Chrysler
10 Dodge Dodge
11 Ford Ford
12 GMC GMC
13 Honda Honda
14 HUMMER Hummer
#packages needed
library(plotly)
library(shiny)
library(DBI)
library(RMySQL)
#connecting db
dealerinventory1<-dbConnect(RMySQL::MySQL(), user='ghhjjl',
password='dfgfdgdg!',
host='hfghfh',
dbname='hhthhq23u')
library(shiny)
library(plotly)
ui <- fluidPage(tagList(
plotlyOutput("pie"),
verbatimTextOutput("makes")
))
server <- function(input, output) {
uscount1=dbGetQuery(dealerinventory1,
'SELECT count(distinct makename) as USmakes FROM dealer_inventory.CarQuery;')
autongincount1=dbGetQuery(dealerinventory1,
'SELECT count(distinct makename) as autonginmakes FROM dealer_inventory.car_inventory_json_lookup;')
usandautongintable <- c(autongincount1,uscount1)
usandautongintable
label <- c(paste("Autongin Count: ", autongincount1),paste("US Industry Count: ", uscount1))
label
unlist <- as.numeric(unlist(usandautongintable))
typeof(unlist)
table<- as.data.frame(usandautongintable)
table
usmakes <- dbGetQuery(dealerinventory1,"SELECT distinct makename as USmakes FROM dealer_inventory.CarQuery;")
autonginmakes <- dbGetQuery(dealerinventory1,
'SELECT distinct makename as autonginmakes FROM dealer_inventory.car_inventory_json_lookup;')
usmakes_count <- dbGetQuery(dealerinventory1,
'SELECT count(distinct makename) as USmakes FROM dealer_inventory.CarQuery;')
autonginmakes_count <- dbGetQuery(dealerinventory1,
'SELECT count(distinct makename) as autonginmakes FROM dealer_inventory.car_inventory_json_lookup;')
output$pie <- renderPlotly({
plot_ly(table, labels = label,
values = unlist,
key=c("us", "autongin"),
type = "pie") %>%
layout(title = 'Comparison of Makes',
xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
})
output$makes <- renderPrint({
clicked <- event_data("plotly_click")
if (is.null(clicked)) {
"Click on US Industry Count or Autongin"
} else if (clicked$key == "us") {
autonginmakes
} else {
usmakes
}
})
}
shinyApp(ui, server)
Output plot link is here http://autonginreports.iinerds.com:3838/sample-apps/plot/
The above code is used to create pie chart .
The code following is used to create bar plot
param_modelyear=c(2008:2018)
for ( i in param_modelyear){
print(i)
}
modelyearquery=paste0(" (SELECT A.makename as AutonginMake,Case when B.makename is null Then A.makename
Else B.makename end As USMakename
,A.AutonginModelcount,Case when B.USModelcount is null Then 0
Else B.USModelcount end as USModelcount
FROM (select count(*) as AutonginModelcount,makename as makename from
(select distinct makename ,modelname from dealer_inventory.car_inventory_json_lookup where modelyear=",param_modelyear," order by makename) as C
group by makename) A Left JOIN (select count(*) as USModelcount,makename as makename from
(select distinct makename ,modelname from dealer_inventory.CarQuery where modelyear=",param_modelyear," order by makename) as D
group by makename) B ON A.makename=B.makename
ORDER BY A.makename)
UNION
(SELECT Case when A.makename is null Then B.makename
Else A.makename end As AutonginMake,B.makename as USMakename,Case when A.AutonginModelcount is null Then 0
Else A.AutonginModelcount end as AutonginModelcount,B.USModelcount
FROM (select count(*) as AutonginModelcount,makename as makename from
(select distinct makename ,modelname from dealer_inventory.car_inventory_json_lookup where modelyear=",param_modelyear," order by makename) as C
group by makename) A Right JOIN (select count(*) as USModelcount,makename as makename from
(select distinct makename ,modelname from dealer_inventory.CarQuery where modelyear=",param_modelyear," order by makename) as D
group by makename) B ON A.makename=B.makename
ORDER BY A.makename);")
modelyearquery
makemodel1=dbGetQuery(dealerinventory1,modelyearquery)
plot5 <- plot_ly(makemodel1, x = ~AutonginMake, y = ~USModelcount, type = 'bar', name = 'USModelcount') %>%
add_trace(y = ~AutonginModelcount, name = 'AutonginModelcount') %>%
layout(
title="Yearwise comparison of Models of each Makes in Autongin and US market",
yaxis = list(title = 'modelcount'),
barmode = 'group')
plot5
library(shiny)
library(plotly)
models= fluidPage(selectInput('my_dropdown','Choose Column',param_modelyear),
plotlyOutput("plot")
)
server3<- function(input,output){
output$plot=renderPlotly({
plot_ly(makemodel1, x = ~AutonginMake, y = ~USModelcount, type = 'bar', name = 'USModelcount') %>%
add_trace(y = ~AutonginModelcount, name = 'AutonginModelcount') %>%
layout(
title="Yearwise comparison of Models of each Makes in Autongin and US market",
yaxis = list(title = 'modelcount'),
barmode = 'group')
})
}
shinyApp(models,server3)
I want to get the bar chart "plot5" when clicking on tab or something displayed on pie chart window
plot5 <- plot_ly(makemodel1, x = ~AutonginMake, y = ~USModelcount, type = 'bar', name = 'USModelcount') %>%
add_trace(y = ~AutonginModelcount, name = 'AutonginModelcount') %>%
layout(
title="Yearwise comparison of Models of each Makes in Autongin and US market",
yaxis = list(title = 'modelcount'),
barmode = 'group')