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"]
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments