Group by a sequence in data frame in R

Question

I have data frame, I need to divide into 3 groups based on month.

• Group 1: month 1,2 & medication change at month 2
• Group 2: month 1,2,3 & medication change at month 3
• Group 3: month 1,2,3,4,up & medication change at month 4

Count frequency, mean, SE of each medication (total 5 medications) they use by 3 groups.

ID <- c(1,1,2,2,2,2,3,3,3,3,3,4,4,4,5,5,5,5,5,6,6,6,6,7,7,8,8,8,8)
month <- c(1,2,1,2,3,4,1,2,3,4,5,1,2,3,1,2,3,4,5,1,2,3,4,1,2,1,2,3,4)
med <- c(1,1,2,2,2,3,4,4,4,4,4,1,1,1,6,6,7,7,7,3,3,3,3,1,1,5,5,5,5)
mean <-  c(4,6,5,2,3,7,5,7,1,3,6,7,5,2,3,3,3,3,3,5,6,7,8,1,6,4,5,6,7)
df <- data.frame(ID,month,med,mean)
df

    ID month med mean
1   1     1   1    4
2   1     2   1    6
3   2     1   2    5
4   2     2   2    2
5   2     3   2    3
6   2     4   3    7
7   3     1   4    5
8   3     2   4    7
9   3     3   4    1
10  3     4   4    3
11  3     5   4    6
12  4     1   1    7
13  4     2   1    5
14  4     3   1    2
15  5     1   6    3
16  5     2   6    3
17  5     3   7    3
18  5     4   7    3
19  5     5   7    3
20  6     1   3    5
21  6     2   3    6
22  6     3   3    7
23  6     4   3    8
24  7     1   1    1
25  7     2   1    6
26  8     1   5    4
27  8     2   5    5
28  8     3   5    6
29  8     4   5    7

I have 8 ID

• ID = 1, should in Group 1, they use Med = 1.
• ID = 2, should in Group 3, Med = 2, if you check Med, they use 2 and 3, but as soon as they keep at least first of 2, I set them in group 3. If they change Med at month 3, I set them group 2. If they change med at month 2, I set them in group 1.
• ID = 3, should in Group 3. Med = 4
• ID = 4, should in Group 2. Med = 1
• ID = 5, should in Group 2. Med = 6
• ID = 6, should in Group 3. Med = 3
• ID = 7, should in Group 1. Med = 1
• ID = 8, should in Group 3. Med = 5

My data is large, I try to use data.table but I don't know how to split it.

Answer 1

If I understand your logic correctly, it can be restated in such a way, if there is no medication change, the group will be determined by the number of month. Otherwise it will be determined by which month the medication is switched and if this is the case, you can accomplish it using data.table as following:

setDT(df)[, list({medSwitch = which(c(0, diff(med)) != 0);
                  Group = ifelse(length(medSwitch) == 0,
                                 ifelse(.N <= 4, .N - 1, 3),
                                 ifelse(min(medSwitch) <= 4, min(medSwitch) - 1, 3))}), .(ID)]
#    ID V1
# 1:  1  1
# 2:  2  3
# 3:  3  3
# 4:  4  2
# 5:  5  2
# 6:  6  3
# 7:  7  1
# 8:  8  3

If you are looking for in-place mutation, i.e. add the group to original data frame, instead of summary as above, you can do:

setDT(df)[, Group := list({medSwitch = which(c(0, diff(med)) != 0);
                           ifelse(length(medSwitch) == 0,
                                  ifelse(.N <= 4, .N - 1, 3),
                                  ifelse(min(medSwitch) <= 4, min(medSwitch) - 1, 3))}), .(ID)] 

Note: The result matches your desired output but differs from the condition you defined at the beginning of your question. Possibly you mean | instead of &?