How to make a matrix from multiple datasets with inconsistent values in each dataset?

Question

I am working with multiple datasets, and am trying to combine them into a matrix, where the each column is a gene name, and the table values are the gene expression data.

The problem is that some of the datasets are missing some genes or have different genes, so I do not have a complete "reference" set of genes.

Dataset 1                   Dataset 2
Gene     expression         Gene     expression
a        0.3                a        0.1
b        0.1                c        -0.3
e        0.2                d        0.05
f        0.2                f        -0.1


Ideal Output:

     a     b     c     d     e     f
1    0.3   0.1   NA    NA    0.2   0.2
2    0.1   NA    -0.3  0.05  NA    -0.1

I was trying to first make a loop that would create the gene columns from the first data frame, and then only add columns if they were not only present in the data frame. From there I was going to make an if-else statement to add the expression data, and NA if not.

I "think" this code works, but the trouble is that it is very slow (currently running still with only a small subset of datasets) because there are 20,000 genes per dataset, and I will eventually be using 100s of datasets.

How can I make a a column for every gene present in any dataset without overlap in a more efficient way?

copynumber_files <- list.files(data_dir)

copynumbers <- data.frame()

for (copynumber_file in copynumber_files){
  copynumber_df <- read.csv(copynumber_file, sep="\t")
  for (i in 1:nrow(copynumber_df)){
    if (!copynumber_df$gene[i] %in% copynumbers){
      copynumbers[copynumber_df$gene[i]] <- list(character(0))
    }
    else {}
    }
}

mikebader mikebader · Accepted Answer · 2021-08-05T19:13:52

Using dplyr and family, you could do the following:

library(dplyr)
library(tidyr)

df1 <- tribble(
    ~Gene, ~expression,
    "a", 0.3, 
    "b", 0.1,
    "e", 0.2, 
    "f", 0.2
)

df2 <- tribble(
    ~Gene, ~expression,
    "a", 0.1,
    "c", -0.3,
    "d", 0.05,
    "f", -0.1
)

df <- bind_rows(
    lapply(seq_along(dflist), function(x) mutate(dflist[[x]], dataset = x))
) %>%
    pivot_wider(id_cols = "dataset", names_from = Gene, values_from = expression)

The lapply function creates a variable in each dataframe called dataset. Since bind_rows can take a list of data frames, the list returned by lapply is bound into a single data frame where the number of rows equals the total number of rows in all of the datasets.

The pivot_longer function rotates the dataset from long to wide, where each gene gets its own column, each dataset its own row, and the cell values equal the expression (with NA where not present).

How to make a matrix from multiple datasets with inconsistent values in each dataset?

1 Answers