Extract date which is in strange format (EURO MTH/MM/YYYY) in R

user_15786038 0
Viewed 70

I want to convert the date in the format MMYYYY from a column which is in this format EURO MTH/MM/YYYY in R. The Monat column is in character.

structure(list(Monat = c("EURO MTH/07/2015", "EURO MTH/08/2015", 
"EURO MTH/09/2015", "EURO MTH/10/2015", "EURO MTH/11/2015", "EURO MTH/12/2015"
), Gesamtpreis = c(145667346.4375, 138659005.976562, 152031299.125, 
127354805.953125, 141803880.734375, 146695055.171875)), row.names = c(NA, 
6L), class = "data.frame")

Thank you.

lubridate r date
3 Answers

Here is a way but not with package lubridate, base R is enough.

df1 <-
  structure(list(
    Monat = c("EURO MTH/07/2015", "EURO MTH/08/2015", 
              "EURO MTH/09/2015", "EURO MTH/10/2015", 
              "EURO MTH/11/2015", "EURO MTH/12/2015"), 
    Gesamtpreis = c(145667346.4375, 138659005.976562, 
                    152031299.125, 127354805.953125, 
                    141803880.734375, 146695055.171875)), 
    row.names = c(NA, 6L), class = "data.frame")

convertdate <- function(x) {
  tmp <- sub("^[^/]+/", "", x)
  tmp <- as.Date(paste0("1/", tmp), "%d/%m/%Y")
  format(tmp, "%m%Y")
}

convertdate(df1$Monat)
#> [1] "072015" "082015" "092015" "102015" "112015" "122015"

Created on 2022-07-03 by the reprex package (v2.0.1)

user_82454060

Here's a way with lubridate, gsub and strftime.

library(lubridate)
df <-
    structure(list(
        Monat = c(
            "EURO MTH/07/2015",
            "EURO MTH/08/2015",
            "EURO MTH/09/2015",
            "EURO MTH/10/2015",
            "EURO MTH/11/2015",
            "EURO MTH/12/2015"
        ),
        Gesamtpreis = c(
            145667346.4375,
            138659005.976562,
            152031299.125,
            127354805.953125,
            141803880.734375,
            146695055.171875
        )
    ),
    row.names = c(NA,
                                6L),
    class = "data.frame")
    
strftime(my(gsub('EURO MTH/','', df$Monat)), '%m%Y')

[1] "072015" "082015" "092015" "102015" "112015" "122015"
user_5768600

Here is another option using stringr package and regex:

library(dplyr)
library(stringr)

df1 %>% 
  mutate(Monat = str_extract(str_remove_all(Monat, '/'), '\\d{6}')) %>% 
  pull(Monat)

[1] "072015" "082015" "092015" "102015" "112015" "122015"
user_133216470
Related