Closest date to another
29 次查看(过去 30 天)
显示 更早的评论
Curious Mind
2020-2-6
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!
采纳的回答
Adam Danz
2020-2-6
编辑:Adam Danz
2020-2-6
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).']
24 个评论
Curious Mind
2020-2-7
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
2020-2-7
编辑:Adam Danz
2020-2-7
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
2020-2-7
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
2020-2-7
Can you post a small sample .xls or .xlsx together with an indication of which fields you need to extract?
Adam Danz
2020-2-7
I wonder how you're importing the excel data. If you use readtable(), dates are automatically read in as datetime values.
Walter Roberson
2020-2-7
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
2020-2-7
编辑:Adam Danz
2020-2-7
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
2020-2-7
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
2020-2-7
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!
Curious Mind
2020-2-7
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
2020-2-7
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
2020-2-7
编辑:Adam Danz
2020-2-7
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
2020-2-8
编辑:Curious Mind
2020-2-8
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
2020-2-8
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
2020-2-8
Yes, that is correct. You can use one of these syntaxes for table t:
t.VariableName
t{:,columnnumber}
t{:, 'VariableName'}
Curious Mind
2020-2-10
编辑:Curious Mind
2020-2-10
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
2020-2-10
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
2020-2-13
编辑:Curious Mind
2020-2-14
@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
2020-2-14
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Time Series Objects 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!发生错误
由于页面发生更改,无法完成操作。请重新加载页面以查看其更新后的状态。
您也可以从以下列表中选择网站:
如何获得最佳网站性能
选择中国网站(中文或英文)以获得最佳网站性能。其他 MathWorks 国家/地区网站并未针对您所在位置的访问进行优化。
美洲
- América Latina (Español)
- Canada (English)
- United States (English)
欧洲
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom(English)
亚太
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)