Search code examples
kdb

split keyvalue pair data into new columns

My table looks something like this:

id   |   data
 1   |   A=1000 B=2000
 2   |   A=200 C=300

In kdb is there a way to normalize the data such that the final table is as follows:

id   | data.1 | data.2
 1   |      A |   1000
 1   |      B |   2000
 2   |      A |    200
 2   |      C |    300
Solution

  • You could use a combination of vs (vector from scalar), each-both ' and ungroup:

    q)t:([]id:1 2;data:("A=1000 B=2000";"A=200 C=300"))
q)t
id data
------------------
1  "A=1000 B=2000"
2  "A=200 C=300"

q)select id, dataA:`$data[;0], dataB:"J"$data[;1] from 
  ungroup update data: "=" vs '' " " vs ' data from t

id dataA dataB
--------------
1  A     1000
1  B     2000
2  A     200
2  C     300

    I wouldn't recommend naming the columns with . e.g. data.1