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
•
written
by
jiumeng66 •
$ 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, |
+---------------------+------------+-----------+------------+---------+--------------------+
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?
Dose it mean that some tool need be installed?
ubuntu:
apt-get install mysql-client
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 |
+---------------------+------------+-----------+------------+------------------+----------------------------------------+
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")'
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, |
+---------------------+------------+-----------+------------+-----------------------------+---------------------------------+
Use the short variation database, filter by your list of IDs, get location and alleles as attributes.
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.
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...
Thank you, but where can I download the dbSNP?
hg19 :
In your browser
ftp://gsapubftp-anonymous@ftp.broadinstitute.org/bundle/hg19/dbsnp_138.hg19.vcf.gz
or in your terminal
wget ftp://gsapubftp-anonymous@ftp.broadinstitute.org/bundle/hg19/dbsnp_138.hg19.vcf.gz
sorry to reply lately, I have download it just now with your help. Thank you very much.
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.
Thank you, it is a little bit complex. I will try.^_^
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.
Log in to answer this question.
Try rsnps package in R
https://cran.r-project.org/web/packages/rsnps/rsnps.pdf
Please add an example/more detail or move your suggestion to a comment. Thank you!
Added link for the package tutorial.
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.