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