Tutorial | Enrich the dataset (Core Designer part 8)

Having explored the Flow in another tutorial, now let’s demonstrate a key visual recipe, the Join recipe, which allows you to enrich your data with columns from another dataset.

Objectives

In this tutorial, you will:

• Use a Join recipe to join two datasets.

Resume or create the project

There are two ways to get started:

Starting here?

If you skipped the previous sections, you need to:

• Have access to a Dataiku instance (version 9.0 or above).

• Create the project (+New Project > DSS tutorials > Core Designer / Basics > Basics 103)) or download and import it from this website.

Continuing from previous tutorials?

If you have worked on previous tutorials in this series so that you already have a project with the orders_by_customer dataset, you’ll need to import one new dataset to that project.

• Download the customers CSV file.

• From the Flow of your existing project, click + Dataset > Upload your files.

• Click on Select Files, and choose your customers.csv file.

• Finish importing the dataset by clicking the Create button or using the shortcut Cmd/Ctrl+S.

Once you have the dataset customers and orders_by_customer in your Flow, you’re ready to start!

Explore the customer data

Tip

A screencast at the end of the page recaps the instructions described here.

The Flow contains a dataset of orders grouped by unique customers. Now we have a customers dataset with more information about our customers.

1. Open the customers dataset by double-clicking on its icon in the Flow.

2. Explore the dataset. As you can see, each row in this dataset represents a separate customer, and records:

   • the unique customer ID

   • the customer’s gender

   • the customer’s birth date

   • the user agent most commonly used by the customer

   • the customer’s IP address

   • whether the customer is part of Haiku T-Shirts’ marketing campaign

Note

Take a few minutes to explore it with tools like Analyze. Also, note the gray portion of the gender column’s data quality bar representing missing values.

Create a Join recipe

We can use the Join recipe to enrich the customers dataset with the information about the aggregate orders customers have made from the orders_by_customer dataset.

1. From the Actions tab in the right panel of the customers dataset, choose Join with… from the list of visual recipes.

2. Select orders_by_customer as the second input dataset.

   Note

   Although only two datasets can be added in the Join recipe creation dialog, more datasets can be added at the Join step after creating the recipe.

3. Change the name of the output dataset to customers_orders_joined.

4. Click Create Recipe.

Define the join condition

The core step of this recipe is the Join step, where you choose how to match rows between the datasets. In this case, we want to match rows from the customers and orders_by_customer datasets that have the same value of customerID and customer_id, respectively.

Note

Notice the + button at the top right of each dataset in the Join step. You can use this button to add more datasets to join with the customers and the orders_by_customer datasets.

1. Click on Add a Condition to tell Dataiku which columns to match. This opens the Join conditions dialog, where Dataiku automatically recognizes that the ID columns are the join key, even though they have different names. This is the only condition we need to add here.

2. Select OK and return to the Join recipe.

By default, the recipe will drop unmatched rows, but we can also configure the recipe to send unmatched rows to another output dataset. This can be helpful to confirm your expectations of the output.

Set the join type

By default, the Join recipe performs a left join, which retains all rows in the left dataset, even if there is no matching information in the right. Since in this case we only want to work with customers who have made at least one order, let’s modify the join type to inner join, which keeps only the rows that match in both datasets.

Note

For more information about join types, visit Concept | Join recipe.

1. Click on the Left join indicator.

2. Select Inner join.

This will retain only the customers who have made an order, and remove the others from the output dataset.

Define output columns

The next step is to choose which columns to retain from the input datasets.

We want to carry over all columns from both datasets into the output dataset, with the exception of customer_id (since the customerID column from the customers dataset has the same information).

1. On the left-hand side, navigate to the Selected columns step.

2. For the orders_by_customer dataset, manually select columns, and de-select customer_id.

3. Click Run to execute the recipe.

View the output

Now, let’s explore the output.

1. When the recipe has finished running, click Explore dataset customers_orders_joined at the bottom of the screen to explore the customers_orders_joined dataset.

2. From the top navigation bar, go back to the Flow to check your progress.

See a video covering these steps

What’s next?

So far all of your work has been in the Flow. Now it’s time to learn about the Lab!