source: trunk/DataCheck/Processing/fill_sqm_data_into_db.py@ 18752

Last change on this file since 18752 was 18259, checked in by dneise, 10 years ago
initial commit, still work to be done
  • Property svn:executable set to *
File size: 5.0 KB
Line 
1#!/usr/bin/env python2
2# coding: utf-8
3"""
4authors: Max Ahnen, Dominik Neise
5----------------------------------------------------------------------------
6"THE BEER-WARE LICENSE" (Revision 42):
7Max Ahnen and Dominik Neise wrote this file. As long as you retain this notice you
8can do whatever you want with this stuff. If we meet some day, and you think
9this stuff is worth it, you can buy us a beer.
10----------------------------------------------------------------------------
11
12This script calculates the mean of the SQM magnitude for a given run
13(run is given by its start_ and stop_time)
14and the p-value for a linear fit.
15
16We hope that this will be used to separate good data, where
17the linear fit is perfect (p-value > 1e-3?) and bad fits,
18where clouds let the magnitude brightness fluctuate stronger,
19so the fit worsens.
20
21Returns: a string
22 "results {mean magnitude:f} {p-value:f}"
23"""
24
25from astropy.io import fits
26import numpy as np
27import scipy as sp
28import sys
29import ROOT
30
31import pandas as pd
32from sqlalchemy import create_engine
33
34import glob
35
36from calendar import timegm
37import time
38
39database = {
40 'user': 'factread',
41 'password': 'r3adfac!',
42 'host': '129.194.168.95',
43 'table': 'factdata',
44}
45
46root_database = {
47 'user': 'root',
48 'password': '1440Gapd',
49 'host': 'localhost',
50 'table': 'factdata',
51}
52
53
54db_string = '{user}:{password}@{host}/{table}'
55
56try:
57 factdb = create_engine('mysql+mysqldb://'+ db_string.format(**database))
58 factdb_root = create_engine('mysql+mysqldb://'+ db_string.format(**root_database))
59except ImportError:
60 factdb = create_engine('mysql+pymysql://'+db_string.format(**database))
61 factdb_root = create_engine('mysql+pymysql://'+db_string.format(**root_database))
62
63
64def get_list_of_SQM_files(base_path='/daq/aux'):
65 return sorted(glob.glob(base_path+'/*/*/*/*.SQM_CONTROL_DATA.fits'))
66
67def get_y_m_d(file_path):
68 s = file_path.split('/')[-1].split('.')[0]
69 return s[0:4], s[4:6], s[6:8]
70
71def get_night(file_path):
72 return file_path.split('/')[-1].split('.')[0]
73
74def mag_mean_p_value(fits_file_path, start_time, stop_time):
75
76 d = fits.open(fits_file_path)[1].data
77 d = d[(d['Time'] > start_time) * (d['Time'] < stop_time)]
78 if len(d)==0:
79 return None
80
81 x = d['Time'].copy() - d['Time'][0]
82 y = d['Mag'].astype(np.float64).copy()
83 start_time, stop_time = x[0], x[-1]
84
85 sigma = 0.025 / np.sqrt(12.)
86 g = ROOT.TGraphErrors(len(x), x, y, np.zeros(len(x), dtype=np.float64), np.ones(len(x), dtype=np.float64)*sigma)
87 f = ROOT.TF1("linfit", "pol1", start_time, stop_time)
88 g.Fit(f, "E")
89
90 function = f.Eval
91 y_fit = map(function, x)
92
93 result = {
94 'fSqmMagMean' : d['Mag'].mean(),
95 'fSqmMagLinFitPValue' : f.GetProb(),
96 'fSqmMagLinFitChi2' : f.GetChisquare(),
97 'fSqmMagLinFitNdf' : f.GetNDF(),
98 'fSqmMagLinFitSlope' : f.GetParameters()[1],
99 }
100
101 return result
102
103def timestamp_from_string(stri):
104 return timegm(time.strptime(stri.replace('Z', 'UTC'),'%Y-%m-%d %H:%M:%S%Z'))
105
106def update_dict_in_database(some_dict, db, primary_keys=("fNight", "fRunID")):
107 commands = []
108 commands.append('BEGIN;')
109
110 # "UPDATE RunInfo SET weight = 160, desiredWeight = 145 WHERE id = 1;"
111 update_string = "UPDATE RunInfo SET "
112 first = True
113 for k in some_dict:
114 if k not in primary_keys:
115 if not first:
116 update_string += ', '
117 else:
118 first = False
119
120 update_string += "{0} = {1}".format(k, some_dict[k])
121 update_string += " WHERE "
122 first = True
123 for k in some_dict:
124 if k in primary_keys:
125 if not first:
126 update_string += ' AND '
127 else:
128 first = False
129 update_string += "{0} = {1}".format(k, some_dict[k])
130 update_string += ';'
131
132 commands.append(update_string)
133 commands.append('COMMIT;')
134
135 # print commands
136 for com in commands:
137 db.engine.execute(com)
138
139
140if __name__ == "__main__":
141 aux_files = get_list_of_SQM_files()
142 for aux_file in aux_files:
143 night = get_night(aux_file)
144
145 query = ("SELECT fRunID, fRunStart, fRunStop from RunInfo"
146 " WHERE fNight={0}").format(night)
147
148 df = pd.read_sql_query(query, factdb)
149
150 for i in range(df.shape[0]):
151 row = df.iloc[i]
152
153 try:
154 run_start = timestamp_from_string(str(row['fRunStart'])+'Z')/(24.*3600.)
155 run_stop = timestamp_from_string(str(row['fRunStop'])+'Z')/(24.*3600.)
156 run_id = row['fRunID']
157 except ValueError as e:
158 print e
159 print row['fRunStart'], row['fRunStop']
160 continue
161
162 result= mag_mean_p_value(aux_file, run_start, run_stop)
163 if result is None:
164 continue
165 result['fNight'] = int(night)
166 result['fRunID'] = run_id
167
168 update_dict_in_database(result, factdb_root)
169
170 print time.asctime(), aux_file, run_start, run_stop, result
171
Note: See TracBrowser for help on using the repository browser.