def ficheexperiment(request):
experiment_id = request.GET.get('experiment', '')
project_id = ''
if experiment_id != '':
#verification de CATMA ou CTMA ou RNASA
requete = "SELECT experiment.analysis_type FROM chips.experiment where experiment.experiment_id='" + experiment_id + "';"
print(getdata(requete)[1][0][0])
if getdata(requete)[1][0][0] == 'RNA-Seq':
if experiment_id != '':
requete = "SELECT experiment.project_id FROM chips.experiment where experiment.experiment_id='" + experiment_id + "';"
project_id = getdata(requete)[1][0][0]
echantilon = sampling_get(experiment_id)
sortage_link = '/CATdb/ftp/'
return render(
request, 'CATdb/ficheexperiment.html', {
'titre_page': 'File_experiment',
'sortage_link': sortage_link,
'experiment': experiment_id,
'project': project_id,
'sample': echantilon
})
else:
return HttpResponseRedirect(
'http://urgv.evry.inra.fr/cgi-bin/projects/CATdb/consult_expce.pl?experiment_id='
+ experiment_id)
else:
return HttpResponseRedirect(
'http://urgv.evry.inra.fr/cgi-bin/projects/CATdb/consult_expce.pl?experiment_id='
+ experiment_id)
def get_information_pop(request):
parametre = request.GET.get('data_requete_element', '')
condiction = request.GET.get('value_search', '')
if parametre == 'contact':
requete = """SELECT * from chips.contact where """ + condiction + ";"
info = getdata(requete)
data = pass_json(info[0], info[1])
if parametre == "array_tye":
requete = "Select array_type_name,platform_name,platform_type,note,spotting_plate,\
slide_dim,surface_type,coating_type,nature_attachment,strand,user_id,submission_date,\
arrayer_IN_file, arrayer_OUT_file,metablock_nb,metacol_nb,metarow_nb,col_nb,row_nb,\
avg_spot_dim, spot_nb, geo_platform, array_probe_file from " + shema + ".array_type where array_type_id=" + condiction + ";"
info = getdata(requete)
data = pass_json(info[0], info[1])
return HttpResponse(json.dumps(data), content_type="application/json")
def get_tableau_format_special(requete):
r, memory = getdata(requete)
data = pd.DataFrame(memory, columns=r)
affiche = [
'Nbs of Experiments', 'Nbs of Mutants', 'Nbs of Organs',
'Nbs of Genotypes'
]
list_column = ['experiment_name', 'mutant_type', 'organ', 'genotype']
data3 = None
for k in range(int(len(list_column) / 2)):
data1 = data[['project_name', list_column[2 * k]]]
data1 = data1.groupby('project_name')[list_column[2 * k]].nunique()
data2 = data[['project_name', list_column[2 * k + 1]
]].loc[data[list_column[2 * k + 1]] != None]
data2 = data2.groupby('project_name')[list_column[2 * k + 1]].nunique()
try:
data3 = pd.concat([data3, data1], axis=1).fillna(0)
except:
pass
try:
data3 = pd.concat([data3, data2], axis=1).fillna(0)
except:
data3 = pd.concat([data1, data2], axis=1).fillna(0)
pass
data3 = data3.reset_index(level=0)
data = data3
corps_table = data.to_html().split('<tbody>')[1].split('</tbody>')[0]
columm_name = """ <tr><th>N°</th>"""
for element in affiche:
columm_name += "<th>" + element + "</th>"
columm_name += """</tr>"""
tableau_id = "example"
tableau = """
<table id='""" + tableau_id + """' class="display" style="width:100%">
<thead>
""" + columm_name + """
</thead>
<tbody>""" + corps_table + """
</tbody>
<tfoot>
""" + columm_name + """
</tfoot>
</table>
<script>
$(document).ready(function() {
$('#""" + tableau_id + """').DataTable( {
"scrollY": "400px",
"scrollCollapse": true,
"paging": false
} );
} );
</script>
"""
return tableau
def analyis_arra_type():
palet = getcolor()
requete = "select CONCAT(ds.analysis_type,' ',ds.array_type) as col,ds.Year,ds.nb from(select df.analysis_type,df.array_type,df.Year,Count(*) as nb from (SELECT experiment.analysis_type, experiment.array_type, to_char(project.public_date,'YYYY') as Year FROM chips.experiment,chips.project WHERE project.project_id = experiment.project_id ) as df group by df.Year,df.analysis_type,df.array_type order by df.array_type) as ds where year<>' ' order by year;"
data_requete_label, data = getdata(requete)
data = pd.DataFrame(data)
data = data[data[1] != None]
res = data.pivot(index=1, columns=0, values=2).fillna(0)
labelbael = list(sorted(res.keys()))
name_label = list(
map(lambda x: x.replace(' ', '_').replace('-', '_'),
labelbael)) #list(set(data_a))#
#color=map(lambda x: '"'+x+'"', labelbael)
#label1=""
value_tampon = 0.000005
data_time = ""
for x in list(res.index.values):
if str(x) != 'nan':
text = "{year:'" + str(x) + "',"
#print(labelbael)
labelbael = ''
i = -1
label1 = ''
color = ''
for y in list(sorted(res.keys().values)):
i += 1
try:
if int(res[y][x]) == 0:
val = value_tampon
else:
val = int(res[y][x])
text += y.replace(' ', '_').replace(
'-', '_') + ":'" + str(val) + "',"
except:
pass
try:
labelbael += "case " + str(i) + ": return '" + name_label[
i] + "';" #"""case """+str(i)+""": return '"""+name_label[i]+"""' ;"""
except:
pass
try:
label1 += '"' + name_label[i] + '"' + ','
#print(name_label[i])
except:
pass
try:
color += '"' + palet[i][random.randint(1, 9)] + '"' + ','
except:
pass
#print(labelbael)
data_time += text[:-1] + ',' + 'None:"0"' + '},'
data_time = data_time #[:-1]
color += '"' + palet[i][random.randint(1, 9)] + '"' + ','
#label1+=label1
return labelbael, color, data_time, value_tampon, label1 #list(res.index.values)
def count_effectif():
datafin = {}
requete = "SELECT count(project.project_id) FROM chips.project where project.is_public='yes';"
r, data = getdata(requete)
datafin['effectifs_projects'] = int(data[0][0])
requete = "SELECT count(experiment.experiment_id),count(distinct experiment.experiment_id) FROM chips.project, chips.experiment WHERE project.project_id = experiment.project_id and project.is_public='yes';"
r, data = getdata(requete)
datafin['effectifs_experiments'] = int(data[0][0])
#datafin['effectifs_distinct_experiments']=int(data[0][1])
requete="SELECT count(distinct treatment.treatment_type) as distinct_ex,count(treatment.treatment_type) Total_ex FROM chips.treatment,chips.experiment,chips.project "\
"WHERE experiment.experiment_id = treatment.experiment_id AND experiment.project_id = treatment.project_id and project.project_id=treatment.project_id and project.is_public='yes';"
r, data = getdata(requete)
datafin['effectifs_Total_treatments'] = int(data[0][1])
datafin['effectifs_distinct_treatments'] = int(data[0][0])
requete = "SELECT count(distinct(experiment.analysis_type)) FROM chips.project, chips.experiment WHERE project.project_id = experiment.project_id and project.is_public='yes';"
r, data = getdata(requete)
datafin['effectifs_technologis'] = int(data[0][0])
requete = "select count(*) from (SELECT distinct(experiment.analysis_type),experiment.array_type FROM chips.project,chips.experiment WHERE project.project_id = experiment.project_id and project.is_public='yes') as df;"
r, data = getdata(requete)
datafin['effectifs_type_technologie'] = int(data[0][0])
requete = "SELECT project.public_date as dat, project.project_id FROM chips.project where project.is_public='yes';"
r, data = getdata(requete)
data = pd.DataFrame(data)
datafin['moyenne_projects_by_year'] = 14
return datafin
def organism(request):
parametre_oragnism = request.GET.get('name',
'').replace('?', '').replace('=', '')
if parametre_oragnism != '':
condition_string = "and organism.organism_name like'%" + parametre_oragnism + "%'"
else:
condition_string = ""
parametre_technolologie = request.GET.get('technologie',
'').replace('?',
'').replace('=', '')
title = parametre_oragnism + " " + parametre_technolologie
requete = """SELECT distinct project.project_name,experiment.experiment_name,sample_source.mutant,sample.organ,sample_source.mutant_type,sample_source.genotype,organism.organism_name
FROM chips.organism,chips.experiment, chips.sample_source,chips.project,chips.sample WHERE sample.sample_source_id=sample_source.sample_source_id and sample_source.organism_id = organism.organism_id AND
experiment.experiment_id=sample_source.experiment_id and project.project_id = sample_source.project_id """ + condition_string + """
""" + public_condition + """
;"""
requete1 = """SELECT
sample.organ ,
count(distinct project.project_id) as weight
FROM
chips.experiment,
chips.sample_source,
chips.sample,
chips.project,chips.organism
WHERE
sample_source.experiment_id = experiment.experiment_id AND
sample_source.experiment_id = sample.experiment_id AND
sample.sample_source_id = sample_source.sample_source_id AND
project.project_id = sample_source.project_id and organism.organism_id=sample_source.organism_id
""" + condition_string + """
""" + public_condition + """
group by sample.organ ;
"""
colnames, memory = getdata(
requete1
) #group by project.project_name,organism.organism_name,sample_source.mutant_type
data = pd.DataFrame(memory, columns=colnames)
graph = countword(data, "visualisation Of organs ", "organ",
"organism_graph_1", "datasp", "organ", "weight")
tableau = get_tableau_format_special(requete)
return render(request, 'CATdb/organism.html', {
'titre': title,
'tableau': tableau,
'graph': graph
})
def crosstablespeciesbytechnologie():
requete="SELECT distinct project.project_id, project.project_name, experiment.analysis_type,experiment.array_type, project.public_date, organism.organism_id, organism.organism_name "\
"FROM chips.project, chips.experiment, chips.sample_source, chips.organism WHERE project.project_id = experiment.project_id AND sample_source.experiment_id = experiment.experiment_id AND sample_source.project_id = experiment.project_id AND organism.organism_id = sample_source.organism_id and project.is_public='yes';"
r, data = getdata(requete)
data = pd.DataFrame(data, columns=r)
data[u'organism_class'] = data[u'organism_name'].apply(
lambda x: 'Arabidopsis thaliana'
if x == 'Arabidopsis thaliana' else 'Others Species')
data_pth = pd.crosstab([data[u'organism_class'], data[u'analysis_type']],
data[u'array_type'],
margins=True)
data_pth1 = pd.crosstab([data[u'organism_name'], data[u'analysis_type']],
data[u'array_type'],
margins=True)
element = '"series":[{"name":"Species","colorByPoint": true,"data":['
souselemen = ""
current_data1 = '["v43.0",0.17],["v41.0",0.17],["v47.0",0.17]'
current_data = "" #"['CATMA_2',146],['CATMA_2.1',352],['CATMA_2.2',1384],['CATMA_2.3',868], ['CATMA_5',1833],[' CATMAv6-HD12',101],['CATMAv6.2-HD12',371],[' CATMAv6.1-HD12',24],[' CATMAv7_4PLEX',453]"
for i in range(0, len(list(data_pth.index)) - 1):
key = list(data_pth.index)[i]
element += '{"name":"' + key[0] + ' ' + key[1] + '", \n "y":' + str(
data_pth.loc[key, 'All']) + ',\n "drilldown":"' + key[0].replace(
' ', '') + key[1].replace(' ', '') + '" \n },'
souselemen += '{"name":"' + key[0] + ' ' + key[1] + '","id":"' + key[
0].replace(' ', '') + key[1].replace(' ', '') + '", \n'
current_data = ""
for j in range(0, len(list(data_pth.columns))):
if data_pth.loc[key, list(data_pth.columns)[j]] != 0 and list(
data_pth.columns
)[j] != 'All' and key[0] == 'Arabidopsis thaliana':
current_data += '["' + list(data_pth.columns)[j] + '",' + str(
data_pth.loc[key, list(data_pth.columns)[j]]) + '],'
if key[0] != 'Arabidopsis thaliana' and list(
data_pth.columns)[j] != 'All':
for k in range(0, len(list(data_pth1.index))):
key1 = list(data_pth1.index)[k]
if key1[0] != 'Arabidopsis thaliana' and key1[1] == key[
1] and data_pth1.loc[
key1, list(data_pth.columns)[j]] != 0:
current_data += '["' + key1[0] + ' by ' + list(
data_pth.columns)[j] + '",' + str(data_pth1.loc[
key1, list(data_pth.columns)[j]]) + '],'
souselemen += '"data":[' + current_data
souselemen += ']},'
text = element[:-1] + ']}], \n "drilldown":{"series":[' + souselemen[:-1] + ']}'
return text
def project(request):
requete = "SELECT project.project_name,experiment.experiment_name, project.public_date, project.is_public FROM chips.experiment,chips.project WHERE experiment.project_id = project.project_id and project.is_public='yes' order by project.public_date desc,project.project_name,experiment.experiment_name;"
#print(getdata(requete))
labelbael, color, data_time, value_tampon, label1 = analyis_arra_type()
data = getdata(requete)
url1 = "CATdb/ficheexperiments/"
url2 = "CATdb/exp/"
return render(
request, 'CATdb/experiment.html', {
'titre_page': 'Experiment',
'labelbael': labelbael,
'color': color,
'data_time': data_time,
'value_tampon': value_tampon,
'label1': label1,
'titre_graph': 'Analysis by Year',
'data': data,
'url1': url1,
'url2': url2
})
def graph_treatment():
key2 = 'piegraph'
requete="SELECT treatment.treatment_type,count(treatment.treatment_type)FROM chips.treatment,chips.experiment "\
"WHERE experiment.experiment_id = treatment.experiment_id AND experiment.project_id = treatment.project_id group by treatment.treatment_type;"
r, data = getdata(requete)
xdata = []
ydata = []
if len(data) > 0:
for i in range(0, len(data)):
xdata.append(data[i][0])
ydata.append(int(data[i][1]))
dimwith = 800
dimheight = 700
from nvd3 import pieChart
type = key2
chart = pieChart(name=type,
color_category='category20c',
height=dimheight,
width=dimwith)
extra_serie = {"tooltip": {"y_start": "", "y_end": " cal"}}
chart.add_serie(y=ydata, x=xdata, extra=extra_serie)
chart.buildcontent()
text = chart.htmlcontent
argument1 = """chart.color(d3.scale.category20c().range());\n\n chart.tooltipContent(function(key, y, e, graph) {\n var x = String(key);\n var y = String(y) + \' cal\';\n\n tooltip_str = \'<center><b>\'+x+\'</b></center>\' + y;\n return tooltip_str;\n });\n"""
##$('#piechart svg.nvd3-svg').attr('width')=dimwith+100
argument2 = " "
argument7 = """});\n var datum ="""
argument8 = """});\n chart.legendPosition("right");\n var datum ="""
text = text.replace(argument7, argument8)
text = text.replace(argument1, argument2)
#text=text.replace('return tooltip_str;\n });\n ','return tooltip_str;\n });*\ \n ')
argument3 = "nv.addGraph(function() {\n"
argument4 = "function rungraphpi(){ \n nv.addGraph(function() {\n"
text = text.replace(argument3, argument4)
argument5 = "</script>"
argument6 = " $('#piegraph').ready(function(){ d3.selectAll('.nv-slice').on('click',function(e){ var col=e.data.label; window.location='treatment.html?treatment='+col; });}); } ;\n rungraphpi(); alert(); \n </script>"
text = text.replace(argument5, argument6)
return text
def tableau_treatment_specifique(key):
requete="SELECT experiment.project_id,experiment.experiment_id,treatment.treatment_type,experiment.experiment_name ,project.title,project.project_name FROM chips.treatment,chips.experiment,chips.project"\
" WHERE experiment.experiment_id = treatment.experiment_id AND experiment.project_id = treatment.project_id and project.project_id=treatment.project_id and treatment.treatment_type='"+key+"';"
sep = '#'
colname, data = getdata(requete)
#dat=pd.DataFrame(data,columns=colname)
dat1 = dict()
for i in range(len(data)):
if data[i][2] not in dat1.keys():
dat1[data[i][2]] = [
str(data[i][0]) + sep + str(data[i][1]) + sep +
str(data[i][3]) + sep + str(data[i][4]) + sep +
str(data[i][5]) + sep
]
else:
cpt = dat1[data[i][2]]
cpt.append(
str(data[i][0]) + sep + str(data[i][1]) + sep +
str(data[i][3]) + sep + str(data[i][4]) + sep +
str(data[i][5]) + sep)
dat1[data[i][2]] = list(set(cpt))
#print("=========new======")
html = "<script> function gotolink(key){ window.location='project.html?project='+key;}; </script>"
html += "<table><thead><th>Treatment</th><th>Project</th></thead><tbody>" #<th>Experiment</th>
for key in dat1.keys():
try:
text = "<tr>" + "<td>" + key + "</td>"
row1 = ""
row2 = ""
for el in range(len(dat1[key])):
row1 += '<div onclick="gotolink(\'' + dat1[key][el].split(
sep)[0] + '\');">' + dat1[key][el].split(sep)[4] + "</div>"
#row2+="<div>"+dat1[key][el].split(sep)[2]+"</div>"
text += "<td>" + row1 + "</td>" + "</tr>" #+"<td>"+row2+"</td>"
html += text
except:
pass
html += "</tbody></table>"
return html
def explorationgraph(request):
list_graph = "<div> <ul><li>Organs</li><li>Treatment</li><li>Graph2</li></ul></div>"
show_treatment = graph_treatment()
show_ecotype = graph_ecotype()
show_experiment_factors = graph_experiment_factors()
requete1 = """SELECT
sample.organ ,
count(distinct project.project_id) as weight
FROM
chips.experiment,
chips.sample_source,
chips.sample,
chips.project,chips.organism
WHERE
sample_source.experiment_id = experiment.experiment_id AND
sample_source.experiment_id = sample.experiment_id AND
sample.sample_source_id = sample_source.sample_source_id AND
project.project_id = sample_source.project_id and organism.organism_id=sample_source.organism_id
""" + public_condition + """
group by sample.organ ;
"""
colnames, memory = getdata(
requete1
) #group by project.project_name,organism.organism_name,sample_source.mutant_type
data = pd.DataFrame(memory, columns=colnames)
graphcount = countword(data, "visualisation of Organs ", "organ",
"organism_graph_1", "datasp", "organ", "weight")
return render(
request, 'CATdb/graph/explore_graph.html', {
'list_graph': list_graph,
'titre_page': 'Explore Databases',
'count_organ': graphcount,
'show_treatment': show_treatment,
'show_ecotype': show_ecotype,
'show_experiment_factors': show_experiment_factors
})
def sampling_table(key):
requete="select pro.protocol_type,pro.protocol_file,concat(e.material_value,e.material_unit) as material,e.quality_file,e.extract_name,e.molecule_type,concat(e.quantity_value,' ',e.quantity_unit) as quantity, o.organism_name, concat(s.dev_stage,'',s.age_value,'',s.age_unit,'',s.harvest_date) as \"dev stage/age/date\",CONCAT(eco.ecotype_name,'/', ss.genotype) as \"ecotype/genotype\", "\
"s.organ,ss.mutant,(select treatment_name from chips.treatment where treatment_id=t.treatment_id), substr(ss.growth_conditions,1,2000) from chips.sample_source ss, chips.organism o, chips.ecotype eco, chips.protocol pro,chips.sample_extract se,chips.extract e, chips.sample s LEFT OUTER JOIN chips.sample_treated t ON t.sample_id=s.sample_id where e.protocol_id=pro.protocol_id and o.organism_id=ss.organism_id and eco.ecotype_id=ss.ecotype_id and ss.experiment_id='"+str(key)+"' and s.sample_source_id=ss.sample_source_id and se.sample_id=s.sample_id and e.extract_id=se.extract_id order by extract_name;"
r, data = getdata(requete)
dat = pd.DataFrame(data)
dat.columns = r
dat = dat.drop_duplicates()
text = np.transpose(dat)
a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a111, a112, a113 = "", "", "", "", "", "", "", "", "", "", "", "", ""
a11, a12, a13, a14, a15, a16, a17, a18, a19, a20, a201, a202, a203 = "", "", "", "", "", "", "", "", "", "", "", "", ""
for i in text.keys():
row = "<tr>"
a11, a12, a13, a14, a15, a16, a17, a18, a19, a20, a201, a202, a203 = text[
i][1], text[i][2], text[i][3], text[i][4], text[i][5], text[i][
6], text[i][7], text[i][8], text[i][9], text[i][10], text[i][
11], text[i][12], text[i][13]
a11 = "" if a11 == a1 else text[i][1]
a12 = "" if a12 == a2 else text[i][2]
a13 = "" if a13 == a3 else text[i][3]
a14 = "" if a14 == a4 else text[i][4]
a15 = "" if a15 == a5 else text[i][5]
a16 = "" if a16 == a6 else text[i][6]
a17 = "" if a17 == a7 else text[i][7]
a18 = "" if a18 == a8 else text[i][8]
a19 = "" if a19 == a9 else text[i][9]
a20 = "" if a20 == a10 else text[i][10]
a201 = "" if a201 == a111 else text[i][11]
a202 = "" if a202 == a112 else text[i][12]
a203 = "" if a203 == a113 else text[i][13]
cont = "<td>" + a14 + "</td>" + "<td>" + a17 + "</td>" + "<td>" + a15 + "</td>" + "<td>" + a20 + "</td>" + "<td>" + a17 + "</td>"
a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a111, a112, a113 = text[i][
1], text[i][2], text[i][3], text[i][4], text[i][5], text[i][
6], text[i][7], text[i][8], text[i][9], text[i][10], text[i][
11], text[i][12], text[i][13]
row += cont + "</tr>"
#print(row)
return text
def ajax_check_email_fields(request):
"""
requete ajax pour envoyer des données
contient des conditions en fonction des varaibles get ou post envoyer par le naviagteur
"""
experiment_id, project_id = '', ''
parametre = request.GET.get('data_requete_element', None)
condition = request.GET.get('value_search', None)
try:
experiment_id = condition.split('#')[0].split('=')[1]
project_id = condition.split('#')[1].split('=')[1]
except:
pass
#print(experiment_id,project_id)
requete = "select ds.project_name,ds.title,ds.biological_interest,ex.experiment_name,ex.experiment_type,ex.analysis_type,ex.array_type,ds.project_id,ex.experiment_id from (SELECT project.project_name,project.project_id, project.is_public, project.public_date FROM chips.project WHERE project.is_public='yes' order by public_date desc limit 5) as ds, chips.experiment as ex where ds.project_id=ex.project_id;"
if parametre == "rnaseqdata":
requete = "SELECT project.project_id,rnaseqdata.project_id, rnaseqdata.experiment_id,rnaseqdata.mapping_type, rnaseqdata.stranded, rnaseqdata.seq_type, rnaseqdata.multihits, rnaseqdata.rnaseqdata_name, rnaseqdata.resume_file, rnaseqdata.log_file,rnaseqdata.bankref_desc, rnaseqdata.project_file FROM chips.project, chips.rnaseqdata WHERE project.project_id = rnaseqdata.project_id and project.project_id='" + project_id + "' and rnaseqdata.experiment_id='" + experiment_id + "';"
if parametre == 'rnaseqlibrary':
requete="SELECT project.project_id,rnaseqdata.project_id,rnaseqdata.experiment_id,rnaseqlibrary.project_id,rnaseqlibrary.experiment_id, "\
"rnaseqlibrary.rnaseqlibrary_name,rnaseqlibrary.library_strategy, rnaseqlibrary.rna_selection,rnaseqlibrary.sizing, rnaseqlibrary.stranded, rnaseqlibrary.seq_type, rnaseqlibrary.seq_length, rnaseqlibrary.multiplexing,rnaseqlibrary.sequencer FROM "\
"chips.project, chips.rnaseqdata, chips.rnaseqlibrary WHERE project.project_id = rnaseqdata.project_id AND rnaseqdata.experiment_id = rnaseqlibrary.experiment_id AND rnaseqdata.project_id = rnaseqlibrary.project_id and project.project_id='"+project_id+"' and rnaseqdata.experiment_id='"+experiment_id+"';"
if parametre == 'experiment_info_rnaseq':
requete="SELECT experiment.array_type,experiment.analysis_type, experiment.experiment_name, experiment.experiment_factors,experiment.experiment_type, experiment.protocol_id, project.biological_interest, project.source, project.project_name, project.title "\
" FROM chips.project, chips.experiment WHERE experiment.project_id = project.project_id and project.project_id='"+project_id+"' and experiment.experiment_id='"+experiment_id+"' and experiment.analysis_type='RNA-Seq';"
colnames, memory = getdata(requete)
cpt = 0
data = dict()
for ele in range(len(memory)):
cpt = cpt + 1
col = []
for ele1 in memory[ele]:
col.append(str(ele1))
dictionary = {'_key': cpt, "_value": dict(zip(colnames, col))}
data[cpt] = dictionary
return HttpResponse(json.dumps(data), content_type="application/json")
def run_script_complete_data(conditionpublic, filtrerequete):
###nom_project
requete="SELECT project.project_id,project.title,project.project_name,gem2net_id FROM chips.project"\
" WHERE "+conditionpublic+filtrerequete+" ;"
r, data = getdata(requete)
dat1 = pd.DataFrame(data, columns=r).drop_duplicates()
####bibliographie
requete="SELECT project.project_id,project_biblio.pubmed_id FROM chips.project, chips.project_biblio WHERE project_biblio.project_id = project.project_id and "\
""+conditionpublic+filtrerequete+";"
r, data = getdata(requete)
dat2 = pd.DataFrame(data, columns=r).drop_duplicates()
##section corrdianteur
requete="SELECT project.project_id,contact.contact_id,"\
" contact.last_name FROM chips.project, chips.contact, "\
" chips.project_coordinator WHERE project_coordinator.contact_id = contact.contact_id AND "\
" project_coordinator.project_id=project.project_id and "+conditionpublic+filtrerequete+"; "
r, data = getdata(requete)
dat3 = pd.DataFrame(data, columns=r).drop_duplicates()
#experiement organ
requete="SELECT distinct sample.project_id,sample.experiment_id,sample.organ,sample.sample_source_id,experiment.experiment_name,experiment.analysis_type FROM chips.project, chips.sample,chips.experiment "\
" WHERE sample.project_id = experiment.project_id AND "\
" experiment.experiment_id = sample.experiment_id and project.project_id=sample.project_id and "+conditionpublic+filtrerequete+"; "
r, data = getdata(requete)
dat4 = pd.DataFrame(data, columns=r).drop_duplicates()
dat5 = dat4[[
u'project_id', u'experiment_id', u'experiment_name', u'analysis_type'
]].drop_duplicates()
dat0 = dat1
dat0 = dat0.set_index(['project_id'])
requete = """select distinct dt.array_type_name,dt.array_type_id,dt.experiment_id,dt.project_id from (SELECT
array_type.array_type_id,
"array".array_type_name,
experiment.experiment_id,
project.project_id
FROM
chips.hybridization,
chips.array_batch,
chips."array",
chips.project,
chips.array_type,
chips.experiment
WHERE
hybridization.array_id = "array".array_id AND
array_batch.array_type_id = array_type.array_type_id AND
"array".array_batch_id = array_batch.array_batch_id AND
experiment.experiment_id = hybridization.experiment_id and
project.project_id=experiment.project_id ) as dt order by dt.project_id ;
"""
r, data = getdata(requete)
dat6 = pd.DataFrame(data, columns=r).drop_duplicates()
requete = "SELECT experiment.experiment_id,rnaseqlibrary.experiment_id,rnaseqlibrary.sequencer FROM chips.experiment,chips.rnaseqlibrary WHERE \
experiment.project_id = rnaseqlibrary.project_id and experiment.experiment_id = rnaseqlibrary.experiment_id;"
r, data = getdata(requete)
dat7 = pd.DataFrame(data, columns=r).drop_duplicates()
requete = "SELECT sample_source.organism_id,organism.organism_id, sample_source.sample_source_id, sample_source.experiment_id, sample_source.project_id, organism.organism_name FROM chips.ecotype, chips.organism, chips.sample_source WHERE organism.organism_id = sample_source.organism_id;"
r, data = getdata(requete)
dat8 = pd.DataFrame(data, columns=r).drop_duplicates()
return dat0, dat1, dat2, dat3, dat4, dat5, dat6, dat7, dat8
def graph_ecotype():
key = 'organsim'
key2 = "multigraph"
requete="SELECT "\
" distinct(organism.organism_name), "\
" ecotype.ecotype_name, "\
" sample_source.project_id "\
" FROM "\
" chips.sample_source, "\
" chips.organism, "\
" chips.ecotype "\
"WHERE "\
" sample_source.ecotype_id = ecotype.ecotype_id AND "\
" organism.organism_id = sample_source.organism_id "\
"order by ecotype_name,organism.organism_name; "
r, data = getdata(requete)
dat = pd.DataFrame(data)
dat2 = pd.crosstab(dat[0], dat[1])
#test du plus lon
dat2 = pd.crosstab(dat[0], dat[1])
dat2 = pd.DataFrame(np.transpose(dat2))
#r,data=getdata(requete)
#if len(data)>0:
# for i in range(0,len(data)):
# xdata.append(data[i][0])
# ydata1.append(int(data[i][1]))
split = dict(np.transpose(dat2).sum())
list1 = []
list2 = []
for element in split.keys():
if split[element] > 1:
list1.append(element)
else:
list2.append(element)
from nvd3 import multiBarChart
chart1 = multiBarChart(name=key2,
width=800,
height=800,
x_axis_format=None)
#text_white="chart.stacked(true);"
#chart1.add_chart_extras(text_white)
chart2 = multiBarChart(width=800, height=800, x_axis_format=None)
xdata = list(dat2.index) #['one', 'two', 'three', 'four']
#ydata1 = [6, 12, 9, 16]
serie_lab = list(dat2.columns)
for ele in range(0, len(serie_lab)):
if ele != 1:
chart1.add_serie(name=serie_lab[ele],
y=list(dat2.loc[list1, serie_lab[ele]]),
x=list1)
#chart2.add_serie(name=serie_lab[ele], y=list(dat2.loc[list2,serie_lab[ele]]), x=list1)
chart1.buildhtml()
text = chart1.htmlcontent
argument1 = "var chart = nv.models.multiBarChart();\n\n"
argument2 = "var chart" + key + " = nv.models.multiBarChart();\n\n \n\n"
text = text.replace(argument1, argument2)
text = text.replace("chart.", "chart" + key + ".")
argument7 = """});\n var datum ="""
argument8 = """});\n chart""" + key + """.legendPosition("right");\n var datum ="""
text = text.replace(argument7, argument8)
text = text.replace(".call(chart);", ".call(chart" + key + ");")
text = text.replace(
"</script>",
"$('#multigraph1 .nv-controlsWrap .nv-series:eq(0)').addClass('nv-disabled');\n </script>"
)
argument3 = "nv.addGraph(function() {\n"
argument4 = "function rungraph(){ \n nv.addGraph(function() {\n"
text = text.replace(argument3, argument4)
argument5 = "</script>"
argument6 = "};\n rungraph(); \n alert(); </script>"
text = text.replace(argument5, argument6)
return text
def graph_experiment_factors():
requete="SELECT experiment.experiment_factors,experiment.project_id "\
"FROM chips.experiment;"
r, data = getdata(requete)
list_element = []
comptability = {}
for j in range(len(data)):
text = data[j][0]
for elemnt in text.split(','):
if elemnt[0] == ' ':
elemnt = elemnt[1:]
elemnt = elemnt.replace('\t', '')
if elemnt[-1] == ' ':
elemnt = elemnt[0:-1]
if elemnt[-5:] == 'a wt)':
elemnt = elemnt[0:-4]
if elemnt[0:2] != '--':
list_element.append(elemnt)
if elemnt not in comptability.keys():
comptability[elemnt] = [data[j][1]]
else:
cpt = comptability[elemnt]
cpt.append(data[j][1])
comptability[elemnt] = list(set(cpt))
#data_plot={}
#for element in comptability.keys():
# data_plot[0]
from nvd3 import discreteBarChart
chart = discreteBarChart(name='discreteBarChart', height=400, width=800)
xdata = []
ydata = []
list1 = list(comptability.keys()) #[0:20]
dataplost = {}
for element in list1:
dataplost[element] = len(comptability[element])
a = sorted(dataplost, key=dataplost.__getitem__, reverse=True)
######a corriger car fixer
for element in a[0:30]:
xdata.append(element)
ydata.append(dataplost[element])
#xdata.append(element)# = ["A", "B", "C", "D", "E", "F"]
#ydata.append(len(comptability[element]))# = [3, 4, 0, -3, 5, 7]
#array=np.array([xdata,ydata])
#a=np.sort(array,axis=1)
chart.add_serie(y=ydata, x=xdata)
chart.buildhtml()
text = chart.htmlcontent
argument1 = "var chart = nv.models.discreteBarChart();\n\n"
argument2 = "var chart = nv.models.discreteBarChart();\n\n chart.xAxis.rotateLabels(-90); \n\n"
text = text.replace(argument1, argument2)
argument3 = "nv.addGraph(function() {\n"
argument4 = "function rungraphdiscret(){ \n nv.addGraph(function() {\n"
text = text.replace(argument3, argument4)
argument5 = "</script>"
argument6 = "};\n rungraphdiscret(); alert();\n </script>"
text = text.replace(argument5, argument6)
return text
