■
*기본 테이블 형태의 데이터 출력하는 샤이니 기본 코드
install.packages("DT")
#한번했으면 주석으로 막기
library(DT)
library(shiny)
library(ggplot2)
#emp <- read.csv("d:\\emp.csv",header=T)
#emp 선언 본인 컴퓨터에 맞게끔!
# Define UI ----
ui <- fluidPage(
titlePanel("EMP DataTable"),
# Create a new Row in the UI for selectInputs
# Create a new row for the table.
DT::dataTableOutput("table")
#뭘 써도 관계는 없는데, 아래 ouput과 이름 일치시켜야 한다.
)
# Define server logic ----
server <- function(input, output) {
# Filter data based on selections
output$table <- DT::renderDataTable(DT::datatable({
data <- emp
}))
}
# Run the app ----
shinyApp(ui = ui, server = server)
문제145.
우리 기존의 완성한 샤이니 코드에 csv 파일을 불러오면
아래와 같이 테이블 형태로 출력되도록 사이트 텝을 만들고 구현하시오
############## set this file location to working directory ##########################
packages <- 'rstudioapi'
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
library('rstudioapi')
current_dir<-dirname(rstudioapi::getSourceEditorContext()$path)
setwd(current_dir)
package_in<-function(p_name,option=1){
packages <- p_name
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
if (option==1){
library(p_name,character.only = TRUE)
}
}
###########################1. 패키지 설치##########################################
package_in('shinydashboard')
package_in('shiny')
package_in('ggplot2')
package_in('plotly')
package_in('lattice')
######################### 2. 화면 개발 ###########################################
sidebar <- dashboardSidebar(
sidebarMenu(
fileInput("file1", "Choose CSV File",
multiple = FALSE,
accept = c("text/csv",".xlsx",".txt",
"text/comma-separated-values,text/plain",
".csv")),
menuItem("Plot",
menuSubItem('Barplot',tabName='barplot'),
menuSubItem('Piechart',tabName='piechart'),
menuSubItem('Lineplot',tabName='lineplot'),
menuSubItem('Scatterplot',tabName='scatterplot')
),
menuItem("table",
menuSubItem('Tableformat',tabName='tableformat'),
menuSubItem('dbconnect',tabName='dbconnect')
)
)
)
body <- dashboardBody(
tabItems(
##### bar plot
tabItem(tabName = "barplot",
sidebarPanel(
selectInput("in_sel_bar_yVar","y Variable:", choices = NULL),
selectInput("in_sel_bar_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotOutput('plot_bar')
)
),
##### piechart
tabItem(tabName = "piechart",
sidebarPanel(
selectInput("in_sel_pie_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotlyOutput('plot_pie')
)
),
##### line plot
tabItem(tabName = "lineplot",
sidebarPanel(
selectInput("in_sel_line_yVar","y Variable:", choices = NULL),
selectInput("in_sel_line_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotlyOutput('plot_line')
)
),
##### scatter plot
tabItem(tabName = "scatterplot",
sidebarPanel(
selectInput("in_sel_scatter_yVar","y Variable:", choices = NULL),
selectInput("in_sel_scatter_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotOutput('plot_scatter'),
textOutput('text_scatter')
)
),
# Table
tabItem(tabName = "tableformat",
mainPanel(
DT::dataTableOutput("table")
)
)
)
)
ui<-dashboardPage(
dashboardHeader(title='my graph'),
sidebar,
body
)
######################3. 서버단 개발 ########################################
server <- function(input, output,session) {
options(warn = -1)
options(shiny.maxRequestSize = 30*1024^2)
dataload<-reactive({
req(input$file1)
file1 = input$file1
data1 = read.csv(file1$datapath)
updateSelectInput(session, "in_sel_bar_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_bar_yVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_pie_xVar", choices = data1[,1])
updateSelectInput(session, "in_sel_line_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_line_yVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_scatter_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_scatter_yVar", choices = colnames(data1))
return(data1)
})
####nomal_bar
output$plot_bar <- renderPlot({
table_in<-dataload()
xdata<-as.factor(table_in[,input$in_sel_bar_xVar])
ydata<-as.factor(table_in[,input$in_sel_bar_yVar])
fdata=data.frame(x=xdata,y=ydata)
ggplot(fdata) +
geom_bar(aes_string(x='x',y='y',fill='x'),stat = "identity",show.legend=F)
})
output$plot_pie <- renderPlotly({
table_in<-dataload()
plot_ly(table_in, labels = ~colnames(table_in)[-1], values=~as.factor( table_in[table_in[,1] == input$in_sel_pie_xVar,-1] ),type='pie')
})
output$plot_line <- renderPlotly({
table_in<-dataload()
x <- list(title = input$in_sel_line_xVar)
y <- list(title = input$in_sel_line_yVar)
plot_ly(data = table_in,x=~table_in[,input$in_sel_line_xVar],y=~table_in[,input$in_sel_line_yVar],type='scatter',mode='dot')%>%
layout(xaxis = x, yaxis = y)
})
output$plot_scatter <- renderPlot({
table_in<-dataload()
xyplot(table_in[,input$in_sel_scatter_yVar]~table_in[,input$in_sel_scatter_xVar], grid=T,type=c('p','smooth'),col.line='darkorange',lwd=2, xlab=input$in_sel_scatter_xVar,ylab=input$in_sel_scatter_yVar)
})
output$text_scatter <- renderText({
table_in<-dataload()
paste("The correlation between the two is: ", cor(table_in[,input$in_sel_scatter_yVar],table_in[,input$in_sel_scatter_xVar]))
})
####table_format
output$table <- DT::renderDataTable(DT::datatable({
req(input$file1)
file1 = input$file1
data1 = read.csv(file1$datapath)
}))
}
######################### 4. 샤이니 실행 ###############################
shinyApp(ui = ui, server = server)
■ 오라클 database 와 R 과의 연동
install.packages('DBI')
install.packages('RJDBC')
library(RJDBC)
library(DBI)
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
emp_query <- 'select * from emp'
emp_data <- dbGetQuery(oracle_db, emp_query)
emp_data
# 인스톨시 rjava 오류뜬다. 너무나도 유명한 오류!
■ Rjava 오류 해결
http://cafe.daum.net/oracleoracle/SRv4/13 <-- 참고
1. 아래에서 java 64비트를 다운로드 받는다
https://www.java.com/en/download/manual.jsp
다운받아서 설치하고 위치를 확인한다
C:\\Program Files\\Java\\jre1.8.0_201
여기 위치와 같으면 그대로 진행하고
다르면 위에대로 바꿔준다.
2. 자바 설치시 대상 폴더 변경으로 설치
3. 아래와 같이 환경설정을 한다.
자바설치하고, R에서 인스톨명령어 위에 아래 명령어인 Sys를 돌린다
Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre1.8.0_201')
4. 설치를 한다.
Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre1.8.0_201')
install.packages('rJava')
library(rJava)
install.packages('DBI')
install.packages('RJDBC')
library(RJDBC)
library(DBI)
이렇게 쫙돌리고 에러없으면 성공!
문제146.
부서번호가 10번인 사원들의 이름,월급을 오라클 데이터 베이스에서
불러와서 R에서 출력하시오
Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre1.8.0_201')
install.packages('rJava')
library(rJava)
install.packages('DBI')
install.packages('RJDBC')
library(RJDBC)
library(DBI)
# rjava 오류뜬다. 너무나도 유명한 오류!
#setwd 위치에 ojdbc6.jar 갖다놓고 아래 driver 돌리기
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
# 1521로 돌렸는데 에러나면 1522로 시도해볼것.
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
emp_query <- 'select ename, sal from emp where deptno=10'
emp_data <- dbGetQuery(oracle_db, emp_query)
emp_data
ENAME SAL
1 KING 5000
2 CLARK 2450
3 MILLER 1300
문제147.
이름,부서위치를 오라클 데이터베이스에서 불러와서
R에서 출력하시오
(*ON 절을 사용한 조인법으로 해야함!)
emp_query <- 'select e.ename, d.loc
from emp e join dept d on (e.deptno=d.deptno)'
emp_data <- dbGetQuery(oracle_db, emp_query)
emp_data
ENAME
LOC
1 KING NEW YORK
2 BLAKE
CHICAGO
3 CLARK NEW YORK
4 JONES DALLAS
5 MARTIN
CHICAGO
6 ALLEN
CHICAGO
7 TURNER
CHICAGO
8 JAMES
CHICAGO
9 WARD
CHICAGO
10 FORD DALLAS
11 SMITH DALLAS
12 SCOTT DALLAS
13 ADAMS DALLAS
14 MILLER NEW YORK
문제148.
scott 유저가 가지고 있는 테이블이 무엇이 있는지 확인하시오
emp_query <- 'select table_name
from user_tables'
emp_data <- dbGetQuery(oracle_db, emp_query)
emp_data
문제149.
R샤이니에서 오라클 데이터 베이스의 테이블의 데이터를
쉽게 볼 수 있게 하시오
##패키지 설치에다가 라이브러리 추가
package_in('RJDBC')
package_in('DBI')
##body에 아래 내용 추가
##### dbconnect_format
tabItem(tabName = "dbconnect",
sidebarPanel(
selectInput("in_sel_table_xVar","x Variable:", choices = NULL)
), # 테이블명 뿌려줘야 해서 추가!
mainPanel(
DT::dataTableOutput("table1")
)
),
##서버단에 아래 내용 추가
updateSelectInput(session, "in_sel_table_xVar", choices = table_list$TABLE_NAME)
##updateSelectInput 위에 이 내용도 넣기
# 오라클 디비(database)와 연동을 위한 코드
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
table_query <- 'select table_name from user_tables'
table_list <- dbGetQuery(oracle_db,table_query )
##아웃풋에 아래내용 추가
####dbconnect_format
output$table1 <- DT::renderDataTable(DT::datatable({
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
# table_query <- 'select table_name from user_tables'
# table_list <- dbGetQuery(oracle_db,table_query )
#에러나면 위 두줄 주석제거하고 돌렵기
ttt <- input$in_sel_table_xVar
#위에서 선언한내용 ttt에 담기
emp_query2 <- paste('select * from ', ttt)
# from 뒤에 띄어쓰기 꼭 할 것!!!
table_list2 <- dbGetQuery(oracle_db,emp_query2 )
}))
※※※※※※※※※※그런데 현재 버그 하나 있음
바로 dbconnect가 안돌아감
돌아가게 하려면 브라우져로 아무 csv 파일 불러온 다음에
dbconnect 해야 함!
↓
dataload 안에있던 update 문을 꺼내서
sever단 바로 아래로 수정시킴
# 오라클연동 코드를 데이터로드 문에 넣으면
# 말그대로 데이터로드를 실행시켜야
# 오라클 테이블 데이터가 불러와지는 거다
# 그런 과정없이 창 키자마자 바로 불러오게 하기위해
# 데이터로드에서 꺼낸것이다!
■수정코드(완성!)
############## set this file location to working directory ##########################
packages <- 'rstudioapi'
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
library('rstudioapi')
current_dir<-dirname(rstudioapi::getSourceEditorContext()$path)
setwd(current_dir)
package_in<-function(p_name,option=1){
packages <- p_name
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
if (option==1){
library(p_name,character.only = TRUE)
}
}
###########################1. 패키지 설치##########################################
package_in('shinydashboard')
package_in('shiny')
package_in('ggplot2')
package_in('plotly')
package_in('lattice')
package_in('RJDBC')
package_in('DBI')
package_in('rJava')
######################### 2. 화면 개발 ###########################################
sidebar <- dashboardSidebar(
sidebarMenu(
fileInput("file1", "Choose CSV File",
multiple = FALSE,
accept = c("text/csv",".xlsx",".txt",
"text/comma-separated-values,text/plain",
".csv")),
menuItem("Plot",
menuSubItem('Barplot',tabName='barplot'),
menuSubItem('Piechart',tabName='piechart'),
menuSubItem('Lineplot',tabName='lineplot'),
menuSubItem('Scatterplot',tabName='scatterplot')
),
menuItem("table",
menuSubItem('Tableformat',tabName='tableformat'),
menuSubItem('dbconnect',tabName='dbconnect')
)
)
)
body <- dashboardBody(
tabItems(
##### bar plot
tabItem(tabName = "barplot",
sidebarPanel(
selectInput("in_sel_bar_yVar","y Variable:", choices = NULL),
selectInput("in_sel_bar_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotOutput('plot_bar')
)
),
##### piechart
tabItem(tabName = "piechart",
sidebarPanel(
selectInput("in_sel_pie_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotlyOutput('plot_pie')
)
),
##### line plot
tabItem(tabName = "lineplot",
sidebarPanel(
selectInput("in_sel_line_yVar","y Variable:", choices = NULL),
selectInput("in_sel_line_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotlyOutput('plot_line')
)
),
##### scatter plot
tabItem(tabName = "scatterplot",
sidebarPanel(
selectInput("in_sel_scatter_yVar","y Variable:", choices = NULL),
selectInput("in_sel_scatter_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotOutput('plot_scatter'),
textOutput('text_scatter')
)
),
# Table
tabItem(tabName = "tableformat",
mainPanel(
DT::dataTableOutput("table")
)
),
##### dbconnect_format
tabItem(tabName = "dbconnect",
sidebarPanel(
selectInput("in_sel_table_xVar","x Variable:", choices = NULL)
),
mainPanel(
DT::dataTableOutput("table1")
)
)
)
)
ui<-dashboardPage(
dashboardHeader(title='my graph'),
sidebar,
body
)
######################3. 서버단 개발 ########################################
server <- function(input, output,session) {
options(warn = -1)
options(shiny.maxRequestSize = 30*1024^2)
# 오라클 디비(database)와 연동을 위한 코드
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
table_query <- 'select table_name from user_tables'
#오라클에 있는 모든 테이블정보를 가져오시오
table_list <- dbGetQuery(oracle_db,table_query )
updateSelectInput(session, "in_sel_table_xVar", choices = table_list$TABLE_NAME)
dataload<-reactive({
req(input$file1)
file1 = input$file1
data1 = read.csv(file1$datapath)
#선택박스 화면 구현
updateSelectInput(session, "in_sel_bar_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_bar_yVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_pie_xVar", choices = data1[,1])
updateSelectInput(session, "in_sel_line_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_line_yVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_scatter_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_scatter_yVar", choices = colnames(data1))
return(data1)
})
####nomal_bar
output$plot_bar <- renderPlot({
table_in<-dataload()
xdata<-as.factor(table_in[,input$in_sel_bar_xVar])
ydata<-as.factor(table_in[,input$in_sel_bar_yVar])
fdata=data.frame(x=xdata,y=ydata)
ggplot(fdata) +
geom_bar(aes_string(x='x',y='y',fill='x'),stat = "identity",show.legend=F)
})
output$plot_pie <- renderPlotly({
table_in<-dataload()
plot_ly(table_in, labels = ~colnames(table_in)[-1], values=~as.factor( table_in[table_in[,1] == input$in_sel_pie_xVar,-1] ),type='pie')
})
output$plot_line <- renderPlotly({
table_in<-dataload()
x <- list(title = input$in_sel_line_xVar)
y <- list(title = input$in_sel_line_yVar)
plot_ly(data = table_in,x=~table_in[,input$in_sel_line_xVar],y=~table_in[,input$in_sel_line_yVar],type='scatter',mode='dot')%>%
layout(xaxis = x, yaxis = y)
})
output$plot_scatter <- renderPlot({
table_in<-dataload()
xyplot(table_in[,input$in_sel_scatter_yVar]~table_in[,input$in_sel_scatter_xVar], grid=T,type=c('p','smooth'),col.line='darkorange',lwd=2, xlab=input$in_sel_scatter_xVar,ylab=input$in_sel_scatter_yVar)
})
output$text_scatter <- renderText({
table_in<-dataload()
paste("The correlation between the two is: ", cor(table_in[,input$in_sel_scatter_yVar],table_in[,input$in_sel_scatter_xVar]))
})
####table_format
output$table <- DT::renderDataTable(DT::datatable({
req(input$file1)
file1 = input$file1
data1 = read.csv(file1$datapath)
}))
####dbconnect_format
output$table1 <- DT::renderDataTable(DT::datatable({
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
#가동
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
#접속정보
# table_query <- 'select table_name from user_tables'
# table_list <- dbGetQuery(oracle_db,table_query )
#에러나면 위 두줄 주석제거하고 돌렵기
ttt <- input$in_sel_table_xVar
#위에서 선언한 내용 ttt에 담기
emp_query2 <- paste('select * from ', ttt)
#from 뒤에 꼭 띄어쓰기
table_list2 <- dbGetQuery(oracle_db,emp_query2 )
}))
}
######################### 4. 샤이니 실행 ###############################
shinyApp(ui = ui, server = server)
문제150(점심시간문제)
오라클 데이터 베이스에서 선택한 테이블로 그래프가 그려지게 하시오!
############## set this file location to working directory ##########################
packages <- 'rstudioapi'
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
library('rstudioapi')
current_dir<-dirname(rstudioapi::getSourceEditorContext()$path)
setwd(current_dir)
package_in<-function(p_name,option=1){
packages <- p_name
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
if (option==1){
library(p_name,character.only = TRUE)
}
}
###########################1. 패키지 설치##########################################
package_in('shinydashboard')
package_in('shiny')
package_in('ggplot2')
package_in('plotly')
package_in('lattice')
package_in('RJDBC')
package_in('DBI')
######################### 2. 화면 개발 ###########################################
sidebar <- dashboardSidebar(
sidebarMenu(
fileInput("file1", "Choose CSV File",
multiple = FALSE,
accept = c("text/csv",".xlsx",".txt",
"text/comma-separated-values,text/plain",
".csv")),
menuItem("테이블",
menuSubItem('Tableformat',tabName='tableformat'),
menuSubItem('Dbconnect',tabName='dbconnect') ),
menuItem("그래프",
menuSubItem('Barplot',tabName='barplot'),
menuSubItem('Piechart',tabName='piechart'),
menuSubItem('Lineplot',tabName='lineplot'),
menuSubItem('Scatterplot',tabName='scatterplot')
)
)
)
body <- dashboardBody(
tabItems(
##### table_format
tabItem(tabName = "tableformat",
mainPanel(
DT::dataTableOutput("table")
)
),
##### dbconnect_format
tabItem(tabName = "dbconnect",
sidebarPanel(
selectInput("in_sel_table_xVar","x Variable:", choices = NULL)
),
mainPanel(
DT::dataTableOutput("table1")
)
),
##### bar plot
tabItem(tabName = "barplot",
sidebarPanel(
selectInput("in_sel_bar_yVar","y Variable:", choices = NULL),
selectInput("in_sel_bar_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotOutput('plot_bar')
)
),
##### piechart
tabItem(tabName = "piechart",
sidebarPanel(
selectInput("in_sel_pie_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotlyOutput('plot_pie')
)
),
##### line plot
tabItem(tabName = "lineplot",
sidebarPanel(
selectInput("in_sel_line_yVar","y Variable:", choices = NULL),
selectInput("in_sel_line_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotlyOutput('plot_line')
)
),
##### scatter plot
tabItem(tabName = "scatterplot",
sidebarPanel(
selectInput("in_sel_scatter_yVar","y Variable:", choices = NULL),
selectInput("in_sel_scatter_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotOutput('plot_scatter'),
textOutput('text_scatter')
)
)
)
)
ui<-dashboardPage(
dashboardHeader(title='Data Analistic Tool'),
sidebar,
body
)
######################3. 서버단 개발 ########################################
server <- function(input, output,session) {
options(warn = -1)
options(shiny.maxRequestSize = 30*1024^2)
# 오라클 디비와 연동을 위한 코드
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
table_query <- 'select table_name from user_tables'
table_list <- dbGetQuery(oracle_db,table_query )
updateSelectInput(session, "in_sel_table_xVar", choices = table_list$TABLE_NAME)
dataload<-reactive({
if(length(table_list)>0)
{ driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
ttt <- input$in_sel_table_xVar
emp_query2 <- paste('select * from ', ttt)
data1 <- dbGetQuery(oracle_db,emp_query2 ) }
else
{ req(input$file1)
file1 = input$file1
data1 = read.csv(file1$datapath) }
# 선택박스 화면 구현
updateSelectInput(session, "in_sel_bar_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_bar_yVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_pie_xVar", choices = data1[,1])
updateSelectInput(session, "in_sel_line_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_line_yVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_scatter_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_scatter_yVar", choices = colnames(data1))
return(data1)
})
####table_format
output$table <- DT::renderDataTable(DT::datatable({
req(input$file1)
file1 = input$file1
data1 = read.csv(file1$datapath)
}))
####dbconnect_format
output$table1 <- DT::renderDataTable(DT::datatable({
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
ttt <- input$in_sel_table_xVar
emp_query2 <- paste('select * from ', ttt)
table_list2 <- dbGetQuery(oracle_db,emp_query2 )
}))
####nomal_bar
output$plot_bar <- renderPlot({
table_in<-dataload()
xdata<-as.factor(table_in[,input$in_sel_bar_xVar])
ydata<-as.factor(table_in[,input$in_sel_bar_yVar])
fdata=data.frame(x=xdata,y=ydata)
ggplot(fdata) +
geom_bar(aes_string(x='x',y='y',fill='x'),stat = "identity",show.legend=F)
})
output$plot_pie <- renderPlotly({
table_in<-dataload()
plot_ly(table_in, labels = ~colnames(table_in)[-1], values=~as.factor( table_in[table_in[,1] == input$in_sel_pie_xVar,-1] ),type='pie')
})
output$plot_line <- renderPlotly({
table_in<-dataload()
x <- list(title = input$in_sel_line_xVar)
y <- list(title = input$in_sel_line_yVar)
plot_ly(data = table_in,x=~table_in[,input$in_sel_line_xVar],y=~table_in[,input$in_sel_line_yVar],type='scatter',mode='dot')%>%
layout(xaxis = x, yaxis = y)
})
output$plot_scatter <- renderPlot({
table_in<-dataload()
xyplot(table_in[,input$in_sel_scatter_yVar]~table_in[,input$in_sel_scatter_xVar], grid=T,type=c('p','smooth'),col.line='darkorange',lwd=2, xlab=input$in_sel_scatter_xVar,ylab=input$in_sel_scatter_yVar)
})
output$text_scatter <- renderText({
table_in<-dataload()
paste("The correlation between the two is: ", cor(table_in[,input$in_sel_scatter_yVar],table_in[,input$in_sel_scatter_xVar]))
})
}
######################### 4. 샤이니 실행 ###############################
shinyApp(ui = ui, server = server)
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
선생님의 db연동 + 그래프 까지 다되는
완성코드
■주의점
데이터 큰 테이블 그래프 그릴때는 오래걸려도 기다려줄 것
강제종료 시키면 R자체가 깨져버려서
싹 밀고 처음부터 다시 설치해야함
############## set this file location to working directory ##########################
packages <- 'rstudioapi'
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
library('rstudioapi')
current_dir<-dirname(rstudioapi::getSourceEditorContext()$path)
setwd(current_dir)
package_in<-function(p_name,option=1){
packages <- p_name
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
if (option==1){
library(p_name,character.only = TRUE)
}
}
###########################1. 패키지 설치##########################################
package_in('shinydashboard')
package_in('shiny')
package_in('ggplot2')
package_in('plotly')
package_in('lattice')
package_in('RJDBC')
package_in('DBI')
######################### 2. 화면 개발 ###########################################
sidebar <- dashboardSidebar(
sidebarMenu(
fileInput("file1", "Choose CSV File",
multiple = FALSE,
accept = c("text/csv",".xlsx",".txt",
"text/comma-separated-values,text/plain",
".csv")),
menuItem("테이블",
menuSubItem('Tableformat',tabName='tableformat'),
menuSubItem('Dbconnect',tabName='dbconnect') ),
menuItem("그래프",
menuSubItem('Barplot',tabName='barplot'),
menuSubItem('Piechart',tabName='piechart'),
menuSubItem('Lineplot',tabName='lineplot'),
menuSubItem('Scatterplot',tabName='scatterplot')
)
)
)
body <- dashboardBody(
tabItems(
##### table_format
tabItem(tabName = "tableformat",
mainPanel(
DT::dataTableOutput("table")
)
),
##### dbconnect_format
tabItem(tabName = "dbconnect",
sidebarPanel(
selectInput("in_sel_table_xVar","x Variable:", choices = NULL)
),
mainPanel(
DT::dataTableOutput("table1")
)
),
##### bar plot
tabItem(tabName = "barplot",
sidebarPanel(
selectInput("in_sel_bar_yVar","y Variable:", choices = NULL),
selectInput("in_sel_bar_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotOutput('plot_bar')
)
),
##### piechart
tabItem(tabName = "piechart",
sidebarPanel(
selectInput("in_sel_pie_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotlyOutput('plot_pie')
)
),
##### line plot
tabItem(tabName = "lineplot",
sidebarPanel(
selectInput("in_sel_line_yVar","y Variable:", choices = NULL),
selectInput("in_sel_line_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotlyOutput('plot_line')
)
),
##### scatter plot
tabItem(tabName = "scatterplot",
sidebarPanel(
selectInput("in_sel_scatter_yVar","y Variable:", choices = NULL),
selectInput("in_sel_scatter_xVar","x Variable:", choices = NULL)
),
mainPanel(
plotOutput('plot_scatter'),
textOutput('text_scatter')
)
)
)
)
ui<-dashboardPage(
dashboardHeader(title='Data Analistic Tool'),
sidebar,
body
)
######################3. 서버단 개발 ########################################
server <- function(input, output,session) {
options(warn = -1)
options(shiny.maxRequestSize = 30*1024^2)
# 오라클 디비와 연동을 위한 코드
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
table_query <- 'select table_name from user_tables'
table_list <- dbGetQuery(oracle_db,table_query )
updateSelectInput(session, "in_sel_table_xVar", choices = table_list$TABLE_NAME)
dataload<-reactive({
if(length(table_list)>0)
{ driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
ttt <- input$in_sel_table_xVar
emp_query2 <- paste('select * from ', ttt)
data1 <- dbGetQuery(oracle_db,emp_query2 )
}
else
{ req(input$file1)
file1 = input$file1
data1 = read.csv(file1$datapath) }
# 선택박스 화면 구현
updateSelectInput(session, "in_sel_bar_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_bar_yVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_pie_xVar", choices = data1[,1])
updateSelectInput(session, "in_sel_line_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_line_yVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_scatter_xVar", choices = colnames(data1))
updateSelectInput(session, "in_sel_scatter_yVar", choices = colnames(data1))
return(data1)
})
####table_format
output$table <- DT::renderDataTable(DT::datatable({
req(input$file1)
file1 = input$file1
data1 = read.csv(file1$datapath)
}))
####dbconnect_format
output$table1 <- DT::renderDataTable(DT::datatable({
driver <- JDBC('oracle.jdbc.driver.OracleDriver', 'ojdbc6.jar')
oracle_db <- dbConnect(driver, 'jdbc:oracle:thin:@//127.0.0.1:1521/xe', 'scott', 'tiger')
ttt <- input$in_sel_table_xVar
emp_query2 <- paste('select * from ', ttt)
table_list2 <- dbGetQuery(oracle_db,emp_query2 )
}))
####nomal_bar
output$plot_bar <- renderPlot({
table_in<-dataload()
xdata<-as.factor(table_in[,input$in_sel_bar_xVar])
ydata<-as.factor(table_in[,input$in_sel_bar_yVar])
fdata=data.frame(x=xdata,y=ydata)
ggplot(fdata) +
geom_bar(aes_string(x='x',y='y',fill='x'),stat = "identity",show.legend=F)
})
output$plot_pie <- renderPlotly({
table_in<-dataload()
plot_ly(table_in, labels = ~colnames(table_in)[-1], values=~as.factor( table_in[table_in[,1] == input$in_sel_pie_xVar,-1] ),type='pie')
})
output$plot_line <- renderPlotly({
table_in<-dataload()
x <- list(title = input$in_sel_line_xVar)
y <- list(title = input$in_sel_line_yVar)
plot_ly(data = table_in,x=~table_in[,input$in_sel_line_xVar],y=~table_in[,input$in_sel_line_yVar],type='scatter',mode='dot')%>%
layout(xaxis = x, yaxis = y)
})
output$plot_scatter <- renderPlot({
table_in<-dataload()
xyplot(table_in[,input$in_sel_scatter_yVar]~table_in[,input$in_sel_scatter_xVar], grid=T,type=c('p','smooth'),col.line='darkorange',lwd=2, xlab=input$in_sel_scatter_xVar,ylab=input$in_sel_scatter_yVar)
})
output$text_scatter <- renderText({
table_in<-dataload()
paste("The correlation between the two is: ", cor(table_in[,input$in_sel_scatter_yVar],table_in[,input$in_sel_scatter_xVar]))
})
}
######################### 4. 샤이니 실행 ###############################
shinyApp(ui = ui, server = server)
'R' 카테고리의 다른 글
| if문 (0) | 2019.03.09 |
|---|---|
| R을 활용한 머신러닝 이란? (0) | 2019.03.09 |
| 그래프(사분위수, 지도그래프, 워드클라우드) (0) | 2019.03.09 |
| 그래프(산포도, 구글 그래프) (0) | 2019.03.09 |
| 그래프(원형,막대) (0) | 2019.03.09 |
