How can I compare a datetime array with a timetable to delete all not existing days in it?

I have a datetime array for some days of the year and a timetable with 24 hours a day (365 days, i.e. 8760 lines). I want to delete all lines in the timetable that are not in my datetime array. Which commands can I use? The timetable has values like temperature.

01/01/2000

01/02/2000

01/04/2000

01/06/2000

01/08/2000

01/20/2000

...

01/01/2000 00:00:00

01/01/2000 01:00:00

01/01/2000 02:00:00

01/01/2000 03:00:00

01/01/2000 04:00:00

01/01/2000 05:00:00

01/01/2000 06:00:00

01/01/2000 07:00:00

01/01/2000 08:00:00

01/01/2000 09:00:00

...

Answer 1

[Adam Danz]

To match dates while ignoring time, use ismember() along with dateshift() to ignore time.

Demo:

• TT is a timetable with datetime values stored in TT.Time.
• dateList is a vector of selected datetime values that indicate which days in TT should be accepted. All other rows of TT are removed.

% Create timetable
Time = datetime(2000,1,1,1,0,0)+days(0:30)'+hours(0:30)';
TT = timetable(Time, rand(size(Time)));
head(TT) 

ans = 8x1 timetable
            Time             Var1  
    ____________________    _______

    01-Jan-2000 01:00:00    0.91812
    02-Jan-2000 02:00:00    0.89075
    03-Jan-2000 03:00:00    0.26125
    04-Jan-2000 04:00:00    0.60428
    05-Jan-2000 05:00:00    0.66809
    06-Jan-2000 06:00:00    0.62913
    07-Jan-2000 07:00:00    0.18667
    08-Jan-2000 08:00:00    0.55318

% List of accepted dates
dateList = datetime(2000,1,1)+days([0 1 3 5 7 19])'

dateList = 6×1 datetime array
   01-Jan-2000
   02-Jan-2000
   04-Jan-2000
   06-Jan-2000
   08-Jan-2000
   20-Jan-2000

% Identify and remove rows of TT with dates outside of dateList, ignoring time.
rmIdx = ~ismember(dateshift(TT.Time,'Start','Day'), dateshift(dateList,'Start','Day'));
TT(rmIdx,:) = []

TT = 6x1 timetable
            Time             Var1  
    ____________________    _______

    01-Jan-2000 01:00:00    0.91812
    02-Jan-2000 02:00:00    0.89075
    04-Jan-2000 04:00:00    0.60428
    06-Jan-2000 06:00:00    0.62913
    08-Jan-2000 08:00:00    0.55318
    20-Jan-2000 20:00:00     0.6004

[Alexander Koch]

It works!!! But the timetable only includes the data from January. I need all the data until 31.12. :(

[Adam Danz]

The solution works for any timetable (TT) and any list of accepted dates (dateList).

Does your list of accepted dates include dates beyond January?

[Alexander Koch]

Yes, the dateList includes 276 days from 01.01. till 31. December.

[Adam Danz]

Then I don't understand the problem. Could you elaborate?

[Alexander Koch]

Sorry I confused myself. The dateList includes days from 1 January to 31 December with gaps. So no 365 days. However, the issue is now limited to January. I need the data until December.

My dateList concludes 276 days.

[Adam Danz]

The dateList in my answer is limited to January but you should replace that with your list of accepted days. Or is the problem something else?

[Alexander Koch]

My timetable isn't like:

01-Jan-2000 01:00:00 0.91812

02-Jan-2000 02:00:00 0.89075

03-Jan-2000 03:00:00 0.26125

04-Jan-2000 04:00:00 0.60428

05-Jan-2000 05:00:00 0.66809

06-Jan-2000 06:00:00 0.62913

07-Jan-2000 07:00:00 0.18667

08-Jan-2000 08:00:00 0.55318

For each day it concludes 24 hours of data, like:

And my dateList looks like this (beginning with 01. January):

The datetime list is 276x1

The result of your code is:

It stopped at the end of January.

[Adam Danz]

Please attach a mat file containing the table and the date list.

The last two lines of my answer are the only ones you need and should work with any range of dates.

[Alexander Koch]

dateList and timetabel. I can't find my fault.

[Adam Danz]

@Alexander Koch it stumped me too and the problem wasn't easy to find.

Let's take a look at the dates,

load('timetable.mat')
load('dateList.mat')
dateList = TTT;
TT = TimeTable_Jahr_Datetime_Tab;

dateList(1:50:end) % sample some dates
ans = 
  6×1 datetime array
   01/01/00
   02/20/00
   04/10/00
   07/11/00
   10/17/00
   12/06/00

And look at the date format

dateList.Format
ans =
    'mm/dd/yy'

Looks reasonable, right? Month/Day/Year, right? Nope! "mm" for datetime format means "minutes" (see datetime formats). The datetime value 12/06/00 means minute-12, day-6, year-2000.

Let's confirm that by requesting the month of the last value,

>>dateList(end)
ans = 
  datetime
   12/31/00
   
>> month(dateList(end))
ans =
     1  % January!!

The month is specified by capital MM, not lowercase mm which means minutes.

Now let's correct the format and look at the same sample of dates.

>> dateList.Format = 'MM/dd/yy';
>> dateList(1:50:end)
ans = 
  6×1 datetime array
   01/01/00
   01/20/00
   01/10/00
   01/11/00
   01/17/00
   01/06/00

All January! In fact, we can confirm that all of the dates are January,

all(month(dateList)==1)
ans =
  logical
   1

Can we use the minutes to identify the intended months?

Perhaps the datetime array was created by mistaking months and minutes. If this hypothesis is true, the distribution of minutes should not exceed 1:12.

figure()
histgram(minute(dateList))
xlabel('minutes in dateList')
ylabel('number of dates')

This supports my hypothesis that the datetime array was created by mistaking months for minutes.

Ok, but how should this be fixed

The best solution is to re-create the vector using the correct datetime format. But since I don't know how you created that vector, the second best solution is to use the minutes to define the month. Note that their displayed format is irrelevant but I'll keep the format the same as the original data.

dateListFixed = datetime(year(dateList), minute(dateList), day(dateList), 'Format', 'MM/dd/yy'); 

Now let's look at the same sample of dates and the months

>> dateListFixed(1:50:end)
ans = 
  6×1 datetime array
   01/01/00
   02/20/00
   04/10/00
   07/11/00
   10/17/00
   12/06/00
   
>> month(dateListFixed(1:50:end))
ans =
     1
     2
     4
     7
    10
    12

Now my answer will work

rmIdx = ~ismember(dateshift(TT.Datum,'Start','Day'), dateshift(dateList,'Start','Day'));
fprintf('%d rows removed (%.1f%%)\n', sum(rmIdx), mean(rmIdx)*100)
TT(rmIdx,:) = [];

% 8040 rows removed (91.5%)

[Alexander Koch]

@Adam Danz First of all, Thank You!! But...

If I would like to convert '31-Jan-2000' to '01/31/2000' Matlab uses mmm for Jan and mm for day 31. I can't understand. So if I'm trying to add one day to '01/31/2000' ('MM/dd/yyyy') Matlab calculates something like '01/01/2000' and with HH:mm:ss you can see that 'mm' means month and 'MM' minutes.... It's reversed.

S1 = '31-Jan-2000';
I = datenum(S1, 'dd-mmm-yyyy');
S2 = datestr(I, 'mm/dd/yyyy');

I'm sorry, but I don't get it if it's mm or MM for month and minutes and in which case :O

Edit:

documentation for datetime formats: The letter identifiers that datetime accepts are different from the identifiers used by the datestr, datenum, and datevec functions.

Now it works, if I'm using mm for Month in datenum... confusing.

[Adam Danz]

@Alexander Koch look at the documentation for datestr datevec and datenum. Unfortunately their format notation is not consistent with datetime. For those functions months use lowercase m. But your data in the mat file are using datetime which uses uppercase M for month. It's a very unfortunate inconsistency that tricks a lot of people. But the lesson is always to refer to the documentation when you're having these kinds of problems.