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

user_16605264 0
Viewed 66

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!

dplyr dataframe r
4 Answers

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
user_39629140

You may try this way

df
rbind(df,
      df%>%
        filter(value %in% c("A", "B", "C")) %>%
        mutate(type = case_when(value == "A" ~ 1,
                              value == "B" ~ 2,
                              value == "C" ~ 3), 
               value = NA)) %>% 
  arrange(ID)

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

That would be my approach, I didn't use dplyr but the order seemed important

my_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"))

my_var <- which(my_df$value %in% c("A", "B", "C"))
if (length(my_var)) {
  my_temp <- my_df[my_var,]
}
my_var <- which(my_temp$value == "A")
if (length(my_var)) {
  my_temp[my_var, "type"] <- 1
}
my_var <- which(my_temp$value == "B")
if (length(my_var)) {
  my_temp[my_var, "type"] <- 2
}
my_var <- which(my_temp$value == "C")
if (length(my_var)) {
  my_temp[my_var, "type"] <- 3
}

my_df <- rbind(my_temp, my_df)
my_df <- my_df[order(my_df$ID, my_df$value),]

my_var <- which(my_df$type == "oth" & my_df$value %in% c("A", "B", "C"))
if (length(my_var)) {
  my_df[my_var, "value"] <- NA
}
user_165037690

Here is potentially another dplyr option.

First, create a vector vec with the specific categories to match and obtain numeric values for.

Then, you can create groups based on whether each row value is contained within the vector vec. This allow you to insert rows, combining rows with rbind.

Within each group, the first row will have the type converted to a number, and the remaining row(s) with either NA for value (if value is in vec) or keep the same value.

This seems to work with your example data. Let me know if this meets your needs.

library(dplyr)

vec <- c("A", "B", "C")

df %>%
  group_by(grp = cumsum(value %in% vec)) %>%
  do(rbind(
    mutate(head(., 1), type = match(value, vec)), 
    mutate(., value = ifelse(value %in% vec, NA, value)))) %>%
  ungroup() %>%
  select(-grp)

Output

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