Assigning 1 or 0 in new column based on similar ID BUT sum not to exceed value in another column in-CodePudding

See table below: I want to assign 1 or 0 to a new_col but the sum of 1s per unique hhid column should not exceed the value of any element in the column "nets" as seen in the table below, assuming new_col doesn't exist

hhid   nets  new_col   
1 1     3     1       
1 1     3     1      
1 1     3     1       
1 1     3     0       
1 2     2     1       
1 2     2     1  
1 2     2     0       
1 3     2     1        
1 3     2     1       
1 3     2     0       
1 3     2     0

I tried code below

df %>% group_by(hhid) %>% mutate(new_col = ifelse(summarise(across(new_col), sum)<= df$nets),1,0)

CodePudding user response：

Try this:

Data:

df <- structure(list(hhid = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 
3L), nets = c(3L, 3L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L, 2L)), class = "data.frame", row.names = c(NA, 
-11L))

   hhid nets
1     1    3
2     1    3
3     1    3
4     1    3
5     2    2
6     2    2
7     2    2
8     3    2
9     3    2
10    3    2
11    3    2

Code:

df %>% 
  group_by(hhid) %>% 
  mutate(new_col = ifelse(row_number() <= nets,1,0))

Output:

# A tibble: 11 x 3
# Groups:   hhid [3]
    hhid  nets new_col
   <int> <int>   <dbl>
 1     1     3       1
 2     1     3       1
 3     1     3       1
 4     1     3       0
 5     2     2       1
 6     2     2       1
 7     2     2       0
 8     3     2       1
 9     3     2       1
10     3     2       0
11     3     2       0

CodePudding user response：

Same solution but using data.table instead of dplyr

dt <- structure(list(hhid = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 
3L), nets = c(3L, 3L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L, 2L)), row.names = c(NA, 
-11L), class = c("data.frame"))

library(data.table)

setDT(dt)

dt[, new_col :=  (seq_len(.N) <= nets), by = hhid]

dt

    hhid nets new_col
 1:    1    3       1
 2:    1    3       1
 3:    1    3       1
 4:    1    3       0
 5:    2    2       1
 6:    2    2       1
 7:    2    2       0
 8:    3    2       1
 9:    3    2       1
10:    3    2       0
11:    3    2       0