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.

Rabbit App Reporting  Rabbit App Maintenance

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.


REST Query

Then with a bit of help from Google, I found I could use the httr and jsonlite R packages to retrieve the data into a new data frame. It was a simple as:


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.

ggplot(data=rabs, aes(x=rabs$Weight_Date,y=rabs$Weight, col=rabs$Name)) + geom_line() + labs(colour="Name",x="Date", y="Weight (g)")

ggplot Graph

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.0 

The 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! :)

Bunnies1 Bunnies2