Hello and welcome to this video on, how to prepare and clean up your data.
In our other videos we show you how to load nice and clean data samples
but in reality data is almost never complete or without issues.
Consequently, in many cases you will find yourself spending more time on data preparation
than on actual modelling.
Here we will show you some of the great operators available in RapidMiner
to help you with this first step of the predictive analytics lifecycle.
So let’s get started and read a file with imperfect raw data.
The one we are using is in the downloadable archive, which you can find on the ‘training videos website’,
so you can follow along if you want.
The file we are using is called ‘raw-customer-churn-data.xlsx’.
Let’s drag and drop the ‘Read Excel’ operator into our ‘process’ panel and then we select the data-file
via the available option in the ‘Parameters’ panel.
The data is in the first sheet, and we don’t have a date column, so we can keep all the default settings.
To get a first look at the data we connect the ‘output’ port of the operator to our ‘results’ port
and run the process via the short-cut-key ‘F11’.
You can probably spot some of the issue that our example-set has immediately,
as we have purposely placed them in the first few lines.
In your scenario it will be certainly less easy to identify problems so we recommend
you start by taking a look at the ‘statistics’ on your data.
In the Column ‘Missing’ you can see that we have some blanks in the Gender and Age attributes.
And as we click on ‘Gender’ we see that there are four unique values where we should have only two.
A look at the details tells us that there is a single ‘m’ instead of male
and one of the ‘female’ entries has some leading blanks.
Further we have some missing values in our Churn attribute and the name “Santiago Cruz” appears twice
so that might be a duplicate entry which we may need to remove.
To make a decision on how to address each of our data issues,
we need to consider our overall analytics task first.
We want to use the information at hand, to find customers who are likely to churn,
so we can encourage them to stay a customer.
For that, we can use the historical information of those, where we know if they are loyal or churners.
With that input we can build a model to make a prediction on the others
where we don’t know yet how they are likely to behave.
So the ones with information on their loyalty are to be placed in one subset
to be used as input for creating a model.
And we want the ones without ‘churn’ information to be grouped in another subset
so we can predict how they will act.
What else?
We have some leading and maybe trailing white spaces which we need to remove.
And what do we do with the ones with missing or wrong values in ‘age’ or ‘gender’?
Let start with the white spaces.
For those we can use the operator ‘trim’ and we will have that applied to all attributes.
Just as a reminder, we are using ‘attribute’ as term for columns at RapidMiner
and data-rows are called ‘examples’.
Ok, we removed the whitespaces and now we have to decide how to handle
the examples with missing Age or Gender values?
Since those are only few, and at the same time the value of the attribute would be an important input for building
or applying the model, we will just get rid of those examples by filtering them out.
The right operator for that is consequently ‘filter examples’.
To configure it correctly we want to select the ‘Add Filters’ button in the ‘parameters’ panel.
When we select the drop down we can see that it is empty.
So to configure our filter we need to type the title of the attributes in here.
Be careful to use upper and lower case as defined in your example-set.
But wait a minute,
wouldn’t it be much more convenient, to just select the available attributes titles from the dropdown?
For that the meta-data from our file needs to be read.
For spreadsheets or CSV files this happens only if we use the ‘import configuration wizard’- so let’s do that.
We select the ‘Read’ operator and then we just go through the wizard.
We will leave all the defaults and then we click finish.
If you want to learn more on the ‘import configuration wizard’ then
please refer to the videos on ‘data loading’ and ‘data import’.
Now, as we select ‘Filter examples’ again we see that our attribute titles have been pre-populated nicely for us.
So let’s add the filters for Gender and Age and set them both to ‘is not missing’.
Note here that you can use ‘Match All’ or ‘Match Any’ to make one
or both criteria necessary for the removal of an example.
The next item on our data preparation list is the removal of a duplicate entry.
So we will drag and drop the ‘remove duplicate’ operator onto our connection and we will leave it
set to ‘all’ as the ‘attribute filter type’ - this means that, two examples are only considered duplicates
if they are identical with respect to all attributes values.
Before we take look at our updated example-set, let’s also fix the issue with the gender value
which was an ‘m’ instead of ‘male’.
We will use the ‘replace’ operator for that.
In the parameters panel we set up that we want to work on a single attribute which is ‘Gender’
and we want to replace ‘m’ by ‘male’.
Ok now let’s click ‘start’ to run this process and see the data.
Oh! What happened here? The gender is all messed up!
What happened is this, RapidMiner replaced all the letters ‘m’ with ‘male’ so also the ‘m’ in ‘male’ or ‘female’.
So the only correct example is now the one which initially only had ‘m’ as value.
Before we go back to fix the gender values let’s look at the other issues we addressed.
In the ‘statistics’ we can see that missing for ‘Gender’ and ‘Age’ are gone
and the duplicate entry has also been removed.
OK now let’s go back to our design view to fix the gender values.
We only want to replace the letter ‘m’ if it is not part of a word.
In order to identify that we need to use something called a ‘Regular expression’.
A regular expression is a special text string for describing a search pattern.
You can think of regular expressions as wildcards on steroids.
The expression we are going to use here to identify our single letter string is a single ‘m’ in square brackets.
And we will add ‘\b’ in front and after which marks the beginning and end of a word.
So now we are searching for a lower case ‘m’ by itself only.
If you want to learn more on regular expressions, we recommend to look at www.regular-expressions.info
but there are also many other site available which will help you get started with this powerful feature.
The last items on our list are, splitting the data in two subsets, and preparing it
for the next steps of the predictive analytics lifecycle.
For splitting we can use the ‘split’ operator or we can also use the ‘filter examples’ operator.
So we copy and paste our previously created ‘filter example’.
Then let’s adjust the settings to filter out examples where ‘churn’ ‘is not missing’.
Now we can use the ‘unmatched’ port and store these examples using the store operator.
I’ll save the dataset in the data folder and will call it ‘unlabeled-customers’.
Since we have only ‘missing values’ in the churn attribute let’s get rid of the attribute to save space.
We will use the ‘select attributes’ operator and will set it to select only the ‘churn’ attribute.
Then we tick the ‘invert selection’ box, and voila we are now storing our unlabeled data for later.
By now our Process panel has been filling up, so we need to get some more room.
We can move operators below or more to the right which will give us a slider, or we zoom out a bit.
However, the most elegant is probably to move all the operators which we used
for data cleaning into a ‘sub-process’.
Just mark all the operators and then we use the right mouse-button to send them all into a new sub-process
which you could also give a descriptive name if you want.
Now we can rearrange things nicely and finally prepare our remaining example-set to become our training-data.
For that we will remove unnecessary information first, which is in our case the names.
Again the ‘select Attributes’ operator with the ‘invert selection’ will do the trick.
In order for the model to know what is the target variable, or as we call it: the ‘label’,
we need to use the ‘Set role’ operator.
So let’s drop that on our connection and set it to make the ‘Churn’ attribute the ‘Label’.
The attribute will now be automatically disregarded for building the model but will be used for validation.
Now as we finally connect the store operator to the results and select the ‘Run process’ button,
we see our two fully prepared subsets in the ‘Results’ view, with the ‘Label’ identified in green.
The next steps like, building and validating a model, are shown in other tutorials
so we are ending this tutorial here.
We hope you enjoyed this video in which we showed you how to clean and prepare your data using some of
the many operators available in RapidMiner for data preparation.
Thank you very much for watching!