group

Grouping rows in a worksheet based on common criteria is a powerful data processing technique. Users may group rows of data per specified criteria. Once rows are grouped, other operations can be applied to the group as an added convenience.

For example, consider a worksheet with columns "Account ID" and "Date". Use this directive to group the rows in this worksheet.

pebblestream:from(Accounts)
pebblestream:group("Account ID", "Date")

📘

Grouping Feature

All rows with matching "Account ID" and "Date" values are put next to each other. This often results in easier data processing.

📘

Grouping parameters are optional!

If no parameters are provided to the pebblestream:group() directive, it will behave as if all rows in the worksheet belong to a single group.

coalesce

Here is the sub-directive coalesce declaration.

pebblestream:coalesce()

After group calculations are performed, coalesce each group into a single row.

📘

If the pebblestream:coalesce directive is not specified, it is applied by default.

🚧

Coalesce is not compatible with ratio, balance, or merge sub-directives.

expand

Here is the sub-directive expand declaration

pebblestream:expand()

This maintains the original rows of the grouped worksheet and assigns the grouped result to each row. Use this to relate each row to the group's computed value.

For instance, to compute the relative size of a column's value to each member of the group, use the pebblestream:min directive with expand, then divide each row's column value by the group's minimum.

pebblestream:from("Customer Orders")
pebblestream:group("customer id")
pebblestream:min("order amount", "minimum order")
pebblestream:expand()

📘

The pebblestream:expand() directive facilitates matching of group results with the original rows of the group. This reduces the need for complicated INDEX-MATCHES.

sum

Use this directive to get the sum of the values in a column for rows grouped by criteria.

pebblestream:from(Amounts)
pebblestream:group("Last", "First")
pebblestream:sum("Amount"[, "Result"])

The "Amount" rows are summed across all rows with the same "Last" and "First" values. If "Result" is provided, a new column called "Result" is created with the value. Otherwise, the "Amount" column is updated with the sum.

Sum Example

Sum up all transfers for the each person in the Transactions worksheet.

Here is the directive that accomplishes this task.

pebblestream:from(Transactions)
pebblestream:group("Last", "First")
pebblestream:sum("Transfer")
pebblestream:coalesce()

This is the resulting worksheet.

If the sub-directive "expand" is used instead of coalesce, the following is the resulting worksheet:

📘

Computation Tip

It can be computationally convenient to not coalesce group results.

mult

Mult Example

pebblestream:mult("Factor"[, "Result"])

The "Factor" rows are multiplied together. If "Result" is provided, a new column called "Result" is created with the value. Otherwise, the "Factor" column is updated.

Apply the following directive declaration:

pebblestream:from(Discounts)
pebblestream:group("Account ID")
pebblestream:mult("Discount")
pebblestream:coalesce()

To obtain the resulting worksheet.

📘

Computation Tip

It can be computationally convenient to not coalesce group results.

min

The pebblestream:min() directive returns the minimum value for each group.

pebblestream:from(Discounts)
pebblestream:group("Account ID")
pebblestream:min("Discount")
pebblestream:coalesce()

max

The pebblestream:max() directive returns the maximum value for each group.

pebblestream:from(Discounts)
pebblestream:group("Account ID")
pebblestream:max("Discount")
pebblestream:coalesce()

average

The pebblestream:average directive returns the average value for each group.

pebblestream:from(Discounts)
pebblestream:group("Account ID")
pebblestream:average("Discount")
pebblestream:coalesce()

equal

The equal sub-directive group's rows that have equal values in the provided column.

pebblestream:equal("Amount"[, "Result"])

The "Amount" rows are checked for whether they are all equal. If "Result" is provided, a new column called "Result" is created with the value. Otherwise, the "Amount" column is updated.
Requires group.

Equal Example

Consider this worksheet Logins

User the following directive declaration:

pebblestream:from(Logins)
pebblestream:group("Account ID", "Account User")
pebblestream:equal("IP Address", "Same IP?")
pebblestream:coalesce()

to obtain the worksheet Same IP

ratio

The "ratio" sub-directive performs a typical operation amongst accountants and financial analysts. Given a group of numbers, determine the relative weight of each number in the group.

pebblestream:ratio("Amount"[, "Result"])

The "Amount" rows are summed together and the ratio of each row is calculated based on the total of the group's values. If "Result" is provided, a new column called "Result" is created with the row's group ratio. Otherwise, the "Amount" column is updated.

❗️

Important

The ratio directive cannot be used with coalesce.

Ratio Example

Consider the following worksheet of Transactions

Using the following directive declaration

pebblestream:from(Transactions)
pebblestream:group("Last", "First")
pebblestream:ratio("Value", "Ratio")

will produce the following Ratio worksheet when placed in a comment in the header row.

regex

❗️

This function is not supported at this time due to inconsistencies in behavior accross platforms. A future implementation of this directive is not guaranteed to be backwards compatible!

Checks whether every row in "Name" matches the provided regular expression. If "Result" is provided, a new column called "Result" is created with the result. Otherwise, the "Name" column is updated.

pebblestream:regex("Name", "[0-9]*MyRegex.*"[, "Result"])

Regex Example

Consider the worksheet Transactions,

Determine whether every transaction for a given user was verified using the following directive declaration:

pebblestream:from(Transactions)
pebblestream:group("Account ID", "Account User #")
pebblestream:regex("Tags", ".*\\bverified\\b.*", "User always verified?")

Placing this declaration in the comment section of the header section of the Verification Status worksheet produces the result below.

balance

The "balance" sub-directive performs a typical operation amongst accountants and financial analysts. Given a total amount segmented into sub-amounts by a series of sharing percentages totaling 1, ensure that the sum of the rounded shares is equal to the rounded original total amount.

This directive requires three parameters: the first is the column header name of the allocated amount, the second is the name of the column that will receive the rounded and balanced amount, and the final parameter is the name of the column that will receive the adjustment added to the floored amount to achieve the balanced amount.

balanced = floor(allocation) + adjustment

Here is an example:

pebblestream:balance(“allocation”, “balanced”,”adjustment”)

❗️

Important

The balance directive cannot be used with coalesce.

Balance Example