The code below shows how I would do this. File1 has four rows of data and File2 has retest values for two of those rows. The idea is to pivot the two data files to a "long" format so there is one column listing the data point and one column showing the value. I then join the data sets. All of the rows from File1 are kept. Where there is a corresponding value from File2, that appears in the same row and an NA is shown if File2 did not have a value. The ifelse() function within the mutate() replaces the File1 values if the File2 value is not NA. I then drop the columns from File2 and pivot the data back to the wide format. The last pivot may not be needed. A lot of data analysis in R is easier in the long format but you will have to decide what works for your situation.
The code shows the steps after the initial pivot twice. The first is a verbose version that prints all of the intermediate steps. The second does the process in one chain of function calls.
library(dplyr)
library(tidyr)
library(tibble)
File1 <- tibble(date = as.Date("2022-04-26"),
lot = c("A", "A", "B", "B"),
site = c("S1", "S2", "S1", "S2"),
Data1 = c(1,2,3,4), Data2 = c(2,3,4,5), Data3 = c(3,4,5,6))
File1
#> # A tibble: 4 × 6
#> date lot site Data1 Data2 Data3
#> <date> <chr> <chr> <dbl> <dbl> <dbl>
#> 1 2022-04-26 A S1 1 2 3
#> 2 2022-04-26 A S2 2 3 4
#> 3 2022-04-26 B S1 3 4 5
#> 4 2022-04-26 B S2 4 5 6
File2 <- tibble(date = as.Date("2022-04-27"),
lot = c("A", "B"),
site = c("S2", "S1"),
Data1 = c(2.1,3.1), Data2 = c(3.1,4.1), Data3 = c(4.1,5.1))
File2
#> # A tibble: 2 × 6
#> date lot site Data1 Data2 Data3
#> <date> <chr> <chr> <dbl> <dbl> <dbl>
#> 1 2022-04-27 A S2 2.1 3.1 4.1
#> 2 2022-04-27 B S1 3.1 4.1 5.1
File1Long <- pivot_longer(data = File1, cols = -c("date", "lot", "site"), names_to = "DataPt")
File1Long
#> # A tibble: 12 × 5
#> date lot site DataPt value
#> <date> <chr> <chr> <chr> <dbl>
#> 1 2022-04-26 A S1 Data1 1
#> 2 2022-04-26 A S1 Data2 2
#> 3 2022-04-26 A S1 Data3 3
#> 4 2022-04-26 A S2 Data1 2
#> 5 2022-04-26 A S2 Data2 3
#> 6 2022-04-26 A S2 Data3 4
#> 7 2022-04-26 B S1 Data1 3
#> 8 2022-04-26 B S1 Data2 4
#> 9 2022-04-26 B S1 Data3 5
#> 10 2022-04-26 B S2 Data1 4
#> 11 2022-04-26 B S2 Data2 5
#> 12 2022-04-26 B S2 Data3 6
File2Long <- pivot_longer(data = File2, cols = -c("date", "lot", "site"), names_to = "DataPt")
#Step by step processing for clarity
FinalData <- left_join(File1Long, File2Long, by = c("lot", "site", "DataPt"), suffix = c(".F1", ".F2"))
FinalData
#> # A tibble: 12 × 7
#> date.F1 lot site DataPt value.F1 date.F2 value.F2
#> <date> <chr> <chr> <chr> <dbl> <date> <dbl>
#> 1 2022-04-26 A S1 Data1 1 NA NA
#> 2 2022-04-26 A S1 Data2 2 NA NA
#> 3 2022-04-26 A S1 Data3 3 NA NA
#> 4 2022-04-26 A S2 Data1 2 2022-04-27 2.1
#> 5 2022-04-26 A S2 Data2 3 2022-04-27 3.1
#> 6 2022-04-26 A S2 Data3 4 2022-04-27 4.1
#> 7 2022-04-26 B S1 Data1 3 2022-04-27 3.1
#> 8 2022-04-26 B S1 Data2 4 2022-04-27 4.1
#> 9 2022-04-26 B S1 Data3 5 2022-04-27 5.1
#> 10 2022-04-26 B S2 Data1 4 NA NA
#> 11 2022-04-26 B S2 Data2 5 NA NA
#> 12 2022-04-26 B S2 Data3 6 NA NA
FinalData <- mutate(.data = FinalData, value.F1 = ifelse(is.na(value.F2), value.F1, value.F2))
FinalData
#> # A tibble: 12 × 7
#> date.F1 lot site DataPt value.F1 date.F2 value.F2
#> <date> <chr> <chr> <chr> <dbl> <date> <dbl>
#> 1 2022-04-26 A S1 Data1 1 NA NA
#> 2 2022-04-26 A S1 Data2 2 NA NA
#> 3 2022-04-26 A S1 Data3 3 NA NA
#> 4 2022-04-26 A S2 Data1 2.1 2022-04-27 2.1
#> 5 2022-04-26 A S2 Data2 3.1 2022-04-27 3.1
#> 6 2022-04-26 A S2 Data3 4.1 2022-04-27 4.1
#> 7 2022-04-26 B S1 Data1 3.1 2022-04-27 3.1
#> 8 2022-04-26 B S1 Data2 4.1 2022-04-27 4.1
#> 9 2022-04-26 B S1 Data3 5.1 2022-04-27 5.1
#> 10 2022-04-26 B S2 Data1 4 NA NA
#> 11 2022-04-26 B S2 Data2 5 NA NA
#> 12 2022-04-26 B S2 Data3 6 NA NA
FinalData <- select(FinalData, date = date.F1, lot, site, DataPt, value = value.F1)
FinalData
#> # A tibble: 12 × 5
#> date lot site DataPt value
#> <date> <chr> <chr> <chr> <dbl>
#> 1 2022-04-26 A S1 Data1 1
#> 2 2022-04-26 A S1 Data2 2
#> 3 2022-04-26 A S1 Data3 3
#> 4 2022-04-26 A S2 Data1 2.1
#> 5 2022-04-26 A S2 Data2 3.1
#> 6 2022-04-26 A S2 Data3 4.1
#> 7 2022-04-26 B S1 Data1 3.1
#> 8 2022-04-26 B S1 Data2 4.1
#> 9 2022-04-26 B S1 Data3 5.1
#> 10 2022-04-26 B S2 Data1 4
#> 11 2022-04-26 B S2 Data2 5
#> 12 2022-04-26 B S2 Data3 6
FinalData <- pivot_wider(FinalData, names_from = DataPt, values_from = value)
FinalData
#> # A tibble: 4 × 6
#> date lot site Data1 Data2 Data3
#> <date> <chr> <chr> <dbl> <dbl> <dbl>
#> 1 2022-04-26 A S1 1 2 3
#> 2 2022-04-26 A S2 2.1 3.1 4.1
#> 3 2022-04-26 B S1 3.1 4.1 5.1
#> 4 2022-04-26 B S2 4 5 6
#Process in one chain without needless print statemements
FinalData2 <- left_join(File1Long, File2Long, by = c("lot", "site", "DataPt"), suffix = c(".F1", ".F2")) %>%
mutate(value.F1 = ifelse(is.na(value.F2), value.F1, value.F2)) %>%
select(date = date.F1, lot, site, DataPt, value = value.F1) %>%
pivot_wider(names_from = DataPt, values_from = value)
#Test that the two process produce the same result
identical(FinalData, FinalData2)
#> [1] TRUE
Created on 2022-04-28 by the reprex package (v0.2.1)