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="north",tea=20},{coffee=50,region="south",tea=0},
   {coffee=50,region="west",tea=39},{coffee=0,region="east",tea=6}]
  : {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.9 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. Morel 0.9's yieldAll step is the direct way to say it: an ordinary yield returns one row per input row, while yieldAll returns a list per input row and flattens the lot. One wide row in, one row per column out.

Variations

Wide to long with yieldAll. Each input row expands to one row per category:

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

from row in wide
  yieldAll [ { region = row.region, category = "tea",    quantity = row.tea },
             { region = row.region, category = "coffee", quantity = row.coffee } ];

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

Before 0.9 you'd write this as a second scan over a list of labels with a case to pick the column — which works, but needs a dead _ => 0 arm that yieldAll doesn't:

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