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

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.

CodePudding user response：

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)

CodePudding user response：

Have you checked out rows_upsert? Its experimental but should work.

Try rows_upsert(data_frame_1, data_frame_2)