Is it possible to return the values of reference columns to multiple columns in r

user_12964060 0
Viewed 51

I am organizing a large dataset adapted to my research. Suppose that I have 9 observations (records) and 4 columns as follows:

z <- data.frame("fa" = c(1, NA, NA, 2, 1, 1, 2, 1, 1), 
                "fb" = c(2, 2, NA, 1, NA, NA, NA, 1, 2),
                "initial_1" = c("A", "B", "B", "B", "A", "C", "D", "B", "A"),
                "initial_2" = c("D", "C", "C", "A", "B", "A", "A", "D", "D"))

I want to create two new columns, fa_new and fb_new according to the values of the first two columns, fa and fb, which are linked to the reference columns, initial_1 and initial_2, such that fa == # is matching to intial_#.

For example, as can be seen above, the first record of the column fa is 1 which is linked to "A" of intial_1. Thus, the first record of the new column fa_new will be "A". Likewise, the first record of fb is 2 which is linked to "D" of intial_2; thus, the first record of fb_new will be "D".

Accordingly, my expectation is:

  fa_new fb_new
1 A      D     
2 NA     C     
3 NA     NA    
4 A      B     
5 A      NA    
6 C      NA    
7 A      NA    
8 B      B     
9 A      D

Is this possible using r?

r reference
1 Answers

You can use lapply to do this for multiple columns :

cols <- 1:2
init_cols <- paste0('initial_', cols)
new_cols <- paste0(names(z)[cols], '_new')
inds <- 1:nrow(z)
z[new_cols] <- lapply(z[cols], function(x) z[init_cols][cbind(inds, x)])
z
#  fa fb initial_1 initial_2 fa_new fb_new
#1  1  2         A         D      A      D
#2 NA  2         B         C   <NA>      C
#3 NA NA         B         C   <NA>   <NA>
#4  2  1         B         A      A      B
#5  1 NA         A         B      A   <NA>
#6  1 NA         C         A      C   <NA>
#7  2 NA         D         A      A   <NA>
#8  1  1         B         D      B      B
#9  1  2         A         D      A      D

The logic here is we create a matrix with cbind which has row/column number. The row number is inds (1:nrow(z)) whereas column number comes from fa/fb columns which is used to subset z dataframe.

The actual dataframe is labelled dataset, the following answer should work on the real data.

cols <- 1:2
init_cols <- paste0('fuinitials_', 1:94)
new_cols <- paste0(names(z)[cols], '_new')
inds <- 1:nrow(z)

z1 <- data.frame(z)
z1[cols][z1[cols] < 1] <- NA
z1[new_cols] <- lapply(z1[cols], function(x) z1[init_cols][cbind(inds, x)])
user_39629140
Related