How to convert YYYY-m date format?

[rama27]

Hi, I have a data with this date format: YYYY-m. I would like to convert it into normal YYYY-MM-DD, where DD will be always 01.

See following example:

df <- data.frame(Date = c('2013-Jan', '2013-Feb', '2013-Mar', '2013-Apr', '2013-May', '2013-Jun', 
                          '2013-Jul', '2013-Aug', '2013-Sep', '2013-Oct', '2013-Nov', '2013-Dec' ) ,
                 Value = c(12, 10, 5, 69, 41, 12, 13, 17, 25, 2, -10, 15))

df$Date <- as.Date(df$Date, "%Y-%b")  #doesnt work

Can you help me, please?

[smichal]

Hi @rama27,

the main problem is that data.frame always converts strings into factors, and as.Datedoes not know how to convert a factor into a Date.

So, here are two ways how to do it. One without and the other with the tidyverse.

library(tidyverse)
df <- data.frame(Date = c('2013-Jan', '2013-Feb', '2013-Mar', '2013-Apr', '2013-May', '2013-Jun', 
                         '2013-Jul', '2013-Aug', '2013-Sep', '2013-Oct', '2013-Nov', '2013-Dec' ) ,
                Value = c(12, 10, 5, 69, 41, 12, 13, 17, 25, 2, -10, 15))

# The problem is, data.frame converts strings into factors
class(df$Date)
#> [1] "factor"

# So, we have to convert it to a character string before we can convert it further
as.Date(paste0(as.character(df$Date), "-01"), format = "%Y-%b-%d")
#>  [1] "2013-01-01" "2013-02-01" "2013-03-01" "2013-04-01" "2013-05-01"
#>  [6] "2013-06-01" "2013-07-01" "2013-08-01" "2013-09-01" "2013-10-01"
#> [11] "2013-11-01" "2013-12-01"

# tidyverse way
df %>% 
 as_tibble() %>% 
 mutate(Date = paste0(as.character(Date), "-01")) %>% 
 mutate(Date = lubridate::as_date(Date, format = "%Y-%b-%d"))
#> # A tibble: 12 x 2
#>    Date       Value
#>    <date>     <dbl>
#>  1 2013-01-01    12
#>  2 2013-02-01    10
#>  3 2013-03-01     5
#>  4 2013-04-01    69
#>  5 2013-05-01    41
#>  6 2013-06-01    12
#>  7 2013-07-01    13
#>  8 2013-08-01    17
#>  9 2013-09-01    25
#> 10 2013-10-01     2
#> 11 2013-11-01   -10
#> 12 2013-12-01    15

^{Created on 2020-09-20 by the reprex package (v0.3.0)}

[rama27]

Hi, thanks for reply. I tried to replicate your code, but I got different output. How is that possible, please?

> df <- data.frame(Date = c('2013-Jan', '2013-Feb', '2013-Mar', '2013-Apr', '2013-May', '2013-Jun', 
+                           '2013-Jul', '2013-Aug', '2013-Sep', '2013-Oct', '2013-Nov', '2013-Dec' ) ,
+                  Value = c(12, 10, 5, 69, 41, 12, 13, 17, 25, 2, -10, 15))
> 
> class(df$Date)
[1] "factor"
> 
> as.Date(paste0(as.character(df$Date), "-01"), format = "%Y-%b-%d")
 [1] NA NA NA NA NA NA NA NA NA NA NA NA

[smichal]

Hmm, could be a locale setting. What is your locale?

Sys.getlocale()

library(tidyverse)
df <- data.frame(Date = c('2013-Jan', '2013-Feb', '2013-Mar', '2013-Apr', '2013-May', '2013-Jun', 
                          '2013-Jul', '2013-Aug', '2013-Sep', '2013-Oct', '2013-Nov', '2013-Dec' ) ,
                 Value = c(12, 10, 5, 69, 41, 12, 13, 17, 25, 2, -10, 15))

# works
Sys.setlocale("LC_TIME", "English_UK")
#> [1] "English_United Kingdom.1252"
as.Date(paste0(as.character(df$Date), "-01"), format = "%Y-%b-%d")
#>  [1] "2013-01-01" "2013-02-01" "2013-03-01" "2013-04-01" "2013-05-01"
#>  [6] "2013-06-01" "2013-07-01" "2013-08-01" "2013-09-01" "2013-10-01"
#> [11] "2013-11-01" "2013-12-01"

# works partly because the month names abbrs are different
Sys.setlocale("LC_TIME", "German")
#> [1] "German_Germany.1252"
as.Date(paste0(as.character(df$Date), "-01"), format = "%Y-%b-%d")
#>  [1] "2013-01-01" "2013-02-01" NA           "2013-04-01" NA          
#>  [6] "2013-06-01" "2013-07-01" "2013-08-01" "2013-09-01" NA          
#> [11] "2013-11-01" NA

# does not work at all
Sys.setlocale("LC_TIME", "Chinese")
#> [1] "Chinese (Simplified)_China.936"
as.Date(paste0(as.character(df$Date), "-01"), format = "%Y-%b-%d")
#>  [1] NA NA NA NA NA NA NA NA NA NA NA NA

^{Created on 2020-09-20 by the reprex package (v0.3.0)}