Fetching stock data using a parameterised Execute R operator

I'm currently delivering data science lectures at the University of Chichester and RapidMiner is part of what I use to teach. And very good it is too. I recently found myself helping my students to get some up to date stock market data. Rather than manually downloading this, I thought I would use RapidMiner with the tidyquant R package and do it automatically. The Finance and Economics Extension seems to be out of date so isn't an option.

My idea was to define a list of stock symbols such as "AAPL", "BTC-USD" and so on and run the Execute R operator in a loop with each symbol individually.

It turns out there isn't a way to parameterise the Execute R operator so I had to invent one.

Basically, I use the Loop Parameters operator to set multiple values for a macro located inside it. This macro is used to create a one row example set with the value of the macro. This example set is then passed to the Execute R operator where the R script uses it as a parameter to drive the rest of the script. It's clunky but it works.

This approach could be adapted to allow R scripts to be run as part of a more complex modelling process. Relatively tough to do but feasible.

Here's a link to the process.

You'll need the R Scripting extension and you will also need to ensure that R is running on your machine with the data.table, tidyverse and tidyquant R packages all installed.

If RapidMiner enhances the Execute R operator to take parameters, (which would be a good enhancement), then this work around will not be needed anymore.

Keras + RapidMiner + digit recognition = 97% accuracy

I've successfully created a process using RapidMiner and Keras to recognise the MNIST handwritten digits with a headline accuracy of 97% on unseen data.

You can download the process here.

It requires R and Keras to be installed - an exercise for the reader.

The main features of the process are

• R is used to get the MNIST data and create a training set and a test set. I use R to add the label to the data to make a single example set for the two cases rather than have separate structures for the data and the labels. This is a big strength of RapidMiner because all the data and labels are in one place. 
• The data is restructured in R to change 3d tensors of shape (60000, 28, 28) to 2d tensors of shape (60000, 768). The 3d tensor represents the images each of size 28 by 28 pixels. RapidMiner example sets are 2d tensors but these are OK to feed into the Keras part of the process.
• The Keras part of the model has the following characteristics
• The input shape is (784,), this matches the number of columns in the 2d tensor.
• The loss parameter is set to "categorical_crossentropy" and the optimizer is set to "RMSprop".
• There are 2 layers in the Keras model. The first is "dense" with 512 units and activation set to "relu". The second is "dense" with 10 units and activation set to "softmax". The 10 in this case is the number of different possible values the label can be.
• The "validation_split" parameter is set to 0.1 so that a loss is calculated on a small part of the training data. This leads to validation loss results in the output which is used to see when over-fitting is happening.

Here is a screenshot of the history from a large run (this is output from the Keras model as an example set).

The training loss (in blue) decreases systematically as the model learns the training data more and more. The loss against the validation data (in red) shows worse performance as the number of epochs increases and the variation between epochs is evidence that perhaps I should use a larger training fraction. Nonetheless, only a small number of epochs would be enough to get a model that would perform well on unseen data.

The Keras model does not use convolution layers (an exercise for a later post) but despite this, it performs very well. Here is the confusion matrix using 3 epochs.

This is a very good result and shows the power of deep learning. It's gratifying that RapidMiner supports it.

As time permits, a future post will look at using convolution layers to see what improvements could be achieved. I may also do some systematic experiments to check how validation loss measured during training maps to actual loss on unseen data.

Visualising the MNIST numbers data

Keras comes with some built in functions to obtain the MNIST dataset created by the National Institute of Standards and Technology. As far as I can tell, it's not possible to get access to these from within RapidMiner but never fear, here is a process that can do it.

It uses R and obviously requires Keras to be have been installed. I'll leave that to the reader to get right.

The process also chooses one of the digits and casts it into a form that allows it to be displayed. It does this using the "Windowing" operator followed by "De-Pivot" to transform the matrix like data into x,y,z tuples.

Here's the 6th digit displayed using a block chart. This looks like a 2.

I've already used R with Keras to create a classifier that can recognise these digits. This is my first step towards using Keras in RapidMiner to build a classifier to do the same job.

Genetic Algorithms with R and Shiny

Following on from this post, here's an application that uses a genetic algorithm to find the maximum of a complex function as the inputs to it are varied.

I've implemented this in R and Shiny and the application is hosted on shinyapps.io here. The application lets a user try to beat the computer and find the optimum by brute force alone. Needless to say, there's no chance of a human managing this in any meaningful time.

As with the previous post, the Rastrigin function is being used as the function to which the optimum inputs are to be sought. The R package GA is being used to find the optimum. Gratifyingly, hardly any code is needed; it is on GitHub here.

The progress of the genetic algorithm towards the goal can be plotted. Here's an example.

As with RapidMiner, convergence towards the optimum is quick, and it usually happens in about 30 generations.

In summary, of course R can do this, but for me the cool part is how concise the code is and by using Shiny how easy it is to show to others.

R packages and Shiny

Despite this blog's title containing RapidMiner, most of what I have been doing recently involves R. I maintain a GitHub repository and at the last count there are more than 50 R packages stored there. Most are private but a few are public.

I can't reveal the private ones but there are a couple of play repositories that I have published as Shiny applications.

The first one is the POTUS Progress Pie - originally posted as an idea on the HalfBakery - a site I visit a lot - see the original idea here and the R Shiny application here.

The second one shows a genetic algorithm finding the maximum to a complex function and again uses Shiny. Here's the application. Move the "audio 1" through "audio 4" sliders to try and maximise the score. Brute force is usually not an option so selecting the Find the Best option will show you the slider settings. Selecting the "Random Choice" button chooses a new function to maximise.

Coming up and in the spirit of mentioning RapidMiner, I will post the evolutionary process I used to help me win RapidMiner's recent contest...

Reading from a SQL Server database

Reading from a SQL Server database is easy using R.

Here's a process that shows how to do this from within a RapidMiner process.

Of course, you can use the built in "Read Database" operator to read from a database, but there are restrictions in the community version. By using R you can get partially round the restrictions but you should always be aware of your license agreement. Just because you can get round the license does not mean that the terms no longer apply. If you do something that would normally trigger the purchase of an additional license then you still need to. I'm not a lawyer thankfully but, you have been warned.

Having said that, there are situations where you have to try things to prove viability and get political buy-in before committing to a more serious plan where money is to be spent. Political buy-in, as everyone knows, can sometimes takes a very long time and even the most trivial objection can completely de-rail progress. Removing the ability to make a full prototype is just such a potential trivial objection.

Having said all of that, the method the process uses here will have some subtle differences in the way it interacts with the database when compared to the "Read Database" operator. This means it might not work for some reason as yet unknown. Simple advice, don't rely on it.

Enough words, on with the process.

The process has two parts, the first sets some macros that are used within the second. It's a little known fact that you can use macros in this way but it's extremely powerful and allows the code to work in lots of places. The macros themselves are shown in the following table.



Change these to match what you have in your environment. Note that I am using SQL Server authentication so this means you have to set up your environment like this. I am led to believe that built-in authentication is possible but I have not tried it.

The R code itself is shown here.



Additional points:

1. Install the RJDBC package into your environment, rJava may also be required.
2. Download the Microsoft JDBC drivers from here (note that care is always needed with downloads such as these because the vendors keep changing their Web sites).
3. If you are running on Ubuntu, the process will still work but there are some changes to do as shown in the R code.
4. I have not tried it on a Mac.
5. Change the query to whatever you want. The example here queries the system table.

The end result is an example set. The query shown in the example yields this.

You will see that the attribute names have been created automatically and a basic mapping to types has been done. The following shows part of the statistics for this example set.

One mapping that would need additional downstream work is the create_date attribute. It looks like it has been transformed into a polynominal. Closer inspection would, no doubt, reveal other foibles.

The example set can then be used in the normal way 

In summary, you can see that it is very easy to access SQL Server using R. It is therefore easy to do it from within RapidMiner.

Unit testing RapidMiner processes using R

I've used R a lot over the last few years. Partly in my day to day activity, but also as part of my efforts as co-editor with Dr. Markus Hofmann for our recent new book "Text Mining and Visualization: Case Studies Using Open-Source Tools", available at all good bookstores ;)

I have also made some R packages and these even contain unit tests implemented with the splendid testthat package. Any minute now, I might even become an R expert.

Unit tests are vital to the health and sanity of the creator of any solution. Once something gets big and is subject to a lot of change, it is extremely important to know whether changes haven't accidentally broken something. RapidMiner processes are no different and it struck me that it would be worth implementing some basic unit testing for these. It could be done using RapidMiner itself but, for fun, I thought I would use R.

Here is a small example process that illustrates this. It starts by estimating the performance of a classifier on the Iris data set. The estimated performance is converted to an example set using the "Performance to Data" operator and is then passed to the "Execute R" operator.

This is the performance vector.

The R script confirms that all the parts of the performance vector are as expected. It uses a base R function called "all.equal()" to do this (I decided not to use testthat to avoid having to install the library so others can get going more quickly).

Here is the R script.

As you can see, the script checks that all the parts of the example set are as expected. For example, this line

confirms that the values for accuracy and kappa are 0.94 and 0.91 respectively with a tolerance of 0.01. The R script then outputs a data frame with the results. When all is well the result looks like this.

By changing the parameters of the earlier operators, the results will change. This can be picked up by examination of the result. Here is an example when the number of cross validations is set to 3.

In other words, the performance has changed and this gets detected automatically. Obviously, this is a simple example but you can see how it could be extended.

As I mentioned above, you could do this in pure RapidMiner but it would need quite a number of operators to realise it. The R integration in RapidMiner is relatively easy to use and so it deserves the time of day when considering how to tackle problems.

R also has a tremendous range of packages and a future post will touch cautiously on how to access a database directly.

Reading and writing xlsx files in RapidMiner

The other day, I stumbled on a nifty way to import and export xlsx files into and out of RapidMiner.

There's an R package called "xlsx" that is able to read and write Excel files including Excel 2007 xlsx format. 

Here's a process that loads the iris data, writes this to an xlsx file at c:\temp\iris.xlsx and then reads it back again.

(Note: after the first run, comment out the line in the R script that installs the xlsx package to avoid downloading the R package again)

Edit: 12/7/12: The new version of RapidMiner supports this - hurrah!

Reading wav files into RapidMiner using R

I prefer using RapidMiner to create complex processes. It's easier to get the big picture perspective of a process using the connected operator view. This means I tend to try and bring things into RapidMiner from elsewhere where possible.

An example of this is reading wav files; very easy in R. Here's an example process that uses R to do this and imports the results into RapidMiner.

You will need to uncomment the line in the R script to fetch the tuneR library.

You will also have to provide the path to the wav file you want to import.

There may be wav files it cannot read but this is an exercise for the reader.

Counting clusters: part R

Here's an example process that uses an R script called from RapidMiner to perform clustering and provide a silhouette validity index.

As before, it uses the same artificial data as for the previous examples; namely 1000 data points in three dimensions with 8 clusters.

The script uses the R package "cluster". This contains the algorithm "partitioning around medoids" which the documentation describes as a more robust k-means.

The process iterates over values of k from 2 to 20, passes the data to R for clustering and generation of an average silhouette value. This allows the optimum value for k to be determined. The "correct" answer is 8 but this may not correspond to the best cluster using the validity measure owing to the random number generator which causes the clusters to differ between each run.

Some points to note

• The R script installs the package and this causes a pop up dialog box to appear. Select the mirror from which to download the package. Comment out the "install.packages" line to stop this (or read the R documentation to work out how to test for the presence of the library before attempting the install).
• The R script takes multiple inputs and these appear as R data frames.
• The output from the R script must be a data frame.
• The returned example set contains the validity measure and this is picked up for logging using the "Extract Performance" operator.
• The order of the processes before the script is important to ensure the correct value of k is passed in. This can be done using the "show and alter operator execution order" feature on the process view.
• The operator "Generate Data By User Specification" is used to create an example set to contain the value of k

The results should look something like this.

A value near 1 is what is being looked for and indicates compact, well separated clusters. The "correct" answer is 8 and the result supports this.