CODEWITHSUNDEEP
kdb+
cjm2671
cjm2671

Reputation: 19466

How do I turn a list of dictionaries into a table in KDB?

I have decoded JSON to produce a list of the form:

T       v       vw       o      c       h       l        t            n
---------------------------------------------------------------------------
"VMAR"  62737   5.0392   5.19   5.07    5.3     4.9555   1.640639e+12 345
"RADA"  376883  9.6936   9.49   9.64    9.87    9.43     1.640639e+12 3216
"MMLG"  23222   27.5049  27.3   27.54   27.55   27.3     1.640639e+12 89
"ITM"   92400   51.3968  51.4   51.4    51.42   51.38    1.640639e+12 307
"IBDS"  111584  26.5429  26.47  26.55   26.57   26.47    1.640639e+12 270
"VGZ"   441111  0.665    0.675  0.67    0.684   0.66     1.640639e+12 533
"MGTX"  110759  22.3499  22.52  22.53   22.62   21.78    1.640639e+12 1672
"AVID"  241058  32.7345  32.16  32.96   33.08   31.87    1.640639e+12 4512
"VBR"   311815  177.2026 176.54 178.52  178.52  175.47   1.640639e+12 4046
"EBIZ"  25115   27.5717  27.52  27.55   27.74   27.48    1.640639e+12 239
"WWJD"  503027  31.2307  31     31.1874 31.26   31       1.640639e+12 248
"OXACW" 6300    0.4      0.4    0.4     0.4     0.4      1.640639e+12 1
"MSOS"  835924  25.4539  25.41  25.29   25.74   25.15    1.640639e+12 5566

Each row is a dict.

I'm trying to turn it into a table. How can I do this?

Code to reproduce, if you have TLS set up on your KDB:

baseUrl: ":https://api.polygon.io/v2/aggs/grouped/locale/us/market/stocks/"
apiKey: "?apiKey=B__L_1gX9IPlqaSW7JDXv_Ef4i_LhObVOwd_yT"
polyDay: {(.j.k .Q.hg baseUrl, x, apiKey)`results}
r: polyDay["2021-12-27"]

Upvotes: 0

Views: 350

Answers (1)

jasonfealy
jasonfealy

Reputation: 1111

For a nonconforming list of dictionaries, you can force it to collapse to a table like so:

q)show D:(`a`b!1 2;`b`c!20 30);
`a`b!1 2
`b`c!20 30
q)(distinct raze key each D)#/:D
a b  c
-------
1 2
  20 30

However, as your data contains various datatypes, that solution will result in some columns being of mixed type which is undesirable e.g. below, columns vw & n are contain mixed types

q)meta (distinct raze key each r)#/:r
c | t f a
--| -----
T | C
v | f
vw|
o | f
c | f
h | f
l | f
t | f
n |

The below should work. Credit to Attila Vrabecz, solution originally given here - How to merge a list of tables with different columns

q)\ts t:{raze((uj/)0#'x)uj/:x}enlist each r
70 9854464

or if you know the schema in advance you can simplify to

q)t:0#t // predefined schema
q)\ts t:raze t uj/:enlist each r
34 7768736
q)
q)meta t
c | t f a
--| -----
T | C
v | f
vw| f
o | f
c | f
h | f
l | f
t | f
n | f

Upvotes: 2

Related Questions