Data Preparation Tool for Machine Learning

Regardless of machine learning library you use, the data preparation is the first and one of the most important step in developing predictive models. It is very often case that the data supposed to be used for the training is dirty with lot of unnecessary columns, full of missing values, un-formatted numbers etc. Before training the data must be cleaned and properly defined in order to get good model. This is known as data preparation. The data preparation consist of cleaning the data, defining features and labels, deriving the new features from the existing data, handling missing values, scaling the data etc.  It can be concluded that the total time we spend in ML modelling,the most of it is related to data preparation.

In this blog post I am going to present the simple tool which can significantly reduce the preparation time for ML. The tool simply loads the data in to GUI, and then the user can define all necessary information. Once the data is prepared user can store the data it to files which can be then directly imported into ML algorithm such as CNTK.

The following image shows the ML Data Preparation Tool main window.

From the image above, the data preparation can be achieved in several steps.

  1. Load dirty data into ML Prep Tool, by pressing Import Data button
  2. Transform the data by providing the flowing:
    1. Type – each column can be:
      1. Numeric – which holds continuous numeric values,
      2. Binary – which indicates two class categorical data,
      3. Category – which indicates categorical data with more than two classes,
      4. String – which indicate the column will not be part of training and testing data set,
    2. Encoding – in case of Binary and Category column type, the encoding must be defined. The flowing encoding is supported:
      1. Binary Encoding with (0,1) – first binary values will be 0, and second binary values will be 1.
      2. Binary encoding with (-1,1) – first binary values will be -1, and second binary values will be 1.
      3. Category Level- which each class treats as numeric value. In case of 3 categories(R,G, B), encoding will be (0,1,2)
      4. Category 1:N- implements One-Hot vector with N columns. In case of 3 categories(R,G, B), encoding will be R =  (1,0,0),G =  (0,1,0), B =  (0,0,1).
      5. Category 1:N-1(0) – implements dummy coding with N-1 columns. In case of 3 categories(R, G, B), encoding will be R =  (1,0),G =  (0,1), B =  (0,0).
      6. Category 1:N-1(-1) – implements dummy coding with N-1 columns. In case of 3 categories(R, G, B), encoding will be R =  (1,0),G =  (0,1), B =  (-1,-1).
    3. Variable – defines features and label. Only one label, and at least one features can be defined. Also the column can be defined as Ignore variable, which will skip that column.  The following options are sported:
      1. Input – which identifies the column as feature or predictor,
      2. Output – which identifies the column as label or model output.
    4. Scaling – defines column scaling. Two scaling options are supported:
      1. MinMax,
      2. Gauss Standardization,
    5. Missing Values – defines the replacement for the missing value withing the column. There are several options related to numeric and two options (Random and Mode ) for categorical type.
  3. Define the testing data set size by providing information of row numbers or percent.
  4. Define export options
  5. Press Export Button.

As can be seen this is straightforward workflow of data preparation.

Besides the general export options which can be achieved by selecting different delimiter options, you can export data set in to CNTK format, which is very handy if you play with CNTK.

After data transformations, the user need to check CNTK format int the export options and press Export in order to get CNTK training and testing files, which can be directly used in the code without any modifications.

Some of examples will be provided int he next blog post.

The project is hosted at GitHub, where the source code can be freely downloaded and used at this location: .

In case you want only binaries, the release of version v1.0 is published here:


Using CNTK and C# to train Mario to drive Kart


In this blog post I am going to explain one of possible way how to implement Deep Learning ML to play video game. For this purpose I used the following:

  1.  N64 Nintendo emulator which can be found here,
  2. Mario Kart 64 ROM, which can be found on internet as well,
  3. CNTK – Microsoft Cognitive Toolkit
  4. .NET Framework and C#

The idea behind this machine learning project is to capture images together with action, while you play Mario Kart game. Then captured images are transformed into features of training data set, and action keys into label hot vectors respectively.  Since we need to capture images, the emulator should be positioned at fixed location and size during playing the game, as well as during testing algorithm to play game. The flowing image shows N64 emulator graphics configuration settings.


Also the N64 emulator is positioned to Top-Left corned of screen, so it is easier to capture the images.

Data collection for training data set

During image captures game is played as you would play normally. Also no special agent, not platform is required.

In .NET and C# it is implemented image capture from the specific position of screen, as well as it is recorded which keys are pressed during game play. In order to record keys press, the code found here is modified and used.

The flowing image shows the position of N64 emulator with playing Mario Kart game (1), the windows which is capture and transform the image (2), and the application which collect images, and key press action and generated training data set into file(3).


The data is generated on the following way:

  • each image is captured, resized to 100×74 pixels and gray scaled prior to be transformed and persisted to data set training file.
  • before image is persisted the hotkey of action key press is recorded and connected to image.

So the training data is persisted into CNTK format which consist of:

  1. |label – which represent 5 component hot vector, indicate: Forward, Break, Forward-Left, Forward-Right and None (1 0 0 0 0)
  2. |features consist of 100×74 numbers which represent pixels of the images.

The following data sample shows how training data set are persisted in the txt file:

|label 1 0 0 0 0 |features 202 202 202 202 202 202 204 189 234 209 199...
|label 0 1 0 0 0 |features 201 201 201 201 201 201 201 201 203 18...
|label 0 0 1 0 0 |features 199 199 199 199 199 199 199 199 199 19...
|label 0 0 0 1 1 |features 199 199 199 199 199 199 199 199 199 19...

Since my training data is more than 300 000 MB of size, I provided just few BM sized file, but you can generate file as big as you wish with just playing the game, and running the flowing code from Program.cs file:

await GenerateData.Start();

Training Model to play the game

Once we generate the data, we can move to the next step: training RCNN model to play the game. For training model the CNTK is used. Also since we play a game and previous sequence will determined the next sequence in the game, LSTM RNN is used. More information about CNTK and LSTM can be found in previous posts. In my case I have collected nearly 15000 images during several round of playing the same level and route. Also for more accurate model much more images should be collected, nearly 100 000. The model is trained in one hour, with 500000 iterations. The source code about whole project can be found on GitHub page. ( )

By running the following code, the training process is started with provided training data:


Playing the game with CNTK model

Once we trained the model, we move to the next step: playing a game. The emulator should be positioned on the same position and with the same size in order to play the game.ONce the model is trained and created in th training folder, the playing game can be achive by running:

var dev = DeviceDescriptor.CPUDevice;
MarioKartPlay.LoadModel(“../../../../training/mario_kart_modelv1”, dev);

How it looks like on my case, you can see on this youtube video:

Deploy CNTK model to Excel using C#

In the last blog post, we saw how to save model state (checkpoint) in order to load it and train again. Also we have seen how to save model for the evaluation and testing. In fact we have seen how to prepare the model to be production ready.

Once we finish with the modelling process, we enter in to production phase, to install the model on place where we can use it for solving real world problems. This blog post is going to describe the process how deployed CNTK model can be exported to Excel like as AddIn and be used like ordinary Excel formula.

Preparing and deploying CNTK model

From the previous posts we saw how to train and save the model. This will be our starting point for this post.

Assume we trained and saved the model for evaluation from the previous blog post with file name as “IrisModel.model”. The model calculates Iris flower based on 4 input parameters, as we saw earlier.

  1. The first step is to create  .NET Class Library and install the following Nugget packages
    1. CNTK CPU Only ver. 2.3
    2. Excel Dna Addin
    3. Include saved IrisModel.model file in the project as Content and should be copied in Debug folder of the application.

As we can see, for this export we need Excel Dna Addin, fantastic library for making anything as Excel Addin. It can be install as Nuget package, and more information can be found at

The following picture shows above 3 actions.

Once we prepare everything, we can start with the implementation of the Excel Addin.

  1. Change the Class.cs name into IrisModel.cs, and implement two methods:
    1. public static string IrisEval(object arg) and
    2. private static float EvaluateModel(float[] vector).

The first method is direct Excel function which will be called in the excel, and the second method is similar from the previous blog post for model evaluation. The following code snippet shows the implementation for the methods:

[ExcelFunction(Description = "IrisEval - Prediction for the Iris flower based on 4 input values.")]
public static string IrisEval(object arg)
        //First convert object in to array
        object[,] obj = (object[,])arg;

        //create list to convert values
        List<float> calculatedOutput = new List<float>();
        foreach (var s in obj)
            var ss = float.Parse(s.ToString(), CultureInfo.InvariantCulture);
        if (calculatedOutput.Count != 4)
            throw new Exception("Incorrect number of input variables. It must be 4!");
        return EvaluateModel(calculatedOutput.ToArray());
    catch (Exception ex)
        return ex.Message;

private static string EvaluateModel(float[] vector)
    //load the model from disk
    var ffnn_model = Function.Load(@"IrisModel.model", DeviceDescriptor.CPUDevice);

    //extract features and label from the model
    Variable feature = ffnn_model.Arguments[0];
    Variable label = ffnn_model.Output;

    Value xValues = Value.CreateBatch<float>(new int[] { feature.Shape[0] }, vector, DeviceDescriptor.CPUDevice);
    //Value yValues = - we don't need it, because we are going to calculate it

    //map the variables and values
    var inputDataMap = new Dictionary<Variable, Value>();
    inputDataMap.Add(feature, xValues);
    var outputDataMap = new Dictionary<Variable, Value>();
    outputDataMap.Add(label, null);

    //evaluate the model
    ffnn_model.Evaluate(inputDataMap, outputDataMap, DeviceDescriptor.CPUDevice);
    //extract the result  as one hot vector
    var outputData = outputDataMap[label].GetDenseData<float>(label);
    var actualLabels = outputData.Select(l => l.IndexOf(l.Max())).ToList();
    var flower = actualLabels.FirstOrDefault();
    var strFlower = flower == 0 ? "setosa" : flower == 1 ? "versicolor" : "virginica";
    return strFlower;
}<span style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" data-mce-type="bookmark" class="mce_SELRES_start"></span>

That is all we need for model evaluation in Excel.

Notice that the project must be build with the x64 architecture, and also installed Excel must be in x64 version.  This demo will not work in Excel 32 bits.

Rebuild the project and open Excel file with Iris Data set. You can use the file included in the demo project,  specially prepared for this blog post.

  • Got to Excel – > Options -> Addins,
    • Install the ExportCNTKToExcel-AddIn64-packed addin file.

  • Start typing the Excel  formula :
    • IrisEval(A1:D1) and press Enter. And the magic happen.


Complete source code for this blog post can be found here.


Uvod u R jezik i mašinsko učenje- youtube lekcije

Do sada urađeno 7 kratkih lekcija u kojima sam pokusao objasniti osnove programskog jezika R i kako koristiti funkcije za statističku i analitičku obradu podataka. Kratkih uradaka biće još, ovo je samo početak. U narednom tekstu možete vidjeti sve do sada objavljene lekcije.