CODEWITHSUNDEEP
kdb+
user9475848
user9475848

Reputation:

Split single column to multiple columns in KDB

I want to split values in a column to multiple columns after applying a complex function.

e.g. For the following trade table t , I want to split the sym into 2 separate columns sym and src. However, the function I would be applying would be slightly complex. 

q)t:([] time:10:01:01 10:01:03 10:01:04;sym:`goog.l`vod.l`apple.o;qty:100 200 150)
time     sym     qty
--------------------
10:01:01 goog.l  100
10:01:03 vod.l   200
10:01:04 apple.o 150

Upvotes: 0

Views: 3509

Answers (4)

Gilmer
Gilmer

Reputation: 420

Another option would be; 

q)(,'/)(t;flip`sym`src!exec flip ` vs'sym from t)
time     sym   qty src
----------------------
10:01:01 goog  100 l
10:01:03 vod   200 l
10:01:04 apple 150 o

Upvotes: 1

terrylynch
terrylynch

Reputation: 13572

If your table is very big and the sym column is very repetitive (which it looks like it will be if it's tick data) then the following will be much quicker:

f:{` vs'x}
@[t;`col1`col2;:;flip .Q.fu[f]t`sym]

Upvotes: 4

Thomas Smyth
Thomas Smyth

Reputation: 5644

You can create a table of sym and src by splitting on ., creating a dictionary then using flip to create a table:

q)show r:exec flip`sym`src!flip` vs/:sym from t
sym   src
---------
goog  l
vod   l
apple o

This can be joined to the original table using each-both ,':

q)t,'r
time     sym   qty src
----------------------
10:01:01 goog  100 l
10:01:03 vod   200 l
10:01:04 apple 150 o

If column order is important then this can be fixed with xcols:

q)`time`sym`src xcols t,'r
time     sym   src qty
----------------------
10:01:01 goog  l   100
10:01:03 vod   l   200
10:01:04 apple o   150

Upvotes: 2

nyi
nyi

Reputation: 3229

One of the way to get this done is : 

q)update sym:sym[;0] , mkt:sym[;1] from update ` vs/:sym from t
time     sym   qty mkt
----------------------
10:01:01 goog  100 l  
10:01:03 vod   200 l  
10:01:04 apple 150 o

If you are not intrested in any other columns except the one from the table that needs spliting then 

q)exec  {`s`mkt!` vs x}each sym  from t
s     mkt
---------
goog  l  
vod   l  
apple o

Upvotes: 2

Related Questions