I think that might risk giving the wrong date for a date like 1/3/1990 which I
think in Val's data is mdy data not dmy.
As I read the data, where the separator is "/" the format is mdy and where the
separator is "-" it's dmy. So I would
go for:
library(lubridate)
DFX$dnew[grep("-", DFX$ddate, fixed = TRUE)] <- dmy(DFX$ddate[grep("-",
DFX$ddate, fixed = TRUE)])
DFX$dnew[grep("/", DFX$ddate, fixed = TRUE)] <- mdy(DFX$ddate[grep("/",
DFX$ddate, fixed = TRUE)])
DFX <- DFX[!is.na(DFX$dnew),]
DFX
name ddate dnew
1 A 19-10-02 2002-10-19
2 B 22-11-20 2020-11-22
3 C 19-01-15 2015-01-19
4 D 11/19/2006 2006-11-19
5 F 9/9/2011 2011-09-09
6 G 12/29/2010 2010-12-29
But I am so much in awe of Rui's skills with R, and that of most of the regular
commentators here, that I submit
this a little nervously!
Many thanks to all who teach me so much here, lovely, if I am correct, to
contribute for a change!
Chris
----- Original Message -----
> From: "Rui Barradas" <[email protected]>
> To: "Val" <[email protected]>, "[email protected] ([email protected])"
> <[email protected]>
> Sent: Tuesday, 21 January, 2020 00:40:29
> Subject: Re: [R] Mixed format
> Hello,
>
> The following strategy works with your data.
> It uses the fact that most dates are in one of 3 formats, dmy, mdy, ymd.
> It tries those formats one by one, after each try looks for NA's in the
> new column.
>
>
> # first round, format is dmy
> DFX$dnew <- lubridate::dmy(DFX$ddate)
> na <- is.na(DFX$dnew)
>
> # second round, format is mdy
> DFX$dnew[na] <- lubridate::mdy(DFX$ddate[na])
> na <- is.na(DFX$dnew)
>
> # last round, format is ymd
> DFX$dnew[na] <- lubridate::ymd(DFX$ddate[na])
>
> # remove what didn't fit any format
> DFX <- DFX[!is.na(DFX$dnew), ]
> DFX
>
>
> Hope this helps,
>
> Rui Barradas
>
> Às 22:58 de 20/01/20, Val escreveu:
>> Hi All,
>>
>> I have a data frame where one column is a mixed date format,
>> a date in the form "%m-%d-%y" and "%m/%d/%Y", also some are not in date
>> format.
>>
>> Is there a way to delete the rows that contain non-dates and
>> standardize the dates in one date format like %m-%d-%Y?
>> Please see my sample data and desired output
>>
>> DFX<-read.table(text="name ddate
>> A 19-10-02
>> B 22-11-20u
>> C 19-01-15
>> D 11/19/2006
>> F 9/9/2011
>> G 12/29/2010
>> H DEX",header=TRUE)
>>
>> Desired output
>> name ddate
>> A 19-10-2002
>> B 22-11-2020
>> C 19-01-2015
>> D 11-19-2006
>> F 09-09-2011
>> G 12-29-2010
>>
>> Thank you
>>
>> ______________________________________________
>> [email protected] 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.
>>
>
> ______________________________________________
> [email protected] 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.
--
Chris Evans <[email protected]> Visiting Professor, University of Sheffield
<[email protected]>
I do some consultation work for the University of Roehampton
<[email protected]> and other places
but <[email protected]> remains my main Email address. I have a work web site
at:
https://www.psyctc.org/psyctc/
and a site I manage for CORE and CORE system trust at:
http://www.coresystemtrust.org.uk/
I have "semigrated" to France, see:
https://www.psyctc.org/pelerinage2016/semigrating-to-france/
That page will also take you to my blog which started with earlier joys in
France and Spain!
If you want to book to talk, I am trying to keep that to Thursdays and my diary
is at:
https://www.psyctc.org/pelerinage2016/ceworkdiary/
Beware: French time, generally an hour ahead of UK.
______________________________________________
[email protected] 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.
