> For the complete documentation index, see [llms.txt](https://docs.redivis.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.redivis.com/reference/workflows/transforms/step-aggregate.md). # 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: ``` /*---------+-------+---------+------------* | test | score | student | date | +---------+-------+---------+------------+ | quiz | 83 | jane | 2020-04-01 | | quiz | 35 | pat | 2020-04-01 | | quiz | 89 | sam | 2020-04-01 | | midterm | 74 | jane | 2020-05-01 | | midterm | 62 | pat | 2020-05-01 | | midterm | 93 | sam | 2020-05-01 | | final | 77 | jane | 2020-06-01 | | final | 59 | pat | 2020-06-01 | | final | 92 | sam | 2020-06-01 | *---------+-------+---------+------------*/ ``` #### 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. ``` /*---------+-------+------------* | test | score | date | +---------+-------+------------+ | quiz | 83 | 2020-04-01 | | quiz | 35 | 2020-04-01 | | quiz | 89 | 2020-04-01 | | midterm | 74 | 2020-05-01 | | midterm | 62 | 2020-05-01 | | midterm | 93 | 2020-05-01 | | final | 77 | 2020-06-01 | | final | 59 | 2020-06-01 | | final | 92 | 2020-06-01 | *---------+-------+------------*/ ``` `Average` is calculated separately for each combination of `test` and `date`, since those are the collapsed variables. ``` /*---------+-------+------------+---------------* | test | score | date | average_score | +---------+-------+------------+---------------+ | quiz | 83 | 2020-04-01 | 69 | | quiz | 35 | 2020-04-01 | 69 | | quiz | 89 | 2020-04-01 | 69 | | midterm | 74 | 2020-05-01 | 76.3333 | | midterm | 62 | 2020-05-01 | 76.3333 | | midterm | 93 | 2020-05-01 | 76.3333 | | final | 77 | 2020-06-01 | 76 | | final | 59 | 2020-06-01 | 76 | | final | 92 | 2020-06-01 | 76 | *---------+-------+------------+---------------*/ ``` The `score` variable is removed, since it was not collapsed on. ``` /*---------+------------+---------------* | test | date | average_score | +---------+------------+---------------+ | quiz | 2020-04-01 | 69 | | quiz | 2020-04-01 | 69 | | quiz | 2020-04-01 | 69 | | midterm | 2020-05-01 | 76.3333 | | midterm | 2020-05-01 | 76.3333 | | midterm | 2020-05-01 | 76.3333 | | final | 2020-06-01 | 76 | | final | 2020-06-01 | 76 | | final | 2020-06-01 | 76 | *---------+------------+---------------*/ ``` Then all exact duplicate records are dropped to create the output. #### Output data: ``` /*---------+-------------+---------------* | test | date | average_score | +---------+-------------+---------------| | quiz | 2020-04-01 | 69 | | midterm | 2020-05-01 | 76.3333 | | final | 2020-06-01 | 76 | *---------+-------------+---------------*/ ``` ### Example 2: Multiple aggregation variables Building on the previous example, we want to know how many students took each test. #### Starting data: ``` /*---------+-------+---------+------------* | test | score | student | date | +---------+-------+---------+------------+ | quiz | 83 | jane | 2020-04-01 | | quiz | 35 | pat | 2020-04-01 | | quiz | 89 | sam | 2020-04-01 | | midterm | 74 | jane | 2020-05-01 | | midterm | 62 | pat | 2020-05-01 | | midterm | 93 | sam | 2020-05-01 | | final | 77 | jane | 2020-06-01 | | final | 59 | pat | 2020-06-01 | | final | 92 | sam | 2020-06-01 | *---------+-------+---------+------------*/ ``` #### 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: ``` /*---------+-------------+---------------+-------------* | test | date | average_score | test_count | +---------+-------------+---------------+-------------+ | quiz | 2020-04-01 | 69 | 3 | | midterm | 2020-05-01 | 76.3333 | 3 | | final | 2020-06-01 | 76 | 3 | *---------+-------------+---------------+-------------*/ ``` ### 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: ``` /*---------+-----------+------------* | test | questions | date | +---------+-----------+------------+ | quiz | 10 | 2020-04-01 | | quiz | 35 | 2020-04-01 | | quiz | 10 | 2020-04-01 | | midterm | 20 | 2020-05-01 | | midterm | 20 | 2020-05-01 | | midterm | 20 | 2020-05-01 | | final | 45 | 2020-06-01 | | final | 45 | 2020-06-01 | | final | 45 | 2020-06-01 | *---------+-----------+------------*/ ``` #### 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: ``` /*---------+-----------+------------* | test | questions | date | +---------+-----------+------------+ | quiz | 10 | 2020-04-01 | | midterm | 20 | 2020-05-01 | | final | 45 | 2020-06-01 | *---------+-----------+------------*/ ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.redivis.com/reference/workflows/transforms/step-aggregate.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.