Morel Cookbook

Problem

I have orders in a long format — one row per observation with a category column and a quantity. I want them wide — one row per region with a tea column and a coffee column. Or the other way: take a wide table and melt it into a long one.

Setup

val orders = [
  { region = "north", category = "tea",    quantity = 12 },
  { region = "south", category = "coffee", quantity = 30 },
  { region = "north", category = "tea",    quantity = 8  },
  { region = "west",  category = "coffee", quantity = 50 },
  { region = "east",  category = "tea",    quantity = 6  },
  { region = "south", category = "coffee", quantity = 20 },
  { region = "west",  category = "tea",    quantity = 14 },
  { region = "west",  category = "tea",    quantity = 25 }
];

Example

Long to wide — pivot category into separate tea and coffee columns, summing quantity within each. The trick is that sum over <expr> takes an expression, and the expression can be conditional. One aggregate per target column:

from ord in orders
  group ord.region
    compute { tea    = sum over (if ord.category = "tea"    then ord.quantity else 0),
              coffee = sum over (if ord.category = "coffee" then ord.quantity else 0) };

val it =
  [{coffee=0,region="east",tea=6},{coffee=50,region="south",tea=0},
   {coffee=0,region="north",tea=20},{coffee=50,region="west",tea=39}]
  : {coffee:int, region:string, tea:int} list

What's happening

Wide-pivoting in Morel is "one aggregate per target column, with a predicate inside the aggregate expression." It works because Morel's sum over e is just sum applied to a derived column — and that column can be anything the row permits, including a conditional. Write one name = ... over (if ... then ... else 0) per output column and you've got a pivot.

The catch is that the output columns are fixed at compile time. You can't pivot a set of categories you don't know until you run the query. That's a real constraint — most cookbook-style reshaping knows the list up front ("tea and coffee"), but a generic pivot_by(col) isn't something Morel 0.8 offers. When you need a dynamic shape, emit the long-format result and do the final reshape in whatever tool will consume it.

Long-to-wide's inverse — melt, unpivot, wide-to-long — is easier. Introduce a second scan over a list of category labels, then yield one row per (entity, label) pair, looking up the column by name with case.

Variations

Wide to long. The outer scan iterates rows; the inner scan iterates category labels; the case picks the right column:

val wide = [
  { region = "north", tea = 20, coffee = 0  },
  { region = "south", tea = 0,  coffee = 50 },
  { region = "west",  tea = 39, coffee = 50 }
];

from row in wide,
     cat in ["tea", "coffee"]
  yield { row.region, category = cat,
          quantity = case cat of
                       "tea"    => row.tea
                     | "coffee" => row.coffee
                     | _        => 0 };

See also