How to mutate new column with intermediate results, accumulate or reduce, in R

user_2884859 0
Viewed 308

I have a dataset my_df with the following structure (dput is added in end of the question)

> my_df
   group_id other_id    case
1         1        1     add
2         1        1     add
3         1       11     add
4         1        1 replace
5         1       11 replace
6         1        1 replace
7         1       10     add
8         1       10 replace
9         2        2     add
10        2       10     add
11        2       10 replace
12        2        2 replace
13        2        3     add
14        2        3 replace

What I want to do (in a tidyverse fashion) is to create a new column say collection wherein other_id will be stored for each group_by on group_id based on these two conditions-

  • If case is add then current row's Other_id will be pasted in the previous value of this column

  • If case == 'replace' then current row's other_id will be replaced with "" (nothing) from the previous row's calculated (accumulated) value.

the result I want is something like

> result
   group_id other_id    case collection
1         1        1     add         1,
2         1        1     add       1,1,
3         1       11     add    1,1,11,
4         1        1 replace      1,11,
5         1       11 replace         1,
6         1        1 replace          
7         1       10     add        10,
8         1       10 replace          
9         2        2     add         2,
10        2       10     add      2,10,
11        2       10 replace         2,
12        2        2 replace          
13        2        3     add         3,
14        2        3 replace

Obviously there will be blanks at the end of each group because my_df is already arranged/sorted like that.

I was trying accumulate and reduce but I was only be able to generate/accumulate values where case == 'add', I could not apply str_replace within this pipeline (below). Moreover, I want that values of other_id will be pasted in collection when case == 'add' but only to the previously occurring value whether it may pertain to different case (rows 7 and 13 in result).

The syntax I was trying was working only partially

library(tidyverse)
my_df %>% group_by(group_id) %>%
  mutate(collection = case_when(case == "add" ~ accumulate(other_id, paste, sep=", "),
                                case == "replace" ~ "?"))

# A tibble: 14 x 4
# Groups:   group_id [2]
   group_id other_id case    collection            
   <chr>    <chr>    <chr>   <chr>                 
 1 1        1        add     1                     
 2 1        1        add     1, 1                  
 3 1        11       add     1, 1, 11              
 4 1        1        replace ?                     
 5 1        11       replace ?                     
 6 1        1        replace ?                     
 7 1        10       add     1, 1, 11, 1, 11, 1, 10
 8 1        10       replace ?                     
 9 2        2        add     2                     
10 2        10       add     2, 10                 
11 2        10       replace ?                     
12 2        2        replace ?                     
13 2        3        add     2, 10, 10, 2, 3       
14 2        3        replace ?

Thanks in anticipation.

sample dput is

my_df <- structure(list(group_id = c("1", "1", "1", "1", "1", "1", "1", 
"1", "2", "2", "2", "2", "2", "2"), other_id = c("1", "1", "11", 
"1", "11", "1", "10", "10", "2", "10", "10", "2", "3", "3"), 
    case = c("add", "add", "add", "replace", "replace", "replace", 
    "add", "replace", "add", "add", "replace", "replace", "add", 
    "replace")), row.names = c(NA, -14L), class = "data.frame")
accumulate tidyverse r reduce
4 Answers

Here is a possibility using accumulate2:

f <- function(cur, new, case) {
  if (case == "add") paste0(cur, new, ",") else sub(paste0(new, ","), "", cur)
}

my_df %>%
  mutate(collection = accumulate2(other_id, case, f, .init = "")[-1])

   group_id other_id    case collection
1         1        1     add         1,
2         1        1     add       1,1,
3         1       11     add    1,1,11,
4         1        1 replace      1,11,
5         1       11 replace         1,
6         1        1 replace           
7         1       10     add        10,
8         1       10 replace           
9         2        2     add         2,
10        2       10     add      2,10,
11        2       10 replace         2,
12        2        2 replace           
13        2        3     add         3,
14        2        3 replace
user_54368770

I had the same idea as @Cettt -- using accumulate2. Here's an option that uses regex to deal with the trailing commas.

addOrRemove = function(acc, other_id, case) {
  if(case == "add") {
    ifelse(acc == "", other_id, paste(acc, other_id, sep = ", "))
  } else {
    sub(
      paste0("((?<=^| )", other_id, "(, ))|((^|(, ))", other_id, "$)"), 
      "", 
      acc
      ,
      perl = TRUE
    )
  }
}


my_df %>% 
  group_by(group_id) %>%
    mutate(collection = unlist(accumulate2(other_id, case[-1], addOrRemove))
)

# A tibble: 14 x 4
# Groups:   group_id [2]
   group_id other_id case    collection
   <chr>    <chr>    <chr>   <chr>     
 1 1        1        add     "1"       
 2 1        1        add     "1, 1"    
 3 1        11       add     "1, 1, 11"
 4 1        1        replace "1, 11"   
 5 1        11       replace "1"       
 6 1        1        replace ""        
 7 1        10       add     "10"      
 8 1        10       replace ""        
 9 2        2        add     "2"       
10 2        10       add     "2, 10"   
11 2        10       replace "2"       
12 2        2        replace ""        
13 2        3        add     "3"       
14 2        3        replace ""
user_73593080

I was finally able to do it, without the need of doing it through custom function to be defined prior

my_df %>% group_by(group_id) %>%
  mutate(new = unlist(accumulate2(other_id, case, ~if_else(..3 != "add", sub(paste0(..2, ","), "", ..1), paste0(..1, ..2, ",")), .init = "")[-1]))

# A tibble: 14 x 4
# Groups:   group_id [2]
   group_id other_id case    new      
   <chr>    <chr>    <chr>   <chr>    
 1 1        1        add     "1,"     
 2 1        1        add     "1,1,"   
 3 1        11       add     "1,1,11,"
 4 1        1        replace "1,11,"  
 5 1        11       replace "1,"     
 6 1        1        replace ""       
 7 1        10       add     "10,"    
 8 1        10       replace ""       
 9 2        2        add     "2,"     
10 2        10       add     "2,10,"  
11 2        10       replace "2,"     
12 2        2        replace ""       
13 2        3        add     "3,"     
14 2        3        replace ""
user_28848590

Also in base R:

my_df$collcetion <- Reduce(function(x, y) {
  if(my_df$case[y] == "add") {
    paste0(x, my_df$other_id[y], ",")
  } else {
    sub(paste0(my_df$other_id[y], ","), "", x)
  }
}, init = "1,", seq_len(nrow(my_df))[-1], accumulate = TRUE)

my_df

   group_id other_id    case collcetion
1         1        1     add         1,
2         1        1     add       1,1,
3         1       11     add    1,1,11,
4         1        1 replace      1,11,
5         1       11 replace         1,
6         1        1 replace           
7         1       10     add        10,
8         1       10 replace           
9         2        2     add         2,
10        2       10     add      2,10,
11        2       10 replace         2,
12        2        2 replace           
13        2        3     add         3,
14        2        3 replace
user_143145200
Related