CODEWITHSUNDEEP
kdb+
delita
delita

Reputation: 1581

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

Upvotes: 0

Views: 268

Answers (2)

Jonathon McMurray
Jonathon McMurray

Reputation: 2981

One option would be to make use of 0: & it's key-value parsing functionality, documented here https://code.kx.com/q/ref/file-text/#key-value-pairs e.g.

q)ungroup delete data from {x,`data1`data2!"S= "0:x`data}'[t]
id data1 data2
---------------
1  A     "1000"
1  B     "2000"
2  A     "200"
2  C     "300"

assuming you want data2 to be long datatype (j), can do

update "J"$data2 from ungroup delete data from {x,`data1`data2!"S= "0:x`data}'[t]

Upvotes: 4

Matt Moore
Matt Moore

Reputation: 2775

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

Upvotes: 1

Related Questions