loop through dataframe to extract specific pairs with value more than threshold in R

user_7186557 0
Viewed 61

I have a data frame of z-scores between various gene pairs. The dataframe looks like this:

df <- data.frame(geneID=c("CDKN2A", "JUN", "IRS2","MTOR",
                            "NRAS"),
                  ABL1=c(-0.19,NA,2.01,0.4,1.23),
                  AKT1=c(0.11,2.45,NA,NA,1.67),
                  AKT2=c(1.19,NA,2.41,0.78,1.93),
                  AKT3=c(2.78,NA,NA,0.7,2.23),
                  ALK=c(NA,NA,NA,2.4,1.23))

I would like to filter z-scores higher than 2 and output them into separate dataframe in the following format:

gene1   gene2 z-score
IRS2    ABL1  2.01
JUN     AKT1  2.45
CDKN2A  AKT3  2,78
NRAS    AKT3  2.23
MTOR    ALK   2.4

I tried to filtered out rows and columns, but it turns out that I lose some of values that meet the threshold of 2. The dimensionality of my table is 18215 rows and 270 columns.

I would highly appreciate any help or suggestions!

Thank you very much! Olha

dataframe r loops
4 Answers

Here is another base R option

inds <- which(df[-1] > 2, arr.ind = TRUE)
dfout <- data.frame(
  gene1 = df$geneID[inds[, "row"]],
  gene2 = names(df[-1])[inds[, "col"]],
  z_score = df[-1][inds]
)

such that

> dfout
   gene1 gene2 z_score
1   IRS2  ABL1    2.01
2    JUN  AKT1    2.45
3   IRS2  AKT2    2.41
4 CDKN2A  AKT3    2.78
5   NRAS  AKT3    2.23
6   MTOR   ALK    2.40
user_121587570

We can convert to 'long' format first and then do the filter

library(dplyr)
library(tidyr)
df %>%
     rename(gene1 = geneID) %>%
     pivot_longer(cols = -gene1, names_to= 'gene2',
             values_to = 'z_score', values_drop_na = TRUE) %>%
     filter(z_score > 2)
# A tibble: 6 x 3
#  gene1  gene2 z_score
#  <chr>  <chr>   <dbl>
#1 CDKN2A AKT3     2.78
#2 JUN    AKT1     2.45
#3 IRS2   ABL1     2.01
#4 IRS2   AKT2     2.41
#5 MTOR   ALK      2.4 
#6 NRAS   AKT3     2.23

It could also be solved with a base R (assuming we need only the first element with greater than 2)

i1 <- df[-1] > 2 & !is.na(df[-1])
j1 <- max.col(i1)
data.frame(gene1 = df$geneID, gene2 = names(df)[-1][j1],
    z_score = df[-1][cbind(seq_len(nrow(df)), j1)])
#   gene1 gene2 z_score
#1 CDKN2A  AKT3    2.78
#2    JUN  AKT1    2.45
#3   IRS2  ABL1    2.01
#4   MTOR   ALK    2.40
#5   NRAS  AKT3    2.23
user_37322710

in base R you could do:

subset(cbind(df[1],stack(df[-1])), values > 2)

   geneID values  ind
3    IRS2   2.01 ABL1
7     JUN   2.45 AKT1
13   IRS2   2.41 AKT2
16 CDKN2A   2.78 AKT3
20   NRAS   2.23 AKT3
24   MTOR   2.40  ALK
user_83802720

Here is a data.table solution:

library(data.table)
df <- data.table(geneID=c("CDKN2A", "JUN", "IRS2","MTOR",
                            "NRAS"),
                  ABL1=c(-0.19,NA,2.01,0.4,1.23),
                  AKT1=c(0.11,2.45,NA,NA,1.67),
                  AKT2=c(1.19,NA,2.41,0.78,1.93),
                  AKT3=c(2.78,NA,NA,0.7,2.23),
                  ALK=c(NA,NA,NA,2.4,1.23))

df2 <- melt.data.table(df)
df2[, variable := as.character(variable)]
names(df2) <- c("gene1", "gene2", "z_score")
df2[z_score > 2]

Output:


#>     gene1 gene2 z_score
#> 1:   IRS2  ABL1    2.01
#> 2:    JUN  AKT1    2.45
#> 3:   IRS2  AKT2    2.41
#> 4: CDKN2A  AKT3    2.78
#> 5:   NRAS  AKT3    2.23
#> 6:   MTOR   ALK    2.40

Created on 2020-08-07 by the reprex package (v0.3.0)

user_133567300
Related