group

Use the "group" directive to collect rows according to shared criteria. Use this directives sub-directives to perform convenient operations on each 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.

FirstLastTransfer
JohnJones-60
AdamJones186
CathyAlbertson48794
JohnJones545
CathyAlbertson8777

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.

FirstLastTransfer
JohnJones485
AdamJones186
CathyAlbertson57571

If the sub-directive "coalesce" is not included, the following is the resulting worksheet:

FirstLastTransfer
JohnJones485
JohnJones485
CathyAlbertson48794
CathyAlbertson48794
AdamJones186

πŸ“˜

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.

Discount IDAccount IDDiscount
110.9
220.8
310.95

Applyi the following directive declaration:

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

To obtain the resulting worksheet.

Account IDDiscount
10.855
20.8

πŸ“˜

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

Account IDAccount User# IP Address
12127.0.38.9
11127.0.5.8
11127.0.5.8
12127.0.48.2
21127.0.59.12

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

Account IDAccount UserSame IP?
12FALSE
11TRUE
21TRUE

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

FirstLastValue
JohnJones3
AdamJones5
JohnJones1
CathyAlbertson-1
CathyAlbertson1
JohnJones0

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.

FirstRatio
John0.75
John0.25
John0.0
Adam1.0
Cathy0
Cathy0

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,

Account IDAccount User #AmountTags
12894home verified
1145981office verified
1115home
125958verified home
21255verified mobile

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.

Account IDAccount User #AmountUser Always Verified?
12894TRUE
125958TRUE
1145981FALSE
1115FALSE
21255TRUE