What is the best way to programmatically convert Ensembl ids from one release to other? e.g. transcript ids from release 75 to 100

Question

3 solutions that I had to unfortunately rule out.

Potential solution #1

As far as I know, the conventional way is to do this is by using CCDS ids (e.g. using biomart). However, the issue is that, as I see on the CCDS project page, the last release of CCDS ids was made almost 3 years ago (!) and it seems to be compatible with Ensembl release 92. Then how should one convert ids to the latest Ensembl release e.g. 100?

CCDS Release	Species	NCBI Annotation Release	Ensembl Annotation Release	Assembly Name	Assembly ID	Made Public
22	Homo sapiens	109	92	GRCh38.p12	GCF_000001405.38	06/14/2018

Potential solution #2

I am also aware of Ensembl's ID History Converter. However, I am looking for a programmatic approach. Also, I noticed that if I try to convert ~>20K ids with ID History Converter, in my experience, there is always some issue. e.g. an error I got when I tried to convert ~70K ensembl transcript ids.

As a side note, I wonder why this ID conversion functionality is not available via Ensembl's REST API. To me, id conversion seems to be a basic enough task that almost every bioinformatician has to do. So it would be immensely helpful to be someday able to do this programmatically using Ensembl's mighty a REST API.

Potential solution #3

Finally, as another potential solution, over at Ensembl's FTP website, located within the directory of the release to which I am interested in converting my ids to (release 100, target release), I saw that according to a README file, "ensembl_archive_" directory should contain "Data on historical Ensembl releases". So, I wondered if this directory contains a map between ids from different releases. However, without any extra info about column names, contents, file formats etc, I couldn't really figure out how to use those files. So, I had to rule this one out too.

Index of /pub/release-100/mysql/ensembl_archive_100/
../
CHECKSUMS                                          26-Mar-2020 14:31                 132
ens_release.txt.gz                                 26-Mar-2020 14:31                1098
ensembl_archive_100.sql.gz                         26-Mar-2020 14:31                 943
release_species.txt.gz                             26-Mar-2020 14:31               39620
species.txt.gz                                     26-Mar-2020 14:31                6669

Of note, I saw a similar "mysql" directory over at the directory of the release whose ids I want to convert (source release): http://ftp.ensembl.org/pub/grch37/release-100/mysql/homo_sapiens_core_100_37/ . However, again, I do not understand its contents without any extra information.

So, finally, I am not sure what is the best way to programmatically convert Ensembl ids from between different releases. As a test case scenario, I want to convert all the protein_coding transcript ids from GRCh37 (release 75) to GRCh38 (release 100). If anybody has any suggestions about this, please kindly let me know.

Answer 1

The ID history converter is available as a Perl script.

This script accesses these MySQL tables, so you could query these tables directly.

Note that the mapping session is only one release at a time, so you would need to run 25 queries to go from release 75 to 100.

Answer 2

Here's one way to do it programmatically using the biomaRt package (your potential solution #1):

library(biomaRt)
library(dplyr)

mart37 <- useMart(biomart = "ENSEMBL_MART_ENSEMBL",
                  host    = "grch37.ensembl.org",
                  path    = "/biomart/martservice",
                  dataset = "hsapiens_gene_ensembl")

mart38 <- useMart(biomart = "ENSEMBL_MART_ENSEMBL",
                  path    = "/biomart/martservice",
                  dataset = "hsapiens_gene_ensembl")

#pick all genes on chr22 from build 37 as a test dataset::
out37 <- getBM(attributes = c('hgnc_symbol', 'chromosome_name',
                              'ensembl_gene_id', 'ensembl_transcript_id', 'ccds'),
                  filters = c('chromosome_name'),
                  values = list("22"),
                  mart = mart37) %>% #empty string CCDS ID's
  filter(ccds != "")

#NOTE:: to check which filters can be used to query mart37, run this command:
#View(listFilters(mart37))

out38 <- getBM(attributes = c('hgnc_symbol', 'chromosome_name',
                              'ensembl_gene_id', 'ensembl_transcript_id', 'ccds'),
                        filters = c('ccds'),
                        values = list(out37$ccds),
                        mart = mart38)

What this does is query the build 38 DB via the CCDS ID's of build 37. You can include other variables in this out38 dataframe by simply adding to the attributes argument above. And in order to see all of the potential variables that can be included, you just have to run View(listFilters(mart37)) or View(listFilters(mart38)), depending on which build you are interested in.

Does this solve your problem?