Merge /home/lei/gene

20 files changed, 1223 insertions, 0 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