# Dictionaries & tables

## ! (dict)¶

Syntax: x ! y

Where x and y are two lists of the same length, returns a dictionary

q)abc!1 2 3
a| 1
b| 2
c| 3


Because tables are lists of dictionaries with the same keys, ! applied to each member of a list will return a table of that list. For example:

q)(abc!)each(0 0 0;1 2 3;2 4 6)
a b c
-----
0 0 0
1 2 3
2 4 6


Flip me

The same result may be achieved with flip:


q)flipabc!flip(0 0 0;1 2 3;2 4 6)
a b c

0 0 0
1 2 3
2 4 6


.h.xt (JSON), .Q.qt (is table), .Q.V (table to dictionary)

## group¶

Syntax: group x

Returns a dictionary in which the keys are the distinct items of x, and the values the indices where the distinct items occur. The order of the keys is the order in which they appear in x.

q)group "mississippi"
m| ,0
i| 1 4 7 10
s| 2 3 5 6
p| 8 9


To count the number of occurrences of each distinct item:

q)count each group "mississippi"
m| 1
i| 4
s| 4
p| 2


To get the index of the first occurrence of each distinct item:

q)first each group "mississippi"
m| 0
i| 1
s| 2
p| 8


## ! (key)¶

Syntax: x ! y

Where x is a non-negative integer atom and y is a table, returns a table:

• if x is positive and y is simple, returns y with x key columns.
q)t:([]a:1 2 3;b:10 20 30;c:xyz)
q)2!t
a b | c
----| -
1 10| x
2 20| y
3 30| z

• if x is 0, returns a simple table.
q)t:([a:1 2 3]b:10 20 30;c:xyz)
q)0!t
a b  c
------
1 10 x
2 20 y
3 30 z


amending in place

If y is a table name, instead of returning a table ! amends y


q)t:([a:1 2 3]b:10 20 30;c:xyz)
q)0!t
t
q)t
a b  c

1 10 x
2 20 y
3 30 z


.Q.fk (foreign key), .Q.ft (apply simple), .Q.id (sanitize)

## ungroup¶

Syntax: ungroup x

Where x is a table, in which some cells are lists, but for any row, all lists are of the same length, returns the normalized table, with one row for each item of a lists.

q)p:((enlist 2);5 7 11;13 17)
q)r:((enlist"A");"CDE";"FG")
q)show t:([]s:abc;p;q:10 20 30;r)
s p      q  r
-----------------
a ,2     10 ,"A"
b 5 7 11 20 "CDE"
c 13 17  30 "FG"
q)ungroup t             / flatten lists p and r
s p  q  r
---------
a 2  10 A
b 5  20 C
b 7  20 D
b 11 20 E
c 13 30 F
c 17 30 G


Typically used on the result of xgroup or select.

q)\l sp.q
q)show t:select p,qty by s from sp where qty>200
s | p            qty
--| ------------------------
s1| p$p1p3p5 300 400 400 s2| p$p1p2    300 400
s4| p$,p4 ,300 q)ungroup t s p qty --------- s1 p1 300 s1 p3 400 s1 p5 400 s2 p1 300 s2 p2 400 s4 p4 300  ## xasc¶ Sorts a table in ascending order of specified columns. The sort is by the first column specified, then by the second column within the first, and so on. Syntax: x xasc y Where x is a symbol vector of column names defined in y, which is passed by • value, returns • reference, updates y sorted in ascending order by cols. The s# attribute is set on the first column given (if possible). The sort is stable, i.e. it preserves order amongst equals. q)\l sp.q q)s s | name status city --| ------------------- s1| smith 20 london s2| jones 10 paris s3| blake 30 paris s4| clark 20 london s5| adams 30 athens q)city xasc s / sort on city s | name status city --| ------------------- s5| adams 30 athens s1| smith 20 london s4| clark 20 london s2| jones 10 paris s3| blake 30 paris q)cityname xasc s / sort on city, and name within city s | name status city --| ------------------- s5| adams 30 athens s4| clark 20 london s1| smith 20 london s3| blake 30 paris s2| jones 10 paris q)statuscityname xasc s / sort on 3 columns, status first s | name status city --| ------------------- s2| jones 10 paris s4| clark 20 london s1| smith 20 london s5| adams 30 athens s3| blake 30 paris q)statuscityname xasc s / table given by reference, updated in place s q)s s | name status city --| ------------------- s2| jones 10 paris s4| clark 20 london s1| smith 20 london s5| adams 30 athens s3| blake 30 paris q)meta s / status column has sorted attribute c | t f a ------| ----- s | s name | s status| i s city | s  Sorting data on disk xasc can sort data on disk directly, without loading the entire table into memory.  q)t:([]b:sgasa;c:30 10 43 13 24;g:til 5) q):dat/t/ set .Q.en[:dat]t / write splayed table:dat/t/ q)\ls dat/t / splayed columns ,"b" ,"c" ,"g" q)c xasc:dat/t / sort table on disk by column c :dat/t q)t / in-memory table is unsorted b c g s 30 0 g 10 1 a 43 2 s 13 3 a 24 4 q)\l dat/t / load table from disk t q)t / table is sorted b c g g 10 1 s 13 3 a 24 4 s 30 0 a 43 2  ## xcol¶ Syntax: x xcol y Where y is a table (passed by value) and x is • a symbol vector of length no greater than count cols y returns y with its first count x columns renamed • a dictionary (since V3.6 2018.08.24) formed from two symbol vectors, of which the keys are all the names of columns of y, returns y with columns renamed according to the dictionary q)\l trade.q q)cols trade timesympricesize q)TimeSymbol xcol trade / rename first two columns Time Symbol price size ------------------------------ 09:30:00.000 a 10.75 100 q)trade:TimeSymbolPriceSize xcol trade / rename all and assign q)cols trade TimeSymbolPriceSize q)cols(ac!AC)xcol([]a:();b:();c:()) / selected columns A b C -----  ## xcols¶ Syntax: x xcols y Where y is a simple table (passed by value) of which symbol vector x lists some or all of the column names, returns y with x as its first column/s. q)\l trade.q q)cols trade timesympricesize q)trade:xcols[reverse cols trade;trade] / reverse cols and reassign trade q)cols trade sizepricesymtime q)trade:sym xcols trade / move sym to the front q)cols trade symsizepricetime  .Q.V (table to dictionary) ## xdesc¶ Syntax: x xdesc y Where x is a symbol vector of column names defined in y, which is passed by • value, returns • reference, updates y sorted in descending order by x. The s# attribute is not set. The sort is stable, i.e. it preserves order amongst equals. q)\l sp.q q)s s | name status city --| ------------------- s1| smith 20 london s2| jones 10 paris s3| blake 30 paris s4| clark 20 london s5| adams 30 athens q)city xdesc s / sort descending by city s | name status city --| ------------------- s2| jones 10 paris s3| blake 30 paris s1| smith 20 london s4| clark 20 london s5| adams 30 athens q)meta city xdesc s / s# attribute not set c | t f a ------| ----- s | s name | s status| i city | s  ## xgroup¶ Syntax: x xgroup y Where y is a table (passed by value) and symbol atom or vector x a list of column names in it, returns y grouped by x. It is equivalent to doing a select … by on y, except that all the remaining columns are grouped without having to be listed explicitly. q)ab xgroup ([]a:0 0 1 1 2;b:aacde;c:til 5) a b| c ---| --- 0 a| 0 1 1 c| ,2 1 d| ,3 2 e| ,4 q)\l sp.q q)meta sp / s and p are both columns of sp c | t f a ---| ----- s | s s p | s p qty| i q)p xgroup sp / group by column p p | s qty --| ------------------------------- p1| s$s1s2       300 300
p2| s$s1s2s3s4 200 400 200 200 p3| s$,s1         ,400
p4| s$s1s4 200 300 p5| s$s4s1       100 400
p6| s$,s1 ,100 q)select s,qty by p from sp / equivalent select statement p | s qty --| ------------------------------- p1| s$s1s2       300 300
p2| s$s1s2s3s4 200 400 200 200 p3| s$,s1         ,400
p4| s$s1s4 200 300 p5| s$s4s1       100 400
p6| s$,s1 ,100 q)ungroup p xgroup sp / ungroup flattens the groups p s qty --------- p1 s1 300 p1 s2 300 p2 s1 200 p2 s2 400 p2 s3 200 p2 s4 200 p3 s1 400 ...  ## xkey¶ Syntax: x xkey y Where symbol atom or vector x lists columns in table y, which is passed by • value, returns • reference, updates y with x set as the primary keys. q)\l trade.q q)keys trade symbol$()            / no primary key
q)sym xkey trade     / return table with primary key sym
sym| time         price size
---| -----------------------
a  | 09:30:00.000 10.75 100
symbol\$()
q)sym xkey trade   / pass trade by reference updates the table in place
,sym

.Q.ff` (append columns)