Using APL’s Key Operator (⌸) for Group‑by Aggregation

Problem & Takeaway

When you have a two‑column table in APL – a key column and a numeric value column – you often want to collapse the table by key and compute an aggregate such as sum, count, or maximum. The APL key operator (⌸) does this in a single, pure expression. The goal of this guide is to show you how to build the expression, validate it, and recover from the most frequent errors.

Desired Outcome

Produce a two‑column matrix where the first column lists each distinct key and the second column holds the aggregated value for that key. For example, given sales data by region, you want a table that shows the total sales per region.

Prerequisites

• APL interpreter that supports the key operator (Dyalog APL 14.0+, GNU APL with ⌸ extension).
• Data in a two‑column array: data ← ⎕NPAR←2⍴⍳10 ; 1 2 3 4 5 6 7 8 9 10 – first column keys, second column values.
• Basic understanding of APL array syntax and the ⌸ operator’s syntax: keys ⌸ fn values.

Step‑by‑Step Procedure

1. Load or create the data array. The array must be a two‑column matrix where the first column contains the keys.
2. Define the aggregation function. Common choices:
   • Sum: +/
   • Maximum: ⌈/
   • Count: ⍴ applied after grouping.
3. Apply the key operator. The syntax is:

   result ← keys ⌸ fn values
   

   Where keys is a vector of the first column, values is the second column, and fn is the aggregation function.
4. Reshape if desired. For a two‑column output you can combine the keys and results:

   output ← (⍴result)⍴⍨⍪keys result
   

   This stacks the keys and aggregated values vertically and then transposes to match the original shape.

Concrete Example

Assume we have sales per region:

data ←  ⎕NS ''
⍝ Columns: 1=Region, 2=Sales
data ← 2 2⍴⍬
⍝ Region codes: 1–4, repeated
data[;1] ← 1 1 2 2 3 3 4 4 1 2
⍝ Sales values
data[;2] ←  10  20  30  40  50  60  70  80  90 100

Compute total sales per region:

keys ← data[;1]
values ← data[;2]
agg ← +/   ⍝ sum
result ← keys ⌸ agg values
output ← (⍴result)⍴⍨⍪keys result
⎕← output

Expected output:

1  120   ⍝ 10+20+90
2  170   ⍝ 30+40+100
3  110   ⍝ 50+60
4   70   ⍝ 70+80

Validation Checks

• Check that the number of aggregated rows equals the number of distinct keys:

  assert (≢result) ≡ ≢∪keys
  

• Verify each aggregated value by recomputing manually:

  ⍝ For each key, recompute sum
  ⍝ This uses the ∘ operator to apply the sum to the subset of values
  manual ← +/∘(keys=∘⊂⍤0)⊂values
  assert result ≡ manual
  

• Ensure no errors were signaled during the key operator call. Wrap in a Try/Catch if you want runtime safety:

  :Try
      result ← keys ⌸ agg values
  :CatchAll e
      ⎕← 'Error: ',⎕UCS e.⎕IO
      ⎕← 'Code: ',⎕UCS e.⎕EX
  :End
  

Common Errors & Recovery

• DOMAIN ERROR – Occurs if the key array is not a simple vector or scalar. Flatten nested keys first:

  keys ← ⊃¨⍬⍴keys   ⍝ flatten nested lists
  

• LENGTH ERROR – Happens if the key and value arrays have mismatched lengths.

  assert ≡/⍴¨keys values
  

• Memory exhaustion – Large datasets cause the key operator to build intermediate groups. Process in chunks or use external storage. Example chunking:

  chunkSize ← 1000
  ⍝ Split data into chunks
  chunks ← ⍬⍴⍨⍪{⍺[⍋⍺[;1];]⍵}¨data
  ⍝ Apply key operator to each chunk and concatenate
  partial ← ⊃¨chunks ⌸ agg¨chunks
  result ← ⊃¨partial ⌸ agg¨partial
  

Limitations

• The key operator is pure; it does not modify the original array.
• It requires the key array to be a vector of atomic values. Composite keys need encoding (e.g., using ⊂ or ⍎).
• Large groups can consume significant memory; consider streaming or external aggregation if scalability is critical.

Practical Verification

To double‑check correctness, run the same grouping logic in a reference language such as Python with pandas:

import pandas as pd
import numpy as np

# Sample data
keys = [1,1,2,2,3,3,4,4,1,2]
values = [10,20,30,40,50,60,70,80,90,100]

df = pd.DataFrame({'region': keys, 'sales': values})
print(df.groupby('region')['sales'].sum())

Ensure the printed totals match the output from APL.

Conclusion

The APL key operator (⌸) offers a concise, high‑performance way to perform group‑by aggregation. By following the steps above, you can construct reliable expressions, validate results, and handle common pitfalls. For large datasets, remember to monitor memory usage and consider chunking or external storage. Happy coding!