repeat the same calculation for multiple columns within a sheet

Hi
I am very new to Knime but I have found this tool full of potential and certainly enjoying using it.

I am trying to do a simple calculation involving one column dividing by another column, and I would like to do it for all 100 columns in the sheet. I can create the calculation with column expression node but I thought it was a bit inefficient since I had to set up the formula for every single column. I was wondering if there a more efficient way to do it? my gut feeling is that it may involve a loop. Thank you so much

1 Like

Hi @davidlan and welcome to the Knime Community.

If you are doing only calculation and are dealing with numbers, you can use the Math Formula node, which also has a version where you can apply the same calculation to multiple columns, and that node is called Math Formula (Multi Column)

Please take a look at them and let us know if you have any questions.

2 Likes

Thanks for the suggestion, but I am not to sure if that works for me.

I have attached my sheet here. What I have are two sets of calculation for each state. MA and MAT. I was trying to have MA divided by MAT for each state. I used column expression but found it a bit time consuming. Any suggestion would be welcomed. Thanks so much. example.xlsx (201.1 KB)

Welcome to the forum @davidlan.

There are probably a few ways to do this, but I think that the easiest way would be to restructure the data.

I find that new users to KNIME tend to think in Spreadsheet Mode (I’m not saying that you’re doing this), and it takes a while to realize that a KNIME table is not a spreadsheet. In KNIME it’s often much more efficient to work in rows compared to columns, and I think this is a prime example.

I think that this task would be much simpler if you had a table with 4 columns: Geography, State, MAT value, and MA value. Having a table with 8600 rows and 4 columns is easier to work with than a table with 167 rows and 105 columns. The final calculation result would be a single additional column instead of 100+, which makes using the resulting value a lot more straightforward.

It seems to me that the data you provided was pivoted at some point. Do you have the unpivoted data?
If not, you can come up with a workflow that does this, and it’ll take far less effort than trying to set up all the calculations in a Column Expressions node or multiple Math Formula nodes or to try to create a loop. You can always pivot the data again later if you really need that structure.

Here’s an example workflow:

image

All the restructuring happens inside a metanode:

And a single Math Formula node gives you the result you want:

4 Likes

Hi @davidlan , alright, since you are using pairs of columns for your calculation, it would be kind of difficult to do this via the Math Formula (Multi Column) indeed.

I put something together that does it another way, and it looks like this:

Results excluding original data (just run up to the last Column Filter):

Results appended to the original data (run up to last node):

Here’s the workflow: Repeat same calculation for multiple column pairs.knwf (251.0 KB)

I did indeed think in Spreadsheet Mode. Thanks for the suggestions and the workflow.

BTW do you have any suggestions on how to learn Knime?

2 Likes

@davidlan you could explore a collection of KNIME courses on the new platform:

https://knime.learnupon.com/

Then there is a free Udemy course about KNIME

Finally I have a larger collection about various sources and examples how to learn KNIME depending on how you like to learn (books, videos, examples, live teaching):

3 Likes

Nice explanation and solution @elsamuel :+1:

Become an active community member and help others with their questions and data issues :wink:

Welcome to Community,
Ivan

1 Like

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