Obtain chromosome, position, and alleles based on a list of SNP names

Question

[jiumeng66]

I have a SNP list (including 3660 SNPs), which have only the name, like rs41457244.

Now I need the other information of the SNPs, such as chromosomes, positions (hg19), and alleles. What can I do?

The following is part of my SNP list:

 rs2088175
 rs2983855
 rs2821958
 rs41469446
 rs619987
 rs2877425
 rs41447048
 rs41497748
 rs503808
 rs386628
 rs6667995
 rs41405345

[Bioinformatics_NewComer]

Try rsnps package in R

https://cran.r-project.org/web/packages/rsnps/rsnps.pdf

[Ram]

Please add an example/more detail or move your suggestion to a comment. Thank you!

[Bioinformatics_NewComer]

Added link for the package tutorial.

[Ram]

IMO it still needs work to qualify as an answer. The package name and package manual link are effectively just a suggestion now. I'm moving this to a comment.

Answer 1

[Pierre Lindenbaum]

$ mysql --user=genome --host=genome-mysql.soe.ucsc.edu -A -P 3306 -D hg38 -e 'select chrom,chromStart,chromEnd,name,alleles,alleleFreqs from snp150 where name in ("rs2088175","rs2983855","rs2821958","rs41469446","rs619987","rs2877425","rs41447048","rs41497748","rs503808","rs386628","rs6667995","rs41405345")'
+---------------------+------------+-----------+------------+---------+--------------------+
| chrom               | chromStart | chromEnd  | name       | alleles | alleleFreqs        |
+---------------------+------------+-----------+------------+---------+--------------------+
| chr3                |   76216220 |  76216221 | rs2088175  | C,G,    | 0.875399,0.124601, |
| chr21               |   13597605 |  13597606 | rs2821958  | A,G,    | 0.884181,0.115819, |
| chr4                |   68990362 |  68990363 | rs2877425  | A,G,    | 0.085463,0.914537, |
| chr4_GL000257v2_alt |     566381 |    566382 | rs2877425  | A,G,    | 0.085463,0.914537, |
| chr10               |   38291051 |  38291052 | rs2983855  | C,T,    | 0.468850,0.531150, |
| chr6                |   28816618 |  28816619 | rs386628   | C,T,    | 0.339257,0.660743, |
| chr6_GL000250v2_alt |      82197 |     82198 | rs386628   | C,T,    | 0.339257,0.660743, |
| chr6_GL000251v2_alt |     307169 |    307170 | rs386628   | C,T,    | 0.339257,0.660743, |
| chr6_GL000252v2_alt |      82220 |     82221 | rs386628   | C,T,    | 0.339257,0.660743, |
| chr6_GL000253v2_alt |      82185 |     82186 | rs386628   | C,T,    | 0.339257,0.660743, |
| chr6_GL000254v2_alt |      82211 |     82212 | rs386628   | C,T,    | 0.339257,0.660743, |
| chr6_GL000255v2_alt |      82201 |     82202 | rs386628   | C,T,    | 0.339257,0.660743, |
| chr6_GL000256v2_alt |     125864 |    125865 | rs386628   | C,T,    | 0.339257,0.660743, |
| chr17               |   22206798 |  22206799 | rs41447048 | C,T,    | 0.833067,0.166933, |
| chr18               |   14208563 |  14208564 | rs41469446 | A,G,    | 0.868011,0.131989, |
| chr1                |   83314732 |  83314733 | rs41497748 | A,T,    | 0.831669,0.168331, |
| chr2                |  126689276 | 126689277 | rs503808   | A,G,    | 0.647364,0.352636, |
| chr1                |  149015643 | 149015644 | rs619987   | A,T,    | 0.869010,0.130990, |
| chr1                |  205854053 | 205854054 | rs6667995  | A,C,    | 0.758586,0.241414, |
+---------------------+------------+-----------+------------+---------+--------------------+

[jiumeng66]

thank you so much. but in my terminal, the command return "-bash: mysql: command not found". Dose it mean that some tool need be installed?

[Pierre Lindenbaum]

Dose it mean that some tool need be installed?

ubuntu:

apt-get install mysql-client

[zx8754]

They have 3660 SNPs. Any way to automate this bit of sql code: ... in ("rs2088175","rs2983855","rs2821958", ... "rs_TheLastSNP")' ?

[Ram]

They can use sed. With an ID-per-line file, replace each line with "\1", then use tr to replace newlines with commas. An additional sed to delete the last comma might be required.

Note: Exact code not provided to encourage learning

[_quantum_girl_]

My output is not exactly that. Somehow I get weird values for alleles and alleleFreqs

+---------------------+------------+-----------+------------+------------------+----------------------------------------+
| chrom               | chromStart | chromEnd  | name       | alleles          | alleleFreqs                            |
+---------------------+------------+-----------+------------+------------------+----------------------------------------+
| chr3                |   76216220 |  76216221 | rs2088175  | 0x432C472C       | 0x302E3837353339392C302E3132343630312C |
| chr21               |   13597605 |  13597606 | rs2821958  | 0x412C472C       | 0x302E3838343138312C302E3131353831392C |
| chr4                |   68990362 |  68990363 | rs2877425  | 0x412C472C       | 0x302E3038353436332C302E3931343533372C |
| chr4_GL000257v2_alt |     566381 |    566382 | rs2877425  | 0x412C472C       | 0x302E3038353436332C302E3931343533372C |
| chr10               |   38291051 |  38291052 | rs2983855  | 0x432C542C       | 0x302E3436383835302C302E3533313135302C |
| chr6_GL000251v2_alt |     307169 |    307170 | rs386628   | 0x432C542C       | 0x302E3333393235372C302E3636303734332C |
| chr6_GL000252v2_alt |      82220 |     82221 | rs386628   | 0x432C542C       | 0x302E3333393235372C302E3636303734332C |
| chr6_GL000253v2_alt |      82185 |     82186 | rs386628   | 0x432C542C       | 0x302E3333393235372C302E3636303734332C |
| chr6_GL000254v2_alt |      82211 |     82212 | rs386628   | 0x432C542C       | 0x302E3333393235372C302E3636303734332C |
| chr6_GL000255v2_alt |      82201 |     82202 | rs386628   | 0x432C542C       | 0x302E3333393235372C302E3636303734332C |
| chr6_GL000256v2_alt |     125864 |    125865 | rs386628   | 0x432C542C       | 0x302E3333393235372C302E3636303734332C |
| chr17               |   22206798 |  22206799 | rs41447048 | 0x432C542C       | 0x302E3833333036372C302E3136363933332C |
| chr18               |   14208563 |  14208564 | rs41469446 | 0x412C472C       | 0x302E3836383031312C302E3133313938392C |
| chr1                |   83314732 |  83314733 | rs41497748 | 0x412C542C       | 0x302E3833313636392C302E3136383333312C |
| chr2                |  126689276 | 126689277 | rs503808   | 0x412C472C       | 0x302E3634373336342C302E3335323633362C |
| chr1                |  149015643 | 149015644 | rs619987   | 0x412C542C       | 0x302E3836393031302C302E3133303939302C |
| chr1                |  205854053 | 205854054 | rs6667995  | 0x412C432C       | 0x302E3735383538362C302E3234313431342C |
+---------------------+------------+-----------+------------+------------------+----------------------------------------+

[Ram]

Show us your command please.

[_quantum_girl_]

I just copied and pasted the same: mysql --user=genome --host=genome-mysql.soe.ucsc.edu -A -P 3306 -D hg38 -e 'select chrom,chromStart,chromEnd,name,alleles,alleleFreqs from snp150 where name in ("rs2088175","rs2983855","rs2821958","rs41469446","rs619987","rs2877425","rs41447048","rs41497748","rs503808","rs386628","rs6667995","rs41405345")'

[Ram]

I think they changed datatypes between 6.3 years ago when this answer was posted and now. Here's what I did:

## See how alleles and alleFreqs are longblob and not text fields
$ mysql --user=genome --host=genome-mysql.soe.ucsc.edu -A -P 3306 -D hg38 -e 'desc snp150' | less -S 
Cmd exec on: Tue Jul 23 10:14:23 CDT 2024
  Field   Type    Null    Key Default Extra
  bin smallint(5) unsigned    NO      NULL
  chrom   varchar(31) NO  MUL NULL
  chromStart  int(10) unsigned    NO      NULL
  chromEnd    int(10) unsigned    NO      NULL
  name    varchar(15) NO  MUL NULL
  score   smallint(5) unsigned    NO      NULL
  strand  enum('+','-')   NO      NULL
  refNCBI blob    NO      NULL
  refUCSC blob    NO      NULL
  observed    varchar(255)    NO      NULL
  molType enum('unknown','genomic','cDNA','mito') NO      NULL
  class   enum('single','in-del','microsatellite','named','mnp','insertion','deletion')   NO      NULL
  valid   set('unknown','by-cluster','by-frequency','by-submitter','by-2hit-2allele','by-hapmap','by-1000genomes')    NO      NULL
  avHet   float   NO      NULL
  avHetSE float   NO      NULL
  func    set('unknown','coding-synon','intron','near-gene-3','near-gene-5','ncRNA','nonsense','missense','stop-loss','frameshift','cds-indel','untranslated-3','untranslated-5','splice-3','splice-5')   NO      NULL
  locType enum('range','exact','between','rangeInsertion','rangeSubstitution','rangeDeletion','fuzzy')    NO      NULL
  weight  int(10) unsigned    NO      NULL
  exceptions  set('RefAlleleMismatch','DuplicateObserved','MixedObserved','FlankMismatchGenomeLonger','FlankMismatchGenomeEqual','FlankMismatchGenomeShorter','NamedDeletionZeroSpan','NamedInsertionNonzeroSpan','SingleClassLongerSpan','SingleClassZeroSpan','SingleClassTriAllelic','SingleClassQuadAllelic','ObservedWrongFormat','ObservedTooLong','ObservedContainsIupac','ObservedMismatch','MultipleAlignments','NonIntegerChromCount','AlleleFreqSumNot1','SingleAlleleFreq','InconsistentAlleles') | NO   |     | NULL    |       |  submitterCount  smallint(5) unsigned    NO      NULL
  submitters  longblob    NO      NULL
  alleleFreqCount smallint(5) unsigned    NO      NULL
  alleles longblob    NO      NULL 
  alleleNs    longblob    NO      NULL
  alleleFreqs longblob    NO      NULL
  bitfields   set('unknown','clinically-assoc','maf-5-some-pop','maf-5-all-pops','has-omim-omia','microattr-tpa','submitted-by-lsdb','genotype-conflict','rs-cluster-nonoverlapping-alleles','observed-mismatch') NO      NULL
$ mysql --user=genome --host=genome-mysql.soe.ucsc.edu -A -P 3306 -D hg38 -e 'select chrom,chromStart,chromEnd,name,CONVERT(alleles USING utf8),CONVERT(alleleFreqs USING utf8) from snp150 where name in ("rs2088175","rs2983855","rs2821958","rs41469446","rs619987","rs2877425","rs41447048","rs41497748","rs503808","rs386628","rs6667995","rs41405345")'
Cmd exec on: Tue Jul 23 10:15:36 CDT 2024
+---------------------+------------+-----------+------------+-----------------------------+---------------------------------+
| chrom               | chromStart | chromEnd  | name       | CONVERT(alleles USING utf8) | CONVERT(alleleFreqs USING utf8) |
+---------------------+------------+-----------+------------+-----------------------------+---------------------------------+
| chr3                |   76216220 |  76216221 | rs2088175  | C,G,                        | 0.875399,0.124601,              |
| chr21               |   13597605 |  13597606 | rs2821958  | A,G,                        | 0.884181,0.115819,              |
| chr4                |   68990362 |  68990363 | rs2877425  | A,G,                        | 0.085463,0.914537,              |
| chr4_GL000257v2_alt |     566381 |    566382 | rs2877425  | A,G,                        | 0.085463,0.914537,              |
| chr10               |   38291051 |  38291052 | rs2983855  | C,T,                        | 0.468850,0.531150,              |
| chr6                |   28816618 |  28816619 | rs386628   | C,T,                        | 0.339257,0.660743,              |
| chr6_GL000250v2_alt |      82197 |     82198 | rs386628   | C,T,                        | 0.339257,0.660743,              |
| chr6_GL000251v2_alt |     307169 |    307170 | rs386628   | C,T,                        | 0.339257,0.660743,              |
| chr6_GL000252v2_alt |      82220 |     82221 | rs386628   | C,T,                        | 0.339257,0.660743,              |
| chr6_GL000253v2_alt |      82185 |     82186 | rs386628   | C,T,                        | 0.339257,0.660743,              |
| chr6_GL000254v2_alt |      82211 |     82212 | rs386628   | C,T,                        | 0.339257,0.660743,              |
| chr6_GL000255v2_alt |      82201 |     82202 | rs386628   | C,T,                        | 0.339257,0.660743,              |
| chr6_GL000256v2_alt |     125864 |    125865 | rs386628   | C,T,                        | 0.339257,0.660743,              |
| chr17               |   22206798 |  22206799 | rs41447048 | C,T,                        | 0.833067,0.166933,              |
| chr18               |   14208563 |  14208564 | rs41469446 | A,G,                        | 0.868011,0.131989,              |
| chr1                |   83314732 |  83314733 | rs41497748 | A,T,                        | 0.831669,0.168331,              |
| chr2                |  126689276 | 126689277 | rs503808   | A,G,                        | 0.647364,0.352636,              |
| chr1                |  149015643 | 149015644 | rs619987   | A,T,                        | 0.869010,0.130990,              |
| chr1                |  205854053 | 205854054 | rs6667995  | A,C,                        | 0.758586,0.241414,              |
+---------------------+------------+-----------+------------+-----------------------------+---------------------------------+

Answer 2

[Emily]

BioMart

Use the short variation database, filter by your list of IDs, get location and alleles as attributes.

Example query

Answer 3

[igor]

There are a lot of excellent responses already, but I wanted to offer an alternate solution.

You can also download SNPs in a table format from UCSC: http://hgdownload.cse.ucsc.edu/goldenPath/hg19/database/snp150.txt.gz

Since these are in a table format, they are a little easier to read and process without specialized tools. For example, you can run:

zcat snp150.txt.gz | grep -w -f snplist.txt

Where snplist.txt is your list of SNPs.

Answer 4

[Bastien Hervé]

If you download the dbSNP of your genome, you will have all the informations you need. Just look into the dbSNP file with your SNPs list using Awk, Python, Perl...

[jiumeng66]

Thank you, but where can I download the dbSNP?

[Bastien Hervé]

hg19 :

In your browser

ftp://[e-mail]/bundle/hg19/dbsnp_138.hg19.vcf.gz

or in your terminal

wget ftp://[e-mail]/bundle/hg19/dbsnp_138.hg19.vcf.gz

[jiumeng66]

sorry to reply lately, I have download it just now with your help. Thank you very much.

Answer 5

[Alex Reynolds]

If you want to do things locally, you can make a searchable resource you can query as you like.

1) Get SNPs and write them into a text file sorted by SNP ID.

For hg19, for instance, using BEDOPS convert2bed to convert VCF to BED:

$ LC_ALL=C
$ wget -qO- ftp://ftp.ncbi.nih.gov/snp/organisms/human_9606_b150_GRCh37p13/VCF/All_20170710.vcf.gz \
   | gunzip -c \
   | convert2bed --input=vcf --sort-tmpdir=${PWD} - \
   | awk -v OFS="\t" '{ print $4,"chr"$1,$2,$3,$6,$7; }' \
   | sort -k1,1 \
   > hg19.snp150.sortedByName.txt

This text file includes the SNP rsID, the genomic position, and the reference and alternate alleles. It is sorted by the SNP rsID, a property which we will use to enable fast searches.

2) Install pts-line-bisect, which does a binary search on lexicographically-sorted files, such as the one that was just made:

$ git clone https://github.com/pts/pts-line-bisect.git
$ cd pts-line-bisect && make && cd ..

Binary searches are pretty fast and great for write-once, read-many applications like this.

3) Run a query. The following command would return a six-column BED file:

$ rs_of_interest=rs41457244
$ ./pts-line-bisect/pts_lbsearch -p hg19.snp150.sortedByName.txt ${rs_of_interest} \
   | head -1 \
   | awk -v OFS="\t" '{ print "chr"$2,$3,$4,$1,$5,$6; }'

Step 3 can be put into a script so that you can re-run it with your SNP of interest on demand.

For instance:

#!/bin/bash
pts_lbsearch_bin=./pts-line-bisect/pts_lbsearch
sorted_snp_txt=hg19.snp150.sortedByName.txt
${pts_lbsearch_bin} -p ${sorted_snp_txt} $1 | head -1 | awk -v OFS="\t" '{ print "chr"$2,$3,$4,$1,$5,$6; }'

Then:

$ ./search_snps.sh rs41457244
...

If you have a file of rs* IDs, you could loop through them via bash:

$ while read rsID; do ./search_snps.sh $rsID; done < snpIDs.txt | sort-bed - > snps.bed

Writing a sorted BED file can be useful for enabling set operations with BEDOPS and other toolkits.

[jiumeng66]

Thank you, it is a little bit complex. I will try.^_^

[Alex Reynolds]

No need, if the other solutions work for you. Once set up, however, it is just a way to query stuff very quickly, repeatedly, without going over a potentially slow network. Lots of great options here, regardless.