Re: Removing rows from a table

*To*: mathgroup at smc.vnet.net*Subject*: [mg128708] Re: Removing rows from a table*From*: Bill Rowe <readnews at sbcglobal.net>*Date*: Mon, 19 Nov 2012 04:08:37 -0500 (EST)*Delivered-to*: l-mathgroup@mail-archive0.wolfram.com*Delivered-to*: l-mathgroup@wolfram.com*Delivered-to*: mathgroup-newout@smc.vnet.net*Delivered-to*: mathgroup-newsend@smc.vnet.net

On 11/18/12 at 5:14 PM, peter_bodon at hotmail.com (Citzen90210) wrote: >Total nubie here and struggling! >I have a dataset of approximately 100,000 records of data points >recorded at 15 minute intervals. Each row looks something like >this: >{"01/01/2010 06:15", 0.04375, 4.96188, 1.00885, 0, 0, 0} >I've worked out how to parse the date column so that seems to be >under control but now I have a list of date ranges that I need to >remove from the dataset. The date ranges to be removed look >something like this: >{{2010, 2, 1, 12, 0, 0.0}, {2010, 2, 15, 12, 0, 0.0}}, {{2010, 7, 1, >12, 0, 0.0}, {2010, 7, 15, 12, 0, 0.0}} >with the first column representing the start date of the block to be >removed and the second column representing the end date of the >block, multiple rows represent multiple blocks to be removed and the >total number of blocks is variable. >Ideally I'd like to be able to use Drop or Delete to remove the >offending blocks of data but I want to be able to somehow feed the >command with the date range table and have it remove the ranges all >in one go. Use DeleteCases. For example, here is some random data in a format similar to what you've indicated above: In[16]:= data = (Flatten[{AbsoluteTime[{2012, 11, 18, 12, #}], RandomInteger[10, 2]}] & /@ Range[0, 100, 15]) /. {a_Integer, b__} :> {DateList[a], b} Out[16]= {{{2012, 11, 18, 12, 0, 0.}, 8, 7}, {{2012, 11, 18, 12, 15, 0.}, 7, 3}, {{2012, 11, 18, 12, 30, 0.}, 4, 10}, {{2012, 11, 18, 12, 45, 0.}, 0, 6}, {{2012, 11, 18, 13, 0, 0.}, 0, 2}, {{2012, 11, 18, 13, 15, 0.}, 10, 3}, {{2012, 11, 18, 13, 30, 0.}, 7, 5}} now to delete the items between 12:05 and 13:05 In[17]:= start = {2012, 11, 18, 12, 5} // AbsoluteTime; end = {2012, 11, 18, 13, 5} // AbsoluteTime; DeleteCases[data, {_?(IntervalMemberQ[Interval@{start, end}, AbsoluteTime@#] &), __}] Out[19]= {{{2012, 11, 18, 12, 0, 0.}, 8, 7}, {{2012, 11, 18, 13, 15, 0.}, 10, 3}, {{2012, 11, 18, 13, 30, 0.}, 7, 5}} And for multiple blocks to delete this same approach can be extended using a helper function. That is I can build up a list of intervals to be deleted as follows: blockStarts = AbsoluteTime /@ {{2012, 11, 18, 12, 5}, {2012, 11, 18, 13, 5}}; blockEnds = AbsoluteTime /@ {{2012, 11, 18, 12, 25}, {2012, 11, 18, 13, 25}}; blocks = MapThread[Interval@{##} &, {blockStarts, blockEnds}]; then my helper function is: itemDeleteQ[item_, blocks_] := Or @@ (IntervalMemberQ[#, item] & /@ blocks) and finally deleting the items in the two intervals defined above In[24]:= DeleteCases[data, {_?(itemDeleteQ[AbsoluteTime@#, blocks] &), __}] Out[24]= {{{2012, 11, 18, 12, 0, 0.}, 8, 7}, {{2012, 11, 18, 12, 30, 0.}, 4, 10}, {{2012, 11, 18, 12, 45, 0.}, 0, 6}, {{2012, 11, 18, 13, 0, 0.}, 0, 2}, {{2012, 11, 18, 13, 30, 0.}, 7, 5}}