how to align two columns with different times

6 次查看（过去 30 天）
Anna Ipata
Anna Ipata 2024-7-16
评论： Umar 2024-7-17
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
  2 个评论
Umar
Umar 2024-7-16
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
Cris LaPierre 2024-7-17
Ran in:
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>

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Cris LaPierre
Cris LaPierre 2024-7-17
Ran in:
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>
  2 个评论
Umar
Umar 2024-7-17
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.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 MATLAB 的更多信息

标签

产品


版本

R2024a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by