Hands-on joining Datasets

In the hands-on lessons of Basics 101 and Basics 102, you created a project, imported a dataset, and did some data exploration and preparation steps.

In this hands-on lesson, we’ll demonstrate another key visual recipe: Join.

Resume/Create Your Project

If you completed all of the steps in the Basics 102 project, you can resume the same project for this lesson. All you need to do is download a copy of the customers CSV file and upload it to the project.

Alternatively, you can create a starter project with these same steps completed. From the Dataiku homepage, click +New Project > DSS Tutorials > Core Designer / Basics > Basics 103.

Click on Go to Flow.

Join Datasets

In Basics 102, we created a dataset of orders grouped by unique customers. Now we have a dataset with more information about our customers. We can use the Join recipe to enrich the customers dataset with the information of the orders_by_customer dataset.

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

Open the customers dataset by double-clicking on its icon in the Flow. Each row in this dataset represents a separate customer, and records:

• the unique customer ID
• the customer’s gender
• the customer’s birthdate
• 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.

We are now ready to enrich the customers dataset with information about the aggregate orders customers have made.

• From the Actions menu, choose Join with… from the list of visual recipes.

• Select orders_by_customer as the second input dataset.
• Change the name of the output dataset to customers_orders_joined.
• Click Create Recipe.

The Join recipe has several steps (shown in the left navigation bar). The core step is the Join step, where you choose how to match rows between the datasets. In this case, we want to match rows from customers and orders_by_customer that have the same value of customerID and customer_id, respectively. Note that Dataiku DSS has automatically discovered the join key, even though the columns have different names.

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 we only want to work with customers who have made at least one order, let’s modify the join type.

Note
Types of joins
There are multiple methods for joining two datasets; the method you choose will depend upon your data and your goals in analysis.

• Left join keeps all rows of the left dataset and adds information from the right dataset when there is a match. This is useful when you need to retain all the information in the rows of the left dataset, and the right dataset is providing extra, possibly incomplete, information.
• Inner join keeps only the rows that that match in both datasets. This is useful when only the rows with complete information from both datasets will be useful downflow.
• Outer join keeps all rows from both datasets, combining rows where there is a match. This is useful when you need to retain all the information in both datasets.
• Right join is similar to a left join, but keeps all rows of the right dataset and adds information from the left dataset when there is a match.
• Cross join is a Cartesian product that matches all rows of the left dataset with all rows of the right dataset. This is useful when you need to compare every row in one dataset to every row of another
• Advanced join provides custom options for row selection and deduplication for when none of the other options are suitable.
  By default, the Join recipe performs a Left join.

• Click on the Left Join indicator.
• Navigate to Join Type.
• Click on Inner join and then Close.

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

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 should be sufficient).

• Click on the Selected columns step.
• Uncheck the customer_id column in the orders_by_customer dataset.

Click Run to execute the recipe, updating the schema in the process.

When it is done, click Explore dataset customers_orders_joined at the bottom of the screen to explore the customers_orders_joined dataset.

The following video goes through what we just covered.

//

What’s next?

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