Replacing values of a row in a dataframe by creating a new row, but keeping the modified original row

Question

I have a dataframe as below, with all the values corresponding to an 'other' type, belonging to specific IDs:

df <- data.frame(ID = c("1", "1", "1", "2", "2", "3"), type = c("oth", "oth", "oth", "oth", "oth", "oth"), value = c("A", "B", "B", "C", "D", "D"))

 ID type value
  1  oth     A
  1  oth     B
  1  oth     B
  2  oth     C
  2  oth     D
  3  oth     D

I would like to change the types of the rows with values A, B, C to be 1, 2, 3 respectively (D stays as 'oth'). If it is changed, I would like to keep the 'oth' row but have the value as NA.

The above df would result into:

df2 <- data.frame(ID = c("1", "1", "1", "1", "1", "1", "2", "2", "2", "3"), type = c("1", "oth", "2", "oth", "2", "oth", "3", "oth", "oth", "oth"), value = c("A", NA, "B", NA, "B", NA, "C", NA, "D", "D"))

 ID type value
  1    1     A
  1  oth  <NA>
  1    2     B
  1  oth  <NA>
  1    2     B
  1  oth  <NA>
  2    3     C
  2  oth  <NA>
  2  oth     D
  3  oth     D

Note that any rows that match A,B,C will create a new row with correct type, but change the original one to value = NA. If possible, a dplyr solution would be preferred.

Any help would be appreciated, thanks!

Answer 1

You can create a vector of values to change and filter (values). Filter those values and replace value column to NA. Use match to change 'A' to 1, 'B' to 2 and 'C' to 3. Bind the two dataframes together.

library(dplyr)

values <- c('A', 'B', 'C')

df %>%
  filter(value %in% values) %>%
  mutate(value = NA)  %>%
  bind_rows(df %>%
  mutate(type = match(value, values), 
         type = replace(type, is.na(type), 'oth'))) %>%
  arrange(ID, type)

#   ID type value
#1   1    1     A
#2   1    2     B
#3   1    2     B
#4   1  oth  <NA>
#5   1  oth  <NA>
#6   1  oth  <NA>
#7   2    3     C
#8   2  oth  <NA>
#9   2  oth     D
#10  3  oth     D