R. Programming with DataBase MySQL

R RMySQL.MD

R GUI

• run ผ่าน C:\Program Files\R\R-3.3.2\bin\i386\rgui.exe
• ทำการ update package
• install Package RMySQL และ ggplot2 และ scatterplot3d , rgl

RMySQL

Install and Using

• https://www.r-bloggers.com/accessing-mysql-through-r/
• https://www.tutorialspoint.com/r/r_database.htm

Data.frame in R

• http://www.r-tutor.com/r-introduction/data-frame

ggplot2 with MySQL by RMySQL

• http://chris.eidhof.nl/post/analyzing-a-mysql-database-with-r/
• scatter diagram http://www.statmethods.net/graphs/scatterplot.html

RGL Library Document

• https://cran.r-project.org/web/packages/rgl/vignettes/rgl.html
• Graphics 3D ! - http://www.sthda.com/english/wiki/a-complete-guide-to-3d-visualization-device-system-in-r-r-software-and-data-visualization

Encoding Problem

• using / solved http://stackoverflow.com/questions/29440733/wrong-character-encoding-in-r-console

Problem with Fetch

• Query multiple time - http://stackoverflow.com/questions/21167070/how-to-query-multiple-times-and-close-the-connection-at-the-end

แสดง graph ให้แกน y ไม่ใช่ scientific notation

• http://r.789695.n4.nabble.com/axis-labels-defaulting-to-scientific-notation-td2320803.html
• ให้เซ็ต options(scipen=3)

default graphics plot

• http://www.statmethods.net/graphs/line.html
• par https://stat.ethz.ch/R-manual/R-devel/library/graphics/html/par.html
• graph type http://www.statmethods.net/graphs/line.html
• type="p" คือ point , "l" == line , "h" == histogram
• graphics parameter -> http://www.statmethods.net/advgraphs/parameters.html !!!
• cex == pixcel size , eg. cex=0.4 -> http://stackoverflow.com/questions/2579995/control-the-size-of-points-in-an-r-scatterplot

SampleCode.R

' install.packages("RMySQL") '
mypass <- 'mypass'

library(RMySQL)

' options(encoding="utf-8") '
Sys.setlocale("LC_CTYPE", "Thai")
mydb = dbConnect(MySQL(), user = 'root', password = mypass, dbname = 'taxbreak',
   host = 'localhost')
'-- Not OK --  dbSendQuery(mydb,"SET NAMES utf8 ");  '
dbSendQuery(mydb,"SET NAMES 'tis620' ");
dbListTables(mydb)
dbListFields(mydb, 't_data')
qry1 = dbGetQuery(mydb, "select * from table_data limit 0,2")


'----------------------'
qry1 = dbGetQuery(mydb, "select v25, v27 from table_data ")

' row  count , and column count  '
nrow(qry1) 
ncol(qry1) 
v25 <- qury["v25"]
v27 <- qury["v27"]
v25 
v27
head(v25)
head(v27)

h25 <- head(v25)
h27 <- head(v27)
h25 
h27

' convert from character to numeric '
' http://stackoverflow.com/questions/2288485/how-to-convert-a-data-frame-column-to-numeric-type ' 

df25 <- as.numeric(as.character(qry1$v25))
df27 <- as.numeric(as.character(qry1$v27))
df25 
df27 
summary(df25)
summary(df27)

dbDisconnect( mydb )

TestData.R

mypass <- 'mypass'
library(RMySQL)
Sys.setlocale("LC_CTYPE", "Thai")
mydb = dbConnect(MySQL(), user = 'root', password = mypass, dbname = 'taxbreak',
   host = 'localhost')
dbSendQuery(mydb,"SET NAMES 'tis620' ");
q1 = dbGetQuery(mydb, "SELECT id,  CAST(v25 AS DECIMAL(10,2)) as d25 , 
							CAST(v27 AS DECIMAL(10,2)) as d27   from table_data  ")

a <- summary(d25)
amax <- a["Max."]
attach(q1)
plot(id, d25 , main="Data Distribution By ID", 
  	xlab="ID ", ylab="Column Value ", pch=19 , col="red" 
  	, ylim=c(-20, amax) 
  	)

# ===============
a <- summary(d27)
amax <- a["Max."]
attach(q1)
plot(id, d27 , main="Data Distribution By ID", 
  	xlab="ID ", ylab="Column Value ", pch=19 , col="red" 
  	, ylim=c(-20, amax) 
  	)

TestData5.R

myuser <- 'root'
mypass <- 'mypass'
mydb   <- 'db1'
library(RMySQL)
Sys.setlocale("LC_CTYPE", "Thai")
mydb = dbConnect(MySQL(), user = myuser , password = mypass, dbname = mydb ,
   host = 'localhost')
dbSendQuery(mydb,"SET NAMES 'tis620' ");
q1 = dbGetQuery(mydb, "SELECT id,  CAST(v27 AS DECIMAL(10,2)) as d27 , 
							CAST(v28 AS DECIMAL(10,2)) as d28   from table_data  ")


#amax <- summary(d27)["Max."]
attach(q1)
options(scipen=3) 
plot(id, d27 , main="Data Distribution By ID", 
  	xlab="ID ", ylab="Column Value ", pch=19 , col="blue" , type="p"  	)

#axis(2,at=marks,labels=marks)

# Program Pause Here 

# ===============
#amax <- summary(d28)["Max."]
attach(q1)
options(scipen=3) 
par(ylim = ( summary(d28)["Min."] , summary(d28)["Max."]))
plot(id, d28 , main="Data Distribution By ID", 
  	xlab="ID ", ylab="Column Value ", pch=19 , col="blue" , type="p"  	)

testData6.R

library(RMySQL)
Sys.setlocale("LC_CTYPE", "Thai")
mydb = dbConnect(MySQL(), user = myuser , password = mypass, dbname = mydb ,
   host = 'localhost')
dbSendQuery(mydb,"SET NAMES 'tis620' ");

q1 = dbGetQuery(mydb, "SELECT id,  CAST(v27 AS DECIMAL(10,2)) as d27  
							from table_data Where CAST(v27 AS DECIMAL(10,2)) <> 0 ")


#amax <- summary(d27)["Max."]
attach(q1)
options(scipen=3) 
ylim1 <- c( summary(d28)["Min."] , summary(d28)["Max."])
ylim1 <- c( summary(d28)["Min."] , summary(d28)["Max."]/2)
ylim1 <- c( summary(d28)["Min."] , summary(d28)["Max."]/4)
plot(id, d27 , main="Data Distribution By ID", 
  	xlab="ID ", ylab="Column Value ", pch=19 , col="blue" , type="p" 
  	,cex = 0.4  ,ylim  = ylim1  )

Trying2.R

# install.packages("RMySQL") '
# install.package("ggplot2") '
mypass <- 'mypass'

library(RMySQL)

# options(encoding="utf-8") '
Sys.setlocale("LC_CTYPE", "Thai")
mydb = dbConnect(MySQL(), user = 'root', password = mypass, dbname = 'taxbreak',
   host = 'localhost')
#'-- Not OK --  dbSendQuery(mydb,"SET NAMES utf8 ");  '
dbSendQuery(mydb,"SET NAMES 'tis620' ");
dbListTables(mydb)
dbListFields(mydb, 't_data')


#'----------------------'
qry1 = dbGetQuery(mydb, "SELECT id,  CAST(v25 AS DECIMAL(10,2)) as d25 , 
							CAST(v27 AS DECIMAL(10,2)) as d27   from table_data  ")

summary(qry1$d25)
summary(qry1$d27)
attach(qry1)
qplot(qry1$d25);

plot(id,d25, main="Scatterplot Example", 
  	xlab="ID ", ylab="v25 value", pch=19)


library(scatterplot3d)
attach(qry1)
scatterplot3d(id,d25,d27, main="3D Scatterplot")

# Spinning 3d Scatterplot
library(rgl)
plot3d(id, d25, d25, col="red", size=3)


dbDisconnect( mydb )