How to convert European short dates to ISO format?

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

How to convert European short dates to ISO format?

Luigi
Hello,
I have been trying to convert European short dates formatted as
dd/mm/yy into the ISO 8601 but the function as.Dates interprets them
as American ones (mm/dd/yy), thus I get:

```
oriDates = c("23/01/20", "24/01/20", "25/01/20", "26/01/20",
"27/01/20", "28/01/20", "29/01/20", "30/01/20",
             "31/01/20", "01/02/20", "02/02/20", "03/02/20",
"04/02/20", "05/02/20", "06/02/20", "07/02/20")
isoDates = as.Date(oriDates, format = "%m/%d/%y")
> isoDates
 [1] NA           NA           NA           NA           NA
NA           NA
 [8] NA           NA           "2020-01-02" "2020-02-02" "2020-03-02"
"2020-04-02" "2020-05-02"
[15] "2020-06-02" "2020-07-02"
```

How can I convert properly?
--
Best regards,
Luigi

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Ivan Krylov
On Wed, 10 Jun 2020 10:20:01 +0200
Luigi Marongiu <[hidden email]> wrote:

> the function as.Dates interprets them as American ones (mm/dd/yy)

> isoDates = as.Date(oriDates, format = "%m/%d/%y")

> How can I convert properly?

Pass the correct format? (Swap m and d in the format string.)

--
Best regards,
Ivan

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Daniel Nordlund-3
In reply to this post by Luigi
On 6/10/2020 1:20 AM, Luigi Marongiu wrote:
> isoDates = as.Date(oriDates, format = "%m/%d/%y")

You need to use the format for European short dates.

isoDates = as.Date(oriDates, format = "%d/%m/%y")


Hope this is helpful,

Dan

--
Daniel Nordlund
Port Townsend, WA  USA

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

K. Elo-2
In reply to this post by Luigi
Hi!

Should it be:

as.Date(oriDates, format="%d/%m/%y") # See the order of %d and %m!!

This command seems to work for me, here the output:

 [1] "2020-01-23" "2020-01-24" "2020-01-25" "2020-01-26" "2020-01-27"
"2020-01-28" "2020-01-29" "2020-01-30"
 [9] "2020-01-31" "2020-02-01" "2020-02-02" "2020-02-03" "2020-02-04"
"2020-02-05" "2020-02-06" "2020-02-07"

HTH,
Kimmo

ke, 2020-06-10 kello 10:20 +0200, Luigi Marongiu kirjoitti:

> Hello,
> I have been trying to convert European short dates formatted as
> dd/mm/yy into the ISO 8601 but the function as.Dates interprets them
> as American ones (mm/dd/yy), thus I get:
>
> ```
> oriDates = c("23/01/20", "24/01/20", "25/01/20", "26/01/20",
> "27/01/20", "28/01/20", "29/01/20", "30/01/20",
>              "31/01/20", "01/02/20", "02/02/20", "03/02/20",
> "04/02/20", "05/02/20", "06/02/20", "07/02/20")
> isoDates = as.Date(oriDates, format = "%m/%d/%y")
> > isoDates
>
>  [1] NA           NA           NA           NA           NA
> NA           NA
>  [8] NA           NA           "2020-01-02" "2020-02-02" "2020-03-02"
> "2020-04-02" "2020-05-02"
> [15] "2020-06-02" "2020-07-02"
> ```
>
> How can I convert properly?

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Berend Hasselman
In reply to this post by Luigi
Luigi,

Try format = "%d/%m/%y"

Berend Hasselman

> On 10 Jun 2020, at 10:20, Luigi Marongiu <[hidden email]> wrote:
>
>  ISO 8601

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Heinz Tuechler
In reply to this post by Luigi
maybe
isoDates <- as.Date(oriDates, format = "%d/%m/%y")

Heinz

Luigi Marongiu wrote/hat geschrieben on/am 10.06.2020 10:20:

> Hello,
> I have been trying to convert European short dates formatted as
> dd/mm/yy into the ISO 8601 but the function as.Dates interprets them
> as American ones (mm/dd/yy), thus I get:
>
> ```
> oriDates = c("23/01/20", "24/01/20", "25/01/20", "26/01/20",
> "27/01/20", "28/01/20", "29/01/20", "30/01/20",
>              "31/01/20", "01/02/20", "02/02/20", "03/02/20",
> "04/02/20", "05/02/20", "06/02/20", "07/02/20")
> isoDates = as.Date(oriDates, format = "%m/%d/%y")
>> isoDates
>  [1] NA           NA           NA           NA           NA
> NA           NA
>  [8] NA           NA           "2020-01-02" "2020-02-02" "2020-03-02"
> "2020-04-02" "2020-05-02"
> [15] "2020-06-02" "2020-07-02"
> ```
>
> How can I convert properly?
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Luigi
In reply to this post by Daniel Nordlund-3
Thank you!

On Wed, Jun 10, 2020 at 10:29 AM Daniel Nordlund <[hidden email]> wrote:

>
> On 6/10/2020 1:20 AM, Luigi Marongiu wrote:
> > isoDates = as.Date(oriDates, format = "%m/%d/%y")
>
> You need to use the format for European short dates.
>
> isoDates = as.Date(oriDates, format = "%d/%m/%y")
>
>
> Hope this is helpful,
>
> Dan
>
> --
> Daniel Nordlund
> Port Townsend, WA  USA
>


--
Best regards,
Luigi

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Jeff Newmiller
In reply to this post by Luigi
Fix your format specification?

?strptime

On June 10, 2020 1:20:01 AM PDT, Luigi Marongiu <[hidden email]> wrote:

>Hello,
>I have been trying to convert European short dates formatted as
>dd/mm/yy into the ISO 8601 but the function as.Dates interprets them
>as American ones (mm/dd/yy), thus I get:
>
>```
>oriDates = c("23/01/20", "24/01/20", "25/01/20", "26/01/20",
>"27/01/20", "28/01/20", "29/01/20", "30/01/20",
>             "31/01/20", "01/02/20", "02/02/20", "03/02/20",
>"04/02/20", "05/02/20", "06/02/20", "07/02/20")
>isoDates = as.Date(oriDates, format = "%m/%d/%y")
>> isoDates
> [1] NA           NA           NA           NA           NA
>NA           NA
> [8] NA           NA           "2020-01-02" "2020-02-02" "2020-03-02"
>"2020-04-02" "2020-05-02"
>[15] "2020-06-02" "2020-07-02"
>```
>
>How can I convert properly?

--
Sent from my phone. Please excuse my brevity.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Rich Shepard
On Wed, 10 Jun 2020, Jeff Newmiller wrote:

> Fix your format specification?
> ?strptime

>> I have been trying to convert European short dates formatted as dd/mm/yy
>> into the ISO 8601 but the function as.Dates interprets them as American
>> ones (mm/dd/yy), thus I get:

Look at Hadley Wickham's 'tidyverse' collection as described in R for Data
Science. There are date, datetime, and time functions that will do just what
you want.

Rich

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Martin Maechler
>>>>> Rich Shepard
>>>>>     on Wed, 10 Jun 2020 07:44:49 -0700 writes:

    > On Wed, 10 Jun 2020, Jeff Newmiller wrote:
    >> Fix your format specification?  ?strptime

    >>> I have been trying to convert European short dates
    >>> formatted as dd/mm/yy into the ISO 8601 but the function
    >>> as.Dates interprets them as American ones (mm/dd/yy),
    >>> thus I get:

    > Look at Hadley Wickham's 'tidyverse' collection as
    > described in R for Data Science. There are date, datetime,
    > and time functions that will do just what you want.

    > Rich

I strongly disagree that automatic guessing of date format is a
good idea:

If you have dates such as  01/02/03, 10/11/12 , ...
you cannot have a software (and also not a human) to *guess* for
you what it means.  You have to *know* or get that knowledge "exogenously",
i.e., from context (say "meta data" if you want) that you as
data analyst must have before you can reliably work with that
data.

There is a global standard (ISO) for dates,  2020-06-11, for today's;
These have the huge advantage that alphabetical ordering is
equivalent to time ordering ... and honestly I don't see why
smart people (such as most? R users) do not all use these much
more often, notably when it comes to data.

But as long as most people in the world don't use that format
and practically all default formats for dates (e.g. in
spreadsheats and computer locales) do not use the ISO
standard, but rather regional conventions, one must add meta
data to have 100% garantee to use the correct format.

Of course, you can often guess correctly with very high
(subjective) probability, e.g.,   11/23/99  is highly probably
the 23rd of Nov, 1999.... and indeed if you have more than a few
dates, it often helps to guess correctly.  But there's no
guarantee.

No, I state that it is much better to ask from the data analyst
to use their brains a little bit and enter the date format
explicitly, than using software that does guess it for them
correctly most of the time.  How should they find out at all in
the rare cases the automatic guess will be wrong ?

Martin Maechler
ETH Zurich  and  R Core team

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Richard O'Keefe-2
I would add to this that in an important data set I was working with,
most of the dates were dd/mm/yy but some of them were mm/dd/yy and
that led to the realisation that I couldn't *tell* for about 40% of
the dates which they were.  If they were all one or the other, no
worries, but when you have people from mixed backgrounds writing in
mixed formats, you have a problem.

On Thu, 11 Jun 2020 at 19:17, Martin Maechler <[hidden email]>
wrote:

> >>>>> Rich Shepard
> >>>>>     on Wed, 10 Jun 2020 07:44:49 -0700 writes:
>
>     > On Wed, 10 Jun 2020, Jeff Newmiller wrote:
>     >> Fix your format specification?  ?strptime
>
>     >>> I have been trying to convert European short dates
>     >>> formatted as dd/mm/yy into the ISO 8601 but the function
>     >>> as.Dates interprets them as American ones (mm/dd/yy),
>     >>> thus I get:
>
>     > Look at Hadley Wickham's 'tidyverse' collection as
>     > described in R for Data Science. There are date, datetime,
>     > and time functions that will do just what you want.
>
>     > Rich
>
> I strongly disagree that automatic guessing of date format is a
> good idea:
>
> If you have dates such as  01/02/03, 10/11/12 , ...
> you cannot have a software (and also not a human) to *guess* for
> you what it means.  You have to *know* or get that knowledge "exogenously",
> i.e., from context (say "meta data" if you want) that you as
> data analyst must have before you can reliably work with that
> data.
>
> There is a global standard (ISO) for dates,  2020-06-11, for today's;
> These have the huge advantage that alphabetical ordering is
> equivalent to time ordering ... and honestly I don't see why
> smart people (such as most? R users) do not all use these much
> more often, notably when it comes to data.
>
> But as long as most people in the world don't use that format
> and practically all default formats for dates (e.g. in
> spreadsheats and computer locales) do not use the ISO
> standard, but rather regional conventions, one must add meta
> data to have 100% garantee to use the correct format.
>
> Of course, you can often guess correctly with very high
> (subjective) probability, e.g.,   11/23/99  is highly probably
> the 23rd of Nov, 1999.... and indeed if you have more than a few
> dates, it often helps to guess correctly.  But there's no
> guarantee.
>
> No, I state that it is much better to ask from the data analyst
> to use their brains a little bit and enter the date format
> explicitly, than using software that does guess it for them
> correctly most of the time.  How should they find out at all in
> the rare cases the automatic guess will be wrong ?
>
> Martin Maechler
> ETH Zurich  and  R Core team
>
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Rich Shepard
In reply to this post by Martin Maechler
On Thu, 11 Jun 2020, Martin Maechler wrote:

>    > Look at Hadley Wickham's 'tidyverse' collection as
>    > described in R for Data Science. There are date, datetime,
>    > and time functions that will do just what you want.

> I strongly disagree that automatic guessing of date format is a
> good idea:

Martin,

I think either you misunderstood what I wrote or I was not sufficiently
explicit in my brief response. I did not mean to imply there was any
automatic guessing involved. Specifying input and output formats is
required.

Reading Hadley's book I was impressed that one could specify the format of
dates in the dataset and convert them all to the ISO-8601 format. Before
learning this I'd use emacs regex to do the reformating I needed (or,
sometimes, awk).

Regards,

Rich

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Rich Shepard
In reply to this post by Richard O'Keefe-2
On Thu, 11 Jun 2020, Richard O'Keefe wrote:

> I would add to this that in an important data set I was working with, most
> of the dates were dd/mm/yy but some of them were mm/dd/yy and that led to
> the realisation that I couldn't *tell* for about 40% of the dates which
> they were. If they were all one or the other, no worries, but when you
> have people from mixed backgrounds writing in mixed formats, you have a
> problem.

Richard,

Ouch! While I've not had data sets with this problem I've had many that were
extracted from spreadsheets that had a mix of date formats mm/dd/yyyy,
mm-dd-yyyy, and other strange strings. That's why I encourage my clients to
use a database rather than spreadsheets for their environmental data.

Regards,

Rich

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Martin Maechler
In reply to this post by Rich Shepard
>>>>> Rich Shepard
>>>>>     on Thu, 11 Jun 2020 06:29:13 -0700 writes:

    > On Thu, 11 Jun 2020, Martin Maechler wrote:
    >> > Look at Hadley Wickham's 'tidyverse' collection as >
    >> described in R for Data Science. There are date,
    >> datetime, > and time functions that will do just what you
    >> want.

    >> I strongly disagree that automatic guessing of date
    >> format is a good idea:

    > Martin,

    > I think either you misunderstood what I wrote or I was not
    > sufficiently explicit in my brief response. I did not mean
    > to imply there was any automatic guessing
    > involved. Specifying input and output formats is required.

Well, ok.  Yes, then I misunderstood.  I know there *are* R
packages out there which boast automatically finding the correct format.

If you are willing to specify the input format, I don't see why
one should use the huge tidyverse  instead of just using the
potent enough base R functions,  one of which Jeff Newmiller was
talking about (and to whom you replied saying you'd rather use
the extra functions).

    > Reading Hadley's book I was impressed that one could
    > specify the format of dates in the dataset and convert
    > them all to the ISO-8601 format. Before learning this I'd
    > use emacs regex to do the reformating I needed (or,
    > sometimes, awk).

Well, as you know I use emacs even more than R (because I use R
via emacs's ESS),  but I think I wouldn't use it to transform
dates or date times [well, unless such a date/datetime column
is so severely messed up that one format is not appropriate for
at least a few large chunks of these] because I'd rather try to
use completely reproducible R code from the beginning of
cleaning/reading/analysing the raw data to the end.

And for that, base R is entirely sufficient in spite of all the
advertisements of the many date/time formatting packages.
But yes, I wrote more about this about 10 weeks ago on the
R-devel list here  (which also seemed to have been
rather mis-understood, for which I must mostly blame myself of course) :

  https://stat.ethz.ch/pipermail/r-devel/2020-April/079260.html

In that thread, the following  'R News' article was mentioned as
good introduction in the subject from a 'base R' (+ "almost
recommended" package 'chron') point of view

  https://www.researchgate.net/publication/229087103_R_Help_Desk_Date_and_time_classes_in_R

which is really from here

  https://www.r-project.org/doc/Rnews/Rnews_2004-1.pdf

'R News' was the predecessor of the
R Journal, https://journal.r-project.org/

I think we should leave it here, because we've been diverting
too much.

Best,
Martin

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Rasmus Liland-3
In reply to this post by Luigi
On 2020-06-10 10:20 +0200, Luigi Marongiu wrote:
> I have been trying to convert European
> short dates formatted as dd/mm/yy into the
> ISO 8601 but the function as.Dates
> interprets them as American ones
> (mm/dd/yy)

Dear Luigi,

?strptime says:

        ‘%D’ Date format such as ‘%m/%d/%y’: the C99 standard says it
             should be that exact format (but not all OSes comply).

as.Date(oriDates, format="%d/%m/%y") works
fine for me here on the Linux laptop ...

You could also try to work on the strings
themselves to convert them to ISO format,
like:

        oriDates = c("23/01/20", "24/01/20", "25/01/20", "26/01/20",
                     "27/01/20", "28/01/20", "29/01/20", "30/01/20",
                     "31/01/20", "01/02/20", "02/02/20", "03/02/20",
                     "04/02/20", "05/02/20", "06/02/20", "07/02/20")
        d <- t(as.data.frame(strsplit(oriDates, "/")))
        dimnames(d) <- list(NULL, c("d", "m", "y"))
        u <- unique(d[,"y"])
        millenia <- "20"
        if (length(u)==1 & u==millenia) {
          Dates <- paste0(millenia, d[,"y"], "-",
                          d[,"m"], "-", d[,"d"])
          Dates <- as.Date(Dates)
        } else {
          Dates <- "'Allo 'allo ... error, error ... more complicated formatting is required to digest those dates ..."
        }
        print(Dates)

Best,
Rasmus

______________________________________________
[hidden email] 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.

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: How to convert European short dates to ISO format?

Luigi
Thank you. It worked. Case closed

On Thu, 11 Jun 2020, 16:48 Rasmus Liland, <[hidden email]> wrote:

> On 2020-06-10 10:20 +0200, Luigi Marongiu wrote:
> > I have been trying to convert European
> > short dates formatted as dd/mm/yy into the
> > ISO 8601 but the function as.Dates
> > interprets them as American ones
> > (mm/dd/yy)
>
> Dear Luigi,
>
> ?strptime says:
>
>         ‘%D’ Date format such as ‘%m/%d/%y’: the C99 standard says it
>              should be that exact format (but not all OSes comply).
>
> as.Date(oriDates, format="%d/%m/%y") works
> fine for me here on the Linux laptop ...
>
> You could also try to work on the strings
> themselves to convert them to ISO format,
> like:
>
>         oriDates = c("23/01/20", "24/01/20", "25/01/20", "26/01/20",
>                      "27/01/20", "28/01/20", "29/01/20", "30/01/20",
>                      "31/01/20", "01/02/20", "02/02/20", "03/02/20",
>                      "04/02/20", "05/02/20", "06/02/20", "07/02/20")
>         d <- t(as.data.frame(strsplit(oriDates, "/")))
>         dimnames(d) <- list(NULL, c("d", "m", "y"))
>         u <- unique(d[,"y"])
>         millenia <- "20"
>         if (length(u)==1 & u==millenia) {
>           Dates <- paste0(millenia, d[,"y"], "-",
>                           d[,"m"], "-", d[,"d"])
>           Dates <- as.Date(Dates)
>         } else {
>           Dates <- "'Allo 'allo ... error, error ... more complicated
> formatting is required to digest those dates ..."
>         }
>         print(Dates)
>
> Best,
> Rasmus
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.