Closest date to another

I have the code below which compares and extract the closest date & time in data1 to data2. It works well but I don’t want duplicates. That is if a closest date in data1 is already assigned to another date in data2, that closest date cannot be assigned again. Instead the nearest closest date should be chosen. No closest data in data1 should be used twice.

[~,ind1] = min(abs(datenum(data2)-datenum(data1))); closest_time = data2(ind1,:)

Any thoughts?

Thanks!

Answer 1

[Adam Danz]

A simple, readable, and fast method would be to loop through the dates in date1, find the closest date in date2, and then replace that date in date2 with NaT so it won't be chosen again. That's what this demo does.

Inputs: two row vectors of dates in datetime format: dates1 & dates2

Outputs: nearestIdx(j) is the closest non-repeated date in date2 to date1(j); m is a nx2 matrix of paired dates.

See inline comments for details.

% Create two arrays of random dates (may contain repeated dates)
dates1 = datetime(2019,1,1)+days(sort(randi(364,1,100)));
dates2 = datetime(2018,12,28)+days(sort(randi(364,1,100)));

% Loop through the dates in date1
nearestIdx = nan(size(dates1));    % Pre allocation
dates2Temp = dates2;               % Make a temp copy of dates2 for NaT replacement
for i = 1:numel(dates1)
    [~, nearestIdx(i)] = min(abs(dates2Temp - dates1(i)));  % find index of closest date
    dates2Temp(nearestIdx(i)) = NaT;                        % replace that date with NaT
end

% Sanity check: all of the values in nearestIdx should be unique
assert(numel(unique(nearestIdx))==numel(dates1),'Sanity check failure: unique date matching error.')

% Match the dates (dates1 and dates2 must have same size)
m = [dates1.', dates2(nearestIdx).']

[Curious Mind]

This is awesome!! Adam. After extracting the closest dates & time (I believe that’s ‘m’) in data2 closest to that of data1, I would like to compare this extracted dates & time to another table called Table1 containing dates & time information in the first column and other values or information in the other columns and if a match is found, extract the entire columns and rows associated with that match in Table1 and output it into C. Once again thank you!

[Adam Danz]

m is a nx2 matrix where column 1 are the date1 values and columns 2 are the date2 matches.

If you just want the date2 matches, dates2(nearestIdx)

When you say that you want to match the datetime values, are you matching by day or do your datetime values have hours, minutes, seconds, too?

In this demo below, the two inputs are two datetime vectors of different lengths (dates & tableDates). The output is rowIdx which identifies the elements of tableDates that have a match in dates. Use that logical index to extract those rows from the table. The matching is only done by day so if your datetime values contain hours, minutes, etc, they will me matched as long as they come from the same day.

% Create two vectors of datetimes
dates = datetime(2019,1,1)+days(unique(randi(364,1,100)));
tableDates = datetime(2018,12,28)+days(linspace(1,364,100));

% Get rid of hours, minutes, seconds if they exist and 
% form a grid for subtraction. 
[dt1Mat, dt2Mat] = ndgrid(dateshift(dates, 'start', 'day'), dateshift(tableDates, 'start', 'day')); 

% Subtract the datetimes, find the indices where the difference is 0
rowIdx = any((dt1Mat - dt2Mat) == 0,1);

% Extract the rows of the table.
T(rowIdx,:)

[Curious Mind]

Awesome Adam. Thanks!

[Curious Mind]

Hi @Adam Danz, I am copying my datetime from excel and it comes with ‘’ symbol. So when I run the code it gives an error message

“Undefined operator ‘_’ for the input arguments of type ‘cell’.

Any idea? Thanks!

[Walter Roberson]

Can you post a small sample .xls or .xlsx together with an indication of which fields you need to extract?

[Adam Danz]

I wonder how you're importing the excel data. If you use readtable(), dates are automatically read in as datetime values.

[Walter Roberson]

If the dates happen to be surrounded with " characters then they would be detected as character vectors by readtable() unless you used special options.

[Adam Danz]

Ah, I thought the single quotes were showing up after the dates were imported. In that case, the dates can be imported as character vectors and converted by

datetime(dateChars,'InputFormat','MM/dd/yyyy') % adapt input format as needed

But if that doesn't work, Francis, you'll have to provide some samples as Walter requested.

[Walter Roberson]

If the data includes the " characters and readtable() is being used, then possibly https://www.mathworks.com/help/matlab/ref/detectimportoptions.html set Prefixes and Suffixes might work.

[Curious Mind]

I will upload an excel spreadsheet soon. I truly appreciate it

[Curious Mind]

Hi, I have attached an excel spreadsheet containing dates1 and dates2. In practice, dates1 and dates2 will be in separate files. I am trying to find a way to import dates1 and dates2 which are contained in separate exel files into matlab and use the code to do the analysis.

Thank you!

[Adam Danz]

The time stamps in that file are not surrounded by quotation marks as you indicated earlier. Have you tried importing them using readtable as I suggested here or are the data not representative of your real data?

[Curious Mind]

When I import them using readtable it modifies the first date as the header. At first I was just copying the date times into {} that’s why they were surrounded by quotation marks. That’s the data I have.

[Walter Roberson]

t = readtable('DateTime.xlsx','headerlines',1,'readvariablenames',false)

if you have to, but newer versions of MATLAB you can just use

t = readtable('DateTime.xlsx')

and it will get the variable names right and it will not use any date as a variable name.

[Adam Danz]

The headerlines argument is only valid for text files and will throw an error for excel files, "Invalid parameter name: headerlines".

Nevermind the rest of my comment that I removed; I realized the datetime values in the file were randomized (not a good choice when sharing sample data)....

[Curious Mind]

Sorry for bothering you but this is probably the last thing I will ask. I successfully imported it. when I run it gives this error;

"Subscripting into a table using one subscript (as in t(i)) or three or more subscripts (as in t(i, j, k)) is not supported. Always specify a row subscript and a variable subscript, as in t(rows, vars)."

[Adam Danz]

No problem at all.

The recommended way to extract a value from a table is to specify the column by its variable name and to specify the row with an index value.

% T is a table
% Date is the name of one of the columns. 
% n is row number
T.Date(n)

If you need to use indexing for the column number, too, use curly brackets.

T{n,m}

[Walter Roberson]

Yes, that is correct. You can use one of these syntaxes for table t:

t.VariableName
t{:,columnnumber}
t{:, 'VariableName'}

[Curious Mind]

It works now. Thank you very much.

[Curious Mind]

Hello,

I am back again. I am able to extract the closest date using the code @Adam Danz provided. I now wants to compare the first column of this table (A) which contains the closest datetimes to another table (B) (27by500). The first column of this table B (27by500) contains datetimes as well. I want to compare the first columns of A to B such that whenever a match is found, the whole row in B for which there is a match is extracted into a new table called Match. Both tables contain headers. Any ideas? Thanks guys!

[Adam Danz]

1) Use the algorithm already provided to match the dates between the two tables.

2) use the rowIdx to identify which rows of the table should be extracted. T(rowIdx,:)

What you're describing sounds the same as what we've already solved. Is the problem that you're working with tables instead of vectors and matrices?

[Curious Mind]

Perfect. Makes sense. Thanks!

[Curious Mind]

@Adam Danz. So everything looks great! Compared the dates and extracted the closest datetime and it’s corresponding data! As you know, for the current code, if I have 2/1/2016 11:42, 2/1/2016 11:43, and 2/1/2016 11:44. The closest value datetime to 2/1/2016 11:43 is 2/1/2016 11:42. Now what if I want to select the second highest datetime to 2/1/2016 11:43? In this case it will be 2/1/2016 11:44. I want to insert a function in the loop to do this. Compare dates1 to dates2 and select the indexes of all second nearest/highest datetime in dates2 to dates1. As usual, no datetime in dates2 can be selected twice. I appreciate it. I’m still learning.

[Adam Danz]

Answered here

https://www.mathworks.com/matlabcentral/answers/505515-find-second-closest-datetime