Extract Month and Year From Date in R

Question

I have tried a number of methods to no avail. I have data in terms of a date (YYYY-MM-DD) and am trying to get in terms of just the month and year, such as: MM-YYYY or YYYY-MM.

Ultimately, I would like it to look like this:

ID    Date         Month_Yr
1     2004-02-06   2004-02
2     2006-03-14   2006-03
3     2007-07-16   2007-07
...   ...          ...

I am doing this in hopes of plotting money earned on average in a month, from a number of orders, over a period of time. Any help, or a push in the right direction would be much appreciated.

Answer 1

This will add a new column to your data.frame with the specified format.

df$Month_Yr <- format(as.Date(df$Date), "%Y-%m")

df
#>   ID       Date Month_Yr
#> 1  1 2004-02-06  2004-02
#> 2  2 2006-03-14  2006-03
#> 3  3 2007-07-16  2007-07

# your data sample
  df <- data.frame( ID=1:3,Date = c("2004-02-06" , "2006-03-14" , "2007-07-16") )

a simple example:

dates <- "2004-02-06"

format(as.Date(dates), "%Y-%m")
> "2004-02"

side note: the data.table approach can be quite faster in case you're working with a big dataset.

library(data.table)
setDT(df)[, Month_Yr := format(as.Date(Date), "%Y-%m") ]

Answer 2

Here's another solution using a package solely dedicated to working with dates and times in R:

library(tidyverse)
library(lubridate)

(df <- tibble(ID = 1:3, Date = c("2004-02-06" , "2006-03-14", "2007-07-16")))
#> # A tibble: 3 x 2
#>      ID Date      
#>   <int> <chr>     
#> 1     1 2004-02-06
#> 2     2 2006-03-14
#> 3     3 2007-07-16

df %>%
  mutate(
    Date = ymd(Date),
    Month_Yr = format_ISO8601(Date, precision = "ym")
  )
#> # A tibble: 3 x 3
#>      ID Date       Month_Yr
#>   <int> <date>     <chr>   
#> 1     1 2004-02-06 2004-02 
#> 2     2 2006-03-14 2006-03 
#> 3     3 2007-07-16 2007-07

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

Answer 3

Use substring?

d = "2004-02-06"
substr(d,0,7)
>"2004-02"

Answer 4

The zoo package has the function of as.yearmon can help to convert.

require(zoo)

df$ym <- as.yearmon(df$date, "%Y %m")

Answer 5

In case the month name and not the numbers are wanted, like in the as duplicated classified question Extract month and year from datetime in R, this can be done with format and %B or %b.

date <- as.Date(c("2011-10-20", "2011-12-25", "2012-04-15"))

format(date, "%Y %B %b %m")
#[1] "2011 October Oct 10"  "2011 December Dec 12" "2012 April Apr 04"

Where

• %Y Year
• %B Full month name in the current locale
• %b Abbreviated month name in the current locale
• %m Month as decimal number

The documentation for the formats can be found in strptime, strftime.

In addition the month name in the current locale could be extracted using months

months(date)
#[1] "October"  "December" "April"

To get it in other languages Sys.setlocale could be used.

Sys.setlocale("LC_TIME", "de_DE.UTF-8")

format(date, "%Y %B %b %m")
#[1] "2011 Oktober Okt 10"  "2011 Dezember Dez 12" "2012 April Apr 04"

months(date)
#[1] "Oktober"  "Dezember" "April"   

Answer 6

The data.table package introduced the IDate class some time ago and zoo-package-like functions to retrieve months, days, etc (Check ?IDate). so, you can extract the desired info now in the following ways:

require(data.table)
df <- data.frame(id = 1:3,
                 date = c("2004-02-06" , "2006-03-14" , "2007-07-16"))
setDT(df)
df[ , date := as.IDate(date) ] # instead of as.Date()
df[ , yrmn := paste0(year(date), '-', month(date)) ]
df[ , yrmn2 := format(date, '%Y-%m') ]