Modifying and splitting row names in an R data frame

user_13990552 0
Viewed 154

I've got the following table with expression data for some genes:

> exp_table
                                                exp
PITG_00005b                              0.16442671
PITG_00005a                              0.94545358
PITG_00004                               0.86324023
PITG_00003                               0.04110668
PITG_00002                               1.10988029
MERGED:PITG_23067_PITG_23068_PITG_16110 34.11854242
MERGED:PITG_23017_PITG_23018             0.00000000

What I'm trying to do is to clean this table up, so that gene names are split as with the following code I used for other purposes:

> exp_names <- gsub("[a-e]", "", rownames(exp_table))
> exp_names <- gsub("MERGED:", "", exp_names)
> exp_names <- strtrim(unlist(strsplit(exp_names, "(?<=[0-9]_)", perl=TRUE)), 10)
> exp_names
 [1] "PITG_00005" "PITG_00005" "PITG_00004" "PITG_00003" "PITG_00002" "PITG_23067"
 [7] "PITG_23068" "PITG_16110" "PITG_23017" "PITG_23018"

i.e., in the data frame I need: if there is an additional letter at the end of the gene (a or b) it should be removed and all gene 'copies' should be assigned an average expression level (here, both 'copies' of PITG_00005 should be assigned exp = (0.16442671 + 0.94545358)/2),
all the genes that were previously merged should have the same expression level assigned (i.e., 34.11854242 for all of "PITG_23067", "PITG_23068", "PITG_16110").

I would appreciate any suggestions!

bioinformatics r
2 Answers

You could do the string manipulations in an lapply. This gives you a list format that can be exploited with the length information to repeat the values.

rn <- rownames(exp_table)
rn <- gsub("MERGED:", "", rn, fixed=T)
rn <- unlist(lapply(rn, strsplit, "(?<=[0-9]_)", perl=TRUE), recursive=F)
rn <- lapply(rn, strtrim, 10)
(tmp <- unlist(mapply(function(x, y) 
  setNames(rep(exp_table[x, 1], length(y)), y), 1:nrow(exp_table), rn)))
# PITG_00005  PITG_00005  PITG_00004  PITG_00003  PITG_00002 
# 0.16442671  0.94545358  0.86324023  0.04110668  1.10988029 
#  PITG_23067  PITG_23068  PITG_16110  PITG_23017  PITG_23018 
# 34.11854242 34.11854242 34.11854242  0.00000000  0.00000000

The vector can be aggregated using the mean to get the final result.

a <- aggregate(tmp ~ nm, data.frame(res, nm=names(tmp)), mean)
res <- `rownames<-`(a[, 2, F], a$nm)
res
#                    res
# PITG_00002  1.10988029
# PITG_00003  0.04110668
# PITG_00004  0.86324023
# PITG_00005  0.55494014
# PITG_16110 34.11854242
# PITG_23017  0.00000000
# PITG_23018  0.00000000
# PITG_23067 34.11854242
# PITG_23068 34.11854242

Data:

exp_table <- structure(list(exp = c(0.16442671, 0.94545358, 0.86324023, 0.04110668, 
1.10988029, 34.11854242, 0)), class = "data.frame", row.names = c("PITG_00005b", 
"PITG_00005a", "PITG_00004", "PITG_00003", "PITG_00002", "MERGED:PITG_23067_PITG_23068_PITG_16110", 
"MERGED:PITG_23017_PITG_23018"))
user_65740380

Try this:

# example data
exp_table <- read.table(text = " exp
PITG_00005b                              0.16442671
PITG_00005a                              0.94545358
PITG_00004                               0.86324023
PITG_00003                               0.04110668
PITG_00002                               1.10988029
MERGED:PITG_23067_PITG_23068_PITG_16110 34.11854242
MERGED:PITG_23017_PITG_23018             0.00000000")

Extending on your regex steps to get IDs, I am creating a look-up dataframe:

exp_names <- gsub("[a-e]", "", rownames(exp_table))
exp_names <- gsub("MERGED:", "", exp_names)
exp_names <- stack(
  setNames(
    lapply(strsplit(exp_names, "(?<=[0-9]_)", perl = TRUE), strtrim, width = 10),
    rownames(exp_table)))

Then merge and get mean when the is ID is not unique:

res <- merge(exp_names, exp_table, by.x = "ind", by.y = 0)
aggregate(exp ~ values, res, mean)
#       values         exp
# 1 PITG_00002  1.10988029
# 2 PITG_00003  0.04110668
# 3 PITG_00004  0.86324023
# 4 PITG_00005  0.55494014
# 5 PITG_16110 34.11854242
# 6 PITG_23017  0.00000000
# 7 PITG_23018  0.00000000
# 8 PITG_23067 34.11854242
# 9 PITG_23068 34.11854242
user_6800680
Related