transposing a dataframe with repeats

0
votes

I have a data frame that has two columns, one for gene symbols, and another for functional pathways. The pathways column has repeated values as there are a number of genes that belong with each pathway. I would like to reorder this dataset so that each column is a single pathway and each row in those columns is a gene that belongs in that pathway.

Starting dataframe:

data.frame(pathway = c("p1", "p1", "p1", "p1", "p2", "p2", "p2"), 
gene.symbol = c("G1", "G2", "G3", "G4", "G33", "G43", "G10"))

Desired dataframe:

data.frame(p1 = c("G1", "G2", "G3", "G4"), p2 = c("G33", "G43", "G10", 
""))

I know that not all columns will be the same length, and having blank values is preferable to NAs.

2
r
Since the columns won't have the same length, you're really better off creating a standard list rather than a data.frame, particularly since row 1, column 1 has nothing to do with row 1, column 2. - Nick Nimchuk

2 Answers

0
votes

Here is another option.

  1. Split into list using pathway as splitting element
  2. Get max length of each group, and set all other groups to the same length
  3. Turn it back into a data frame

Here is the code.

mydf <- data.frame(pathway = c("p1", "p1", "p1", "p1", "p2", "p2", "p2"), 
           gene.symbol = c("G1", "G2", "G3", "G4", "G33", "G43", "G10"))

# function to run over each element in list
set_to_max_length <- function(x) {
  length(x) <- max.length
  return(x)
}

# 1. split into  list
mydf.split <- split(mydf$gene.symbol, mydf$pathway)

# 2.a get max length of all columns
max.length <- max(sapply(mydf.split, length))

# 2.b set each list element to max length
mydf.split.2 <- lapply(mydf.split, set_to_max_length)

# 3. combine back into df
data.frame(mydf.split.2)

EDIT

Here is another option using the tidyverse - somewhat more succinct:

library(tidyverse)
mydf <- data.frame(pathway = c("p1", "p1", "p1", "p1", "p2", "p2", "p2"), 
                   gene.symbol = c("G1", "G2", "G3", "G4", "G33", "G43", "G10"))

mydf %>% 
  group_by(pathway) %>% 
  mutate(rownum = row_number()) %>% 
  ungroup() %>% 
  spread(pathway, gene.symbol) %>% 
  select(-1)
0
votes

This might seem a bit convoluted but it achieves the desired output by first going to lists than back to data.frame:

df$gene.symbol <- as.character(df$gene.symbol)

pw_list <- list()
for (pw in unique(df$pathway)) {
  pw_list[[pw]] <- df[df$pathway == pw, "gene.symbol"]
}
pw_list
$p1
[1] "G1" "G2" "G3" "G4"

$p2
[1] "G33" "G43" "G10"


reordered <- matrix("", nrow = max(sapply(pw_list, length)), ncol = length(pw_list))
colnames(reordered) <- names(pw_list)

for (pw in names(pw_list)){
  n <- length(pw_list[[pw]])
  reordered[1:n, pw] <- pw_list[[pw]]
}
reordered <- as.data.frame(reordered)
reordered
  p1  p2
1 G1 G33
2 G2 G43
3 G3 G10
4 G4

EDIT

A slightly more succinct version:

df$gene.symbol <- as.character(df$gene.symbol)
pw_list <- list()
for (pw in unique(df$pathway)) {
  pw_list[[pw]] <- df[df$pathway == pw, "gene.symbol"]
}
reordered <- as.data.frame(sapply(pw_list, "[", i = 1:max(sapply(pw_list, length))), 
                           stringsAsFactors = FALSE)
reordered[is.na(reordered)] <- ""
names(reordered) <- names(pw_list)