#####################################
######  	 ABOUT THIS SCRIPY 	    #
#####################################
# This script reads the results of sequenced barcodes of tissue and execute them to an SQLite3 database.
# URL to retreive gene sequencing data for CCA tissue:
# https://tcga-data.nci.nih.gov/tcga/tcgaCancerDetails.jsp?diseaseType=CHOL&diseaseName=Cholangiocarcinoma


#####################################
######  INSTRUCTIONS FOR USE:		#
#####################################
#In the shell go to this directory and type 'python analize.py' to run the script.
# -- It seems to run much faster this way, than to run it in Sublime Text 2, in my experience.
# check out the README for more information..


#####################################
import csv		#this is a library used to read files in
import sqlite3 as lite	# these libraries will help us write our info to a database. easiest to read in.

#setting up the outfile for out results!
con = lite.connect('/Users/elenka/Documents/larriscku/geneSequenceResults.db'); #initilazing output file
cur = con.cursor() #save cursor to var
cur.execute("DROP TABLE IF EXISTS genes") #if this table doesn't exist, create it. SQLITE3
cur.execute("CREATE TABLE genes (geneid text, normcount real, barcode text, tissuetype text)")# determine the vals in each col of table
cur.execute("DROP TABLE IF EXISTS isoforms"); # a second table
cur.execute("CREATE TABLE isoforms (geneid text, isoformid text, normcount real, barcode text, tissuetype text)")

# we will fill this "Cabinet" list with objects of class TissueSample..
cabinet = [] # array will hold TissueSample objects, holding a tissue barcode and it's data.
tissueTypeDict = {}; #this will be the reference for when we find which samples as cancerous... further down.

# file path's are relative to where the py script is saved. so they could cause problems if you aren't lookin the right place.
manifestFile = "/Users/elenka/Documents/larriscku/file_manifest.txt"; #this is the manifest file path. it has barcode names and filenames.
pathToDataFiles = "/Users/elenka/Documents/larriscku/RNASeqV2/UNC__IlluminaHiSeq_RNASeqV2/Level_3/";#where the files are,will append to filenames from manifest.
filenameKeywords = ["genes.results","genes.normalized","isoforms.normalized"]; # the files scientist wants for his study.
tissue_type_file = "tissuetype.csv"; #file made by scientist to match tissue type and sample name.
output_genes = 'genes_table.tsv'; #output file for easy read and access.
output_isoforms = 'isoforms_table.tsv';

########################################################
#Reading Samples: Tissue Types. Adding to dictionary.###
########################################################
with open(tissue_type_file, 'rt') as f:
	reader = csv.reader(f, delimiter="\t")
	#print("\n\nReading in tissue type, line by line...")
	##print "current case:", currCase, "current tissue:", tissue
	ctr = 0;
	for line in reader:
		#print(line)
		tissueTypeDict[line[0]] = line[1];
		ctr = ctr + 1;
	#print("Done!")

tmpList = []; #just preloading. this will be used to populate the db file! see the class
isoCount = 0;


########################################################
# Defining Attributes and Functions for Tissue Samples #
########################################################

#This function allows you to query the 45 sample for a certain gene.
# Will output all of it's isoforms and their expressions in cancerous and non cancerous tissues.
def search_isoform_expressions_by_gene(genename):
	cur.execute("SELECT * FROM genes") # you can also select from genes to see the other table!
	#for row in cur:
		#print(row)




# This is a class definition, an object called TissueSample that will get information using functions, and storing it in its attributes
class TissueSample:
	def __init__(self,barcode): # function initializes class, must pass in a barcode and tissue type.
		self.barcode = barcode;	#see example below class definition
		self.sample = barcode[:15] #the sample is first 15 char, see manifest for example.
		self.tissue_type = tissueTypeDict[self.sample]; #find matching tissue type.
		#print("\tFound tissue type:", self.barcode, self.tissue_type, "Added to cabinet!")

	#attributes for the TissueSample. These hold the filenames for data, and will hold data themselves.
	genes_results = {"gene_id":"multiple_transcript_ids", #attribute  with dictionary
					"filepath": ""} #currently set at NULL, and genes_results['filename'] is considered NULL.
	genes_norms = 	{"gene_id":"normalized_count", #from genes.normalized
					"filepath": ""}
	isoforms_norms = {"isoform_id":"normalized_count", #from gene.results, these are the isoforms and their expressions per gene.
						"filepath": ""}
	tissue_type = "none";
# you can add attributes (or attr's) to your cases by defining (or def) functions to input
# learned from: http://sthurlow.com/python/lesson08/
#functions to add attribute to the object....

	def filename_function_switch(self, keyword):
		if "genes.normalized" in keyword: #this function should really cycle through all the keywords that are inputted, not just the three hardcoded.
			self.add_genes_norms(keyword)
		elif "genes.results" in keyword:
			self.get_genes_results(keyword);
		elif "isoforms.normalized" in keyword:
			self.get_isoforms_norms(keyword);

	def add_genes_norms(self,filename): #read in 1 of 3 files, get information to execute immediately..
		path = pathToDataFiles + filename;
		self.genes_norms['filepath'] = path;
		#print("\tAdded filepath to TissueSample.")
		with open(output_genes, 'w') as p: #this is to write formatted output
			writer = csv.writer(p, delimiter='\t')
			with open(path, 'rt') as f: #this is to read the genes_norms file
				#print("\tReading in genes normalized results and executing to DB.")
				reader = csv.reader(f, delimiter="\t")
				for line in reader:
					if line[0] == 'gene_id': #removing unnecces
						tmpList = [];
						tmpList.extend([line[0],line[1],'barcode','tissue_type']);
					else:
						tmpList = [];
						tmpList.extend([line[0],line[1],self.barcode,self.tissue_type]);
					writer.writerow(tmpList)
					cur.execute('INSERT INTO genes VALUES (?,?,?,?)', tmpList) #here i'm actually putting things into the DB.

	def get_isoforms_norms(self,filename): #read in 1 of 3 files, store information for later...
		path = pathToDataFiles + filename;# filename passed from manifest needs it's path prepended.
		self.isoforms_norms['filepath'] = path; 			# so we use functions for the name, but when adding genes
		#print("\tAdded filepath to TissueSample.")
		with open(path, 'rt') as f: #use the file we read in to get all its necessary contents
			#print("\tReading in isoforms norm results and storing in TissueSample.")
			reader = csv.reader(f, delimiter="\t")
			for line in reader:
				self.isoforms_norms[line[0]] = line[1] #add this data to the dict attribute above
		if (self.genes_results['filepath']): #both files are GO.
			self.add_isoforms_ids(); #call function to write to DB.

	def get_genes_results(self,filename): #read in 1 of 3 files, store information for later...
		path = pathToDataFiles + filename;
		self.genes_results['filepath'] = path;
		#print("\tAdded filepath to TissueSample.")
		with open(path, 'rt') as f: #this is using the csv library to read the file into python
			#print("\tReading in gene results and adding to TissueSample.")
			reader = csv.reader(f, delimiter="\t") #telling python to read it by every tab or \t
			for line in reader: #we are reading one line of the file at a time. the value 'line' is a list.
				arrayify = line[3].split(",") #split() makes a list out of transcript ids in the 3rd column by seperating by comma.
				self.genes_results[line[0]] = arrayify; #this dictionary will have the gene key match to a list of isoforms.
		if (self.isoforms_norms['filepath']): # if corresponding file has been read in...
			self.add_isoforms_ids(); #call this function which will write to DB.

	def add_isoforms_ids(self):
		#print("\tReading in genes and their isoforms ids and normcounts.")
		#print("\tWriting to Database under table 'isoforms'.")
		with open(output_isoforms, 'w') as p:
			writer = csv.writer(p, delimiter='\t')
			for key, val in self.genes_results.items():
				for x in val:
					isoCount = self.isoforms_norms.get(x, None);
					tmpList = []; #empty the list again.
					tmpList.extend([key, x, isoCount, self.barcode, self.tissue_type]); #populate the list with our output: barcode,gene.isoform.isoform count.
					writer.writerow(tmpList) #write to txt file
					cur.execute('INSERT INTO isoforms VALUES (?,?,?,?, ?)', tmpList) #write to DB.

#############################
#Reading in the file_manifest
#############################
fileInCabinet = False; #this boolean is to let us know if we found the barcode name in the cabinet.
importantLine = False; #this boolean is to let us know if the line we read is important!
placeHolder = -1; #if the barcode is in the cabinet, then this number will be it's index.
i = 0; #count lines read by script.

with open(manifestFile, 'rt') as f:
	reader = csv.reader(f, delimiter="\t")
	#print("Reading in file_manifest, line by line...")
	for line in reader:
		i = i+1 #update count!
		for name in filenameKeywords:
			if name in line[6]:#remove top 3 rows, and also two quantification filename_barcodes per barcode b/c unnecessary
				#print("\nLine", i,"from manifest. barcode:", line[5], "\n\tfilename snippet: ",line[6].split("rsem.",1)[1])
				importantLine = True;
				for l in cabinet: #search the cabinet
					if (line[5] == l.barcode): #the barcode is already in cabinet.
						placeHolder = cabinet.index(l); #find position of the TissueSample
						fileInCabinet = True;
						#print("\tFound the TissueSample in cabinet! placeHolder: ", placeHolder)
								#this needs to be after the for loop searching cabinet.
								#b/c we search the whole cabinet for the file.
				if (fileInCabinet == True): # the barcode is already there!
					cabinet[placeHolder].filename_function_switch(line[6])
					fileInCabinet = False #now search is done, reset this indicator for next barcode.
				else: #otherwise, the barcode is not in Cabinet.
					x = TissueSample(line[5]) #We make an new instance of the object, with tissue sample.
					x.filename_function_switch(line[6])

					cabinet.append(x) #and add it to the cabinet.
		if (importantLine == True):
			importantLine = False;
		#else:
			#print("\nLine",i,"does not contain a barcode or proper filename\n")

#########################################
#####	#Printing stuff to console #######
#########################################
#print("Now #printing genes table to console from DB... They have already been #printed to geneSequenceResults.db in this directory.")
#print("\tThis will take about 10-15 minutes... ")
									#this is basic mysqlite and it's pretty simple!
#cur.execute("SELECT * FROM genes") # you can also select from genes to see the other table!
#for row in cur:
	#print(row)

#print("Above is the genes table from database #printed out to console.")
#print('-' * 55)
#print("Now #printing isoforms table to console from DB")

#cur.execute('SELECT * FROM isoforms')
#for row in cur:
#	print(row)

#print("Above is the isoforms and genes table from database #printed out to console.")
#print('-' * 55)


con.commit() #commit all the additions to our Database. This is what takes so long... i think.
con.close()

2588bd38aea86e78a069933517ad9c4863181a06
<mask token>


class TissueSample:
    <mask token>
    <mask token>
    <mask token>
    <mask token>
    <mask token>
    <mask token>

    def add_genes_norms(self, filename):
        path = pathToDataFiles + filename
        self.genes_norms['filepath'] = path
        with open(output_genes, 'w') as p:
            writer = csv.writer(p, delimiter='\t')
            with open(path, 'rt') as f:
                reader = csv.reader(f, delimiter='\t')
                for line in reader:
                    if line[0] == 'gene_id':
                        tmpList = []
                        tmpList.extend([line[0], line[1], 'barcode',
                            'tissue_type'])
                    else:
                        tmpList = []
                        tmpList.extend([line[0], line[1], self.barcode,
                            self.tissue_type])
                    writer.writerow(tmpList)
                    cur.execute('INSERT INTO genes VALUES (?,?,?,?)', tmpList)

    def get_isoforms_norms(self, filename):
        path = pathToDataFiles + filename
        self.isoforms_norms['filepath'] = path
        with open(path, 'rt') as f:
            reader = csv.reader(f, delimiter='\t')
            for line in reader:
                self.isoforms_norms[line[0]] = line[1]
        if self.genes_results['filepath']:
            self.add_isoforms_ids()

    def get_genes_results(self, filename):
        path = pathToDataFiles + filename
        self.genes_results['filepath'] = path
        with open(path, 'rt') as f:
            reader = csv.reader(f, delimiter='\t')
            for line in reader:
                arrayify = line[3].split(',')
                self.genes_results[line[0]] = arrayify
        if self.isoforms_norms['filepath']:
            self.add_isoforms_ids()

    def add_isoforms_ids(self):
        with open(output_isoforms, 'w') as p:
            writer = csv.writer(p, delimiter='\t')
            for key, val in self.genes_results.items():
                for x in val:
                    isoCount = self.isoforms_norms.get(x, None)
                    tmpList = []
                    tmpList.extend([key, x, isoCount, self.barcode, self.
                        tissue_type])
                    writer.writerow(tmpList)
                    cur.execute('INSERT INTO isoforms VALUES (?,?,?,?, ?)',
                        tmpList)


<mask token>

<mask token>


class TissueSample:

    def __init__(self, barcode):
        self.barcode = barcode
        self.sample = barcode[:15]
        self.tissue_type = tissueTypeDict[self.sample]
    <mask token>
    <mask token>
    <mask token>
    <mask token>

    def filename_function_switch(self, keyword):
        if 'genes.normalized' in keyword:
            self.add_genes_norms(keyword)
        elif 'genes.results' in keyword:
            self.get_genes_results(keyword)
        elif 'isoforms.normalized' in keyword:
            self.get_isoforms_norms(keyword)

    def add_genes_norms(self, filename):
        path = pathToDataFiles + filename
        self.genes_norms['filepath'] = path
        with open(output_genes, 'w') as p:
            writer = csv.writer(p, delimiter='\t')
            with open(path, 'rt') as f:
                reader = csv.reader(f, delimiter='\t')
                for line in reader:
                    if line[0] == 'gene_id':
                        tmpList = []
                        tmpList.extend([line[0], line[1], 'barcode',
                            'tissue_type'])
                    else:
                        tmpList = []
                        tmpList.extend([line[0], line[1], self.barcode,
                            self.tissue_type])
                    writer.writerow(tmpList)
                    cur.execute('INSERT INTO genes VALUES (?,?,?,?)', tmpList)

    def get_isoforms_norms(self, filename):
        path = pathToDataFiles + filename
        self.isoforms_norms['filepath'] = path
        with open(path, 'rt') as f:
            reader = csv.reader(f, delimiter='\t')
            for line in reader:
                self.isoforms_norms[line[0]] = line[1]
        if self.genes_results['filepath']:
            self.add_isoforms_ids()

    def get_genes_results(self, filename):
        path = pathToDataFiles + filename
        self.genes_results['filepath'] = path
        with open(path, 'rt') as f:
            reader = csv.reader(f, delimiter='\t')
            for line in reader:
                arrayify = line[3].split(',')
                self.genes_results[line[0]] = arrayify
        if self.isoforms_norms['filepath']:
            self.add_isoforms_ids()

    def add_isoforms_ids(self):
        with open(output_isoforms, 'w') as p:
            writer = csv.writer(p, delimiter='\t')
            for key, val in self.genes_results.items():
                for x in val:
                    isoCount = self.isoforms_norms.get(x, None)
                    tmpList = []
                    tmpList.extend([key, x, isoCount, self.barcode, self.
                        tissue_type])
                    writer.writerow(tmpList)
                    cur.execute('INSERT INTO isoforms VALUES (?,?,?,?, ?)',
                        tmpList)


<mask token>

<mask token>
cur.execute('DROP TABLE IF EXISTS genes')
cur.execute(
    'CREATE TABLE genes (geneid text, normcount real, barcode text, tissuetype text)'
    )
cur.execute('DROP TABLE IF EXISTS isoforms')
cur.execute(
    'CREATE TABLE isoforms (geneid text, isoformid text, normcount real, barcode text, tissuetype text)'
    )
<mask token>
with open(tissue_type_file, 'rt') as f:
    reader = csv.reader(f, delimiter='\t')
    ctr = 0
    for line in reader:
        tissueTypeDict[line[0]] = line[1]
        ctr = ctr + 1
<mask token>


def search_isoform_expressions_by_gene(genename):
    cur.execute('SELECT * FROM genes')


class TissueSample:

    def __init__(self, barcode):
        self.barcode = barcode
        self.sample = barcode[:15]
        self.tissue_type = tissueTypeDict[self.sample]
    genes_results = {'gene_id': 'multiple_transcript_ids', 'filepath': ''}
    genes_norms = {'gene_id': 'normalized_count', 'filepath': ''}
    isoforms_norms = {'isoform_id': 'normalized_count', 'filepath': ''}
    tissue_type = 'none'

    def filename_function_switch(self, keyword):
        if 'genes.normalized' in keyword:
            self.add_genes_norms(keyword)
        elif 'genes.results' in keyword:
            self.get_genes_results(keyword)
        elif 'isoforms.normalized' in keyword:
            self.get_isoforms_norms(keyword)

    def add_genes_norms(self, filename):
        path = pathToDataFiles + filename
        self.genes_norms['filepath'] = path
        with open(output_genes, 'w') as p:
            writer = csv.writer(p, delimiter='\t')
            with open(path, 'rt') as f:
                reader = csv.reader(f, delimiter='\t')
                for line in reader:
                    if line[0] == 'gene_id':
                        tmpList = []
                        tmpList.extend([line[0], line[1], 'barcode',
                            'tissue_type'])
                    else:
                        tmpList = []
                        tmpList.extend([line[0], line[1], self.barcode,
                            self.tissue_type])
                    writer.writerow(tmpList)
                    cur.execute('INSERT INTO genes VALUES (?,?,?,?)', tmpList)

    def get_isoforms_norms(self, filename):
        path = pathToDataFiles + filename
        self.isoforms_norms['filepath'] = path
        with open(path, 'rt') as f:
            reader = csv.reader(f, delimiter='\t')
            for line in reader:
                self.isoforms_norms[line[0]] = line[1]
        if self.genes_results['filepath']:
            self.add_isoforms_ids()

    def get_genes_results(self, filename):
        path = pathToDataFiles + filename
        self.genes_results['filepath'] = path
        with open(path, 'rt') as f:
            reader = csv.reader(f, delimiter='\t')
            for line in reader:
                arrayify = line[3].split(',')
                self.genes_results[line[0]] = arrayify
        if self.isoforms_norms['filepath']:
            self.add_isoforms_ids()

    def add_isoforms_ids(self):
        with open(output_isoforms, 'w') as p:
            writer = csv.writer(p, delimiter='\t')
            for key, val in self.genes_results.items():
                for x in val:
                    isoCount = self.isoforms_norms.get(x, None)
                    tmpList = []
                    tmpList.extend([key, x, isoCount, self.barcode, self.
                        tissue_type])
                    writer.writerow(tmpList)
                    cur.execute('INSERT INTO isoforms VALUES (?,?,?,?, ?)',
                        tmpList)


<mask token>
with open(manifestFile, 'rt') as f:
    reader = csv.reader(f, delimiter='\t')
    for line in reader:
        i = i + 1
        for name in filenameKeywords:
            if name in line[6]:
                importantLine = True
                for l in cabinet:
                    if line[5] == l.barcode:
                        placeHolder = cabinet.index(l)
                        fileInCabinet = True
                if fileInCabinet == True:
                    cabinet[placeHolder].filename_function_switch(line[6])
                    fileInCabinet = False
                else:
                    x = TissueSample(line[5])
                    x.filename_function_switch(line[6])
                    cabinet.append(x)
        if importantLine == True:
            importantLine = False
con.commit()
con.close()

<mask token>
con = lite.connect('/Users/elenka/Documents/larriscku/geneSequenceResults.db')
cur = con.cursor()
cur.execute('DROP TABLE IF EXISTS genes')
cur.execute(
    'CREATE TABLE genes (geneid text, normcount real, barcode text, tissuetype text)'
    )
cur.execute('DROP TABLE IF EXISTS isoforms')
cur.execute(
    'CREATE TABLE isoforms (geneid text, isoformid text, normcount real, barcode text, tissuetype text)'
    )
cabinet = []
tissueTypeDict = {}
manifestFile = '/Users/elenka/Documents/larriscku/file_manifest.txt'
pathToDataFiles = (
    '/Users/elenka/Documents/larriscku/RNASeqV2/UNC__IlluminaHiSeq_RNASeqV2/Level_3/'
    )
filenameKeywords = ['genes.results', 'genes.normalized', 'isoforms.normalized']
tissue_type_file = 'tissuetype.csv'
output_genes = 'genes_table.tsv'
output_isoforms = 'isoforms_table.tsv'
with open(tissue_type_file, 'rt') as f:
    reader = csv.reader(f, delimiter='\t')
    ctr = 0
    for line in reader:
        tissueTypeDict[line[0]] = line[1]
        ctr = ctr + 1
tmpList = []
isoCount = 0


def search_isoform_expressions_by_gene(genename):
    cur.execute('SELECT * FROM genes')


class TissueSample:

    def __init__(self, barcode):
        self.barcode = barcode
        self.sample = barcode[:15]
        self.tissue_type = tissueTypeDict[self.sample]
    genes_results = {'gene_id': 'multiple_transcript_ids', 'filepath': ''}
    genes_norms = {'gene_id': 'normalized_count', 'filepath': ''}
    isoforms_norms = {'isoform_id': 'normalized_count', 'filepath': ''}
    tissue_type = 'none'

    def filename_function_switch(self, keyword):
        if 'genes.normalized' in keyword:
            self.add_genes_norms(keyword)
        elif 'genes.results' in keyword:
            self.get_genes_results(keyword)
        elif 'isoforms.normalized' in keyword:
            self.get_isoforms_norms(keyword)

    def add_genes_norms(self, filename):
        path = pathToDataFiles + filename
        self.genes_norms['filepath'] = path
        with open(output_genes, 'w') as p:
            writer = csv.writer(p, delimiter='\t')
            with open(path, 'rt') as f:
                reader = csv.reader(f, delimiter='\t')
                for line in reader:
                    if line[0] == 'gene_id':
                        tmpList = []
                        tmpList.extend([line[0], line[1], 'barcode',
                            'tissue_type'])
                    else:
                        tmpList = []
                        tmpList.extend([line[0], line[1], self.barcode,
                            self.tissue_type])
                    writer.writerow(tmpList)
                    cur.execute('INSERT INTO genes VALUES (?,?,?,?)', tmpList)

    def get_isoforms_norms(self, filename):
        path = pathToDataFiles + filename
        self.isoforms_norms['filepath'] = path
        with open(path, 'rt') as f:
            reader = csv.reader(f, delimiter='\t')
            for line in reader:
                self.isoforms_norms[line[0]] = line[1]
        if self.genes_results['filepath']:
            self.add_isoforms_ids()

    def get_genes_results(self, filename):
        path = pathToDataFiles + filename
        self.genes_results['filepath'] = path
        with open(path, 'rt') as f:
            reader = csv.reader(f, delimiter='\t')
            for line in reader:
                arrayify = line[3].split(',')
                self.genes_results[line[0]] = arrayify
        if self.isoforms_norms['filepath']:
            self.add_isoforms_ids()

    def add_isoforms_ids(self):
        with open(output_isoforms, 'w') as p:
            writer = csv.writer(p, delimiter='\t')
            for key, val in self.genes_results.items():
                for x in val:
                    isoCount = self.isoforms_norms.get(x, None)
                    tmpList = []
                    tmpList.extend([key, x, isoCount, self.barcode, self.
                        tissue_type])
                    writer.writerow(tmpList)
                    cur.execute('INSERT INTO isoforms VALUES (?,?,?,?, ?)',
                        tmpList)


fileInCabinet = False
importantLine = False
placeHolder = -1
i = 0
with open(manifestFile, 'rt') as f:
    reader = csv.reader(f, delimiter='\t')
    for line in reader:
        i = i + 1
        for name in filenameKeywords:
            if name in line[6]:
                importantLine = True
                for l in cabinet:
                    if line[5] == l.barcode:
                        placeHolder = cabinet.index(l)
                        fileInCabinet = True
                if fileInCabinet == True:
                    cabinet[placeHolder].filename_function_switch(line[6])
                    fileInCabinet = False
                else:
                    x = TissueSample(line[5])
                    x.filename_function_switch(line[6])
                    cabinet.append(x)
        if importantLine == True:
            importantLine = False
con.commit()
con.close()

#####################################
######  	 ABOUT THIS SCRIPY 	    #
#####################################
# This script reads the results of sequenced barcodes of tissue and execute them to an SQLite3 database.
# URL to retreive gene sequencing data for CCA tissue:
# https://tcga-data.nci.nih.gov/tcga/tcgaCancerDetails.jsp?diseaseType=CHOL&diseaseName=Cholangiocarcinoma


#####################################
######  INSTRUCTIONS FOR USE:		#
#####################################
#In the shell go to this directory and type 'python analize.py' to run the script.
# -- It seems to run much faster this way, than to run it in Sublime Text 2, in my experience.
# check out the README for more information..


#####################################
import csv		#this is a library used to read files in
import sqlite3 as lite	# these libraries will help us write our info to a database. easiest to read in.

#setting up the outfile for out results!
con = lite.connect('/Users/elenka/Documents/larriscku/geneSequenceResults.db'); #initilazing output file
cur = con.cursor() #save cursor to var
cur.execute("DROP TABLE IF EXISTS genes") #if this table doesn't exist, create it. SQLITE3
cur.execute("CREATE TABLE genes (geneid text, normcount real, barcode text, tissuetype text)")# determine the vals in each col of table
cur.execute("DROP TABLE IF EXISTS isoforms"); # a second table
cur.execute("CREATE TABLE isoforms (geneid text, isoformid text, normcount real, barcode text, tissuetype text)")

# we will fill this "Cabinet" list with objects of class TissueSample..
cabinet = [] # array will hold TissueSample objects, holding a tissue barcode and it's data.
tissueTypeDict = {}; #this will be the reference for when we find which samples as cancerous... further down.

# file path's are relative to where the py script is saved. so they could cause problems if you aren't lookin the right place.
manifestFile = "/Users/elenka/Documents/larriscku/file_manifest.txt"; #this is the manifest file path. it has barcode names and filenames.
pathToDataFiles = "/Users/elenka/Documents/larriscku/RNASeqV2/UNC__IlluminaHiSeq_RNASeqV2/Level_3/";#where the files are,will append to filenames from manifest.
filenameKeywords = ["genes.results","genes.normalized","isoforms.normalized"]; # the files scientist wants for his study.
tissue_type_file = "tissuetype.csv"; #file made by scientist to match tissue type and sample name.
output_genes = 'genes_table.tsv'; #output file for easy read and access.
output_isoforms = 'isoforms_table.tsv';

########################################################
#Reading Samples: Tissue Types. Adding to dictionary.###
########################################################
with open(tissue_type_file, 'rt') as f:
	reader = csv.reader(f, delimiter="\t")
	#print("\n\nReading in tissue type, line by line...")
	##print "current case:", currCase, "current tissue:", tissue
	ctr = 0;
	for line in reader:
		#print(line)
		tissueTypeDict[line[0]] = line[1];
		ctr = ctr + 1;
	#print("Done!")

tmpList = []; #just preloading. this will be used to populate the db file! see the class
isoCount = 0;


########################################################
# Defining Attributes and Functions for Tissue Samples #
########################################################

#This function allows you to query the 45 sample for a certain gene.
# Will output all of it's isoforms and their expressions in cancerous and non cancerous tissues.
def search_isoform_expressions_by_gene(genename):
	cur.execute("SELECT * FROM genes") # you can also select from genes to see the other table!
	#for row in cur:
		#print(row)




# This is a class definition, an object called TissueSample that will get information using functions, and storing it in its attributes
class TissueSample:
	def __init__(self,barcode): # function initializes class, must pass in a barcode and tissue type.
		self.barcode = barcode;	#see example below class definition
		self.sample = barcode[:15] #the sample is first 15 char, see manifest for example.
		self.tissue_type = tissueTypeDict[self.sample]; #find matching tissue type.
		#print("\tFound tissue type:", self.barcode, self.tissue_type, "Added to cabinet!")

	#attributes for the TissueSample. These hold the filenames for data, and will hold data themselves.
	genes_results = {"gene_id":"multiple_transcript_ids", #attribute  with dictionary
					"filepath": ""} #currently set at NULL, and genes_results['filename'] is considered NULL.
	genes_norms = 	{"gene_id":"normalized_count", #from genes.normalized
					"filepath": ""}
	isoforms_norms = {"isoform_id":"normalized_count", #from gene.results, these are the isoforms and their expressions per gene.
						"filepath": ""}
	tissue_type = "none";
# you can add attributes (or attr's) to your cases by defining (or def) functions to input
# learned from: http://sthurlow.com/python/lesson08/
#functions to add attribute to the object....

	def filename_function_switch(self, keyword):
		if "genes.normalized" in keyword: #this function should really cycle through all the keywords that are inputted, not just the three hardcoded.
			self.add_genes_norms(keyword)
		elif "genes.results" in keyword:
			self.get_genes_results(keyword);
		elif "isoforms.normalized" in keyword:
			self.get_isoforms_norms(keyword);

	def add_genes_norms(self,filename): #read in 1 of 3 files, get information to execute immediately..
		path = pathToDataFiles + filename;
		self.genes_norms['filepath'] = path;
		#print("\tAdded filepath to TissueSample.")
		with open(output_genes, 'w') as p: #this is to write formatted output
			writer = csv.writer(p, delimiter='\t')
			with open(path, 'rt') as f: #this is to read the genes_norms file
				#print("\tReading in genes normalized results and executing to DB.")
				reader = csv.reader(f, delimiter="\t")
				for line in reader:
					if line[0] == 'gene_id': #removing unnecces
						tmpList = [];
						tmpList.extend([line[0],line[1],'barcode','tissue_type']);
					else:
						tmpList = [];
						tmpList.extend([line[0],line[1],self.barcode,self.tissue_type]);
					writer.writerow(tmpList)
					cur.execute('INSERT INTO genes VALUES (?,?,?,?)', tmpList) #here i'm actually putting things into the DB.

	def get_isoforms_norms(self,filename): #read in 1 of 3 files, store information for later...
		path = pathToDataFiles + filename;# filename passed from manifest needs it's path prepended.
		self.isoforms_norms['filepath'] = path; 			# so we use functions for the name, but when adding genes
		#print("\tAdded filepath to TissueSample.")
		with open(path, 'rt') as f: #use the file we read in to get all its necessary contents
			#print("\tReading in isoforms norm results and storing in TissueSample.")
			reader = csv.reader(f, delimiter="\t")
			for line in reader:
				self.isoforms_norms[line[0]] = line[1] #add this data to the dict attribute above
		if (self.genes_results['filepath']): #both files are GO.
			self.add_isoforms_ids(); #call function to write to DB.

	def get_genes_results(self,filename): #read in 1 of 3 files, store information for later...
		path = pathToDataFiles + filename;
		self.genes_results['filepath'] = path;
		#print("\tAdded filepath to TissueSample.")
		with open(path, 'rt') as f: #this is using the csv library to read the file into python
			#print("\tReading in gene results and adding to TissueSample.")
			reader = csv.reader(f, delimiter="\t") #telling python to read it by every tab or \t
			for line in reader: #we are reading one line of the file at a time. the value 'line' is a list.
				arrayify = line[3].split(",") #split() makes a list out of transcript ids in the 3rd column by seperating by comma.
				self.genes_results[line[0]] = arrayify; #this dictionary will have the gene key match to a list of isoforms.
		if (self.isoforms_norms['filepath']): # if corresponding file has been read in...
			self.add_isoforms_ids(); #call this function which will write to DB.

	def add_isoforms_ids(self):
		#print("\tReading in genes and their isoforms ids and normcounts.")
		#print("\tWriting to Database under table 'isoforms'.")
		with open(output_isoforms, 'w') as p:
			writer = csv.writer(p, delimiter='\t')
			for key, val in self.genes_results.items():
				for x in val:
					isoCount = self.isoforms_norms.get(x, None);
					tmpList = []; #empty the list again.
					tmpList.extend([key, x, isoCount, self.barcode, self.tissue_type]); #populate the list with our output: barcode,gene.isoform.isoform count.
					writer.writerow(tmpList) #write to txt file
					cur.execute('INSERT INTO isoforms VALUES (?,?,?,?, ?)', tmpList) #write to DB.

#############################
#Reading in the file_manifest
#############################
fileInCabinet = False; #this boolean is to let us know if we found the barcode name in the cabinet.
importantLine = False; #this boolean is to let us know if the line we read is important!
placeHolder = -1; #if the barcode is in the cabinet, then this number will be it's index.
i = 0; #count lines read by script.

with open(manifestFile, 'rt') as f:
	reader = csv.reader(f, delimiter="\t")
	#print("Reading in file_manifest, line by line...")
	for line in reader:
		i = i+1 #update count!
		for name in filenameKeywords:
			if name in line[6]:#remove top 3 rows, and also two quantification filename_barcodes per barcode b/c unnecessary
				#print("\nLine", i,"from manifest. barcode:", line[5], "\n\tfilename snippet: ",line[6].split("rsem.",1)[1])
				importantLine = True;
				for l in cabinet: #search the cabinet
					if (line[5] == l.barcode): #the barcode is already in cabinet.
						placeHolder = cabinet.index(l); #find position of the TissueSample
						fileInCabinet = True;
						#print("\tFound the TissueSample in cabinet! placeHolder: ", placeHolder)
								#this needs to be after the for loop searching cabinet.
								#b/c we search the whole cabinet for the file.
				if (fileInCabinet == True): # the barcode is already there!
					cabinet[placeHolder].filename_function_switch(line[6])
					fileInCabinet = False #now search is done, reset this indicator for next barcode.
				else: #otherwise, the barcode is not in Cabinet.
					x = TissueSample(line[5]) #We make an new instance of the object, with tissue sample.
					x.filename_function_switch(line[6])

					cabinet.append(x) #and add it to the cabinet.
		if (importantLine == True):
			importantLine = False;
		#else:
			#print("\nLine",i,"does not contain a barcode or proper filename\n")

#########################################
#####	#Printing stuff to console #######
#########################################
#print("Now #printing genes table to console from DB... They have already been #printed to geneSequenceResults.db in this directory.")
#print("\tThis will take about 10-15 minutes... ")
									#this is basic mysqlite and it's pretty simple!
#cur.execute("SELECT * FROM genes") # you can also select from genes to see the other table!
#for row in cur:
	#print(row)

#print("Above is the genes table from database #printed out to console.")
#print('-' * 55)
#print("Now #printing isoforms table to console from DB")

#cur.execute('SELECT * FROM isoforms')
#for row in cur:
#	print(row)

#print("Above is the isoforms and genes table from database #printed out to console.")
#print('-' * 55)


con.commit() #commit all the additions to our Database. This is what takes so long... i think.
con.close()
