# Filter list based on date

What I would like to do is remove entries from a list of instrument data when it is in maintenance. The maintenance data I have is a series of dates which look like this;

{{{2009, 6, 29, 10, 41, 0.}, {2009, 6, 30, 15, 26, 0.}}, {{2009, 6,
30, 16, 52, 0.}, {2009, 7, 1, 6, 0, 0.}}, {{2009, 7, 1, 6, 0,
0.}, {2009, 7, 1, 6, 2, 0.}}}


So between 29/6/2009 10:41 and 30/6/2008 15:26 the instrument was in maintenance.

The instrument data looks like this;

{{{2010, 1, 1, 6, 15, 0.}, 0.04375}, {{2010, 1, 1, 6, 30, 0.},
0.04375}, {{2010, 1, 1, 6, 45, 0.},
0.04375}, {{2010, 1, 1, 7, 0, 0.},
0.04375}, {{2010, 1, 1, 7, 15, 0.}, 0.04375}}


With the first column being the date/time and the second the value.

What I would like is a quick (the instrument data is 100,000 records) way to remove rows from the instrument data when their date falls inside a maintenance period.

-

Starting with:

gaps = {{{2009, 6, 29, 10, 41, 0.}, {2009, 6, 30, 15, 26,
0.}}, {{2009, 6, 30, 16, 52, 0.}, {2009, 7, 1, 6, 0,
0.}}, {{2009, 7, 1, 6, 0, 0.}, {2009, 7, 1, 6, 2, 0.}}};

data = {{{2010, 1, 1, 6, 15, 0.}, 0.04375}, {{2010, 1, 1, 6, 30, 0.},
0.04375}, {{2010, 1, 1, 6, 45, 0.},
0.04375}, {{2010, 1, 1, 7, 0, 0.},
0.04375}, {{2010, 1, 1, 7, 15, 0.},
0.04375}, {{2009, 6, 30, 7, 26, 0.}, 0.1}};


I recommend:

maint = Interval @@ Map[AbsoluteTime, gaps, {2}];

Cases[data, {date_, _} /; ! IntervalMemberQ[maint, AbsoluteTime@date]]


Or if you prefer a form with Select like wxffles shows you could write:

makeTest[gaps_] :=
With[{maint = Interval @@ Map[AbsoluteTime, gaps, {2}]},
! IntervalMemberQ[maint, AbsoluteTime @ #[[1]]] &
]

Select[data, makeTest @ gaps]


This should be more convenient to use for multiple "gaps" lists.

This is what I believe Murta wanted to write:

removeRanges[data_, gaps_] :=
Module[{absData, absGaps},
absData = AbsoluteTime /@ data[[All, 1]];
absGaps = Interval @@ Map[AbsoluteTime, gaps, {2}];
Pick[data, ! IntervalMemberQ[absGaps, #] & /@ absData]
]

-
The first method I got 17 seconds for the same test I did for the other answer (5,000 x 3) which is faster but still not fast enough. The second method I got 0.39 second for my entire data set which is great. Just let me check that it's filtering properly and I'll give you the tick. – Cam Nov 13 '12 at 0:28
@Cam don't be in a rush to Accept an answer; I recommend waiting 24 hours to give everyone (around the world) a chance to answer. – Mr.Wizard Nov 13 '12 at 0:29
@Cam the first method is really poor programming as AbsoluteTime is reevaluated for each interval in every test. I cannot see a problem with the second method so I am going to edit my answer to remove the sloppy one. – Mr.Wizard Nov 13 '12 at 0:44
Hey @Mr.Wizard, you were faster then me! :p – Murta Nov 13 '12 at 1:31

There is my way:

removeRanges[data_,gaps_]:=Module[{absData,absGaps},
absData=AbsoluteTime/@data[[All,1]];
absGaps=Interval@@Map[AbsoluteTime,gaps,{2}];
Pick[data,Not@IntervalMemberQ[absGaps,#]&/@absData]
]


A little bit clean.

-
When I ran this one I got 0.531 seconds for 100,000 rows for data and 3 rows for gaps. The problem seems to be as gaps increases so does the execution time. When gaps gets to 100 rows the execution time is 4.7 seconds. My gap file is 2,000 rows so this method is going to be a bit slow. – Cam Nov 13 '12 at 1:10
Nice attempt at packaging my method but you've introduced some problems causing a major slowdown; this is clean but not fast. Interval@@absGaps does not take an infinitesimal amount of time so it should not be done for every element of absData. – Mr.Wizard Nov 13 '12 at 1:11
+1 This is now very nice. :-) – Mr.Wizard Nov 13 '12 at 1:49
I get 0.375 seconds for my entire data set now. Thanks. – Cam Nov 13 '12 at 2:30
Glad it works! You're welcome. – Murta Nov 13 '12 at 10:39

You could make a function to decide whether a given date is in maintenance:

Clear[inMaintenance];
inMaintenance[d_, maintenance_] :=
Or @@ (0 >= DateDifference[#[[1]], d, "Minute"][[1]]
DateDifference[#[[2]], d, "Minute"][[1]] & /@ maintenance)


This takes the DateDifference (in minutes) between your given date and each end of an interval. If their product is negative (or zero), then the date must be within the interval. If it's in within any of the given intervals (Or @@) then in must be in maintenance.

Then you can use this function to select from your instrument data:

Select[data, ! inMaintenance[#[[1]], maintenance] &]


As for making this fast, you could incorporate the answers from this question.

-
+1 Using java libraries is the only way I've seen to get decent speed for handling lots of date data in Mathematica – ssch Nov 13 '12 at 0:04
When I ran that code I got 33 second execution time for 5,000 rows in by data file and only 3 rows in my maintenance file. My actual data set is 100,000 rows for data and 2,000 rows for maintenance. I don't think this approach is going to work. – Cam Nov 13 '12 at 0:15
@Cam what timings do you get with my methods? They appear serviceably fast to me. – Mr.Wizard Nov 13 '12 at 0:17
I was thinking of writing same Java code to handle date parsing and some other things. Is this a good idea? I've got a lot more experience writing Java code so it would be a fairly simple thing for me to do but I would like to use Mathematica on its own if I could. – Cam Nov 13 '12 at 0:31

Knowing full well this is an old question, it seems to be a good candidate for a general function that I have made for myself. See the filterData function defined in this answer.

You can then setup your data:

data = {{{2010, 1, 1, 6, 15, 0.}, 0.04375}, {{2010, 1, 1, 6, 30, 0.},
0.04375}, {{2010, 1, 1, 6, 45, 0.},
0.04375}, {{2010, 1, 1, 7, 0, 0.},
0.04375}, {{2010, 1, 1, 7, 15, 0.}, 0.04375}}

data = {AbsoluteTime /@ data[[All, 1]], data[[All, 2]]}\[Transpose]

gaps = {{{2009, 6, 29, 10, 41, 0.}, {2009, 6, 30, 15, 26,
0.}}, {{2009, 6, 30, 16, 52, 0.}, {2009, 7, 1, 6, 0,
0.}}, {{2009, 7, 1, 6, 0, 0.}, {2009, 7, 1, 6, 2, 0.}}};


And finally use the function specified. This should work... the gaps are off relative to the data so I can't test easily.

Fold[filterData[#1, 1 >= AbsoluteTime[#2[[1]]],
1 <= AbsoluteTime[#2[[2]]],
BooleanFunction -> (Not[And[#]] &)] &, data, gaps]

-