CODEWITHSUNDEEP
r
Anand
Anand

Reputation: 769

Sequentially create date for each record by ID in R datatable

I have a datatable with ID and origination date, each unique ID represent a row. I have to use the variable 'COUNT' (which is essentially the interval between orig_date and close_date in months) and sequentially replicate ORIG_DATE into DATE field as shown. The code I tried is taking only the first value of 'COUNT' (in this case its 3) and sequentialy replicating ORIG_DATE. I have different COUNT for different ID. How do I use corresponding COUNT for each unique ID and replicate the ORIG_DATE into another column called DATE

test.data

ID  COUNT   SCORE   VALUE   ORIG_DATE   CLOSE_DATE  
10748   3   750 450231  2015-03-01  2015-06-01      
10845   4   680 590231  2015-01-01  2015-05-01      
21758   7   760 650839  2014-11-01  2015-06-01   

test.panel <- test.data[rep(sequence(nrow(test.data)),COUNT)]

test.panel$DATE <- ymd(test.panel$ORIG_DATE)+ months(1:test.panel$COUNT)

Given below is the structure of datatable I am trying to create

 ID COUNT   SCORE   VALUE   ORIG_DATE   DATE
10748   3   750 450231  2015-03-01  2015-03-01
10748   3   750 450231  2015-03-01  2015-04-01
10748   3   750 450231  2015-03-01  2015-05-01
10748   3   750 450231  2015-03-01  2015-06-01
10845   4   680 590231  2015-01-01  2015-01-01
10845   4   680 590231  2015-01-01  2015-02-01
10845   4   680 590231  2015-01-01  2015-03-01
10845   4   680 590231  2015-01-01  2015-04-01
10845   4   680 590231  2015-01-01  2015-05-01
21758   7   760 650839  2014-11-01  2014-11-01
21758   7   760 650839  2014-11-01  2014-12-01
21758   7   760 650839  2014-11-01  2015-01-01
21758   7   760 650839  2014-11-01  2015-02-01
..........................................................
..........................................................

Upvotes: 1

Views: 34

Answers (1)

Carlos Cinelli
Carlos Cinelli

Reputation: 11597

It is actually simple to do this with data.table. Recreating your sample data:

test.data <- read.table( text = "
ID  COUNT   SCORE   VALUE   ORIG_DATE   CLOSE_DATE  
10748   3   750 450231  2015-03-01  2015-06-01      
10845   4   680 590231  2015-01-01  2015-05-01      
21758   7   760 650839  2014-11-01  2015-06-01", 
                 header = TRUE,
                 stringsAsFactors = FALSE,
                 colClasses = c("integer", "integer", "integer","integer", "Date", "Date") )
str(df)

Now doing what you want in data.table:

library(data.table)
test.data <- data.table(test.data)
test.data[ , list(CLOSE_DATE = seq(ORIG_DATE, CLOSE_DATE, by = "month")), 
   by = c("ID", "COUNT", "SCORE", "VALUE", "ORIG_DATE")]

       ID COUNT SCORE  VALUE  ORIG_DATE CLOSE_DATE
 1: 10748     3   750 450231 2015-03-01 2015-03-01
 2: 10748     3   750 450231 2015-03-01 2015-04-01
 3: 10748     3   750 450231 2015-03-01 2015-05-01
 4: 10748     3   750 450231 2015-03-01 2015-06-01
 5: 10845     4   680 590231 2015-01-01 2015-01-01
 6: 10845     4   680 590231 2015-01-01 2015-02-01
 7: 10845     4   680 590231 2015-01-01 2015-03-01
 8: 10845     4   680 590231 2015-01-01 2015-04-01
 9: 10845     4   680 590231 2015-01-01 2015-05-01
10: 21758     7   760 650839 2014-11-01 2014-11-01
11: 21758     7   760 650839 2014-11-01 2014-12-01
12: 21758     7   760 650839 2014-11-01 2015-01-01
13: 21758     7   760 650839 2014-11-01 2015-02-01
14: 21758     7   760 650839 2014-11-01 2015-03-01
15: 21758     7   760 650839 2014-11-01 2015-04-01
16: 21758     7   760 650839 2014-11-01 2015-05-01
17: 21758     7   760 650839 2014-11-01 2015-06-01

Upvotes: 2

Related Questions