CODEWITHSUNDEEP
rdplyrtidyverse
te time
te time

Reputation: 495

Change last value of group based on group aggregate

I would like to update the last value of a group to 1 if all the values in the group are 0. I would prefer a tidyverse solution to go with my overall workflow but others are welcome. The sample dataset below has two columns group1 and group2 to be grouped by, the valuecolumn where the last value needs to be updated and the results column showing my desired results. I tried conditioning on the sum of the value column per group but wasn't able to make it work. Thanks

  df= structure(list(group1 = c("A", "A", "A", "A", "A", "A", "A", 
"A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B"), group2 = c("20171", 
"20171", "20171", "20171", "20171", "20172", "20172", "20172", 
"20172", "20171", "20171", "20171", "20171", "20171", "20172", 
"20172", "20172", "20172"), value = c(`0.5%` = 0L, `0.5%` = 1L, 
`0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, 
`0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, 
`0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, `0.5%` = 0L, 
`0.5%` = 0L), result = c(0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 
0, 1, 0, 0, 0, 1)), row.names = c(NA, -18L), class = c("tbl_df", 
"tbl", "data.frame"))

Upvotes: 1

Views: 84

Answers (2)

AnilGoyal
AnilGoyal

Reputation: 26218

this will also work

df <- df[1:3]

df %>% group_by(group1, group2) %>%
  mutate(result = ifelse(row_number() == max(row_number()) & last(cumsum(value)) ==0, 1, value))
# A tibble: 18 x 4
# Groups:   group1, group2 [4]
   group1 group2 value result
   <chr>  <chr>  <int>  <dbl>
 1 A      20171      0      0
 2 A      20171      1      1
 3 A      20171      0      0
 4 A      20171      0      0
 5 A      20171      0      0
 6 A      20172      0      0
 7 A      20172      0      0
 8 A      20172      0      0
 9 A      20172      0      1
10 B      20171      0      0
11 B      20171      0      0
12 B      20171      0      0
13 B      20171      0      0
14 B      20171      0      1
15 B      20172      0      0
16 B      20172      0      0
17 B      20172      0      0
18 B      20172      0      1

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388817

You can use :

library(dplyr)

df %>%
  group_by(group1, group2) %>%
  mutate(result1 = if(all(value == 0)) replace(value, n(), 1) else value)

#   group1 group2 value result result1
#   <chr>  <chr>  <int>  <dbl>   <dbl>
# 1 A      20171      0      0       0
# 2 A      20171      1      1       1
# 3 A      20171      0      0       0
# 4 A      20171      0      0       0
# 5 A      20171      0      0       0
# 6 A      20172      0      0       0
# 7 A      20172      0      0       0
# 8 A      20172      0      0       0
# 9 A      20172      0      1       1
#10 B      20171      0      0       0
#11 B      20171      0      0       0
#12 B      20171      0      0       0
#13 B      20171      0      0       0
#14 B      20171      0      1       1
#15 B      20172      0      0       0
#16 B      20172      0      0       0
#17 B      20172      0      0       0
#18 B      20172      0      1       1

Upvotes: 3

Related Questions