Extract information for date from other columns

user_10924836 0
Viewed 38

I working with monthly data. Data is in a specific format in two columns Month and Year. Below you can see a sample of data:

df<-data.frame(
              Month=c("m1","m2","m3","m4","m5","m6","m7","m8","m9","m10","m11","m12"),
       Year=c("2020","2020","2020","2020","2020","2020","2020","2020","2020","2020","2020","2020"))

Now I want to convert this data, from that format into the format shown below or more precisely in column Date

enter image description here

So can anybody help me how to solve this problem?

dplyr r
3 Answers

In base R you can do:

df$Date <- as.Date(paste0(df$Year, gsub("m", "-", df$Month, fixed = TRUE), "-01"))
user_63564300

Here is an option using parse_number and my function -

library(dplyr)
library(readr)
library(lubridate)

df %>%
  mutate(Month = parse_number(Month), 
         Date = my(paste(Month, Year)))

#   Month Year       Date
#1      1 2020 2020-01-01
#2      2 2020 2020-02-01
#3      3 2020 2020-03-01
#4      4 2020 2020-04-01
#5      5 2020 2020-05-01
#6      6 2020 2020-06-01
#7      7 2020 2020-07-01
#8      8 2020 2020-08-01
#9      9 2020 2020-09-01
#10    10 2020 2020-10-01
#11    11 2020 2020-11-01
#12    12 2020 2020-12-01

Base R option -

transform(df, Date = as.Date(paste(1, sub('m', '', Month), Year), '%d %m %Y'))
user_39629140

Try

as.Date(paste0(df$Year, '-', gsub('\\D+', '', df$Month), '-01'))

#[1] "2020-01-01" "2020-02-01" "2020-03-01" "2020-04-01" "2020-05-01" "2020-06-01" "2020-07-01" "2020-08-01" "2020-09-01" "2020-10-01" "2020-11-01" "2020-12-01"
user_56355800
Related