def create_dataframe(result_dict, measurepoint_list):
'''
Créé une dataframe à partir d'un référence de campagne.
Les colonnes de la dataframe sont les sandres dont les seuils 7j sont non nuls dans r3 (table reference)
Les colonnes vides sont supprimées
:param result_dict:
:return: dataframe:
'''
elements_metal = QueryScript(f" SELECT sandre FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()} AND 7j_threshold IS NOT NULL AND familly='Métaux'").execute()
elements_metal = [int(float(element)) for element in elements_metal]
elements_PCB = QueryScript(f" SELECT sandre FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()} AND 7j_threshold IS NOT NULL AND familly LIKE 'PCB%'").execute()
elements_PCB = [int(float(element)) for element in elements_PCB]
elements_HAP = QueryScript(f" SELECT sandre FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()} AND 7j_threshold IS NOT NULL AND familly = 'HAP'").execute()
elements_HAP = [int(float(element)) for element in elements_HAP]
elements_others = QueryScript(f" SELECT sandre FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()} AND 7j_threshold IS NOT NULL AND familly != 'HAP' AND familly != 'Métaux' AND familly NOT LIKE 'PCB%'").execute()
elements_others = [int(float(element)) for element in elements_others]
matrix = []
for measurepoint_id in measurepoint_list:
if measurepoint_id in result_dict :
matrix.append([''] + [result_dict[measurepoint_id][sandre] if sandre in result_dict[measurepoint_id] and result_dict[measurepoint_id][sandre] !='0.0' else 'ND' for sandre in elements_metal ]+[''] + [result_dict[measurepoint_id][sandre] if sandre in result_dict[measurepoint_id] and result_dict[measurepoint_id][sandre] !='0.0' else 'ND' for sandre in elements_PCB ]+[''] + [result_dict[measurepoint_id][sandre] if sandre in result_dict[measurepoint_id] and result_dict[measurepoint_id][sandre] !='0.0' else 'ND' for sandre in elements_HAP ]+[''] + [result_dict[measurepoint_id][sandre] if sandre in result_dict[measurepoint_id] and result_dict[measurepoint_id][sandre] !='0.0' else 'ND' for sandre in elements_others ] + [measurepoint_id])
else :
matrix.append([''] + ['ND' for sandre in elements_metal ]+[''] + ['ND' for sandre in elements_PCB ]+[''] + ['ND' for sandre in elements_HAP ]+[''] + ['ND' for sandre in elements_others ] + [measurepoint_id])
df = pd.DataFrame(matrix)
df.columns = [''] + [element for element in elements_metal] + [''] + [element for element in elements_PCB] + [''] + [element for element in elements_HAP] + [''] + [element for element in elements_others] + ['']
df = df.dropna(how='all', axis='columns')
return df
def create_empty_dataframe(result_dict, measurepoint_list):
elements_metal = QueryScript(f" SELECT sandre FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()} AND 7j_threshold IS NOT NULL AND familly='Métaux'").execute()
elements_metal = [int(float(element)) for element in elements_metal]
elements_PCB = QueryScript(f" SELECT sandre FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()} AND 7j_threshold IS NOT NULL AND familly LIKE 'PCB%'").execute()
elements_PCB = [int(float(element)) for element in elements_PCB]
elements_HAP = QueryScript(f" SELECT sandre FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()} AND 7j_threshold IS NOT NULL AND familly = 'HAP'").execute()
elements_HAP = [int(float(element)) for element in elements_HAP]
elements_others = QueryScript(f" SELECT sandre FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()} AND 7j_threshold IS NOT NULL AND familly != 'HAP' AND familly != 'Métaux' AND familly NOT LIKE 'PCB%'").execute()
elements_others = [int(float(element)) for element in elements_others]
matrix = []
# data = chemistry.result_by_packs_and_sandre(result_dict)
for measurepoint_id in measurepoint_list:
if measurepoint_id in result_dict:
matrix.append([''] + ['' for sandre in elements_metal ]+[''] + ['' for sandre in elements_PCB ]+[''] + ['' for sandre in elements_HAP ]+[''] + ['' for sandre in elements_others ] + [measurepoint_id])
else :
matrix.append([''] + ['ND' for sandre in elements_metal ]+[''] + ['ND' for sandre in elements_PCB ]+[''] + ['ND' for sandre in elements_HAP ]+[''] + ['ND' for sandre in elements_others ] + [measurepoint_id])
df = pd.DataFrame(matrix)
df.columns = [''] + [element for element in elements_metal] + [''] + [element for element in elements_PCB] + [''] + [element for element in elements_HAP] + [''] + [element for element in elements_others] + ['']
df = df.dropna(how='all', axis='columns')
return df
def neurotoxicity(dict_pack):
constant_AChE = QueryScript(
f" SELECT value FROM {env.DATABASE_TREATED}.r2_constant WHERE name LIKE 'Constante ache%' AND version= {env.CHOSEN_VERSION()}").execute()
pack_dict = {}
for element in dict_pack:
try:
pack_dict[dict_pack[element]['neurology']] = element
except KeyError:
None
output = QueryScript(f" SELECT pack_id, ache, weight FROM {env.DATABASE_RAW}.Cage WHERE pack_id IN {tuple([element for element in pack_dict]) if len([element for element in pack_dict])>1 else '('+(str([element for element in pack_dict][0]) if len([element for element in pack_dict]) else '0')+')'}").execute()
result = {element:None for element in dict_pack}
pack_checked = None
for cage in output:
if not result[pack_dict[cage[0]]] and cage[1]:
result[pack_dict[cage[0]]] = {'ache':[cage[1]], 'weight':[cage[2]]}
elif cage[1] :
result[pack_dict[cage[0]]]['ache'].append(cage[1])
result[pack_dict[cage[0]]]['weight'].append(cage[2])
for element in result:
if result[element]:
mean_AChE = sum(result[element]['ache'])/len(result[element]['ache'])
mean_weight = sum(result[element]['weight'])/len(result[element]['weight'])
AChE_expected = constant_AChE[0] + constant_AChE[1] * ( mean_weight ** constant_AChE[2] )
result[element] = ( mean_AChE - AChE_expected ) / AChE_expected * 100
return result
def insert_key_dates(cas):
if cas == 1:
QueryScript(f"DROP TABLE IF EXISTS key_dates").execute(admin=True)
QueryScript(
f'CREATE TABLE key_dates (id INT AUTO_INCREMENT PRIMARY KEY, measurepoint_id INT, date_id INT, date DATETIME, version INT)'
).execute(admin=True)
if cas == 2:
QueryScript(
f"DELETE FROM {env.DATABASE_TREATED}.key_dates WHERE version = {env.CHOSEN_VERSION()}"
).execute(admin=True)
insertion = QueryScript(
f" INSERT INTO key_dates (measurepoint_id, date_id, date, version) VALUES (%s, %s, %s, %s)"
)
values = []
global_dict = create_global_dict()
fusion = 0
for place_id in global_dict["data"]:
for measurepoint_id in global_dict["data"][place_id]:
chemistry_present = False
for pack_id in global_dict["data"][place_id][measurepoint_id][
"pack"]:
if global_dict["data"][place_id][measurepoint_id]["pack"][
pack_id] == 'chemistry':
chemistry_present = True
for i, date in enumerate(
order_dates(
global_dict["data"][place_id][measurepoint_id]
["measureExposureCondition"], chemistry_present)):
values.append((int(measurepoint_id), i + 1, date))
insertion.setRows(values)
insertion.executemany()
def number_days_exposition(dict_pack):
nature = 'reproduction'
list_mp_repro = []
for mp in dict_pack:
try:
dict_pack[mp][nature]
except KeyError:
pass
else:
list_mp_repro.append(mp)
# Récupération des dates de début et de fin
output_dates_debut = QueryScript(
f" SELECT measurepoint_id, date FROM {env.DATABASE_TREATED}.key_dates where date_id=1 and measurepoint_id IN {tuple(list_mp_repro) if len(list_mp_repro)>1 else '('+(str(list_mp_repro[0]) if len(list_mp_repro) else '0')+')'} and version= {env.CHOSEN_VERSION()};"
).execute()
output_dates_fin = QueryScript(
f" SELECT measurepoint_id, date FROM {env.DATABASE_TREATED}.key_dates where date_id=4 and measurepoint_id IN {tuple(list_mp_repro) if len(list_mp_repro)>1 else '('+(str(list_mp_repro[0]) if len(list_mp_repro) else '0')+')'} and version= {env.CHOSEN_VERSION()};"
).execute()
output_mp_debut = [x[0] for x in output_dates_debut]
output_mp_fin = [x[0] for x in output_dates_fin]
dict_dates_debut_fin = {} # {mp: [date_debut, date_fin]}
for mp in list_mp_repro:
try :
idx_debut = output_mp_debut.index(mp)
idx_fin = output_mp_fin.index(mp)
dict_dates_debut_fin[mp] = [output_dates_debut[idx_debut][1], output_dates_fin[idx_fin][1]]
except ValueError:
dict_dates_debut_fin[mp] = [None, None]
# Initialisation du dictionnaire de sortie
dict_nbr_days_exposition = {mp: None for mp in dict_pack} # {mp: nbrdays}
# Calcul
for mp in list_mp_repro:
[date_debut, date_fin] = dict_dates_debut_fin[mp]
if date_debut is not None and date_fin is not None:
if date_debut is None and date_fin is None:
dict_nbr_days_exposition[mp] = "NA"
else:
date_fin_sans_heure = date(date_fin.year, date_fin.month, date_fin.day)
date_debut_sans_heure = date(date_debut.year, date_debut.month, date_debut.day)
nbrdays = (date_fin_sans_heure - date_debut_sans_heure).days
dict_nbr_days_exposition[mp] = nbrdays
else:
dict_nbr_days_exposition[mp] = "NA"
return dict_nbr_days_exposition
def index_fecundity_female(list_pack_repro):
SQL_request = f" SELECT pack_id, female, molting_stage, embryo_stage, specimen_size_mm, specimen_size_px, embryo_total FROM {env.DATABASE_RAW}.MeasureReprotoxicity where pack_id IN {tuple(list_pack_repro) if len(list_pack_repro)>1 else '('+(str(list_pack_repro[0]) if len(list_pack_repro) else '0')+')'};"
output = QueryScript(SQL_request).execute()
# Initialisation du dictionnaire de la requête mise en forme
# {pack_id: {'px_to_mm': int, 'data': [[molting_stage, embryo_stage, specimen_size_mm, specimen_size_px, embryo_total], [...]] }}
dict_result = {pack_id: {'px_to_mm': None, 'data': []} for pack_id in list_pack_repro}
pack_errors = []
for row in output:
[pack_id, female, molting_stage, embryo_stage, specimen_size_mm, specimen_size_px, embryo_total] = row
if female == '' or female == '0bis':
continue
if int(female) == 0: # Valeur étalon
try:
px_to_mm = specimen_size_mm/specimen_size_px
except (TypeError, ZeroDivisionError):
pack_errors.append(pack_id)
continue
dict_result[pack_id]['px_to_mm'] = px_to_mm
elif female is not None: # Données à traiter ensuite
data = [molting_stage, embryo_stage, specimen_size_mm, specimen_size_px, embryo_total]
dict_result[pack_id]['data'].append(data)
# Initialisation du dictionnaire de sortie
dict_index_fecundity = {pack_id: {'list_molting_stage': [], 'list_index_fecundity': []} for pack_id in list_pack_repro}
for pack_id in dict_result.keys():
data = dict_result[pack_id]['data']
px_to_mm = dict_result[pack_id]['px_to_mm']
for row in data:
[molting_stage, embryo_stage, specimen_size_mm, specimen_size_px, embryo_total] = row
if len(row) == 0:
dict_index_fecundity[pack_id]['list_index_fecundity'].append(0)
else:
dict_index_fecundity[pack_id]['list_molting_stage'].append(molting_stage)
if embryo_stage in [2, 3, 4]:
if embryo_total == 0:
dict_index_fecundity[pack_id]['list_index_fecundity'].append(0)
else:
if specimen_size_mm is None or specimen_size_mm == 0:
if specimen_size_px == 0:
continue
try:
specimen_size_mm = specimen_size_px * px_to_mm
except TypeError:
pack_errors.append(pack_id)
continue
if specimen_size_mm == 0:
pack_errors.append(pack_id)
continue
dict_index_fecundity[pack_id]['list_index_fecundity'].append(embryo_total/(specimen_size_mm-5))
else:
dict_index_fecundity[pack_id]['list_index_fecundity'].append(0)
return dict_index_fecundity # {pack_id: {'list_molting_stage': [...], 'list_index_fecundity': [...]}
def update_version():
new_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
QueryScript(
f'UPDATE {env.DATABASE_TREATED}.version SET date="{new_date}" WHERE id={env.CHOSEN_VERSION()}'
).execute(admin=True)
print(
f'Updating version {env.CHOSEN_VERSION()} at {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}'
)
def female_survivor(dict_pack):
nature = 'reproduction'
list_pack_repro = []
list_mp_repro = []
for mp in dict_pack:
try:
pack_id = dict_pack[mp][nature]
except KeyError:
pass
else:
list_mp_repro.append(mp)
list_pack_repro.append(pack_id)
output = QueryScript(
f" SELECT pack_id, scud_survivor_female, scud_quantity FROM {env.DATABASE_RAW}.Cage WHERE pack_id IN {tuple([element for element in list_pack_repro]) if len([element for element in list_pack_repro])>1 else '('+(str([element for element in list_pack_repro][0]) if len([element for element in list_pack_repro]) else '0')+')'};"
).execute()
# Reformatage des données de la requête
dict_requete = {
pack_id: {
'scud_quantity': None,
'scud_survivor_female': []
}
for pack_id in list_pack_repro
}
for row in output:
[pack_id, scud_survivor_female, scud_quantity] = row
if dict_requete[pack_id]['scud_quantity'] is None:
dict_requete[pack_id]['scud_quantity'] = scud_quantity
if scud_survivor_female is not None:
dict_requete[pack_id]['scud_survivor_female'].append(
scud_survivor_female)
# Calcul du pourcentage de survie par pack
dict_survie_femelle = {mp: None for mp in dict_pack.keys()}
for mp in dict_survie_femelle.keys():
if mp not in list_mp_repro:
continue
pack_id = dict_pack[mp]['reproduction']
scud_quantity = dict_requete[pack_id]['scud_quantity']
scud_survivor_female = dict_requete[pack_id][
'scud_survivor_female']
if scud_quantity is not None and len(scud_survivor_female) != 0:
try:
survie_femelle = (
sum(scud_survivor_female) /
len(scud_survivor_female)) / scud_quantity
except ZeroDivisionError:
print(
f"Point de mesure: {mp} --> Erreur de calcul de survie femelle, quantité de gamares dans la cage inconnu"
)
continue
dict_survie_femelle[mp] = survie_femelle * 100
return dict_survie_femelle
def agency_finder(measurepoint_id):
'''
Permet de récupérer le code agence associé à un point de mesure
:param measurepoint_id:
:return: code_agence:
'''
try:
agency = QueryScript(
f" SELECT code FROM {env.DATABASE_RAW}.Agency JOIN {env.DATABASE_RAW}.Place ON Agency.id = Place.agency_id JOIN {env.DATABASE_RAW}.Measurepoint ON Place.id = Measurepoint.place_id WHERE Measurepoint.id = {measurepoint_id};"
).execute()[0]
except IndexError:
agency = None
return agency
def create_new_version(date=None, comment=None):
query = QueryScript(
f" INSERT INTO version (date, comment) VALUES (%s, %s)")
query.setRows([(date, comment)])
query.executemany(True)
version = env.LATEST_VERSION()
version_file = open("version.txt", "w")
version_file.write(f"CHOSEN_VERSION={version}")
version_file.close()
def add_campaign(self):
is_existing = QueryScript(
f"SELECT * FROM {env.DATABASE_RAW}.Campaign WHERE reference='{self.campaign_input.get()}'"
).execute()
if len(is_existing
) and not self.campaign_input.get() in self.campaign_list:
if self.campaign_input_text.get() != "":
self.campaign_input_text.set(self.campaign_input_text.get() +
"\n")
self.campaign_input_text.set(self.campaign_input_text.get() +
self.campaign_input.get().upper())
else:
self.campaign_input_text.set(self.campaign_input.get().upper())
self.show_end()
self.campaign_list.append(self.campaign_input.get().upper())
self.reset_input()
def maximum_freq_quanti(code_sandre):
'''
Crée un tuple composé d'une liste de fréquences quanti et une autre des maxima
:param code_sandre:
:return: ([maximum], [freq_quanti]):
'''
output = QueryScript(f" SELECT maximum, freq_quanti FROM {env.DATABASE_TREATED}.r3 WHERE sandre IN {tuple(code_sandre) if len(code_sandre)>1 else '('+(str(code_sandre[0]) if len(code_sandre) else '0')+')'} AND version= {env.CHOSEN_VERSION()}").execute()
maximum, freq_quanti = [], []
for couple in output:
if couple[0] != 0:
maximum.append(couple[0])
else:
maximum.append(None)
freq_quanti.append(couple[1])
return(maximum, freq_quanti)
def create_tox_dataframe(campaigns_dict, measurepoint_list):
list_dataframe = []
data = QueryScript(f"SELECT measurepoint_id, round(male_survival_7_days), round(alimentation, 1), round(neurotoxicity, 1), round(female_survivor), number_days_exposition, number_female_concerned, round(percent_inhibition_fecondite, 1), number_female_analysis, molting_cycle,number_female_concerned_area,endocrine_disruption, molting_cycle_conformity, surface_retard_conformity FROM {env.DATABASE_TREATED}.toxtable WHERE version= {env.CHOSEN_VERSION()} AND measurepoint_id IN {tuple(measurepoint_list) if len(measurepoint_list)>1 else '('+(str(measurepoint_list[0]) if len(measurepoint_list) else '0')+')'};").execute()
for campaign_id in campaigns_dict:
matrix =[]
for place_id in campaigns_dict[campaign_id]["place"]:
temp = [None]*20
if "duplicate" in campaigns_dict[campaign_id]["place"][place_id] and ("alimentation" in campaigns_dict[campaign_id]["place"][place_id]["duplicate"] or "neurology" in campaigns_dict[campaign_id]["place"][place_id]["duplicate"] or "reproduction" in campaigns_dict[campaign_id]["place"][place_id]["duplicate"]):
for measurepoint_id in campaigns_dict[campaign_id]["place"][place_id]["measurepoint"]:
number = float(str(campaigns_dict[campaign_id]["place"][place_id]["number"])+'.'+str(campaigns_dict[campaign_id]["place"][place_id]["measurepoint"][measurepoint_id]["number"]))
for mp_id, male_survival_7_days, alimentation, neurotoxicity, female_survivor, number_days_exposition, number_female_concerned, percent_inhibition_fecondite, number_female_analysis, molting_cycle, number_female_concerned_area, endocrine_disruption, molting_cycle_conformity, surface_retard_conformity in data :
if measurepoint_id==mp_id and measurepoint_id in measurepoint_list :
for index in range(4):
temp[index] = [campaigns_dict[campaign_id]["number"], number, translate(campaigns_dict[campaign_id]["place"][place_id]["name"]), campaigns_dict[campaign_id]["place"][place_id]["agency"] if "agency" in campaigns_dict[campaign_id]["place"][place_id] else 'ND'][index]
temp[5], temp[6] = male_survival_7_days if male_survival_7_days else 'NA', alimentation if male_survival_7_days else None
if neurotoxicity != None:
temp[7] = neurotoxicity if male_survival_7_days else None
if female_survivor or number_days_exposition:
for index in range(9,20):
temp[index] = [female_survivor, number_days_exposition, number_female_concerned, percent_inhibition_fecondite if percent_inhibition_fecondite and female_survivor else "NA", number_female_analysis, molting_cycle, number_female_concerned_area, endocrine_disruption if endocrine_disruption else "NA", measurepoint_id, molting_cycle_conformity, surface_retard_conformity][index-9]
matrix.append(temp)
temp = [None]*20
else :
number = campaigns_dict[campaign_id]["place"][place_id]["number"]
for measurepoint_id in campaigns_dict[campaign_id]["place"][place_id]["measurepoint"]:
for mp_id, male_survival_7_days, alimentation, neurotoxicity, female_survivor, number_days_exposition, number_female_concerned, percent_inhibition_fecondite, number_female_analysis, molting_cycle, number_female_concerned_area, endocrine_disruption, molting_cycle_conformity, surface_retard_conformity in data :
if measurepoint_id==mp_id :
for index in range(4):
temp[index] = [campaigns_dict[campaign_id]["number"], number, translate(campaigns_dict[campaign_id]["place"][place_id]["name"]), campaigns_dict[campaign_id]["place"][place_id]["agency"] if "agency" in campaigns_dict[campaign_id]["place"][place_id] else 'ND'][index]
temp[5], temp[6] = male_survival_7_days if male_survival_7_days else 'NA', alimentation if male_survival_7_days else None
if neurotoxicity !=None :
temp[7] = neurotoxicity if male_survival_7_days else None
if female_survivor or number_days_exposition :
for index in range(9,20):
temp[index] = [female_survivor, number_days_exposition, number_female_concerned, percent_inhibition_fecondite if percent_inhibition_fecondite and female_survivor else "NA" , number_female_analysis, molting_cycle, number_female_concerned_area, endocrine_disruption if endocrine_disruption else "NA", measurepoint_id, molting_cycle_conformity, surface_retard_conformity][index-9]
matrix.append(temp)
df = pd.DataFrame(matrix)
df.columns = ['Campagne', 'Numéro', 'Station de mesure', 'Code Agence','','Survie Male - 7 jours', 'Alimentation',
'Neurotoxicité AChE','', 'Survie Femelle','Nombre jours exposition in situ',
'n','Fécondité','n','Cycle de mue','n','Perturbation endocrinienne','', '', '']
list_dataframe.append(df)
df_values = pd.concat(list_dataframe)
df_sorted = df_values.sort_values(['Numéro', 'Campagne'])
return df_sorted
def get_tox_data(measurepoint_list, pack_list) :
pack_tuple = tuple(pack_list) if len(pack_list)>1 else "('"+str(pack_list[0])+"')"
measurepoint_tuple = tuple(measurepoint_list) if len(measurepoint_list)>1 else "('"+str(measurepoint_list[0])+"')"
cage_data = QueryScript(f"SELECT pack_id, replicate, scud_quantity, scud_survivor, weight, ache, nature FROM {env.DATABASE_RAW}.Cage WHERE pack_id IN {pack_tuple}").execute()
remaining_leaves_data = QueryScript(f"SELECT pack_id, replicate, value FROM {env.DATABASE_RAW}.MeasureLeaf WHERE pack_id IN {pack_tuple}").execute()
specimen_size_data = QueryScript(f" SELECT pack_id, individual, size_px, size_mm FROM {env.DATABASE_RAW}.MeasureSize WHERE pack_id IN {pack_tuple}").execute()
specimen_size_t0_id = QueryScript(f"SELECT Size_t0_id.pack_id, Pack.id FROM {env.DATABASE_RAW}.Measurepoint JOIN {env.DATABASE_RAW}.Pack On Pack.measurepoint_id=Measurepoint.id JOIN (SELECT Measurepoint.id as mp_id, Pack.id as pack_id FROM {env.DATABASE_RAW}.Measurepoint JOIN {env.DATABASE_RAW}.Pack On Pack.measurepoint_id=Measurepoint.id) as Size_t0_id ON Size_t0_id.mp_id=Measurepoint.code_size_t0_id AND Pack.id IN {pack_tuple};").execute()
t0_id_list = [element[0] for element in specimen_size_t0_id]
size_t0_dict = {}
for t0_pack_id, pack_id in specimen_size_t0_id :
size_t0_dict[pack_id] = t0_pack_id
if len(t0_id_list) :
t0_pack_tuple = tuple(t0_id_list) if len(t0_id_list)>1 else "('"+str(t0_id_list[0])+"')"
specimen_size_t0_data = QueryScript(f" SELECT pack_id, individual, size_px, size_mm FROM {env.DATABASE_RAW}.MeasureSize WHERE pack_id IN {t0_pack_tuple}").execute()
else :
specimen_size_t0_data = []
average_temperature_data = QueryScript(f" SELECT measurepoint_id, sensor1_average, sensor1_min, sensor1_max FROM {env.DATABASE_TREATED}.average_temperature WHERE measurepoint_id IN {measurepoint_tuple} AND version= {env.CHOSEN_VERSION()}").execute()
female_data = QueryScript(f"SELECT pack_id, female, molting_stage, oocyte_left, oocyte_right, specimen_size_px, specimen_size_mm, embryo_total, embryo_stage, oocyte_area_pixel, oocyte_area_mm FROM {env.DATABASE_RAW}.MeasureReprotoxicity").execute()
temperature_repro_data = QueryScript(f"SELECT measurepoint_id, expected_C2, expected_D2 FROM {env.DATABASE_TREATED}.temperature_repro WHERE version={env.CHOSEN_VERSION()}").execute()
return cage_data, remaining_leaves_data, specimen_size_data, average_temperature_data, female_data, temperature_repro_data, size_t0_dict, specimen_size_t0_data
def survie_alim(dict_pack):
pack_dict = {}
for element in dict_pack:
try:
pack_dict[dict_pack[element]['alimentation']] = element
except KeyError:
None
survivor_list = QueryScript(
f" SELECT pack_id, scud_survivor, scud_quantity, replicate FROM {env.DATABASE_RAW}.Cage WHERE pack_id IN {tuple([element for element in pack_dict]) if len([element for element in pack_dict])>1 else '('+(str([element for element in pack_dict][0]) if len([element for element in pack_dict]) else '0')+')'} AND scud_survivor IS NOT NULL"
).execute()
result = {element: None for element in dict_pack}
pack_checked = None
current_quantity = None
for pack_id, scud_survivor, scud_quantity, replicate in survivor_list:
if pack_checked != pack_id:
if pack_checked:
result[pack_dict[pack_checked]]['average'] = sum(
[(result[pack_dict[pack_checked]]['replicate']
[replicate] * 2 - current_quantity) /
current_quantity * 100 for replicate in
result[pack_dict[pack_checked]]['replicate']]) / len([
replicate
for replicate in result[pack_dict[pack_checked]]
['replicate'] if replicate
])
pack_checked = pack_id
current_quantity = None
if scud_quantity:
current_quantity = scud_quantity
result[pack_dict[pack_checked]] = {
'replicate': {
replicate: (scud_survivor + current_quantity) / 2
}
}
else:
result[pack_dict[pack_checked]]['replicate'][replicate] = (
scud_survivor + current_quantity) / 2
return result
def list_agency_finder(list_mp):
'''
Permet de récupérer les codes agences associés à une liste de points de mesure
:param list_mp: liste d'id de points de mesure
:return: list_agency: liste de codes agences
'''
if len(list_mp) > 1:
query_tuple_mp = tuple(list_mp)
else:
query_tuple_mp = f"({list_mp[0]})"
output = QueryScript(
f" SELECT Measurepoint.id, code FROM {env.DATABASE_RAW}.Agency JOIN {env.DATABASE_RAW}.Place ON Agency.id = Place.agency_id JOIN {env.DATABASE_RAW}.Measurepoint ON Place.id = Measurepoint.place_id WHERE Measurepoint.id IN {query_tuple_mp};"
).execute()
list_agency = []
list_mp_output = [x[0] for x in output]
for mp_id in list_mp:
try:
idx_code = list_mp_output.index(mp_id)
list_agency.append(output[idx_code][1])
except ValueError:
list_agency.append(None)
return list_agency
def create_dataframe(result_dict, measurepoint_list):
'''
Créé une dataframe à partir d'un référence de campagne.
Les colonnes de la dataframe sont les sandres des différentes familles d'éléments
Les colonnes vides sont supprimées
:param result_dict:
:return: dataframe:
'''
sandre_data = QueryScript(
f" SELECT distinct sandre FROM {env.DATABASE_RAW}.Analysis WHERE name!='' AND sandre!=''"
).execute()
sandre_data = sorted(sandre_data)
columns = []
for sandre in sandre_data:
try:
sandre_transformed = int(float(sandre))
except ValueError:
sandre_transformed = sandre
columns.append(sandre_transformed)
matrix = []
for measurepoint_id in measurepoint_list:
if measurepoint_id in result_dict:
matrix.append([''] + [
result_dict[measurepoint_id][sandre]
if sandre in result_dict[measurepoint_id]
and result_dict[measurepoint_id][sandre] != '0.0' else None
for sandre in columns
] + [measurepoint_id])
else:
matrix.append([''] + [None
for sandre in columns] + [measurepoint_id])
df = pd.DataFrame(matrix)
df.columns = [''] + columns + ['']
df = df.dropna(how='all', axis='columns')
return df
def create_calcul_tox_dataframe(campaign_dict, J_dict, measurepoint_list, pack_list) :
constant_list = QueryScript(f"SELECT name, value FROM {env.DATABASE_TREATED}.r2_constant WHERE version={env.CHOSEN_VERSION()}").execute()
constant_dict = {}
for name, value in constant_list:
if name :
constant_dict[name] = value
matrix = []
matrix.append(["Mois de transplantation"])
matrix.append(["Population de gammares"])
matrix.append(["Feuilles Biotests alimentation - reprotoxicité"])
matrix.append(["Survie 7 jours"])
matrix.append(["Moyenne de survie Alim 1 à 4 / Nb par réplictats"])
matrix.append(["Survie Alim 1"])
matrix.append(["Survie Alim 2"])
matrix.append(["Survie Alim 3"])
matrix.append(["Survie Alim 4"])
matrix.append(["Moyenne de survie Alim X ; Nb par réplictats"])
matrix.append([""])
matrix.append(["Critère de validité - survie globale"])
matrix.append(["Critère de validité - réplicat 1 (+75% survie)"])
matrix.append(["Critère de validité - réplicat 2 (+75% survie)"])
matrix.append(["Critère de validité - réplicat 3 (+75% survie)"])
matrix.append(["Critère de validité - réplicat 4 (+75% survie)"])
matrix.append(["Alimentation"])
matrix.append(["Taille pixels - X x Contrôle étalon mm/pxl "])
matrix.append(["TAILLE MALE"])
matrix.append(["Taille - Minimum (mm)"])
matrix.append(["Taille - Maximum (mm)"])
matrix.append(["Taille - Ecart type"])
matrix.append(["Taille - coefficient de variation"])
matrix.append(["Taille mm - 1 "])
matrix.append(["Taille mm - 2"])
matrix.append(["Taille mm - 3"])
matrix.append(["Taille mm - 4"])
matrix.append(["Taille mm - 5"])
matrix.append(["Taille mm - 6"])
matrix.append(["Taille mm - 7"])
matrix.append(["Taille mm - 8"])
matrix.append(["Taille mm - 9"])
matrix.append(["Taille mm - 10"])
matrix.append(["Taille mm - 11"])
matrix.append(["Taille mm - 12"])
matrix.append(["Taille mm - 13"])
matrix.append(["Taille mm - 14"])
matrix.append(["Taille mm - 15"])
matrix.append(["Taille mm - 16"])
matrix.append(["Taille mm - 17"])
matrix.append(["Taille mm - 18"])
matrix.append(["Taille mm - 19"])
matrix.append(["Taille mm - 20"])
matrix.append(["Contrôle étalon mm/pxl"])
matrix.append(["Etalon taille mm / Etalon taille pixel"])
matrix.append([""])
matrix.append(["Valeurs brutes réplicat 0 (pxl)"])
matrix.append(["Somme des valeurs brutes pour 10 (témoin) / Nombre de disques (témoin)"])
matrix.append(["Somme des valeurs brutes pour 20 (témoin)"])
matrix.append([""])
matrix.append(["Pixels consommés-réplicat 1"])
matrix.append(["Pixels consommés-réplicat 2"])
matrix.append(["Pixels consommés-réplicat 3"])
matrix.append(["Pixels consommés-réplicat 4"])
matrix.append(["Somme des valeurs brutes pour 20 (témoin) - Somme des valeurs pour réplicat X"])
matrix.append(["Pixels consommés/jour/individu - réplicat 1"])
matrix.append(["Pixels consommés/jour/individu - réplicat 2"])
matrix.append(["Pixels consommés/jour/individu - réplicat 3"])
matrix.append(["Pixels consommés/jour/individu - réplicat 4"])
matrix.append(["mm² consommés/jour/individu - réplicat 1"])
matrix.append(["mm² consommés/jour/individu - réplicat 2"])
matrix.append(["mm² consommés/jour/individu - réplicat 3"])
matrix.append(["mm² consommés/jour/individu - réplicat 4"])
matrix.append(["MM² CONSOMMES/JOUR/INDIVIDU - MOYENNE"])
matrix.append(["MM² CONSOMMES/JOUR/INDIVIDU - SD"])
matrix.append([""])
matrix.append(["TEMPERATURE MOYENNE (°C) ALIM"])
matrix.append(["TEMPERATURE MIN (°C) in situ"])
matrix.append(["TEMPERATURE MAX (°C) in situ"])
matrix.append([""])
matrix.append(["Valeur d'alimentation attendue"])
matrix.append(["Constante alim 1 x TEMPERATURE MOYENNE + Constante alim 2 + Constante alim 3 x (TAILLE MALE - Constante 4)"])
matrix.append(["% inhibition (FI) - Réplicat 1"])
matrix.append(["% inhibition (FI) - Réplicat 2"])
matrix.append(["% inhibition (FI) - Réplicat 3"])
matrix.append(["% inhibition (FI) - Réplicat 4"])
matrix.append(["% INHIBITION (FI) - MOY"])
matrix.append(["% INHIBITION (FI) - SD"])
matrix.append(["(Valeur d'alimentation attendue - mm² consommés/jour/individu rép X) / valeur d'alimentation attendue x 100"])
matrix.append(["% INHIBITION (FI) - MOY"])
matrix.append(["(Valeur d'alimentation attendue - mm² consommés/jour/individu rép X) x -1 / valeur d'alimentation attendue x 100 --> inhibition = résultat négatif"])
matrix.append(["Nombre de réplicats pris en compte"])
# matrix.append(["Seuils varient en fonction du nombre de réplicats pris en compte"])
# matrix.append(["Résultat (en fonction des anciens seuils variables)"])
# matrix.append(["Intensité"])
# matrix.append(["Proba - Réplicat 1"])
# matrix.append(["Proba - Réplicat 2"])
# matrix.append(["Proba - Réplicat 3"])
# matrix.append(["Proba - Réplicat 4"])
# matrix.append(["logVraisemblance"])
# matrix.append(["Nombre de réplicats pris en compte"])
# matrix.append(["Seuils varient en fonction du nombre de réplicats pris en compte"])
# matrix.append(["Résultat"])
# matrix.append(["Intensité"])
matrix.append([""])
matrix.append(["Neurotoxicité - Activité AChE"])
matrix.append(["Moyenne des masses"])
matrix.append(["Masse - Ecart type"])
matrix.append(["Masse - coefficient de variation"])
matrix.append(["Activité AChE attendue (nmol/mon)"])
matrix.append(["Activité AChE - Réplicat 1"])
matrix.append(["Activité AChE - Réplicat 2"])
matrix.append(["Activité AChE - Réplicat 3"])
matrix.append(["Activité AChE - Réplicat 4"])
matrix.append(["Activité AChE - Réplicat 5"])
matrix.append(["Constante ache 1 x (Moyenne des masses ^ (Constante ache 2))"])
matrix.append(["Activité AChE moyenne observée (nmol/min)"])
matrix.append(["sd observé"])
matrix.append(["Critère de qualité"])
matrix.append(["Commentaires"])
matrix.append(["% INHIBITION - AChE"])
matrix.append(["% INHIBITION (AChE) - Resultat rendu"])
matrix.append([" --> inhibition = résultat négatif"])
# matrix.append(["Résultat"])
matrix.append([""])
matrix.append(["Reprotoxicité"])
matrix.append([""])
matrix.append(["Nombre de B-C1"])
matrix.append(["Nombre de C2-D1"])
matrix.append(["Nombre de D2"])
matrix.append(["Nombre de femelles analysées"])
matrix.append(["Distribution observée - B/C1"])
matrix.append(["Distribution observée - C2/D1"])
matrix.append(["Distribution observée - D2"])
matrix.append(["Distribution attendue - B/C1"])
matrix.append(["Distribution attendue - C2/D1"])
matrix.append(["Distribution attendue - D2"])
matrix.append(["Test unilatéral 5%"])
matrix.append(["Test unilatéral 1%"])
matrix.append(["1 seule femelle ?"])
matrix.append([""])
matrix.append(["indice de fertilité - moyenne"])
matrix.append(["Nombre de femelles concernées"])
matrix.append(["Nombre de femelles avec un stade de mue C2 ou D1 et avec des ovocytes"])
matrix.append(["% INHIBITION - FERTILITE"])
# matrix.append(["Seuil 5%"])
# matrix.append(["Seuil 1%"])
# matrix.append(["Résultat - Fertilité"])
matrix.append(["indice de fertilité - femelle 1"])
matrix.append(["indice de fertilité - femelle 2"])
matrix.append(["indice de fertilité - femelle 3"])
matrix.append(["indice de fertilité - femelle 4"])
matrix.append(["indice de fertilité - femelle 5"])
matrix.append(["indice de fertilité - femelle 6"])
matrix.append(["indice de fertilité - femelle 7"])
matrix.append(["indice de fertilité - femelle 8"])
matrix.append(["indice de fertilité - femelle 9"])
matrix.append(["indice de fertilité - femelle 10"])
matrix.append(["indice de fertilité - femelle 11"])
matrix.append(["indice de fertilité - femelle 12"])
matrix.append(["indice de fertilité - femelle 13"])
matrix.append(["indice de fertilité - femelle 14"])
matrix.append(["indice de fertilité - femelle 15"])
matrix.append(["Si std mue fem X = C2 ou D1 alors SI somme des ovocytes > 0 alors somme des ovocytes / taille fem X sinon """])
matrix.append([""])
matrix.append(["indice de fécondité - moyenne"])
matrix.append(["Nombre de femelles concernées"])
matrix.append(["Nombre de femelles avec un stade de mue C2 ou D1 et avec des ovocytes"])
matrix.append(["% INHIBITION - FECONDITE"])
matrix.append(["% INHIBITION FECONDITE - Resultat rendu"])
# matrix.append(["Seuil 5% fécondité"])
# matrix.append(["Seuil 1% fécondité"])
# matrix.append(["Résultat - Fécondité"])
matrix.append(["indice de fécondité - femelle 1"])
matrix.append(["indice de fécondité - femelle 2"])
matrix.append(["indice de fécondité - femelle 3"])
matrix.append(["indice de fécondité - femelle 4"])
matrix.append(["indice de fécondité - femelle 5"])
matrix.append(["indice de fécondité - femelle 6"])
matrix.append(["indice de fécondité - femelle 7"])
matrix.append(["indice de fécondité - femelle 8"])
matrix.append(["indice de fécondité - femelle 9"])
matrix.append(["indice de fécondité - femelle 10"])
matrix.append(["indice de fécondité - femelle 11"])
matrix.append(["indice de fécondité - femelle 12"])
matrix.append(["indice de fécondité - femelle 13"])
matrix.append(["indice de fécondité - femelle 14"])
matrix.append(["indice de fécondité - femelle 15"])
matrix.append(["Si std emb fem X = 2 ou 3 ou 4 alors SI nombre embryons > 0 alors nombre embryons / (taille fem X - 5) sinon """])
matrix.append([""])
matrix.append(["Surface des retards - PE"])
matrix.append(["surface des retards - femelle 1"])
matrix.append(["surface des retards - femelle 2"])
matrix.append(["surface des retards - femelle 3"])
matrix.append(["surface des retards - femelle 4"])
matrix.append(["surface des retards - femelle 5"])
matrix.append(["surface des retards - femelle 6"])
matrix.append(["surface des retards - femelle 7"])
matrix.append(["surface des retards - femelle 8"])
matrix.append(["surface des retards - femelle 9"])
matrix.append(["surface des retards - femelle 10"])
matrix.append(["surface des retards - femelle 11"])
matrix.append(["surface des retards - femelle 12"])
matrix.append(["surface des retards - femelle 13"])
matrix.append(["surface des retards - femelle 14"])
matrix.append(["surface des retards - femelle 15"])
matrix.append(["surface des retards - moyenne"])
matrix.append(["Nombre de femelles concernées"])
matrix.append(["SEUIL UNILATERAL 5%"])
matrix.append([""])
matrix.append(["Surfaces C2D1 inhibition (%)"])
matrix.append(["Seuil 5% surface C2-D1"])
matrix.append(["Seuil 1% surface C2-D1"])
matrix.append(["Résultat"])
matrix.append(["Intensité"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 1"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 2"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 3"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 4"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 5"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 6"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 7"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 8"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 9"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 10"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 11"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 12"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 13"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 14"])
matrix.append(["pourcentage d'inhibition surfaces C2 D1 - femelle 15"])
matrix.append(["pourcentage moyen d'inhibition"])
matrix.append(["Nombre de femelles concernées"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 1"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 2"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 3"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 4"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 5"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 6"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 7"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 8"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 9"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 10"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 11"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 12"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 13"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 14"])
matrix.append(["écart centré réduit surfaces C2 D1 - femelle 15"])
matrix.append([""])
matrix.append(["taille femelle 1 - mm²"])
matrix.append(["taille femelle 2 - mm²"])
matrix.append(["taille femelle 3 - mm²"])
matrix.append(["taille femelle 4 - mm²"])
matrix.append(["taille femelle 5 - mm²"])
matrix.append(["taille femelle 6 - mm²"])
matrix.append(["taille femelle 7 - mm²"])
matrix.append(["taille femelle 8 - mm²"])
matrix.append(["taille femelle 9 - mm²"])
matrix.append(["taille femelle 10 - mm²"])
matrix.append(["taille femelle 11 - mm²"])
matrix.append(["taille femelle 12 - mm²"])
matrix.append(["taille femelle 13 - mm²"])
matrix.append(["taille femelle 14 - mm²"])
matrix.append(["taille femelle 15 - mm²"])
matrix.append([""])
matrix.append(["surface ovocytaire 1 - µm²"])
matrix.append(["surface ovocytaire 2 - µm²"])
matrix.append(["surface ovocytaire 3 - µm²"])
matrix.append(["surface ovocytaire 4 - µm²"])
matrix.append(["surface ovocytaire 5 - µm²"])
matrix.append(["surface ovocytaire 6 - µm²"])
matrix.append(["surface ovocytaire 7 - µm²"])
matrix.append(["surface ovocytaire 8 - µm²"])
matrix.append(["surface ovocytaire 9 - µm²"])
matrix.append(["surface ovocytaire 10 - µm²"])
matrix.append(["surface ovocytaire 11 - µm²"])
matrix.append(["surface ovocytaire 12 - µm²"])
matrix.append(["surface ovocytaire 13 - µm²"])
matrix.append(["surface ovocytaire 14 - µm²"])
matrix.append(["surface ovocytaire 15 - µm²"])
matrix.append([""])
matrix.append(["somme des ovocytes - femelle 1"])
matrix.append(["somme des ovocytes - femelle 2"])
matrix.append(["somme des ovocytes - femelle 3"])
matrix.append(["somme des ovocytes - femelle 4"])
matrix.append(["somme des ovocytes - femelle 5"])
matrix.append(["somme des ovocytes - femelle 6"])
matrix.append(["somme des ovocytes - femelle 7"])
matrix.append(["somme des ovocytes - femelle 8"])
matrix.append(["somme des ovocytes - femelle 9"])
matrix.append(["somme des ovocytes - femelle 10"])
matrix.append(["somme des ovocytes - femelle 11"])
matrix.append(["somme des ovocytes - femelle 12"])
matrix.append(["somme des ovocytes - femelle 13"])
matrix.append(["somme des ovocytes - femelle 14"])
matrix.append(["somme des ovocytes - femelle 15"])
matrix.append([""])
matrix.append(["Std embryo 1"])
matrix.append(["Std embryo 2"])
matrix.append(["Std embryo 3"])
matrix.append(["Std embryo 4"])
matrix.append(["Std embryo 5"])
matrix.append(["Std embryo 6"])
matrix.append(["Std embryo 7"])
matrix.append(["Std embryo 8"])
matrix.append(["Std embryo 9"])
matrix.append(["Std embryo 10"])
matrix.append(["Std embryo 11"])
matrix.append(["Std embryo 12"])
matrix.append(["Std embryo 13"])
matrix.append(["Std embryo 14"])
matrix.append(["Std embryo 15"])
matrix.append(["Nombre de stade embryo 1 - 2"])
matrix.append(["Nombre de stade embryo 3 - 4"])
matrix.append(["Nombre de stade embryo 5"])
matrix.append(["Nombre de femelles anaylsées"])
matrix.append(["Pourcentage observé - 1/2"])
matrix.append(["Pourcentage observé - 3/4"])
matrix.append(["Pourcentage observé - 5"])
matrix.append(["Pourcentage attendu - 1/2"])
matrix.append(["Pourcentage attendu - 3/4"])
matrix.append(["Pourcentage attendu - 5"])
matrix.append(["Test unilatéral 5%"])
matrix.append(["Test unilatéral 1%"])
# matrix.append(["Résultat"])
# matrix.append(["Intensité"])
# matrix.append(["1 seule femelle ?"])
cage_data, remaining_leaves_data, specimen_size_data, average_temperature_data, female_data, temperature_repro_data, size_t0_dict, specimen_size_t0_data = get_tox_data(measurepoint_list, pack_list)
reference_list = [["",""]]
campaign_reference = ""
count = 1
for campaign_index, campaign_id in enumerate(campaign_dict):
campaign_reference = campaign_dict[campaign_id]["name"]
for place_id in campaign_dict[campaign_id]["place"]:
place_reference = campaign_reference + "-" + ( "0" + str(campaign_dict[campaign_id]["place"][place_id]['number']) if campaign_dict[campaign_id]["place"][place_id]['number']<10 else str(campaign_dict[campaign_id]["place"][place_id]['number']))
if 'duplicate' in campaign_dict[campaign_id]["place"][place_id] and ('alimentation' in campaign_dict[campaign_id]["place"][place_id]['duplicate'] or 'neurology' in campaign_dict[campaign_id]["place"][place_id]['duplicate'] or 'reproduction' in campaign_dict[campaign_id]["place"][place_id]['duplicate']):
for measurepoint_id in campaign_dict[campaign_id]["place"][place_id]["measurepoint"]:
measurepoint_reference = place_reference + "-" + ( "0" + str(campaign_dict[campaign_id]["place"][place_id]["measurepoint"][measurepoint_id]['number']) if campaign_dict[campaign_id]["place"][place_id]["measurepoint"][measurepoint_id]['number']<10 else str(campaign_dict[campaign_id]["place"][place_id]["measurepoint"][measurepoint_id]['number']))
reference_list.append([measurepoint_reference, measurepoint_id])
matrix[0].append(J_dict[place_id]['J0']['truncated_date'])
matrix[1].append('CRE')
matrix[2].append('Lot ' + J_dict[place_id]['J0']['truncated_date'][-4:] if J_dict[place_id]['J0']['truncated_date'] else None)
matrix = add_result(matrix, campaign_dict[campaign_id]["place"][place_id]["measurepoint"][measurepoint_id],measurepoint_id, cage_data, remaining_leaves_data, specimen_size_data, average_temperature_data, female_data, temperature_repro_data, constant_dict, size_t0_dict, specimen_size_t0_data)
count +=1
matrix = fill_empty(matrix, count)
else :
matrix[0].append(J_dict[place_id]['J0']['truncated_date'])
matrix[1].append('CRE')
matrix[2].append('Lot ' + J_dict[place_id]['J0']['truncated_date'][-4:] if J_dict[place_id]['J0']['truncated_date'] else None)
reference_list.append([place_reference, place_id])
matrix = add_result(matrix, campaign_dict[campaign_id]["place"][place_id],place_id, cage_data, remaining_leaves_data, specimen_size_data, average_temperature_data, female_data, temperature_repro_data, constant_dict, size_t0_dict, specimen_size_t0_data)
count += 1
matrix = fill_empty(matrix, count)
df = pd.DataFrame(matrix, columns=list(map(lambda x : x[0], reference_list)))
return df
def create_global_dict(cas):
packs = QueryScript(
f'SELECT Pack.id, Measurepoint.id FROM {env.DATABASE_RAW}.Pack JOIN {env.DATABASE_RAW}.Measurepoint ON Measurepoint.id=Pack.measurepoint_id'
).execute()
key_dates = QueryScript(
f'SELECT measurepoint_id, date_id, date FROM {env.DATABASE_TREATED}.key_dates'
).execute()
if cas == 1 or cas == 3:
temperatures = QueryScript(
f'SELECT measurepoint_id, pack_id, recordedAt, value, nature FROM {env.DATABASE_RAW}.MeasureTemperature WHERE recordedAt<="2017-00-00 00:00:00"'
).execute()
looked_year = 2017
print(f'{len(temperatures)} rows loaded')
next_temperatures = QueryScript(
f'SELECT measurepoint_id, pack_id, recordedAt, value, nature FROM {env.DATABASE_RAW}.MeasureTemperature WHERE recordedAt<="2018-00-00 00:00:00" AND recordedAt>="2017-00-00 00:00:00"'
).execute()
while len(next_temperatures):
for element in next_temperatures:
temperatures.append(element)
print(f'{len(temperatures)} rows loaded')
looked_year += 1
next_temperatures = QueryScript(
f'SELECT measurepoint_id, pack_id, recordedAt, value, nature FROM {env.DATABASE_RAW}.MeasureTemperature WHERE recordedAt<="{looked_year+1}-00-00 00:00:00" AND recordedAt>="{looked_year}-00-00 00:00:00"'
).execute()
global_dict = {"data": {}}
for pack_id, measurepoint_id in packs:
measurepoint_id = measurepoint_id
if measurepoint_id in global_dict["data"]:
if pack_id not in global_dict["data"][measurepoint_id]:
global_dict["data"][measurepoint_id]["packs"].append(pack_id)
else:
global_dict["data"][measurepoint_id] = {"packs": [pack_id]}
for measurepoint_id, date_id, date in key_dates:
measurepoint_id = measurepoint_id
if measurepoint_id in global_dict["data"]:
if "key_dates" in global_dict["data"][measurepoint_id]:
global_dict["data"][measurepoint_id]["key_dates"][
date_id] = str(date) if date else None
else:
global_dict["data"][measurepoint_id]["key_dates"] = {
date_id: str(date) if date else None
}
else:
global_dict["data"][measurepoint_id] = {
"key_dates": {
date_id: str(date) if date else None
}
}
if cas == 2:
last_update = QueryScript(
f'SELECT date FROM {env.DATABASE_TREATED}.version WHERE id={env.CHOSEN_VERSION()}'
).execute()[0]
last_update_1hour_delay = last_update - datetime.timedelta(minutes=60)
temperatures = QueryScript(
f'SELECT measurepoint_id, pack_id, recordedAt, value, nature FROM {env.DATABASE_RAW}.MeasureTemperature WHERE measurepoint_id IN (SELECT measurepoint_id FROM {env.DATABASE_RAW}.MeasureTemperature WHERE updatedAt>="{last_update_1hour_delay}") OR measurepoint_id IN (SELECT measurepoint_id FROM {env.DATABASE_RAW}.MeasureExposureCondition WHERE updatedAt>="{last_update_1hour_delay}") OR pack_id IN (SELECT pack_id FROM {env.DATABASE_RAW}.MeasureTemperature WHERE updatedAt>="{last_update_1hour_delay}");'
).execute()
print(f'{len(temperatures)} rows loaded')
global_dict["need_update"] = []
for measurepoint_id, pack_id, _, _, _ in temperatures:
if measurepoint_id:
if measurepoint_id not in global_dict["need_update"]:
global_dict["need_update"].append(measurepoint_id)
elif pack_id:
for row in packs:
if row[0] == pack_id:
if row[1] not in global_dict["need_update"]:
global_dict["need_update"].append(row[1])
if len(temperatures):
current_measurepoint_id = str(
temperatures[0][0]) if temperatures[0][0] else None
current_key_dates = {}
count = 0
no_key_dates_mp_id_list = []
for mp_id, pack_id, recordedAt, value, nature in temperatures:
recordedAt = str(recordedAt) if recordedAt else None
count += 1
can_be_stored = False
is_lab = False
if mp_id:
if not mp_id == current_measurepoint_id:
for measurepoint in global_dict["data"]:
if measurepoint == mp_id:
current_measurepoint_id = measurepoint
elif pack_id:
for measurepoint in global_dict["data"]:
if "packs" in global_dict["data"][
measurepoint] and pack_id in global_dict["data"][
measurepoint]["packs"]:
current_measurepoint_id = measurepoint
if "need_update" in global_dict and current_measurepoint_id not in global_dict[
"need_update"]:
pass
elif "key_dates" in global_dict["data"][current_measurepoint_id]:
for key_date in global_dict["data"][current_measurepoint_id][
"key_dates"]:
current_key_dates[key_date] = global_dict["data"][
current_measurepoint_id]["key_dates"][key_date]
if nature == "sensor1" and (1 in current_key_dates
and 2 in current_key_dates):
if current_key_dates[1]:
start_date = str(
datetime.datetime.strptime(current_key_dates[1],
'%Y-%m-%d %H:%M:%S') +
datetime.timedelta(0, 3600))
else:
start_date = None
end_date = current_key_dates[2]
if start_date and end_date:
if (recordedAt <= end_date
and recordedAt >= start_date):
can_be_stored = True
elif nature == "sensor2" and (3 in current_key_dates
and 4 in current_key_dates
and 5 in current_key_dates):
if current_key_dates[3]:
start_date = str(
datetime.datetime.strptime(current_key_dates[3],
'%Y-%m-%d %H:%M:%S') +
datetime.timedelta(0, 3600))
else:
start_date = None
end_date = current_key_dates[4]
lab_date = current_key_dates[5]
if start_date and lab_date and (recordedAt <= lab_date and
recordedAt >= start_date):
is_lab = True
if start_date and end_date and (recordedAt <= end_date and
recordedAt >= start_date):
can_be_stored = True
elif nature == "sensor3" and (6 in current_key_dates
and 7 in current_key_dates):
if current_key_dates[6]:
start_date = str(
datetime.datetime.strptime(current_key_dates[6],
'%Y-%m-%d %H:%M:%S') +
datetime.timedelta(0, 3600))
else:
start_date = None
end_date = current_key_dates[7]
if start_date and end_date and (recordedAt <= end_date and
recordedAt >= start_date):
can_be_stored = True
if value:
if can_be_stored:
if nature in global_dict["data"][
current_measurepoint_id]:
global_dict["data"][current_measurepoint_id][
nature].append(value)
else:
global_dict["data"][current_measurepoint_id][
nature] = [value]
if "all" in global_dict["data"][
current_measurepoint_id]:
global_dict["data"][current_measurepoint_id][
"all"].append(value)
else:
global_dict["data"][current_measurepoint_id][
"all"] = [value]
if is_lab:
if "sensor2lab" in global_dict["data"][
current_measurepoint_id]:
global_dict["data"][current_measurepoint_id][
"sensor2lab"].append(value)
else:
global_dict["data"][current_measurepoint_id][
"sensor2lab"] = [value]
if count % 10000 == 0:
print(f'{count} rows sorted')
return global_dict
def insert_average_temperature(cas, global_dict):
if cas == 1:
QueryScript(f"DROP TABLE IF EXISTS average_temperature").execute(
admin=True)
average_temperature_table = QueryScript(
"CREATE TABLE average_temperature (id INT AUTO_INCREMENT PRIMARY KEY, measurepoint_id INT(11), sensor1_average DOUBLE, sensor1_min DOUBLE, sensor1_max DOUBLE, sensor2_average DOUBLE, sensor2_min DOUBLE, sensor2_max DOUBLE, sensor3_average DOUBLE, sensor3_min DOUBLE, sensor3_max DOUBLE, sensor2_average_labo DOUBLE, all_sensor_average DOUBLE, version INT );"
)
average_temperature_table.execute(admin=True)
if cas == 2:
need_update = global_dict['need_update'] if len(
global_dict['need_update']) else [0]
QueryScript(
f"DELETE FROM {env.DATABASE_TREATED}.average_temperature WHERE version = {env.CHOSEN_VERSION()} and measurepoint_id in {tuple(need_update) if len(need_update)>1 else '('+(str(need_update[0]) if len(need_update) else '0')+')'};"
).execute(admin=True)
insertion = QueryScript(
f" INSERT INTO average_temperature (measurepoint_id, sensor1_average, sensor1_min, sensor1_max, sensor2_average, sensor2_min, sensor2_max, sensor3_average, sensor3_min, sensor3_max, sensor2_average_labo, all_sensor_average, version) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
)
output = []
for measurepoint_id in global_dict["data"]:
if measurepoint_id != "None" and (
cas in [1, 3] or
('need_update' in global_dict
and measurepoint_id in global_dict["need_update"])):
row = [measurepoint_id]
if "sensor1" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["sensor1"]) /
len(global_dict["data"][measurepoint_id]["sensor1"]))
row.append(min(
global_dict["data"][measurepoint_id]["sensor1"]))
row.append(max(
global_dict["data"][measurepoint_id]["sensor1"]))
else:
row += [None] * 3
if "sensor2" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["sensor2"]) /
len(global_dict["data"][measurepoint_id]["sensor2"]))
row.append(min(
global_dict["data"][measurepoint_id]["sensor2"]))
row.append(max(
global_dict["data"][measurepoint_id]["sensor2"]))
else:
row += [None] * 3
if "sensor3" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["sensor3"]) /
len(global_dict["data"][measurepoint_id]["sensor3"]))
row.append(min(
global_dict["data"][measurepoint_id]["sensor3"]))
row.append(max(
global_dict["data"][measurepoint_id]["sensor3"]))
else:
row += [None] * 3
if "sensor2lab" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["sensor2lab"]) /
len(global_dict["data"][measurepoint_id]["sensor2lab"]))
else:
row.append(None)
if "all" in global_dict["data"][measurepoint_id]:
row.append(
sum(global_dict["data"][measurepoint_id]["all"]) /
len(global_dict["data"][measurepoint_id]["all"]))
else:
row.append(None)
output.append(tuple(row))
if len(output):
insertion.setRows(output)
insertion.executemany()