How to create a new column in R from data in another data frame?

Below is the sample data. The goal is to create a new column in second (name of the data frame) titled Adjustment that would be the corresponding indcode,area,owership combination from the d2022 column. Below is the first row of the desired result. Any ideas as to how this is done? The end goal is to get this adjustment and then create another new column that first$2022 – first$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)

Desired result

  indcode      area     ownership     d2020    d2021   d2022  adjustment
  482000      000032         50       200        201    204      10

>Solution :

Maybe we could modify the column names of the dataset and do a join

library(dplyr)
library(stringr)
 First %>%
   rename_with(~ str_c(.x, '_adjustment'), matches("^d\\d{4}$")) %>% 
  left_join(second, .)

-output

  indcode   area ownership d2020 d2021 d2022 d2022_adjustment
1   482000 000032        50   200   201   204               10
2   482000 029820        50   199   200   203                6
3   482000 039900        50   198   199   202                2
4   484000 000032        20   197   198   201               NA
5   484000 029820        30   196   197   200               NA
6   484000 039900        50   195   196   199               NA
7   524000 000032        50   194   195   198              100
8   524000 029820        50   193   194   197               60
9   524000 039900        50   192   193   196               20
10  531000 000032        20   191   192   195               NA
11  531000 029820        30   190   191   194               NA
12  531000 039900        50   189   190   193               NA

Leave a Reply