CODEWITHSUNDEEP
kdb+q
Invictus
Invictus

Reputation: 4338

Convert a column of dictionary datatype to table in kdb

I was trying to convert a column which has a dictionary datatype on a table to a separate table to fetch information from it easily. This works with a simple uj for me when the dictionaries have the same set of keys. E.g. is shown below, which gives me a table with column names x and y as each dictionary in the table had two of the same keys.

q)t1:([] a:1 2 3 ; b: `a`b`c; d:(`x`y!(1 2); `x`y!(11 22); `x`y!(111 222)))
q){(uj/) value x }each select d from t1
x   y
-------
1   2
11  22
111 222

When I try and do this with a table which has a dictionary with different keys, shown below as 2 of these has keys x and y while the last one also has z in it. This would return me a kind of list of dictionaries rather than a table structure, how can I handle this?

q)t1:([] a:1 2 3 ; b: `a`b`c; d:(`x`y!(1 2); `x`y!(11 22); `x`y`z!(111 222 333)))
q){(uj/) value x }each select d from t1
`x`y!1 2
`x`y!11 22
`x`y`z!111 222 333
q)

Upvotes: 0

Views: 105

Answers (3)

terrylynch
terrylynch

Reputation: 13657

Another approach using a more custom iterator

q)(){x uj enlist y}/t1`d
x   y   z
-----------
1   2
11  22
111 222 333

/or (same thing)
{x uj enlist y}/[();t1`d]

Ultimately the same as Cathals approach

Upvotes: 0

mkst
mkst

Reputation: 684

Alternative method:

q)t1:([] a:1 2 3 ; b: `a`b`c; d:(`x`y!(1 2); `x`y!(11 22); `x`y`z!(111 222 333)))
q){(distinct raze key each x)#/:x} exec d from t1
x   y   z
-----------
1   2
11  22
111 222 333

Upvotes: 0

Cathal O'Neill
Cathal O'Neill

Reputation: 3179

One method:

q)(uj/)enlist each exec d from t1
x   y   z
-----------
1   2
11  22
111 222 333

enlist each is converting each dictionary to a table and (uj/) will return a union of their columns.

FWIW, in this scenario exec d from t1 has the same output as {(uj/) value x }each select d from t1

q)(exec d from t1)~{(uj/) value x }each select d from t1
1b

Upvotes: 2

Related Questions