how to align two columns with different times

Hi, I have to align two timestamps, but I need help!

Input data: ML

the first two columns refer to the events codes (column 1) and the corresponding timestamps (column 2, in millieconds) from a time 0, of when each events occurs

ML (:,1)) = (events codes)

9 62 15 40 54 50 18 9 63 15 40 54 50 18 9 64 15 40 54 50 18 9 65 15 40 54 50 18 9 66 15 40 18 9 67 15 40 54 50 18 9 68 15 40 54 50 18 9 69 15 40 54 50 18 9 70 15 40 54 50 18 9 71 15 40 54 50 18 9 72 15 40 54 50 18 9 73 15 40 54 50 18 9 74 15 40 54 50 18 9 75 15 40 54 50 18 9 76 15 40 54 50 18 9 77 15 40 54 50 18 9 78 15 40 54 50 18 9 79 15 40 54 50 18 9 80 15 40 54 50 18 9 81 15 40 54 50 18 ;

ML(:,2) = ( timestamp 1, milliseconds of the times of each code from 0)

4974 4980 5112 5579 5968 6042 6374 6877 6882 6912 7545 8066 8133 8419 8921 8925 9012 9612 10115 10247 10533 11036 11040 11112 11679 12082 12102 12393 12896 12899 12979 13445 16592 17095 17098 17179 17545 17917 18000 18307 18809 18813 18913 19645 20148 20163 20462 20964 20968 21079 21612 21999 22065 22422 22926 22930 23112 23613 24010 24081 24384 24888 24893 24912 25379 25946 26006 26420 26922 26925 27112 27612 27991 28075 28368 28870 28873 28979 29546 30045 30061 30374 30875 30878 30946 31512 32029 32090 32349 32851 32854 33046 33712 34107 34164 34487 34988 34991 35046 35579 36093 36165 36460 36962 36965 37046 37446 37843 37919 38211 38712 38715 38780 39279 39655 39672 39975 40477 40479 40580 41146 41691 41753 42002 42504 42507 42613 43146 43657 43728 44019 44520 44523 44579 45180 45576 45641 45936

The third column is a separate time stamp (timestamp 2) , wtih the time when the codes40 occur from a time 0, relative to THAT timestamp (not to the timestamp 1) . The absolute values are different. In this timestamp, there are only the value of the code 40. The zeroes values correspond to a NaN.

ML(:,3)

0 0 0 1989 0 0 0 0 0 0 3464 0 0 0 0 0 0 5014 0 0 0 0 0 0 6564 0 0 0 0 0 0 7889 0 0 0 0 10964 0 0 0 0 0 0 12539 0 0 0 0 0 0 14014 0 0 0 0 0 0 15514 0 0 0 0 0 0 16839 0 0 0 0 0 0 18514 0 0 0 0 0 0 19964 0 0 0 0 0 0 21439 0 0 0 0 0 0 23089 0 0 0 0 0 0 24489 0 0 0 0 0 0 25889 0 0 0 0 0 0 27265 0 0 0 0 0 0 28665 0 0 0 0 0 0 30165 0 0 0 0 0 0 31690 0 0 0

even thought the absolute values of the two timestamps are different, nonetheless, the difference between two consecutive times of codes 40 in the column 2, should be the same as the difference bettween the consecutive times of code s 40 in colum 3. If this is the case, I can fill the zeroes value of the column 3, and obtain a complete timestamp of column 3 (timestamp 2) which correspond to the event codes:

%% select only the values which correspond to the code 40

gt = find(ML(:,1) == 40;

new_ML = ML(gt,:);

%% differences between times of consecutive codes 40:

timestamp1_diff = new_ML(2:end-1,2) - new_ML(1:end-1,2)

timestamp2_diff = new_ML(2:end-1,3) - new_ML(1:end-1,3)

However, the values of timestamp1_diff and timestamp2_diff are not the same. The values of the timestamp1_diff are alwasy between 400 and 550 bigger than the timestamp2_diff That means, that the values of the column 3 do not correspond to the code 40. I have to find to which code the values of the timestamp2 (ML:,3) correspond.

I tried, but I gave up!

any idea??

thanks

Anna

[Umar]

Hi Anna,

To align two timestamps in MATLAB, you can compare the differences between consecutive times of a specific event code in both timestamps. In your case, you want to align the timestamps based on the event code 40. Here's how you can do it:

Select the rows in the input data (ML) that correspond to the event code 40:

gt = find(ML(:,1) == 40); new_ML = ML(gt,:);

Calculate the differences between the consecutive times of event code 40 in both timestamps:

timestamp1_diff = new_ML(2:end,2) - new_ML(1:end-1,2); timestamp2_diff = new_ML(2:end,3) - new_ML(1:end-1,3);

Compare the differences between the two timestamps:

diff_ratio = timestamp1_diff ./ timestamp2_diff;

If the values of diff_ratio are close to 1, it means that the differences between consecutive times of event code 40 in both timestamps are similar. However, if the values are significantly different, it indicates that the values in column 3 (timestamp 2) do not correspond to event code 40.

To determine which event code the values in column 3 correspond to, you can compare the differences between consecutive times of different event codes. For example, you can calculate the differences between consecutive times of event code 15 in both timestamps and compare them with timestamp2_diff. If the differences match, it means that the values in column 3 correspond to event code 15.

Repeat this process for other event codes until you find a match. Once you determine the correct event code, you can fill the zeros in column 3 with the corresponding values.I hope this helps! Let me know if you have any further questions.

[Cris LaPierre]

Here is a runnable example after fixign some errors found in the code shared.

% (events codes)
ML (:,1) =   [9    62    15    40    54    50    18    9    63    15    40    54    50    18    9    64    15    40    54    50    18    9    65    15    40    54    50    18    9    66    15    40    18    9    67    15    40    54    50    18    9    68    15    40    54    50    18    9    69    15    40    54    50    18    9    70    15    40    54    50    18    9    71    15    40    54    50    18    9    72    15    40    54    50    18    9    73    15    40    54    50    18    9    74    15    40    54    50    18    9    75    15    40    54    50    18    9    76    15    40    54    50    18    9    77    15    40    54    50    18    9    78    15    40    54    50    18    9    79    15    40    54    50    18    9    80    15    40    54    50    18    9    81    15    40    54    50    18]';
% ( timestamp 1, milliseconds of the times of each code from 0)
ML(:,2) = [4974    4980    5112    5579    5968    6042    6374    6877    6882    6912    7545    8066    8133    8419    8921    8925    9012    9612    10115    10247    10533    11036    11040    11112    11679    12082    12102    12393    12896    12899    12979    13445    16592    17095    17098    17179    17545    17917    18000    18307    18809    18813    18913    19645    20148    20163    20462    20964    20968    21079    21612    21999    22065    22422    22926    22930    23112    23613    24010    24081    24384    24888    24893    24912    25379    25946    26006    26420    26922    26925    27112    27612    27991    28075    28368    28870    28873    28979    29546    30045    30061    30374    30875    30878    30946    31512    32029    32090    32349    32851    32854    33046    33712    34107    34164    34487    34988    34991    35046    35579    36093    36165    36460    36962    36965    37046    37446    37843    37919    38211    38712    38715    38780    39279    39655    39672    39975    40477    40479    40580    41146    41691    41753    42002    42504    42507    42613    43146    43657    43728    44019    44520    44523    44579    45180    45576    45641    45936];
%  time when the codes40 occur from a time 0, relative to THAT timestamp (not to the timestamp 1)
ML(:,3) = [0    0    0    1989    0    0    0    0    0    0    3464    0    0    0    0    0    0    5014    0    0    0    0    0    0    6564    0    0    0    0    0    0    7889    0    0    0    0    10964    0    0    0    0    0    0    12539    0    0    0    0    0    0    14014    0    0    0    0    0    0    15514    0    0    0    0    0    0    16839    0    0    0    0    0    0    18514    0    0    0    0    0    0    19964    0    0    0    0    0    0    21439    0    0    0    0    0    0    23089    0    0    0    0    0    0    24489    0    0    0    0    0    0    25889    0    0    0    0    0    0    27265    0    0    0    0    0    0    28665    0    0    0    0    0    0    30165    0    0    0    0    0    0    31690    0    0    0];
ML(:,3) = standardizeMissing(ML(:,3),0);

%% select only the values which correspond to the code 40
gt = find(ML(:,1) == 40);
new_ML = ML(gt,:);
%% differences between times of consecutive codes 40:
timestamp1_diff = diff(new_ML(:,2)) 

timestamp1_diff = 19x1

1966 2067 2067 1766 4100 2100 1967 2001 1766 2233

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

timestamp2_diff = diff(new_ML(:,3)) 

timestamp2_diff = 19x1

1475 1550 1550 1325 3075 1575 1475 1500 1325 1675

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Now look at the difference between the time stamps. It is inconsistent. This suggests that your underlying assumption about the timestamps is incorrect.

tdiff = timestamp1_diff-timestamp2_diff

tdiff = 19x1

491 517 517 441 1025 525 492 501 441 558

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Answer 1

[Cris LaPierre]

Your timestamps are using different scales. You can see this by fitting a line to the series by plotting timestamp 1 against timestamp 2.

ML = [9    62    15    40    54    50    18    9    63    15    40    54    50    18    9    64    15    40    54    50    18    9    65    15    40    54    50    18    9    66    15    40    18    9    67    15    40    54    50    18    9    68    15    40    54    50    18    9    69    15    40    54    50    18    9    70    15    40    54    50    18    9    71    15    40    54    50    18    9    72    15    40    54    50    18    9    73    15    40    54    50    18    9    74    15    40    54    50    18    9    75    15    40    54    50    18    9    76    15    40    54    50    18    9    77    15    40    54    50    18    9    78    15    40    54    50    18    9    79    15    40    54    50    18    9    80    15    40    54    50    18    9    81    15    40    54    50    18;
    4974    4980    5112    5579    5968    6042    6374    6877    6882    6912    7545    8066    8133    8419    8921    8925    9012    9612    10115    10247    10533    11036    11040    11112    11679    12082    12102    12393    12896    12899    12979    13445    16592    17095    17098    17179    17545    17917    18000    18307    18809    18813    18913    19645    20148    20163    20462    20964    20968    21079    21612    21999    22065    22422    22926    22930    23112    23613    24010    24081    24384    24888    24893    24912    25379    25946    26006    26420    26922    26925    27112    27612    27991    28075    28368    28870    28873    28979    29546    30045    30061    30374    30875    30878    30946    31512    32029    32090    32349    32851    32854    33046    33712    34107    34164    34487    34988    34991    35046    35579    36093    36165    36460    36962    36965    37046    37446    37843    37919    38211    38712    38715    38780    39279    39655    39672    39975    40477    40479    40580    41146    41691    41753    42002    42504    42507    42613    43146    43657    43728    44019    44520    44523    44579    45180    45576    45641    45936;...
    0    0    0    1989    0    0    0    0    0    0    3464    0    0    0    0    0    0    5014    0    0    0    0    0    0    6564    0    0    0    0    0    0    7889    0    0    0    0    10964    0    0    0    0    0    0    12539    0    0    0    0    0    0    14014    0    0    0    0    0    0    15514    0    0    0    0    0    0    16839    0    0    0    0    0    0    18514    0    0    0    0    0    0    19964    0    0    0    0    0    0    21439    0    0    0    0    0    0    23089    0    0    0    0    0    0    24489    0    0    0    0    0    0    25889    0    0    0    0    0    0    27265    0    0    0    0    0    0    28665    0    0    0    0    0    0    30165    0    0    0    0    0    0    31690    0    0    0]';
ML(:,3) = standardizeMissing(ML(:,3),0);

%% select only the values which correspond to the code 40
gt = ~ismissing(ML(:,3));
new_ML = ML(gt,:);

%% Fit a straight line to the timestamp data
f = fit(new_ML(:,2),new_ML(:,3),'poly1')

f = 
     Linear model Poly1:
     f(x) = p1*x + p2
     Coefficients (with 95% confidence bounds):
       p1 =        0.75  (0.75, 0.75)
       p2 =       -2195  (-2196, -2195)

plot(f,new_ML(:,2),new_ML(:,3))
xlabel('Timestamp 1')
ylabel('Timestamp 2')

You can see that the slope and y intercept values indicate a linear trend exists. Use this to convert your timestamps to the same scale.

new_ML(:,4) = new_ML(:,2)*f.p1+f.p2

new_ML = 20x4

1.0e+04 * 0.0040 0.5579 0.1989 0.1989 0.0040 0.7545 0.3464 0.3464 0.0040 0.9612 0.5014 0.5014 0.0040 1.1679 0.6564 0.6564 0.0040 1.3445 0.7889 0.7889 0.0040 1.7545 1.0964 1.0964 0.0040 1.9645 1.2539 1.2539 0.0040 2.1612 1.4014 1.4014 0.0040 2.3613 1.5514 1.5515 0.0040 2.5379 1.6839 1.6839

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Now taking the difference will give the similar step sizes. The difference is likely due to lost precision when capturing the timestamps here.

t_diff = diff(new_ML(:,[4 3])) 

t_diff = 19x2

1.0e+03 * 1.4745 1.4750 1.5503 1.5500 1.5503 1.5500 1.3245 1.3250 3.0750 3.0750 1.5750 1.5750 1.4753 1.4750 1.5008 1.5000 1.3245 1.3250 1.6748 1.6750

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

[Anna Ipata]

thank you!! I'll try it!

[Umar]

Hi Anna,

Glad to know that you will use Cris approached solution. Please give him a vote. If you have any further questions, please let us know, we will be more happy to help you out.