How to extract and convert first 6 digits of a 10 character-number into as.Date?

user_8811399 0
Viewed 147

Let's say I have 10 digits representing a social security numbers (obviously fake in this example) stored as.character().

df <- data.frame(number = as.character(c("1208891384", "2105650124", "0102018213")))

> df
      number
1 1208891384
2 2105650124
3 0102018213

The social security number represents ddmmyy-xxxx. I want to extract these ddmmyy from the character string an create a new covariate including the 6 digits as.Date().

I would prefer a solution in dplyr

Expected output

> df
      number          date
1 1208891384    1989-08-12
2 2105650124    1965-05-21
3 0102018213    2001-02-01

And

str(df)
'data.frame':   6 obs. of  2 variable:
 $ number: chr  "1208891384" "2105650124" "0102018213"
 $ date      : Date, format: 1989-08-12 1965-05-21 ...

Update

Tried approach below on my data. Then received an error.

pp <- structure(list(cpr = c("0311611951", "1902521268", "1209470762"
)), row.names = c(NA, -3L), class = "data.frame")

And

pp %>% mutate(b_day=substr(cpr,1,2),
       month=substr(cpr,3,4),
       year=as.numeric(substr(cpr,5,6)),
       date=as.Date(substr(cpr,1,6),'%d%m%y'),
       yearcheck=as.numeric(format(date,'%Y')),
       year2=ifelse(yearcheck>format(Sys.Date(),'%Y'),yearcheck-100,yearcheck),
       Date=as.Date(paste0(as.character(year2),month,day),'%Y%m%d'))

Gives

Problem with mutate() input Date. x cannot coerce type 'closure' to vector of type 'character' ℹ Input Date is as.Date(paste0(as.character(year2), month, day), "%Y%m%d"). Run rlang::last_error() to see where the error occurred.

dplyr dataframe r date string
1 Answers

Try this. Pay attention of years, as some dates before 1970 in R can not be transformed properly. Here the code:

library(dplyr)
#Data
df <- data.frame(number = as.character(c("1208891384", "2105650124", "0102018213")))
#Code
df <- df %>% mutate(day=substr(number,1,2),
              month=substr(number,3,4),
              year=as.numeric(substr(number,5,6)),
              date=as.Date(substr(number,1,6),'%d%m%y'),
              yearcheck=as.numeric(format(date,'%Y')),
              year2=ifelse(yearcheck>as.numeric(format(Sys.Date(),'%Y')),yearcheck-100,yearcheck),
              Date=as.Date(paste0(as.character(year2),month,day),'%Y%m%d')) %>%
  select(c(number,Date))

Output:

      number       Date
1 1208891384 1989-08-12
2 2105650124 1965-05-21
3 0102018213 2001-02-01
user_20808480
Related