def max_temp_select(weatherapp):
if weatherapp == 'accuweathercom':
accuweather_compare_query = se2.execute(
'select dwd.postcode, dwd.measure_date, dwd.max_temp, app.max_temp from (select dwd.postcode, dwd.max_temp, dwd.measure_date from dwd where measure_date > 20180522 and dwd.max_temp <> NULL) as dwd join (select sum(app.max_temp)/count(app.max_temp) as max_temp, measure_date, postcode from accuweathermaporg as app where app.max_temp <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
max_temp_compare = fle.getResult(se2, accuweather_compare_query)
elif weatherapp == 'openweathermaporg':
openweather_compare_query = se3.execute(
'select dwd.postcode, dwd.measure_date, dwd.max_temp, app.max_temp from (select dwd.postcode, dwd.max_temp, dwd.measure_date from dwd where measure_date > 20180522 and dwd.max_temp <> NULL) as dwd join (select sum(app.max_temp)/count(app.max_temp) as max_temp, measure_date, postcode from openweathermaporg as app where app.max_temp <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
max_temp_compare = fle.getResult(se3, openweather_compare_query)
elif weatherapp == 'wettercom':
wettercom_compare_query = se4.execute(
'select dwd.postcode, dwd.measure_date, dwd.max_temp, app.max_temp from (select dwd.postcode, dwd.max_temp, dwd.measure_date from dwd where measure_date > 20180522 and dwd.max_temp <> NULL) as dwd join (select sum(app.max_temp)/count(app.max_temp) as max_temp, measure_date, postcode from wettercom as app where app.max_temp <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
max_temp_compare = fle.getResult(se4, wettercom_compare_query)
elif weatherapp == 'wetterde':
wetterde_compare_query = se5.execute(
'select dwd.postcode, dwd.measure_date, dwd.max_temp, app.max_temp from (select dwd.postcode, dwd.max_temp, dwd.measure_date from dwd where measure_date > 20180522 and dwd.max_temp <> NULL) as dwd join (select sum(app.max_temp)/count(app.max_temp) as max_temp, measure_date, postcode from wetterde as app where app.max_temp <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
max_temp_compare = fle.getResult(se5, wetterde_compare_query)
else:
wetterdienstde_compare_query = se6.execute(
'select dwd.postcode, dwd.measure_date, dwd.max_temp, app.max_temp from (select dwd.postcode, dwd.max_temp, dwd.measure_date from dwd where measure_date > 20180522 ) as dwd join (select sum(app.max_temp)/count(app.max_temp) as max_temp, measure_date, postcode from wetterdienstde as app where app.max_temp <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
max_temp_compare = fle.getResult(se6, wetterdienstde_compare_query)
return max_temp_compare
def wind_speed_select(weatherapp):
if weatherapp == 'accuweathercom':
accuweather_compare_query = se2.execute(
'select dwd.postcode, dwd.measure_date, average_wind_speed, wind_speed from (select dwd.postcode, dwd.average_wind_speed, dwd.measure_date from dwd where measure_date > 20180522 and average_wind_speed <> NULL) as dwd join (select sum(wind_speed)/count(wind_speed) as wind_speed, measure_date, postcode from accuweathercom as app where wind_speed <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
wind_speed_compare = fle.getResult(se2, accuweather_compare_query)
elif weatherapp == 'openweathermaporg':
openweather_compare_query = se3.execute(
'select dwd.postcode, dwd.measure_date, average_wind_speed, wind_speed from (select dwd.postcode, dwd.average_wind_speed, dwd.measure_date from dwd where measure_date > 20180522 and average_wind_speed <> NULL) as dwd join (select sum(wind_speed)/count(wind_speed) as wind_speed, measure_date, postcode from openweathermaporg as app where wind_speed <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
wind_speed_compare = fle.getResult(se3, openweather_compare_query)
elif weatherapp == 'wettercom':
wettercom_compare_query = se4.execute(
'select dwd.postcode, dwd.measure_date, average_wind_speed, wind_speed from (select dwd.postcode, dwd.average_wind_speed, dwd.measure_date from dwd where measure_date > 20180522 and average_wind_speed <> NULL) as dwd join (select sum(wind_speed)/count(wind_speed) as wind_speed, measure_date, postcode from wettercom as app where wind_speed <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
wind_speed_compare = fle.getResult(se4, wettercom_compare_query)
elif weatherapp == 'wetterde':
wetterde_compare_query = se5.execute(
'select dwd.postcode, dwd.measure_date, average_wind_speed, wind_speed from (select dwd.postcode, dwd.average_wind_speed, dwd.measure_date from dwd where measure_date > 20180522 and average_wind_speed <> NULL) as dwd join (select sum(wind_speed)/count(wind_speed) as wind_speed, measure_date, postcode from wetterde as app where wind_speed <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
wind_speed_compare = fle.getResult(se5, wetterde_compare_query)
else:
wetterdienstde_compare_query = se6.execute(
'select dwd.postcode, dwd.measure_date, average_wind_speed, wind_speed from (select dwd.postcode, dwd.average_wind_speed, dwd.measure_date from dwd where measure_date > 20180522 and average_wind_speed <> NULL) as dwd join (select sum(wind_speed)/count(wind_speed) as wind_speed, measure_date, postcode from wetterdienstde as app where wind_speed <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
wind_speed_compare = fle.getResult(se6, wetterdienstde_compare_query)
return wind_speed_compare
def cloud_select(weatherapp):
if weatherapp == 'accuweathercom':
accuweather_compare_query = se2.execute(
'select dwd.postcode, dwd.measure_date,coverage_amount, clouds_temp from (select dwd.postcode, coverage_amount, dwd.measure_date from dwd where measure_date > 20180522 and coverage_amount <> NULL) as dwd join (select sum(app.clouds)/count(app.clouds) as clouds, measure_date, postcode from accuweathercom as app where clouds <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
cloud_compare = fle.getResult(se2, accuweather_compare_query)
elif weatherapp == 'openweathermaporg':
openweather_compare_query = se3.execute(
'select dwd.postcode, dwd.measure_date,coverage_amount, clouds_temp from (select dwd.postcode, coverage_amount, dwd.measure_date from dwd where measure_date > 20180522 and coverage_amount <> NULL) as dwd join (select sum(app.clouds)/count(app.clouds) as clouds, measure_date, postcode from openweathermaporg, as app where clouds <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
cloud_compare = fle.getResult(se3, openweather_compare_query)
elif weatherapp == 'wettercom':
wettercom_compare_query = se4.execute(
'select dwd.postcode, dwd.measure_date,coverage_amount, clouds_temp from (select dwd.postcode, coverage_amount, dwd.measure_date from dwd where measure_date > 20180522 and coverage_amount <> NULL) as dwd join (select sum(app.clouds)/count(app.clouds) as clouds, measure_date, postcode from wettercom as app where clouds <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
cloud_compare = fle.getResult(se4, wettercom_compare_query)
elif weatherapp == 'wetterde':
wetterde_compare_query = se5.execute(
'select dwd.postcode, dwd.measure_date,coverage_amount, clouds_temp from (select dwd.postcode, coverage_amount, dwd.measure_date from dwd where measure_date > 20180522 and coverage_amount <> NULL) as dwd join (select sum(app.clouds)/count(app.clouds) as clouds, measure_date, postcode from wetterde as app where clouds <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
cloud_compare = fle.getResult(se5, wetterde_compare_query)
else:
wetterdienstde_compare_query = se6.execute(
'select dwd.postcode, dwd.measure_date,coverage_amount, clouds_temp from (select dwd.postcode, coverage_amount, dwd.measure_date from dwd where measure_date > 20180522 and coverage_amount <> NULL) as dwd join (select sum(app.clouds)/count(app.clouds) as clouds, measure_date, postcode from wetterdienstde as app where clouds <> NULL group by measure_date, postcode) as app on dwd.measure_date = app.measure_date'
)
cloud_compare = fle.getResult(se6, wetterdienstde_compare_query)
return cloud_compare
def plot_mintemp(tuplelist = None, measure_date = None, x_day_forecast = None):
datalist = []
lgnd = []
for (table, se) in tuplelist:
if str(table) == 'openweathermaporg' :
wetterdienst_maxTemp = fl.getResult(se.execute(f'SELECT min_temp FROM {str(table)} WHERE measure_date > {str(measure_date)} and measure_date_prediction - measure_date = {x_day_forecast} '), se)
datalist.append(wetterdienst_maxTemp)
else :
wetterdienst_maxTemp = fl.getResult(se.execute(f'SELECT max_temp FROM {str(table)} WHERE measure_date > {str(measure_date)} and measure_date_prediction - measure_date = {x_day_forecast} '), se)
datalist.append(wetterdienst_maxTemp)
lgnd.append(str(table))
plot(datalist, lgnd, title = 'MinTemp')
def boxplot_windspeed(tuplelist = None, measure_date = None, x_day_forecast = None) :
datalist = []
lgnd = []
for (table, se) in tuplelist:
wetterdienst_sunhours = fl.getResult(se.execute(f'SELECT wind_speed FROM {str(table)} WHERE measure_date > {str(measure_date)} and measure_date_prediction - measure_date = {x_day_forecast} '), se)
datalist.append(wetterdienst_sunhours[wetterdienst_sunhours != np.array(None)])
lgnd.append(str(table))
boxplot(datalist, lgnd, title = 'WindSpeed')
def boxplot_mintemp(tuplelist = None, measure_date = None, x_day_forecast = None):
'''
dimension represents data values (columns of service provider) which will be plotted
Plots compare dwd data with each service provider of weather data
Not recommended to plot "cloud" because of its natural appearance (x/8)'''
datalist = []
lgnd = []
for (table, se) in tuplelist:
if str(table) == 'openweathermaporg' :
wetterdienst_maxTemp = fl.getResult(se.execute(f'SELECT min_temp FROM {str(table)} WHERE measure_date > {str(measure_date)} and measure_date_prediction - measure_date = {x_day_forecast} '), se)
datalist.append(wetterdienst_maxTemp)
else :
wetterdienst_maxTemp = fl.getResult(se.execute(f'SELECT max_temp FROM {str(table)} WHERE measure_date > {str(measure_date)} and measure_date_prediction - measure_date = {x_day_forecast} '), se)
datalist.append(wetterdienst_maxTemp)
lgnd.append(str(table))
boxplot(datalist, lgnd, title = 'MinTemp')
def main(): data_dwd, dwd_se = fl.getConnectionDWD() data_wetterdienst, wd_se = fl.getConnectionWetterdienstde() data_wetterde, we_se = fl.getConnectionWetterde() data_wettercom, wc_se = fl.getConnectionWettercom() data_openweather, ow_se = fl.getConnectionOpenWeatherMaporg() data_accuweather, aw_se = fl.getConnectionAccuweathercom() measure_date = 20180604 # x_day_forecast = 1 #Attention: 20180630 - 2018071 != 1 ... tuple_without_dwd = [(data_openweather, ow_se),(data_accuweather, aw_se), (data_wetterdienst, wd_se), (data_wettercom, wc_se)] boxPlot_maxtemp(tuple_without_dwd, measure_date, x_day_forecast) boxplot_mintemp(tuple_without_dwd, measure_date, x_day_forecast) boxplot_sunhours(tuple_without_dwd, measure_date, x_day_forecast) boxplot_windspeed(tuple_without_dwd, measure_date, x_day_forecast) plot_mintemp(tuple_without_dwd, measure_date, x_day_forecast) plot_maxtemp(tuple_without_dwd, measure_date, x_day_forecast) plt.show()
def start(self):
tmp = library.getConnectionDWD()
#Anfrage PLZ
# result=tmp[1].execute("""SELECT station_id,postcode, station_name, count(*)
# FROM dwd
# GROUP BY station_name
# """)
#result=tmp[1].execute("""SELECT station_id,postcode, station_name,measure_date
# FROM dwd
# Where station_name='Berlin-Tegel'
# ORDER BY measure_date ASC
# """)
#
# result=tmp[1].execute("""SELECT m1.measure_date, COUNT(m1.measure_date) as c FROM dwd m1
# Where (m1.station_name='BRAUNLAGE' OR m1.station_name='POTSDAM' OR
# m1.station_name='BREMEN' OR m1.station_name='TRIER-PETRISBERG' OR m1.station_name='FICHTELBERG') AND
# m1.measure_date>=19480101
# GROUP BY m1.measure_date
# """)
result = tmp[1].execute(
"""SELECT m1.station_name,m1.measure_date,m1.average_temp,m2.c
FROM dwd m1
join
(SELECT measure_date, COUNT(measure_date) as c FROM dwd
Where (station_name='BRAUNLAGE' OR station_name='POTSDAM' OR
station_name='BREMEN' OR station_name='TRIER-PETRISBERG' OR station_name='FICHTELBERG') AND
measure_date>=19480101
GROUP BY measure_date) m2
on (m1.measure_date = m2.measure_date)
Where (m1.station_name='BRAUNLAGE' OR m1.station_name='POTSDAM' OR
m1.station_name='BREMEN' OR m1.station_name='TRIER-PETRISBERG' OR m1.station_name='FICHTELBERG') AND
m1.measure_date>=19480101 and m2.c=5
""")
fold = [[] for i in range(6)]
res = [[] for i in range(6)]
tmp = [[] for i in range(5)]
tmpres = []
result = sorted(result, key=lambda element: (element[1]))
for line in result:
if (len(tmp[4]) == 7):
fold_num = np.random.randint(6, size=1)[0]
if len(fold[fold_num]) == 0:
fold[fold_num] = np.array([tmp])
else:
fold[fold_num] = np.append(fold[fold_num],
np.array([tmp]),
axis=0)
for i in range(5):
tmp[i] = tmp[i][1:]
res[fold_num].append(line[2])
if "Bremen" in line[0]:
tmp[0].append(line[2])
if "Braunlage" in line[0]:
tmp[2].append(line[2])
if "Potsdam" in line[0]:
tmp[3].append(line[2])
if "Fichtelberg" in line[0]:
tmp[1].append(line[2])
if "Trier" in line[0]:
tmp[4].append(line[2])
for i in range(6):
res[i] = np.array(res[i])
self.res1 = res[0]
self.res2 = res[1]
self.res3 = res[2]
self.res4 = res[3]
self.res5 = res[4]
self.res6 = res[5]
self.fold1 = fold[0].reshape((fold[0].shape[0], 5, 7, 1))
self.fold2 = fold[1].reshape((fold[1].shape[0], 5, 7, 1))
self.fold3 = fold[2].reshape((fold[2].shape[0], 5, 7, 1))
self.fold4 = fold[3].reshape((fold[3].shape[0], 5, 7, 1))
self.fold5 = fold[4].reshape((fold[4].shape[0], 5, 7, 1))
self.fold6 = fold[5].reshape((fold[5].shape[0], 5, 7, 1))
import pandas as pd
import numpy as np
import sys
#sys.path.append("C:/Users/Lukas Tilmann/mkp_database")
sys.path.append('/Users/kyrak/PycharmProjects/MKP/')
import FunctionLibraryExtended as fl
dwd, se = fl.getConnectionDWD()
acc, se_acc = fl.getConnectionAccuweathercom()
op, se_op = fl.getConnectionOpenWeatherMaporg()
wc, se_wc = fl.getConnectionWettercom()
wd, se_wd = fl.getConnectionWetterdienstde()
'''
Data available - copied from Slack:
1 - es gibt einen Eintrag
0 - Null
accuweather openweathermapporg wettercom wetterdienstde
measure_date 1 1 1 1
measure_date_hour 1 1 1 1
measure_date_prediction 1 1 1 1
measure_date_prediction_hour 1 1 1 1
postcode 0 1 1 1
city 1 1 0 0
temp 0 1 0 0
import numpy as np
import sys
sys.path.append("C:/Users/Lukas Tilmann/mkp_database")
import FunctionLibraryExtended as fl
dwd, se = fl.getConnectionDWD()
acc, se_acc = fl.getConnectionAccuweathercom()
op, se_op = fl.getConnectionOpenWeatherMaporg()
wdde, se_wd = fl.getConnectionWetterdienstde()
wde, se_wde = fl.getConnectionWetterde()
wc, se_wc = fl.getConnectionWettercom()
#creates timespan queries
dwd_timespan_query = se.execute(
"SELECT MAX(measure_date), MIN(measure_date) FROM dwd")
acc_timespan_query = se.execute(
"SELECT MAX(measure_date_prediction), MIN(measure_date_prediction) FROM accuweathercom"
)
op_timespan_query = se.execute(
"SELECT MAX(measure_date_prediction), MIN(measure_date_prediction) FROM openweathermaporg"
)
wdde_timespan_query = se.execute(
"SELECT MAX(measure_date_prediction), MIN(measure_date_prediction) FROM wetterdienstde"
)
wde_timespan_query = se.execute(
"SELECT MAX(measure_date_prediction), MIN(measure_date_prediction) FROM wetterde"
)
wc_timespan_query = se.execute(
"SELECT MAX(measure_date_prediction), MIN(measure_date_prediction) FROM wettercom"
def boxPlotAvgTemp():
'''
dimension represents data values (columns of service provider) which will be plotted
Plots compare dwd data with each service provider of weather data
Not recommended to plot "cloud" because of its natural appearance (x/8)'''
data_dwd, dwd_se = fl.getConnectionDWD()
data_wetterdienst, wd_se = fl.getConnectionWetterdienstde()
data_wetterde, we_se = fl.getConnectionWetterde()
data_wettercom, wc_se = fl.getConnectionWettercom()
data_openweather, ow_se = fl.getConnectionOpenWeatherMaporg()
data_accuweather, aw_se = fl.getConnectionAccuweathercom()
#session_list = []
#session_list.extend([wd_se, wc_se, ow_se, aw_se])
#table_list = []
#table_list.extend([data_wetterdienst, data_wetterde, data_wettercom, data_openweather, data_accuweather])
tuple_list = [(data_openweather, ow_se),(data_accuweather, aw_se), (data_wetterdienst, wd_se), (data_wetterde, we_se), (data_wettercom, wc_se)]
dataList = []
for (table, se) in tuple_list :
wetterdienst_maxTemp = fl.getResult(se.execute('SELECT max_temp FROM ' + str(table)), se)
wetterdienst_minTemp = fl.getResult(se.execute('SELECT min_temp FROM ' + str(table)), se)
wetterdienst_avgTemp = (wetterdienst_maxTemp*58 + wetterdienst_minTemp* 42)/100 #Juni18 überd. warm, 13 Sonnenstunden ca. => gew. Temp
dataList.append(wetterdienst_avgTemp)
print(dataList[0])
#data_dwd = pd.read_sql('SELECT dimension FROM dwd', con = fl.getConnectionDWD))
#data_wetterdienst = pd.read_sql('SELECT dimension FROM wetterdienstde', con = fl.getConnectionWetterdienstde))
#data_wetterde = pd.read_sql('SELECT dimension FROM wetterde', con = fl.getConnectionWetterde))
#data_wettercom = pd.read_sql('SELECT dimension FROM wettercom', con = fl.getConnectionWettercom))
#data_openweather = pd.read_sql('SELECT dimension FROM openweathermaporg', con = fl.getConnectionOpenWeatherMaporg))
#data_accuweather = pd.read_sql('SELECT dimension FROM accuweathercom', con = fl.getConnectionAccuweathercom))
dataList.append(dwd_se.execute('SELECT average_temp FROM dwd'))
sample_query = dwd_se.execute('SELECT average_temp FROM dwd')
FunctionLibraryExtended.getResult(sample_query, dwd_se)
# dataList.append(pd.read_sql('SELECT average_temp FROM dwd', con = fl.getConnectionDWD()))
# dataList.append(pd.read_sql('SELECT maxTemp FROM getConnectionWetterdienstde', con = fl.getConnectionWetterdienstde()))
# dataList.append(pd.read_sql('SELECT maxTemp+minTemp FROM wetterde', con = fl.getConnectionWetterde()))
# dataList.append(pd.read_sql('SELECT (maxTemp+minTemp)/2 FROM wettercom', con = fl.getConnectionWettercom()))
# dataList.append(pd.read_sql('SELECT (maxTemp+minTemp)/2 FROM openweathermaporg', con = fl.getConnectionOpenWeatherMaporg()))
# dataList.append(pd.read_sql('SELECT (maxTemp+minTemp)/2 FROM accuweathercom', con = fl.getConnectionAccuweathercom()))
fig = plt.figure(figsize = (5,8))
for data in dataList:
data.plot(kind = 'box')
fig.add_subplot(111)
df.data.plot()
#ax = fig.add_subplot(111)
#bp = ax.boxplot(dataPlot)
#ax.set_xticklabels(labels)
fig.show()
plt.show()
sys.path.append('/Users/kyrak/PycharmProjects/MKP/')
import pandas as pd
import scipy as sc
from scipy import stats
import FunctionLibraryExtended as fle
import MySql
from sqlalchemy import create_engine
import FunctionLibraryExtended as fle
import sqlalchemy as sqla
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
import numpy as np
import matplotlib
import matplotlib.pyplot as plt
dwd, se1 = fle.getConnectionDWD()
acc, se2 = fle.getConnectionAccuweathercom()
openW, se3 = fle.getConnectionOpenWeatherMaporg()
wetter_com, se4 = fle.getConnectionWettercom()
wetter_de, se5 = fle.getConnectionWetterde()
wetter_dienst, se6 = fle.getConnectionWetterdienstde()
"""
Queries:
1. Min_Temp
2. Max_Temp
3. Clouds
4. Wind_Speed
5. Precipitation
Functions:
1. Difference