Convert value of matrix to 1 or 0 based on the conditions of the second matrix-CodePudding

I have two matrices you can see below:
The first matrix is my actual data collection field. just last column (Group) is the number of group classification of a sample (for example samples 1 & 3 belong to group 1), and other columns are the value of species. I want to convert the value of species in each sample to number one if it has the conditions of the second matrix and if it does not have the conditions of the second matrix convert to zero.

matrix (1)     
   A  B  C  D      Group  
1  5  1  6  1        1  
2  4  4  5  8        2   
3  1  4  3  4        1   
4  0  2  7  5        3    
5  8  4  3  1        3

matrix (2)
     Group1     Group2     Group3     
A      1          0           0
B      1          1           0
C      0          0           1
D      1          1           1

The new matrix I want to have is as follows

new matrix
   A  B  C  D   
1  1  1  0  1     
2  0  1  0  1     
3  1  1  0  1     
4  0  0  1  1     
5  0  0  1  1

To better understand the new matrix, let me give an example:
For example, since species A is present only in group 1 (1 is present and 0 is absent in matrix (2)), its value became 1 in samples 1 and 3 and 0 in other samples, or for species D, because it was present in all groups, its value for all samples was one.

CodePudding user response：

df1%>%
  rownames_to_column('ID')%>%
  pivot_longer(-c(ID, Group), names_to = 'rn', values_to = 'val1') %>%
  left_join(df2%>%
  rownames_to_column('rn') %>%
  pivot_longer(-rn, names_pattern = '(\\d)$',
               names_to = 'Group',
               names_transform = list(Group = as.integer)))%>%
  pivot_wider(ID, names_from = 'rn', values_from = 'value')

# A tibble: 5 x 5
  ID        A     B     C     D
  <chr> <int> <int> <int> <int>
1 1         1     1     0     1
2 2         0     1     0     1
3 3         1     1     0     1
4 4         0     0     1     1
5 5         0     0     1     1

data

df1 <- structure(list(A = c(5L, 4L, 1L, 0L, 8L), B = c(1L, 4L, 4L, 2L, 
4L), C = c(6L, 5L, 3L, 7L, 3L), D = c(1L, 8L, 4L, 5L, 1L), Group = c(1L, 
2L, 1L, 3L, 3L)), class = "data.frame", row.names = c("1", "2", 
"3", "4", "5"))

df2 <- structure(list(Group1 = c(1L, 1L, 0L, 1L), Group2 = c(0L, 1L, 
0L, 1L), Group3 = c(0L, 0L, 1L, 1L)), class = "data.frame", row.names = c("A", 
"B", "C", "D"))

CodePudding user response：

Here is another approach:

df2mask <- t(df2)[df1$Group, ]
df2mask
#        A B C D
# Group1 1 1 0 1
# Group2 0 1 0 1
# Group1 1 1 0 1
# Group3 0 0 1 1
# Group3 0 0 1 1

dfnew <- ifelse(df1[, -5] * df2mask > 0, 1, 0)
dfnew
#   A B C D
# 1 1 1 0 1
# 2 0 1 0 1
# 3 1 1 0 1
# 4 0 0 1 1
# 5 0 0 1 1

Note that the mask and the final matrix are identical because your example does not contain any cell where the species is absent but the mask value is 1.