3
$\begingroup$

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

$\endgroup$
2
$\begingroup$
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]

enter image description here

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

enter image description here

$\endgroup$
0
$\begingroup$

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.

$\endgroup$
0
$\begingroup$

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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