kdb: Add a column showing sum of rows in a table with dynamic headers while ignoring nulls

Question

I have a table whose columns are dynamic, except one column:A. The table also has some null values (0n) in it. How do I add another column that shows total of each row and either ignores the column that has "0n" in that particular row or takes 0 in its place. Here is my code, it fails on sum and also does not ignore nulls.

addTotalCol:{[]
    table:flip`A`B`C`D!4 4#til 9;
    colsToSum: string (cols table) except `A;   / don't sum A
    table: update Total: sum (colsToSum) from table;  / type error here. Also check for nulls
    :table;
   }

WooiKent Lee · Accepted Answer · 2014-03-26T09:05:27.587

I think it is better to use functional update in your case:

addTotalCol:{[]
    table:flip`A`B`C`D!4 4#til 9;
    colsToSum:cols[table] except `A;   / don't sum A
    table:![table;();0b;enlist[`Total]!enlist(sum;enlist,colsToSum)];
    :table;
   }

Reason why it is not working is because your fourth line is parsed as:

table: update Total: sum (enlist"B";enlist"C";enlist"D") from table;

Since sum only works with numbers, it returns 'type error since your inputs are string.

Another solution to use colsToSum as string input:

addTotalCol:{[]
    table:flip`A`B`C`D!4 4#til 9;
    colsToSum:string cols[table] except `A;   / don't sum A
    table:get"update Total:sum(",sv[";";colsToSum],") from table"
    :table;
   }

Basically this will build the query in string before it is executed in q.

Still, functional update is preferred though.

EDIT: Full answer to sum 0n:

addTotalCol:{[]
    table:flip`A`B`C`D!4 4#0n,til 9;
    colsToSum:cols[table] except `A;   / don't sum A
    table:![table;();0b;enlist[`Total]!enlist(sum;(^;0;enlist,colsToSum))];
    :table;
   }

Thank you. I'm using the functional form and that has fixed the type error but if any cell in a row has "0n" the total is "0n" for that row. Is there a way to ignore cells with nulls? — user2696565, Mar 25 '14 at 15:07
ahh I see, sure you can use fill (^) to fill the nulls with zero. addTotalCol:{[]table:flip`A`B`C`D!4 4#0n,til 9;colsToSum:cols[table] except `A;table:![table;();0b;enlist[`Total]!enlist(sum;(^;0;enlist,colsToSum))];:table;} But beware that the column type is promoted to float instead of long. — WooiKent Lee, Mar 25 '14 at 15:20

score 2 · Answer 2 · answered Mar 26 '14 at 13:47

I think there is a cleaner version here without a functional form.

q)//let us build a table where our first col is symbols and the rest are numerics,
  /// we will exclude first from row sums
q)t:flip `c0`c1`c2`c3!(`a`b`c`d;1 2 3 0N;0n 4 5 6f;1 2 3 0Nh)
q)//columns for sum
q)sc:cols[t] except `c0
q)///now let us make sure we fill in each column with zero,
  /// add across rows and append as a new column
q)show t1:t,'flip enlist[`sumRows]!enlist sum each flip  0^t sc
c0 c1 c2 c3 sumRows
-------------------
a  1     1  2
b  2  4  2  8
c  3  5  3  11
d     6     6
q)meta t1
c      | t f a
-------| -----
c0     | s
c1     | i
c2     | f
c3     | h
sumRows| f

kdb: Add a column showing sum of rows in a table with dynamic headers while ignoring nulls

2 Answers2