Lookup previous year value ( ID and Year )

hi freinds :slight_smile:

how can i lookup a value based on two things : ID and last year : (t+1 )

i tried to use the merge but it didn't work with me , i couldnt figure out how to link it with 2 columns and previous year value .

image

ID Year RR RR t+1
A.N 2020 -0.021371138 0.005854115
A.N 2019 0.005854115 -0.091502492
A.N 2018 -0.091502492 -0.026302284
A.N 2017 -0.026302284 -0.044257254
A.N 2016 -0.044257254 -0.085159501
A.N 2015 -0.085159501 -0.015160022
A.N 2014 -0.015160022 NA
AAON 2020 -0.134092035 -0.143554745
AAON 2019 -0.143554745 -0.042209718
AAON 2018 -0.042209718 -0.01623202
AAON 2017 -0.01623202 -0.045294476
AAON 2016 -0.045294476 -0.04241455
AAON 2015 -0.04241455 -0.043445164
AAON 2014 -0.043445164 NA

Thank you

dplyr/tidyverse lets you group_by your mutates; and has lag and lead functions; what you describe is a lead


(in_1 <- data.frame(id=rep(letters[1:2],each=6),
           row=rep(1:6,2),
           val = c((1:6)^2,(1:6)^3)))

library(tidyverse)

group_by(in_1,
         id) |> mutate(lead_val = lead(val))
# Groups:   id [2]
   id      row   val lead_val
   <chr> <int> <dbl>    <dbl>
 1 a         1     1        4
 2 a         2     4        9
 3 a         3     9       16
 4 a         4    16       25
 5 a         5    25       36
 6 a         6    36       NA
 7 b         1     1        8
 8 b         2     8       27
 9 b         3    27       64
10 b         4    64      125
11 b         5   125      216
12 b         6   216       NA
1 Like

You can use the dplyr library in R to accomplish this task. The first step would be to create a new dataframe that includes the ID, Year, and RR columns. Next, you can use the lead() function to create a new column that shows the next value of RR. Finally, you can use the filter() function to select only the rows where the Year column is equal to (t+1) and the ID column matches the desired ID. Here is an example code snippet that demonstrates this process:

library(dplyr)

# Create new dataframe with only ID, Year, and RR columns
df_subset <- df %>% select(ID, Year, RR)

# Add a new column showing the next value of RR
df_subset <- df_subset %>% mutate(RR_next = lead(RR))

# Filter dataframe to select rows where Year is (t+1) and ID matches desired value
df_subset_filtered <- df_subset %>% filter(Year == (t+1) & ID == "A.N")

You can then use the filtered dataframe to perform your desired analysis.

1 Like

thank you so much , but I run the same code which you sent me , i got the below error :

(in_1 <- data.frame(id=rep(letters[1:2],each=6),
+                     row=rep(1:6,2),
+                     val = c((1:6)^2,(1:6)^3)))
   id row val
1   a   1   1
2   a   2   4
3   a   3   9
4   a   4  16
5   a   5  25
6   a   6  36
7   b   1   1
8   b   2   8
9   b   3  27
10  b   4  64
11  b   5 125
12  b   6 216
> group_by(in_1, id) |> mutate(lead_val = lead(val))
Error in `mutate()`:
! Problem while
  computing `lead_val =
  lead(val)`.
ℹ The error occurred in group
  1: id = "a".
Caused by error in `UseMethod()`:
! no applicable method for 'lead' applied to an object of class "c('double', 'numeric')"
Run `rlang::last_error()` to see where the error occurred.

probably another package you loaded is causing a different lead to attempt to run.

group_by(in_1, id) |> mutate(lead_val = dplyr::lead(val))

if this works and therefore the name clash is evidenced; you can find out where the clash is by

getAnywhere(lead)

does anything aside from dplyr show up?

1 Like

now its working perfectly ..
thank you soo much :slight_smile:
have a wonderful day