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)
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
--| --------
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
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.



At May 7, 2015 at 1:14 AM , Blogger Unknown said...

Pretty interesting! Couple of words pop up like tabulate, normalize, standardize. So maybe "pivTab","pivNom","pivStd".

At June 1, 2015 at 12:53 PM , Blogger dumb phoenix said...

I wrote something similar, called it .util.explodeDictCol


Post a Comment

Subscribe to Post Comments [Atom]

<< Home