James Thatcher James Thatcher - 1 year ago 65
R Question

Working with times in R - categorising time intervals by an ID

I'd really appreciate some help on a problem I'm struggling to resolve in R.

I have a data frame with a series of IDs, dates and treatments. My end goal is to count the number of events that happen to an ID by treatment within a given timeframe.

For example,

ID has treatment A within twice within the space of three months, and four times within six months. I expect to have a series of conditional columns which count the number of occurrences.

The data frame follows a similar structure to:

ID date treatment
1A 20/09/2015 A
1A 21/09/2015 B
1A 22/10/2015 A
2A 22/09/2015 C
2A 20/10/2015 C

My end goal would be to have something like...

ID date treatment
1A 01/01/2016 A
1A 01/03/2016 A
1A 01/04/2016 A
1A 01/05/2016 A
1A 01/11/2016 A
2A 01/01/2016 A
2A 01/09/2016 A

Grouping to...

ID a_within_3_months a_within_6_months...
1A 3 1
2A 0 0

I'm sure this must be possible in data.table, but I'm struggling to figure out how to calculate this over rows by the conditions I want.

I hope this is clear - happy to provide more detail is helpful.

Really appreciate any help with this issue! Thank you for your time.

Answer Source

This might be what you are looking for:

> first_date <- as.Date(
> data<- data.frame(
       ID=c(rep(1,5), rep(2,5)), 
        date=seq(first_date, by="1 day", length.out=10),
        trtm=c(rep("a",3), rep("b",2), rep("c",3), rep("d",2)))

ID       date trtm
1 2014-06-12    a
1 2014-06-13    a
1 2014-06-14    a
1 2014-06-15    b
1 2014-06-16    b
2 2014-06-17    c
2 2014-06-18    c
2 2014-06-19    c
2 2014-06-20    d
2 2014-06-21    d

> data <- data.table(data)

> data[,.( within=max(date)-min(date),       
           n_of_trtm=length(date) ), 

    ID trtm within n_of_trtm
 1:  1    a 2 days         3
 2:  1    b 1 days         2
 3:  2    c 2 days         3
 4:  2    d 1 days         2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download