diff options
Diffstat (limited to 'wqflask/maintenance/dataset')
20 files changed, 0 insertions, 1223 deletions
diff --git a/wqflask/maintenance/dataset/.gitignore b/wqflask/maintenance/dataset/.gitignore deleted file mode 100644 index 4910042e..00000000 --- a/wqflask/maintenance/dataset/.gitignore +++ /dev/null @@ -1,2 +0,0 @@ -*.pyc -datadir/ diff --git a/wqflask/maintenance/dataset/__init__.py b/wqflask/maintenance/dataset/__init__.py deleted file mode 100644 index e69de29b..00000000 --- a/wqflask/maintenance/dataset/__init__.py +++ /dev/null diff --git a/wqflask/maintenance/dataset/calculate.py b/wqflask/maintenance/dataset/calculate.py deleted file mode 100644 index 6aada827..00000000 --- a/wqflask/maintenance/dataset/calculate.py +++ /dev/null @@ -1,7 +0,0 @@ -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 deleted file mode 100644 index b089e446..00000000 --- a/wqflask/maintenance/dataset/correlations.py +++ /dev/null @@ -1,47 +0,0 @@ -# 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 deleted file mode 100644 index abff371b..00000000 --- a/wqflask/maintenance/dataset/datasampledir/load_genotypes/config.ini +++ /dev/null @@ -1,7 +0,0 @@ -[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 deleted file mode 100644 index 0024ffd1..00000000 --- a/wqflask/maintenance/dataset/datasampledir/load_genotypes/sample.geno +++ /dev/null @@ -1,12 +0,0 @@ -@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 deleted file mode 100644 index 638c3bd8..00000000 --- a/wqflask/maintenance/dataset/datasampledir/load_phenotypes/config.ini +++ /dev/null @@ -1,4 +0,0 @@ -[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 deleted file mode 100644 index a39fd3be..00000000 --- a/wqflask/maintenance/dataset/datasampledir/load_phenotypes/sample_data.txt +++ /dev/null @@ -1,10 +0,0 @@ -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 deleted file mode 100644 index 5172223f..00000000 --- a/wqflask/maintenance/dataset/datasampledir/load_phenotypes/sample_meta.txt +++ /dev/null @@ -1,4 +0,0 @@ -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 deleted file mode 100644 index 7139856f..00000000 --- a/wqflask/maintenance/dataset/datastructure.py +++ /dev/null @@ -1,155 +0,0 @@ -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 deleted file mode 100644 index fcb2d2d8..00000000 --- a/wqflask/maintenance/dataset/fetch.py +++ /dev/null @@ -1,11 +0,0 @@ -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 deleted file mode 100644 index b57d4651..00000000 --- a/wqflask/maintenance/dataset/genotypes.py +++ /dev/null @@ -1,48 +0,0 @@ -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 deleted file mode 100644 index 4697382b..00000000 --- a/wqflask/maintenance/dataset/load_genotypes.py +++ /dev/null @@ -1,157 +0,0 @@ -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 deleted file mode 100644 index 27e340f8..00000000 --- a/wqflask/maintenance/dataset/load_phenotypes.py +++ /dev/null @@ -1,171 +0,0 @@ -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 deleted file mode 100644 index ed30f33f..00000000 --- a/wqflask/maintenance/dataset/phenotypes.py +++ /dev/null @@ -1,100 +0,0 @@ -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 deleted file mode 100644 index 97bb5bdf..00000000 --- a/wqflask/maintenance/dataset/probesets.py +++ /dev/null @@ -1,90 +0,0 @@ -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 deleted file mode 100644 index 9159fd7f..00000000 --- a/wqflask/maintenance/dataset/specials1.py +++ /dev/null @@ -1,53 +0,0 @@ -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 deleted file mode 100644 index 2438af43..00000000 --- a/wqflask/maintenance/dataset/specials2.py +++ /dev/null @@ -1,139 +0,0 @@ -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 deleted file mode 100644 index 237df27e..00000000 --- a/wqflask/maintenance/dataset/specials3.py +++ /dev/null @@ -1,117 +0,0 @@ -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 deleted file mode 100644 index 787c9481..00000000 --- a/wqflask/maintenance/dataset/utilities.py +++ /dev/null @@ -1,89 +0,0 @@ -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 |