Cumulative duration omitting short spell breaks

[Henry Strawforrd]

I want to calculate the cumulative count within id. Below is an example where x is a dummy and I count the =1 within id over t.

The complication is that I want to allow only for short breaks in spell length. So if the spell break with x=0 is shorter or equal than 3 periods, than I continue counting, otherwise I reset to zero.

Below in the example, I have added the outcome y (I only care about the variable if x=1. Not important how it defined for the obs with x=0).

MWE

clear
input id t x y
1 2 1 1
1 2 0 1
1 2 1 2
1 2 0 2
1 2 0 2
1 2 0 2
1 2 0 0
1 2 1 1
end

[Ken Chui]

Are you sure that t is a column of 2? How would you even rank the data within ID?

[Henry Strawforrd]

Oh right, sorry, this is supposed to be an increasing time variable.

clear
input id t x y
1 1 1 1
1 2 0 1
1 3 1 2
1 4 0 2
1 5 0 2
1 6 0 2
1 7 0 0
1 8 1 1
end

[Hemanshu Kumar]

Not particularly elegant code, but I think it works:

sort id t
by id: gen byte spell_start = x == 1 & (_n == 1 | x[_n-1] == 0)
by id: gen spell_counter = sum(spell_start)
by id: gen spell_adjust = -spell_counter if x == 0 & x[_n-1] == 0 & x[_n-2] == 0 & x[_n-3] == 0 & x[_n-4] == 1
by id: replace spell_adjust = sum(spell_adjust)
replace spell_counter = spell_counter + spell_adjust
drop spell_start spell_adjust

[Clyde Schechter]

I'm a bit unclear about what you want. You say you want "the cumulative count within id" but you never say what you want the count of. You make a point of saying that the values of y are not relevant when x = 0. But, given everything you have said, it isn't clear that the variable y is ever relevant. So maybe you want a total of the values of y when x == 1, allowing for a run of at most 3 zeroes of x, and restarting the total at zero if you hit a run of more than 3 zeroes? If that's it, then the following code will do it:

assert inlist(x, 0, 1)  
by id (t), sort: gen spell1 = sum(x != x[_n-1])
by id spell (t), sort: gen d1 = _N
by id spell1 (t): gen skip = (d1 >= 3 & !x[1])
by id (t), sort: gen spell2 = sum(skip == 0 & skip[_n-1] != 0)
by id spell2 (t), sort: egen wanted = total(cond(!skip & x == 1, y, . ))

If that's not what you wanted, please explain more clearly when you post back. And it will help if you also show the result you are hoping for in the example data.

Added: Crossed with #3.

[Nick Cox]

The help file for tsspell (SSC) includes a worked example of how a short break may be indulged between two spells.

[Henry Strawforrd]

Thanks a lot! I think somehow this does not do it for me. In the cases where the first spell x=0 (which I don't have in the example but in the data) it counts them and then adds the spell of x=1.

Apologies if I was not clear. Y was what I would like to have, the wanted so to say.

I want to count the number of cases where x=1 in a spell and skip short breaks.

I'm a bit unclear about what you want. You say you want "the cumulative count within id" but you never say what you want the count of. You make a point of saying that the values of y are not relevant when x = 0. But, given everything you have said, it isn't clear that the variable y is ever relevant. So maybe you want a total of the values of y when x == 1, allowing for a run of at most 3 zeroes of x, and restarting the total at zero if you hit a run of more than 3 zeroes? If that's it, then the following code will do it:

assert inlist(x, 0, 1)
by id (t), sort: gen spell1 = sum(x != x[_n-1])
by id spell (t), sort: gen d1 = _N
by id spell1 (t): gen skip = (d1 >= 3 & !x[1])
by id (t), sort: gen spell2 = sum(skip == 0 & skip[_n-1] != 0)
by id spell2 (t), sort: egen wanted = total(cond(!skip & x == 1, y, . ))

If that's not what you wanted, please explain more clearly when you post back. And it will help if you also show the result you are hoping for in the example data.

Added: Crossed with #3.

[Henry Strawforrd]

Not particularly elegant code, but I think it works:

sort id t
by id: gen byte spell_start = x == 1 & (_n == 1 | x[_n-1] == 0)
by id: gen spell_counter = sum(spell_start)
by id: gen spell_adjust = -spell_counter if x == 0 & x[_n-1] == 0 & x[_n-2] == 0 & x[_n-3] == 0 & x[_n-4] == 1
by id: replace spell_adjust = sum(spell_adjust)
replace spell_counter = spell_counter + spell_adjust
drop spell_start spell_adjust

Thank you! I guess here I also need to repeat the adjust step to account for shorter spell breaks that only last one period or two?

[Henry Strawforrd]

The help file for tsspell (SSC) includes a worked example of how a short break may be indulged between two spells.

xtset id t
tsspell x
tsspell, cond(_spell == 0) spell(_gap) seq(_gapseq) end(_gapend)
bysort id _gap: gen _gaplength = _N if _gap
tsset
tsspell, cond(_spell | _gaplength <= 3) spell(_spell2) seq(_seq2) end(_end2)

did not give the expected y

[Henry Strawforrd]

Thanks a lot! I think somehow this does not do it for me. In the cases where the first spell x=0 (which I don't have in the example but in the data) it counts them and then adds the spell of x=1.

Apologies if I was not clear. Y was what I would like to have, the wanted so to say.

I want to count the number of cases where x=1 in a spell and skip short breaks.

Not also that y is cumulatively increasing, not total spell duration.

[Nick Cox]

#9 I came late to this thread which already seems a bit tangled. Reading the question again!

It seems that you want to count (or equivalently sum) instances of x with two rules:

1. Start at the beginning for each identifier.

2. But restart the count after 3 zeros.

Does this help? tsspell is not essential either way, but it gives you the count of consecutive zeros which you need for Rule 2.

clear
input id t x y
1 1 1 1
1 2 0 1
1 3 1 2
1 4 0 2
1 5 0 2
1 6 0 2
1 7 0 0
1 8 1 1
end

tsset id t
tsspell, cond(x == 0)

by id : gen wanted = x if _n == 1 | _seq == 4
by id : replace wanted = wanted[_n-1] + x if missing(wanted)

list, sep(0)

     +------------------------------------------------+
     | id   t   x   y   _seq   _spell   _end   wanted |
     |------------------------------------------------|
  1. |  1   1   1   1      0        0      0        1 |
  2. |  1   2   0   1      1        1      1        1 |
  3. |  1   3   1   2      0        0      0        2 |
  4. |  1   4   0   2      1        2      0        2 |
  5. |  1   5   0   2      2        2      0        2 |
  6. |  1   6   0   2      3        2      0        2 |
  7. |  1   7   0   0      4        2      1        0 |
  8. |  1   8   1   1      0        0      0        1 |
     +------------------------------------------------+

[Nick Cox]

Here is a way to cut out tsspell and use official Stata commands directly. It's perhaps more transparent.

I suppose my sub-theme is to attend to spells of zeros first. Then the rest of the calculation is easier.

clear
input id t x y
1 1 1 1
1 2 0 1
1 3 1 2
1 4 0 2
1 5 0 2
1 6 0 2
1 7 0 0
1 8 1 1
end

bysort id (t): gen zeros = 1 if x == 0 & x[_n-1] != 0
by id : replace zeros = zeros[_n-1] + 1 if x == 0 & zeros == .
by id : gen wanted = x if _n == 1 | zeros == 4
by id : replace wanted = wanted[_n-1] + x if missing(wanted)

list, sep(0)

     +---------------------------------+
     | id   t   x   y   zeros   wanted |
     |---------------------------------|
  1. |  1   1   1   1       .        1 |
  2. |  1   2   0   1       1        1 |
  3. |  1   3   1   2       .        2 |
  4. |  1   4   0   2       1        2 |
  5. |  1   5   0   2       2        2 |
  6. |  1   6   0   2       3        2 |
  7. |  1   7   0   0       4        0 |
  8. |  1   8   1   1       .        1 |
     +---------------------------------+

[Henry Strawforrd]

Wow spectacular! Thank you

[Nick Cox]

I wonder whether the wording led to some of the puzzlement, as you want is not at all to omit short spell breaks but to include them!

[Hemanshu Kumar]

#12 is so much nicer than my code in #4! I knew there had to be a better way to do it, it just didn't come together in my head at that time!