0

I am trying to plot a graph using R which is populated by MySQL query results. I have the following code:

rs = dbSendQuery(con, "SELECT BuildingCode, AccessTime from access")
data = fetch(rs, n=-1)
x = data[,1]
y = data[,2]
cat(colnames(data),x,y)

This gives me an output of:

BuildingCode AccessTime TEST-0 TEST-1 TEST-2 TEST-3 TEST-4 14:40:59 07:05:00 20:10:59 08:40:00 07:30:59

But this is where I get stuck. I have idea how to pass the "cat" data into an R plot. I have spend hours searching online and most of the examples of R plots I have come across use read.tables(text=""). This is not feasible for me as the data has to come from a database and not be hard coded in. I also found something about saving the output as a CSV but MySQL can not overwrite existing files so after the code was executed once I was unable to do it again as a file already existed.

My question is, how can I use the "cat" data (or another way of doing it if there is a better way) to plot a graph using data that isn't hard coded?

Note: I am using RApache as my web server and I have installed the Brew package.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Dom Abbott
  • 1,157
  • 2
  • 8
  • 11
  • Why can't you directly use `plot(x=data[,1],y=data[,2],xlab=colnames(data)[1],ylab=colnames(data)[2])` ? – vrajs5 Jun 12 '14 at 10:09
  • I will try it now. I am new to R so I'm still trying to get my head around how it works. – Dom Abbott Jun 12 '14 at 10:16
  • I have just tried running it in RStudio and got these messages: `Error in plot.window(...) : need finite 'xlim' values In addition: Warning messages: 1: In xy.coords(x, y, xlabel, ylabel, log) : NAs introduced by coercion 2: In xy.coords(x, y, xlabel, ylabel, log) : NAs introduced by coercion 3: In min(x) : no non-missing arguments to min; returning Inf 4: In max(x) : no non-missing arguments to max; returning -Inf 5: In min(x) : no non-missing arguments to min; returning Inf 6: In max(x) : no non-missing arguments to max; returning -Inf` – Dom Abbott Jun 12 '14 at 10:20
  • It seems your data has some missing values or null... What you wanna do about null values? – vrajs5 Jun 12 '14 at 10:25
  • The data I'm using is just test data and as far as I'm aware (as I created it) there is no null values? But lets just say there is, how would I fix this? – Dom Abbott Jun 12 '14 at 10:29
  • Try this if that works I will add proper answer... `data = data[complete.cases(data),];plot(x=data[,1],y=data[,2],xlab=colnames(data)[1],ylab=colnames(data)[2])` – vrajs5 Jun 12 '14 at 10:45
  • Nope, in RStudio it gives me a blank terminal and in RApahe it gives a server error – Dom Abbott Jun 12 '14 at 10:52
  • When I use add `plot(x=data[,1],y=data[,2],xlab=colnames(data)[1],ylab=colnames(data)[2])` in RApache I get `[1] "cannot open file 'Rplots.pdf'"` ... I've tried dev.off() in several places on the code but it doesn't fix it, just gives me a null device error – Dom Abbott Jun 12 '14 at 10:57

1 Answers1

0

Make the plot using R and just pass the path to the file back in cat

<%
## Your other code to get the data, assuming it gets a data.frame called data

## Plot code
library(Cairo)
myplotfilename <- "/path/to/dir/myplot.png"
CairoPNG(filename = myplotfilename, width = 480, height = 480)
plot(x=data[,1],y=data[,2])
tmp <- dev.off()
cat(myplotfilename)
%>
  • I have tried adding this and I get the error "There is no package called 'Cairo'" ... I have installed it from R using "install.packages('Cairo')" and I am calling "library(Cairo)" as you said to – Dom Abbott Jun 12 '14 at 12:13
  • As I mention in my comment above I have already installed Cairo but I still get the error. – Dom Abbott Jun 12 '14 at 13:04
  • install it as root and restart Apache –  Jun 12 '14 at 13:07