Detect time Shift between two tables

Hi there,

I would like to combine to measurement tables to do some further mining activities. Unfortunately both tables use a different time steps (table 1: 1s, table 2: 0.1s) with an unknown time shift but with one common signal (a temperature signal).
What I am doing right now is to create an Excel File with the time and temperature from both columns. Create a scatter plot of both signals and add an additional time shift manually, so both signals match perfect (lay on each other).
Is there any way to do this automatically? In Knime?

Andreas

Hi Andreas!

Probably you can but I’m not sure I got it. Can you provide an example?

Br,
Ivan

Sure.

Upload_KnimeForum.xlsx (98.5 KB)

The result would be offset = -10.

1 Like

Hi!

I see. It can be done. In example you provided you just need to count number of rows with data value less then zero.

Just couple of questions. You know that time steps are always 1 sec for table one and 0,1 sec for table two? Does table one always start with data 0 are offset needs to be calculated for table two?

So I have read your data into Knime. Filter values less then 0. Extracted dimensions and used it in Column Expressions node to get shifted time.

Br,
Ivan

Hi Ivan,

in my case the time stepping is always 0.1s and 1s for both data sets. And yes one table always starts with 0.
I cannot download your example, so I think that my example was probably misleading.

  • The time shift between both signals is unknown. (Here in my example the result would be -10s).
  • The data (temperature measurement in real case) has no zero crossing or similiar.

I thought for illustration it would be convinient to work with a sinus signal :slight_smile:

In Matlab I would do something like this:

  • pick “1s” time vector as main time
  • interpolate measurement values from 0.1s stream onto main time vector
    assuming 0s as offset
  • calculate Root Mean Square Error of “interpolated_values - measured_1s_values”
  • repeat with changing offsets until RMS is under a certain limit

So I think you have to use some (maybe linear equation) solving of this problem.

Andi

Hi!

Yep. It was a bit misleading and those values were familiar :slight_smile:

I will think a bit about this one and get back to you.

Br,
Ivan

Hi,

one minor step ahead… Just found out, that there are some optimization nodes available. So I “just” have to add the interpolation and mathematics nodes… Just ehm… Well this does not work.

Do you have an idea how to interpolate the data?

Andreas

Hi @ActionAndi!

I forgot about this one? Did you manage this?

Br,
Ivan

Hi Ivan,
yeah I managed it… with Matlab. It was the fasted solution for me. The interpolation issue from my last post I solved in Knime but the whole workflow was not very efficient. This was based on the optimization node, which does a constant stepping only. So if you start pretty far away from the solution it takes hours…
I recently dicovered the recursive loop nodes. Maybe I can create a Newton Solver with variable step size.

Andi

Hi!

ok. Good luck :slight_smile:

Br,
Ivan