Services & Resources / Wolfram Forums / MathGroup Archive
-----

MathGroup Archive 2012

[Date Index] [Thread Index] [Author Index]

Search the Archive

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




  • Prev by Date: Animating a 3D Plot
  • Next by Date: Re: correlation function
  • Previous by thread: Re: Removing rows from a table
  • Next by thread: Re: Removing rows from a table