R: Return column names for all matching values in a row

Question

I have a data frame (hits_map) containing a list of genes (rows) against binding sites within each gene (columns). The values indicate how many sites are within each gene, with NA for 0.

Here is a small subset, as the actual data frame is much larger:

         AscG Dan.4 IclR.3 MraZ.1
afaE      NA     1     NA      1
afaF      NA    NA     NA     NA
agn43.1    1    NA      1     NA
agn43.2    1    NA     NA     NA
agn43.3    1    NA     NA     NA
chuA      NA    NA     NA      1
csgA       1    NA     NA      1
csgB      NA    NA     NA     NA
csgC      NA    NA     NA     NA`

For each column I would like to get a list of binding sites/column names which have values in, which I can then use to pull rows from a corresponding data frame, nameseq, to get more info.

Currently I use the following to do this row by row, with a function remove_zero_cols to remove values of 0, but I want to be able to do this for every row just by inputting the data.frame.

vec <- hits_map[row,]
vec <- remove_zero_cols(vec)
vec <- colnames(vec)
nameseq[nameseq$Name %in% vec,]

Any suggestions on how I could go about this?

Answer 1

One way is to convert the data frame to a single vector, row-wise, and create a logical vector off of that based on the value you are looking for, ensuring to convert FALSE to NA. Then create a vector of repeated column names with the same length as the logical vector, subset and reconvert to a matrix:

> set.seed(1)
> DF = data.frame(first = sample(c(NA,1), 5, T), second = sample(c(NA,1), 5, T),
+                 third = sample(c(NA,1), 5, T), fourth = sample(c(NA,1), 5, T),
+                 fifth = sample(c(NA,1), 5, T))
> DF
  first second third fourth fifth
1    NA      1    NA     NA     1
2    NA      1    NA      1    NA
3     1      1     1      1     1
4     1      1    NA     NA    NA
5    NA     NA     1      1    NA
> DFvector = as.vector(t(DF))
> DFvector
 [1] NA  1 NA NA  1 NA  1 NA  1 NA  1  1  1  1  1  1  1 NA NA NA NA NA  1  1 NA
# Create a repeated vector of column names
> columnNames = rep(colnames(DF), times = nrow(DF))
> myNames = columnNames[as.logical(DFvector)]
> myNames[is.na(myNames)] = ""
> myNames
 [1] ""       "second" ""       ""       "fifth"  ""       "second" ""       "fourth" ""       "first" 
[12] "second" "third"  "fourth" "fifth"  "first"  "second" ""       ""       ""       ""       ""      
[23] "third"  "fourth" ""      
# Convert to matrix, by row
myMatrix = matrix(myNames, ncol = ncol(DF), byrow = T)
# Can group per row, by using assertr package
> library(assertr)
> library(stringr)
> concat = assertr::col_concat(myMatrix[], sep = " ")
> concat
[1] " second   fifth"                 " second  fourth "                "first second third fourth fifth"
[4] "first second   "                 "  third fourth "                
> noWS = trimws(concat)
> noWS
[1] "second   fifth"                  "second  fourth"                  "first second third fourth fifth"
[4] "first second"                    "third fourth"                   
> noS = gsub(pattern = "\\s+", replacement = " ", x = noWS)
> noS
[1] "second fifth"                    "second fourth"                   "first second third fourth fifth"
[4] "first second"                    "third fourth"                   
> stringr::str_split(noS, " ", simplify = T)
     [,1]     [,2]     [,3]    [,4]     [,5]   
[1,] "second" "fifth"  ""      ""       ""     
[2,] "second" "fourth" ""      ""       ""     
[3,] "first"  "second" "third" "fourth" "fifth"
[4,] "first"  "second" ""      ""       ""     
[5,] "third"  "fourth" ""      ""       "" 

Now you can use the same rows in your original data frame to get the corresponding column names per row. I hope someone can post a data.table/dplyr alternative, as this is quite tedious if lapplyis to be avoided.