Concept | Pivot recipe#

Watch the video

The Pivot recipe transforms datasets into pivot tables, which are tables of summary statistics. This operation can also be referred to as reshaping from long to wide format.

Use case#

In the following example, we will use a Pivot recipe to extract some useful statistics from a table of retail transactions. Specifically, we want to calculate the total number of items purchased as well as the most recent year of purchase for each customer.

Slide depicting a table before and after a Pivot recipe transformation.

To do this, we’ll need to configure the Pivot recipe.

Pivot configuration#

There are three main components of the Pivot recipe:

  • Pivot, which determines the reshaping of a dataset into a pivot table. Specifically, we decide which rows we want to transform into columns.

  • Group keys (or row identifiers), which determine the rows of a pivot table.

  • Aggregations to indicate which columns contains values that we want to aggregate.

In our example, we:

  1. Select to pivot by the item column.

  2. Choose to use the name column as the group key.

  3. Tell Dataiku to compute aggregations for the quantity and year columns.

Dataiku screenshot of the Pivot step to configure a Pivot recipe.


As a result, our pivot table displays aggregates on hat and shoe categories using Alice and Bob as the pivot table row identifiers. Our pivot table displays the aggregations we selected for the quantity and year columns.

Screenshot showing the output of a Pivot recipe.