CODEWITHSUNDEEP
rdatedataframe
Andra Barbu
Andra Barbu

Reputation: 5

Fill in missing date and fill with the data above

I've researched enough until i ask this here but can you please help me with some ideas for this issue?

My data table (df) looks like this:

client id   value    repmonth
123          100     2012-01-31
123          200     2012-02-31
123          300     2012-05-31

Therefore I have 2 missing months. And i want my data table to look like this:

client id   value    repmonth
123          100     2012-01-31
123          200     2012-02-31
123          200     2012-03-31
123          200     2012-04-31
123          300     2012-05-31

The code should be filling in the missing repmonth and fill the rows with the last value, in this case 200 and the came client id.

I have tried the following: 

zoo library 
tidyr library 
dlpyr library 
posixct

As for codes: ...plenty of fails 

library(tidyr)
df %>%
  mutate (repmonth = as.Date(repmonth)) %>%
  complete(repmonth = seq.Date(min(repmonth), max(repmonth),by ="month"))

or 

library(dplyr)

df$reportingDate.end.month <- as.POSIXct(df$datetime, tz = "GMT")
df <- tbl_df(df)   

list_df <- list(df, df)    # fake list of data.frames
seq_df <- data_frame(datetime = seq.POSIXt(as.POSIXct("2012-01-31"), 
                                           as.POSIXct("2018-12-31"), 
                                           by="month"))

lapply(list_df, function(x){full_join(total_loan_portfolios_3$reportingDate.end.month, seq_df, by=reportingDate.end.month)})

total_loan_portfolios_3$reportingmonth_notmissing <- full_join(seq_df,total_loan_portfolios_3$reportingDate.end.month)

or 

library(dplyr)

ts <- seq.POSIXt(as.POSIXct("2012-01-01",'%d/%m/%Y'), as.POSIXct("2018/12/01",'%d/%m/%Y'), by="month")

ts <- seq.POSIXt(as.POSIXlt("2012-01-01"), as.POSIXlt("2018-12-01"), by="month")
ts <- format.POSIXct(ts,'%d/%m/%Y')

df <- data.frame(timestamp=ts)

total_loan_portfolios_3 <- full_join(df,total_loan_portfolios_3$Reporting_date)

Finally, I have plenty of errors like

the format is not date

or

Error in seq.int(r1$mon, 12 * (to0$year - r1$year) + to0$mon, by) :
'from' must be a finite number

and others.

Upvotes: 0

Views: 648

Answers (1)

Soren
Soren

Reputation: 2445

The following solution uses lubridate and tidyr packages. Note that in OP example, dates are malformed, but implies having data with last-day-of-month input, so tried to replicate it here. Solution creates a sequence of dates from min input date to max input date to get all possible months of interest. Note that input dates are normalized to first-day-of-month to ensure proper sequence generation. With the sequence created, a left-join merge is done to merge data we have and identify missing data. Then fill() is applied to columns to fill in the missing NAs.

library(lubridate)
library(tidyr)
#Note OP has month of Feb with 31 days... Corrected to 28 but this fails to parse as a date
df <- data.frame(client_id=c(123,123,123),value=c(100,200,300),repmonth=c("2012-01-31","2012-02-29","2012-05-31"),stringsAsFactors = F)

df$repmonth <- ymd(df$repmonth) #convert character dates to Dates
start_month <- min(df$repmonth)
start_month <- start_month - days(day(start_month)-1) #first day of month to so seq.Date sequences properly

all_dates <- seq.Date(from=start_month,to=max(df$repmonth),by="1 month")
all_dates <- (all_dates %m+% months(1)) - days(1) #all end-of-month-day since OP suggests having last-day-of-month input?
all_dates <- data.frame(repmonth=all_dates)
df<-merge(x=all_dates,y=df,by="repmonth",all.x=T)

df <- fill(df,c("client_id","value"))

Solution yields:

> df
    repmonth client_id value
1 2012-01-31       123   100
2 2012-02-29       123   200
3 2012-03-31       123   200
4 2012-04-30       123   200
5 2012-05-31       123   300

Upvotes: 1

Related Questions