Generate new rows and assign values using data from specific parameters in dataframe

I have been wanting to assign values for unknown data using specific parameters. For example, I have land usage data for specific lakes for the year 2004. However, I do not have land use data for these lakes for the years 2002 & 2003. Is there a way to add new rows with land use data for the years 2002-2003 using 2004 data? I will provide a data set for how my data looks like now.

This is what I would like my data frame to look like:

Any help is appreciated. Thanks again!

I do not know where to start so I have not tried anything.

No doubt there are lots of ways to do this. If this is really your use case, then for each row, you could turn year into a list with the values 2002:2004. Then, just unnest() the year list and all the other data will be replicated for all values of year:

library(dplyr)
library(tidyr)
tab <- read.table(header=TRUE, textConnection("Lake year    urban   agriculture wetlands    forest  shrub
A   2004    90  5   1   1   3
B   2004    80  1   4   5   10
C   2004    20  20  20  20  20"))

tab %>% 
  rowwise() %>% 
  mutate(year = list(2002:2004)) %>% 
  unnest(year)
#> # A tibble: 9 × 7
#>   Lake   year urban agriculture wetlands forest shrub
#>   <chr> <int> <int>       <int>    <int>  <int> <int>
#> 1 A      2002    90           5        1      1     3
#> 2 A      2003    90           5        1      1     3
#> 3 A      2004    90           5        1      1     3
#> 4 B      2002    80           1        4      5    10
#> 5 B      2003    80           1        4      5    10
#> 6 B      2004    80           1        4      5    10
#> 7 C      2002    20          20       20     20    20
#> 8 C      2003    20          20       20     20    20
#> 9 C      2004    20          20       20     20    20

^{Created on 2023-06-08 with reprex v2.0.2}

library(dplyr)
library(tidyr)

df |>
  uncount(3, .id = "year") |>
  mutate(year = year + 2001)

This assume a sequence of 3 consecutive years starting after 2001 (e.g. 2002, 2003, 2004).

• uncount duplicates each row 3 times and adds the column year which is just a sequence 1:3 to identify each duplicated row.
• mutate we use to adjust this created year by adding 2001, which transforms 1:3 into 2002:2004.

Output

  Lake year urban agriculture wetlands forest shrub
1    A 2002    90           5        1      1     3
2    A 2003    90           5        1      1     3
3    A 2004    90           5        1      1     3
4    B 2002    80           1        4      5    10
5    B 2003    80           1        4      5    10
6    B 2004    80           1        4      5    10
7    C 2002    20          20       20     20    20
8    C 2003    20          20       20     20    20
9    C 2004    20          20       20     20    20

If you wanted to do non-sequential years you could do something like:

library(dplyr)
library(tidyr)

years <- c(2002, 2004) # edit this for years you want

df |>
  uncount(length(years)) |>
  mutate(year = rep(years, times = nrow(df)))

Data

df <- structure(list(Lake = c("A", "B", "C"), year = c(2004L, 2004L, 
2004L), urban = c(90L, 80L, 20L), agriculture = c(5L, 1L, 20L
), wetlands = c(1L, 4L, 20L), forest = c(1L, 5L, 20L), shrub = c(3L, 
10L, 20L)), class = "data.frame", row.names = c(NA, -3L))