CODEWITHSUNDEEP
rdata.table
Gin_Salmon
Gin_Salmon

Reputation: 847

Change value of column based on criteria and by group

My data looks like this: 

     year month flag group
 1: 1992     6    1     8
 2: 1992     7    0     8
 3: 1992     8    0     8
 4: 1992     9    0     8
 5: 1992    10    0     8
 6: 1992    11    0     8
 7: 1992    12    0     8
 8: 1995     6    0    10
 9: 1995     7    0    11
10: 1995     8    0    11
11: 1995     9    1    11
12: 1995    10    0    11
13: 1995    11    0    11
14: 1995    12    0    11
15: 1998     6    0    13
16: 1998     7    0    13
17: 1998     8    0    13
18: 1998     9    0    13
19: 1998    10    0    13
20: 1998    11    0    13
21: 1998    12    0    13

What i need to do is to assign a value of 1 to all the rows that follow the first observation of 1 in the flag column, this however needs to also be done by group.

As a concrete example, i want this:

     year month flag group
 1: 1992     6    1     8
 2: 1992     7    1     8
 3: 1992     8    1     8
 4: 1992     9    1     8
 5: 1992    10    1     8
 6: 1992    11    1     8
 7: 1992    12    1     8
 8: 1995     6    0    10
 9: 1995     7    0    11
10: 1995     8    0    11
11: 1995     9    1    11
12: 1995    10    1    11
13: 1995    11    1    11
14: 1995    12    1    11
15: 1998     6    0    13
16: 1998     7    0    13
17: 1998     8    0    13
18: 1998     9    0    13
19: 1998    10    0    13
20: 1998    11    0    13
21: 1998    12    0    13

Notice how rows 1:7 are now 1, as well as 11:14 and also notice how there have been no changes to rows 15:21 seeing how there was no 1 initially.

Most of my ideas have revolved around using which to find out the index of the first 1 by group, but i have run into some trouble.

If anyone has any data.table() based solutions that'd be great.

I appreciate any help!

Here's a dput() of my base data if helpful:

library(data.table)

DT = setDT(structure(list(year = c(1992, 1992, 1992, 1992, 1992, 1992, 1992, 
1992, 1992, 1992, 1992, 1992, 1995, 1995, 1995, 1995, 1995, 1995, 
1995, 1995, 1995, 1995, 1995, 1995, 1998, 1998, 1998, 1998, 1998, 
1998, 1998, 1998, 1998, 1998, 1998, 1998), month = c(1, 2, 3, 
4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 
11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), flag = c(0, 0, 
0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 
1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), group = c(8L, 8L, 8L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 10L, 10L, 10L, 10L, 10L, 
10L, 11L, 11L, 11L, 11L, 11L, 11L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L)), row.names = c(NA, -36L), 
class = c("data.table", "data.frame")))

Upvotes: 0

Views: 784

Answers (4)

Frank
Frank

Reputation: 66819

You can do a non-equi join with the first month per group:

DT[unique(DT[flag==1], by="group"), on=.(group, month >= month), flag := 1]

This is the result with the dput from the OP:

    year month flag group
 1: 1992     1    0     8
 2: 1992     2    0     8
 3: 1992     3    0     8
 4: 1992     4    0     8
 5: 1992     5    0     8
 6: 1992     6    1     8
 7: 1992     7    1     8
 8: 1992     8    1     8
 9: 1992     9    1     8
10: 1992    10    1     8
11: 1992    11    1     8
12: 1992    12    1     8
13: 1995     1    0    10
14: 1995     2    0    10
15: 1995     3    0    10
16: 1995     4    0    10
17: 1995     5    0    10
18: 1995     6    0    10
19: 1995     7    0    11
20: 1995     8    0    11
21: 1995     9    1    11
22: 1995    10    1    11
23: 1995    11    1    11
24: 1995    12    1    11
25: 1998     1    0    13
26: 1998     2    0    13
27: 1998     3    0    13
28: 1998     4    0    13
29: 1998     5    0    13
30: 1998     6    0    13
31: 1998     7    0    13
32: 1998     8    0    13
33: 1998     9    0    13
34: 1998    10    0    13
35: 1998    11    0    13
36: 1998    12    0    13
    year month flag group

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389235

We return 1 for the rows from the first occurrence where flag = 1 and the group has at least one flag = 1

library(data.table)
dt[,flag := +(seq_len(.N)>= which.max(flag == 1) & any(flag == 1)),by = group]

dt

#    year month flag group
# 1: 1992     6    1     8
# 2: 1992     7    1     8
# 3: 1992     8    1     8
# 4: 1992     9    1     8
# 5: 1992    10    1     8
# 6: 1992    11    1     8
# 7: 1992    12    1     8
# 8: 1995     6    0    10
# 9: 1995     7    0    11
#10: 1995     8    0    11
#11: 1995     9    1    11
#12: 1995    10    1    11
#13: 1995    11    1    11
#14: 1995    12    1    11
#15: 1998     6    0    13
#16: 1998     7    0    13
#17: 1998     8    0    13
#18: 1998     9    0    13
#19: 1998    10    0    13
#20: 1998    11    0    13
#21: 1998    12    0    13
#    year month flag group

Which in dplyr would be

library(dplyr)
dt %>%
   group_by(group) %>%
   mutate(flag = +(row_number() >= which.max(flag == 1) & any(flag == 1)))

and in base R using ave would be

dt$flag <- with(dt, +(ave(flag == 1, group, FUN = function(x) 
                     seq_along(x) >= which.max(x) & any(x))))

data

dt <- structure(list(year = c(1992, 1992, 1992, 1992, 1992, 1992, 1992, 
1992, 1992, 1992, 1992, 1992, 1995, 1995, 1995, 1995, 1995, 1995, 
1995, 1995, 1995, 1995, 1995, 1995, 1998, 1998, 1998, 1998, 1998, 
1998, 1998, 1998, 1998, 1998, 1998, 1998), month = c(1, 2, 3, 
4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 
11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), flag = c(0, 0, 
0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 
1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), group = c(8L, 8L, 8L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 10L, 10L, 10L, 10L, 10L, 
10L, 11L, 11L, 11L, 11L, 11L, 11L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L)), row.names = c(NA, -36L), class = 
c("data.table","data.frame"))

Upvotes: 2

Asp DS
Asp DS

Reputation: 1

Use na.locf() from the zoo package

Step 1: Filter for groups containing at least one "1" and replace the "0"s in them with NA

Step 2: Use na.locf() to drag the most recent non-NA value to everything below

library(zoo)
library(data.table)

temp[group %in% temp[,max(flag),.(group)][V1==1]$group & flag == 0,flag:= NA][,flag:=na.locf(flag,na.rm = FALSE)]

Input table (temp)

structure(list(year = c(1992, 1992, 1992, 1992, 1992, 1992, 1992, 
1995, 1995, 1995, 1995, 1995, 1995, 1995, 1998, 1998, 1998, 1998, 
1998, 1998, 1998), month = c(6, 7, 8, 9, 10, 11, 12, 6, 7, 8, 
9, 10, 11, 12, 6, 7, 8, 9, 10, 11, 12), flag = c(1, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), group = c(8L, 
8L, 8L, 8L, 8L, 8L, 8L, 10L, 11L, 11L, 11L, 11L, 11L, 11L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L)), row.names = c(NA, -21L), class = c("data.table", 
"data.frame"))

Output table

structure(list(year = c(1992, 1992, 1992, 1992, 1992, 1992, 1992, 
1995, 1995, 1995, 1995, 1995, 1995, 1995, 1998, 1998, 1998, 1998, 
1998, 1998, 1998), month = c(6, 7, 8, 9, 10, 11, 12, 6, 7, 8, 
9, 10, 11, 12, 6, 7, 8, 9, 10, 11, 12), flag = c(1, 1, 1, 1, 
1, 1, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0), group = c(8L, 
8L, 8L, 8L, 8L, 8L, 8L, 10L, 11L, 11L, 11L, 11L, 11L, 11L, 13L, 
13L, 13L, 13L, 13L, 13L, 13L)), row.names = c(NA, -21L), class = c("data.table", 
"data.frame"))

Upvotes: 0

Sonny
Sonny

Reputation: 3183

You could use dplyr and cumsum:

library(dplyr)
df %>%
  group_by(group) %>%
  mutate(flag = ifelse(cumsum(flag) > 1, 1, 0))

Another way could be by using lag:

df %>%
  group_by(group) %>%
  mutate(flag = ifelse(flag != 1 & row_number() > 1, lag(flag, 1), flag))

Or in data.table as:

df[, flag := ifelse(cumsum(flag) > 1, 1, 0), by=group]

Upvotes: 0

Related Questions