How to find common datetime and put each datasets next to each other?

6 views (last 30 days)
Hi, I am really stuck and struggling to find a solution for my problem. I have two datasets one positioning and one echosounder pings. Here is what the extracted positioning dataset looks like:
%Position Datenum, Lat (Degrees, Minutes), Lon (Degrees, Min), Orthrometric Height, Ellipsoidal Height
"07:09:20:08:06:27.798" 4957.09105400000 616.902179000000 2.27400000000000 54.2000000000000
"07:09:20:08:06:27.999" 4957.09102900000 616.902153000000 2.28000000000000 54.2000000000000
"07:09:20:08:06:28.032" NaN NaN NaN NaN
"07:09:20:08:06:28.197" 4957.09100700000 616.902128000000 2.28400000000000 54.2000000000000
"07:09:20:08:06:28.398" 4957.09098500000 616.902106000000 2.28000000000000 54.2000000000000
"07:09:20:08:06:28.433" NaN NaN NaN NaN
"07:09:20:08:06:28.478" 7 2020 NaN NaN
"07:09:20:08:06:28.598" 4957.09095500000 616.902089000000 2.27700000000000 54.2000000000000
"07:09:20:08:06:28.797" 4957.09092700000 616.902070000000 2.27300000000000 54.2000000000000
"07:09:20:08:06:28.999" 4957.09090200000 616.902045000000 2.27700000000000 54.2000000000000
"07:09:20:08:06:29.032" NaN NaN NaN NaN
"07:09:20:08:06:29.197" 4957.09088100000 616.902018000000 2.28400000000000 54.2000000000000
"07:09:20:08:06:29.400" 4957.09086000000 616.901996000000 2.28800000000000 54.2000000000000
"07:09:20:08:06:29.434" NaN NaN NaN NaN
"07:09:20:08:06:29.482" 7 2020 NaN NaN
"07:09:20:08:06:29.599" 4957.09083600000 616.901976000000 2.28900000000000 54.2000000000000
"07:09:20:08:06:29.799" 4957.09080900000 616.901949000000 2.29800000000000 54.2000000000000
"07:09:20:08:06:29.998" 4957.09077900000 616.901916000000 2.31300000000000 54.2000000000000
"07:09:20:08:06:30.033" NaN NaN NaN NaN
"07:09:20:08:06:30.197" 4957.09075400000 616.901888000000 2.31500000000000 54.2000000000000
"07:09:20:08:06:30.399" 4957.09073100000 616.901871000000 2.30400000000000 54.2000000000000
"07:09:20:08:06:30.433" NaN NaN NaN NaN
"07:09:20:08:06:30.478" 7 2020 NaN NaN
"07:09:20:08:06:30.601" 4957.09071200000 616.901848000000 2.30300000000000 54.2000000000000
"07:09:20:08:06:30.797" 4957.09068900000 616.901812000000 2.32000000000000 54.2000000000000
"07:09:20:08:06:30.997" 4957.09066500000 616.901771000000 2.33600000000000 54.2000000000000
"07:09:20:08:06:31.032" NaN NaN NaN NaN
"07:09:20:08:06:31.198" 4957.09064100000 616.901740000000 2.33500000000000 54.2000000000000
My other dataset is a datenum dataset which took timings of each ping and both datasets are in a double matrix array, I also have them in a table array. The positioning times are a lot more frequent (9 times a second) compared to pings (3 times a second). The only common variable between the two is the datenum, I have been trying to match up my positioning times with my ping times but with no luck. What i need to have is an output of positioning datenum, lat, long, and both heights, with correctly matched up ping times. I have tried using ismembertol which does work but I need to have both times matched up and next to each other with an accuracy of +/- 1 second whilst also ignoring the lines with NaN's in them as I don't want those matched up. I think this would require a loop but I am unsure how to write that so any help/suggestions would be very appreciated.
Here is the commands that I have been trying to get to work:
I've been trying to do this for the past few days when I could but with no luck so any help or suggestions would be very appreciated
[found, where] = ismembertol(time, ping_time, 'ByRows', true) %this one outputs matching rows as 0 and 1's but I am unsure how to save the matched up values in a new array which would then have 6 columns
time_join = join(latlondata, pingtable, 'keys', 'pingtime') % this one just didn't work I have also tried synchronize which didn't work at all
The final output I want is the matched up data pasted above with a column of datenum from the ping data. Both datasets are different lengths and many of the functions I tried want it to be the same so I am unsure how to go about this. The datenums need to be matched up as accurately as possible within a second. Thank you for reading and taking the time to help it's incredibly appreciated.
  14 Comments
Daryna Butash
Daryna Butash on 9 Aug 2021
Edited: Daryna Butash on 9 Aug 2021
Hi Peter, I am still learning all of the matlab processes and for some reason I thought datenums would be easier to macth up but I was very wrong so I took the advice and now it is all in timetables which is so much easier to handle so I completely agree, thank you! I am having a little issue though if theres any chance you could help with the matchups where the pings match with my Nan lines which I dont want and if I use rmmissing and get rid of them on the timetable they come back when I synchronise them again. I am unsure how to use tol as I tried it but it didnt quite work I still had the same outcome :// here's my code so far it works super well in matching the pings but every synchronised time and ping is on a line with NaN's so I am unsure how to fix it if you have a minute to have a look. Ill attach my TT1, TT2 and TT3 variables (T3 is the outcome of the commands and you can see that the each 'ping' is on a line with NaN positoning). Thank you for your helpful suggestions and taking the time to help!!
Ping = repmat('ping', length(ping_time), 1);
%ping_time = rmmissing(ping_time)
TT1 = timetable(datetime(ping_time, 'convertfrom', 'datenum'), Ping); %attach 'ping' to each time so every sonar ping is synced
%TT1 = rmmissing(TT1); % removing rows with times missing
% make latlon timetable with 4 data columns
%latlon = rmmissing(latlon)
Lat = latlon(:,2);
Lon = latlon(:,3);
OrthHeight = latlon(:,4);
EllipHeight = latlon(:,5);
TT2 = timetable(datetime(latlon(:,1), 'convertfrom', 'datenum'), ...
Lat, Lon, OrthHeight, EllipHeight);
%tt2_matched = tt2(withtol(tMatch_tt1, tol),:);
%TT2 = rmmissing(TT2);
% combine
TT3 = synchronize(TT1, TT2);
%TT4 = circshift(TT3 (:,1),1)
Peter Perkins
Peter Perkins on 2 Sep 2021
Darnya, I'm not sure what you are asking. I ran your code, including the two commented-out lines that rmmissing from TT1 and TT2. TT1 starts something like an hour earlier than TT2, so there are many rows that won't match TT2. And even over their common range of times, the timestamps are off by tenths or hundredths of seconds.
>> TT1.Time.Format = "dd-MMM-uuuu HH:mm:ss.SSS";
>> TT2.Time.Format = "dd-MMM-uuuu HH:mm:ss.SSS";
>> TT1(timerange("07-Sep-2020 08:06:26","07-Sep-2020 08:06:30"),:)
ans =
13×1 timetable
Time Ping
________________________ ____
07-Sep-2020 08:06:26.071 ping
07-Sep-2020 08:06:26.414 ping
07-Sep-2020 08:06:26.730 ping
07-Sep-2020 08:06:27.048 ping
07-Sep-2020 08:06:27.349 ping
07-Sep-2020 08:06:27.664 ping
07-Sep-2020 08:06:27.977 ping
07-Sep-2020 08:06:28.286 ping
07-Sep-2020 08:06:28.556 ping
07-Sep-2020 08:06:28.864 ping
07-Sep-2020 08:06:29.171 ping
07-Sep-2020 08:06:29.464 ping
07-Sep-2020 08:06:29.745 ping
>> TT2(timerange("07-Sep-2020 08:06:26","07-Sep-2020 08:06:30"),:)
ans =
12×4 timetable
Time Lat Lon OrthHeight EllipHeight
________________________ ___________ __________ __________ ___________
07-Sep-2020 08:06:27.797 4957.091054 616.902179 2.274 54.2
07-Sep-2020 08:06:27.998 4957.091029 616.902153 2.28 54.2
07-Sep-2020 08:06:28.196 4957.091007 616.902128 2.284 54.2
07-Sep-2020 08:06:28.398 4957.090985 616.902106 2.28 54.2
07-Sep-2020 08:06:28.597 4957.090955 616.902089 2.277 54.2
07-Sep-2020 08:06:28.797 4957.090927 616.90207 2.273 54.2
07-Sep-2020 08:06:28.999 4957.090902 616.902045 2.277 54.2
07-Sep-2020 08:06:29.196 4957.090881 616.902018 2.284 54.2
07-Sep-2020 08:06:29.400 4957.09086 616.901996 2.288 54.2
07-Sep-2020 08:06:29.599 4957.090836 616.901976 2.289 54.2
07-Sep-2020 08:06:29.799 4957.090809 616.901949 2.298 54.2
07-Sep-2020 08:06:29.998 4957.090779 616.901916 2.313 54.2
So you can't just call synchronize with no parameters and expect to get something useful. synchronize by default uses the union of the times, and fills non matches with missing. You will need to do something like this:
>> TT3 = synchronize(TT1, TT2, 'last', 'nearest');
>> head(TT3)
ans =
8×5 timetable
Time Ping Lat Lon OrthHeight EllipHeight
________________________ ____ ___________ __________ __________ ___________
07-Sep-2020 08:06:27.797 ping 4957.091054 616.902179 2.274 54.2
07-Sep-2020 08:06:27.998 ping 4957.091029 616.902153 2.28 54.2
07-Sep-2020 08:06:28.196 ping 4957.091007 616.902128 2.284 54.2
07-Sep-2020 08:06:28.398 ping 4957.090985 616.902106 2.28 54.2
07-Sep-2020 08:06:28.597 ping 4957.090955 616.902089 2.277 54.2
07-Sep-2020 08:06:28.797 ping 4957.090927 616.90207 2.273 54.2
07-Sep-2020 08:06:28.999 ping 4957.090902 616.902045 2.277 54.2
07-Sep-2020 08:06:29.196 ping 4957.090881 616.902018 2.284 54.2
But I have no idea specifically what you will need to do. Do you want the result to use the timestamps from TT1, or TT2, or both? Do you want to attach the data from rows with the nearest timestamps from TT2 to rows of TT1, or vice versa? Do you want to interpolate values from TT2 to match TT1's timestamps? You need to look at thedoc for synchronize and figure all that out.
Also, this char vector
Ping = repmat('ping', length(ping_time), 1);
is probably not a good idea. For one thing, it's the same text in every element. If you really want it, use "ping" instead. If there can be other things besides "pinfsz", you should consider using categorical.

Sign in to comment.

Answers (0)

Categories

Find more on Tables in Help Center and File Exchange

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!