Convert Python code into Column Expression code

denzilsdn · December 1, 2021, 1:18pm

Hi.

I need the following Python code to be converted into a readable code in Column Expression:

import pandas as pd
input_table.columns = [‘Current Reading’]
l = list(input_table[‘Current Reading’])
for n,i in enumerate(l[:-1]):
if l[n]>l[n+1]:
l[n+1] = l[n]
output_table = pd.DataFrame(l)

Your assistance will be greatly appreciated.

bruno29a · December 1, 2021, 1:32pm

Hi @denzilsdn , looking at the Python code, it looks like it’s accessing different rows at the same time (n and n+1), and that’s not really possible to do in Column Expressions as far as I know. You’d actually use a Python script/snippet or Java script/snippet for these kind of operations.

However, since it seems to need to access only 2 rows and within the same column, alternatively you can use the Lag Column node, which allows you to make a copy of the n on the same row as n+1 (or vice versa). You can then just use the Rule Engine node to apply the rules for every row.

denzilsdn · December 1, 2021, 4:40pm

Okay thanks, I will try this method and will let you know.

Much appreciated.

Daniel_Weikert · December 1, 2021, 6:13pm

If possible share a sample of your data and the result. That certainly makes it easier to understand.
To me it looks like convert the column first into a list and then sort it.
So in KNIME just use the Sorter node.
br

denzilsdn · December 1, 2021, 6:48pm

Hi @Daniel_Weikert.

Here’s the input data:

And here’s the output data:

So, if the previous value (Row0) is higher than the current value (Row1), then the previous value must be rolled over to the current value and the same rule must be applied going forward.

Saying that, we cannot really cater for 12 rows, because it’s a large dataset and the number of rows changes depending on your unique identifier.

bruno29a · December 1, 2021, 7:44pm

Hi @denzilsdn , so the Lag Column node would not work as what you are looking for.

The Lag Column would lag the values based on the initial values from the table, but in your case, the evaluation needs to be done row by row after n+1 has been modified, and needs to be done on the new modified value.

So with the Lag Column node, you would get the rows shifted with the original values like this:

And if we apply the rules just based on these original values, you would get this:

Obviously, it’s not the results that you want.

You have no choice but to use a Loop in this case and keep track of what the n+1 has become.

I’ve put something together that does what you need, and it looks like this:

And it provides the same results as yours:

I included the Lag Column option too in my workflow just as an example of using the Lag Column, but as I said, it will not produce the expected results because it’s based on the original values of n+1: