R help - Feb 2014 - datetime and date

Hi,

I have the following issue. The dataframe  df has a column (Date1) supposed
to be a date but read as a factor. There are two types of values in the
same column Date1
Type 1 are datetime like "5/23/2008 0:00:00"
Type 2 have no time like "1/10/13".

When I apply the following to the date column
df$Date1<-as.POSIXct(as.character(df$Date1, format = "%d/%m/%Y"))

For type 1 I got the expected result: "2008-05-23"
For type 2 I got NA.

I have searched but could not solve it. Please help.

Thanks,
Yolande

	[[alternative HTML version deleted]]

On 02/03/2014 12:53 PM, Yolande Tra wrote:
> Hi,
>
> I have the following issue. The dataframe  df has a column (Date1) supposed
> to be a date but read as a factor. There are two types of values in the
> same column Date1
> Type 1 are datetime like "5/23/2008 0:00:00"
> Type 2 have no time like "1/10/13".
>
> When I apply the following to the date column
> df$Date1<-as.POSIXct(as.character(df$Date1, format =
"%d/%m/%Y"))
>
> For type 1 I got the expected result: "2008-05-23"
> For type 2 I got NA.
>
> I have searched but could not solve it. Please help.
>
Hi Yolande,
Try this:

# first get the dates with times
df$Date1<-
  as.POSIXct(as.character(df$Date1, format = "%m/%d/%Y %H:%M:%S"))
# then fill in the ones without times
df$Date1[nchar(df$Date1) < 10]<-
  as.POSIXct(as.character(df$Date1, format = "%m/%d/%y"))

Notice that I think you got the month/day order wrong, if your first 
type is correct.

Jim

Please read the Posting Guide, which warns you to (among other things) post in
plain text and provide a reproducible example.

Read ?strptime. Note that you must provide a format that matches the data you
parse with it.

If you have both formats because you are also using Excel, you can fix the
inconsistent formatting quickly by re-loading it in Excel and setting a uniform
cell format for that column before saving again as csv.

If you really need to do this in R, you will have to convert twice, once for
each format, and transfer the non-NA values from one result vector to the other.
Something like (untested):
dtm1 <- as.POSIXct(as.character(df$Date1, format = "%d/%m/%Y
%H:%M"))
dtm2 <- as.POSIXct(as.character(df$Date1, format = "%d/%m/%Y"))
ok2 <- !is.na(dtm2)
dtm1[ok2] <- dtm2[ok2]
df$Date1 <- dtm1

Note: I recommend always setting the TZ environment variable appropriately for
the data you are working with before converting character to POSIXt types.
Whether this works and what to set it to can be frustratingly system-dependent
and since you did not follow the posting guidelines you will have to search for
that info yourself.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live
Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

On February 2, 2014 5:53:22 PM PST, Yolande Tra <yolande.tra at gmail.com>
wrote:
>Hi,
>
>I have the following issue. The dataframe  df has a column (Date1)
>supposed
>to be a date but read as a factor. There are two types of values in the
>same column Date1
>Type 1 are datetime like "5/23/2008 0:00:00"
>Type 2 have no time like "1/10/13".
>
>When I apply the following to the date column
>df$Date1<-as.POSIXct(as.character(df$Date1, format =
"%d/%m/%Y"))
>
>For type 1 I got the expected result: "2008-05-23"
>For type 2 I got NA.
>
>I have searched but could not solve it. Please help.
>
>Thanks,
>Yolande
>
>	[[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org mailing list
>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.