[R] Selecting 1st and last dates from a set of dates
Nordlund, Dan (DSHS/RDA)
NordlDJ at dshs.wa.gov
Thu Jul 14 19:30:37 CEST 2016
It would be possible to ease the task of returning full rows from the matching dates (min or max) by wrapping the aggregate function up in a utility function and just returning the dates. I also borrowed the use of format() from Bill Dunlap’s example so that loading the zoo package is not necessary. I don’t have any notion about the efficiency of this approach as compared to Bill’s.
isFirstInMonth <- function(y) aggregate(y,list(format(y, "%Y-%m")),min)$x
d[d$Date %in% isFirstInMonth(d$Date), ]
There are other differences between the two approaches. Bill’s code will return one row per minimum and maximum date in the month. My approach would return all rows where d$Date was equal to the minimum or maximum dates in a month. Which to use depends on what the OP needs.
Dan
Daniel Nordlund, PhD
Research and Data Analysis Division
Services & Enterprise Support Administration
Washington State Department of Social and Health Services
From: William Dunlap [mailto:wdunlap at tibco.com]
Sent: Thursday, July 14, 2016 9:32 AM
To: Nordlund, Dan (DSHS/RDA)
Cc: R-help Mailing List
Subject: Re: [R] Selecting 1st and last dates from a set of dates
I did not use aggregate because it did not make it convenient to
return the rest of the row with the min or max data in it.
Bill Dunlap
TIBCO Software
wdunlap tibco.com<http://tibco.com>
On Thu, Jul 14, 2016 at 9:22 AM, Nordlund, Dan (DSHS/RDA) <NordlDJ at dshs.wa.gov<mailto:NordlDJ at dshs.wa.gov>> wrote:
Using William Dunlap's data, here is another alternative:
library(zoo)
aggregate(d$Date,list(as.yearmon(d$Date)),min)
aggregate(d$Date,list(as.yearmon(d$Date)),max)
Hope this is helpful,
Dan
Daniel Nordlund, PhD
Research and Data Analysis Division
Services & Enterprise Support Administration
Washington State Department of Social and Health Services
> -----Original Message-----
> From: R-help [mailto:r-help-bounces at r-project.org<mailto:r-help-bounces at r-project.org>] On Behalf Of Mehta,
> Gaurang
> Sent: Thursday, July 14, 2016 9:01 AM
> To: William Dunlap
> Cc: R-help Mailing List
> Subject: Re: [R] Selecting 1st and last dates from a set of dates
>
> Thanks William.
> This works. Thanks again.
>
> From: William Dunlap [mailto:wdunlap at tibco.com<mailto:wdunlap at tibco.com>]
> Sent: 14 July 2016 16:42
> To: Mehta, Gaurang
> Cc: Jeff Newmiller; R-help Mailing List
> Subject: Re: [R] Selecting 1st and last dates from a set of dates
>
> Does the following example help? isFirstInRun() and isLastInRun() are handy
> utility functions.
>
> > d <- transform(data.frame(Date=as.Date(c("2016-01-05", "2016-03-04",
> > "2016-03-30", "2015-12-02", "2016-03-04", "2015-12-21"))),
> > DaysSince1970=as.integer(Date), I=seq_along(Date)) d
> Date DaysSince1970 I
> 1 2016-01-05 16805 1
> 2 2016-03-04 16864 2
> 3 2016-03-30 16890 3
> 4 2015-12-02 16771 4
> 5 2016-03-04 16864 5
> 6 2015-12-21 16790 6
> > isFirstInRun <- function(x) c(TRUE, x[-1] != x[-length(x)])
> > isLastInRun <- function(x) c(x[-1] != x[-length(x)], TRUE) ds <-
> > d[order(d$Date),] ds
> Date DaysSince1970 I
> 4 2015-12-02 16771 4
> 6 2015-12-21 16790 6
> 1 2016-01-05 16805 1
> 2 2016-03-04 16864 2
> 5 2016-03-04 16864 5
> 3 2016-03-30 16890 3
> > ds[isFirstInRun(format(ds$Date, "%Y-%m")),]
> Date DaysSince1970 I
> 4 2015-12-02 16771 4
> 1 2016-01-05 16805 1
> 2 2016-03-04 16864 2
> > ds[isLastInRun(format(ds$Date, "%Y-%m")),]
> Date DaysSince1970 I
> 6 2015-12-21 16790 6
> 1 2016-01-05 16805 1
> 3 2016-03-30 16890 3
>
>
> Bill Dunlap
> TIBCO Software
> wdunlap tibco.com<http://tibco.com><http://tibco.com>
>
> On Thu, Jul 14, 2016 at 8:26 AM, Mehta, Gaurang
> <Gaurang.Mehta at royallondon.com<mailto:Gaurang.Mehta at royallondon.com><mailto:Gaurang.Mehta at royallondon.c<mailto:Gaurang.Mehta at royallondon.c>
> om>> wrote:
> Hi Jeff,
> I would say my problem is what you described in 2 below. My data is as
> follows:
> Date UC11 UC12 UC13
> 02/01/1997 1 2 0
> 03/01/1997 5 6 3
> 06/01/1997 5 4 6
> 07/01/1997 6 4 3
> 08/01/1997 6 5 5
> 09/01/1997 7 6 8
> 10/01/1997 8 5 5
> 13/01/1997 8 6 5
> 14/01/1997 7 4 4
> 15/01/1997 6 3 3
> 16/01/1997 8 5 5
> 17/01/1997 6 4 3
> 20/01/1997 5 4 2
> 21/01/1997 7 5 5
> 22/01/1997 16 12 12
> 23/01/1997 5 3 4
> 24/01/1997 5 2 2
> 27/01/1997 8 4 5
> 28/01/1997 7 5 9
> 29/01/1997 4 4 4
> 30/01/1997 4 4 6
> 31/01/1997 9 7 8
> 03/02/1997 9 6 8
>
>
> I want to select the data on the first date it can be 1st , 2nd or 3rd or any and
> last date it can be 31st, 30th and /or29th. I don’t need time.
> It would be great if you could help.
> Regards,
> Gaurang Mehta
>
>
>
> -----Original Message-----
> From: Jeff Newmiller
> [mailto:jdnewmil at dcn.davis.ca.us<mailto:jdnewmil at dcn.davis.ca.us><mailto:jdnewmil at dcn.davis.ca.us<mailto:jdnewmil at dcn.davis.ca.us>>]
> Sent: 14 July 2016 16:03
> To: Mehta, Gaurang; R-help Mailing List
> Subject: Re: [R] Selecting 1st and last dates from a set of dates
>
> I suspect the answer to your question (is there a function...) is almost
> certainly yes, but your question is too vague to be sure.
>
> 1) Data frames and matrices are different in important ways... it is highly
> unlikely that matrices would be appropriate for date data.
>
> 2) Do you mean "select records with earliest date in each month" or "select
> records whose day of month is 1"? If you need to work with time of day
> along with date then the solution will be different than if you are working
> with date only.
>
> 3) Have you converted your dates to Date or POSIXct or chron already?
> Which?
>
> 4) There are a lot of useful functions in base R [1][2], as well as contributed
> packages such as chron and lubridate.
>
> A reproducible example [3] is the standard way to communicate what
> problem you actually have. In particular, including the output of dput for a
> representative sample of data is a key element of that example.
>
> [1] ?DateTimeClasses
> [2] R News 4/1 p29
> [3] http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-
> reproducible-example
> --
> Sent from my phone. Please excuse my brevity.
>
> On July 14, 2016 5:44:52 AM PDT, "Mehta, Gaurang"
> <Gaurang.Mehta at royallondon.com<mailto:Gaurang.Mehta at royallondon.com><mailto:Gaurang.Mehta at royallondon.c<mailto:Gaurang.Mehta at royallondon.c>
> om>> wrote:
> >Hi Team,
> >I am struggling to select the first date and last date of the month
> >where there is data in a dataframe/matrix.
> >Is there any r function that can help to easily select data on the
> >first and last day of the month from the given set of dates and values.
> >Thanks for the help in advance.
> >Regards,
> >Gaurang
> >
> >
> >This email is intended for the person or company named and access by
> >anyone else is unauthorised. If you are not the person or company
> >named, please delete this email and notify the sender.
> >
> >The information in this email, including any attachments, may be
> >confidential or legally privileged (meaning that its disclosure is
> >protected in law). Its unauthorised disclosure, copying, distribution
> >or use is prohibited and may be unlawful.
> >
> >Email communications sent over the internet are not guaranteed to be
> >secure or virus-free and such messages are potentially at risk. The
> >Royal London Group accepts no liability for any claims arising from use
> >of the internet to transmit messages by or to any company within the
> >Royal London Group.
> >
> >The Royal London Group consists of The Royal London Mutual Insurance
> >Society Limited and its subsidiaries.
> >
> >The Royal London Mutual Insurance Society Limited is authorised by the
> >Prudential Regulation Authority and regulated by the Financial Conduct
> >Authority and the Prudential Regulation Authority and provides life
> >assurance and pensions.
> >
> >Registered in England and Wales number 99064.
> >
> >Registered office: 55 Gracechurch Street, London, EC3V 0RL.
> >
> >In the Republic of Ireland: The Royal London Mutual Insurance Society
> >Limited is authorised by the Prudential Regulation Authority in the UK
> >and is regulated by the Central Bank of Ireland for conduct of business
> >rules.
> >
> >
> > [[alternative HTML version deleted]]
> >
> >______________________________________________
> >R-help at r-project.org<mailto:R-help at r-project.org><mailto:R-help at r-project.org<mailto:R-help at r-project.org>> mailing list -- To
> >UNSUBSCRIBE and more, see 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.
>
> ______________________________________________
> R-help at r-project.org<mailto:R-help at r-project.org><mailto:R-help at r-project.org<mailto:R-help at r-project.org>> mailing list -- To
> UNSUBSCRIBE and more, see 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.
>
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org<mailto:R-help at r-project.org> mailing list -- To UNSUBSCRIBE and more, see
> 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.
______________________________________________
R-help at r-project.org<mailto:R-help at r-project.org> mailing list -- To UNSUBSCRIBE and more, see
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.
[[alternative HTML version deleted]]
More information about the R-help
mailing list