Friday, April 24, 2015

Name! That! Function! (Pivot Table Edition)

What time is it, kids? That’s right, it’s time to play Name! That! Function!
Seriously though, I have a small, useful (IMAO) function I’ve been entering freehand in the console for something like two years now.
Normally, I’d put it in my personal library, but there’s a problem—I can’t think of a good name for it.
Here’s the function: {((union)over key each x)#/:x}.
And here it is in context, showing what it’s good for:
q)t:([]id:1 1 2 2 3;k:`a`b`b`a`b;v:1 2 3 4 5)
q)t
id k v
------
1  a 1
1  b 2
2  b 3
2  a 4
3  b 5
q)exec k!v by id:id from t
id|         
--| --------
1 | `a`b!1 2
2 | `b`a!3 4
3 | (,`b)!,5
q){((union)over key each x)#/:x}exec k!v by id:id from t
id| a b
--| ---
1 | 1 2
2 | 4 3
3 |   5
q)
So, there it is—an easy way to fix up ad-hoc pivots1 when your data doesn’t have all keys present (and in the same order) on all ids.
Anyone have any ideas what to call it?
  1. Note that for production pivots, particularly if they involve significant amounts of data, you should be using an optimized pivot function.

Labels: