Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Excerpt

Aggregate functions perform a computation against a set of values to generate a single result. For example, you could use an aggregate function to compute the average (mean) order over a period of time. Aggregations can be applied as standard functions or used as part of a transformation step to reshape the data.

Aggregate across an entire column:

D trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:average(Scores)
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueaverage(Scores)
SearchTermNew formula

Output: Generates a new column containing the average of all values in the Scores column.

D trans
RawWrangletrue
Typestep
WrangleTextpivot value: average(Score) limit: 1
p01NameValues
p01Valueaverage(Score)
p02NameMax number of columns to create
p02Value1
SearchTermPivot columns

Output: Generates a single-column table with a single value, which contains the average of all values in the Scores column. The limit defines the maximum number of columns that can be generated.

Info

NOTE: When aggregate functions are applied as part of a pivot transformation, they typically involve multiple parameters as part of an operation to reshape the dataset. See below.

Aggregate across groups of values within a column:

Aggregate functions can be used with the pivot transformation to change the structure of your data. Example:

D trans
RawWrangletrue
p03Value1
Typestep
WrangleTextpivot group: StudentId value: average(Score) limit: 1
p01NameRow labels
p01ValueStudentId
p02NameValues
p02Valueaverage(Score)
p03NameMax number of columns to create
SearchTermPivot columns

In the above instance, the resulting dataset contains two columns:

  • studentId - one row for each distinct student ID value
  • average_Scores - average score by each student (studentId)
Info

NOTE: You cannot use aggregate functions inside of conditionals that evaluate to true or false.

A pivot transformation can include multiple aggregate functions and group columns from the pre-aggregate dataset.

For more information on the transformation, see Pivot Data.

Info

NOTE: Null values are ignored as inputs to these functions.

 These aggregate functions are available:

D children
alltrue