CODEWITHSUNDEEP
kdb+
cjm2671
cjm2671

Reputation: 19466

How do I convert a dictionary of dictionaries into a table?

I've got a dictionary of dictionaries:

`1`2!((`a`b`c!(1 2 3));(`a`b`c!(4 5 6)))

 | a b c
-| -----
1| 1 2 3
2| 4 5 6

I'm trying to work out how to turn it into a table that looks like:

1 a 1
1 b 2
1 c 3
2 a 4
2 b 5
2 c 6

What's the easiest/'right' way to achieve this in KDB?

Upvotes: 0

Views: 495

Answers (3)

cjm2671
cjm2671

Reputation: 19466

Just an update to this, I solved this problem a different way to the selected answer.

In the end, I:

  • Converted each row into a table with one row in it and all the columns I needed.
  • Joined all the tables together.

Upvotes: 0

terrylynch
terrylynch

Reputation: 13572

What you're essentially trying to do is to "unpivot" - see the official pivot page here: https://code.kx.com/q/kb/pivoting-tables/

Unfortunately that page doesn't give a function for unpivoting as it isn't trivial and it's hard to have a general solution for it, but if you search the Kx/K4/community archives for "unpivot" you'll find some examples of unpivot functions, for example this one from Aaron Davies:

unpiv:{[t;k;p;v;f] ?[raze?[t;();0b;{x!x}k],'/:(f C){![z;();0b;x!enlist each (),y]}[p]'v xcol't{?[x;();0b;y!y,:()]}/:C:(cols t)except k;enlist(not;(.q.each;.q.all;(null;v)));0b;()]};

Using this, your problem (after a little tweak to the input) becomes:

q)t:([]k:`1`2)!((`a`b`c!(1 2 3));(`a`b`c!(4 5 6)));
q)`k xasc unpiv[t;1#`k;1#`p;`v;::]
k v p
-----
1 1 a
1 2 b
1 3 c
2 4 a
2 5 b
2 6 c

This solution is probably more complicated than it needs to be for your use case as it tries to solve for the general case of unpivoting.

Upvotes: 2

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

Not sure if this is the shortest or best way, but my solution is:

ungroup flip`c1`c2`c3!
  {(key x;value key each x;value value each x)}
    `1`2!((`a`b`c!(1 2 3));(`a`b`c!(4 5 6)))

Which gives expected table with column names c1, c2, c3

Upvotes: 2

Related Questions