Coding Multiple Rule-Based Row Splitter Conditions

Hello! New user here with minimal coding background. I am a biochemist studying mitochondrial morphology as some background information.

I take images of mitochondria on a microscope, and then run these images through CellProfiler. This has an output that looks like this:

And I want to have an output with a separate table for each Shape/Size Measurement:

Tables 2

I will upload what I have so far for coding (screenshot only, I can’t figure out how to upload the actual workflow file) - I can only get the splitter to divide into two categories (image number 1 and 2+).

Logically I think what is easiest is to have a group loop function and have the rule-based row splitter set to increase number sequence (though I’m not sure how to do this). Then transpose the row/column for the proper layout (I don’t actually need to keep track of object number as a title, I use them as replicates).

Hi @nagemneuman and welcome to the forum.

To me, this looks like a job for the Pivoting node, where you’ll have a different Pivoting node for each measurement. (Or I suppose you could loop over them if there are many, but probably simpler to start with a node for each one.)

In the configuration, you’ll want to group on Image Number, pivot on Object Number, and then use first aggregation on the measurement itself.

See the attached example:
MultiplePivotingExample.knwf (24.9 KB)

2021-08-05 16_25_02-KNIME Analytics Platform

By the way, to export your workflow for upload to the forum, you can just go to File → Export KNIME Workflow to create a KNWF file like I did above. Then drag-and-drop the generated file into the forum text box and it will create a link for you.

6 Likes

Hi @ScottF - Thanks so much for your quick response!!

The Pivoting node seemed to help a lot! But now I’m missing my replicates or object numbers, and the image number is now represented as an average (not helpful when I need to remove outliers!). I’m assuming I need to add a loop in after the pivoting to do this? But not sure which or under what parameters?

Here is what my output looks like with your suggestion:

But I want every column to show a different replicate (object number) like this:

Capture

I was able to export my file with your instructions, and have attached it here! Thanks again!!

KNIME_project.knwf (22.4 KB)

Can you upload your input data as well, assuming it’s not confidential?

MyExpt_Puncta CONDENSED.xlsx (565.4 KB)

Yes! I meant to, but had to condense it down so that it would upload.

First of all, your initial GroupBy isn’t doing anything as configured, so I think you can remove that.

Secondly, make sure you look carefully at the output port of the Pivoting nodes. Here I’ve tried to produce a result that is close to what you’re looking for, although it’s not clear to me how you’re coming up with the values for each object in the Table above.

2021-08-06 11_01_54-KNIME Analytics Platform

2021-08-06 11_01_45-Filtered table - 7_21 - Column Filter

MultiplePivotingExample2.knwf (487.6 KB)

2 Likes

Thank you! This actually helped a lot!! I didn’t realize that the location of the output port was important, so thank you for that :sweat_smile:

I have figured out how to remove outliers right in KNIME, but am now stuck on how to turn “0” into missing/null values. Sometimes there is an error in Cell Profiler that doesn’t measure properly, so I get a reading of 0 for one of the object numbers. In Excel, I just use the “Find and Replace” function - but is there a way I can do this in KNIME? I did try the Multi Column Math Formula node but I can’t just seem to get the script right.

Thanks for your attention!

Hi @nagemneuman , nodes with multiple input or output ports are not expecting input randomly from the input ports, nor are they sending results randomly to the output ports.

Each input port and each output port have their own purpose. When not sure which port is for what, just do a quick reference to the node documentation. The documentation is easily accessible in the description of the node. It even includes what the configuration of the node means.

If we look at the Pivoting node from the Knime UI:

It explains what each of the output port is for.

If you want to turn zeros into missing values, you can use a bit of hacky syntax in the Math Formula (Multi Column) node, like this:

if($$CURRENT_COLUMN$$==0,1/0,$$CURRENT_COLUMN$$)

This basically says “if the value in the current column is equal to zero, then attempt to calculate 1/0. Otherwise return the original value.” KNIME will interpret the divide-by-zero error as a missing.

Be sure to click the “Replace Selected Columns” radio button if you implement this, otherwise you end up with a table that’s twice as wide. :slight_smile:

1 Like

Worked like a charm, thank you so much!!!

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.