Subset stable IDs based on the largest integer value after the decimal point

user_13188556 0
Viewed 62

I have a dataframe of old ensembl stable transcript IDs that I am in the process of mapping to current genes. Ensembl stable transcript IDs are a combination of characters and integers. As new information comes out they may choose to update the version number, which is the number after the decimal. I want to subset this dataframe further so that it only includes the most recent (largest) version number.

Here is some example data:

stableID release
ENSMUST00000080572.10 80
ENSMUST00000080572.11 81
ENSMUST00000080572.12 82
ENSMUST00000071062.6 80
ENSMUST00000071062.7 81
ENSMUST00000071062.8 82
ENSMUST00000124232.1 62
ENSMUST00000124232.1 64

Here is what I would like to return through some code:

stableID release
ENSMUST00000080572.12 82
ENSMUST00000071062.8 82
ENSMUST00000124232.1 62
ENSMUST00000124232.1 64

I haven't the slightest clue of what to write for this. Current thought is to split the decimal into a third column and then some dplyr::group_by() %>% dplyr::filter() shenanigans perhaps?

dplyr filtering dataframe r string
3 Answers

You can divide the stableID column into two to divide the ID and version number information. For each ID keep rows that have max version number and combine ID and version number information again using unite

library(dplyr)
library(tidyr)

df %>%
  separate(stableID, c('ID', 'version_num'), sep = '\\.', convert = TRUE) %>%
  group_by(ID) %>%
  filter(version_num == max(version_num)) %>%
  ungroup %>%
  unite(stableID, ID, version_num, sep = '.')

# stableID              release
#  <chr>                   <int>
#1 ENSMUST00000080572.12      82
#2 ENSMUST00000071062.8       82
#3 ENSMUST00000124232.1       62
#4 ENSMUST00000124232.1       64

data

df <- structure(list(stableID = c("ENSMUST00000080572.10", "ENSMUST00000080572.11", 
"ENSMUST00000080572.12", "ENSMUST00000071062.6", "ENSMUST00000071062.7", 
"ENSMUST00000071062.8", "ENSMUST00000124232.1", "ENSMUST00000124232.1"
), release = c(80L, 81L, 82L, 80L, 81L, 82L, 62L, 64L)), row.names = c(NA, -8L), class = "data.frame")
user_39629140

Using base R with ave and transform. Read the 'stableID' column with read.table into two column, subset the rows based on the max value of 'V2'

subset(transform(read.table(text = df$stableID, sep=".", header = FALSE), 
    release = df$release), ave(V2, V1, FUN = max) == V2)
                  V1 V2 release
3 ENSMUST00000080572 12      82
6 ENSMUST00000071062  8      82
7 ENSMUST00000124232  1      62
8 ENSMUST00000124232  1      64

data

df <- structure(list(stableID = c("ENSMUST00000080572.10", "ENSMUST00000080572.11", 
"ENSMUST00000080572.12", "ENSMUST00000071062.6", "ENSMUST00000071062.7", 
"ENSMUST00000071062.8", "ENSMUST00000124232.1", "ENSMUST00000124232.1"
), release = c(80L, 81L, 82L, 80L, 81L, 82L, 62L, 64L)), row.names = c(NA, 
-8L), class = "data.frame")
user_37322710

Another base R option using subset + ave

subset(
    df,
    !!ave(
        as.numeric(gsub(".*\\.", "", stableID)),
        gsub("\\..*", "", stableID),
        FUN = function(x) x == max(x)
    )
)

gives

               stableID release
3 ENSMUST00000080572.12      82
6  ENSMUST00000071062.8      82
7  ENSMUST00000124232.1      62
8  ENSMUST00000124232.1      64
user_121587570
Related