[R] Date seq question

[Brian Diggs]

On 1/20/2012 9:12 AM, cameron wrote:
> Can anyone please help me with this?
> I have a list of business dates.  What I want is to have last day of last
> month and paste them on next month.
>
> What i have                        What i want
> 5725 2011-09-22
> 5726 2011-09-23
> 5727 2011-09-26
> 5728 2011-09-27
> 5729 2011-09-28
> 5730 2011-09-29
> 5731 2011-09-30
> 5742 2011-10-17                 2011-09-30
> 5743 2011-10-18                 2011-09-30
> 5744 2011-10-19                 2011-09-30
> 5745 2011-10-20                 2011-09-30
> 5746 2011-10-21                 2011-09-30
> 5747 2011-10-24                 2011-09-30
> 5748 2011-10-25                 2011-09-30
> *5749 2011-10-26*                 2011-09-30
> 5765 2011-11-17                 2011-10-26
> 5766 2011-11-18                 2011-10-26
> 5767 2011-11-21                 2011-10-26
> 5768 2011-11-22                 2011-10-26
> 5769 2011-11-23                 2011-10-26
> 5770 2011-11-25                 2011-10-26
> 5771 2011-11-28                 2011-10-26
> 5772 2011-11-29                 2011-10-26
> *5773 2011-11-30*                 2011-10-26
> 5780 2011-12-09                 2011-11-30
> 5781 2011-12-12                 2011-11-30
> 5782 2011-12-13                 2011-11-30
> 5783 2011-12-14                 2011-11-30
> 5784 2011-12-15                 2011-11-30
> 5785 2011-12-16                 2011-11-30
> 5786 2011-12-19                 2011-11-30
> 5787 2011-12-20                 2011-11-30
> 5788 2011-12-21                 2011-11-30
> 5789 2011-12-22                 2011-11-30

Reformatting the data you gave:

> date<- c("9/22/2011", "9/23/2011", "9/26/2011", "9/27/2011",
> "9/28/2011", "9/29/2011", "9/30/2011", "10/17/2011", "10/18/2011",
> "10/19/2011", "10/20/2011", "10/21/2011", "10/24/2011",
> "10/25/2011", "10/26/2011", "11/17/2011", "11/18/2011", "11/21/2011",
> "11/22/2011", "11/23/2011", "11/25/2011", "11/28/2011", "11/29/2011",
> "11/30/2011", "12/9/2011", "12/12/2011", "12/13/2011", "12/14/2011",
> "12/15/2011", "12/16/2011", "12/19/2011", "12/20/2011",
> "12/21/2011", "12/22/2011")

Here is a solution using plyr and zoo:

library("plyr")
library("zoo")

DF <- data.frame(date)
DF <- mutate(DF,
              dt = as.Date(date, format="%m/%d/%Y"),
              ym = as.yearmon(dt))
lastday <- ddply(DF, .(ym), summarise, last=max(dt))
lastday <- mutate(lastday, ym=ym+(1/12))
merge(DF, lastday, all.x=TRUE)

I first turn the strings into dates, and then into yearmons (year-month) 
which is what I need zoo for.  Use ddply to get the last date within 
each year-month group.  Add one month to the yearmon summaries to go to 
the next month.  Then merge these two back together, implicitly by the 
yearmon.  You could then delete any of the intermediate result columns 
if you like.

-- 
Brian S. Diggs, PhD
Senior Research Associate, Department of Surgery
Oregon Health & Science University