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.

The following examples illustrate how group's sub directives are used.

coalesce

Here is the sub-directive coalesce declaration.

pebblestream:coalesce()

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

🚧

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

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 together for all rows that have 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 result of 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 "coalesce" is not included, 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.

Applyi 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.

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.

hEqual 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

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?")

By placing his declaration in the comment in the header section of the Verification Status worksheet to obtain the results below.