Delete rows based on specific periods

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).

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

Or, using Select:

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

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[#[]] &];

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]] 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[]];
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,
,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 > AbsoluteTime[#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.

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[]]]]@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[]]]]@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}]

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.

• @rabbit3698741 See update with interval creation function. – Edmund Mar 24 '15 at 13:28