CODEWITHSUNDEEP
kdb+
delita
delita

Reputation: 1591

KDB: transpose a dual keyed table to a matrix

How can i transpose a dual keyed dictionay(x,y)

x y | z
- - | -
1 a | data
2 a | data
3 a | data
4 a | data
5 a | data 
1 b | data
2 b | data  
3 b | data 
4 b | data
5 b | data
1 c | data
2 c | data
3 c | data
4 c | data
5 c | data

to matrix style structure

x\y   1 2 3 4 5
     ------------    
 a   |data.......
 b   |data.......
 c   |data.......

I am not sure how to start. I have the concept of using flip group twice. Can anyone help?

Upvotes: 1

Views: 664

Answers (1)

Gilmer
Gilmer

Reputation: 420

I believe you want a pivot table. http://code.kx.com/q/cookbook/pivoting-tables/

Nick Psaris has a nice pivot function on his github from the qtips book;

pivot:{[t]
 u:`$string asc distinct last f:flip key t;
 pf:{x#(`$string y)!z};
 p:?[t;();g!g:-1_ k;(pf;`u;last k:key f;last key flip value t)];
 p}

q)t:2!ungroup ([]x:1+til 5;y:5#enlist `a`b`c;z:`data)
q)pivot 2!`y`x`z xcols 0!t
y| 1    2    3    4    5   
-| ------------------------
a| data data data data data
b| data data data data data
c| data data data data data

Upvotes: 6

Related Questions