Hide navigation sidebar
Hide table of contents sidebar
Back to top
Toggle table of contents sidebar

# Tutorial | Distinct recipe

Let’s practice using the Distinct recipe to remove duplicate rows in Dataiku!

## Get started

### Objectives

In this tutorial, you will:

- Remove duplicates on all columns.

- Check that each row in your dataset is unique.

### Prerequisites

To complete this tutorial, you’ll need the following:

- Dataiku 12.0 or later.

### Create the project

To create the project:

1. From the Dataiku Design homepage, click + New Project > DSS tutorials > Core Designer > Distinct Recipe.

2. From the project homepage, click Go to Flow (or `g` + `f`).

Note

You can also download the starter project from this website and import it as a zip file.

You’ll next want to build the Flow.

1. Click Flow Actions at the bottom right of the Flow.

2. Click Build all.

3. Keep the default settings and click Build.

### Use case summary

Let’s say we’re a financial company that uses some credit card data to detect fraudulent transactions.

The project comes with three datasets, described in the table below.

Dataset | Description
--- | ---
tx | Each row is a unique credit card transaction with information such as the card that was used and the merchant where the transaction was made. It also indicates whether the transaction has either been: Authorized (a score of 1 in the authorized_flag column) Flagged for potential fraud (a score of 0)
merchants | Each row is a unique merchant with information such as the merchant’s location and category.
cards | Each row is a unique credit card ID with information such as the card’s activation month or the cardholder’s FICO score (a common measure of creditworthiness in the US).

## Create the Distinct recipe

We’ll create a Distinct recipe from the tx_prepared dataset.

1. From the Flow, select the tx_prepared dataset and click the Actions icon (+) from the right panel to open the Actions tab.

2. Under the Visual Recipes section, click on Distinct.

   The New distinct recipe window opens.

3. In the recipe dialog, change the output name to `tx_distinct`.

4. Click Create Recipe.

## Remove duplicates on all columns

We’ll start with the default settings to understand what happens when you remove duplicates on all columns.

1. Under Operation mode, keep the default selection Remove duplicates (on all columns).

2. Make sure Compute count of original rows for each deduplicated output row is checked under Settings.

3. Run the recipe and then open the output dataset.

Here, you can see a new column: count. Quickly scroll and see that all counts = 1 in this sample. Let’s confirm that this is true and that there were no duplicates in the dataset.

### Set the post-filter

1. From the output dataset, click Parent Recipe.

2. Navigate to the Post-filter step of the recipe.

3. Toggle On the Filter tile.

4. Add a condition where `count > 1`.

5. Run the recipe and then open the output dataset.

Because there are no records in the output dataset, we are now certain that there were no entirely duplicate rows in the dataset. Next, we will try to find duplicates on a subset of columns!

## Find distinct values of a subset of all columns

Let’s find out if there are any duplicate combinations of purchase_date and card_id. This will tell us if any card was used more than once in one day.

1. Reopen the Distinct recipe.

2. Under Operation mode, choose Find distinct values of a subset of all columns.

3. Add or keep purchase_date and card_id in the Selected columns box.

4. Ensure that the box Compute count of original rows for each deduplicated output row is checked.

5. Make sure the post-filter is still active.

6. Run the recipe and then open the output dataset.

In the new tx_distinct dataset, you should see all records that have one or more duplicates across these two columns. In other words, this output shows all instances where a customer made multiple purchases using the same card on the same day.

Note

Finding duplicates is particularly useful when preparing to use the Window recipe, as it helps to confirm whether or not rows will have a unique order.

## What’s next?

You just practiced using the Distinct recipe to find duplicates in your data.

See also

For more information on this recipe, see also the Distinct recipe article in the reference documentation.

To try out more visual recipes, visit our page on Visual Recipes!