Convert date fromat from 31-May-2020 to timeseries data in R

Admin 1
Viewed 40

I have a date format as 01-June-2020. I would like to convert it to a time series data in R. I tried as.Date but it returns NAs.

Here is the data:

dput(head(TData))
structure(list(Date = c("31-May-20", "01-Jun-20", "02-Jun-20", 
"03-Jun-20", "04-Jun-20", "07-Jun-20"), Price = c(7213.03, 7288.81, 
7285.23, 7222.41, 7207.78, 7267.86), Open = c(7050.66, 7213.03, 
7288.81, 7285.23, 7222.41, 7207.78), High = c(7338.96, 7288.81, 
7321.36, 7311.85, 7207.78, 7277.7), Low = c(7149.71, 7202.14, 
7277.63, 7202.39, 7129.25, 7233.67), Vol. = c("307.44M", "349.59M", 
"343.52M", "286.85M", "234.18M", "225.87M"), `Change %` = c("2.30%", 
"1.05%", "-0.05%", "-0.86%", "-0.20%", "0.83%")), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))
time-series r
2 Answers

We have to specify the format. By default, the format is YYYY-MM-DD i.e. %Y-%m-%d. Here, it is %d 2 digit day, followed by abbreviated month in characters- %b and 2 digit year - %y

TData$Date <- as.Date(TData$Date, '%d-%b-%y')

If we want to create a time series data, may be use xts

library(lubridate)
library(xts)
library(dplyr)
TData %>% 
    mutate(Date  = dmy(Date)) %>% 
    select(Date, where(is.numeric)) %>% 
    {xts(.[-1], order.by = .$Date)}
             Price    Open    High     Low
2020-05-31 7213.03 7050.66 7338.96 7149.71
2020-06-01 7288.81 7213.03 7288.81 7202.14
2020-06-02 7285.23 7288.81 7321.36 7277.63
2020-06-03 7222.41 7285.23 7311.85 7202.39
2020-06-04 7207.78 7222.41 7207.78 7129.25
2020-06-07 7267.86 7207.78 7277.70 7233.67

or may use tsibble

library(tsibble)
TData %>% 
    mutate(Date  = dmy(Date)) %>% 
    select(Date, where(is.numeric)) %>%
    as_tsibble(index = Date)

-output

# A tsibble: 6 x 5 [1D]
  Date       Price  Open  High   Low
  <date>     <dbl> <dbl> <dbl> <dbl>
1 2020-05-31 7213. 7051. 7339. 7150.
2 2020-06-01 7289. 7213. 7289. 7202.
3 2020-06-02 7285. 7289. 7321. 7278.
4 2020-06-03 7222. 7285. 7312. 7202.
5 2020-06-04 7208. 7222. 7208. 7129.
6 2020-06-07 7268. 7208. 7278. 7234.
user_37322710

We can also use lubridate package functions. Since months are stored as abbreviated month names we use %b instead of %m here:

library(lubridate)

df %>%
  mutate(Date = as_date(Date, format = "%d-%b-%Y"))

# A tibble: 6 x 7
  Date       Price  Open  High   Low Vol.    `Change %`
  <date>     <dbl> <dbl> <dbl> <dbl> <chr>   <chr>     
1 2020-05-31 7213. 7051. 7339. 7150. 307.44M 2.30%     
2 2020-06-01 7289. 7213. 7289. 7202. 349.59M 1.05%     
3 2020-06-02 7285. 7289. 7321. 7278. 343.52M -0.05%    
4 2020-06-03 7222. 7285. 7312. 7202. 286.85M -0.86%    
5 2020-06-04 7208. 7222. 7208. 7129. 234.18M -0.20%    
6 2020-06-07 7268. 7208. 7278. 7234. 225.87M 0.83%
user_143145200
Related