Hello,
I am new to KDB ! I would like to split a columns into multiple ones:
t:(a:1,2;b:(“aa=45;ab=c;ac=37;ad=56”;“aa=5;ab=cdd;ac=;ad=6”))
Final table would have cols: a,b,aa,ab,ac,ad.
I do not know in advance the exact number of columns. But it is always separated by ; and there is always an = even when there is nothing. Also there are a combination of symbols, float,..
Is there an easy and quick Kdb magic for that ?
Thank you !
https://code.kx.com/v2/ref/file-text/#key-value-pairs
q)t,'((!/)"S=;"0:)@/:t`b
a b aa ab ac ad
-----------------------------------------------
1 “aa=45;ab=c;ac=37;ad=56” “45” ,“c” “37” “56”
2 “aa=5;ab=cdd;ac=;ad=6” ,“5” “cdd” “” ,“6”
Assuming you always have each key like you say:
q)t,'exec {(!/)"S=;"0:x}'[b] from t
a b aa ab ac ad
-----------------------------------------------
1 "aa=45;ab=c;ac=37;ad=56" "45" ,"c" "37" "56"
2 "aa=5;ab=cdd;ac=;ad=6" ,"5" "cdd" "" ,"6"
You can cast to different datatypes subsequently
Terry
q)exec ('[abaaabacad#;!/])'["S=;"0:/:b] from ta b aa ab ac ad--------------------------"" "" "45" ,"c" "37" "56""" "" ,"5" "cdd" "" ,"6"
It is hard to easily produce a robust method to handle the final types with this case. You could look into the csvguess function. The actual breaking up of the data can be achieved by the data column as the key value pairs they are
q)delete b from t,'{(!) . "S=;" 0: x} each tba aa ab ac ad----------------------1 “45” ,“c” “37” "56"2 ,“5” “cdd” “” ,“6”`
Breaking that up
tb is indexing into the columnq)tb"aa=45;ab=c;ac=37;ad=56""aa=5;ab=cdd;ac=;ad=6"
Then we can use the 0: overload from key value pairs, casting the keys to symbols
q) "S=;" 0: "aa=45;ab=c;ac=37;ad=56"aa ab ac ad"45" ,"c" "37" "56"
After that, we can create a dictionary from that result, where the keys are the first vector and values are the last. That’s done by wrapping (!) and applying with dot product. This is because I’m lazy and don’t want to create variables in my lambda
q)(!) . "S=;" 0: "aa=45;ab=c;ac=37;ad=56"aa| "45"ab| ,"c"ac| "37"ad| "56"
Applying this routine to each row from the table, with each , which also will promote the dictionary into a table. This is because each will cause the output to be a list, and a list of dictionaries is a table. The final join to concatenate the columns is done with each-both
Thanx guys !