Below is the sample data. Here is what I am trying to do.. I have data
in the first data frame. It is an adjustment. The second data frame is the existing data. For the year, d2022, I need to substract the values from first from the items in second. How would I go about this?
this is the adjustment
indcode <- c(482000,482000,482000,524000,524000,524000)
area <- c(000032,029820,039900,000032,029820,039900)
ownership <- c(50,50,50,50,50,50)
d2022 <- c(10,6,2,100,60,20)
First<-data.frame(indcode,area,ownership,d2022)
## This is the existing data
indcode <- c(482000,482000,482000,484000,484000,484000,524000,524000,524000,531000,531000,531000)
area <- c(000032,029820,039900,000032,029820,039900,000032,029820,039900,000032,029820,039900)
ownership <- c(50,50,50,20,30,50,50,50,50,20,30,50)
d2020 <- c(200,199,198,197,196,195,194,193,192,191,190,189)
d2021 <- c(201,200,199,198,197,196,195,194,193,192,191,190)
d2022 <- c(204,203,202,201,200,199,198,197,196,195,194,193)
second <- data.frame(indcode1,area1,ownership1,d2020,d2021,d2022)
>Solution :
Well this works, but its hard coded explicitly for the year 2022 that you asked about, but I’m guessing you maybe have corrections for other years too?
library(tidyverse)
library(zoo)
second %>%
left_join(First,join_by(indcode, area, ownership)) %>%
mutate(d2022 = d2022.x-na.fill(d2022.y,0)) %>%
select(-matches("\\.[xy]$"))
Data:
> second %>%
+ left_join(First,join_by(indcode, area, ownership)) %>%
+ mutate(d2022 = d2022.x-na.fill(d2022.y,0)) %>%
+ select(-matches("\\.[xy]$"))
+ indcode area ownership d2020 d2021 d2022
1 482000 32 50 200 201 194
2 482000 29820 50 199 200 197
3 482000 39900 50 198 199 200
4 484000 32 20 197 198 201
5 484000 29820 30 196 197 200
6 484000 39900 50 195 196 199
7 524000 32 50 194 195 98
8 524000 29820 50 193 194 137
9 524000 39900 50 192 193 176
10 531000 32 20 191 192 195
11 531000 29820 30 190 191 194
12 531000 39900 50 189 190 193