A while back I wrote a blog post on The Power of R, and I want to extend that a little now by looking at how easy it is to source data from an APEX Cloud instance directly into a R program. To give a bit of background on this, for the past few weeks I’ve been looking after 5 baby rabbits that were rescued by our friend who is a vet. We had to weigh them daily, and keep the vets updated with these weights, so I knocked up a very simple APEX application on apex.oracle.com which let me add weights on my mobile. You can take a look at the app here (no login required) and see how they have progressed! Anyway, this got me thinking… if I have this data in APEX Cloud instance, how difficult can it be to access that from other systems, such as an R script. Well… not difficult at all it would seem. First of all I created a REST Service using the RESTful Services utility in APEX, which simply returns the weight data in JSON format. Then with a bit of help from Google, I found I could use the
jsonliteR packages to retrieve the data into a new data frame. It was a simple as:
require("httr") require("jsonlite") rest_call Now we can see we have the data (I made the headings a bit more friendly and typed the Weight_Date field), plus the reference URL:
> head(rabs) Name Weight Weight_Date R1 1 Charlie 168 2018-07-28 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 2 Charlie 179 2018-07-29 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 3 Charlie 192 2018-07-30 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 4 Tinker 157 2018-07-29 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 5 Tinker 190 2018-07-31 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 6 Tilly 132 2018-07-28 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 >Then using ggplot, I could very quickly produce a graph based on that data.
require("ggplot2") ggplot(data=rabs, aes(x=rabs$Weight_Date,y=rabs$Weight, col=rabs$Name)) + geom_line() + labs(colour="Name",x="Date", y="Weight (g)")I can also easily see statistics about any individual bunny!
> charlie summary(charlie$Weight) Min. 1st Qu. Median Mean 3rd Qu. Max. 168.0 262.5 358.0 357.5 438.0 550.0The great thing about this though of course, is that my data set in R is live – there’s no manual downloading, no intermediate spreadsheets, and I could of course have made the data secured by authentication had I wanted to. So whilst APEX is a wonderful tool with great power and flexibility, it’s good to know it’s equally as easy to integrate it with other tools as well. Don’t forget to keep a watch on the progress of the little bunnies! 🙂