You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

aggregateweather.py 5.8KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179
  1. #!/usr/bin/python3
  2. from datetime import datetime
  3. from eto import ETo, datasets
  4. from io import StringIO
  5. import argparse
  6. import configparser
  7. import mysql.connector
  8. import pandas as pd
  9. import requests
  10. import sys
  11. def usage():
  12. print(
  13. "Usage: " + __file__ +
  14. "[--config path_to_file.ini] [--station smhi_station_number ] --database database] [--host dbhost] [--user dbuser] [--password dbpassword]\n"
  15. "Default configfile is weather.ini, any parameter can be overwritten on the command line"
  16. )
  17. parser = argparse.ArgumentParser()
  18. parser.add_argument('--config')
  19. parser.add_argument('--database')
  20. parser.add_argument('--host')
  21. parser.add_argument('--user')
  22. parser.add_argument('--password')
  23. parser.add_argument('--station')
  24. args = parser.parse_args()
  25. config_file = "weather.ini"
  26. if args.config:
  27. config_file = args.config
  28. config = configparser.ConfigParser()
  29. config.read(config_file)
  30. db = config['MySQL']['database']
  31. host = config['MySQL']['host']
  32. user = config['MySQL']['user']
  33. password = config['MySQL']['password']
  34. station = config['SMHI']['station']
  35. # Defaults
  36. z_msl = 48.854
  37. lat = 59.178503
  38. lon = 17.909265
  39. TZ_lon = lon
  40. freq = 'D'
  41. if args.station:
  42. station = args.station
  43. if not station:
  44. station = "97100"
  45. if args.database:
  46. db = args.database
  47. if args.host:
  48. host = args.host
  49. if not host:
  50. host = "localhost"
  51. if args.user:
  52. user = args.usr
  53. if args.password:
  54. password = args.password
  55. if not (db and host and user and password):
  56. usage()
  57. sys.exit(1)
  58. pd.options.mode.chained_assignment = None
  59. station_data = requests.get(
  60. url=
  61. 'https://opendata-download-metobs.smhi.se/api/version/1.0/parameter/1/station/{}.json'
  62. .format(station)).json()
  63. newest_to = 0
  64. for i in station_data['position']:
  65. if i['to'] > newest_to:
  66. z_msl = i['height']
  67. lat = i['latitude']
  68. lon = i['longitude']
  69. TZ_lon = lon
  70. mydb = mysql.connector.connect(auth_plugin='mysql_native_password',
  71. database=db,
  72. host=host,
  73. passwd=password,
  74. user=user)
  75. cursor = mydb.cursor()
  76. date_select = ("SELECT DISTINCT `date` FROM weather WHERE station = {}".format(station))
  77. s_name = ""
  78. cursor.execute(date_select)
  79. dates = cursor.fetchall()
  80. csv = "date,T_max,T_min,T_mean,RH_max,RH_min,RH_mean,Rainfall\n"
  81. for i in dates:
  82. working_date = i[0].strftime('%Y-%m-%d')
  83. day_select = ('SELECT * FROM weather '
  84. 'WHERE date = "{}" AND station = {}'.format(working_date,station))
  85. cursor.execute(day_select)
  86. day = cursor.fetchall()
  87. sum_rain = 0
  88. T_max = -9999
  89. T_min = 9999
  90. RH_max = -9999
  91. RH_min = 9999
  92. sum_temp = 0
  93. sum_rel_hum = 0
  94. counter = 0
  95. for j in day:
  96. counter += 1
  97. #+----------------+---------------+------+-----+---------+----------------+
  98. #| Field | Type | Null | Key | Default | Extra |
  99. #+----------------+---------------+------+-----+---------+----------------+
  100. #| observation_id | int | NO | PRI | NULL | auto_increment |
  101. #| date | date | YES | MUL | NULL | |
  102. #| time | time | YES | | NULL | |
  103. #| rainfall | float | YES | | NULL | |
  104. #| rel_hum | decimal(10,0) | YES | | NULL | |
  105. #| temp | float | YES | | NULL | |
  106. #| windspeed | float | YES | | NULL | |
  107. #| station | int | YES | | NULL | |
  108. #| station_name | varchar(255) | YES | | NULL | |
  109. #| winddir | int | YES | | NULL | |
  110. #+----------------+---------------+------+-----+---------+----------------+
  111. observation_id = 0
  112. date = 1
  113. time = 2
  114. rainfall = 3
  115. rel_hum = 4
  116. temp = 5
  117. #windspeed = 6
  118. #station = 7
  119. station_name = 8
  120. #winddir = 9
  121. sum_rain += j[rainfall]
  122. sum_temp += j[temp]
  123. sum_rel_hum += j[rel_hum]
  124. s_name = j[station_name]
  125. if T_max < j[temp]:
  126. T_max = j[temp]
  127. if T_min > j[temp]:
  128. T_min = j[temp]
  129. if RH_max < j[rel_hum]:
  130. RH_max = j[rel_hum]
  131. if RH_min > j[rel_hum]:
  132. RH_min = j[rel_hum]
  133. T_mean = sum_temp / counter
  134. RH_mean = sum_rel_hum / counter
  135. csv += working_date + "," + str(T_max) + "," + str(T_min) + "," + str(
  136. T_mean) + "," + str(RH_max) + "," + str(RH_min) + "," + str(
  137. RH_mean) + "," + str(sum_rain) + "\n"
  138. DATA = StringIO(csv)
  139. tsdata = pd.read_csv(DATA,
  140. parse_dates=True,
  141. infer_datetime_format=True,
  142. index_col='date')
  143. et1 = ETo()
  144. et1.param_est(tsdata, freq, z_msl, lat, lon, TZ_lon)
  145. et1.ts_param.head()
  146. eto1 = et1.eto_hargreaves()
  147. upsert = (
  148. "REPLACE INTO aggregated_weather "
  149. "(Date, T_max, T_min, T_mean, RH_max, RH_min, RH_mean, Rainfall, ETo, station, station_name) "
  150. "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")
  151. for key, value in eto1.items():
  152. aggdate = key.strftime('%Y-%m-%d')
  153. data = (aggdate, float(tsdata.loc[aggdate, 'T_max']),
  154. float(tsdata.loc[aggdate,
  155. 'T_min']), float(tsdata.loc[aggdate, 'T_mean']),
  156. float(tsdata.loc[aggdate,
  157. 'RH_max']), float(tsdata.loc[aggdate, 'RH_min']),
  158. float(tsdata.loc[aggdate, 'RH_mean']),
  159. float(tsdata.loc[aggdate, 'Rainfall']), float(value), int(station), s_name)
  160. cursor.execute(upsert, data)
  161. mydb.commit()
  162. mydb.close()