Convert column of single values into column of value lists with variable list length depending on data

Hello KNIME community,

I sincerely hope, that this is much easier than I thought and someone tells me the simple solution.

My dataset looks like this:


y-axis: value of analog sensor
x-axis: seconds/timestamp

My sensor produces around 1 row of data per second. some times there is a gap/pause of several minutes/hours.

What I want to do is cut out arrays for the “smaller” and the “larger” signal durations and transform them into lists. I also want to have lists of the gaps between them.

What I came up with, was to loop through the data and look for changes from 0 to positive value (=start) and then from value to zero (=end) for the signals. But I dont know how to implement this with loops.

What I want is for a dataset like this:

time [s] AI
1 0
2 1
3 2
4 3
5 4
6 4
7 4
8 4
9 4
10 4
11 4
12 4
13 4
14 4
15 4
16 4
17 4
18 4
19 4
20 3
21 2
22 1
23 0
24 0
25 0
26 1
27 2
28 3
29 3
30 2
31 1
32 0
33 0
34 0
35 0
36 0
37 1
38 2
39 3
40 4
41 4
42 4
43 4
44 4
45 4
46 4
47 4
48 4
49 4
50 4
51 4
52 4
53 4
54 4
55 3
56 2
57 1
58 0

to be transformed into this:

time[s] AI
[…, 1] [0,…, 0]
[2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22] [1, 2, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 3, 2, 1]
[23, 24, 25] [0, 0, 0]
[26, 27, 28, 29, 30, 31] [1, 2, 3, 3, 2, 1]
[32, 33, 34, 35, 36] [0, 0, 0, 0, 0]
[37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57] [1, 2, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 3, 2, 1]

I am trying to solve this since yesterday. Now I am at the end of my rope and still have no idea of the right way to approach the problem. Unfortunately my programming skills are limited and my googling skills seem to have forsaken me.

Kind regards and thank you in advance for all suggestions
Daniel

Here’s what I did. I used a Java Snippet node to avoid any looping, and a Lag Column node to prepare the data for indexing. It’s pretty hacky, but it works.
2019-03-29%2014_05_41-KNIME%20Analytics%20Platform

2019-03-29%2013_59_14-Group%20table%20-%202_276%20-%20GroupBy%20(Group%20by%20time%20and)

CalcIndexGroupIntoListsExample.knwf (11.5 KB)

3 Likes

Thank you ScottF,

this seems to work perfectly, even on the larger dataset of > 400k Rows (from ~ 5 days). At first glance I would even say, that I might understand what you did there :slight_smile:

I am interested to learn more about Java or Python integration/use in KNIME. Would it be possible for you to point me to some good tutorials/examples?

Thank you again. My headaches are getting back to normal levels now.

Have a nice day/night(?)
Daniel

2 Likes

Moritz wrote a short post about conditionals in the Column Expressions node, which is Javascript syntax:

Most of my scripting experience is with R and SAS, so I will defer to others who have better insight on Java and Python primer material. But I will say you can search for example integration workflows on the new Workflow Hub!

(For example: https://hub.knime.com/knime/workflows/Examples/07_Scripting/01_Java/01_Example_of_Java_Snippet*-L6cZ5ZEx93PIU7G)

Cheers!

1 Like

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