# Lookup previous year value ( ID and Year )

hi freinds 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 . 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,
``````
``````# Groups:   id 
<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
Error in `mutate()`:
! Problem while
ℹ 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 have a wonderful day

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.