Updating Values in New Data with Old Data

Question

I have the following data frame:

library(dplyr)

old_data = data.frame(id = c(1,2,3), var1 = c(11,12,13))

> old_data

  id var1
1  1   11
2  2   12
3  3   13

I want to replace the values in the 2nd row of "old_data" with data in "new_data" (i.e. rows in "old_data" where the id variables matches ):

new_data = data.frame(id = c(4,2,5), var1 = c(11,15,13))

> new_data

  id var1
1  4   11
2  2   15
3  5   13

Using the answer found here (Update rows of data frame in R), I tried to do this with the "dplyr" library:

update =  old_data %>%
     rows_update(new_data, by = "id")

But this gave me the following error:

Error: Attempting to update missing rows.
Run `rlang::last_error()` to see where the error occurred.

This is what I am trying to get:

Can someone please tell me what I am doing wrong?

Thanks!

score 4 · Accepted Answer · answered Dec 29 '21 at 04:37

4

A little bit messy but this works (on this sample data at least)

old_data %>% 
  left_join(new_data,by="id") %>% 
  mutate(var1 = if_else(!is.na(var1.y),var1.y,var1.x)) %>% 
  select(id,var1)

#  id var1
#1  1   11
#2  2   15
#3  3   13

answered Dec 29 '21 at 04:37

Martin

307
1
10

@ Martin : Thank you so much! This worked! – stats_noob Dec 29 '21 at 04:38
I'm glad! Please consider marking as accepted when you can. Thank you! – Martin Dec 29 '21 at 04:48

score 2 · Answer 2 · answered Dec 29 '21 at 05:21

2

A base R approach using match -

inds <- match(old_data$id, new_data$id)
old_data$var1[!is.na(inds)] <- na.omit(new_data$var1[inds])
old_data

#  id var1
#1  1   11
#2  2   15
#3  3   13

answered Dec 29 '21 at 05:21

Ronak Shah

377,200
20
156
213

AndrewGB · Answer 3 · 2021-12-29T07:21:52.203

A data.table approach (with turning the data table back into a dataframe):

library(data.table)

as.data.frame(setDT(old_data)[new_data, var1 := .(i.var1), on = "id"])

Output

An alternative tidyverse option using rows_update. You can filter new_data to only have ids that appear in old_data. Then, you can update those values, like you had previously tried. Essentially, new_data must only have id values that appear in old_data.

library(tidyverse)

old_data %>% 
  rows_update(., new_data %>% filter(id %in% old_data$id), by = "id")

Data

old_data <-
  structure(list(id = c(1, 2, 3), var1 = c(11, 12, 13)),
            class = "data.frame",
            row.names = c(NA,-3L))
new_data <-
  structure(list(id = c(4, 2, 5), var1 = c(11, 15, 13)),
            class = "data.frame",
            row.names = c(NA,-3L))

score 1 · Answer 4 · answered Dec 29 '21 at 08:42

We can use dplyr::rows_update if we first use a semi_join on new_data to filter only those ids that are included in old_data.

library(dplyr)

old_data %>% 
  rows_update(new_data %>%
                semi_join(old_data, by = "id"),
              by = "id")

#>   id var1
#> 1  1   11
#> 2  2   15
#> 3  3   13

^{Created on 2021-12-29 by the reprex package (v0.3.0)}

Updating Values in New Data with Old Data

4 Answers4