CODEWITHSUNDEEP
rdataframedplyr
Alex Carr
Alex Carr

Reputation: 13

Update existing rows and insert new rows of an old data frame with new data

Suppose I start with an old data frame:

 date        positivity  upper  lower 
 2022-08-01  10          12     7     
 2022-08-02  12          14     9     
 2022-08-03  13          15     11    
 2022-08-04  15          17     14    
 2022-08-05  17          20     16

And then I have a new data frame which slightly overlaps the old one:

 date        positivity  upper  lower 
 2022-08-04  16          18     10    
 2022-08-05  19          22     15    
 2022-08-06  22          24     19    
 2022-08-07  25          29     20    
 2022-08-08  26          30     22

How do I use R to both update the old data frame with existing/overlapping rows in the new data frame (2022-08-04 & 2022-08-05) and also insert the new rows of data?

I have tried dplyr::rows_update() but this will only take data that exists in both data frames.

The data frames are much larger in reality but the overlap is always around 36 rows in length. Each week I need to be able to update the old dataset with new data.

Upvotes: 1

Views: 306

Answers (1)

Darren Tsai
Darren Tsai

Reputation: 35554

You could use rows_upsert from dplyr. It inserts or updates depending on whether or not the key value in y already exists in x.

library(dplyr)

rows_upsert(df1, df2, by = "date")

#         date positivity upper lower
# 1 2022-08-01         10    12     7
# 2 2022-08-02         12    14     9
# 3 2022-08-03         13    15    11
# 4 2022-08-04         16    18    10
# 5 2022-08-05         19    22    15
# 6 2022-08-06         22    24    19
# 7 2022-08-07         25    29    20
# 8 2022-08-08         26    30    22
Data
df1 <- read.table(text = " date        positivity  upper  lower 
 2022-08-01  10          12     7     
 2022-08-02  12          14     9     
 2022-08-03  13          15     11    
 2022-08-04  15          17     14    
 2022-08-05  17          20     16", header = TRUE)

df2 <- read.table(text = " date        positivity  upper  lower 
 2022-08-04  16          18     10    
 2022-08-05  19          22     15    
 2022-08-06  22          24     19    
 2022-08-07  25          29     20    
 2022-08-08  26          30     22", header = TRUE)

Upvotes: 1

Related Questions