Updating one table with second table with dplyr

Question

I have two data frames. The first one is named first_df and the second one is named second_df. Now, I want to update the data from the second data frame to the first one.

 # First data frame
library(dplyr)
    first_df <- data.frame(
                        ShortName = c("A", "B", "C"),
                        t1 = c(0, 0, 0 ),
                        t2 = c(1, 1, 1),
                        t3 = c(2, 2, 2),
                        t4 = c(3, 3, 3)
                      )

 # Second data frame
    second_df <- data.frame(
                    ShortName = c("A", "B", "C"),
                    Year = c(2024, 2025, 2026 ),
                    Value=c(0, 5, 10)
                  )
    

The update should be in such a way that columns t1, t2, t3, and t4 correspond to the years 2024, 2025, 2026, and 2027.If the year in the value parameter is 2026, it means that the value will be applied from 2026 onwards, which includes the year 2027 as well.

In the end, the table should look like the table shown in the following image.

Can someone help me to do this with dplyr ?

Answer 1

I may have misunderstood, but here is one potential solution:

library(dplyr)
library(tidyr)

first_df <- data.frame(
  ShortName = c("A", "B", "C"),
  t1 = c(0, 0, 0 ),
  t2 = c(1, 1, 1),
  t3 = c(2, 2, 2),
  t4 = c(3, 3, 3)
)

second_df <- data.frame(
  ShortName = c("A", "B", "C"),
  Year = c(2024, 2025, 2026 ),
  Value=c(0, 5, 10)
)

first_df %>%
  pivot_longer(-ShortName,
               names_to = "timepoint",
               values_to = "original_value") %>%
  mutate(original_year = 2023 + row_number(), .by = ShortName) %>% 
  left_join(second_df, by = join_by(ShortName)) %>%
  mutate(amended_value = ifelse(original_year < Year, original_value, Value)) %>%
  pivot_wider(id_cols = ShortName,
              names_from = timepoint,
              values_from = amended_value)
#> # A tibble: 3 × 5
#>   ShortName    t1    t2    t3    t4
#>   <chr>     <dbl> <dbl> <dbl> <dbl>
#> 1 A             0     0     0     0
#> 2 B             0     5     5     5
#> 3 C             0     1    10    10

^{Created on 2024-05-13 with reprex v2.1.0}