-
Harmel Tristan authoredc9a02a96
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
# -*- coding: utf-8 -*-
"""
----------------------------------------------------------------
PYTHON SCRIPT
----------------------------------------------------------------
DBtrios.py
Created on: may 2018
Last update: 01/02/2019
Author: Nathalie REYNAUD
OBS2CO
Post-traitement des données TRIOS récoltées dans le cadre du
projet TELQUEL/OBS2CO - facultatif : ajout des valeurs de
profondeur tirées du capteur de pression HOBO
----------------------------------------------------------------
requirements warning: pandas
----------------------------------------------------------------
"""
""" Required libraries """
import os
import pyodbc
import pandas as pd
import glob
""" Environment """
""" Local variables """
# <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<I/O>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# répertoire projet
mydir = r'Z:\Hyax_Travail\LACS\TELQUEL'
# répertoire des données TRIOS
DBrep = r'Donnees\Donnees_Brutes\Terrain\TRIOS'
hobo_process = True
# dictionnaire du décalage (en mètres) entre les radiomètres et le capteur de pression
dec_m = {'Luz': 0.07, 'Edz': -0.28, 'reflectancez': -0.105}
# <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<_>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# base de données TRIOS
DBfile = glob.glob(os.path.join(mydir, DBrep, 'dataTriOS_*.mdb'))[1]
""" Fonctions """
def odbc2lst(conn, query):
cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchall()
return result
""" Processing """
'''
STEP 1: trios TRIOS data
'''
# connect to bd TRIOS
odbc = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + DBfile)
# retrieve RAMSES data
query = 'SELECT * FROM tblData WHERE ((tblData.IDDataType LIKE \'SPECTRUM\') AND ' \
'((tblData.IDDataTypeSub1 LIKE \'CALIBRATED\') OR (tblData.IDDataTypeSub1 LIKE \'CALCULATED\')))'
ramses_df = pd.read_sql(query, odbc)
# isolate measurement methods
dicmeth = {'Lu0+': ramses_df[ramses_df['CommentSub1'] == 'Lu0+'],
'uw': ramses_df[ramses_df['CommentSub1'] == 'underwater'],
'aw': ramses_df[ramses_df['CommentSub1'] == 'above water']}
# for each measurement method
for key in dicmeth.keys():
lst_idpr = []
# group by parameters and samples
groups = dicmeth[key].groupby(['MethodName', 'CommentSub2', 'CommentSub3'])
for name, group in groups:
print(name)
# retrieve and format data
idpr = name[2].replace(' ', '').split('pr')[-1]
# add idpr to list
if idpr not in lst_idpr:
lst_idpr.append(idpr)
# check for retry
if 'bis' in group['Comment'].values:
group = group[group['Comment'] == 'bis']
dt = group['DateTime']
mes = group['Data'].transform(lambda x: x.split('\r\n '))
# transpose measurement values per wavelength
wls = [x.split(' ')[0] for x in mes.values[0] if x.split(' ')[0] not in (u'', u'0')]
vals = []
for row in mes:
vals.append([x.split(' ')[1] for x in row if x.split(' ')[0] in wls])
mes_df = pd.DataFrame(vals, columns=wls, index=mes.index)
exp_df = pd.concat([dt, mes_df], axis=1, join='outer')
# add depth information for underwater measurements
if key == 'uw':
z = name[1][2:]
depth = pd.DataFrame([z] * len(exp_df), columns=['depth'], index=mes.index)
exp_df = pd.concat([depth, exp_df], axis=1, join='outer')
# save result to csv
out_file = os.path.join(mydir, DBrep,
'{0}_{1}_idpr{2}.csv'.format(key, name[0], idpr))
if 'SAM' in name[0]:
out_file = os.path.join(mydir, DBrep,
'{0}_{1}_{3}_idpr{2}.csv'.format(key, name[1], idpr, name[0].replace('_', ''), ))
elif key == 'uw':
out_file = os.path.join(mydir, DBrep,
'{0}_{1}_idpr{2}.csv'.format(key, name[0] + z, idpr))
exp_df.to_csv(out_file, sep=';', index=False)
# compile data for underwater measurements at various depths in a single csv file
if key == 'uw':
for par in ('Ed', 'Lu', 'reflectance'):
for idpr in lst_idpr:
flst = glob.glob(os.path.join(mydir, DBrep,
'uw_{0}[0,-][-,0-9]*{1}.csv'.format(par, idpr)))
try:
foo = os.path.basename(flst[0]).split('_')[1]
comp_file = flst[0].replace(foo, foo.strip('-.0123456789') + 'z')
comp_df = pd.concat([pd.read_csv(f, sep=';') for f in flst])
comp_df.to_csv(comp_file, sep=';', index=False)
for f in flst:
os.remove(f)
except IndexError:
pass
'''
STEP 2: trios HOBO data
'''
if hobo_process:
# for each "underwater" RAMSES file
uwfiles = glob.glob(os.path.join(mydir, DBrep, 'csv', 'uw_*z_*.csv'))
for f in uwfiles:
# find corresponding hobo file
idpr = f[-7:-4]
mes = os.path.basename(f).split('_')[1]
hobo = glob.glob(os.path.join(mydir, r'Donnees\Donnees_Brutes\Terrain\*\TRIOS\HOBO_pression',
'*_idpr{0}*.txt'.format(idpr)))
# print message on error
if len(hobo) == 0:
print('ERREUR sur idpr{0} : fichier hobo manquant !'.format(idpr))
elif len(hobo) > 1:
print('ERREUR sur idpr{0} : plusieurs fichiers hobo trouvés.' \
'Vérifiez les idpr et/ou supprimer les fichiers superflus !'.format(idpr))
else:
# load data from RAMSES and HOBO
dfh = pd.read_csv(hobo[0], sep=';', header=1, usecols=range(5), decimal=',',
names=['id', 'date', 'time', 'pres', 'temp'])
dff = pd.read_csv(f, sep=';')
# calculate depth from pressure (p0 = 1st pressure value after launching sensor)
# formule: z = (p-p0)/(ro*g) avec ro = 0.999 (environ : dépend de la température) et g = 9.81 (standard)
p0 = dfh['pres'][0]
dfh = dfh.assign(prof=(dfh['pres'] - p0) / (0.999 * 9.81) + dec_m[mes])
dt_df = pd.DataFrame(dfh['date'].str.replace('/', '-') + ' ' + dfh['time'],
columns=['DateTime'], index=dfh.index)
dfh = pd.concat([dfh, dt_df], axis=1, join='outer')
# join data on datetime
all_df = pd.merge(dff, dfh, how='left', on='DateTime')
# export final results to csv
fdf = pd.concat([all_df['prof'], all_df.iloc[:, 1:-7]], axis=1, join='outer')
fdf.to_csv('_hobo.'.join(f.split('.')), sep=';', index=False)
""" ------------------------------END---------------------------------- """