CODEWITHSUNDEEP
rdata.table
Lorinc Nyitrai
Lorinc Nyitrai

Reputation: 960

Iterating through a data.table using aggregation

My data.table is like: 

AccountNo    SubscriptionStart     SubscriptionEnd
11111        2010-10-12            2011-10-11
11112        2009-03-08            2010-03-08
11112        2010-03-08            2011-03-08
11112        2012-03-08            2013-03-08
11113        2011-08-21            2012-08-21

All I would like to achieve is adding a new column that flags accounts that got renewed later.

In other words: GotRenewed is TRUE if SubscriptionEnd <= max(SubscritionStart) within the subset defined by AccountNo. In this example it would be like:

AccountNo    SubscriptionStart     SubscriptionEnd    GotRenewed
11111        2010-10-12            2011-10-11         0
11112        2009-03-08            2010-03-07         1
11112        2010-03-08            2011-03-07         1
11112        2012-03-08            2013-03-07         0
11113        2011-08-21            2012-08-21         0

How could I achieve this? I appreciate your help!

Thanks.

Upvotes: 3

Views: 77

Answers (1)

Roland
Roland

Reputation: 132989

dt[,GotRenewed := SubscriptionEnd <= max(SubscriptionStart), by=AccountNo]

   AccountNo SubscriptionStart SubscriptionEnd GotRenewed
1:     11111        2010-10-12      2011-10-11      FALSE
2:     11112        2009-03-08      2010-03-08       TRUE
3:     11112        2010-03-08      2011-03-08       TRUE
4:     11112        2012-03-08      2013-03-08      FALSE
5:     11113        2011-08-21      2012-08-21      FALSE

Use as.numeric if you really need 0/1.

Upvotes: 2

Related Questions