Hi, I’m looking for a node to calculate the difference between each corresponding cell of two input tables, both having identical number of rows and columns, and delivering the results on a table of equal dimensions. Ideally, the node should also operate sums, products and divisions.
Hi @JScheidegger , I cannot think of a single node that can do that, although maybe there is an “exotic” node I’ve not seen, so I would think this possibly needs to be done as a series of nodes.
Can you confirm though, you said that the tables would have the same dimensions, but would they necessarily have the same column names. And if they do, would they be assumed to be in the same sequence? Alternatively would the solution just be treating the cells positionally irrespective of the column names?
My immediate thoughts are that this is likely a problem easily solved with a python node and a relatively short script.
I don’t know R, but that may also have some quick-win solution available.
Edit: The python to do this is straightforward, if you have python configured on your KNIME installation
e.g.
import knime.scripting.io as knio
import pandas as pd
# This example script performs an inner join on the node's input tables using pandas.
df0 = knio.input_tables[0].to_pandas()
df1 = knio.input_tables[1].to_pandas()
df2=df0 - df1 /* matrix subtraction */
knio.output_tables[0] = knio.Table.from_pandas(df2)
Matrix Calculation with Python Node.knwf (10.7 KB)
4 Likes
Here’s an ancient post with an R solution. The string of posts is the most interesting part. Seems to confirm @takbb about there being no “native” way to do this in Knime. I tested the workflow. It works, but I like @takbb 's solution a lot better.
1 Like
Thanks @takbb, I was afraid no native node was available. Regarding the workflow, while the column names will not be the same, the cell positions will definitly relate ones to others. Think of a table of SKU’s external diameters and another of internal ones, where I need thickness to be found. I don’t know how many SKU there are, neither how many measurements need difference to be calculated, but I am sure they match cell by cell between both tables.
Both Python and R are no my aquintances. I’ll study your sugestions to check against my needs and keep trying to build a node flow meanwhile. Best Regards!
1 Like
Thanks @rfeigel, I will give that a try. Regards!
1 Like
No probs @JScheidegger . Here is an alternative way using nodes (and a component). Ordinarily I’d use a Column Loop but as we are wanting to work through two tables positionally, I built it as a loop based on the number of columns and made use of a component to return each column name from its position.
The calculation is in the Math Formula node (highlighted).
Matrix Calculation using KNIME loop.knwf (72.1 KB)
2 Likes
I “like” click isn’t enough. Kudos to you. That workflow is really elegant.
2 Likes
Thanks @rfeigel, I really appreciate your comment…
Isn’t it funny how the availability of a simple innocuous component (in this case Column Name at Position, which is really just a Java snippet in disguise) can have such an impact on the overall approach, and turned it into a much more straightforward exercise.
That said, in KNIME 5.1 I just realised that this would probably be a use case for the new Table Cropper, as, if memory serves, that can return a column positionally, and then I believe the component wouldn’t be needed… will have a look at that later. 
Well, I took a look at KNIME 5.1’s version using Table Cropper. Slightly frustrating as it should have simplified it more than it does, except for the introduction of a new “gotcha”.
Unlike Java Snippet and Column Expressions, where column numbers start at zero, with Table Cropper they start at 1. We have no way have specifying that our loop starts at one, so we need to add 1 to the loop counter.
This is what I ended up with in KNIME 5.1
For comparison, here is previously uploaded KNIME 4.7 version, but laid out similarly to the 5.1 version
I also replaced the Column Renamer with the Regex version because I no longer know the column names. That could have been used equally in both versions.
Matrix Calculation using KNIME loop 5.1.knwf (164.7 KB)
@JScheidegger , try this 
I’ve packaged it (the KNIME 4.7 compatible version) into a component:
3 Likes
Thanks for the component. I will need to check my workflow connection to it, because the component complains of unequal dimensions, even with the outputs of previous nodes saying otherwise. Anyways, I managed to build a standard nodes solution (rather long, sure), but enough to lighten the pressure. I will keep your component in my toolbox, thanks again!
2 Likes
@JScheidegger
My first thought was python dataframes as @takbb already provided.
you only want the simple math operations? Import both tables, do an unpivot operations, subtract the two columns and do a pivot operation and write the output. Math operation can do any of the 4 operations.
br
Hi @Daniel_Weikert , that sounds like a cool trick. Hadn’t thought of that but I can see how it could work. I’ll have to give that a go when next at the pc. Nice one. 
1 Like
Are you after matrix functions, or are you just wanting to add one cell to the corresponding cell in the other table (or multiply, subtract etc)?
If it’s the second option, could you use a column appender node to combine them into one table, then use ordinary maths nodes on the resulting table?
Hi @justin.m, I am looking just for the simple matching cells math between two tables and getting the result in another table with same dimensions of both former ones. I’ll certainly try your solution, thanks a lot!
For ease of reference as a solution in future, I have uploaded here my take on the unpivot/pivot method suggested by @Daniel_Weikert , which you can see is faster than the looping solution from my “Matrix Calculator”.
In the meantime, I have updated the component so that you can select the numeric columns that are included in the matrices. It still uses them in the order they appear, but means if you have additional columns (such as SKU) these will be ignored by the calculator. NB, My component doesn’t make use of the unpivot/pivot simply because it would take me a bit more effort to work the required configs for those nodes into a generic component solution.
Matrix Calc using Pivoting.knwf (86.1 KB)
2 Likes
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.