Replace values in dataframe based on other dataframe with column name and value

Question

Let's say I have a dataframe of scores

library(dplyr)

id <- c(1 , 2)
name <- c('John', 'Ninaa')
score1 <- c(8, 6)
score2 <- c(NA, 7)

df <- data.frame(id, name, score1, score2)

Some mistakes have been made so I want to correct them. My corrections are in a different dataframe.

id <- c(2,1)
column <- c('name', 'score2')
new_value <- c('Nina', 9)
corrections <- data.frame(id, column, new_value)

I want to search the dataframe for the correct id and column and change the value. I have tried something with match but I don't know how mutate the correct column.

df %>% mutate(corrections$column = replace(corrections$column, match(corrections$id, id), corrections$new_value))

akrun · Accepted Answer · 2022-09-14 15:28:08Z

3

We could join by 'id', then mutate across the columns specified in the column and replace the elements based on the matching the corresponding column name (cur_column()) with the column

library(dplyr)
df %>% 
  left_join(corrections) %>% 
  mutate(across(all_of(column), ~ replace(.x, match(cur_column(), 
     column), new_value[match(cur_column(), column)]))) %>% 
  select(names(df))

-output

  id name score1 score2
1  1 John      8      9
2  2 Nina      6      7

edited Sep 14, 2022 at 15:28

answered Sep 14, 2022 at 15:16

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nina van Bruggen Over a year ago

Thank you so much, I could never have thought of this. I checked and it works!

Nina van Bruggen Over a year ago

I just found out that this solution doesn't work if you have multiple corrections for one id. It works well for the question I asked though!

Darren Tsai · Accepted Answer · 2022-09-14 16:21:18Z

2

It's an implementation of a feasible idea with dplyr::rows_update, though it involves functions of multiple packages. In practice I prefer a moderately parsimonious approach.

library(tidyverse)

corrections %>%
  group_by(id) %>%
  group_map(
    ~ pivot_wider(.x, names_from = column, values_from = new_value) %>% type_convert,
    .keep = TRUE) %>%
  reduce(rows_update, by = 'id', .init = df)

#   id name score1 score2
# 1  1 John      8      9
# 2  2 Nina      6      7

edited Sep 14, 2022 at 16:21

answered Sep 14, 2022 at 15:54

Darren Tsai

36.6k6 gold badges27 silver badges58 bronze badges

1 Comment

Nina van Bruggen Over a year ago

Actually, if you have multiple corrections for one id, this is the solution that works.

Collectives™ on Stack Overflow

Replace values in dataframe based on other dataframe with column name and value

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related