Hi,
dat1<- read.table(text="
Restaurant owner purchase_date
23 Chuck 3/4/2011
23 Chuck 3/4/2011
23 Chuck 3/4/2011
23 Chuck 3/4/2011
23 Bob 1/1/2013
23 Bob 1/1/2013
23 Bob 1/1/2013
15 Hazel 4/11/2010
15 Hazel 4/11/2010
15 Hazel 4/11/2010
15 Hazel 4/11/2010
17 Pete 9/2/2012
17 Pete 9/2/2012
17 Pete 9/2/2012
17 Pete 9/2/2012
",sep="",header=TRUE,stringsAsFactors=FALSE)
#if the dates are in order for the Restaurant group,
dat1$Current_Owner<-with(dat1,ave(owner,Restaurant,FUN=function(x) tail(x,1)))
dat1
# Restaurant owner purchase_date Current_Owner
#1 23 Chuck 3/4/2011 Bob
#2 23 Chuck 3/4/2011 Bob
#3 23 Chuck 3/4/2011 Bob
#4 23 Chuck 3/4/2011 Bob
#5 23 Bob 1/1/2013 Bob
#6 23 Bob 1/1/2013 Bob
#7 23 Bob 1/1/2013 Bob
#8 15 Hazel 4/11/2010 Hazel
#9 15 Hazel 4/11/2010 Hazel
#10 15 Hazel 4/11/2010 Hazel
#11 15 Hazel 4/11/2010 Hazel
#12 17 Pete 9/2/2012 Pete
#13 17 Pete 9/2/2012 Pete
#14 17 Pete 9/2/2012 Pete
#15 17 Pete 9/2/2012 Pete
If the order is different:
dat2<- read.table(text="
Restaurant owner purchase_date
23 Bob 1/1/2013
23 Bob 1/1/2013
23 Bob 1/1/2013
23 Chuck 3/4/2011
23 Chuck 3/4/2011
23 Chuck 3/4/2011
23 Chuck 3/4/2011
15 Hazel 4/11/2010
15 Hazel 4/11/2010
15 Hazel 4/11/2010
15 Hazel 4/11/2010
17 Pete 9/2/2012
17 Pete 9/2/2012
17 Pete 9/2/2012
17 Pete 9/2/2012
",sep="",header=TRUE,stringsAsFactors=FALSE)
dat2New<-unsplit(lapply(split(dat2,dat2$Restaurant), FUN= function(x)
{x1<-x[order(as.Date(x$purchase_date,format="%m/%d/%Y")),]; x1$Current_Owner<-
tail(x1$owner,1); x1}),dat2$Restaurant)
rownames(dat2New)<- 1:nrow(dat2New)
dat2New
# Restaurant owner purchase_date Current_Owner
#1 23 Chuck 3/4/2011 Bob
#2 23 Chuck 3/4/2011 Bob
#3 23 Chuck 3/4/2011 Bob
#4 23 Chuck 3/4/2011 Bob
#5 23 Bob 1/1/2013 Bob
#6 23 Bob 1/1/2013 Bob
#7 23 Bob 1/1/2013 Bob
#8 15 Hazel 4/11/2010 Hazel
#9 15 Hazel 4/11/2010 Hazel
#10 15 Hazel 4/11/2010 Hazel
#11 15 Hazel 4/11/2010 Hazel
#12 17 Pete 9/2/2012 Pete
#13 17 Pete 9/2/2012 Pete
#14 17 Pete 9/2/2012 Pete
#15 17 Pete 9/2/2012 Pete
A.K.
>I have the following data frame including restaurants (id#) ,owner and
purchase date. Sometimes the restaurant changes ownership, but for
>analysis purposes I want to create another column which only keeps the
name of the latest owner, this is determined by the "purchase date". How
>can I create this new column? In this example restaurant 23 changes
hand on 1/1/2013, so I want to include only the new owner's name (Bob)
as >the current owner for all rows for this restaurant as shown below. If
the restaurant does not change hands, keep the same name for the
"current >owner" column as the "owner" column.
>
>Thanks,
>Johnny
>
>
>
>Restaurant owner purchase date
> 23 Chuck 3/4/2011
> 23 Chuck 3/4/2011
> 23 Chuck 3/4/2011
> 23 Chuck 3/4/2011
> 23 Bob 1/1/2013
> 23 Bob 1/1/2013
> 23 Bob 1/1/2013
> 15 Hazel 4/11/2010
> 15 Hazel 4/11/2010
> 15 Hazel 4/11/2010
> 15 Hazel 4/11/2010
> 17 Pete 9/2/2012
> 17 Pete 9/2/2012
> 17 Pete 9/2/2012
> 17 Pete 9/2/2012
>
>Restaurant owner purchase date Current owner
> 23 Chuck 3/4/2011 Bob
> 23 Chuck 3/4/2011 Bob
> 23 Chuck 3/4/2011 Bob
> 23 Chuck 3/4/2011 Bob
> 23 Bob 1/1/2013 Bob
> 23 Bob 1/1/2013 Bob
> 23 Bob 1/1/2013 Bob
> 15 Hazel 4/11/2010 Hazel
> 15 Hazel 4/11/2010 Hazel
> 15 Hazel 4/11/2010 Hazel
> 15 Hazel 4/11/2010 Hazel
> 17 Pete 9/2/2012 Pete
> 17 Pete 9/2/2012 Pete
> 17 Pete 9/2/2012 Pete
> 17 Pete 9/2/2012 Pete
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
