7
$\begingroup$

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.

Improve this question
$\endgroup$

5 Answers 5

Reset to default
5
$\begingroup$

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]
 ]
Improve this answer
$\endgroup$
4
  • $\begingroup$ 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. $\endgroup$
    – Cam
    Nov 13, 2012 at 0:28
  • $\begingroup$ @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. $\endgroup$
    – Mr.Wizard
    Nov 13, 2012 at 0:29
  • $\begingroup$ @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. $\endgroup$
    – Mr.Wizard
    Nov 13, 2012 at 0:44
  • $\begingroup$ Hey @Mr.Wizard, you were faster then me! :p $\endgroup$
    – Murta
    Nov 13, 2012 at 1:31
5
$\begingroup$

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.

Improve this answer
$\endgroup$
4
  • $\begingroup$ 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. $\endgroup$
    – Cam
    Nov 13, 2012 at 1:10
  • $\begingroup$ 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. $\endgroup$
    – Mr.Wizard
    Nov 13, 2012 at 1:11
  • $\begingroup$ I get 0.375 seconds for my entire data set now. Thanks. $\endgroup$
    – Cam
    Nov 13, 2012 at 2:30
  • $\begingroup$ Glad it works! You're welcome. $\endgroup$
    – Murta
    Nov 13, 2012 at 10:39
2
$\begingroup$

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.

Improve this answer
$\endgroup$
4
  • $\begingroup$ +1 Using java libraries is the only way I've seen to get decent speed for handling lots of date data in Mathematica $\endgroup$
    – ssch
    Nov 13, 2012 at 0:04
  • $\begingroup$ 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. $\endgroup$
    – Cam
    Nov 13, 2012 at 0:15
  • $\begingroup$ @Cam what timings do you get with my methods? They appear serviceably fast to me. $\endgroup$
    – Mr.Wizard
    Nov 13, 2012 at 0:17
  • $\begingroup$ 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. $\endgroup$
    – Cam
    Nov 13, 2012 at 0:31
2
$\begingroup$

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]
Improve this answer
$\endgroup$
1
$\begingroup$

enter image description here

Borrowing data from Mr. Wizard's answer:

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}}

mints = {{{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.}}};

di = {DateObject@First@#, Last@#} & /@ data

enter image description here

mdates = DateInterval /@ (Map[
     DateObject] /@ {{{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.}}});

Pick[di, NoneTrue[#, # == True &] & /@ ( 
   DateWithinQ[mdates, First@#] & /@ di)]

enter image description here

To visualize:

TimelinePlot[{mdates, DateObject@First@# & /@ data}, 
 PlotRange -> 
  MinMax[mdates] + {Quantity[-4, "Hours"], Quantity[4, "Hours"]}
 ]

enter image description here

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.