Conditional update referring to other columns

Question

I'm trying to do a conditional update in R on some data similar in shape to what's given below.

The rule is, where the value for Category Z == 0, set it to the value for Category X in the same Type.

Being an R newbie and not having a lot of time, I ended up using a loop because I couldn't see how to update a value using by(), but I'm hoping there is a more obvious solution.

Type    Category    Value
A       X           5
A       Y           2
A       Z           3
B       X           6
B       Y           2
B       Z           0
C       X           7
C       Y           2
C       Z           0

The output I want is:

Type    Category    Value
A       X           5
A       Y           2
A       Z           3 <- remains 3
B       X           6
B       Y           2
B       Z           6 <- updated to 6
C       X           7
C       Y           2
C       Z           7 <- updated to 7

Thanks in advance.

Answer 1

Here's a solution using the dplyr package:

library(dplyr)

# Assume your data frame is called "dat"
dat = dat %>% group_by(Type) %>%
  mutate(ValueNew = ifelse(Category=="Z" & Value==0, Value[Category=="X"], Value))

  Type Category Value ValueNew
1    A        X     5        5
2    A        Y     2        2
3    A        Z     3        3
4    B        X     6        6
5    B        Y     2        2
6    B        Z     0        6
7    C        X     7        7
8    C        Y     2        2
9    C        Z     0        7