Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Step: Aggregate

Overview

The Aggregate step collapses rows that are identical across a set of variables, optionally creating new aggregate variables in the process.

Example starting data:

/*---------+------------+---------+--------*
 | test    | date       | student | score  |
 +---------+------------+---------+--------+
 | quiz    | 2020-04-01 | jane    | 83     |
 | quiz    | 2020-04-01 | pat     | 35     |
 | midterm | 2020-05-01 | jane    | 74     |
 | midterm | 2020-05-01 | pat     | 62     |
 *---------+------------+---------+--------*/

Example output data

Collapsing on variables test and date, and creating new variable average_score to aggregate data from the score variable.

/*---------+-------------+---------------*
 | test    | date        | average_score |
 +---------+-------------+---------------|
 | quiz    | 2020-04-01  | 59            |
 | midterm | 2020-05-01  | 68            |
 *---------+-------------+---------------*/

Step structure

  • There is one collapse block where you define how the data will be reshaped. On execution:

    • The data is cut to only include the variables chosen in this block.

    • Duplicate records across the collapsed variables are dropped.

  • There can be one or more aggregation blocks where you can capture aggregate information in a newly created variable.

    • You can collapse your table without creating any new variables in aggregation blocks.

    • Each aggregation block in the step represents one new variable in the output data.

    • Aggregation blocks are how you can capture information about records dropped in the collapse block.

Field definitions

Collapse block:

Field
Description

Variables to collapse on

All variables you want included in the output.

Aggregation blocks:

Field
Description

Name

The name of the new variable being created.

Aggregation method

How the new variable is summarized, such as SUM or COUNT.

[Method fields]

After selecting an aggregation method, you are prompted to input the information your chosen method needs to execute. You can see more specifics in the Variable creation methods section.

Examples

Example 1: Basic collapse

We have test score data recorded per test, student, and date. However, we want to know the average score on each test overall.

Starting data:

Input fields:

  • Variables to collapse on: Of the variables in our table, the only ones we want in the final output are test and date, so we select these here. We leave out student because that information doesn't matter anymore, and we leave out score because we are creating a new variable to replace it with aggregated information.

  • Name: We give our new variable a descriptive name, average_score.

  • Aggregation method: We want to average all values in the grades variable per test, so we choose Average.

  • Variable to aggregate: Here we choose score, as it is the variable containing the data we want to Average. If we had chosen a different Aggregation method, we might have different input fields here to answer.

Execution:

All variables that aren't collapsed on or used in aggregation are removed.

Average is calculated separately for each combination of test and date, since those are the collapsed variables.

The score variable is removed, since it was not collapsed on.

Then all exact duplicate records are dropped to create the output.

Output data:

Example 2: Multiple aggregation variables

Building on the previous example, we want to know how many students took each test.

Starting data:

Input fields:

2nd aggregation block

  • Name: We want to give the new variable a descriptive name unique from the other block (and other variables in the table), in this case test_count.

  • Aggregation method: We want to count all values in the test variable per date, so we choose Count.

  • Variable to count: We choose test as the variable containing the data we want to Count. Since none of our variables have null entries, we could choose any variable here and get the same result. If there were nulls, they would not be included in the Count. Conceptually we do not want to only include distinct values in our count so we leave that off

Output data:

Example 3: Drop duplicates

This step can be used to drop duplicated records even in cases where no aggregation happens.

Let's say we have data that we know had duplicate records that we don't need.

Starting data:

Input fields:

Variables to collapse on: To drop all records that are an exact duplicate across all variables, we just need the collapse block with no aggregation blocks. We can select all variables by typing them all out, or inputting *.

Output data:

PreviousTransform concepts
NextStep: Create variables

Last updated

Was this helpful?