# count

Hi,

I have a data set including 20 columns and 900 rows having values 1 to 26. I want to count the value 1 in a row and get the result in one new column, comlum name is 1. then column two having the total count of 2 , this way upto column 26. and obviously the count has to be done for the all rows. how to do it?

jony

dont group by any column. In the aggregate section, choose to aggregate on all columns choosing "sum" for the aggregate type.

simon.

hi,

May be i was not clear about my problem..

My data sets have values like 1,2,3..randmoly in columns. i want to count how many 1s are there in one row and put it in new column. i know every row has maximum one 1 as value. so if there is something like if column1=1 or column2=1 and so on then put 1 otherwise zero.

p1 p2 p3 1 2
0 1 2 1 1
2 1 0 1 1
0 0 1 1 0
2 0 0 0 1

the first three columns in this example is my data set.  now as first row contains 1 in p2 so the resulting column 1 is 1 and this row also contains 2 so column 2 contains 1. row three does not contain any 2 so solumn 2 is o and it contains 1 so column 1 is 1.

This is the example of what i want to do.

sorry i created a table, but when i posted it something wnet wrong.. :(

every first 5 values makes are row from the example

p1 p2 p3 1 2
0 1 2 1 1
2 1 0 1 1
0 0 1 1 0
2 0 0 0 1

Hi Jony,

Try with "Value counter" node....

;-) MitchP

Alternatively,

you can use a rule engine node. In here you will enter if column1=1 then outcome is 1, then keep adding rules for the remaining columns. For default value label choose 0. You can then repeat this with another rule engine node for the value 2 and so on. It may be time consuming to set up, but could be much simplified with column loop start and end nodes if you are familiar with using loops in knime.

simon.

Another way could be using the column aggregator node, choosing the aggregate on all columns using unique concatenate as aggregate type. This gets all the values in to one column. You could again use a rule engine node on this new column which would be significantly quicker than the first post I provided

Simon.

Java snippet may be your way to go.

Steve

You could also use an unpivot, groupby (to count all values), filter (just the 1s) and join (to catch the 0s).  See attached for an example.

Aaron

hi aaron,

have you acually attached the file? i am trying downloading it, but it says there is no file. I can see the row_value_counting.zip file. but when i download it it says no file..

hi richards,

I was trying to follow your second idea, where i first used column aggregator, but when i am trying to impose rule engine in the new aggregated column (unique concatenate) i says it is not numeric column, so this types of rules can not be imposed may be. what to do then?

Ah yes, just put quotes around the 1, so it will be "1". Also now the column contains other values, you are not doing an exact match so in this second scenario it will be along the lines of column1 LIKE "1" then outcome 1

hope this helps

simon.

It should work now!

Hi Simon,

I have put \$Unique concatenate\$ LIKE"1" in the condition of the rule engine, but all the outcomes are zero, but i have mentioned the outcome as 1 and changed the default as zero. I can see 1s in unique concatenate but why its giving 0 for all? this is bit strange, am i missing something?

hi aaron,

Thanks for the example process, it works good. can you please tell me, if i want to do the same for values 1 to 39 then how the loop should be placed? i have tried using loop, but could not actaully solve it.

Hey jonny,

you are nearly there. I just tried it out, you need wild cards either side of your value, so for the text you mention, it will need to be;

\$Unique concatenate\$ LIKE"*1*"

simon.

Hi simon,

I am using your provided condition, but it is giving me 1s for the cases where 1 is not even available. only few results are zeros, where 1s are not available. but for lots of them result is 1 even there is no 1s.

Is this because you have numbers like "11" or "15" etc. It will obviously pick these up.

To avoid this, you can use this "* 1,*".

Simon.