Replacing Zeros with Avg from other rows

[RichardFL]

In the table below, I need to replace the zero by the average visibility of all DRA59 items listed.

How can I accomplish this using Knime? what node(s) could I use?
Do I need a javascript?

[mlauber71]

You could use this concept of moving average but use it to calculate the avg value without the missing or zeros. And then replace them with the avg value using a Rule Engine

Cumulative sum with a condition

Hi @tomekk! Don’t get discouraged! It can be pretty simple with KNIME. Like this: [GroupCumulativeSum] In case I haven’t missed something of course And don’t hold it against @armingrudd and @mlauber71. They like KNIME nodes so much they can’t get enough of it Now you can use some rule based node to see which invoice exceeded a certain project sum 2019_01_23_Group_Cumulative_Sum.knwf (15.8 KB) Have a nice day, Ivan

Replace missing data baed on mean of alternative column

hi there I would use the group by node to group by "new.title" and getting the mean for "age". having theses numbers, got to the main table and use the rule engine node and set it like: $new.title$ LIKE "master"  AND MISSING $age$ => 888 $new.title$ LIKE "mrs"  AND MISSING $age$ => 888 $new.title$ LIKE "XYZ"  AND MISSING $age$ => 888 where 888 is your dedicated mean that you got from the group by node..   NOTE you have to set the rule engine node to replace column age   good luck!

[ipazin]

Hi there!

Additionally what you can do is to replace all 0s with missing values using Rule Engine node with only following rule:
$visibility$ > 0 => $visibility$ and option replace column checked.

Then there is Missing Value node that you can use inside of a Loop which will replace missing values with desired function (mean in your case). Check this topic:

Smarter Missing Value approach

Hi there! Why not using a bit less nodes [GroupMeanForMissingValue] 2019_01_16_Group_Mean_For_Missing_Value.knwf (16.0 KB) Br, Ivan

Br,
Ivan

[RichardFL]

Thank you all for your ideas and replies. Greatly appreciate it.

I will test, Ivan’s workflow seems to be the simplest and easiest to implement.

I greatly appreciate ideas for my other thread:

Replacing missing values with value from different row

In the table below, some rows have items with missing weight value. I need to replace missing value by getting the item’s weight from a row where it is not missing. How can I do this using knime, “Missing Value” node did not help… Thanks! [missingValues]

Thanks!