KX Community

Find answers, ask questions, and connect with our KX Community around the world.

Home Forums kdb+ Unpack nested column in table

  • Unpack nested column in table

    Posted by powerpeanuts on August 23, 2022 at 12:00 am

    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))

     

     

     

    powerpeanuts replied 1 week, 6 days ago 7 Members · 7 Replies
  • 7 Replies
  • pfullen1997

    Member
    August 23, 2022 at 12:00 am

    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

    Member
    August 23, 2022 at 12:00 am

    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

    Member
    August 23, 2022 at 12:00 am

    Another option:

    flip `a`b1`b2`b3!flip raze each t
  • baichen

    Member
    August 23, 2022 at 12:00 am

    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

     

     

  • mmckenna

    Member
    August 23, 2022 at 12:00 am

    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 order 
               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

    Member
    August 23, 2022 at 12:00 am

    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]] 
    }

     

  • Laura

    Administrator
    September 6, 2022 at 12:00 am

    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

     

    Heres 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

     

     

Log in to reply.