08 Turbo Prep - Merging Data Video Lecture | Rapid Miner: Learn and Master - Data & Analytics

[Music]
[Music]
in introduction video on turbo prep we
gave you an overview on all the menu
items and the general process of loading
data in this video we're going to
demonstrate how to merge datasets
we've pre-loaded here the Titanic
training and the Titanic unlabeled data
sets from the samples folder the
difference between those two is that the
Titanic training already has a text
column with flags or labels so that it
can be used to train a classification
model the Titanic unlabeled data set on
the other hand is missing the column
with those labels so we can use a model
to assign a predicted label to each of
the 392 rows to do that with our
easy-to-use auto model feature we need
to merge those two example sets together
into one which means we'll add the new
rows at the bottom the method to do this
is called append we'll start with our
first data preparation session by
selecting the primary set to which we
want to add data and then we select
merge
here we select the table we want to add
and use append as merging method oh it
is not active what happened to find out
what has gone wrong let's see the help
file here quickly it says that this is
only available if both datasets have
exactly the same columns and column
types okay there we have the problem our
Titanic unlabeled data set is missing
the label column obviously let's fix
this by simply adding an empty column
which can then be used to fill in
predictions later first select the data
set you want to edit then select the
preparation group in our case generate
as the name we have to use survived and
for the content we'll use a constant or
a more specific and empty text or
nominal since columns need to match
exactly including name and data type to
add the constant simply double-click it
and then update the preview
all right looks good let's commit this
generate
now back to emerging
and as I now update the preview you can
see nicely highlighted by the background
color where the additional rows will be
added we commit the merge and I'll use
this data set as input for auto model
I'll go fast through that feature as
there are detailed tutorials available
and we just want to show you the final
output with the scored examples and
while the more calculation intensive
models are still running we can already
look at the predictions done here by our
naive Bayes model adding rows is one
option to merge two datasets the other
commonly used option is to add
additional columns to an existing data
set based on one or multiple key columns
let's see how this works I'll go back to
turbo prep and clean out all the loaded
data sets
we'll use some more data from the
samples folder of rapid miner studio
allowing you to easily follow along I'll
load the transactions this time which is
a list of purchases but it could
represent any kind of transactions as
you can see it's just three simple
columns with the customer ID the product
ID and the total items or a number of
transactions if we want to know what has
been sold we would need to know what the
product IDs referred to if we had a
table which contains product IDs and
product names then we could easily look
up or even add the details via the
product ID as the matching identifier
luckily we have one in the samples
folder it is called products to make
sure that the product information is
added to our transactions table and not
the other way around we need to
highlight that table first then we click
merge to enter our next data preparation
cycle now we can just go through the
dialog first select the table we want to
merge then the join type all go for a
left join which ensures that all of my
existing entries are kept and details
from the products table are added to the
rows if there's a matching Product ID so
our join keys are the product IDs in the
two tables as we update the preview you
can now see the columns product name
product category and price appear by the
way for more details on the concepts of
joins and what that has to do with
ducklings and piglets I recommend
checking out the five minutes within go
episode hour join here is now completed
and so we can end the preparation
iteration as always with a commit since
we don't care for the category in price
we can do somehow
simply select both by holding down the
ctrl or command key when clicking and
with a quick and easy remove we're
entering the next transform session now
just apply and commit to have our final
table with the merged product names as
always with rapid minor things are not
hidden in a black box so we can take a
look at the process which was generated
in the background let's use that for a
brief recap ok now you can see the two
datasets coming into the join then we
have a select attributes operator which
is selecting all but the two columns
which we wanted to have removed one note
before we end keep in mind that if you
change something here in the design view
it will not be picked up in your turbo
prep session but it will only be applied
to your output once you hit the run
process button now this concludes our
tutorial on merging data thank you very
much for watching
[Music]