CODEWITHSUNDEEP
rdate
Kay Khaing Kyaw
Kay Khaing Kyaw

Reputation: 35

converting to date format in dataframe in R

I would like to convert my Date column into real date format. I cannot convert with as.Date. Could you please help me? I tried with following. But it doesn't work. 1)as.POSIXct(as.character(df1$Date, format="%Y%m%d")) 2)as.Date(as.character(Date),"%Y/%m%/d").

    > head(df1)
          Date                sim                obs
    2 43091.25 313.62295499999999             314.39
    3 43093.25 313.60034200000001             314.43
    4 43094.25         313.608948             314.31
    5 43095.25 313.56323200000003             314.24
    6 43096.25 313.52330000000001              314.2
    7 43097.25 313.47250000000003 314.29000000000002
    > str(df1)
   data.frame': 700 obs. of  3 variables:
     $ Date: chr  "43091.25" "43093.25" "43094.25" "43095.25" ...
     $ sim : chr  "313.62295499999999" "313.60034200000001" "313.608948" 
                      "313.56323200000003" ...
     $ obs : chr  "314.39" "314.43" "314.31" "314.24" ...

Upvotes: 2

Views: 1010

Answers (3)

s_baldur
s_baldur

Reputation: 33753

There is no need to use external packages, just specify the origin Excel uses:

Date <- c("43091.25", "43093.25", "43094.25", "43095.25", "43096.25", "43097.25")

as.Date(as.numeric(Date), origin = "1899-12-30")
# [1] "2017-12-22" "2017-12-24" "2017-12-25" "2017-12-26" "2017-12-27" "2017-12-28"

Upvotes: 2

Waldi
Waldi

Reputation: 41260

Dates with a 43ddd.dd pattern usually come from Excel.
You can use openxlsx :

openxlsx::convertToDateTime(as.numeric("43091.25"))
[1] "2017-12-22 06:00:00 CET"

Upvotes: 1

duncan
duncan

Reputation: 3

Waldi's answer is great and openxlsx is great. As an alternative for anyone coming across this in the future, you can also use janitor:

df <- tribble(~Date, ~sim, ~obs,
      43091.25, 313.6222956, 314.39,
    43093.25, 313.60034200000001, 314.43,
    43094.25, 313.608948, 314.31)

df %>%
  mutate(Date = janitor::excel_numeric_to_date(Date))

Produces:

  Date         sim   obs
  <date>     <dbl> <dbl>
1 2017-12-22  314.  314.
2 2017-12-24  314.  314.
3 2017-12-25  314.  314.

Upvotes: 0

Related Questions