Using data.table to identify all event occurence with condition of picking first occurence if in sequence
<p>I am trying to identify all occurrence of an event and if repeating in sequence pick the first occurrence. I am able to tag and add a count but not able to reset the count after there is change in event.</p>

<p>My data has ~1M rows with 30 odd IDs. I have added only one ID but have 30 odd IDs in my data. The table has an ID, datetime and status. </p>

<p>Status is the event which can have multiple values -A, B, C... The event I am concerned is for B. </p>

<p>I want to add three columns -</p>

<p>Occurrence_B - Flag for event is B</p>

<p>Count_B - Counts the consecutive occurrence for event=B with reset when the event changes</p>

<p>Include_B - Flag to show if that specific occurrence is the first or continued occurrence </p>

<p>I will subset the data where Include_B='new' to pick the first occurrence among a sequence. </p>

<pre><code>ID  Date    Status  Occurrence_B    Count_B Include_B

A   7/28/15 12:00 AM    A   0   0   0

A   7/28/15 12:30 AM    A   0   0   0

A   7/30/15 12:00 AM    B   1   1   new

A   7/31/15 12:00 AM    B   1   2   continued

A   7/31/15 11:00 AM    B   1   3   continued

A   8/2/15 10:00 AM         B   0   0   0

A   8/3/15 12:00 AM         C   0   0   0

A   8/4/15 12:00 AM         B   1   1   new

A   8/5/15 12:00 AM         B   1   2   continued

A   8/6/15 12:00 AM         A   1   0   continued

A   8/7/15 12:00 AM         B   1   1   new
</code></pre>

<p><a href="http://i.stack.imgur.com/9OxOC.png" rel="nofollow">table_picture</a></p>

<p>My sample code--</p>

<pre><code>d1[, Occurrence_B:=Status %in% c('B')+0L]

d1[, Count_B := cumsum(Occurrence_B), by=.(ID,Status)]
</code></pre>

<p>Issue is I don't know how to reset count_B once event changes. I am trying to investigate but I am new to data.table so will greatly appreciate any help.</p>

<p>Please let me know if you have any questions.</p>

<p>SK</p>