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.