Delete rows based on specific periods

Question

I have data (.xlsx format): two columns - data in first column imported as DateObject (dates with specific time), data in second column are numbers (113388.28, 865165.98 etc.).

I want to delete the "rows" which falls into specific intervals, e.g.:

-each Monday from 10PM to 11PM & each Friday from 8AM to 10AM.

I found this: Filter list based on date, but that is not exactly the same as my problem since there was only one specific period and I need that for several periods (each week the same one).

Thanks in advance!!!

Answer 1

ClearAll[pickDaysHours, selectDaysHours ]
pickDaysHours = Module[{dt = #, 
      selF = Function[{x}, DayName[x] == # && #2 <= x[[4]] <= #3] & @@@ {##2}}, 
    Pick[dt, Or @@ Through@selF@# & /@ dt[[All, 1]]]] &;

Or, using Select:

selectDaysHours = Module[{dt = #, 
     selF = Function[{x}, DayName[x] == # && #2 <= x[[4]] <= #3] & @@@ {##2}}, 
    Select[dt, Or @@ Through@selF@#[[1]] &]] &;

Examples:

Generating example data using the approach in @Edmund's answer:

datesample = Sort[RandomInteger[{AbsoluteTime[{2015, 03, 22}], 
    AbsoluteTime[{2015, 03, 28}]}, 500]];
data = Transpose@{DateList /@ datesample, RandomReal[{20 10^3, 90 10^3}, 500]};

(selected = selectDaysHours[data, {Friday, 8, 10}, {Monday, 22, 23}]) == 
     pickDaysHours[data, {Friday, 8, 10}, {Monday, 22, 23}]
(* True *)

Grid[selected, Alignment->Left]

For plotting, split the data by DayName to color different days differently:

splitdata = SplitBy[data, DayName[#[[1]]] &]; 


Legended[DateListPlot[splitdata, Joined -> True, AspectRatio -> 1/3, 
  ImageSize -> 700,
  Epilog -> {Red, PointSize[Large], Point /@ selected},
  PlotLegends -> LineLegend[DeleteDuplicates[DayName /@ data[[All, 1]]]]], 
 Placed[PointLegend[{Red}, {"Selected hours"}, BaseStyle -> PointSize[Large]], After]]

Answer 2

I have a general "filter data" function that I've cooked up to use for these sorts of questions.

filterData::usage="filterData[data_,filters__] filters the data based on the filters defined. Filters are in the form `ColumnNumber Comparison Value` such as `1 <= 0.1`."

Clear[filterData];

Options[filterData] = {
    BooleanFunction -> And
};

filterData[d_?MatrixQ,filters__,options:OptionsPattern[]] := Module[{dd=d,c,f,v,l,r,s=0,searchFunction,fil=Hold[{filters}]},
    l = Length[fil[[1]]];
    c = fil[[1,All,1]];
    f = fil[[1,All,0]];
    v = fil[[1,All,2]];
    r = Position[f,Rule];
    If[r=!={},
        s = Extract[v,r];
        c=Delete[c,r];
        f=Delete[f,r];
        v=Delete[v,r];
        l=l-1;
    ];
    searchFunction = Function[x,OptionValue[BooleanFunction]@@(f[[#]][x[[ c[[#]] ]],v[[#]]]&/@Range[l])];
    dd=Select[dd,searchFunction];
    If[s=!=0,
        sortByColumn[dd,PadLeft[{Sign[s] 1},Abs[s]]]
        ,dd
    ]
];

SetAttributes[filterData,HoldRest]

I then use @Edmund way creating a date sample:

datesample = 
  RandomInteger[{AbsoluteTime[{2015, 03, 22}], 
    AbsoluteTime[{2015, 03, 28}]}, 1000];
data = Transpose@{datesample, RandomReal[{20 10^3, 90 10^3}, 1000]};

And then filter the data:

dataFiltered = 
  filterData[data, 1 < AbsoluteTime[{2015, 03, 24}], 
   1 > AbsoluteTime[{2015, 03, 25}], BooleanFunction->Or];

You can then combine these in some fashion using Fold:

dates = Table[
  DatePlus[#, 7 i] & /@ {DateObject[{2015, 03, 23, 20, 00}], 
    DateObject[{2015, 03, 23, 23, 00}]}
  , {i, 0, 10}]

dataFiltered = 
  Fold[filterData[#1, 1 < AbsoluteTime[#2[[1]]], 
     1 > AbsoluteTime[#2[[2]]], BooleanFunction -> Or] &, data, 
   dates];

Which will get you all the data without the pesky 8 to 11 time frame for Monday's for the next few months. Adjust your Dates accordingly.

Answer 3

Questions will get answered quicker if you provide some code and a dataset to go with it.

I've worked out how to do this for a particular week. You can generalize this for any date range by generating the weekday dates and times that you want to exclude.

(*make a dataset for the example*)
datesample = RandomInteger[{AbsoluteTime[{2015, 03, 22}], 
 AbsoluteTime[{2015, 03, 28}]}, 1000];
data = Transpose@{DateList /@ datesample , RandomReal[{20 10^3, 90 10^3}, 1000]};

(*Create the interval over which items should be deleted. Make a function
  that takes the start and end dates and returns the union of the intervals.*)
delInterval = Interval[{AbsoluteTime[{2015, 03, 23, 22, 0}], AbsoluteTime[{2015, 03, 23, 22, 59}]},
 {AbsoluteTime[{2015, 03, 27, 8, 0}], AbsoluteTime[{2015, 03, 27, 9, 59}]}];

(*Delete items inside of the interval.*)
DeleteCases[x_ /; IntervalMemberQ[ delInterval, AbsoluteTime[x[[1]]]]]@data

Items with date times that fall in the two specified will match and be deleted.

Update with Interval creation function

You can generate a interval exclusion Interval by using function that takes start and end dates along with the days and time periods.

startDate = {2014, 1, 8}; endDate = {2015, 3, 23};
timeSpans = {{Monday, {{22, 0}, {22, 59,59}}}, {Friday, {{8, 0}, {9, 59, 59}}}};

This function takes information, finds the first day of the week for each timeSpans set, and generates all the weekday time intervals between the start and end date.

dateTimeInterval[start_, end_, day_, timeSpan_] :=
 Module[
  {first = Piecewise[{{start, DayName[start] == day}, {DayPlus[start, 1, day], True}}]},
  datetimes = (
   Function[{time}, 
    DateObject[DatePlus[first, {#, "Week"}], TimeObject[time]]] /@ timeSpan) & /@ 
     Range[QuantityMagnitude@DateDifference[first, end, "Week"]];
  Interval[Sequence @@ Map[AbsoluteTime, datetimes, {2}]]
 ]

So now, starting from the top, we can generate a much larger test dataset and apply the new method to it.

(*Make a dataset for the example*)
datesample = RandomInteger[{AbsoluteTime[startDate], AbsoluteTime[endDate]}, 10000];
data = Transpose@{DateList /@ datesample , RandomReal[{20 10^3, 90 10^3}, 10000]};

(*Create the interval over which items should be deleted.*)
delInterval = 
 IntervalUnion[Sequence @@ (dateTimeInterval[startDate, endDate, Sequence @@ #] & /@ timeSpans)];

(*Delete items inside of the interval.*)
cleanedData = DeleteCases[x_ /; IntervalMemberQ[delInterval, AbsoluteTime[x[[1]]]]]@data

You can also grab the location of the items that will be deleted. Which is useful if you want to do something with them.

Position[data, item_ /; IntervalMemberQ[delInterval, AbsoluteTime[item[[1]]]], {1}]

This does give an odd error but it completes ok. Anyone have an idea why this Position errors please make a note in the comments.

Hope this helps. - Don't forget to accept an answer by clicking the checkmark next to it in the left margin.