I have the following data frame with monthly observations of n individuals.
df <- structure(list(
ID = c("A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ),
Gender = c("M","M","F","M","F","F","F","M","F","F"),
y_0101 = c(0,2,0,0,0,3,0,0,0,3),
y_0102 = c(6,2,0,0,2,0,2,0,6,0),
y_0103 = c(0,0,1,0,0,0,0,4,0,0),
y_0104 = c(0,0,0,2,0,3,0,2,0,2),
y_0105 = c(2,2,1,4,5,3,4,0,5,2),
y_0106 = c(2,2,1,4,5,5,6,4,3,0),
y_0107 = c(2,2,1,4,0,0,6,0,0,4),
y_0108 = c(0,0,0,1,0,0,7,0,2,8),
y_0109 = c(2,8,0,0,0,0,0,3,0,0),
y_0110 = c(2,0,0,2,2,0,0,0,0,4),
y_0111 = c(0,0,0,0,0,0,0,0,0,0),
y_0112 = c(0,0,0,0,0,2,0,0,0,0)),
class = "data.frame", row.names = c(NA, -10L))
df
I want for each row to create a measure that calculates the amount of time (# of columns) that elapses from the first cohort of zeros (could be another value like 'NA' or "sick"), to the start of the next cohort-period depending on some conditions.
This must be done within a specific interval of columns (y_0101:y_0112).
These sums of periods should apply, when two following two conditions holds:
First condition: First interval of 0's should be >= 2 before counted as starting point.
Second condition: next cohort of 0's must be >=2.
The sums of columns between cohorts will be added as a new column (name: "Diff").
Third condition: If more than one intervals, then this should be added as a new column (name: "Diff_2") and so on. If no more than one interval, then NA.
So, it ends up looking like the following:
ID | Gender | y_0101 | y_0102 | y_0103 | y_0104 | y_0105 | y_0106 | y_0107 | y_0108 | y_0109 | y_0110 | y_0111 | y_0112 | Diff | Diff_2 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | M | 0 | 6 | 0 | 0 | 2 | 2 | 2 | 0 | 2 | 2 | 0 | 0 | 6 | NA |
B | M | 2 | 2 | 0 | 0 | 2 | 2 | 2 | 0 | 8 | 0 | 0 | 0 | 5 | NA |
C | F | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 5 | NA |
D | M | 0 | 0 | 0 | 2 | 4 | 4 | 4 | 1 | 0 | 2 | 0 | 0 | 7 | NA |
E | F | 0 | 2 | 0 | 0 | 5 | 5 | 0 | 0 | 0 | 2 | 0 | 0 | 2 | 2 |
F | F | 3 | 0 | 0 | 3 | 3 | 5 | 0 | 0 | 0 | 0 | 0 | 2 | 3 | NA |
G | F | 0 | 2 | 0 | 0 | 4 | 6 | 6 | 7 | 0 | 0 | 0 | 0 | 4 | NA |
H | M | 0 | 0 | 4 | 2 | 0 | 4 | 0 | 0 | 3 | 0 | 0 | 0 | 4 | 1 |
I | F | 0 | 6 | 0 | 0 | 5 | 3 | 0 | 2 | 0 | 0 | 0 | 0 | 4 | NA |
J | F | 3 | 0 | 0 | 2 | 2 | 0 | 4 | 8 | 0 | 4 | 0 | 0 | 7 | NA |