cancel
Showing results for 
Search instead for 
Did you mean: 

Unpack nested column in table

powerpeanuts
New Contributor III

hi, how can we unpack nested column in a table? Thanks.

e.g. t:flip `a`b!((1 2 3);((4 5 6);(6 12 23);)(12 36 14)))

to t: flip `a`b1`b2`b3!((1 2 3);(4 6 12);(5 12 36);(6 23 14))

 

 

 

7 REPLIES 7

pfullen1997
New Contributor

Not too sure on a more elegant and generalisable way, but this code will produce the desired result:

(flip enlist[`a]!enlist[t `a]),'flip `b1`b2`b3!t `b

BaiChen
New Contributor II

I guess you mean:

 

 

(flip enlist[`a]!enlist[t `a]),'flip `b1`b2`b3!flip t `b

a b1 b2 b3

----------

1 4  5  6 

2 6  12 23

3 12 36 14

 

 

BaiChen
New Contributor II

select a:a ,b1:b[;0],b2:b[;1],b3:b[;2] from t

a b1 b2 b3

----------

1 4  5  6 

2 6  12 23

3 12 36 14

mcruz
New Contributor
New Contributor

Another option: flip `a`b1`b2`b3!flip raze each t

mmckenna
New Contributor
New Contributor

Its a little slower, but generalizes the solution a bit.

func:{[td] 
    c:where 0=type each flip td; / find which columns need unpacked
    ocn:cols td; / store all original column names for oder
    ncn:`$raze{string[x],/:string 1+til count first each flip[y]x}[;td]each(),c; / calculate new column names
    acn:ncn,ocn except c; / list of all final columns
    c:raze {x where x like y}[acn;]each string[ocn],'"*"; / all final columns in order
    
    flip c!flip raze each td / create final table with column names and column data
    }

q)t
a b       
----------
1 4  5  6 
2 6  12 23
3 12 36 14
q)func t
a b1 b2 b3
----------
1 4  5  6 
2 6  12 23
3 12 36 14
q)t3
a b        c d    
------------------
1 4  5  6  1 1 2 3
2 6  12 23 2 4 5 6
3 12 36 14 3 7 8 9
q)func t3
a b1 b2 b3 c d1 d2 d3
---------------------
1 4  5  6  1 1  2  3 
2 6  12 23 2 4  5  6 
3 12 36 14 3 7  8  9

darrenwsun
New Contributor III

A generalized solution that doesn't use hardcoded column names (provided as input) or assume the the cells in the nested column having the same length (padded with null for those with shorter length).

 

unpack:{[t;c]
  maxLen:max count each t[c];
  newCols:`$string[c] ,/: string 1+til maxLen;
  // concatenate the parts other than the specified column, with the unpacked parts
  // (x;::;y) is the parsed form of x[;y]
  ![t; (); 0b; enlist c] ,' ?[t; (); 0b; newCols!{(x;::;y)}'[c;til maxLen]]
 }

 

SJT
Contributor III
Contributor III

Assume here the column value is a matrix. Start with the new column names.

 

q)show ncn:`$string[`b],/:string 1+til count first t`b
`b1`b2`b3

 

Pair them with the matrix columns.

 

q)show d:ncn!flip t`b
b1| 4 6  12
b2| 5 12 36
b3| 6 23 14

 

Here’s the table with the nested column removed. (We use Functional qSQL rather than hard-code the column name.) 

 

q)show twc:![t;();0b;enlist`b]  / table without column
a
-
1
2
3

q)twc,'flip d  / Join Each new tuple
a b1 b2 b3
----------
1 4  5  6
2 6  12 23
3 12 36 14

 

Putting it together:

 

un:{[tbl;col]                                  / un-nest col in tbl
  mat:flip tbl col;                              / un-nest column
  ncn:`$(,/:) . string(col;) 1+til count mat;    / new column names
  ![tbl;();0b;enlist col],'flip ncn!mat }
q)un[t;`b]
a b1 b2 b3
----------
1 4  5  6
2 6  12 23
3 12 36 14