def rev_data(estacion, fecha, contaminante, tipo):
oztool = ContIOTools()
conexion = SqlCont()
conn = conexion.getPostgresConn()
cur = conn.cursor()
tempData = pd.read_sql_query(
"""SELECT COUNT(fecha) FROM {0} WHERE fecha = '{1}' AND id_est = '{2}' AND id_tipo_pronostico = {3}; """
.format(contaminante, fecha, estacion, tipo), conn)
return tempData['count'][0]
def buildClass(allData, estation, contaminant, delta):
"""
Function so that every date in the allData table is added n hours and get the value that belongs to it.
:param allData: is data extracted the function readData
:type allData: DataFrame.
:param estation: station from wich the data will be taken.
:type estation: string.
:param contaminant: contaminant from wich the data will be taken.
:type contaminant: string.
:param delta : number of hours added to the original.
:type delta: int, float
:return: build
:rtype: DataFrame
"""
oztool = ContIOTools()
conexion = SqlCont()
conn = conexion.getPostgresConn()
cur = conn.cursor()
#conexion database
tableContaminant = oztool.findTable(contaminant)
# name the contaminant in the database
fechas = allData['fecha']
build = pd.DataFrame(fechas, columns=['fecha'])
cont = len(build.index) - 1
values = np.ones((cont + 1, 1)) * -1
#building the DataFrame, the first column is the date
x = 0
for xv in estation:
name = tableContaminant + '_' + xv + '_delta'
#name the column in the DataFrame
while x <= cont:
#Construction of the second column having the pollutant value
time = build.ix[x, 'fecha']
time = time + timedelta(hours=delta)
#You add the delta to the date
sql = """SELECT val FROM {0} WHERE id_est ='{1}' AND fecha ='{2}';""".format(
tableContaminant, xv, time)
cur.execute(sql)
temp = cur.fetchall()
tempValue = np.array(temp)
if len(tempValue) != 0:
#If the query is empty it puts -1 in the value of the column
values[x, 0] = tempValue[0, 0]
x = x + 1
else:
values[x, 0] = np.nan
x = x + 1
temBuild = pd.DataFrame(values, columns=[name])
x = 0
build[name] = temBuild
conn.commit()
cur.close()
#The connection to the database is closed
build.dropna()
return build.fillna(value=-1)
def readData_corr(startDate, endDate, estations, contaminant):
"""
Function to extract information from the database.
:param startDate: range of data wit wich the vaues of tue query are extracted.
:type startDate: timedata
:param endDate : range of data wit wich the vaues of tue query are extracted.
:type endDate: timedata
:param estations: set the stations to get the values.
:type estations : list with the stations
:return : allData all data that was taken from the database.
:rtype: DataFrame
"""
oztool = ContIOTools()
tables_contaminants = oztool.getTables()
cont = oztool.findTable(contaminant)
conexion = SqlCont()
conn = conexion.getPostgresConn()
cur = conn.cursor()
#conexion for the database
allData = pd.read_sql_query(
"""SELECT fecha FROM {0} WHERE id_est ='{1}' AND fecha >= '{2}' AND fecha <= '{3}' ORDER BY fecha ASC;"""
.format(cont, estations[0], startDate, endDate), conn)
#query the dates in the gives range
numberows = len(
allData.index) #Numbers the data given by the previous query
for x in estations:
for y in tables_contaminants:
name = y + '_' + x.lower()
#name the column in the DataFrame
tempDataValues = pd.read_sql_query(
"""SELECT fecha, val as {0} FROM {1} WHERE id_est = '{2}' AND fecha >= '{3}' AND fecha <= '{4}'ORDER BY fecha ASC;"""
.format(name, y, x, startDate, endDate), conn)
#query the values in the gives rangefrom Utilites.FormatData import FormatData as fdfrom Utilites.FormatData import FormatData as fd
if tempDataValues.empty:
#if the query is empty fill it will -1
tempData = pd.DataFrame(np.ones((numberows, 1)) * -1,
columns=[y + '_' + x.lower()])
#allData[y + '_' + x.lower()]= tempData;
#pass;
elif not tempDataValues.empty:
allData = allData.merge(tempDataValues,
how='left',
on='fecha')
#allData[name]=tempDataValues[y+'_'+x.lower()];
conn.commit()
cur.close()
#The connection to the database is closed
#allData=allData.dropna(how = 'any');
#return allData.fillna(value=-1);
return allData
def buildClass2(allData, estation, contaminant, delta, startDate, endDate):
"""
Function so that every date in the allData table is added n hours and get the value that belongs to it.
:param allData: is data extracted the function readData
:type allData: DataFrame.
:param estation: station from wich the data will be taken.
:type estation: string.
:param contaminant: contaminant from wich the data will be taken.
:type contaminant: string.
:param delta : number of hours added to the original.
:type delta: int, float
:return: build
:rtype: DataFrame
"""
oztool = ContIOTools()
conexion = SqlCont()
conn = conexion.getPostgresConn()
cur = conn.cursor()
#conexion database
tableContaminant = oztool.findTable(contaminant)
# name the contaminant in the database
fechas = allData['fecha']
build = pd.DataFrame(fechas, columns=['fecha'])
cont = len(build.index)
values = np.ones((cont, 1)) * -1
start = datetime.strptime(startDate, '%Y/%m/%d')
end = datetime.strptime(endDate, '%Y/%m/%d')
startDelta = start + timedelta(hours=delta)
#You add the delta to the date
endDelta = end + timedelta(hours=delta)
#You add the delta to the date
for xv in estation:
name = tableContaminant + '_' + xv + '_delta'
#name the column in the DataFrame
sql = pd.read_sql_query(
"""SELECT fecha, val FROM {0} WHERE id_est ='{1}' AND fecha >= '{2}' AND fecha <= '{3}' ORDER BY fecha ASC;"""
.format(tableContaminant, xv, startDelta, endDelta), conn)
valPredic = sql['val']
if valPredic.empty:
temBuild = pd.DataFrame(values, columns=[name])
build[name] = temBuild
else:
build[name] = valPredic
conn.commit()
cur.close()
#The connection to the database is closed
return build
def get_climatology(fechaInicio, fechaFinal, estacion):
"""
function to bring the climatology data stored in the database
:param fechaInicio: range of data wit wich the vaues of tue query are extracted.
:type fechaInicio: datetime
:param fechaFinal: range of data wit wich the vaues of tue query are extracted.
:type fechaFinal: datetime
:param estacion: set the stations to get the values
:type estacion: String
:return: all data that was taken from the database.
"""
oztool = ContIOTools()
conexion = SqlCont()
conn = conexion.getPostgresConn()
cur = conn.cursor()
allData = pd.DataFrame()
fechaMeta = fechaInicio
while fechaMeta <= fechaFinal:
month_init = fechaMeta.month
hour_init = datetime.strptime(
str(fechaMeta.hour) + ':00:00', '%H:%M:%S')
tempData = pd.read_sql_query(
"""SELECT hora FROM climatologia WHERE id_est = '{0}' AND mes = {1} AND hora = '{2}' AND id_tabla = '{3}';"""
.format(estacion, month_init, hour_init, 'cont_otres'), conn)
contaminants = oztool.getTables()
for xs in contaminants:
temp = pd.read_sql_query(
"""SELECT hora, val FROM climatologia WHERE id_est = '{0}' AND mes = {1} AND hora = '{2}' AND id_tabla = '{3}';"""
.format(estacion, month_init, hour_init, xs), conn)
temp.rename(columns={
'fecha': 'fecha',
'val': xs + '_' + estacion.lower()
},
inplace=True)
if temp.empty:
pass
else:
tempData = tempData.merge(temp, how='left', on='hora')
conn.commit()
cur.close()
allData = pd.concat([allData, tempData], axis=0)
fechaMeta = fechaMeta + timedelta(hours=1)
allData = allData.reset_index(drop=True)
return allData
def get_forecast(nameContaminant, estacion, tipo):
"""
function that brings from the database the last prediction saved
:param nameContaminant: name of the pollutant
:type nameContaminant: SyntaxWarning
:param estacion: name of the weather station
:type estacion: String
:return: DataFrame
"""
oztool = ContIOTools()
conexion = SqlCont()
conn = conexion.getPostgresConn()
cur = conn.cursor()
nameTable = nameContaminant
tempDataValues = pd.read_sql_query(
"""SELECT * FROM {0} WHERE id_est = '{1}' AND id_tipo_pronostico = {2} ORDER BY fecha DESC LIMIT 1; """
.format(nameTable, estacion, tipo), conn)
conn.commit()
cur.close()
return tempDataValues
def saveData(estacion, fecha, Valor, contaminant, tipo):
"""
function to save the prediction in the database
:param estacion: name of the station to which the prediction belongs
:type estacion: string
:param fecha: prediction date
:type fecha: date
:param valor: prediction value
:type valor: float32
"""
table = contaminant
oztool = ContIOTools()
conexion = SqlCont()
conn = conexion.getPostgresConn()
cur = conn.cursor()
#conexion database
sql = """INSERT INTO {0}(fecha,val,id_est, id_tipo_pronostico) VALUES (\'{1}\',{2},\'{3}\',{4});""".format(
table, fecha, Valor[0], estacion, tipo)
cur.execute(sql)
conn.commit()
cur.close()