diff options
Diffstat (limited to 'wqflask/maintenance')
21 files changed, 1224 insertions, 1 deletions
diff --git a/wqflask/maintenance/dataset/.gitignore b/wqflask/maintenance/dataset/.gitignore new file mode 100644 index 00000000..4910042e --- /dev/null +++ b/wqflask/maintenance/dataset/.gitignore @@ -0,0 +1,2 @@ +*.pyc +datadir/ diff --git a/wqflask/maintenance/dataset/__init__.py b/wqflask/maintenance/dataset/__init__.py new file mode 100644 index 00000000..e69de29b --- /dev/null +++ b/wqflask/maintenance/dataset/__init__.py diff --git a/wqflask/maintenance/dataset/calculate.py b/wqflask/maintenance/dataset/calculate.py new file mode 100644 index 00000000..6aada827 --- /dev/null +++ b/wqflask/maintenance/dataset/calculate.py @@ -0,0 +1,7 @@ +import scipy.stats + +def correlation(a1, a2): + re = [] + re.append(scipy.stats.pearsonr(a1, a2)) + re.append(scipy.stats.spearmanr(a1, a2)) + return re diff --git a/wqflask/maintenance/dataset/correlations.py b/wqflask/maintenance/dataset/correlations.py new file mode 100644 index 00000000..b089e446 --- /dev/null +++ b/wqflask/maintenance/dataset/correlations.py @@ -0,0 +1,47 @@ +# Author: Lei Yan +# Create Date: 2014-01-21 +# Last Update Date: 2014-01-24 + +# import +import sys +import os +import re +import MySQLdb +import ConfigParser + +def main(argv): + + # load configuration from configuration file + config = ConfigParser.ConfigParser() + config.read(argv[1]) + genofile = config.get('configuration', 'genofile') + + # parse genofile + genotypes = [] + file_geno = open(genofile, 'r') + for line in file_geno: + line = line.strip() + if line.startswith('#'): + continue + if line.startswith('@'): + continue + cells = line.split() + if line.startswith("Chr"): + strains = cells[4:] + continue + genotype = {} + genotype['chr'] = cells[0] + genotype['locus'] = cells[1] + genotype['cm'] = cells[2] + genotype['mb'] = cells[3] + genotype['values'] = cells[4:] + genotypes.append(genotype) + print "get %d strains:\t%s" % (len(strains), strains) + print "load %d genotypes" % len(genotypes) + + # phenotypes + +# main +if __name__ == "__main__": + main(sys.argv) + print "exit successfully" diff --git a/wqflask/maintenance/dataset/datasampledir/load_genotypes/config.ini b/wqflask/maintenance/dataset/datasampledir/load_genotypes/config.ini new file mode 100644 index 00000000..abff371b --- /dev/null +++ b/wqflask/maintenance/dataset/datasampledir/load_genotypes/config.ini @@ -0,0 +1,7 @@ +[config] +inbredsetid = 1 +genofile = datasampledir/load_genotypes/sample.geno +genovalue_U = x +genovalue_H = 0 +genovalue_B = -1 +genovalue_D = 1 diff --git a/wqflask/maintenance/dataset/datasampledir/load_genotypes/sample.geno b/wqflask/maintenance/dataset/datasampledir/load_genotypes/sample.geno new file mode 100644 index 00000000..0024ffd1 --- /dev/null +++ b/wqflask/maintenance/dataset/datasampledir/load_genotypes/sample.geno @@ -0,0 +1,12 @@ +@name:BXD +@type:riset +@mat:B +@pat:D +@het:H +@unk:U +Chr Locus cM Mb BXD1 BXD2 BXD5 BXD6 BXD8 +1 rs6269442 0 3.482275 D B D D D +2 rs6365999 0.3 4.811062 B B D D D +3 rs6376963 0.895 5.008089 B B D D D +4 rs3677817 1.185 5.176058 B B D D D +5 rs8236463 2.081 5.579193 B B D D D diff --git a/wqflask/maintenance/dataset/datasampledir/load_phenotypes/config.ini b/wqflask/maintenance/dataset/datasampledir/load_phenotypes/config.ini new file mode 100644 index 00000000..638c3bd8 --- /dev/null +++ b/wqflask/maintenance/dataset/datasampledir/load_phenotypes/config.ini @@ -0,0 +1,4 @@ +[config] +inbredsetid = 1 +datafile = datasampledir/load_phenotypes/sample_data.txt +metafile = datasampledir/load_phenotypes/sample_meta.txt diff --git a/wqflask/maintenance/dataset/datasampledir/load_phenotypes/sample_data.txt b/wqflask/maintenance/dataset/datasampledir/load_phenotypes/sample_data.txt new file mode 100644 index 00000000..a39fd3be --- /dev/null +++ b/wqflask/maintenance/dataset/datasampledir/load_phenotypes/sample_data.txt @@ -0,0 +1,10 @@ +CaseID BXD1 BXD2 BXD3 BXD4 BXD5 +Value 1 2 3 4 5 +SE 6 7 8 9 10 +N 11 12 13 14 15 +Sex 16 17 18 19 20 +SE 21 22 23 24 25 +N 26 27 28 29 30 +Age 31 32 33 34 35 +SE 36 37 38 39 40 +N 41 42 43 44 45 diff --git a/wqflask/maintenance/dataset/datasampledir/load_phenotypes/sample_meta.txt b/wqflask/maintenance/dataset/datasampledir/load_phenotypes/sample_meta.txt new file mode 100644 index 00000000..5172223f --- /dev/null +++ b/wqflask/maintenance/dataset/datasampledir/load_phenotypes/sample_meta.txt @@ -0,0 +1,4 @@ +Pubmed ID Pre Publication Description Post Publication Description Original Description Pre Publication Abbreviation Post Publication Abbreviation Lab Code Submitter Owner Authorized Users Authors Title Abstract Journal Volume Pages Month Year Units +x Pre Publication Description 1 Post Publication Description 1 Original Description 1 Pre Publication Abbreviation 1 Post Publication Abbreviation 1 Lab Code 1 Submitter 1 Owner 1 Authorized Users 1 Authors 1 Title 1 Abstract 1 Journal 1 Volume 1 Pages 1 Month 1 2001 unit 1 +x Pre Publication Description 2 Post Publication Description 2 Original Description 2 Pre Publication Abbreviation 2 Post Publication Abbreviation 2 Lab Code 2 Submitter 2 Owner 2 Authorized Users 2 Authors 2 Title 2 Abstract 2 Journal 2 Volume 2 Pages 2 Month 2 2002 unit 2 +19958391 Pre Publication Description 3 Post Publication Description 3 Original Description 3 Pre Publication Abbreviation 3 Post Publication Abbreviation 3 Lab Code 3 Submitter 3 Owner 3 Authorized Users 3 Authors 3 Title 3 Abstract 3 Journal 3 Volume 3 Pages 3 Month 3 2003 unit 3 diff --git a/wqflask/maintenance/dataset/datastructure.py b/wqflask/maintenance/dataset/datastructure.py new file mode 100644 index 00000000..7139856f --- /dev/null +++ b/wqflask/maintenance/dataset/datastructure.py @@ -0,0 +1,155 @@ +import utilities + +def get_probesetfreezes(inbredsetid): + cursor, con = utilities.get_cursor() + sql = """ + SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName` + FROM ProbeSetFreeze, ProbeFreeze + WHERE ProbeSetFreeze.`ProbeFreezeId`=ProbeFreeze.`Id` + AND ProbeFreeze.`InbredSetId`=%s + """ + cursor.execute(sql, (inbredsetid)) + return cursor.fetchall() + +def get_probesetfreeze(probesetfreezeid): + cursor, con = utilities.get_cursor() + sql = """ + SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName` + FROM ProbeSetFreeze + WHERE ProbeSetFreeze.`Id`=%s + """ + cursor.execute(sql, (probesetfreezeid)) + return cursor.fetchone() + +def get_strains(inbredsetid): + cursor, con = utilities.get_cursor() + sql = """ + SELECT Strain.`Id`, Strain.`Name` + FROM StrainXRef, Strain + WHERE StrainXRef.`InbredSetId`=%s + AND StrainXRef.`StrainId`=Strain.`Id` + ORDER BY StrainXRef.`OrderId` + """ + cursor.execute(sql, (inbredsetid)) + return cursor.fetchall() + +def get_inbredset(probesetfreezeid): + cursor, con = utilities.get_cursor() + sql = """ + SELECT InbredSet.`Id`, InbredSet.`Name`, InbredSet.`FullName` + FROM InbredSet, ProbeFreeze, ProbeSetFreeze + WHERE InbredSet.`Id`=ProbeFreeze.`InbredSetId` + AND ProbeFreeze.`Id`=ProbeSetFreeze.`ProbeFreezeId` + AND ProbeSetFreeze.`Id`=%s + """ + cursor.execute(sql, (probesetfreezeid)) + return cursor.fetchone() + +def get_species(inbredsetid): + cursor, con = utilities.get_cursor() + sql = """ + SELECT Species.`Id`, Species.`Name`, Species.`MenuName`, Species.`FullName` + FROM InbredSet, Species + WHERE InbredSet.`Id`=%s + AND InbredSet.`SpeciesId`=Species.`Id` + """ + cursor.execute(sql, (inbredsetid)) + return cursor.fetchone() + +def get_genofreeze_byinbredsetid(inbredsetid): + cursor, con = utilities.get_cursor() + sql = """ + SELECT GenoFreeze.`Id`, GenoFreeze.`Name`, GenoFreeze.`FullName`, GenoFreeze.`InbredSetId` + FROM GenoFreeze + WHERE GenoFreeze.`InbredSetId`=%s + """ + cursor.execute(sql, (inbredsetid)) + return cursor.fetchone() + +def get_nextdataid_genotype(): + cursor, con = utilities.get_cursor() + sql = """ + SELECT GenoData.`Id` + FROM GenoData + ORDER BY GenoData.`Id` DESC + LIMIT 1 + """ + cursor.execute(sql) + re = cursor.fetchone() + dataid = re[0] + dataid += 1 + return dataid + +def get_nextdataid_phenotype(): + cursor, con = utilities.get_cursor() + sql = """ + SELECT PublishData.`Id` + FROM PublishData + ORDER BY PublishData.`Id` DESC + LIMIT 1 + """ + cursor.execute(sql) + re = cursor.fetchone() + dataid = re[0] + dataid += 1 + return dataid + +def insert_strain(inbredsetid, strainname, updatestrainxref=None): + speciesid = get_species(inbredsetid)[0] + cursor, con = utilities.get_cursor() + sql = """ + INSERT INTO Strain + SET + Strain.`Name`=%s, + Strain.`Name2`=%s, + Strain.`SpeciesId`=%s + """ + cursor.execute(sql, (strainname, strainname, speciesid)) + strainid = con.insert_id() + if updatestrainxref: + sql = """ + SELECT StrainXRef.`OrderId` + FROM StrainXRef + where StrainXRef.`InbredSetId`=%s + ORDER BY StrainXRef.`OrderId` DESC + LIMIT 1 + """ + cursor.execute(sql, (inbredsetid)) + re = cursor.fetchone() + orderid = re[0] + 1 + # + sql = """ + INSERT INTO StrainXRef + SET + StrainXRef.`InbredSetId`=%s, + StrainXRef.`StrainId`=%s, + StrainXRef.`OrderId`=%s, + StrainXRef.`Used_for_mapping`=%s, + StrainXRef.`PedigreeStatus`=%s + """ + cursor.execute(sql, (inbredsetid, strainid, orderid, "N", None)) + +def get_strain(inbredsetid, strainname): + speciesid = get_species(inbredsetid)[0] + cursor, con = utilities.get_cursor() + sql = """ + SELECT Strain.`Id`, Strain.`Name` + FROM Strain + WHERE Strain.`SpeciesId`=%s + AND Strain.`Name` LIKE %s + """ + cursor.execute(sql, (speciesid, strainname)) + return cursor.fetchone() + +def get_strain_sure(inbredsetid, strainname, updatestrainxref=None): + strain = get_strain(inbredsetid, strainname) + if not strain: + insert_strain(inbredsetid, strainname, updatestrainxref) + strain = get_strain(inbredsetid, strainname) + return strain + +def get_strains_bynames(inbredsetid, strainnames, updatestrainxref=None): + strains = [] + for strainname in strainnames: + strains.append(get_strain_sure(inbredsetid, strainname, updatestrainxref)) + return strains diff --git a/wqflask/maintenance/dataset/fetch.py b/wqflask/maintenance/dataset/fetch.py new file mode 100644 index 00000000..fcb2d2d8 --- /dev/null +++ b/wqflask/maintenance/dataset/fetch.py @@ -0,0 +1,11 @@ +import sys + +inputfile = open(sys.argv[1], 'r') + +for line in inputfile: + cells = line.split() + #print cells[int(sys.argv[2])] + i = len(cells) + print i + +inputfile.close() diff --git a/wqflask/maintenance/dataset/genotypes.py b/wqflask/maintenance/dataset/genotypes.py new file mode 100644 index 00000000..b57d4651 --- /dev/null +++ b/wqflask/maintenance/dataset/genotypes.py @@ -0,0 +1,48 @@ +import utilities + +def get_geno(inbredsetid, name): + cursor = utilities.get_cursor() + sql = """ + SELECT Geno.`Id`, Geno.`Name`, Geno.`Chr`, Geno.`Mb` + FROM (Geno, InbredSet) + WHERE Geno.`SpeciesId`=InbredSet.`SpeciesId` + AND InbredSet.`Id`=%s + AND Geno.`Name` LIKE %s + """ + cursor.execute(sql, (inbredsetid, name)) + return cursor.fetchone() + +def load_genos(file): + genotypes = [] + file_geno = open(file, 'r') + for line in file_geno: + line = line.strip() + if line.startswith('#'): + continue + if line.startswith('@'): + continue + cells = line.split() + if line.startswith("Chr"): + strains = cells[4:] + strains = [strain.lower() for strain in strains] + continue + genotype = {} + genotype['chr'] = cells[0] + genotype['locus'] = cells[1] + genotype['cm'] = cells[2] + genotype['mb'] = cells[3] + values = cells[4:] + values = [to_number(value) for value in values] + genotype['values'] = values + genotype['dicvalues'] = utilities.to_dic(strains, values) + genotypes.append(genotype) + return strains, genotypes + +def to_number(char): + dic = { + 'b': -1, + 'd': 1, + 'h': 0, + 'u': None, + } + return dic.get(char.lower(), None) diff --git a/wqflask/maintenance/dataset/load_genotypes.py b/wqflask/maintenance/dataset/load_genotypes.py new file mode 100644 index 00000000..4697382b --- /dev/null +++ b/wqflask/maintenance/dataset/load_genotypes.py @@ -0,0 +1,157 @@ +from __future__ import absolute_import, print_function, division + +import sys +import re +import argparse + +import utilities +import datastructure + +def main(argv): + config = utilities.get_config(argv[1]) + print("config file:") + for item in config.items('config'): + print("\t", str(item)) + parse_genofile(config, fetch_parameters(config)) + +def fetch_parameters(config): + config_dic = {} + config_dic['inbredsetid'] = config.get('config', 'inbredsetid') + config_dic["speciesid"] = datastructure.get_species(config_dic['inbredsetid'])[0] + config_dic['genofreezeid'] = datastructure.get_genofreeze_byinbredsetid(config_dic['inbredsetid'])[0] + config_dic['dataid'] = datastructure.get_nextdataid_genotype() + config_dic['genofile'] = config.get('config', 'genofile') + print("config dictionary:") + for k, v in config_dic.items(): + print("\t%s: %s" % (k, v)) + return config_dic + +def parse_genofile(config, config_dic): + genofile = open(config_dic['genofile'], 'r') + meta_dic = {} + for line in genofile: + line = line.strip() + if len(line) == 0: + continue + if line.startswith('#'): + continue + if line.startswith('@'): + line = line.strip('@') + items = line.split(';') + for item in items: + kv = re.split(':|=', item) + meta_dic[kv[0].strip()] = kv[1].strip() + continue + if line.lower().startswith("chr"): + # + print("geno file meta dictionary:") + for k, v in meta_dic.items(): + print("\t%s: %s" % (k, v)) + # + print("geno file head:\n\t%s" % line) + strainnames = line.split()[4:] + config_dic['strains'] = datastructure.get_strains_bynames(inbredsetid=config_dic['inbredsetid'], strainnames=strainnames, updatestrainxref="yes") + continue + # geno file line, marker + marker_dic = parse_marker(line) + marker_dic['genoid'] = check_or_insert_geno(config_dic, marker_dic) + if check_genoxref(config_dic, marker_dic): + continue + insert_genodata(config, config_dic, marker_dic) + insert_genoxref(config_dic, marker_dic) + config_dic['dataid'] += 1 + genofile.close() + +def parse_marker(line): + marker_dic = {} + cells = line.split() + marker_dic['chromosome'] = cells[0] + marker_dic['locus'] = cells[1] + marker_dic['cm'] = cells[2] + marker_dic['mb'] = cells[3] + marker_dic['values'] = cells[4:] + return marker_dic + +def check_or_insert_geno(config_dic, marker_dic): + cursor, con = utilities.get_cursor() + sql = """ + SELECT Geno.`Id` + FROM Geno + WHERE Geno.`SpeciesId`=%s + AND Geno.`Name` like %s + """ + cursor.execute(sql, (config_dic["speciesid"], marker_dic['locus'])) + result = cursor.fetchone() + if result: + genoid = result[0] + print("get geno record: %d" % genoid) + else: + sql = """ + INSERT INTO Geno + SET + Geno.`SpeciesId`=%s, + Geno.`Name`=%s, + Geno.`Marker_Name`=%s, + Geno.`Chr`=%s, + Geno.`Mb`=%s + """ + cursor.execute(sql, (config_dic['speciesid'], marker_dic['locus'], marker_dic['locus'], marker_dic['chromosome'], marker_dic['mb'])) + rowcount = cursor.rowcount + genoid = con.insert_id() + print("INSERT INTO Geno: %d record: %d" % (rowcount, genoid)) + return genoid + +def check_genoxref(config_dic, marker_dic): + cursor, con = utilities.get_cursor() + sql = """ + select GenoXRef.* + from GenoXRef + where GenoXRef.`GenoFreezeId`=%s + AND GenoXRef.`GenoId`=%s + """ + cursor.execute(sql, (config_dic['genofreezeid'], marker_dic['genoid'])) + rowcount = cursor.rowcount + return rowcount + +def insert_genodata(config, config_dic, marker_dic): + cursor, con = utilities.get_cursor() + for index, strain in enumerate(config_dic['strains']): + strainid = strain[0] + value = utilities.to_db_string(marker_dic['values'][index], None) + if not value: + continue + value = config.get('config', "genovalue_" + value) + try: + number = int(value) + except: + continue + if not number in [-1, 0, 1]: + continue + sql = """ + INSERT INTO GenoData + SET + GenoData.`Id`=%s, + GenoData.`StrainId`=%s, + GenoData.`value`=%s + """ + cursor.execute(sql, (config_dic['dataid'], strainid, number)) + +def insert_genoxref(config_dic, marker_dic): + cursor, con = utilities.get_cursor() + sql = """ + INSERT INTO GenoXRef + SET + GenoXRef.`GenoFreezeId`=%s, + GenoXRef.`GenoId`=%s, + GenoXRef.`DataId`=%s, + GenoXRef.`cM`=%s, + GenoXRef.`Used_for_mapping`=%s + """ + cursor.execute(sql, (config_dic['genofreezeid'], marker_dic['genoid'], config_dic['dataid'], marker_dic['cm'], 'N')) + rowcount = cursor.rowcount + print("INSERT INTO GenoXRef: %d record" % (rowcount)) + +if __name__ == "__main__": + print("command line arguments:\n\t%s" % sys.argv) + main(sys.argv) + print("exit successfully") diff --git a/wqflask/maintenance/dataset/load_phenotypes.py b/wqflask/maintenance/dataset/load_phenotypes.py new file mode 100644 index 00000000..27e340f8 --- /dev/null +++ b/wqflask/maintenance/dataset/load_phenotypes.py @@ -0,0 +1,171 @@ +import sys +import csv + +import utilities +import datastructure + +def main(argv): + # config + config = utilities.get_config(argv[1]) + print "config:" + for item in config.items('config'): + print "\t%s" % (str(item)) + # var + inbredsetid = config.get('config', 'inbredsetid') + print "inbredsetid: %s" % inbredsetid + species = datastructure.get_species(inbredsetid) + speciesid = species[0] + print "speciesid: %s" % speciesid + dataid = datastructure.get_nextdataid_phenotype() + print "next data id: %s" % dataid + cursor, con = utilities.get_cursor() + # datafile + datafile = open(config.get('config', 'datafile'), 'r') + phenotypedata = csv.reader(datafile, delimiter='\t', quotechar='"') + phenotypedata_head = phenotypedata.next() + print "phenotypedata head:\n\t%s" % phenotypedata_head + strainnames = phenotypedata_head[1:] + strains = datastructure.get_strains_bynames(inbredsetid=inbredsetid, strainnames=strainnames, updatestrainxref=None) + # metafile + metafile = open(config.get('config', 'metafile'), 'r') + phenotypemeta = csv.reader(metafile, delimiter='\t', quotechar='"') + phenotypemeta_head = phenotypemeta.next() + print "phenotypemeta head:\n\t%s" % phenotypemeta_head + print + # load + for metarow in phenotypemeta: + # + datarow_value = phenotypedata.next() + datarow_se = phenotypedata.next() + datarow_n = phenotypedata.next() + # Phenotype + sql = """ + INSERT INTO Phenotype + SET + Phenotype.`Pre_publication_description`=%s, + Phenotype.`Post_publication_description`=%s, + Phenotype.`Original_description`=%s, + Phenotype.`Pre_publication_abbreviation`=%s, + Phenotype.`Post_publication_abbreviation`=%s, + Phenotype.`Lab_code`=%s, + Phenotype.`Submitter`=%s, + Phenotype.`Owner`=%s, + Phenotype.`Authorized_Users`=%s, + Phenotype.`Units`=%s + """ + cursor.execute(sql, ( + utilities.to_db_string(metarow[1], None), + utilities.to_db_string(metarow[2], None), + utilities.to_db_string(metarow[3], None), + utilities.to_db_string(metarow[4], None), + utilities.to_db_string(metarow[5], None), + utilities.to_db_string(metarow[6], None), + utilities.to_db_string(metarow[7], None), + utilities.to_db_string(metarow[8], None), + utilities.to_db_string(metarow[9], ""), + utilities.to_db_string(metarow[18], None), + )) + rowcount = cursor.rowcount + phenotypeid = con.insert_id() + print "INSERT INTO Phenotype: %d record: %d" % (rowcount, phenotypeid) + # Publication + publicationid = None # reset + pubmed_id = utilities.to_db_string(metarow[0], None) + if pubmed_id: + sql = """ + SELECT Publication.`Id` + FROM Publication + WHERE Publication.`PubMed_ID`=%s + """ + cursor.execute(sql, (pubmed_id)) + re = cursor.fetchone() + if re: + publicationid = re[0] + print "get Publication record: %d" % publicationid + if not publicationid: + sql = """ + INSERT INTO Publication + SET + Publication.`PubMed_ID`=%s, + Publication.`Abstract`=%s, + Publication.`Authors`=%s, + Publication.`Title`=%s, + Publication.`Journal`=%s, + Publication.`Volume`=%s, + Publication.`Pages`=%s, + Publication.`Month`=%s, + Publication.`Year`=%s + """ + cursor.execute(sql, ( + utilities.to_db_string(metarow[0], None), + utilities.to_db_string(metarow[12], None), + utilities.to_db_string(metarow[10], ""), + utilities.to_db_string(metarow[11], None), + utilities.to_db_string(metarow[13], None), + utilities.to_db_string(metarow[14], None), + utilities.to_db_string(metarow[15], None), + utilities.to_db_string(metarow[16], None), + utilities.to_db_string(metarow[17], None), + )) + rowcount = cursor.rowcount + publicationid = con.insert_id() + print "INSERT INTO Publication: %d record: %d" % (rowcount, publicationid) + # data + for index, strain in enumerate(strains): + # + strainid = strain[0] + value = utilities.to_db_float(datarow_value[index+1], None) + se = utilities.to_db_float(datarow_se[index+1], None) + n = utilities.to_db_int(datarow_n[index+1], None) + # + if value: + sql = """ + INSERT INTO PublishData + SET + PublishData.`Id`=%s, + PublishData.`StrainId`=%s, + PublishData.`value`=%s + """ + cursor.execute(sql, (dataid, strainid, value)) + if se: + sql = """ + INSERT INTO PublishSE + SET + PublishSE.`DataId`=%s, + PublishSE.`StrainId`=%s, + PublishSE.`error`=%s + """ + cursor.execute(sql, (dataid, strainid, se)) + if n: + sql = """ + INSERT INTO NStrain + SET + NStrain.`DataId`=%s, + NStrain.`StrainId`=%s, + NStrain.`count`=%s + """ + cursor.execute(sql, (dataid, strainid, n)) + # PublishXRef + sql = """ + INSERT INTO PublishXRef + SET + PublishXRef.`InbredSetId`=%s, + PublishXRef.`PhenotypeId`=%s, + PublishXRef.`PublicationId`=%s, + PublishXRef.`DataId`=%s, + PublishXRef.`comments`=%s + """ + cursor.execute(sql, (inbredsetid, phenotypeid, publicationid, dataid, "")) + rowcount = cursor.rowcount + publishxrefid = con.insert_id() + print "INSERT INTO PublishXRef: %d record: %d" % (rowcount, publishxrefid) + # for loop next + dataid += 1 + print + # release + con.close() + +if __name__ == "__main__": + print "command line arguments:\n\t%s" % sys.argv + main(sys.argv) + print "exit successfully" diff --git a/wqflask/maintenance/dataset/phenotypes.py b/wqflask/maintenance/dataset/phenotypes.py new file mode 100644 index 00000000..ed30f33f --- /dev/null +++ b/wqflask/maintenance/dataset/phenotypes.py @@ -0,0 +1,100 @@ +import utilities + +def fetch(): + # parameters + inbredsetid = 1 + phenotypesfile = open('bxdphenotypes.txt', 'w+') + # + phenotypesfile.write("id\tAuthors\tOriginal_description\tPre_publication_description\tPost_publication_description\t") + # open db + cursor = utilities.get_cursor() + # get strain list + strains = [] + sql = """ + SELECT Strain.`Name` + FROM StrainXRef, Strain + WHERE StrainXRef.`StrainId`=Strain.`Id` + AND StrainXRef.`InbredSetId`=%s + ORDER BY StrainXRef.`OrderId` + """ + cursor.execute(sql, (inbredsetid)) + results = cursor.fetchall() + for row in results: + strain = row[0] + strain = strain.lower() + strains.append(strain) + print "get %d strains: %s" % (len(strains), strains) + phenotypesfile.write('\t'.join([strain.upper() for strain in strains])) + phenotypesfile.write('\n') + phenotypesfile.flush() + # phenotypes + sql = """ + SELECT PublishXRef.`Id`, Publication.`Authors`, Phenotype.`Original_description`, Phenotype.`Pre_publication_description`, Phenotype.`Post_publication_description` + FROM (PublishXRef, Phenotype, Publication) + WHERE PublishXRef.`InbredSetId`=%s + AND PublishXRef.`PhenotypeId`=Phenotype.`Id` + AND PublishXRef.`PublicationId`=Publication.`Id` + """ + cursor.execute(sql, (inbredsetid)) + results = cursor.fetchall() + print "get %d phenotypes" % (len(results)) + for phenotyperow in results: + publishxrefid = phenotyperow[0] + authors = utilities.clearspaces(phenotyperow[1]) + original_description = utilities.clearspaces(phenotyperow[2]) + pre_publication_description = utilities.clearspaces(phenotyperow[3]) + post_publication_description = utilities.clearspaces(phenotyperow[4]) + phenotypesfile.write("%s\t%s\t%s\t%s\t%s\t" % (publishxrefid, authors, original_description, pre_publication_description, post_publication_description)) + sql = """ + SELECT Strain.Name, PublishData.value + FROM (PublishXRef, PublishData, Strain) + WHERE PublishXRef.`InbredSetId`=%s + AND PublishXRef.Id=%s + AND PublishXRef.DataId=PublishData.Id + AND PublishData.StrainId=Strain.Id + """ + cursor.execute(sql, (inbredsetid, publishxrefid)) + results = cursor.fetchall() + print "get %d values" % (len(results)) + strainvaluedic = {} + for strainvalue in results: + strainname = strainvalue[0] + strainname = strainname.lower() + value = strainvalue[1] + strainvaluedic[strainname] = value + for strain in strains: + if strain in strainvaluedic: + phenotypesfile.write(str(strainvaluedic[strain])) + else: + phenotypesfile.write('x') + phenotypesfile.write('\t') + phenotypesfile.write('\n') + phenotypesfile.flush() + # release + phenotypesfile.close() + +def delete(publishxrefid, inbredsetid): + cursor = utilities.get_cursor() + sql = """ + DELETE Phenotype + FROM PublishXRef,Phenotype + WHERE PublishXRef.`Id`=%s + AND PublishXRef.`InbredSetId`=%s + AND PublishXRef.`PhenotypeId`=Phenotype.`Id` + """ + cursor.execute(sql, (publishxrefid, inbredsetid)) + sql = """ + DELETE PublishData + FROM PublishXRef,PublishData + WHERE PublishXRef.`Id`=%s + AND PublishXRef.`InbredSetId`=%s + AND PublishXRef.`DataId`=PublishData.`Id` + """ + cursor.execute(sql, (publishxrefid, inbredsetid)) + sql = """ + DELETE PublishXRef + FROM PublishXRef + WHERE PublishXRef.`Id`=%s + AND PublishXRef.`InbredSetId`=%s + """ + cursor.execute(sql, (publishxrefid, inbredsetid)) diff --git a/wqflask/maintenance/dataset/probesets.py b/wqflask/maintenance/dataset/probesets.py new file mode 100644 index 00000000..97bb5bdf --- /dev/null +++ b/wqflask/maintenance/dataset/probesets.py @@ -0,0 +1,90 @@ +import utilities +import datastructure +import genotypes + +def get_probesetxref(probesetfreezeid): + cursor = utilities.get_cursor() + sql = """ + SELECT ProbeSetXRef.`ProbeSetId`, ProbeSetXRef.`DataId` + FROM ProbeSetXRef + WHERE ProbeSetXRef.`ProbeSetFreezeId`=%s + """ + cursor.execute(sql, (probesetfreezeid)) + return cursor.fetchall() + +def get_probeset(probesetid): + cursor = utilities.get_cursor() + sql = """ + SELECT ProbeSet.`Id`, ProbeSet.`Name`, ProbeSet.`Symbol`, ProbeSet.`description`, ProbeSet.`Probe_Target_Description`, ProbeSet.`Chr`, ProbeSet.`Mb` + FROM ProbeSet + WHERE ProbeSet.`Id`=%s + """ + cursor.execute(sql, (probesetid)) + return cursor.fetchone() + +def get_probesetdata(probesetdataid): + cursor = utilities.get_cursor() + sql = """ + SELECT Strain.`Id`, Strain.`Name`, ProbeSetData.`value` + FROM ProbeSetData, Strain + WHERE ProbeSetData.`Id`=%s + AND ProbeSetData.`StrainId`=Strain.`Id`; + """ + cursor.execute(sql, (probesetdataid)) + return cursor.fetchall() + +def get_probesetxref_probesetfreezeid(locus, probesetfreezeid): + cursor = utilities.get_cursor() + sql = """ + SELECT ProbeSetXRef.`ProbeSetId` + FROM ProbeSetXRef + WHERE ProbeSetXRef.`ProbeSetFreezeId`=%s + AND ProbeSetXRef.`Locus` LIKE %s + """ + cursor.execute(sql, (probesetfreezeid, locus)) + return cursor.fetchall() + +def get_probesetxref_inbredsetid(locus, inbredsetid): + cursor = utilities.get_cursor() + sql = """ + SELECT ProbeSetXRef.`ProbeSetId`, ProbeSetXRef.`mean`, ProbeSetXRef.`LRS`, ProbeSetXRef.`Locus`, ProbeSetXRef.`ProbeSetFreezeId` + FROM (ProbeSetXRef, ProbeSetFreeze, ProbeFreeze) + WHERE ProbeSetXRef.`ProbeSetFreezeId`=ProbeSetFreeze.`Id` + AND ProbeSetFreeze.`ProbeFreezeId`=ProbeFreeze.`Id` + AND ProbeFreeze.`InbredSetId`=%s + AND ProbeSetXRef.`Locus` LIKE %s + """ + cursor.execute(sql, (inbredsetid, locus)) + return cursor.fetchall() + +def get_normalized_probeset(locus, inbredsetid): + normalized_probesets = [] + probesetxrefs = get_probesetxref_inbredsetid(locus, inbredsetid) + for probesetxref in probesetxrefs: + normalized_probeset = [] + # + probesetfreezeid = probesetxref[4] + probesetfreeze = datastructure.get_probesetfreeze(probesetfreezeid) + normalized_probeset.append(probesetfreeze[0]) + normalized_probeset.append(probesetfreeze[1]) + normalized_probeset.append(probesetfreeze[2]) + # + probesetid = probesetxref[0] + probeset = get_probeset(probesetid) + normalized_probeset.append(probeset[1]) + normalized_probeset.append(probeset[2]) + normalized_probeset.append(probeset[3]) + normalized_probeset.append(probeset[4]) + normalized_probeset.append(probeset[5]) + normalized_probeset.append(probeset[6]) + # + normalized_probeset.append(probesetxref[1]) + normalized_probeset.append(probesetxref[2]) + # + locus = probesetxref[3] + geno = genotypes.get_geno(inbredsetid=inbredsetid, name=locus) + normalized_probeset.append(geno[2]) + normalized_probeset.append(geno[3]) + # + normalized_probesets.append(normalized_probeset) + return normalized_probesets diff --git a/wqflask/maintenance/dataset/specials1.py b/wqflask/maintenance/dataset/specials1.py new file mode 100644 index 00000000..9159fd7f --- /dev/null +++ b/wqflask/maintenance/dataset/specials1.py @@ -0,0 +1,53 @@ +import utilities +import datastructure +import genotypes +import probesets +import calculate + +""" +For: Rob, GeneNetwork +Date: 2014-02-04 +Function: + For BXD group, fetch probesets with given locus (mapping info). + +locus="rs3663871" +""" +def bxd_probesets_locus(locus, inbredsetid): + # + file = open('probesets_%s.txt' % (locus), 'w+') + file.write("GN Dataset ID\t") + file.write("Dataset Full Name\t") + file.write("ProbeSet Name\t") + file.write("Symbol\t") + file.write("ProbeSet Description\t") + file.write("Probe Target Description\t") + file.write("ProbeSet Chr\t") + file.write("ProbeSet Mb\t") + file.write("Mean\t") + file.write("LRS\t") + file.write("Geno Chr\t") + file.write("Geno Mb\t") + file.write("\n") + file.flush() + # + results = probesets.get_normalized_probeset(locus=locus, inbredsetid=inbredsetid) + for row in results: + file.write("%s\t" % (row[0])) + file.write("%s\t" % (utilities.clearspaces(row[2], default=''))) + file.write("%s\t" % (utilities.clearspaces(row[3], default=''))) + file.write("%s\t" % (utilities.clearspaces(row[4], default=''))) + file.write("%s\t" % (utilities.clearspaces(row[5], default=''))) + file.write("%s\t" % (utilities.clearspaces(row[6], default=''))) + file.write("%s\t" % (utilities.clearspaces(row[7], default=''))) + file.write("%s\t" % (row[8])) + file.write("%s\t" % (row[9])) + file.write("%s\t" % (row[10])) + file.write("%s\t" % (utilities.clearspaces(row[11], default=''))) + file.write("%s\t" % (row[12])) + file.write('\n') + file.flush() + file.close() + +locus='rs3663871' +inbredsetid=1 +bxd_probesets_locus(locus=locus, inbredsetid=inbredsetid) diff --git a/wqflask/maintenance/dataset/specials2.py b/wqflask/maintenance/dataset/specials2.py new file mode 100644 index 00000000..2438af43 --- /dev/null +++ b/wqflask/maintenance/dataset/specials2.py @@ -0,0 +1,139 @@ +import utilities +import datastructure +import genotypes +import probesets +import calculate + +""" +For: Ash +Date: 2014-02-07 +Function: + For BXD group, get a probesetfreeze name list. +""" +def probesetfreeze_list(): + # + inbredsetid = 1 + outputdir = "/home/leiyan/gn2/wqflask/maintenance/dataset/datadir/20140205_Ash_correlations/output" + # + probesetfreezes = datastructure.get_probesetfreezes(inbredsetid) + print "From DB, get %d probesetfreezes" % (len(probesetfreezes)) + file = open(outputdir + '/' + 'probesetfreezes.txt', 'w+') + # + for probesetfreeze in probesetfreezes: + # + print probesetfreeze + probesetfreezeid = probesetfreeze[0] + probesetfreezename = probesetfreeze[1] + probesetfreezefullname = probesetfreeze[2] + # + file.write("%s\t" % probesetfreezeid) + file.write("%s" % probesetfreezefullname) + file.write("\n") + file.flush() + # + file.close() + +""" +For: Ash +Date: 2014-02-05 +Function: + For BXD group, calculate correlations with genotypes and probesets. +""" +def bxd_correlations(): + # + inbredsetid = 1 + genofile = "/home/leiyan/gn/web/genotypes/BXD.geno" + outputdir = "/home/leiyan/gn2/wqflask/maintenance/dataset/datadir/20140205_Ash_correlations/output" + # + t = genotypes.load_genos(genofile) + genostrains = t[0] + genos = t[1] + print "From geno file, get %d strains" % (len(genostrains)) + print "From geno file, get %d genos" % (len(genos)) + # + probesetfreezes = datastructure.get_probesetfreezes(inbredsetid) + print "From DB, get %d probesetfreezes" % (len(probesetfreezes)) + for probesetfreeze in probesetfreezes: + correlations(outputdir=outputdir, genos=genos, probesetfreeze=probesetfreeze) + +def correlations(outputdir, genos, probesetfreeze): + print probesetfreeze + probesetfreezeid = probesetfreeze[0] + probesetfreezename = probesetfreeze[1] + probesetfreezefullname = probesetfreeze[2] + # + outputfile = open("%s/%d_%s.txt" % (outputdir, probesetfreezeid, probesetfreezename), "w+") + outputfile.write("%s\t" % "ProbeSet Id") + outputfile.write("%s\t" % "ProbeSet Name") + outputfile.write("%s\t" % "Geno Name") + outputfile.write("%s\t" % "Overlap Number") + outputfile.write("%s\t" % "Pearson r") + outputfile.write("%s\t" % "Pearson p") + outputfile.write("%s\t" % "Spearman r") + outputfile.write("%s\t" % "Spearman p") + outputfile.write("\n") + outputfile.flush() + # + probesetxrefs = probesets.get_probesetxref(probesetfreezeid) + print "Get %d probesetxrefs" % (len(probesetxrefs)) + # + for probesetxref in probesetxrefs: + # + probesetid = probesetxref[0] + probesetdataid = probesetxref[1] + probeset = probesets.get_probeset(probesetid) + probesetname = probeset[1] + probesetdata = probesets.get_probesetdata(probesetdataid) + probesetdata = zip(*probesetdata) + probesetdata = utilities.to_dic([strain.lower() for strain in probesetdata[1]], probesetdata[2]) + # + for geno in genos: + genoname = geno['locus'] + outputfile.write("%s\t" % probesetid) + outputfile.write("%s\t" % probesetname) + outputfile.write("%s\t" % genoname) + # + dic1 = geno['dicvalues'] + dic2 = probesetdata + keys, values1, values2 = utilities.overlap(dic1, dic2) + rs = calculate.correlation(values1, values2) + # + outputfile.write("%s\t" % len(keys)) + outputfile.write("%s\t" % rs[0][0]) + outputfile.write("%s\t" % rs[0][1]) + outputfile.write("%s\t" % rs[1][0]) + outputfile.write("%s\t" % rs[1][1]) + outputfile.write("\n") + outputfile.flush() + # + outputfile.close() + +""" +For: Ash +Date: 2014-02-10 +Function: + For BXD group, calculate correlations with genotypes and probesets. + given probesetfreezes +""" +def bxd_correlations_givenprobesetfreezes(probesetfreezesfile): + # + inbredsetid = 1 + genofile = "/home/leiyan/gn/web/genotypes/BXD.geno" + outputdir = "/home/leiyan/gn2/wqflask/maintenance/dataset/datadir/20140205_Ash_correlations/output" + # + t = genotypes.load_genos(genofile) + genostrains = t[0] + genos = t[1] + print "From geno file, get %d strains" % (len(genostrains)) + print "From geno file, get %d genos" % (len(genos)) + # + file = open(probesetfreezesfile, 'r') + for line in file: + line = line.strip() + cells = line.split() + probesetfreezeid = cells[0] + probesetfreeze = datastructure.get_probesetfreeze(probesetfreezeid) + correlations(outputdir=outputdir, genos=genos, probesetfreeze=probesetfreeze) + file.close() + +bxd_correlations_givenprobesetfreezes('/home/leiyan/gn2/wqflask/maintenance/dataset/datadir/20140205_Ash_correlations/output/probesetfreezes_filter.txt') diff --git a/wqflask/maintenance/dataset/specials3.py b/wqflask/maintenance/dataset/specials3.py new file mode 100644 index 00000000..237df27e --- /dev/null +++ b/wqflask/maintenance/dataset/specials3.py @@ -0,0 +1,117 @@ +import utilities +import datastructure +import genotypes +import probesets +import calculate + +def correlations(outputdir, genos, probesetfreeze): + print probesetfreeze + probesetfreezeid = probesetfreeze[0] + probesetfreezename = probesetfreeze[1] + probesetfreezefullname = probesetfreeze[2] + # + outputfile = open("%s/%d_%s.txt" % (outputdir, probesetfreezeid, probesetfreezename), "w+") + outputfile.write("%s\t" % "ProbeSet Id") + outputfile.write("%s\t" % "ProbeSet Name") + outputfile.write("%s\t" % "Geno Name") + outputfile.write("%s\t" % "Overlap Number") + outputfile.write("%s\t" % "Pearson r") + outputfile.write("%s\t" % "Pearson p") + outputfile.write("%s\t" % "Spearman r") + outputfile.write("%s\t" % "Spearman p") + outputfile.write("\n") + outputfile.flush() + # + probesetxrefs = probesets.get_probesetxref(probesetfreezeid) + print "Get %d probesetxrefs" % (len(probesetxrefs)) + # + for probesetxref in probesetxrefs: + # + probesetid = probesetxref[0] + probesetdataid = probesetxref[1] + probeset = probesets.get_probeset(probesetid) + probesetname = probeset[1] + probesetdata = probesets.get_probesetdata(probesetdataid) + probesetdata = zip(*probesetdata) + probesetdata = utilities.to_dic([strain.lower() for strain in probesetdata[1]], probesetdata[2]) + # + for geno in genos: + genoname = geno['locus'] + outputfile.write("%s\t" % probesetid) + outputfile.write("%s\t" % probesetname) + outputfile.write("%s\t" % genoname) + # + dic1 = geno['dicvalues'] + dic2 = probesetdata + keys, values1, values2 = utilities.overlap(dic1, dic2) + rs = calculate.correlation(values1, values2) + # + outputfile.write("%s\t" % len(keys)) + outputfile.write("%s\t" % rs[0][0]) + outputfile.write("%s\t" % rs[0][1]) + outputfile.write("%s\t" % rs[1][0]) + outputfile.write("%s\t" % rs[1][1]) + outputfile.write("\n") + outputfile.flush() + # + outputfile.close() + +""" +For: Ash +Date: 2014-02-12 +Function: + Generate probeset data files. + given probesetfreeze list. +""" +def generate_probesets(probesetfreezesfile, outputdir): + file = open(probesetfreezesfile, 'r') + for line in file: + line = line.strip() + cells = line.split() + probesetfreezeid = cells[0] + probesetfreeze = datastructure.get_probesetfreeze(probesetfreezeid) + probesetfreezeid = probesetfreeze[0] + probesetfreezename = probesetfreeze[1] + inbredset = datastructure.get_inbredset(probesetfreezeid) + inbredsetid = inbredset[0] + strains = datastructure.get_strains(inbredsetid) + # + outputfile = open("%s/%d_%s.txt" % (outputdir, probesetfreezeid, probesetfreezename), "w+") + outputfile.write("%s\t" % "ProbeSet Id") + outputfile.write("%s\t" % "ProbeSet Name") + outputfile.write('\t'.join([strain[1].upper() for strain in strains])) + outputfile.write("\n") + outputfile.flush() + # + probesetxrefs = probesets.get_probesetxref(probesetfreezeid) + print probesetfreeze + print len(probesetxrefs) + for probesetxref in probesetxrefs: + probesetid = probesetxref[0] + probesetdataid = probesetxref[1] + probeset = probesets.get_probeset(probesetid) + probesetname = probeset[1] + probesetdata = probesets.get_probesetdata(probesetdataid) + probesetdata = zip(*probesetdata) + probesetdata = utilities.to_dic([strain.lower() for strain in probesetdata[1]], probesetdata[2]) + # + outputfile.write("%s\t" % probesetid) + outputfile.write("%s\t" % probesetname) + # + for strain in strains: + strainname = strain[1] + strainname = strainname.lower() + if strainname in probesetdata: + value = probesetdata[strainname] + else: + value = 'x' + outputfile.write("%s\t" % value) + outputfile.write("\n") + outputfile.flush() + # + outputfile.close() + file.close() + +probesetfreezesfile = "/home/leiyan/gn2/wqflask/maintenance/dataset/datadir/20140205_Ash_correlations/output2/probesetfreezes_filter.txt" +outputdir = "/home/leiyan/gn2/wqflask/maintenance/dataset/datadir/20140205_Ash_correlations/output2" +generate_probesets(probesetfreezesfile, outputdir) diff --git a/wqflask/maintenance/dataset/utilities.py b/wqflask/maintenance/dataset/utilities.py new file mode 100644 index 00000000..787c9481 --- /dev/null +++ b/wqflask/maintenance/dataset/utilities.py @@ -0,0 +1,89 @@ +import MySQLdb +import re +import ConfigParser + +def get_cursor(): + host = 'localhost' + user = 'gn2' + passwd = 'UhHJuiS6gC8hj4a' + db = 'db_webqtl' + con = MySQLdb.Connect(db=db, host=host, user=user, passwd=passwd) + cursor = con.cursor() + return cursor, con + +def clearspaces(s, default=None): + if s: + s = re.sub('\s+', ' ', s) + s = s.strip() + return s + else: + return default + +def to_dic(keys, values): + dic = {} + for i in range(len(keys)): + key = keys[i] + value = values[i] + dic[key] = value + return dic + +def overlap(dic1, dic2): + keys = [] + values1 = [] + values2 = [] + for key in dic1.keys(): + if key in dic2: + value1 = dic1[key] + value2 = dic2[key] + if value1 and value2: + keys.append(key) + values1.append(value1) + values2.append(value2) + return keys, values1, values2 + +def to_db_string(s, default): + if s: + s = s.strip() + if len(s) == 0: + return default + elif s == 'x': + return default + else: + return s + else: + return default + +def to_db_float(s, default): + if s: + s = s.strip() + if len(s) == 0: + return default + elif s == 'x': + return default + else: + try: + return float(s) + except: + return default + else: + return default + +def to_db_int(s, default): + if s: + s = s.strip() + if len(s) == 0: + return default + elif s == 'x': + return default + else: + try: + return int(s) + except: + return default + else: + return default + +def get_config(configfile): + config = ConfigParser.ConfigParser() + config.read(configfile) + return config diff --git a/wqflask/maintenance/our_settings.py b/wqflask/maintenance/our_settings.py index 14efe407..b14de960 120000 --- a/wqflask/maintenance/our_settings.py +++ b/wqflask/maintenance/our_settings.py @@ -1 +1 @@ -../../../zach_settings.py
\ No newline at end of file +../../../lei_settings.py
\ No newline at end of file |