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?
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.
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.
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.
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
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..
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
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?
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.
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.