vendredi 21 février 2020

Change All Values in the Future Based on a Value Today in R

I am currently working with historical, monthly, customer data. My fields include: CustomerName, OpenDate, ClosedDate, ProcessDate and ProdNum. This data tracks a customer's product type on a monthly level. The OpenDate is the original date the account opened, ClosedDate is if the customer closed the account, is null otherwise, ProcessDate is a monthly seq of dates for the account (think time series, Jan 2014, Feb 2014, March 2014, etc) and ProdNum is a number id assigned to a product.

The Problem is if a customer changes a product type at any given month, the only field that changes is ProdNum. I need OpenDate and ClosedDate to change as well.

Example

df<-data.frame(CustName=c("Jim","Jim","Jim","Jim","Jim","Jim","Jim","Jim","Jim"),
           OpenDate=seq(as.Date('2009-12-10'),by=0,length.out = 9),
           ClosedDate=seq(is.na(.),by=0,length.out = 9),
           PrcsDate=seq(as.Date('2010-01-01'),as.Date('2010-09-01'),by="months"),
           ProdNum=c('12','12','12','12','12','12','22','22','22'),
           Lagging=c("","12","12","12","12","12","12","22","22"),
           Leading=c("12","12","12","12","12","22","22","22",""),
           NewOpen=c('2009-12-10','2009-12-10','2009-12-10','2009-12-10','2009-12-10','2009-12-10','2010-07-01','2009-12-10','2009-12-10'),
           NewClosed=c('','','','','','2010-06-01','','',''))

My first attempt is a simple if_else statement:

df$NewOpen<-if_else(df$Lagging==df$ProdNum,df$OpenDate,df$ProcessDate)
df$NewClosed<-if_else(df$Lead==df$ProdNum,df$ClosedDate,df$ProcessDate)

This actually words for the specific row it is looking at, but I need a code where it will apply these NewOpen and NewClosed dates to all t+1 rows, where t is the month.

I'm actually stumped and not really sure where/how to proceed.

Aucun commentaire:

Enregistrer un commentaire