kdb+: Amend/Apply and Error Trapping for Beginners’
In the latest of our ‘kdb+ guides for Beginners’ series, we’ve decided to tackle kdb+ Amend/Apply and Error Trapping. Whether we’re working with tables, lists or dictionaries, sometimes we want to only perform operations on a subset of our rows/elements. This is where we can utilize the power of both . and @.
Indexing
Indexing using @
NOTE: container can be a table/list/dictionary
- Selects elements from your container at indices provided.
q)list: 10 20 30 40 50
q)@[list;0 2]
10 30
Indexing using .
Although most structures in q can be modified adequately using the various forms of ‘@ Index’ – it does have some limitations. ‘@ Index’ can only index at one level of depth, i.e. if we have a list; it can index to an element in the list.
But what if we had a list of lists, and wanted to index into an element of one of the sub lists? This is where we can harness the power of ‘Dot Index’. Dot Index allows us to provide a list of indices and with each of these indices, it will traverse down the nested data structure – so we can access nested elements. Here is a further in dept blog on how Indexing works.
- Selects elements of your nested container at indices provided.
- If your indices are a single list, each subsequent index will index at an additional layer of depth.
- If your indices are a list of lists, each subsequent list of indices will index at increasing levels of depth.
q)list: (1 2 3; 4 5 6;7 8 9)
//Indices are a single list
q)@[list;0 1]
2
q)list: (1 2 3; 4 5 6;7 8 9)
//Indices are a list of lists
q)@[list;(2;1 2)]
8 9
Applying
Using @ Apply (3 Arguments)
- Applies your function to each of the specified indices in your container.
- Used when your function only requires one input parameter (which will be the container values at the specified indices).
Working with lists
In the example below we want to multiply supplied indices on our list by 10.
q)list:10 20 30 40 50
q)@[list; 0 2 4 ; {x*10} ]
100 20 300 40 500
One caveat is that type of the resultant list must match the type of your input list. In the example below, the result of attempting to cast these indices to strings will result in a mixed list of longs and strings – and therefore throws a ‘type error. This also applies to dictionaries and tables.
q)type 10 20 30 40 50
7h
q)@[10 20 30 40 50 ; 0 2 4 ; string ]
‘type
Working with dictionaries
In the example below we want to update certain dictionary values to lowercase. When working with dictionaries, as opposed to indexing with numeric indices, we index using the dictionary keys – as these uniquely identify one value/set of values. Note that ‘lower’ is a predefined function in q, which takes a string and returns the string in lowercase.
q)users:`0001`0002`0003`0005!("JOHN";"TOM";"BRIAN";"KATIE")
q)users
0001| “JOHN”
0002| “TOM”
0003| “BRIAN”
0004| “KATIE”
q)@[users;`0001`0004;lower]
0001| “john”
0002| “TOM”
0003| “BRIAN”
0004| “katie”
Working with tables
When using ‘@ Apply’ with tables, we can update a table column’s values seamlessly, as we can index into tables using their column names. In the example below, we have a table of users with plaintext passwords. We can hide each user’s password by running their passwords through an ‘md5’ hashing function – which essentially turns each password string into a 128 bit hash.
q)usertable:([]id:`0001`0002`0003`0004;name:("JOHN";"TOM";"BRIAN";"KATIE");
password:("john1952";"bluedoor9";"rolex@23!";"qwerty"))
q)usertable
id name password
————————
0001 “JOHN” “john1952”
0002 “TOM” “bluedoor9”
0003 “BRIAN” “rolex@23!”
0004 “KATIE” “qwerty”
//We can index to view values stored in that column (list of strings)
q)@[usertable;`password]
“john1952”
“bluedoor9”
“rolex@23!”
“qwerty”
q)@[usertable;`password;md5 each]
id name password
———————————————–
0001 “JOHN” 0x4b5472fe32502c3197ac8df8c7589158
0002 “TOM” 0x5b27b67a66d3aa275bf03b988d439f6d
0003 “BRIAN” 0xf5abb5ecd27ef30d575966896ff138a9
0004 “KATIE” 0xd8578edf8458ce06fbc5bb76a58c5ca4
Using @ Apply (4 Arguments)
- Applies your function to each of the specified indices in your container, using your ‘second_args’ as the second parameter to your function (your first parameter being your container value at the specified indices).
- Used when your function requires two input parameters.
- The length of ‘second_args’ must be equal to the length of indices supplied. However if an atom is supplied, it will be extended to a vector to match the indices length.
Working with lists
Similar to our list example with the 3 argument apply, we want to multiply our list at supplied indices by a number. The advantage in using the 4 argument apply is that it allows us to specify a different y argument (multiplier in this case) for each index.
q)list: 10 20 30 40 50
q)@[list; 0 2 4 ; * ; 10 100 1000]
100 20 3000 40 50000
As mentioned, if the length of ‘second_args’ does not match the length of supplied indices and is not an atom – we will get a length error.
q)list:10 20 30 40 50
//3 indices, 2 supplied values
q)@[list; 0 2 4; * ; 10 20]
‘length
Working with dictionaries
In this scenario, we want to append surnames onto a specified list of users in our ‘users’ dictionary. We can achieve this by utilizing the inbuilt “,” function, which can be used to join strings.
q)users:`0001`0002`0003`0005!(“JOHN”;”TOM”;”BRIAN”;”KATIE”)
q)@[users;`0002`0005;,;(” GREEN”;” JONES”)]
0001| “JOHN”
0002| “TOM GREEN”
0003| “BRIAN”
0005| “KATIE JONES”
Working with tables
We can use the 4 argument apply to increase/decrease the value of a column’s values by supplying a list of numbers to add. In the example below we want to update the quantity of each of our stocks to match trades that were executed throughout the day.
q)stocks:([] sym:`VOD`AAPL`GOOG; price:152.8 205.5 104.8; quantity: 40 500 80)
q)stocks
sym price quantity
——————-
VOD 152.8 40
AAPL 205.5 500
GOOG 104.8 80
q)@[stocks;`quantity;+;30 -10 19]
sym price quantity
——————-
VOD 152.8 70
AAPL 205.5 490
GOOG 104.8 99
NOTE: The number of second arguments supplied must equal the length of your table when indexing using column names.
Using . Apply (3 Arguments)
- Applies your function to each of the specified indices in your container.
- If your indices are a single list, each subsequent index indexes at an additional layer of depth.
- If your indices are a list of lists, each subsequent list indexes at an additional layer of depth.
- Used when your function only requires one input parameter (which will be the container values at the specified indices).
Working with nested lists
In the example below, we want to update the 2nd element in the 3rd list to 8. We can use dot apply along with the inbuilt ‘abs’ function (which returns the absolute value of a number) to index to this element and update it accordingly.
q)numSquare:(1 2 3;4 5 6;7 -8 9)
q)numSquare
1 2 3
4 5 6
7 -8 9
//We first index into the 3rd list, then the 2nd element of that list.
q).[numSquare;2 1;abs]
1 2 3
4 5 6
7 8 9
Working with nested dictionaries
In the example below, we have a nested dictionary of animals, subclasses & extinction status. Note that the ‘not’ function is an inbuilt q function which returns the opposite of the Boolean value it’s provided.
q)animals:`mammal`reptile`bird!((`human`mammoth!01b);(`lizard`turtle!00b);(`pigeon`dodo!00b))
q)animals
mammal | `human`mammoth!01b
reptile| `lizard`turtle!00b
bird | `pigeon`dodo!00b
//First we index into Bird, then Index into Dodo and finally apply our function.
q).[animals;`bird`dodo;not]
mammal | `human`mammoth!01b
reptile| `lizard`turtle!00b
bird | `pigeon`dodo!01b
Working with tables
We can index into table structures two different ways. We can select an entire row use row index, or an entire column’s values using column name. In the example below we want to update the volume of our aapl shares in our portfolio table.
q)portfolio:([] sym:`aapl`vod`goog; price:102 99 203; volume:500 400 900)
q)portfolio
sym price volume
-----------------
aapl 102 500
vod 99 400
goog 203 900
//Index into our table row (3rd row) – then index into the column we want to update (`volume). We can then apply our function.
q).[portfolio;(2;`volume); {x-300} ]
sym price volume
-----------------
aapl 102 500
vod 99 400
goog 203 600
Using . Apply (4 Arguments)
Working with nested lists
In the example below we want to update multiple elements inside multiple lists. Each of our second_args is extended to match the length of indices in their sublists.
q)numSquare:(1 2 3 4;5 6 7 8;9 10 11 12)
q)numSquare
1 2 3 4
5 7 8 9
9 10 11 12
//2 argument dot apply to view selected elements
q).[numSquare;(1 2;0 3)]
5 8
9 12
//We first index the 2nd and 3rd Lists, then elements 0th and 3rd element in each of these lists.
q).[numSquare;(1 2;0 3);*;10 100]
1 2 3 4
50 6 7 80
900 10 11 1200
Working with tables
In this example we demonstrate how we can update multiple elements from different columns/rows in a table from one functional dot apply. Below we update the latest price/quantity changes on two symbols in our portfolio.
q)stocks:([] sym:`VOD`AAPL`GOOG; price:152.8 205.5 104.8; quantity: 40 500 80)
q)stocks
sym price quantity
-------------------
VOD 152.8 40
AAPL 205.5 500
GOOG 104.8 80
//Index into 1st and 2nd rows. Then index `price`column on these two rows.
q).[stocks;(1 2;`price`quantity);+;(0.5 -20;-6.3 150)]
sym price quantity
-------------------
VOD 152.8 40
AAPL 206 480
GOOG 98.5 230
Working with keyed tables
Keyed tables work a little differently to regular tables due to their structure ‘under the hood’. To Index into a keyed table, instead of using row number to select an individual row, we use the keyed column’s values. In the example below we have a keyed table with holds information on our users and want to increase the age and weight of one of our users ‘matt’.
q) userstats:([name:`lisa`chris`matt] age:21 35 26; weight: 125 190 145; height: 168 221 91)
q)userstats
name | age weight height
-----| -----------------
lisa | 21 125 168
chris| 35 190 221
matt | 26 145 91
//Index by key column value `matt. Then index into our rows using `age`weight.
q).[userstats;(`matt;`age`weight);+;1 25]
name | age weight height
---- | -----------------
lisa | 21 125 168
chris| 35 190 221
matt | 27 170 91
Trapping
Trapping using @
Similar to a ‘try catch’ statement in more conventional programming languages, we can also trap errors in q. Note that our ‘error statement’ can have two different forms:
- A generic output of any type e.g. error string/ integer error code.
- A function where the ‘x’ argument is set by default to the console thrown error (as a string).
- Function below accepts 1 argument:
//We error trap our function – but it completes successfully, so trap isn't hit.
q)func: {1 + x}
q)@[ func ; 2 ; "Function has returned an error"]
3
***************** GENERIC TRAP ******************
//Try to add a long and a symbol – error trap is hit.
q)@[ func; `hello ; "Function has returned an error"]
"Function has returned an error"
***************** FUNCTION TRAP *****************
q)@[ func; `hello ; {"Failed to complete addition function – error is: ", x}]
"Failed to complete addition function - error is: type"
Trapping using .
We can use dot notation to trap errors when we have a function that requires multiple arguments.
- ‘args’ is a list of input parameters.
- Each ‘args’ will be passed sequentially to the function as individual input parameters.
//Successful execution – error trap not hit.
q) .[ {x + y + z} ; (20;50;30) ; "Function has returned an error"]
100
***************** GENERIC TRAP ******************
//Try to add two longs and a symbol – error trap is hit.
q).[ {x + y + z} ; (20;50;"c") ; "ERROR"]
"ERROR"
***************** FUNCTION TRAP *****************
q).[ {x + y + z} ; (20;50;"c") ; {"Function has returned an error: ", x}]
"Function has returned an error: type"
Note that if we supply more parameters than our function can handle, we will get a ‘rank’ error:
//Expects 2 input parameters, 3 are supplied.
q).[ {x + y} ; (20;50;30) ; {"Function has returned an error ", x}]
"Function has returned an error: rank"
