Transform a data frame according to the data of a matrix

[Delphine]

Hi !

I am trying to organise gigantic tables and get kinda stuck.

I have a dataframe such as :

v1 <- c('D7','X',23)
v2 <- c('D7','Y',56)
v3 <- c('D7','Z',84)
v4 <- c('D8','X',11)
v5 <- c('D8','Y',12)
v6 <- c('D8','Z',35)
book <- data.frame(v1,v2,v3,v4,v5,v6)

and I have the uniqued values of column 1:

lvl <- unique(book[,1])

> lvl
[1] "D7" "D8"

I am able to creat a matrix with the levels (D7, D8 here)

I want to add X, Y and Z as columns, and the values in the matching lines.

I guess I'll need to use some homemade functions, but I'll definitly need help to get started.

Thank you so much!

D

[technocrat]

If I understand the question correctly

b <- matrix(data =
  c('AA','GG','AA','GG','TT','AA',
    'AA','GG','GG','GG','GG','GG',
    'TT','TT','AA','TT','GG','GG',
    'CC','CC','CC','TT','TT','AA',
    'AA','CC','AA','CC','AA','GG',
    'AA','TT','CC','CC','TT','CC'),
  nrow = 6)
  
colnames(b) <- paste0('v',1:6)

the_min <- c('AA', 'GG', 'GG', 'CC', 'AA', 'CC')
the_max <- c('TT','AA','AA','AA','GG','AA')

b
#>      v1   v2   v3   v4   v5   v6  
#> [1,] "AA" "AA" "TT" "CC" "AA" "AA"
#> [2,] "GG" "GG" "TT" "CC" "CC" "TT"
#> [3,] "AA" "GG" "AA" "CC" "AA" "CC"
#> [4,] "GG" "GG" "TT" "TT" "CC" "CC"
#> [5,] "TT" "GG" "GG" "TT" "AA" "TT"
#> [6,] "AA" "GG" "GG" "AA" "GG" "CC"
the_min
#> [1] "AA" "GG" "GG" "CC" "AA" "CC"
the_max
#> [1] "TT" "AA" "AA" "AA" "GG" "AA"

is_min <- function(x) b[x,] == the_min[x]
is_min(1)
#>    v1    v2    v3    v4    v5    v6 
#>  TRUE  TRUE FALSE FALSE  TRUE  TRUE

is_max <- function(x) b[x,] == the_max[x]
is_max(1)
#>    v1    v2    v3    v4    v5    v6 
#> FALSE FALSE  TRUE FALSE FALSE FALSE

holder <- matrix(nrow = 6, ncol = 6)

for(i in 1:6)  holder[i,] = is_min(i) * 1
the_mins <- holder
holder
#>      [,1] [,2] [,3] [,4] [,5] [,6]
#> [1,]    1    1    0    0    1    1
#> [2,]    1    1    0    0    0    0
#> [3,]    0    1    0    0    0    0
#> [4,]    0    0    0    0    1    1
#> [5,]    0    0    0    0    1    0
#> [6,]    0    0    0    0    0    1

holder <- matrix(nrow = 6, ncol = 6)

for(i in 1:6)  holder[i,] = is_max(i) * 1
the_maxs <- holder
holder
#>      [,1] [,2] [,3] [,4] [,5] [,6]
#> [1,]    0    0    1    0    0    0
#> [2,]    0    0    0    0    0    0
#> [3,]    1    0    1    0    1    0
#> [4,]    0    0    0    0    0    0
#> [5,]    0    1    1    0    0    0
#> [6,]    1    0    0    1    0    0

^{Created on 2022-11-18 by the reprex package (v2.0.1)}

If not or if my outline is unclear, please let me know.

[Delphine]

I does work ! I applied it to my giant table with tiny changes, and it is perfect, thank you so much !