CODEWITHSUNDEEP
rdatedataframe
T Richard
T Richard

Reputation: 601

Replacement of missing day and month in dates using R

This question is about how to replace missing days and months in a data frame using R. Considering the data frame below, 99 denotes missing day or month and NA represents dates that are completely unknown. 

df<-data.frame("id"=c(1,2,3,4,5),
           "date" = c("99/10/2014","99/99/2011","23/02/2016","NA",
                      "99/04/2009"))

I am trying to replace the missing days and months based on the following criteria:

  1. For dates with missing day but known month and year, the replacement date would be a random selection from the middle of the interval (first day to the last day of that month). Example, for id 1, the replacement date would be sampled from the middle of 01/10/2014 to 31/10/2014. For id 5, this would be the middle of 01/04/2009 to 30/04/2009. Of note is the varying number of days for different months, e.g. 31 days for October and 30 days for April.

  2. As in the case of id 2, where both day and month are missing, the replacement date is a random selection from the middle of the interval (first day to last day of the year), e.g 01/01/2011 to 31/12/2011.

Please note: complete dates (e.g. the case of id 3) and NAs are not to be replaced.

I have tried by making use of the seq function together with the as.POSIXct and as.Date functions to obtain the sequence of dates from which the replacement dates are to be sampled. The difficulty I am experiencing is how to automate the R code to obtain the date intervals (it varies across distinct id) and how to make a random draw from the middle of the intervals.

The expected output would have the date of id 1, 2 and 5 replaced but those of id 3 and 4 remain unchanged. Any help on this is greatly appreciated.

Upvotes: 1

Views: 532

Answers (1)

thelatemail
thelatemail

Reputation: 93813

This isn't the prettiest, but it seems to work and adapts to differing month and year lengths:

set.seed(999)
df$dateorig <- df$date

seld <- grepl("^99/", df$date)
selm <- grepl("^../99", df$date)
md <- seld & (!selm)
mm <- seld & selm
df$date <- as.Date(gsub("99","01",as.character(df$date)), format="%d/%m/%Y")

monrng <- sapply(df$date[md], function(x) seq(x, length.out=2, by="month")[2]) - as.numeric(df$date[md])
df$date[md] <- df$date[md] + sapply(monrng, sample, 1)

yrrng <- sapply(df$date[mm], function(x) seq(x, length.out=2, by="12 months")[2]) - as.numeric(df$date[mm])
df$date[mm] <- df$date[mm] + sapply(yrrng, sample, 1)

#df
#  id       date   dateorig
#1  1 2014-10-14 99/10/2014
#2  2 2011-02-05 99/99/2011
#3  3 2016-02-23 23/02/2016
#4  4       <NA>         NA
#5  5 2009-04-19 99/04/2009

Upvotes: 1

Related Questions