[R] Removing rows with earlier dates

[David Winsemius]

On Dec 24, 2010, at 8:45 AM, Ali Salekfard wrote:

> Hi all,
>
> I'm new to the list but have benfited from it quite extensively.  
> Straight to
> my rather strange question:
>
> I have a data frame that contains mapping rules in this way:
>
> ACCOUNT, RULE COLUMNS, Effective Date
>
>
> The dataframe comes from a database that stores all dates. What I  
> would like
> to do is to create a data frame with only the most recent rule for  
> each
> account. In traditional programming languages I would loop through  
> each
> account find the most recent rule(s) and fill up my updated data  
> frame.
>
> Does anyone have any better idea to use R's magic (Its syntax is still
> magical to me) for this problem?

It's going to remain magic until you start thinking about what is  
needed. In this case the need is for a good understanding of the  
structure of the data object and the str function is the usual way to  
examine such AND to then communicate with the list. Read the Posting  
Guide again and the references it cites, please.

>

Here would have been my first attempt, assuming a dataframe named dfrm:
#make sure the most recent is on top
dfrm <- dfrm[ order(dfrm["Effective Date"], decreasing=TRUE), ]
# then pull the first record within ACCOUNT
tapply(dfrm, dfrm$ACCOUNT , FUN= "[", 1 , )


> By the way  the list of rules is quite extensive (144643 lines to be
> precise), and there are usually 1-3 most recent rules (rows) for each
> account.

That is a bit different than the initial problem statement in which  
you asked for the "only the most recent" within each account. How are  
we supposed to get 3 _most_ recent rules? I think you are expecting us  
to read your mind regarding how you are thinking about this problem  
and pull all the records with the maximum date within an account.

Perhaps this effort to create a logical vector would be in the right  
direction:

dfrm[ ave(dfrm["Effective Date"], dfrm[ , "ACCOUNT"], function(x) x ==  
max(x), ]

It should pull all records for which the Effective Date is equal to  
the maximum within ACCOUNT. It is going to depend on whether  
"Effective Date" of of a class that can be properly compared with  
max(). Both Date and character representations of dates in standard y- 
m-d form would qualify. Other date formats might not:
 > max("01-02-2011", "02-01-2010")
[1] "02-01-2010"




-- 
David Winsemius, MD
West Hartford, CT