CODEWITHSUNDEEP
kdb+
Carrein
Carrein

Reputation: 3371

Renaming a table column by its sub-string in KDB?

How would i rename a column in KDB with its substring?

Example:

Initial Table

    name  | number
------------------
  foo/bar |  999
  foo/log |  999
  foo/ton |  999
  foo/tin |  999

The query should truncate the name column by a certain delimiter, in the case the / character.

Resultant Table 1

    name  | number
------------------
    foo   |  999
    foo   |  999
    foo   |  999
    foo   |  999

Or remove a sub-string between the string i.e. the / character.

Resultant Table 2

    name  | number
------------------
   foobar |  999
   foolog |  999
   footon |  999
   footin |  999

Upvotes: 0

Views: 1990

Answers (2)

Rahul
Rahul

Reputation: 3969

For the case where you just want to remove substring, another option is to use ssr which will replace all occurrences of substring with target string/character.

q) t:([name:("foo/bar";"foo/bar/thing")]number:999 1000)

name           | number
---------------| ------
"foo/bar"      | 999   
"foo/bar/thing"| 1000 

q) update ssr[;"/";""]@'name from t

Output:

name         | number
-------------| ------
"foobar"     | 999   
"foobarthing"| 1000

Upvotes: 1

Michael McParland
Michael McParland

Reputation: 906

There are a number of ways you can go about this. 

t:([name:("foo/bar";"foo/log";"foo/ton";"foo/tin")]number:999 999 999 999)

You can you vs which allows you to split a string on a separator. Eg. the below returns a nested list of the two constituent strings which made up the original string. 

q)"/" vs "foo/bar"
"foo"
"bar"

From here you can then choose the string you wish to use in the updated column.

q)last "/" vs "foo/bar"
"bar"

Or can combine the output to one string using raze. 

q)raze "/" vs "foo/bar"
"foobar"

You can then use these methods in an update statement for your table. In this case I am using each-right with the sv operator so that each string in the name column is operated upon. You could also use a function to be called with each string. (eg. update name:{last "/" vs x}each name from t) 

q)update raze each  "/" vs/: name from t
name    | number
--------| ------
"foobar"| 999
"foolog"| 999
"footon"| 999
"footin"| 999
q)update last each  "/" vs/: name from t
name | number
-----| ------
"bar"| 999
"log"| 999
"ton"| 999
"tin"| 999
q)update first each  "/" vs/: name from t
name | number
-----| ------
"foo"| 999
"foo"| 999
"foo"| 999
"foo"| 999

For your first resultant table, if your filepath is always the same length a simpler method could be using the take operator on each string like below. 

q)update 3#'name from t
name | number
-----| ------
"foo"| 999
"foo"| 999
"foo"| 999
"foo"| 999

Upvotes: 5

Related Questions