Transposing syms to dynamic columns

Community Support
1

Hi All,

Wondering if anyone can help.  I’m sure theres a better way to do this.  I am trying to take a table with syms, transpose them to columns dynamically change the column names to include the sym.

I’ve done it this way but it seems a bit brute force and I’m sure theres better ways to do this.  

//gen tableq)t:flip (symval1val2)!(abc;(1f;2f;3f);(4f;5f;6f));sym val1 val2-------------a 1 4b 2 5c 3 6//gen all new col names q)newcols:raze {$(raze string[x[0],“_”]),/:string[x[1]]} each (exec sym from t),: enlist ((cols t) except sym);a_val1a_val2b_val1b_val2c_val1c_val2//update table t to includes all new colst:eval (!;t;();0b;(newcols)!(count newcols)#0n);sym val1 val2 a_val1 a_val2 b_val1 b_val2 c_val1 c_val2-------------------------------------------------------a 1 4b 2 5c 3 6//iterate each sym and set updated colsq)raze { t2:select from x[1] where sym=x[0]; eval (!;t2;();0b;(($string[t2[0][sym]],"_val1");($string[t2[0][sym]],"_val2"))!(t2[0][val1];t2[0][val2])) } each ((exec sym from t),\:enlist t)sym val1 val2 a_val1 a_val2 b_val1 b_val2 c_val1 c_val2a 1 4 1 4 0n 0n 0n 0nb 2 5 0n 0n 2 5 0n 0nc 3 6 0n 0n 0n 0n 3 6

Thanks!

2

The general pivot function on the Kx site will do this for you: https://code.kx.com/q/kb/pivoting-tables/ 

q)piv[t;1#`sym;1#`sym;`val1`val2;{`$"_"sv’string raze y,:/:x};{x,z}]

sym| a_val1 b_val1 c_val1 a_val2 b_val2 c_val2

—| -----------------------------------------

a | 1 4

b | 2 5

c | 3 6

Use the final function {x,z} to reorder the columns if you want the “a”'s together etc, and you can join on your original columns with a left join. 

Terry

3

Thanks very much for this Terry, and pointing to wiki article, much better.