CODEWITHSUNDEEP
rfilterdplyr
Mr Frog
Mr Frog

Reputation: 446

Mutate on specific rows in R

I would like to mutate specific rows while evaluating the mutation on multiple rows that would remain outside the boundaries of a simple filter(). How can I do?

df <-
  data.frame(ID = c(1,1,1,2,2,2,3,3,3),
             Date= c(1,2,3,1,2,3,1,2,3),
             Money = c(500,400,500,5000,100,100,200,300,300), 
             Status = c("Good", "Bad", "Good", "Good","Good","Good", "Bad","Good","Good"))

For example I want to mutate(Money = max(Money)) at Date==1 but at the same time I don't want to drop other variables or perform the mutation on all grouping dates. Is there any way? The result should be as in the below table.

result <- 
  data.frame(ID = c(1,1,1,2,2,2,3,3,3), 
                     Date= c(1,2,3,1,2,3,1,2,3),
                     Money = c(5000,400,500,5000,100,100,5000,300,300),
                     Status = c("Good", "Bad", "Good", "Good","Good","Good", "Bad","Good","Good"),
                     Status_overall = c("Bad", "Bad", "Bad", "Good","Good","Good", "Bad","Bad","Bad"))

Upvotes: 0

Views: 1383

Answers (2)

akrun
akrun

Reputation: 887691

We can use case_when

library(dplyr)
df %>%
    mutate(Money = case_when(Date == 1 ~ max(Money), TRUE ~ Money))
#  ID Date Money Status
#1  1    1  5000   Good
#2  1    2   400    Bad
#3  1    3   500   Good
#4  2    1  5000   Good
#5  2    2   100   Good
#6  2    3   100   Good
#7  3    1  5000    Bad
#8  3    2   300   Good
#9  3    3   300   Good

Upvotes: 0

Duck
Duck

Reputation: 39613

Maybe try this. You can store the maximum in a new variable and then mutate by group using a conditional. I do not know where the last variable in your outcome comes:

library(dplyr)
#Code
new <- df %>% mutate(Val=max(Money)) %>%
  group_by(ID) %>% mutate(Money=ifelse(Date==1,Val,Money)) %>%
  select(-Val)

Output:

# A tibble: 9 x 4
# Groups:   ID [3]
     ID  Date Money Status
  <dbl> <dbl> <dbl> <fct> 
1     1     1  5000 Good  
2     1     2   400 Bad   
3     1     3   500 Good  
4     2     1  5000 Good  
5     2     2   100 Good  
6     2     3   100 Good  
7     3     1  5000 Bad   
8     3     2   300 Good  
9     3     3   300 Good

Maybe the simply way without complexity could be (Suggested by @GregorThomas):

#Code2
new <- df %>% mutate(Money=ifelse(Date==1,max(Money),Money))

Output:

  ID Date Money Status
1  1    1  5000   Good
2  1    2   400    Bad
3  1    3   500   Good
4  2    1  5000   Good
5  2    2   100   Good
6  2    3   100   Good
7  3    1  5000    Bad
8  3    2   300   Good
9  3    3   300   Good

Upvotes: 1

Related Questions