def main(argv): """Go Main Go.""" table = argv[1] nt = NetworkTable(["WFO", "RFC", "NWS", "NCEP", "CWSU", "WSO"]) pgconn = get_dbconn('afos', user='******') mpgconn = get_dbconn('mesosite') cursor = pgconn.cursor() mcursor = mpgconn.cursor() df = read_sql(""" SELECT source, count(*) from """ + table + """ WHERE source is not null GROUP by source ORDER by source """, pgconn, index_col='source') for source, row in df.iterrows(): if source[0] not in ['K', 'P']: continue if source in UNKNOWN: continue iemsource = source[1:] if source[0] == 'K' else source if iemsource in nt.sts: continue if source in XREF: cursor.execute(""" UPDATE """ + table + """ SET source = %s WHERE source = %s """, (XREF[source], source)) print(("Correcting %s -> %s, %s rows" ) % (source, XREF[source], cursor.rowcount)) else: if row['count'] < 10: print("skipping %s as row count is low" % (source, )) continue mcursor.execute(""" WITH centers as ( select id, geom::geography from stations where network in ('WFO', 'RFC', 'NWS', 'NCEP', 'CWSU', 'WSO') ), asos as ( SELECT geom::geography from stations where id = %s and network ~* 'ASOS' ) SELECT c.id as center, st_distance(c.geom, a.geom) from centers c, asos a ORDER by st_distance ASC """, (iemsource, )) if mcursor.rowcount < 5: print("Source: %s is double unknown" % (source, )) continue for i, row2 in enumerate(mcursor): print("%s %s %.2f" % (source, row2[0], row2[1])) if i > 4: break newval = input( "What do you want to do with %s (count:%s)? " % ( source, row['count'])) if len(newval) == 4: XREF[source] = newval else: UNKNOWN.append(source) print(json.dumps(XREF, indent=4)) print(UNKNOWN) cursor.close() pgconn.commit()
def main(): """Go!""" nt = NetworkTable("IA_ASOS") pgconn = get_dbconn('iem') df = read_sql(""" SELECT id, valid, tmpf::int as tmpf, dwpf::int as dwpf, sknt from current_log c JOIN stations t on (c.iemid = t.iemid) WHERE t.network = 'IA_ASOS' and c.valid > 'TODAY' and c.tmpf > 70 ORDER by id ASC """, pgconn, index_col=None) pgconn = get_dbconn('asos') for _, row in df.iterrows(): df2 = read_sql(""" SELECT valid, tmpf, dwpf, sknt from alldata WHERE station = %s and valid < '2018-10-03' and tmpf::int >= %s and dwpf::int >= %s and sknt >= %s ORDER by valid DESC """, pgconn, params=(row['id'], row['tmpf'], row['dwpf'], row['sknt']), index_col=None) if len(df2.index) > 5: continue lastdate = None if not df2.empty: lastdate = df2.iloc[0]['valid'].date() print( ("%s,%s,%s,%s,%.0f,%s,%s,%s" ) % (row['id'], row['valid'], row['tmpf'], row['dwpf'], row['sknt'], len(df2.index), lastdate, nt.sts[row['id']]['archive_begin'].year) )
def main(argv): """Go Main Go.""" afostable = argv[1] stations = load_stations() iem_pgconn = get_dbconn('iem') icursor = iem_pgconn.cursor() pgconn = get_dbconn('afos') cursor = pgconn.cursor() cursor.execute(""" SELECT entered, data from """ + afostable + """ WHERE substr(pil, 1, 3) = 'DSM' ORDER by entered """) updates = 0 for row in cursor: if row[1].startswith("\001"): try: dsm = parser(row[1], utcnow=row[0]) dsm.tzlocalize(stations) except Exception as exp: print(exp) print(row[1]) continue dsm.sql(icursor) else: try: dsm = process(row[1]) if dsm is None: continue dsm.compute_times(row[0]) dsm.tzlocalize(stations[dsm.station]) except Exception as exp: print(exp) print(row[1]) continue # print(row[1]) dsm.sql(icursor) # print("%s %s %s/%s %s\n\n" % ( # dsm.station, dsm.date, dsm.groupdict['high'], # dsm.groupdict['low'], dsm.groupdict['pday'])) updates += 1 if updates % 1000 == 0: icursor.close() iem_pgconn.commit() icursor = iem_pgconn.cursor() icursor.close() iem_pgconn.commit()
def main(): """Go """ pgconn = get_dbconn('coop', user='******') df = gpd.read_postgis(""" WITH data as ( SELECT station, year, avg((high+low)/2.) as avg_temp, sum(precip) as tot_precip, sum(snow) as tot_snow from alldata_ia where year >= 1998 and year < 2016 and station != 'IA0000' and substr(station, 3, 1) != 'C' GROUP by station, year ), agg as ( SELECT * from data WHERE tot_snow > 0 ) select a.*, t.geom from agg a JOIN stations t on (a.station = t.id) WHERE t.network = 'IACLIMATE' """, pgconn, geom_col='geom', index_col=None) df['cycles'] = 0 for station in tqdm.tqdm(df['station'].unique()): uri = ("http://iem.local/plotting/auto/plot/121/network:IACLIMATE::" "station:%s::thres1:30-32::dpi:100.csv") % (station, ) req = requests.get(uri) ldf = pd.read_csv(StringIO(req.content), index_col='year') ldf['total'] = ldf['30-32f'] + ldf['30-32s'] for year in range(1998, 2016): val = ldf.loc[year]['total'] df.loc[((df['year'] == year) & (df['station'] == station)), 'cycles'] = val for year in range(1998, 2016): df2 = df[df['year'] == year] df2.to_file('iowaclimate_%s.shp' % (year, )) shutil.copyfile('/mesonet/data/gis/meta/4326.prj', 'iowaclimate_%s.prj' % (year, ))
def main(): """Go Main Go""" pgconn = get_dbconn('postgis') sql = get_polygon() df = read_sql(sql, pgconn, index_col='year') print(df) (fig, ax) = plt.subplots(1, 1) ax.bar(df.index.values - 0.2, df['count'].values, width=0.4, fc='r') ax.set_ylabel("Warning Count", color='r') y2 = ax.twinx() y2.bar(df.index.values + 0.2, df['area'].values, width=0.4, fc='b') y2.set_ylabel("Size (Continental United States)", color='b') p1 = plt.Rectangle((0, 0), 1, 1, fc="r") p3 = plt.Rectangle((0, 0), 1, 1, fc="b") ax.legend([p1, p3], ["Counts", "Size"], loc=2) ax.grid(True) ax.set_title("NWS *Storm Based* Tornado + Severe Thunderstorm Warnings") ax.set_ylim(0, 90000) y2.set_ylim(0, 25) ax.set_xlim(1985.5, 2017.5) fig.text(0.01, 0.01, 'Generated %s' % (datetime.date.today(), )) fig.savefig('test.png')
def get_data(): """The data we want and the data we need""" pgconn = get_dbconn('coop', user='******') df = read_sql(""" select year, week_ending, num_value, state_alpha from nass_quickstats where commodity_desc = 'CORN' and statisticcat_desc = 'PROGRESS' and unit_desc = 'PCT SILKING' and util_practice_desc = 'ALL UTILIZATION PRACTICES' and num_value is not null ORDER by state_alpha, week_ending """, pgconn, index_col=None) df['week_ending'] = pd.to_datetime(df['week_ending']) data = {} for state, gdf in df.groupby('state_alpha'): sdf = gdf.copy() sdf.set_index('week_ending', inplace=True) newdf = sdf.resample('D').interpolate(method='linear') y10 = newdf[newdf['year'] > 2007] doyavgs = y10.groupby(y10.index.strftime("%m%d")).mean() lastdate = pd.Timestamp(newdf.index.values[-1]).to_pydatetime() data[state] = {'date': lastdate, 'avg': doyavgs.at[lastdate.strftime("%m%d"), 'num_value'], 'd2017': newdf.at[lastdate, 'num_value']} print("%s %s" % (state, data[state])) return data
def main(): """Go Main Go""" pgconn = get_dbconn('postgis') years = np.arange(1997, 2019) days = np.arange(1, 32) data = np.ma.zeros((len(years), len(days))) data[-1, -10:] = -1 cursor = pgconn.cursor() cursor.execute(""" SELECT extract(year from issued) as year, extract(day from issued) as day, count(*) from watches where type = 'TOR' and extract(month from issued) = 5 GROUP by year, day """) for row in cursor: data[int(row[0]) - 1997, int(row[1]) - 1] = row[2] data.mask = (data < 0) print(data) ax = plt.axes([0.1, 0.15, 0.9, 0.75]) cmap = plt.get_cmap('plasma') cmap.set_under('white') cmap.set_bad('gray') res = ax.imshow(np.flipud(data), cmap=cmap, extent=[0.5, 31.5, 1996.5, 2018.5], vmin=1, aspect='auto', zorder=4, alpha=0.8) plt.colorbar(res, label='count') ax.set_yticks(range(1998, 2019, 2)) ax.grid(True, zorder=3) ax.set_title("1997-2018 May\nStorm Prediction Center Daily Tornado Watch Count") ax.set_xlabel("CDT Calendar Day of May") plt.gcf().text(0.01, 0.01, '@akrherz, generated 22 May 2018') plt.gcf().savefig('test.png')
def main(): """Ingest things from Gio""" pgconn = get_dbconn('td') cursor = pgconn.cursor() df = pd.read_csv(sys.argv[1]) uniqueid = sys.argv[2] cursor.execute(""" DELETE from watertable_data where uniqueid = %s and valid between %s and %s """, (uniqueid, df['Date'].min(), df['Date'].max())) deleted = cursor.rowcount if deleted > 0: print("Removed %s" % (deleted,)) inserts = 0 for _idx, row in df.iterrows(): if row['Date'] == ' ' or row['Date'] is None: continue cursor.execute(""" INSERT into watertable_data (uniqueid, plotid, valid, depth_mm_qc, depth_mm) VALUES (%s, %s, %s, %s, %s) """, (uniqueid, row['plot'], row['Date'], row.get('WAT4'), row.get('WAT4'))) inserts += 1 print("Inserted %s, Deleted %s entries for %s" % (inserts, deleted, uniqueid)) cursor.close() pgconn.commit() pgconn.close()
def main(): """Go Main Go""" pgconn = get_dbconn('coop') df = read_sql(""" WITH data as ( SELECT snowd, lead(high) OVER (ORDER by day ASC) as nextday from alldata_ia WHERE station = 'IA0200') select * from data where snowd >= 0.1 and snowd < 18 """, pgconn, index_col=None) df['cat'] = (df['snowd'] / 2).astype(int) print(df) (fig, ax) = plt.subplots(1, 1) df.boxplot(column='nextday', by='cat', ax=ax) ax.set_xticks(range(1, 10)) ax.set_xticklabels(['Trace-2', '2-4', '4-6', '6-8', '8-10', '10-12', '12-14', '14-16', '16-18']) ax.axhline(43, color='r') ax.text(9.5, 43, r"43$^\circ$F", color='r') ax.set_xlabel("Reported Snow Depth [inch], n=%.0f" % (len(df.index), )) ax.set_ylabel(r"Next Day High Temperature [$^\circ$F]") plt.suptitle('') ax.set_title(("[IA0200] Ames Snow Depth and Next Day High Temperature\n" r"boxes are inner quartile range, whiskers are 1.5 $\sigma$")) fig.savefig('test.png')
def main(): """Go""" pgconn = get_dbconn("afos") acursor = pgconn.cursor() payload = getattr(sys.stdin, 'buffer', sys.stdin).read() payload = payload.decode('ascii', errors='ignore') data = payload.replace("\r\r\n", "z") tokens = re.findall(r"(\.A [A-Z0-9]{3} .*?=)", data) utcnow = datetime.datetime.utcnow() gmt = utcnow.replace(tzinfo=pytz.utc) gmt = gmt.replace(second=0) table = "products_%s_0106" % (gmt.year,) if gmt.month > 6: table = "products_%s_0712" % (gmt.year,) for token in tokens: # print(tokens) sql = """ INSERT into """ + table + """ (pil, data, entered) values(%s,%s,%s) """ sqlargs = ("%s%s" % ('RR7', token[3:6]), token.replace("z", "\n"), gmt) acursor.execute(sql, sqlargs) acursor.close() pgconn.commit() pgconn.close()
def main(): """Go Main Go.""" config = get_properties() access_token = '...' api = twitter.Api(consumer_key=config['bot.twitter.consumerkey'], consumer_secret=config['bot.twitter.consumersecret'], access_token_key=access_token, access_token_secret='...') pgconn = get_dbconn('mesosite') cursor = pgconn.cursor() cursor2 = pgconn.cursor() cursor.execute(""" SELECT screen_name from iembot_twitter_oauth where user_id is null """) for row in cursor: try: user_id = api.UsersLookup(screen_name=row[0])[0].id except Exception as _exp: print("FAIL %s" % (row[0], )) continue print("%s -> %s" % (row[0], user_id)) cursor2.execute(""" UPDATE iembot_twitter_oauth SET user_id = %s where screen_name = %s """, (user_id, row[0])) cursor2.close() pgconn.commit()
def main(): """Go Main Go.""" nt = NetworkTable(["WFO", "CWSU"]) df = read_sql(""" SELECT screen_name, access_token, access_token_secret from iembot_twitter_oauth WHERE access_token is not null """, get_dbconn('mesosite'), index_col='screen_name') wfos = list(nt.sts.keys()) wfos.sort() for wfo in wfos: username = "******" % (wfo.lower()[-3:], ) if username not in df.index: print("%s is unknown?" % (username, )) continue api = twitter.Api( consumer_key=PROPS['bot.twitter.consumerkey'], consumer_secret=PROPS['bot.twitter.consumersecret'], access_token_key=df.at[username, 'access_token'], access_token_secret=df.at[username, 'access_token_secret']) location = "%s, %s" % (nt.sts[wfo]['name'], nt.sts[wfo]['state']) desc = ( 'Syndication of National Weather Service Office %s. ' 'Unmonitored, contact @akrherz who developed this.' ) % (location, ) print("len(desc) = %s" % (len(desc), )) profileURL = "https://mesonet.agron.iastate.edu/projects/iembot/" twuser = api.UpdateProfile( description=desc, profileURL=profileURL, location=location) # twuser.AsDict()['followers_count'] print("%s %s" % (username, location))
def main(): """Go Main Go.""" pgconn = get_dbconn('postgis') base = read_sql(""" select segid, archive_begin, archive_end from roads_base """, pgconn, index_col='segid') for year in range(2003, 2018): table = "roads_%s_%s_log" % (year, year + 1) df = read_sql(""" SELECT segid, min(valid), max(valid) from """ + table + """ WHERE valid is not null GROUP by segid """, pgconn, index_col='segid') for segid, row in df.iterrows(): curmin = base.at[segid, 'archive_begin'] curmax = base.at[segid, 'archive_end'] if curmin is None or row['min'] < curmin: base.at[segid, 'archive_begin'] = row['min'] if curmax is None or row['max'] > curmax: base.at[segid, 'archive_end'] = row['max'] cursor = pgconn.cursor() for segid, row in base.iterrows(): if pd.isnull(row['archive_begin']) or pd.isnull(row['archive_end']): continue print("%s %s -> %s" % ( segid, row['archive_begin'], row['archive_end'])) cursor.execute(""" update roads_base SET archive_begin = %s, archive_end = %s where segid = %s """, (row['archive_begin'], row['archive_end'], segid)) cursor.close() pgconn.commit()
def main(): """Go Main Go""" output = open('insert.sql', 'w') pgconn = get_dbconn('postgis') df = read_sql(""" SELECT st_x(st_centroid(geom)) as lon, st_y(st_centroid(geom)) as lat, st_abbrv, name, iemid from climdiv ORDER by iemid """, pgconn, index_col='iemid') for station, row in df.iterrows(): if row['st_abbrv'] in ['AK', 'HI', 'PR']: continue name = "%s - %s Climate Division" % (state_names[row['st_abbrv']], row['name'].title()) # can't have commas name = name.replace(",", " ") sql = """ INSERT into stations(id, name, network, country, state, plot_name, online, metasite, geom) VALUES ('%s', '%s', '%sCLIMATE', 'US', '%s', '%s', 't', 't', 'SRID=4326;POINT(%s %s)'); """ % (station, name, row['st_abbrv'], row['st_abbrv'], name, row['lon'], row['lat']) output.write(sql) output.close()
def main(): """Go Main""" pgconn = get_dbconn('postgis') df = read_postgis(""" select geom, issue from sbw where wfo = 'PUB' and phenomena = 'TO' and significance = 'W' and status = 'NEW' and issue > '2007-10-01' and issue < '2019-01-01' """, pgconn, geom_col='geom', crs={'init': 'epsg:4326', 'no_defs': True}) bounds = df['geom'].total_bounds # bounds = [-102.90293903, 40.08745967, -97.75622311, 43.35172981] bbuf = 0.25 mp = MapPlot( sector='custom', west=bounds[0] - bbuf, south=bounds[1] - bbuf, east=bounds[2] + bbuf, north=bounds[3] + bbuf, continentalcolor='white', # '#b3242c', title='NWS Pueblo Issued Tornado Warnings [2008-2018]', subtitle='%s warnings plotted' % (len(df.index), )) crs_new = ccrs.Mercator() crs = ccrs.PlateCarree() new_geometries = [crs_new.project_geometry(ii, src_crs=crs) for ii in df['geom'].values] # mp.draw_cwas() mp.ax.add_geometries(new_geometries, crs=crs_new, lw=0.5, edgecolor='red', facecolor='None', alpha=1, zorder=5) mp.drawcounties() mp.postprocess(filename='test.png')
def __init__(self, network, cursor=None): """A class representing a network(s) of IEM metadata Args: network (str or list): A network identifier used by the IEM, this can be either a string or a list of strings. cursor (dbcursor,optional): A database cursor to use for the query """ self.sts = OrderedDict() if network is None: return if cursor is None: dbconn = get_dbconn('mesosite', user='******') cursor = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor) if isinstance(network, str): network = [network, ] cursor.execute(""" WITH myattrs as ( SELECT a.iemid, array_agg(attr) as attrs, array_agg(value) as attr_values from stations s JOIN station_attributes a on (s.iemid = a.iemid) WHERE s.network in %s GROUP by a.iemid ) SELECT s.*, ST_x(geom) as lon, ST_y(geom) as lat, a.attrs, a.attr_values from stations s LEFT JOIN myattrs a on (s.iemid = a.iemid) WHERE network in %s ORDER by name ASC """, (tuple(network), tuple(network))) for row in cursor: self.sts[row['id']] = dict(row) self.sts[row['id']]['attributes'] = dict( zip(row['attrs'] or [], row['attr_values'] or []))
def main(): """Map some CLI data""" pgconn = get_dbconn('iem') df = read_sql(""" WITH data as ( SELECT station, snow_jul1 - snow_jul1_normal as s from cli_data where valid = '2019-02-18' and snow_jul1 > 0 and snow_jul1_normal > 0) select station, st_x(geom) as lon, st_y(geom) as lat, c.s as val from data c JOIN stations s on (s.id = c.station) WHERE s.network = 'NWSCLI' """, pgconn, index_col=None) df['color'] = '#ff0000' df.loc[df['val'] > 0, 'color'] = '#0000ff' mp = MapPlot(sector='midwest', axisbg='white', title=("2018-2019 Snowfall Total Departure " "from Average [inches]"), subtitle='18 Feb 2019 Based on NWS CLI Reporting Sites') mp.plot_values( df['lon'].values, df['lat'].values, df['val'].values, fmt='%.1f', textsize=12, color=df['color'].values, labelbuffer=1) mp.postprocess(filename='test.png')
def main(): """Go Main Go""" pgconn = get_dbconn('asos') dfin = read_sql(""" with mob as ( select date_trunc('hour', valid) as ts, avg(dwpf) from alldata where station = 'MOB' and dwpf is not null GROUP by ts), cmi as ( select date_trunc('hour', valid) as ts, avg(dwpf) from alldata where station = 'CMI' and dwpf is not null GROUP by ts), agg as ( select m.ts, m.avg as dwpf, c.avg as tmpf from mob m JOIN cmi c on (m.ts = c.ts)) select extract(month from ts) as month, extract(hour from ts) as hour, sum(case when dwpf >= tmpf then 1 else 0 end) / count(*)::float * 100. as freq from agg GROUP by month, hour ORDER by month, hour """, pgconn, index_col=None) df = dfin.pivot("month", "hour", "freq") fig, ax = plt.subplots(figsize=(9, 6)) ax.set_title(("Hourly Frequency of Mobile (MOB) Dew Point\n" "greater than or equal to Champaign (CMI) Dew Point")) sns.heatmap(df, annot=True, fmt=".0f", linewidths=.5, ax=ax, vmin=5, vmax=100) print(ax.get_yticks()) ax.set_xlabel("Hour of Day (CDT or CST)") ax.set_xticklabels(["Mid", "1AM", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "Noon", "1PM", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11"]) ax.set_yticklabels(calendar.month_abbr[1:]) fig.savefig('test.png')
def run_db(dbname): """ Lets do an actual database """ dbconn = get_dbconn(dbname) cursor = dbconn.cursor() check_management(cursor) cursor.execute(""" SELECT version, updated from iem_schema_manager_version """) row = cursor.fetchone() baseversion = row[0] print(("Database: %-15s has revision: %3s (%s)" ) % (dbname, baseversion, row[1].strftime("%Y-%m-%d %H:%M"))) while True: baseversion += 1 fn = '%s/%s.sql' % (dbname, baseversion) if not os.path.isfile(fn): break print(' -> Attempting schema upgrade #%s ...' % (baseversion,)) cursor.execute(open(fn).read()) cursor.execute(""" UPDATE iem_schema_manager_version SET version = %s, updated = now() """, (baseversion,)) if len(sys.argv) == 1: cursor.close() dbconn.commit() else: print(' + No changes made since argument provided') dbconn.close()
def main(): """Go!""" pgconn = get_dbconn('afos') cursor = pgconn.cursor() payload = getattr(sys.stdin, 'buffer', sys.stdin).read() prod = product.TextProduct(payload.decode('ascii', errors='ignore')) prod.valid = prod.valid.replace(second=0, minute=0, microsecond=0) offset = prod.unixtext.find(prod.afos[:3]) + 7 sections = re.split("\n\n", prod.unixtext[offset:]) table = "products_%s_0106" % (prod.valid.year,) if prod.valid.month > 6: table = "products_%s_0712" % (prod.valid.year,) for sect in sections: if sect[1:4].strip() == "": continue # print("%s%s %s %s %s" % (prod.afos[:3], sect[1:4], prod.source, # prod.valid, prod.wmo)) cursor.execute(""" INSERT into """+table+""" (pil, data, source, entered, wmo) values (%s, %s, %s, %s, %s) """, (prod.afos[:3] + sect[1:4], prod.text[:offset] + sect, prod.source, prod.valid, prod.wmo)) cursor.close() pgconn.commit() pgconn.close()
def gendata(): """Generate the data we need.""" pgconn = get_dbconn('idep') dates = pd.date_range('2019-01-01', '2019-12-31').strftime("%m%d") for scenario in [0, 36, 37, 38]: df = read_sql(""" WITH data as ( SELECT r.huc_12, to_char(valid, 'mmdd') as sday, sum(avg_delivery) / 10. as d from results_by_huc12 r, huc12 h WHERE r.huc_12 = h.huc_12 and h.scenario = 0 and h.states ~* 'IA' and r.scenario = %s and valid between '2008-01-01' and '2018-01-01' GROUP by r.huc_12, sday), huc_count as ( select count(*) from huc12 WHERE scenario = 0 and states ~* 'IA' ), agg as ( SELECT sday, sum(d) * 4.463 as data FROM data d, huc_count h GROUP by sday ORDER by sday ) select sday, data / h.count as avg from agg a, huc_count h WHERE sday != '0229' ORDER by sday """, pgconn, params=(scenario, ), index_col='sday') df = df.reindex(dates).fillna(0) df.index.name = 'sday' df.to_csv('/tmp/s%s.csv' % (scenario, ), index=True)
def main(): """Go Main Go""" pgconn = get_dbconn('postgis') cursor = pgconn.cursor() cursor2 = pgconn.cursor() table = "sbw_%s" % (sys.argv[1],) cursor.execute("""SET TIME ZONE 'UTC'""") cursor.execute(""" SELECT report, expire from """+table+""" where status = 'CAN' and polygon_end != expire and phenomena in ('TO', 'SV') """) for row in cursor: prod = parser(row[0]) if not prod.is_single_action(): continue vtec = prod.segments[0].vtec[0] cursor2.execute("""UPDATE """ + table + """ SET expire = %s where %s <= expire and wfo = %s and phenomena = %s and significance = %s and eventid = %s """, (prod.valid, prod.valid, vtec.office, vtec.phenomena, vtec.significance, vtec.etn)) print("%s -> %s rows:%s" % (row[1], prod.valid, cursor2.rowcount)) print('%s Processed %s rows' % (sys.argv[1], cursor.rowcount)) cursor2.close() pgconn.commit() pgconn.close()
def main(argv): """Go Main Go.""" year = argv[1] pgconn = get_dbconn('asos') cursor = pgconn.cursor() cursor2 = pgconn.cursor() cursor.execute(""" SELECT station, valid, sknt, gust, metar from t"""+year+""" WHERE round(gust::numeric, 2) != gust::int """) hits = 0 for row in cursor: m = WIND_RE.findall(row[4]) if not m: continue sknt, gust = m[0] dirty = False if int(sknt) != row[2]: # print("sknt old: %s new: %s" % (row[2], int(sknt))) dirty = True if int(gust) != row[3]: # print("gust old: %s new: %s" % (row[3], int(gust))) dirty = True if not dirty: continue cursor2.execute(""" UPDATE t"""+year+""" SET sknt = %s, gust = %s WHERE station = %s and valid = %s """, (sknt, gust, row[0], row[1])) hits += 1 print("%s %s/%s rows updated" % (year, hits, cursor.rowcount)) cursor2.close() pgconn.commit()
def excel_summary(hucs, name): """Generate a Excel file with yearly summaries.""" pgconn = get_dbconn('idep') df = read_sql(""" SELECT huc_12, extract(year from valid) as year, sum(avg_loss) * 4.463 as loss_ton_per_acre, sum(avg_delivery) * 4.463 as delivery_ton_per_acre, sum(qc_precip) / 25.4 as precip_inch, sum(avg_runoff) / 25.4 as runoff_inch from results_by_huc12 WHERE scenario = 0 and huc_12 in %s and valid >= '2007-01-01' and valid < '2018-01-01' GROUP by huc_12, year """, pgconn, params=(tuple(hucs), )) writer = pd.ExcelWriter( '%s.xlsx' % (name, ), options={'remove_timezone': True}) df.to_excel(writer, 'Yearly Totals', index=False) gdf = df.groupby('huc_12').mean() gdf[['loss_ton_per_acre', 'delivery_ton_per_acre', 'precip_inch', 'runoff_inch']].to_excel(writer, 'Yearly Averages') format1 = writer.book.add_format({'num_format': '0.00'}) worksheet = writer.sheets['Yearly Totals'] worksheet.set_column('A:A', 18) worksheet.set_column('C:F', 20, format1) worksheet = writer.sheets['Yearly Averages'] worksheet.set_column('A:A', 18) worksheet.set_column('B:E', 20, format1) writer.save()
def iemob(): """Database.""" res = blah() ts = utc(2015, 9, 1, 1, 0) sid = ''.join(random.choice( string.ascii_uppercase + string.digits) for _ in range(7)) res.iemid = 0 - random.randint(0, 1000) res.ob = observation.Observation(sid, 'FAKE', ts) res.conn = get_dbconn('iem') res.cursor = res.conn.cursor( cursor_factory=psycopg2.extras.DictCursor) # Create fake station, so we can create fake entry in summary # and current tables res.cursor.execute(""" INSERT into stations(id, network, iemid, tzname) VALUES (%s, 'FAKE', %s, 'UTC') """, (sid, res.iemid)) res.cursor.execute(""" INSERT into current(iemid, valid) VALUES (%s, '2015-09-01 00:00+00') """, (res.iemid, )) res.cursor.execute(""" INSERT into summary_2015(iemid, day) VALUES (%s, '2015-09-01') """, (res.iemid, )) return res
def main(): """Go""" pgconn = get_dbconn('postgis') df = read_sql(""" with data as ( select distinct date(issue), wfo, eventid from warnings where phenomena = 'TO' and significance = 'W' and issue > '1996-01-01') select wfo, date, count(*) from data GROUP by wfo, date ORDER by wfo, date """, pgconn, index_col=None) rows = [] for wfo, df2 in df.groupby(by='wfo'): maxdate = df2['date'].max() mindate = df2['date'].min() data = [0] * ((maxdate - mindate).days + 4) for _, row in df2.iterrows(): data[(row['date'] - mindate).days] = row['count'] for i in range(0, len(data) - 2): if sum(data[i:i+3]) > 50 or wfo in COMPAREWFOS: date = mindate + datetime.timedelta(days=i) rows.append(dict(wfo=wfo, date=date, count=sum(data[i:i+3]), one=data[i], two=data[i+1], three=data[i+2])) df = pd.DataFrame(rows) df.sort_values('count', ascending=False, inplace=True) for _, row in df.head(15).iterrows(): printr(row) for wfo in COMPAREWFOS: df2 = df[df['wfo'] == wfo] printr(df2.iloc[0])
def loadqc(cursor=None, date=None): """ Load the current IEM Tracker QC'd variables Args: cursor (cursor,optional): Optionally provided database cursor date (date,optional): Defaults to today, which tickets are valid for now """ if date is None: date = datetime.date.today() qdict = {} if cursor is None: portfolio = get_dbconn('portfolio', user='******') cursor = portfolio.cursor() cursor.execute(""" select s_mid, sensor, status from tt_base WHERE sensor is not null and date(entered) <= %s and (status != 'CLOSED' or closed > %s) and s_mid is not null """, (date, date)) for row in cursor: sid = row[0] if row[0] not in qdict: qdict[sid] = {} for vname in row[1].split(","): qdict[sid][vname.strip()] = True return qdict
def main(argv): """Go Main Go""" basedir = "/mesonet/data/prism" outdir = "swatfiles_prism_arealaverage" if os.path.isdir(outdir): print("ABORT: as %s exists" % (outdir, )) return os.mkdir(outdir) for dirname in ['precipitation', 'temperature']: os.mkdir("%s/%s" % (outdir, dirname)) pgconn = get_dbconn('idep') huc8df = gpd.GeoDataFrame.from_postgis(""" SELECT huc8, ST_Transform(simple_geom, %s) as geo from wbd_huc8 WHERE swat_use ORDER by huc8 """, pgconn, params=(PROJSTR,), index_col='huc8', geom_col='geo') hucs = huc8df.index.values years = range(1981, 2018) nc = netCDF4.Dataset("%s/%s_daily.nc" % (basedir, years[0])) # compute the affine ncaffine = Affine(nc.variables['lon'][1] - nc.variables['lon'][0], 0., nc.variables['lon'][0], 0., nc.variables['lat'][0] - nc.variables['lat'][1], nc.variables['lat'][-1]) czs = CachingZonalStats(ncaffine) nc.close() fps = [] for year in years: nc = netCDF4.Dataset("%s/%s_daily.nc" % (basedir, year)) basedate, timesz = get_basedate(nc) for i in tqdm(range(timesz), desc=str(year)): # date = basedate + datetime.timedelta(days=i) # keep array logic in top-down order tasmax = np.flipud(nc.variables['tmax'][i, :, :]) tasmin = np.flipud(nc.variables['tmin'][i, :, :]) pr = np.flipud(nc.variables['ppt'][i, :, :]) mytasmax = czs.gen_stats(tasmax, huc8df['geo']) mytasmin = czs.gen_stats(tasmin, huc8df['geo']) mypr = czs.gen_stats(pr, huc8df['geo']) for j, huc12 in enumerate(hucs): if i == 0 and year == years[0]: fps.append([open(('%s/precipitation/P%s.txt' ) % (outdir, huc12), 'w'), open(('%s/temperature/T%s.txt' ) % (outdir, huc12), 'w')]) fps[j][0].write("%s\n" % (basedate.strftime("%Y%m%d"), )) fps[j][1].write("%s\n" % (basedate.strftime("%Y%m%d"), )) fps[j][0].write(("%.1f\n" ) % (mypr[j], )) fps[j][1].write(("%.2f,%.2f\n" ) % (mytasmax[j], mytasmin[j])) for fp in fps: fp[0].close() fp[1].close()
def main(): """Go Main Go""" pgconn = get_dbconn('scan') for station in ['S2004', 'S2196', 'S2002', 'S2072', 'S2068', 'S2031', 'S2001', 'S2047']: df = read_sql(""" select extract(year from valid + '2 months'::interval) as wy, tmpf, dwpf from alldata where station = %s and tmpf is not null and dwpf is not null """, pgconn, params=(station, ), index_col=None) df['mixingratio'] = meteorology.mixing_ratio( temperature(df['dwpf'].values, 'F')).value('KG/KG') df['vapor_pressure'] = mcalc.vapor_pressure( 1000. * units.mbar, df['mixingratio'].values * units('kg/kg')).to(units('kPa')) df['saturation_mixingratio'] = ( meteorology.mixing_ratio( temperature(df['tmpf'].values, 'F')).value('KG/KG')) df['saturation_vapor_pressure'] = mcalc.vapor_pressure( 1000. * units.mbar, df['saturation_mixingratio'].values * units('kg/kg')).to(units('kPa')) df['vpd'] = df['saturation_vapor_pressure'] - df['vapor_pressure'] means = df.groupby('wy').mean() counts = df.groupby('wy').count() for yr, row in means.iterrows(): print(("%s,%s,%.0f,%.3f" ) % (yr, station, counts.at[yr, 'vpd'], row['vpd']))
def main(): """Go""" pgconn = get_dbconn('iem') cursor = pgconn.cursor() cursor.execute(""" with data as ( select distinct t.iemid, date_trunc('hour', valid + '10 minutes'::interval) as v from current_log c JOIN stations t on (c.iemid = t.iemid) where raw ~* ' FU ' and t.country = 'US') SELECT v, count(*) from data GROUP by v ORDER by v ASC """) xs = [] ys = [] for row in cursor: xs.append(row[0]) ys.append(row[1]) (fig, ax) = plt.subplots(1, 1) ax.bar(xs, ys, width=(1./24.)) ax.grid(True) ax.set_ylabel("Number of ASOS/METAR Sites") ax.set_xlabel("3-5 July 2017 Valid Time (Central Daylight Time)") ax.set_title(("Number of ASOS/METAR Sites Reporting Smoke (FU)\n" "based on METAR reports for the United States processed by IEM")) ax.xaxis.set_major_formatter(mdates.DateFormatter('%-I %p\n%-d %b', tz=pytz.timezone("America/Chicago"))) ax.set_position([0.1, 0.15, 0.8, 0.75]) fig.savefig('test.png')
def plotter(fdict): """ Go """ pgconn = get_dbconn('asos', user='******') ctx = get_autoplot_context(fdict, get_description()) station = ctx['zstation'] month = int(ctx['month']) thres = ctx['t'] mydir = ctx['dir'] tzname = ctx['_nt'].sts[station]['tzname'] df = read_sql(""" WITH data as ( SELECT valid at time zone %s + '10 minutes'::interval as v, tmpf from alldata where station = %s and tmpf > -90 and tmpf < 150 and extract(month from valid) = %s and report_type = 2) SELECT extract(hour from v) as hour, min(v) as min_valid, max(v) as max_valid, sum(case when tmpf::int < %s THEN 1 ELSE 0 END) as below, sum(case when tmpf::int >= %s THEN 1 ELSE 0 END) as above, count(*) from data GROUP by hour ORDER by hour ASC """, pgconn, params=(tzname, station, month, thres, thres), index_col='hour') if df.empty: raise NoDataFound("No data found.") df['below_freq'] = df['below'].values.astype('f') / df['count'] * 100. df['above_freq'] = df['above'].values.astype('f') / df['count'] * 100. freq = df[mydir + "_freq"].values hours = df.index.values (fig, ax) = plt.subplots(1, 1, figsize=(8, 6)) bars = ax.bar(hours, freq, fc='blue', align='center') for i, mybar in enumerate(bars): ax.text(i, mybar.get_height() + 3, "%.0f" % (mybar.get_height(), ), ha='center', fontsize=10) ax.set_xticks(range(0, 25, 3)) ax.set_xticklabels( ['Mid', '3 AM', '6 AM', '9 AM', 'Noon', '3 PM', '6 PM', '9 PM']) ax.grid(True) ax.set_ylim(0, 100) ax.set_yticks([0, 25, 50, 75, 100]) ax.set_ylabel("Frequency [%]") ax.set_xlabel("Hour Timezone: %s" % (tzname, )) ax.set_xlim(-0.5, 23.5) ax.set_title(("(%s - %s) %s [%s]\n" r"Frequency of %s Hour, %s: %s$^\circ$F") % (df['min_valid'].min().year, df['max_valid'].max().year, ctx['_nt'].sts[station]['name'], station, calendar.month_name[month], PDICT[mydir], thres)) return fig, df
def plotter(fdict): """ Go """ import matplotlib matplotlib.use('agg') import matplotlib.pyplot as plt pgconn = get_dbconn('asos') cursor = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor) ctx = get_autoplot_context(fdict, get_description()) station = ctx['zstation'] network = ctx['network'] hours = ctx['hours'] interval = ctx['interval'] if interval > 10 or interval < 0.1: return "Invalid interval provided, positive number less than 10" nt = NetworkTable(network) cursor.execute( """ WITH one as ( select valid, tmpf::int as t from alldata where station = %s and tmpf is not null and tmpf > -50 ), two as (SELECT valid + '%s hours'::interval as v, t from one ) SELECT extract(week from one.valid), two.t - one.t from one JOIN two on (one.valid = two.v) """, (station, hours)) weeks = [] deltas = [] for row in cursor: weeks.append(row[0]) deltas.append(float(row[1])) sts = datetime.datetime(2012, 1, 1) xticks = [] for i in range(1, 13): ts = sts.replace(month=i) xticks.append(int(ts.strftime("%j"))) # We want bins centered on zero bins = compute_bins(interval) hist, xedges, yedges = np.histogram2d(weeks, deltas, [range(0, 54), bins]) years = float(datetime.datetime.now().year - nt.sts[station]['archive_begin'].year) hist = np.ma.array(hist / years / 7.0) hist.mask = np.where(hist < (1. / years), True, False) (fig, ax) = plt.subplots(1, 1) res = ax.pcolormesh((xedges - 1) * 7, yedges, hist.transpose(), cmap=plt.get_cmap('spectral')) fig.colorbar(res, label="Hours per Day") ax.grid(True) ax.set_title(("%s [%s]\nHistogram (bin=%s$^\circ$F) " "of %s Hour Temperature Change") % (nt.sts[station]['name'], station, interval, hours)) ax.set_ylabel("Temperature Change $^{\circ}\mathrm{F}$") ax.set_xticks(xticks) ax.set_xticklabels(calendar.month_abbr[1:]) ax.set_xlim(0, 366) rng = max([max(deltas), 0 - min(deltas)]) ax.set_ylim(0 - rng - 4, rng + 4) return fig
def plotter(fdict): """ Go """ ctx = get_autoplot_context(fdict, get_description()) station = ctx["station"] days = ctx["days"] days2 = ctx["days2"] _days2 = days2 if days2 > 0 else 1 days3 = ctx["days3"] _days3 = days3 if days3 > 0 else 1 sts = ctx["sdate"] ets = ctx["edate"] yrrange = ets.year - sts.year year2 = ctx.get("year2") # could be null! year3 = ctx.get("year3") # could be null! pgconn = get_dbconn("coop") table = "alldata_%s" % (station[:2], ) df = read_sql( """ WITH agg as ( SELECT o.day, o.sday, avg(high) OVER (ORDER by day ASC ROWS %s PRECEDING) as avgt, sum(precip) OVER (ORDER by day ASC ROWS %s PRECEDING) as sump, count(*) OVER (ORDER by day ASC ROWS %s PRECEDING) as cnt, avg(high) OVER (ORDER by day ASC ROWS %s PRECEDING) as avgt2, sum(precip) OVER (ORDER by day ASC ROWS %s PRECEDING) as sump2, count(*) OVER (ORDER by day ASC ROWS %s PRECEDING) as cnt2, avg(high) OVER (ORDER by day ASC ROWS %s PRECEDING) as avgt3, sum(precip) OVER (ORDER by day ASC ROWS %s PRECEDING) as sump3, count(*) OVER (ORDER by day ASC ROWS %s PRECEDING) as cnt3 from """ + table + """ o WHERE station = %s), agg2 as ( SELECT sday, avg(avgt) as avg_avgt, stddev(avgt) as std_avgt, avg(sump) as avg_sump, stddev(sump) as std_sump, avg(avgt2) as avg_avgt2, stddev(avgt2) as std_avgt2, avg(sump2) as avg_sump2, stddev(sump2) as std_sump2, avg(avgt3) as avg_avgt3, stddev(avgt3) as std_avgt3, avg(sump3) as avg_sump3, stddev(sump3) as std_sump3 from agg WHERE cnt = %s GROUP by sday) SELECT day, (a.avgt - b.avg_avgt) / b.std_avgt as t, (a.sump - b.avg_sump) / b.std_sump as p, (a.avgt2 - b.avg_avgt2) / b.std_avgt2 as t2, (a.sump2 - b.avg_sump2) / b.std_sump2 as p2, (a.avgt3 - b.avg_avgt3) / b.std_avgt3 as t3, (a.sump3 - b.avg_sump3) / b.std_sump3 as p3 from agg a JOIN agg2 b on (a.sday = b.sday) ORDER by day ASC """, pgconn, params=( days - 1, days - 1, days - 1, _days2 - 1, _days2 - 1, _days2 - 1, _days3 - 1, _days3 - 1, _days3 - 1, station, days, ), index_col="day", ) if df.empty: raise NoDataFound("No Data Found.") df["arridity"] = df["t"] - df["p"] df["arridity2"] = df["t2"] - df["p2"] df["arridity3"] = df["t3"] - df["p3"] (fig, ax) = plt.subplots(1, 1) if year2 is None: df2 = df.loc[sts:ets] ax.plot( df2.index.values, df2["arridity"], color="r", lw=2, label="%s days" % (days, ), ) maxval = df2["arridity"].abs().max() + 0.25 if days2 > 0: ax.plot( df2.index.values, df2["arridity2"], color="b", lw=2, label="%s days" % (days2, ), ) maxval = max([maxval, df2["arridity2"].abs().max() + 0.25]) if days3 > 0: ax.plot( df2.index.values, df2["arridity3"], color="g", lw=2, label="%s days" % (days3, ), ) maxval = max([maxval, df2["arridity3"].abs().max() + 0.25]) ax.xaxis.set_major_formatter(mdates.DateFormatter("%-d %b\n%Y")) title = "" else: df2 = df.loc[sts:ets] ax.plot( np.arange(len(df2.index)), df2["arridity"], color="r", lw=2, label="%s" % (ets.year, ), ) maxval = df2["arridity"].abs().max() + 0.25 if year2 is not None: sts2 = sts.replace(year=(year2 - yrrange)) ets2 = ets.replace(year=year2) xticks = [] xticklabels = [] now = sts2 i = 0 while now < ets2: if now.day == 1: xticks.append(i) xticklabels.append(now.strftime("%b")) i += 1 now += datetime.timedelta(days=1) ax.set_xticks(xticks) ax.set_xticklabels(xticklabels) df2 = df.loc[sts2:ets2] ax.plot( np.arange(len(df2.index)), df2["arridity"], color="b", lw=2, label="%s" % (year2, ), ) maxval = max([maxval, df2["arridity"].abs().max() + 0.25]) if year3 is not None: sts2 = sts.replace(year=(year3 - yrrange)) ets2 = ets.replace(year=year3) df2 = df.loc[sts2:ets2] ax.plot( np.arange(len(df2.index)), df2["arridity"], color="g", lw=2, label="%s" % (year3, ), ) maxval = max([maxval, df2["arridity"].abs().max() + 0.25]) # Compute year of best fit arridity = df.loc[sts:ets, "arridity"].values mae = 100 useyear = None for _year in range(1951, datetime.date.today().year + 1): if _year == ets.year: continue sts2 = sts.replace(year=(_year - yrrange)) ets2 = ets.replace(year=_year) arridity2 = df.loc[sts2:ets2, "arridity"].values sz = min([len(arridity2), len(arridity)]) error = (np.mean((arridity2[:sz] - arridity[:sz])**2))**0.5 if error < mae: mae = error useyear = _year if useyear: sts2 = sts.replace(year=(useyear - yrrange)) ets2 = ets.replace(year=useyear) df2 = df.loc[sts2:ets2] ax.plot( np.arange(len(df2.index)), df2["arridity"], color="k", lw=2, label="%s (%s best match)" % (useyear, ets.year), ) maxval = max([maxval, df2["arridity"].abs().max() + 0.25]) title = "%s Day" % (days, ) ax.set_xlabel("%s to %s" % (sts.strftime("%-d %b"), ets.strftime("%-d %b"))) ax.grid(True) ax.set_title(("%s [%s] %s Arridity Index\n" "Std. High Temp Departure minus Std. Precip Departure") % (ctx["_nt"].sts[station]["name"], station, title)) ax.set_ylim(0 - maxval, maxval) ax.set_ylabel("Arridity Index") ax.text( 1.01, 0.75, "<-- More Water Stress", ha="left", va="center", transform=ax.transAxes, rotation=-90, ) ax.text( 1.01, 0.25, "Less Water Stress -->", ha="left", va="center", transform=ax.transAxes, rotation=-90, ) ax.legend(ncol=4, loc="best", fontsize=10) return fig, df
def plotter(fdict): """ Go """ from seaborn import heatmap ctx = get_autoplot_context(fdict, get_description()) pgconn = get_dbconn("coop") table = "alldata_%s" % (ctx["station"][:2], ) df = read_sql( """ select day, sday, precip, high, extract(doy from day)::int as doy, year from """ + table + """ WHERE station = %s ORDER by day ASC """, pgconn, params=(ctx["station"], ), index_col="day", parse_dates="day", ) if df.empty: raise NoDataFound("Did not find any data for station!") if ctx["var"] == "trail_precip_percent": climo = df[["precip", "sday"]].groupby("sday").mean() df["precip_avg"] = df.merge(climo, left_on="sday", right_index=True, suffixes=("", "_avg"))["precip_avg"] df["trail_precip_percent"] = ( df["precip"].rolling(ctx["days"]).sum() / df["precip_avg"].rolling(ctx["days"]).sum() * 100.0) levels = [0, 25, 50, 75, 100, 150, 200, 250, 300] label = "Percent" elif ctx["var"] == "daily_high_depart": climo = df[["high", "sday"]].groupby("sday").mean() df["high_avg"] = df.merge(climo, left_on="sday", right_index=True, suffixes=("", "_avg"))["high_avg"] df["daily_high_depart"] = df["high"] - df["high_avg"] levels = list(range(-20, 21, 4)) label = "Temperature [F] Departure" baseyear = max([df["year"].min(), ctx["syear"]]) endyear = min([df["year"].max(), ctx["eyear"]]) years = endyear - baseyear + 1 cmap = get_cmap(ctx["cmap"]) norm = mpcolors.BoundaryNorm(levels, cmap.N) data = np.full((years, 366), np.nan) df2 = df[(df["year"] >= baseyear) & (df["year"] <= endyear)] for day, row in df2.iterrows(): data[day.year - baseyear, row["doy"] - 1] = row[ctx["var"]] fig, ax = plt.subplots(1, 1) heatmap( data, cmap=cmap, norm=norm, ax=ax, cbar_kws={ "spacing": "proportional", "label": label }, ) ax.set_xticks((1, 32, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335)) ax.set_xticklabels(calendar.month_abbr[1:], rotation=0) yticks = [] yticklabels = [] delta = 5 if (endyear - baseyear) < 30 else 10 for i, year in enumerate(range(baseyear, endyear + 1)): if year % delta == 0: yticks.append(i + 0.5) yticklabels.append(year) ax.set_yticks(yticks[::-1]) ax.set_yticklabels(yticklabels[::-1], rotation=0) ax.xaxis.grid(True, color="k") ax.set_title("[%s] %s (%s-%s)\n%s" % ( ctx["station"], ctx["_nt"].sts[ctx["station"]]["name"], ctx["syear"], ctx["eyear"], PDICT[ctx["var"]].replace("XX", str(ctx["days"])), )) return fig, df
def fix_nulls(): """Correct any nulls.""" pgconn = get_dbconn("isuag") cursor = pgconn.cursor() cursor2 = pgconn.cursor() nt = NetworkTable("ISUSM") cursor.execute(""" SELECT station, valid from sm_daily where (slrmj_tot_qc is null or slrmj_tot_qc = 0) and valid > '2019-04-14' ORDER by valid ASC """) for row in cursor: station = row[0] v1 = datetime.datetime(row[1].year, row[1].month, row[1].day) v2 = v1.replace(hour=23, minute=59) cursor2.execute( """ SELECT sum(slrmj_tot_qc), count(*) from sm_hourly WHERE station = %s and valid >= %s and valid < %s """, (station, v1, v2), ) row2 = cursor2.fetchone() if row2[0] is None or row2[0] < 0.01: LOG.info( "%s %s summed %s hourly for solar, using IEMRE", station, v1.strftime("%d %b %Y"), row2[0], ) if station not in nt.sts: LOG.info("unknown station %s metadata, skipping", station) continue # Go fetch me the IEMRE value! uri = ("http://iem.local/iemre/daily/%s/%.2f/%.2f/json") % ( v1.strftime("%Y-%m-%d"), nt.sts[station]["lat"], nt.sts[station]["lon"], ) res = requests.get(uri) if res.status_code != 200: LOG.info("Fix solar got %s from %s", res.status_code, uri) continue j = json.loads(res.content) if not j["data"]: LOG.info( "fix solar: No data for %s %s", station, v1.strftime("%d %b %Y"), ) continue row2 = [j["data"][0]["srad_mj"], -1] if row2[0] is None or row2[0] < 0.01: LOG.info("Triple! Failure %s %s", station, v1.strftime("%d %b %Y")) continue LOG.info( "%s %s -> %.2f (%s obs)", station, v1.strftime("%d %b %Y"), row2[0], row2[1], ) cursor2.execute( """ UPDATE sm_daily SET slrmj_tot_qc = %s WHERE station = %s and valid = %s """, (row2[0], station, row[1]), ) cursor2.close() pgconn.commit()
def do_ugc(ctx): pgconn = get_dbconn('postgis') cursor = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor) varname = ctx['v'] station = ctx['station'][:4] state = ctx['state'] phenomena = ctx['phenomena'] significance = ctx['significance'] t = ctx['t'] sdate = ctx['sdate'] edate = ctx['edate'] year = ctx['year'] year2 = ctx['year2'] if varname == 'lastyear': if t == 'cwa': cursor.execute(""" select ugc, max(issue at time zone 'UTC') from warnings WHERE wfo = %s and phenomena = %s and significance = %s GROUP by ugc """, (station if len(station) == 3 else station[1:], phenomena, significance)) else: cursor.execute(""" select ugc, max(issue at time zone 'UTC') from warnings WHERE substr(ugc, 1, 2) = %s and phenomena = %s and significance = %s GROUP by ugc """, (state, phenomena, significance)) rows = [] data = {} for row in cursor: rows.append(dict(valid=row[1], year=row[1].year, ugc=row[0])) data[row[0]] = row[1].year ctx['title'] = "Year of Last" datavar = "year" elif varname == 'yearcount': table = "warnings_%s" % (year, ) if t == 'cwa': cursor.execute(""" select ugc, count(*) from """ + table + """ WHERE wfo = %s and phenomena = %s and significance = %s GROUP by ugc """, (station if len(station) == 3 else station[1:], phenomena, significance)) else: cursor.execute(""" select ugc, count(*) from """ + table + """ WHERE substr(ugc, 1, 2) = %s and phenomena = %s and significance = %s GROUP by ugc """, (state, phenomena, significance)) rows = [] data = {} for row in cursor: rows.append(dict(count=row[1], year=year, ugc=row[0])) data[row[0]] = row[1] ctx['title'] = "Count for %s" % (year,) datavar = "count" elif varname == 'total': table = "warnings" if t == 'cwa': cursor.execute(""" select ugc, count(*), min(issue at time zone 'UTC'), max(issue at time zone 'UTC') from """ + table + """ WHERE wfo = %s and phenomena = %s and significance = %s and issue >= %s and issue <= %s GROUP by ugc """, (station if len(station) == 3 else station[1:], phenomena, significance, sdate, edate)) else: cursor.execute(""" select ugc, count(*), min(issue at time zone 'UTC'), max(issue at time zone 'UTC') from """ + table + """ WHERE substr(ugc, 1, 2) = %s and phenomena = %s and significance = %s and issue >= %s and issue < %s GROUP by ugc """, (state, phenomena, significance, sdate, edate)) rows = [] data = {} for row in cursor: rows.append(dict(count=row[1], year=year, ugc=row[0], minissue=row[2], maxissue=row[3])) data[row[0]] = row[1] ctx['title'] = "Total" ctx['subtitle'] = (" between %s and %s UTC" ) % (sdate.strftime("%d %b %Y %H%M"), edate.strftime("%d %b %Y %H%M")) datavar = "count" elif varname == 'yearavg': table = "warnings" if t == 'cwa': cursor.execute(""" select ugc, count(*), min(issue at time zone 'UTC'), max(issue at time zone 'UTC') from """ + table + """ WHERE wfo = %s and phenomena = %s and significance = %s and issue >= %s and issue <= %s GROUP by ugc """, (station if len(station) == 3 else station[1:], phenomena, significance, datetime.date(year, 1, 1), datetime.date(year2 + 1, 1, 1))) else: cursor.execute(""" select ugc, count(*), min(issue at time zone 'UTC'), max(issue at time zone 'UTC') from """ + table + """ WHERE substr(ugc, 1, 2) = %s and phenomena = %s and significance = %s and issue >= %s and issue < %s GROUP by ugc """, (state, phenomena, significance, datetime.date(year, 1, 1), datetime.date(year2 + 1, 1, 1))) rows = [] data = {} minv = datetime.datetime(2050, 1, 1) maxv = datetime.datetime(1986, 1, 1) for row in cursor: if row[2] < minv: minv = row[2] if row[3] > maxv: maxv = row[3] rows.append(dict(count=row[1], year=year, ugc=row[0], minissue=row[2], maxissue=row[3])) data[row[0]] = row[1] ctx['title'] = ("Yearly Avg: %s and %s" ) % (minv.strftime("%d %b %Y"), maxv.strftime("%d %b %Y")) datavar = "average" if not rows: raise ValueError("Sorry, no data found for query!") df = pd.DataFrame(rows) if varname == 'yearavg': years = maxv.year - minv.year + 1 df['average'] = df['count'] / years for key in data: data[key] = round(data[key] / float(years), 2) maxv = df[datavar].max() for delta in [500, 50, 5, 1, 0.5, 0.05]: bins = np.arange(0, (maxv + 1.) * 1.05, delta) if len(bins) > 8: break if len(bins) > 8: bins = bins[::int(len(bins) / 8.)] bins[0] = 0.01 else: bins = range(np.min(df[datavar][:]), np.max(df[datavar][:])+2, 1) if len(bins) < 3: bins.append(bins[-1]+1) if len(bins) > 8: bins = np.linspace(np.min(df[datavar][:]), np.max(df[datavar][:])+2, 8, dtype='i') ctx['bins'] = bins ctx['data'] = data ctx['df'] = df
"""Run to ingest gempak files from mtarchive""" import subprocess import datetime import requests import pytz from ingest_from_rucsoundings import RAOB from pyiem.util import get_dbconn POSTGIS = get_dbconn('postgis') def conv(raw): if float(raw) < -9998: return None return float(raw) def conv_speed(raw): ''' convert sped to mps units ''' if raw in ['99999', '-9999.00']: return None return float(raw) * 0.5144 sts = datetime.datetime(1989, 9, 25) ets = datetime.datetime(1989, 9, 26) interval = datetime.timedelta(days=1) now = sts while now < ets: print now
def run(station, syear, eyear): """Do something""" pgconn = get_dbconn("coop") cursor = pgconn.cursor() table = "alldata_%s" % (station[:2],) cursor.execute( f""" WITH data as ( SELECT sday, year, precip, avg(precip) OVER (PARTITION by sday) as avg_precip, high, rank() OVER (PARTITION by sday ORDER by high DESC) as max_high, avg(high) OVER (PARTITION by sday) as avg_high, rank() OVER (PARTITION by sday ORDER by high ASC) as min_high, low, rank() OVER (PARTITION by sday ORDER by low DESC) as max_low, avg(low) OVER (PARTITION by sday) as avg_low, rank() OVER (PARTITION by sday ORDER by low ASC) as min_low, rank() OVER (PARTITION by sday ORDER by precip DESC) as max_precip from {table} WHERE station = %s and year >= %s and year < %s), max_highs as ( SELECT sday, high, array_agg(year) as years from data where max_high = 1 GROUP by sday, high), min_highs as ( SELECT sday, high, array_agg(year) as years from data where min_high = 1 GROUP by sday, high), max_lows as ( SELECT sday, low, array_agg(year) as years from data where max_low = 1 GROUP by sday, low), min_lows as ( SELECT sday, low, array_agg(year) as years from data where min_low = 1 GROUP by sday, low), max_precip as ( SELECT sday, precip, array_agg(year) as years from data where max_precip = 1 GROUP by sday, precip), avgs as ( SELECT sday, count(*) as cnt, max(avg_precip) as p, max(avg_high) as h, max(avg_low) as l from data GROUP by sday) SELECT a.sday, a.cnt, a.h, xh.high, xh.years, nh.high, nh.years, a.l, xl.low, xl.years, nl.low, nl.years, a.p, mp.precip, mp.years from avgs a, max_highs xh, min_highs nh, max_lows xl, min_lows nl, max_precip mp WHERE xh.sday = a.sday and xh.sday = nh.sday and xh.sday = xl.sday and xh.sday = nl.sday and xh.sday = mp.sday ORDER by sday ASC """, (station, syear, eyear), ) res = { "station": station, "start_year": syear, "end_year": eyear, "climatology": [], } for row in cursor: res["climatology"].append( dict( month=int(row[0][:2]), day=int(row[0][2:]), years=row[1], avg_high=float(row[2]), max_high=row[3], max_high_years=row[4], min_high=row[5], min_high_years=row[6], avg_low=float(row[7]), max_low=row[8], max_low_years=row[9], min_low=row[10], min_low_years=row[11], avg_precip=float(row[12]), max_precip=row[13], max_precip_years=row[14], ) ) return json.dumps(res)
def plotter(fdict): """ Go """ import matplotlib matplotlib.use('agg') pgconn = get_dbconn('coop') ctx = get_autoplot_context(fdict, get_description()) station = ctx['station'] table = "alldata_%s" % (station[:2], ) nt = NetworkTable("%sCLIMATE" % (station[:2], )) res = ("# IEM Climodat https://mesonet.agron.iastate.edu/climodat/\n" "# Report Generated: %s\n" "# Climate Record: %s -> %s\n" "# Site Information: [%s] %s\n" "# Contact Information: Daryl Herzmann " "[email protected] 515.294.5978\n") % ( datetime.date.today().strftime("%d %b %Y"), nt.sts[station]['archive_begin'].date(), datetime.date.today(), station, nt.sts[station]['name']) res += ("# THESE ARE THE HEAT STRESS VARIABLES FOR STATION # %s\n") % ( station, ) s = nt.sts[station]['archive_begin'] e = datetime.date.today().year + 1 df = read_sql(""" SELECT year, month, sum(case when high > 86 then 1 else 0 end) as days, sum(case when high > 86 then high - 86 else 0 end) as sdd from """ + table + """ WHERE station = %s GROUP by year, month """, pgconn, params=(station, ), index_col=None) sdd = df.pivot('year', 'month', 'sdd') days = df.pivot('year', 'month', 'days') df = sdd.join(days, lsuffix='sdd', rsuffix='days') res += (" # OF DAYS MAXT >86 " "ACCUMULATED (MAXT - 86 )\n" " YEAR MAY JUNE JULY AUG SEPT TOTAL " "MAY JUNE JULY AUG SEPT TOTAL\n") yrCnt = 0 for yr in range(s.year, e): yrCnt += 1 res += ("%5s" % (yr, )) total = 0 for mo in range(5, 10): val = df.at[yr, "%sdays" % (mo, )] if np.isnan(val): res += ("%6s" % ("M", )) else: res += ("%6i" % (val, )) total += val res += ("%6i " % (total, )) total = 0 for mo in range(5, 10): val = df.at[yr, "%ssdd" % (mo, )] if np.isnan(val): res += ("%6s" % ("M", )) else: res += ("%6i" % (val, )) total += val res += ("%6i \n" % (total, )) res += (" **************************************************************" "************************\n") res += ("MEANS") tot = 0 for mo in range(5, 10): val = df["%sdays" % (mo, )].mean() tot += val res += ("%6.1f" % (val, )) res += ("%6.1f " % (tot, )) tot = 0 for mo in range(5, 10): val = df["%ssdd" % (mo, )].mean() tot += val res += ("%6.1f" % (val, )) res += ("%6.1f\n" % (tot, )) return None, df, res
def get_context(fdict): """ Get the context""" pgconn = get_dbconn('coop') ctx = get_autoplot_context(fdict, get_description()) station = ctx['station'] month = ctx['month'] ptype = ctx['type'] threshold = ctx['threshold'] table = "alldata_%s" % (station[:2], ) nt = network.Table("%sCLIMATE" % (station[:2], )) lag = "0 days" if month == 'fall': months = [9, 10, 11] label = "Fall (SON)" elif month == 'winter': months = [12, 1, 2] lag = "31 days" label = "Winter (DJF)" elif month == 'spring': months = [3, 4, 5] label = "Spring (MAM)" elif month == 'summer': months = [6, 7, 8] label = "Summer (JJA)" else: months = [ int(month), ] label = calendar.month_name[int(month)] df = read_sql(""" WITH climo as ( SELECT to_char(valid, 'mmdd') as sday, high, low from ncdc_climate81 WHERE station = %s), day2day as ( SELECT extract(year from day + '""" + lag + """'::interval)::int as myyear, month, abs(high - lag(high) OVER (ORDER by day ASC)) as dhigh, abs(low - lag(low) OVER (ORDER by day ASC)) as dlow, abs((high+low)/2. - lag((high+low)/2.) OVER (ORDER by day ASC)) as dtemp from """ + table + """ WHERE station = %s), agg as ( SELECT myyear, avg(dhigh) as dhigh, avg(dlow) as dlow, avg(dtemp) as dtemp from day2day WHERE month in %s GROUP by myyear), agg2 as ( SELECT extract(year from day + '""" + lag + """'::interval)::int as myyear, max(o.high) as "max-high", min(o.high) as "min-high", avg(o.high) as "avg-high", stddev(o.high) as "std-high", max(o.low) as "max-low", min(o.low) as "min-low", avg(o.low) as "avg-low", stddev(o.low) as "std-low", avg((o.high + o.low)/2.) as "avg-temp", stddev((o.high + o.low)/2.) as "std-temp", max(o.precip) as "max-precip", sum(o.precip) as "sum-precip", avg(o.high) - avg(o.low) as "range-avghi-avglo", sum(case when o.high >= %s then 1 else 0 end) as "days-high-above", sum(case when o.high < %s then 1 else 0 end) as "days-high-below", sum(case when o.high >= c.high then 1 else 0 end) as "days-high-above-avg", sum(case when o.low >= %s then 1 else 0 end) as "days-lows-above", sum(case when o.low < c.low then 1 else 0 end) as "days-lows-below-avg", sum(case when o.low < %s then 1 else 0 end) as "days-lows-below" from """ + table + """ o JOIN climo c on (o.sday = c.sday) where station = %s and month in %s GROUP by myyear) SELECT b.*, a.dhigh as "delta-high", a.dlow as "delta-low", a.dtemp as "delta_temp" from agg a JOIN agg2 b on (a.myyear = b.myyear) ORDER by b.myyear ASC """, pgconn, params=(nt.sts[station]['ncdc81'], station, tuple(months), threshold, threshold, threshold, threshold, station, tuple(months)), index_col='myyear') if df.empty: raise ValueError("No data was found for query") # Figure out the max min values to add to the row df2 = df[df[ptype] == df[ptype].max()] if df2.empty: raise ValueError("No data was found for query") df = df.dropna() xlabel = "Year, Max: %.2f %s%s" % (df[ptype].max(), df2.index.values[0], '+' if len(df2.index) > 1 else '') df2 = df[df[ptype] == df[ptype].min()] xlabel += ", Min: %.2f %s%s" % (df[ptype].min(), df2.index.values[0], '+' if len(df2.index) > 1 else '') ctx['xlabel'] = xlabel data = df[ptype].values ctx['data'] = data ctx['avgv'] = df[ptype].mean() ctx['df'] = df # Compute 30 year trailing average tavg = [None] * 30 for i in range(30, len(data)): tavg.append(np.average(data[i - 30:i])) ctx['tavg'] = tavg # End interval is inclusive ctx['a1981_2010'] = df.loc[1981:2010, ptype].mean() ctx['ptype'] = ptype ctx['station'] = station ctx['threshold'] = threshold ctx['month'] = month ctx['title'] = ("[%s] %s %s-%s") % (station, nt.sts[station]['name'], df.index.min(), df.index.max()) ctx['subtitle'] = ("%s %s") % (label, PDICT[ptype]) if ptype.find("days") == 0 and ptype.find('avg') == -1: ctx['subtitle'] += " (%s)" % (threshold, ) units = r"$^\circ$F" if ctx['ptype'].find('precip') > 0: units = "inches" elif ctx['ptype'].find('days') > 0: units = "days" ctx['ylabel'] = "%s [%s]" % (PDICT[ctx['ptype']], units) return ctx
def run(ctx, start_response): """Go run!""" pgconn = get_dbconn("postgis", user="******") cursor = pgconn.cursor() if (ctx["ets"] - ctx["sts"]).days > 120: ctx["ets"] = ctx["sts"] + datetime.timedelta(days=120) sql = """ SELECT to_char(valid at time zone 'UTC', 'YYYYMMDDHH24MI') as utctime, case when is_urgent then 'T' else 'F' end, substr(aircraft_type, 0, 40), substr(report, 0, 255), ST_y(geom::geometry) as lat, ST_x(geom::geometry) as lon from pireps WHERE valid >= '%s' and valid < '%s' ORDER by valid ASC """ % ( ctx["sts"].strftime("%Y-%m-%d %H:%M+00"), ctx["ets"].strftime("%Y-%m-%d %H:%M+00"), ) cursor.execute(sql) if cursor.rowcount == 0: start_response("200 OK", [("Content-type", "text/plain")]) return b"ERROR: no results found for your query" fn = "pireps_%s_%s" % ( ctx["sts"].strftime("%Y%m%d%H%M"), ctx["ets"].strftime("%Y%m%d%H%M"), ) # sys.stderr.write("End SQL with rowcount %s" % (cursor.rowcount, )) if ctx["fmt"] == "csv": sio = StringIO() headers = [ ("Content-type", "application/octet-stream"), ("Content-Disposition", "attachment; filename=%s.csv" % (fn, )), ] start_response("200 OK", headers) sio.write("VALID,URGENT,AIRCRAFT,REPORT,LAT,LON\n") for row in cursor: sio.write(",".join([str(s) for s in row]) + "\n") return sio.getvalue().encode("ascii", "ignore") shpio = BytesIO() shxio = BytesIO() dbfio = BytesIO() with shapefile.Writer(shx=shxio, dbf=dbfio, shp=shpio) as shp: shp.field("VALID", "C", 12) shp.field("URGENT", "C", 1) shp.field("AIRCRAFT", "C", 40) shp.field("REPORT", "C", 255) # Max field size is 255 shp.field("LAT", "F", 7, 4) shp.field("LON", "F", 9, 4) for row in cursor: shp.point(row[-1], row[-2]) shp.record(*row) zio = BytesIO() with zipfile.ZipFile(zio, mode="w", compression=zipfile.ZIP_DEFLATED) as zf: zf.writestr(fn + ".prj", open(("/opt/iem/data/gis/meta/4326.prj")).read()) zf.writestr(fn + ".shp", shpio.getvalue()) zf.writestr(fn + ".shx", shxio.getvalue()) zf.writestr(fn + ".dbf", dbfio.getvalue()) headers = [ ("Content-type", "application/octet-stream"), ("Content-Disposition", "attachment; filename=%s.zip" % (fn, )), ] start_response("200 OK", headers) return zio.getvalue()
def plotter(fdict): """ Go """ pgconn = get_dbconn("iem") ctx = get_autoplot_context(fdict, get_description()) station = ctx["station"] days = ctx["days"] sts = datetime.date(2012, ctx["month"], ctx["day"]) ets = sts + datetime.timedelta(days=(days - 1)) varname = ctx["varname"] year = ctx["year"] sdays = [] for i in range(days): ts = sts + datetime.timedelta(days=i) sdays.append(ts.strftime("%m%d")) doff = (days + 1) if ets.year != sts.year else 0 df = read_sql( """ SELECT extract(year from day - '""" + str(doff) + """ days'::interval) as yr, avg((max_tmpf+min_tmpf)/2.) as avg_temp, avg(max_tmpf) as avg_high_temp, avg(min_tmpf) as avg_low_temp, sum(pday) as precip, avg(avg_sknt) * 1.15 as avg_wind_speed, min(min_tmpf) as min_low, max(min_tmpf) as max_low, max(max_tmpf) as max_high, avg((max_dwpf + min_dwpf)/2.) as avg_dewp from summary s JOIN stations t on (s.iemid = t.iemid) WHERE t.network = %s and t.id = %s and to_char(day, 'mmdd') in %s GROUP by yr ORDER by yr ASC """, pgconn, params=(ctx["network"], station, tuple(sdays)), ) if df.empty: raise NoDataFound("No data was found.") (fig, ax) = plt.subplots(2, 1, figsize=(8, 6)) bars = ax[0].bar(df["yr"], df[varname], facecolor="r", edgecolor="r", align="center") thisvalue = "M" for mybar, x, y in zip(bars, df["yr"], df[varname]): if x == year: mybar.set_facecolor("g") mybar.set_edgecolor("g") thisvalue = y ax[0].set_xlabel("Year, %s = %s" % (year, nice(thisvalue))) ax[0].axhline(df[varname].mean(), lw=2, label="Avg: %.2f" % (df[varname].mean(), )) ylabel = r"Temperature $^\circ$F" if varname in ["precip"]: ylabel = "Precipitation [inch]" elif varname in ["avg_wind_speed"]: ylabel = "Wind Speed [MPH]" ax[0].set_ylabel(ylabel) ax[0].set_title(("[%s] %s\n%s from %s through %s") % ( station, ctx["_nt"].sts[station]["name"], PDICT.get(varname), sts.strftime("%d %b"), ets.strftime("%d %b"), )) ax[0].grid(True) ax[0].legend(ncol=2, fontsize=10) ax[0].set_xlim(df["yr"].min() - 1, df["yr"].max() + 1) dy = df[varname].max() - df[varname].min() ax[0].set_ylim(df[varname].min() - dy * 0.2, df[varname].max() + dy * 0.25) box = ax[0].get_position() ax[0].set_position([box.x0, box.y0 + 0.02, box.width, box.height * 0.98]) # Plot 2: CDF vals = df[pd.notnull(df[varname])][varname] X2 = np.sort(vals) ptile = np.percentile(vals, [0, 5, 50, 95, 100]) N = len(vals) F2 = np.array(range(N)) / float(N) * 100.0 ax[1].plot(X2, 100.0 - F2) ax[1].set_xlabel("based on summarized hourly reports, %s" % (ylabel, )) ax[1].set_ylabel("Observed Frequency [%]") ax[1].grid(True) ax[1].set_yticks([0, 5, 10, 25, 50, 75, 90, 95, 100]) mysort = df.sort_values(by=varname, ascending=True) info = ("Min: %.2f %.0f\n95th: %.2f\nMean: %.2f\nSTD: %.2f\n5th: %.2f\n" "Max: %.2f %.0f") % ( df[varname].min(), df["yr"][mysort.index[0]], ptile[1], df[varname].mean(), df[varname].std(), ptile[3], df[varname].max(), df["yr"][mysort.index[-1]], ) ax[1].axvline(thisvalue, lw=2, color="g") ax[1].text( 0.8, 0.95, info, transform=ax[1].transAxes, va="top", bbox=dict(facecolor="white", edgecolor="k"), ) return fig, df
def plotter(fdict): """ Go """ pgconn = get_dbconn('coop') cursor = pgconn.cursor() ctx = get_autoplot_context(fdict, get_description()) station = ctx['station'] thres1 = ctx['thres1'] thres2 = ctx['thres2'] thres3 = ctx['thres3'] thres4 = ctx['thres4'] thres = [thres1, thres2, thres3, thres4] prs = [make(thres1), make(thres2), make(thres3), make(thres4)] table = "alldata_%s" % (station[:2], ) s = ctx['_nt'].sts[station]['archive_begin'] e = datetime.date.today() if s is None: raise NoDataFound("Unknown metadata.") res = """\ # IEM Climodat https://mesonet.agron.iastate.edu/climodat/ # Report Generated: %s # Climate Record: %s -> %s # Site Information: [%s] %s # Contact Information: Daryl Herzmann [email protected] 515.294.5978 # SEASONAL TEMPERATURE CYCLES PER YEAR # 1 CYCLE IS A TEMPERATURE VARIATION FROM A VALUE BELOW A THRESHOLD # TO A VALUE EXCEEDING A THRESHOLD. THINK OF IT AS FREEZE/THAW CYCLES # FIRST DATA COLUMN WOULD BE FOR CYCLES EXCEEDING 26 AND 38 DEGREES F THRES %2.0f-%2.0f %2.0f-%2.0f %2.0f-%2.0f %2.0f-%2.0f %2.0f-%2.0f %2.0f-%2.0f %2.0f-%2.0f %2.0f-%2.0f YEAR SPRING FALL SPRING FALL SPRING FALL SPRING FALL """ % ( datetime.date.today().strftime("%d %b %Y"), s.date(), datetime.date.today(), station, ctx['_nt'].sts[station]['name'], prs[0][0], prs[0][1], prs[0][0], prs[0][1], prs[1][0], prs[1][1], prs[1][0], prs[1][1], prs[2][0], prs[2][1], prs[2][0], prs[2][1], prs[3][0], prs[3][1], prs[3][0], prs[3][1], ) df = pd.DataFrame( { thres1 + 's': 0, thres1 + 'f': 0, thres2 + 's': 0, thres2 + 'f': 0, thres3 + 's': 0, thres3 + 'f': 0, thres4 + 's': 0, thres4 + 'f': 0 }, index=pd.Series(range(s.year, e.year + 1), name='year')) cycle_pos = [-1, -1, -1, -1] cursor.execute( """ SELECT day, high, low from """ + table + """ WHERE station = %s and high is not null and low is not null ORDER by day ASC """, (station, )) for row in cursor: ts = row[0] high = int(row[1]) low = int(row[2]) for i, (lower, upper) in enumerate(prs): ckey = thres[i] + ('s' if ts.month < 7 else 'f') # cycles lower if cycle_pos[i] == 1 and low < lower: # print 'Cycled lower', low, ts cycle_pos[i] = -1 df.loc[ts.year, ckey] += 0.5 # cycled higher if cycle_pos[i] == -1 and high > upper: # print 'Cycled higher', high, ts cycle_pos[i] = 1 df.loc[ts.year, ckey] += 0.5 for yr, row in df.iterrows(): res += ("%s %-8i%-8i%-8i%-8i%-8i%-8i%-8i%-8i\n") % ( yr, row[thres1 + 's'], row[thres1 + 'f'], row[thres2 + 's'], row[thres2 + 'f'], row[thres3 + 's'], row[thres3 + 'f'], row[thres4 + 's'], row[thres4 + 'f']) res += ("AVG %-8.1f%-8.1f%-8.1f%-8.1f%-8.1f%-8.1f%-8.1f%-8.1f\n") % ( df[thres1 + 's'].mean(), df[thres1 + 'f'].mean(), df[thres2 + 's'].mean(), df[thres2 + 'f'].mean(), df[thres3 + 's'].mean(), df[thres3 + 'f'].mean(), df[thres4 + 's'].mean(), df[thres4 + 'f'].mean()) return None, df, res
from pandas.io.sql import read_sql import matplotlib.pyplot as plt from pyiem.util import get_dbconn pgconn = get_dbconn("idep") df = read_sql( """ SELECT * from results_by_huc12 where scenario in (0, 7, 9) and huc_12 = '102300031504' and valid >= '2008-01-01' and valid < '2016-01-01' ORDER by valid ASC """, pgconn, index_col=None, ) (fig, ax) = plt.subplots(1, 1) for scenario, label in zip([0, 7, 9], ["Baseline", "UCS 4 Year", "UCS 3 Year"]): df2 = df[df["scenario"] == scenario] x = [] y = [] accum = 0 for i, row in df2.iterrows(): x.append(row["valid"]) accum += row["avg_loss"] * 4.463 y.append(accum) ax.plot(x, y, label="%s" % (label, )) ax.legend(loc="best") ax.set_title("Accumulated Soil Detachment for 102300031504") ax.set_ylabel("Soil Detachment [t/a]")
def do_polygon(ctx): """polygon workflow""" pgconn = get_dbconn("postgis") varname = ctx["v"] station = ctx["station"][:4] state = ctx["state"] phenomena = ctx["phenomena"] significance = ctx["significance"] t = ctx["t"] sdate = ctx["sdate"] edate = ctx["edate"] year = ctx["year"] year2 = ctx["year2"] # figure out the start and end timestamps if varname == "total": sts = sdate ets = edate elif varname == "hour": raise NoDataFound("Sorry, not implemented for polygon summaries.") elif varname == "yearcount": sts = datetime.datetime(year, 1, 1).replace(tzinfo=pytz.utc) ets = datetime.datetime(year, 12, 31, 23, 59).replace(tzinfo=pytz.utc) else: sts = datetime.datetime(year, 1, 1).replace(tzinfo=pytz.utc) ets = datetime.datetime(year2, 12, 31, 23, 59).replace(tzinfo=pytz.utc) # We need to figure out how to get the warnings either by state or by wfo if t == "cwa": (west, south, east, north) = wfo_bounds[station] else: (west, south, east, north) = state_bounds[state] # buffer by 5 degrees so to hopefully get all polys (west, south) = [x - 2 for x in (west, south)] (east, north) = [x + 2 for x in (east, north)] # create grids griddelta = 0.01 lons = np.arange(west, east, griddelta) lats = np.arange(south, north, griddelta) YSZ = len(lats) XSZ = len(lons) lons, lats = np.meshgrid(lons, lats) affine = Affine(griddelta, 0.0, west, 0.0, 0 - griddelta, north) ones = np.ones((int(YSZ), int(XSZ))) counts = np.zeros((int(YSZ), int(XSZ))) wfolimiter = "" if ctx["t"] == "cwa": wfolimiter = " wfo = '%s' and " % (station, ) # do arbitrary buffer to prevent segfaults? df = read_postgis( """ SELECT ST_Forcerhr(ST_Buffer(geom, 0.0005)) as geom, issue, expire from sbw where """ + wfolimiter + """ phenomena = %s and status = 'NEW' and significance = %s and ST_Within(geom, ST_GeomFromEWKT('SRID=4326;POLYGON((%s %s, %s %s, %s %s, %s %s, %s %s))')) and ST_IsValid(geom) and issue >= %s and issue <= %s ORDER by issue ASC """, pgconn, params=( phenomena, significance, west, south, west, north, east, north, east, south, west, south, sts, ets, ), geom_col="geom", index_col=None, ) # print df, sts, ets, west, east, south, north zs = zonal_stats( df["geom"], ones, affine=affine, nodata=-1, all_touched=True, raster_out=True, ) for i, z in enumerate(zs): aff = z["mini_raster_affine"] mywest = aff.c mynorth = aff.f raster = np.flipud(z["mini_raster_array"]) x0 = int((mywest - west) / griddelta) y1 = int((mynorth - south) / griddelta) dy, dx = np.shape(raster) x1 = x0 + dx y0 = y1 - dy if x0 < 0 or x1 >= XSZ or y0 < 0 or y1 >= YSZ: # print raster.mask.shape, west, x0, x1, XSZ, north, y0, y1, YSZ continue if varname == "lastyear": counts[y0:y1, x0:x1] = np.where(raster.mask, counts[y0:y1, x0:x1], df.iloc[i]["issue"].year) else: counts[y0:y1, x0:x1] += np.where(raster.mask, 0, 1) if np.max(counts) == 0: raise NoDataFound("Sorry, no data found for query!") # construct the df ctx["df"] = pd.DataFrame({ "lat": lats.ravel(), "lon": lons.ravel(), "val": counts.ravel() }) minv = df["issue"].min() maxv = df["issue"].max() if varname == "lastyear": ctx["title"] = "Year of Last" if (maxv.year - minv.year) < 3: bins = range(int(minv.year) - 4, int(maxv.year) + 2) else: bins = range(int(minv.year), int(maxv.year) + 2) elif varname == "yearcount": ctx["title"] = "Count for %s" % (year, ) elif varname == "total": ctx["title"] = "Total" ctx["subtitle"] = (" between %s and %s UTC") % ( sdate.strftime("%d %b %Y %H%M"), edate.strftime("%d %b %Y %H%M"), ) elif varname == "yearavg": ctx["title"] = ("Yearly Avg: %s and %s") % ( minv.strftime("%d %b %Y"), maxv.strftime("%d %b %Y"), ) years = (maxv.year - minv.year) + 1 counts = counts / years maxv = np.max(counts) if varname != "lastyear": if varname == "total": if maxv < 8: bins = np.arange(1, 8, 1) else: bins = np.linspace(1, maxv + 3, 10, dtype="i") else: for delta in [500, 50, 5, 1, 0.5, 0.05]: bins = np.arange(0, (maxv + 1.0) * 1.05, delta) if len(bins) > 8: break bins[0] = 0.01 ctx["bins"] = bins ctx["data"] = counts ctx["lats"] = lats ctx["lons"] = lons
from __future__ import print_function import os import datetime import sys import re import requests import pandas as pd from pandas.io.sql import read_sql import numpy as np from pyiem.reference import TRACE_VALUE from pyiem.datatypes import temperature, distance from pyiem.network import Table as NetworkTable from pyiem.util import get_dbconn, exponential_backoff PGCONN = get_dbconn('coop') BASEDIR = "/mesonet/tmp" BASE = datetime.date(1850, 1, 1) TODAY = datetime.date.today() STCONV = { 'WA': '45', 'DE': '07', 'DC': '18', 'WI': '47', 'WV': '46', 'HI': '51', 'FL': '08', 'WY': '48', 'NH': '27',
def plotter(fdict): """ Go """ pgconn = get_dbconn('coop') ctx = get_autoplot_context(fdict, get_description()) station = ctx['station'] ab = ctx['_nt'].sts[station]['archive_begin'] if ab is None: raise NoDataFound("Unknown station metadata.") year1 = ctx.get('year1') year2 = ctx.get('year2') year3 = ctx.get('year3') sdate = ctx['sdate'] table = "alldata_%s" % (station[:2], ) delta = 1 if sdate.month > 6 else 0 df = read_sql(""" WITH years as ( SELECT distinct year + %s as myyear from """ + table + """ WHERE station = %s and sday = %s), obs as ( SELECT day, precip, case when sday >= %s then year + %s else year end as year from """ + table + """ WHERE station = %s and precip is not null ) SELECT day, year, precip, row_number() OVER (PARTITION by year ORDER by day ASC) as row, sum(precip) OVER (PARTITION by year ORDER by day ASC) as accum from obs WHERE year in (select myyear from years) ORDER by day ASC """, pgconn, params=(delta, station, sdate.strftime("%m%d"), sdate.strftime("%m%d"), delta, station), index_col='day') if df.empty: raise NoDataFound("No data found!") (fig, ax) = plt.subplots(1, 1) # Average jday = df[['row', 'accum']].groupby('row').mean() jday['accum'].values[-1] = jday['accum'].values[-2] ax.plot(range(1, len(jday.index) + 1), jday['accum'], lw=2, zorder=5, color='k', label='Average - %.2f' % (jday['accum'].iloc[-1], )) # Min and Max jmin = df[['row', 'accum']].groupby('row').min() jmax = df[['row', 'accum']].groupby('row').max() ax.fill_between(range(1, len(jday.index) + 1), jmin['accum'], jmax['accum'], zorder=2, color='tan') # find max year plotted = [] for year, color in zip([ df['accum'].idxmax().year, df[df['row'] == 365]['accum'].idxmin().year, year1, year2, year3 ], ['b', 'brown', 'r', 'g', 'purple']): if year is None or year in plotted: continue plotted.append(year) df2 = df[df['year'] == year] ax.plot(range(1, len(df2.index) + 1), df2['accum'], label='%s - %.2f' % (year, df2['accum'].iloc[-1]), color=color, lw=2) ax.set_title( ("Accumulated Precipitation after %s\n" "[%s] %s (%s-%s)") % (sdate.strftime("%-d %B"), station, ctx['_nt'].sts[station]['name'], ab.year, datetime.date.today().year)) ax.set_ylabel("Precipitation [inch]") ax.grid(True) ax.legend(loc=2) xticks = [] xticklabels = [] for i in range(366): date = sdate + datetime.timedelta(days=i) if date.day != 1: continue xticks.append(i) xticklabels.append(date.strftime("%b")) ax.set_xlim(0, 367) ax.set_ylim(bottom=-0.1) ax.set_xticks(xticks) ax.set_xticklabels(xticklabels) return fig, df
def do_ugc(ctx): """Do UGC based logic.""" pgconn = get_dbconn("postgis") cursor = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor) varname = ctx["v"] station = ctx["station"][:4] state = ctx["state"] phenomena = ctx["phenomena"] significance = ctx["significance"] t = ctx["t"] sdate = ctx["sdate"] edate = ctx["edate"] year = ctx["year"] year2 = ctx["year2"] if varname == "lastyear": if t == "cwa": cursor.execute( """ select ugc, max(issue at time zone 'UTC') from warnings WHERE wfo = %s and phenomena = %s and significance = %s GROUP by ugc """, ( station if len(station) == 3 else station[1:], phenomena, significance, ), ) else: cursor.execute( """ select ugc, max(issue at time zone 'UTC') from warnings WHERE substr(ugc, 1, 2) = %s and phenomena = %s and significance = %s GROUP by ugc """, (state, phenomena, significance), ) rows = [] data = {} for row in cursor: rows.append(dict(valid=row[1], year=row[1].year, ugc=row[0])) data[row[0]] = row[1].year ctx["title"] = "Year of Last" datavar = "year" elif varname == "yearcount": table = "warnings_%s" % (year, ) if t == "cwa": cursor.execute( """ select ugc, count(*) from """ + table + """ WHERE wfo = %s and phenomena = %s and significance = %s GROUP by ugc """, ( station if len(station) == 3 else station[1:], phenomena, significance, ), ) else: cursor.execute( """ select ugc, count(*) from """ + table + """ WHERE substr(ugc, 1, 2) = %s and phenomena = %s and significance = %s GROUP by ugc """, (state, phenomena, significance), ) rows = [] data = {} for row in cursor: rows.append(dict(count=row[1], year=year, ugc=row[0])) data[row[0]] = row[1] ctx["title"] = "Count for %s" % (year, ) datavar = "count" elif varname == "total": if t == "cwa": cursor.execute( """ select ugc, count(*), min(issue at time zone 'UTC'), max(issue at time zone 'UTC') from warnings WHERE wfo = %s and phenomena = %s and significance = %s and issue >= %s and issue <= %s GROUP by ugc """, ( station if len(station) == 3 else station[1:], phenomena, significance, sdate, edate, ), ) else: cursor.execute( """ select ugc, count(*), min(issue at time zone 'UTC'), max(issue at time zone 'UTC') from warnings WHERE substr(ugc, 1, 2) = %s and phenomena = %s and significance = %s and issue >= %s and issue < %s GROUP by ugc """, (state, phenomena, significance, sdate, edate), ) rows = [] data = {} for row in cursor: rows.append( dict( count=row[1], year=year, ugc=row[0], minissue=row[2], maxissue=row[3], )) data[row[0]] = row[1] ctx["title"] = "Total" ctx["subtitle"] = (" between %s and %s UTC") % ( sdate.strftime("%d %b %Y %H%M"), edate.strftime("%d %b %Y %H%M"), ) datavar = "count" elif varname == "hour": cursor.execute( """ WITH data as ( SELECT ugc, issue at time zone tzname as v from warnings w JOIN stations t ON (w.wfo = (case when length(t.id) = 4 then substr(t.id, 1, 3) else t.id end)) WHERE t.network = 'WFO' and phenomena = %s and significance = %s and issue >= %s and issue < %s), agg as ( SELECT ugc, extract(hour from v) as hr, count(*) from data GROUP by ugc, hr), ranks as ( SELECT ugc, hr, rank() OVER (PARTITION by ugc ORDER by count DESC) from agg) SELECT ugc, hr from ranks where rank = 1 """, (phenomena, significance, sdate, edate), ) rows = [] data = {} ctx["labels"] = {} midnight = datetime.datetime(2000, 1, 1) for row in cursor: rows.append(dict(hour=int(row[1]), ugc=row[0])) data[row[0]] = row[1] ctx["labels"][row[0]] = ( midnight + datetime.timedelta(hours=row[1])).strftime("%-I %p") ctx["title"] = ("Most Freq. Issue Hour: %s and %s") % ( sdate.strftime("%d %b %Y"), edate.strftime("%d %b %Y"), ) datavar = "hour" elif varname == "yearavg": if t == "cwa": cursor.execute( """ select ugc, count(*), min(issue at time zone 'UTC'), max(issue at time zone 'UTC') from warnings WHERE wfo = %s and phenomena = %s and significance = %s and issue >= %s and issue <= %s GROUP by ugc """, ( station if len(station) == 3 else station[1:], phenomena, significance, datetime.date(year, 1, 1), datetime.date(year2 + 1, 1, 1), ), ) else: cursor.execute( """ select ugc, count(*), min(issue at time zone 'UTC'), max(issue at time zone 'UTC') from warnings WHERE substr(ugc, 1, 2) = %s and phenomena = %s and significance = %s and issue >= %s and issue < %s GROUP by ugc """, ( state, phenomena, significance, datetime.date(year, 1, 1), datetime.date(year2 + 1, 1, 1), ), ) rows = [] data = {} minv = datetime.datetime(2050, 1, 1) maxv = datetime.datetime(1986, 1, 1) for row in cursor: if row[2] < minv: minv = row[2] if row[3] > maxv: maxv = row[3] rows.append( dict( count=row[1], year=year, ugc=row[0], minissue=row[2], maxissue=row[3], )) data[row[0]] = row[1] ctx["title"] = ("Yearly Avg: %s and %s") % ( minv.strftime("%d %b %Y"), maxv.strftime("%d %b %Y"), ) datavar = "average" if not rows: raise NoDataFound("Sorry, no data found for query!") df = pd.DataFrame(rows) if varname == "yearavg": years = maxv.year - minv.year + 1 df["average"] = df["count"] / years for key in data: data[key] = round(data[key] / float(years), 2) maxv = df[datavar].max() for delta in [500, 50, 5, 1, 0.5, 0.05]: bins = np.arange(0, (maxv + 1.0) * 1.05, delta) if len(bins) > 8: break if len(bins) > 8: bins = bins[::int(len(bins) / 8.0)] bins[0] = 0.01 elif varname == "hour": bins = list(range(0, 25)) else: bins = list( range(np.min(df[datavar][:]), np.max(df[datavar][:]) + 2, 1)) if len(bins) < 3: bins.append(bins[-1] + 1) if len(bins) > 8: bins = np.linspace( np.min(df[datavar][:]), np.max(df[datavar][:]) + 2, 8, dtype="i", ) ctx["bins"] = bins ctx["data"] = data ctx["df"] = df
def plotter(fdict): """ Go """ pgconn = get_dbconn('asos') ctx = get_autoplot_context(fdict, get_description()) station = ctx['zstation'] network = ctx['network'] syear = ctx['syear'] eyear = ctx['eyear'] groupby = ctx['groupby'] sts = datetime.date(syear, 1, 1) ets = datetime.date(eyear + 1, 1, 1) nt = NetworkTable(network) code = ctx['code'] if code == 'PSN': code = "+SN" PDICT['+SN'] = PDICT['PSN'] if groupby == 'week': data = np.ma.zeros((24, 52), 'f') df = read_sql(""" WITH data as ( SELECT valid at time zone %s + '10 minutes'::interval as v from alldata where station = %s and array_to_string(wxcodes, '') LIKE '%%""" + code + """%%' and valid > %s and valid < %s), agg as ( SELECT distinct extract(week from v)::int as week, extract(doy from v)::int as doy, extract(year from v)::int as year, extract(hour from v)::int as hour from data) SELECT week, year, hour, count(*) from agg WHERE week < 53 GROUP by week, year, hour """, pgconn, params=(nt.sts[station]['tzname'], station, sts, ets), index_col=None) else: data = np.ma.zeros((24, 366), 'f') df = read_sql(""" WITH data as ( SELECT valid at time zone %s + '10 minutes'::interval as v from alldata where station = %s and array_to_string(wxcodes, '') LIKE '%%""" + code + """%%' and valid > %s and valid < %s), agg as ( SELECT distinct extract(doy from v)::int as doy, extract(year from v)::int as year, extract(hour from v)::int as hour from data) SELECT doy, year, hour, count(*) from agg GROUP by doy, year, hour """, pgconn, params=(nt.sts[station]['tzname'], station, sts, ets), index_col=None) if df.empty: raise ValueError("No data was found, sorry!") minyear = df['year'].min() maxyear = df['year'].max() for _, row in df.iterrows(): data[row['hour'], row[groupby] - 1] += 1 data.mask = np.where(data == 0, True, False) fig = plt.figure(figsize=(8, 6)) ax = plt.axes([0.11, 0.25, 0.7, 0.65]) cax = plt.axes([0.82, 0.04, 0.02, 0.15]) res = ax.imshow(data, aspect='auto', rasterized=True, interpolation='nearest') fig.colorbar(res, cax=cax) xloc = plt.MaxNLocator(4) cax.yaxis.set_major_locator(xloc) cax.set_ylabel("Count") ax.set_ylim(-0.5, 23.5) ax.set_yticks((0, 4, 8, 12, 16, 20)) ax.set_ylabel("Local Time, %s" % (nt.sts[station]['tzname'], )) ax.set_yticklabels(('Mid', '4 AM', '8 AM', 'Noon', '4 PM', '8 PM')) ax.set_title(("[%s] %s %s Reports\n[%.0f - %.0f]" " by hour and %s") % (station, nt.sts[station]['name'], PDICT[code], minyear, maxyear, PDICT2[groupby].replace("group ", ""))) ax.grid(True) lax = plt.axes([0.11, 0.1, 0.7, 0.15]) if groupby == 'week': ax.set_xticks(np.arange(0, 55, 7)) lax.bar(np.arange(0, 52), np.ma.sum(data, 0), facecolor='tan') lax.set_xlim(-0.5, 51.5) lax.set_xticks(np.arange(0, 55, 7)) lax.set_xticklabels(('Jan 1', 'Feb 19', 'Apr 8', 'May 27', 'Jul 15', 'Sep 2', 'Oct 21', 'Dec 9')) else: ax.set_xticks( [1, 32, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 365]) lax.bar(np.arange(0, 366), np.ma.sum(data, 0), facecolor='tan') lax.set_xlim(-0.5, 365.5) lax.set_xticks( [1, 32, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 365]) lax.set_xticklabels(calendar.month_abbr[1:]) plt.setp(ax.get_xticklabels(), visible=False) # Bottom grid lax.grid(True) yloc = plt.MaxNLocator(3) lax.yaxis.set_major_locator(yloc) lax.yaxis.get_major_ticks()[-1].label1.set_visible(False) # Right grid rax = plt.axes([0.81, 0.25, 0.15, 0.65]) rax.barh(np.arange(0, 24) - 0.4, np.ma.sum(data, 1), facecolor='tan') rax.set_ylim(-0.5, 23.5) rax.set_yticks([]) xloc = plt.MaxNLocator(3) rax.xaxis.set_major_locator(xloc) rax.xaxis.get_major_ticks()[0].label1.set_visible(False) rax.grid(True) return fig, df
def run(wfo, year, phenomena, significance, etn): """Do great things""" pgconn = get_dbconn('postgis') cursor = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor) table = "warnings_%s" % (year,) # This is really a BUG here and we need to rearch the database cursor.execute(""" SELECT first_value(report) OVER (ORDER by product_issue ASC) as report, first_value(svs) OVER (ORDER by product_issue ASC) as svs_updates, first_value(issue at time zone 'UTC') OVER (ORDER by issue ASC NULLS LAST) as utc_issue, first_value(expire at time zone 'UTC') OVER (ORDER by expire DESC NULLS LAST) as utc_expire from """+table+""" w WHERE w.wfo = %s and eventid = %s and phenomena = %s and significance = %s """, (wfo, etn, phenomena, significance)) res = { 'generation_time': datetime.datetime.utcnow().strftime(ISO9660), 'year': year, 'phenomena': phenomena, 'significance': significance, 'etn': etn, 'wfo': wfo } if cursor.rowcount == 0: return json.dumps(res) row = cursor.fetchone() res['report'] = {'text': row['report']} res['svs'] = [] if row['svs_updates'] is not None: for token in row['svs_updates'].split("__"): if token.strip() != '': res['svs'].append({'text': token}) res['utc_issue'] = row['utc_issue'].strftime(ISO9660) res['utc_expire'] = row['utc_expire'].strftime(ISO9660) # Now lets get UGC information cursor.execute(""" SELECT u.ugc, u.name, w.status, w.product_issue at time zone 'UTC' utc_product_issue, w.issue at time zone 'UTC' utc_issue, w.expire at time zone 'UTC' utc_expire, w.init_expire at time zone 'UTC' utc_init_expire, w.updated at time zone 'UTC' utc_updated from """+table+""" w JOIN ugcs u on (w.gid = u.gid) WHERE w.wfo = %s and eventid = %s and phenomena = %s and significance = %s ORDER by u.ugc ASC """, (wfo, etn, phenomena, significance)) res['ugcs'] = [] for row in cursor: res['ugcs'].append({ 'ugc': row['ugc'], 'name': row['name'], 'status': row['status'], 'utc_product_issue': row['utc_product_issue'].strftime(ISO9660), 'utc_issue': row['utc_issue'].strftime(ISO9660), 'utc_init_expire': row['utc_init_expire'].strftime(ISO9660), 'utc_expire': row['utc_expire'].strftime(ISO9660), 'utc_updated': row['utc_updated'].strftime(ISO9660), }) return json.dumps(res)
import mx.DateTime from pyiem.network import Table as NetworkTable from pyiem.util import get_dbconn nt = NetworkTable(('IACLIMATE', 'ILCLIMATE', 'INCLIMATE', 'OHCLIMATE', 'MICLIMATE', 'KYCLIMATE', 'WICLIMATE', 'MNCLIMATE', 'SDCLIMATE', 'NDCLIMATE', 'NECLIMATE', 'KSCLIMATE', 'MOCLIMATE')) _THISYEAR = mx.DateTime.now().year _ENDYEAR = mx.DateTime.now().year + 1 _ARCHIVEENDTS = mx.DateTime.now() - mx.DateTime.RelativeDateTime(days=1) _ENDTS = mx.DateTime.DateTime(_ENDYEAR, 1, 1) mesosite = get_dbconn('mesosite', user='******') mcursor = mesosite.cursor() mcursor.execute(""" SELECT propvalue from properties where propname = 'iaclimate.end' """) row = mcursor.fetchone() _QCENDTS = mx.DateTime.strptime(row[0], '%Y-%m-%d') mcursor.close() def get_table(sid): """ Return the table which has the data for this siteID """ return "alldata_%s" % (sid[:2],)
"""Figure out if we have new daily records set""" from __future__ import print_function import sys import json import datetime import psycopg2.extras from pyiem.util import get_dbconn COOP = get_dbconn('coop') ccursor = COOP.cursor(cursor_factory=psycopg2.extras.DictCursor) ccursor2 = COOP.cursor() sts = datetime.datetime(int(sys.argv[1]), int(sys.argv[2]), 1) ets = (sts + datetime.timedelta(days=35)).replace(day=1) cnt = { 'climate': { 'max_high': 0, 'min_high': 0, 'max_low': 0, 'min_low': 0, 'max_precip': 0 }, 'climate51': { 'max_high': 0, 'min_high': 0, 'max_low': 0, 'min_low': 0, 'max_precip': 0 }
def plotter(fdict): """ Go """ pgconn = get_dbconn("postgis") ctx = get_autoplot_context(fdict, get_description()) station = ctx["station"][:4] ctx["_nt"].sts["_ALL"] = {"name": "All Offices"} fig = plt.figure(figsize=(8, 14 if station != "_ALL" else 21)) ax = [None, None] ax[0] = plt.axes([0.1, 0.75, 0.85, 0.2]) ax[1] = plt.axes([0.1, 0.05, 0.85, 0.65]) if station == "_ALL": df = read_sql( """ SELECT distinct extract(year from issue) as year, phenomena, significance from warnings WHERE phenomena is not null and significance is not null and issue > '2005-01-01' """, pgconn, index_col=None, ) else: df = read_sql( """ SELECT distinct extract(year from issue) as year, phenomena, significance from warnings WHERE wfo = %s and phenomena is not null and significance is not null and issue > '2005-01-01' """, pgconn, params=(station, ), index_col=None, ) if df.empty: raise NoDataFound("No data was found for this WFO.") df["wfo"] = station df["year"] = df["year"].astype("i") gdf = df.groupby("year").count() ax[0].bar(gdf.index.values, gdf["wfo"], width=0.8, fc="b", ec="b", align="center") for yr, row in gdf.iterrows(): ax[0].text(yr, row["wfo"] + 1, "%s" % (row["wfo"], ), ha="center") ax[0].set_title(("[%s] NWS %s\nCount of Distinct VTEC Phenomena/" "Significance - %i to %i") % ( station, ctx["_nt"].sts[station]["name"], df["year"].min(), df["year"].max(), )) ax[0].grid() ax[0].set_ylabel("Count") ax[0].set_xlim(gdf.index.values.min() - 0.5, gdf.index.values.max() + 0.5) pos = {} i = 1 df.sort_values(["phenomena", "significance"], inplace=True) for _, row in df.iterrows(): key = "%s.%s" % (row["phenomena"], row["significance"]) if key not in pos: pos[key] = i i += 1 ax[1].text( row["year"], pos[key], key, ha="center", va="center", fontsize=10, bbox=dict(color="white"), ) ax[1].set_title("VTEC <Phenomena.Significance> Issued by Year") ax[1].set_ylim(0, i) ax[1].grid(True) ax[1].set_xlim(gdf.index.values.min() - 0.5, gdf.index.values.max() + 0.5) return fig, df
def plotter(fdict): """ Go """ pgconn = get_dbconn('iem') ctx = get_autoplot_context(fdict, get_description()) varname = ctx['var'] sector = ctx['sector'] state = ctx['state'] wfo = ctx['wfo'] today = ctx['sdate'] yesterday = today - datetime.timedelta(days=1) d180 = today - datetime.timedelta(days=180) df = read_sql(""" with obs as ( select station, valid, (case when low > low_normal then 1 else 0 end) as low_hit, (case when high > high_normal then 1 else 0 end) as high_hit, (case when precip >= 0.01 then 1 else 0 end) as precip_hit from cli_data where high is not null and high_normal is not null and low is not null and low_normal is not null and valid > %s and valid <= %s), totals as ( SELECT station, max(case when low_hit = 0 then valid else %s end) as last_low_below, max(case when low_hit = 1 then valid else %s end) as last_low_above, max(case when high_hit = 0 then valid else %s end) as last_high_below, max(case when high_hit = 1 then valid else %s end) as last_high_above, max(case when precip_hit = 0 then valid else %s end) as last_dry, max(case when precip_hit = 1 then valid else %s end) as last_wet, count(*) as count from obs GROUP by station) SELECT station, last_low_below, last_low_above, last_high_below, last_high_above, last_dry, last_wet from totals where count > 170 """, pgconn, params=(d180, today, d180, d180, d180, d180, d180, d180), index_col='station') if df.empty: raise NoDataFound("No Data Found.") lats = [] lons = [] vals = [] colors = [] labels = [] df['precip_days'] = (df['last_dry'] - df['last_wet']).dt.days df['low_days'] = (df['last_low_above'] - df['last_low_below']).dt.days df['high_days'] = (df['last_high_above'] - df['last_high_below']).dt.days # reorder the frame so that the largest values come first df = df.reindex(df[varname + '_days'].abs().sort_values(ascending=False).index) for station, row in df.iterrows(): if station not in ctx['_nt'].sts: continue lats.append(ctx['_nt'].sts[station]['lat']) lons.append(ctx['_nt'].sts[station]['lon']) if varname == 'precip': last_wet = row['last_wet'] days = 0 if last_wet in [today, yesterday] else row['precip_days'] else: days = row[varname + '_days'] vals.append(days) colors.append('r' if days > 0 else 'b') labels.append(station[1:]) title = ('Consecutive Days with %s Temp ' 'above(+)/below(-) Average') % (varname.capitalize(), ) if varname == 'precip': title = 'Days Since Last Measurable Precipitation' mp = MapPlot(sector=sector, state=state, cwa=(wfo if len(wfo) == 3 else wfo[1:]), axisbg='tan', statecolor='#EEEEEE', title=title, subtitle=('based on NWS CLI Sites, map approximately ' 'valid for %s') % (today.strftime("%-d %b %Y"), )) mp.plot_values(lons, lats, vals, color=colors, labels=labels, labeltextsize=(8 if sector != 'state' else 12), textsize=(12 if sector != 'state' else 16), labelbuffer=10) return mp.fig, df
def plotter(fdict): """ Go """ ctx = util.get_autoplot_context(fdict, get_description()) date = ctx['date'] sector = ctx['sector'] threshold = ctx['threshold'] threshold_mm = distance(threshold, 'IN').value('MM') window_sts = date - datetime.timedelta(days=90) if window_sts.year != date.year: raise ValueError('Sorry, do not support multi-year plots yet!') # idx0 = iemre.daily_offset(window_sts) idx1 = iemre.daily_offset(date) ncfn = iemre.get_daily_mrms_ncname(date.year) ncvar = 'p01d' nc = util.ncopen(ncfn) if nc is None: raise ValueError("No data for that year, sorry.") # Get the state weight df = gpd.GeoDataFrame.from_postgis(""" SELECT the_geom from states where state_abbr = %s """, util.get_dbconn('postgis'), params=(sector, ), index_col=None, geom_col='the_geom') czs = CachingZonalStats(iemre.MRMS_AFFINE) czs.gen_stats( np.zeros((nc.variables['lat'].size, nc.variables['lon'].size)), df['the_geom']) jslice = None islice = None for nav in czs.gridnav: # careful here as y is flipped in this context jslice = slice(nc.variables['lat'].size - (nav.y0 + nav.ysz), nc.variables['lat'].size - nav.y0) islice = slice(nav.x0, nav.x0 + nav.xsz) grid = np.zeros((jslice.stop - jslice.start, islice.stop - islice.start)) total = np.zeros((jslice.stop - jslice.start, islice.stop - islice.start)) for i, idx in enumerate(range(idx1, idx1 - 90, -1)): total += nc.variables[ncvar][idx, jslice, islice] grid = np.where(np.logical_and(grid == 0, total > threshold_mm), i, grid) lon = nc.variables['lon'][islice] lat = nc.variables['lat'][jslice] nc.close() mp = MapPlot(sector='state', state=sector, titlefontsize=14, subtitlefontsize=12, title=("NOAA MRMS Q3: Number of Recent Days " "till Accumulating %s\" of Precip") % (threshold, ), subtitle=("valid %s: based on per calendar day " "estimated preciptation, GaugeCorr and " "RadarOnly products") % (date.strftime("%-d %b %Y"), )) x, y = np.meshgrid(lon, lat) cmap = plt.get_cmap('terrain') cmap.set_over('k') cmap.set_under('white') mp.pcolormesh(x, y, grid, np.arange(0, 81, 10), cmap=cmap, units='days') mp.drawcounties() mp.drawcities() return mp.fig
"""Legacy.""" from io import StringIO import datetime from paste.request import parse_formvars from pyiem.network import Table as NetworkTable from pyiem.util import get_dbconn from pyiem.templates.iem import TEMPLATE nt = NetworkTable("IACLIMATE") COOP = get_dbconn("coop") ccursor = COOP.cursor() def weather_logic(month, high, low, rain, snow): """Do Something.""" deltaT = high - low if month > 4 and month < 11: # It is summer if deltaT >= 30: if rain == 0.00: return "Sunny!!" return "Mostly sunny w/ Rain!!" if deltaT >= 15: if rain == 0.00: return "Mostly Sunny!!" return "Partly Sunny w/ Rain!!" if rain == 0.00: return "Cloudy!!" return "Cloudy and rainy!!"
def fetch_hourly(form, cols): ''' Return a fetching of hourly data ''' pgconn = get_dbconn('isuag', user='******') cursor = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor) sts, ets = get_dates(form) stations = get_stations(form) delim = get_delimiter(form) if delim == 'tab': delim = '\t' elif delim == 'comma': delim = ',' elif delim == 'space': delim = ' ' if not cols: cols = ["station", "valid", "tmpf", "relh", "solar", "precip", "speed", "drct", "et", "soil04t", "soil12t", "soil24t", "soil50t", "soil12vwc", "soil24vwc", "soil50vwc"] else: cols.insert(0, 'valid') cols.insert(0, 'station') table = 'sm_hourly' sqlextra = ', null as bp_mb_qc, etalfalfa_qc ' if form.getfirst('timeres') == '15minute': table = 'sm_15minute' sqlextra = ', bp_mb_qc, null as etalfalfa_qc' else: if "bp_mb" in cols: cols.remove('bp_mb') cursor.execute("""SELECT station, valid, tair_c_avg_qc, rh_qc, slrkw_avg_qc, rain_mm_tot_qc, ws_mps_s_wvt_qc, winddir_d1_wvt_qc, tsoil_c_avg_qc, t12_c_avg_qc, t24_c_avg_qc, t50_c_avg_qc, calc_vwc_12_avg_qc, calc_vwc_24_avg_qc, calc_vwc_50_avg_qc, lwmv_1_qc, lwmv_2_qc, lwmdry_1_tot_qc, lwmcon_1_tot_qc, lwmwet_1_tot_qc, lwmdry_2_tot_qc, lwmcon_2_tot_qc, lwmwet_2_tot_qc, bpres_avg_qc """ + sqlextra + """ from """ + table + """ WHERE valid >= '%s 00:00' and valid < '%s 00:00' and station in %s ORDER by valid ASC """ % (sts.strftime("%Y-%m-%d"), ets.strftime("%Y-%m-%d"), str(tuple(stations)))) values = [] for row in cursor: valid = row['valid'] station = row['station'] tmpf = temperature(row['tair_c_avg_qc'], 'C').value( 'F') if row['tair_c_avg_qc'] is not None else -99 relh = row['rh_qc'] if row['rh_qc'] is not None else -99 solar = row['slrkw_avg_qc'] if row['slrkw_avg_qc'] is not None else -99 precip = (distance(row['rain_mm_tot_qc'], 'MM').value('IN') if row['rain_mm_tot_qc'] is not None else -99) speed = (row['ws_mps_s_wvt_qc'] * 2.23 if row['ws_mps_s_wvt_qc'] is not None else -99) drct = (row['winddir_d1_wvt_qc'] if row['winddir_d1_wvt_qc'] is not None else -99) et = (distance(row['etalfalfa_qc'], 'MM').value('IN') if row['etalfalfa_qc'] is not None else -99) soil04t = temperature(row['tsoil_c_avg_qc'], 'C').value( 'F') if row['tsoil_c_avg_qc'] is not None else -99 soil12t = temperature(row['t12_c_avg_qc'], 'C').value( 'F') if row['t12_c_avg_qc'] is not None else -99 soil24t = temperature(row['t24_c_avg_qc'], 'C').value( 'F') if row['t24_c_avg_qc'] is not None else -99 soil50t = temperature(row['t50_c_avg_qc'], 'C').value( 'F') if row['t50_c_avg_qc'] is not None else -99 soil12vwc = (row['calc_vwc_12_avg_qc'] if row['calc_vwc_12_avg_qc'] is not None else -99) soil24vwc = (row['calc_vwc_24_avg_qc'] if row['calc_vwc_24_avg_qc'] is not None else -99) soil50vwc = (row['calc_vwc_50_avg_qc'] if row['calc_vwc_50_avg_qc'] is not None else -99) bp_mb = (row['bp_mb_qc'] if row['bp_mb_qc'] is not None else -99) values.append(dict(station=station, valid=valid.strftime("%Y-%m-%d %H:%M"), tmpf=tmpf, relh=relh, solar=solar, precip=precip, speed=speed, drct=drct, et=et, soil04t=soil04t, soil12t=soil12t, soil24t=soil24t, soil50t=soil50t, soil12vwc=soil12vwc, soil24vwc=soil24vwc, soil50vwc=soil50vwc, lwmv_1=row['lwmv_1_qc'], lwmv_2=row['lwmv_2_qc'], lwmdry_1_tot=row['lwmdry_1_tot_qc'], lwmcon_1_tot=row['lwmcon_1_tot_qc'], lwmwet_1_tot=row['lwmwet_1_tot_qc'], lwmdry_2_tot=row['lwmdry_2_tot_qc'], lwmcon_2_tot=row['lwmcon_2_tot_qc'], lwmwet_2_tot=row['lwmwet_2_tot_qc'], bpres_avg=row['bpres_avg_qc'], bp_mb=bp_mb)) return values, cols
def fetch_daily(form, cols): ''' Return a fetching of daily data ''' pgconn = get_dbconn('isuag', user='******') cursor = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor) sts, ets = get_dates(form) stations = get_stations(form) delim = get_delimiter(form) if delim == 'tab': delim = '\t' elif delim == 'comma': delim = ',' elif delim == 'space': delim = ' ' if not cols: cols = ["station", "valid", "high", "low", "rh_min", "rh", "rh_max", "gdd50", "solar", "precip", "sped", "gust", "et", "soil04t", "soil12t", "soil24t", "soil50t", "soil12vwc", "soil24vwc", "soil50vwc"] else: cols.insert(0, 'valid') cols.insert(0, 'station') sql = """ --- Get the Daily Max/Min soil values WITH soils as ( SELECT station, date(valid) as date, min(rh) as rh_min, avg(rh) as rh, max(rh) as rh_max, min(tsoil_c_avg_qc) as soil04tn, max(tsoil_c_avg_qc) as soil04tx, min(t12_c_avg_qc) as soil12tn, max(t12_c_avg_qc) as soil12tx, min(t24_c_avg_qc) as soil24tn, max(t24_c_avg_qc) as soil24tx, min(t50_c_avg_qc) as soil50tn, max(t50_c_avg_qc) as soil50tx from sm_hourly where valid >= '%s 00:00' and valid < '%s 00:00' and station in %s GROUP by station, date ), daily as ( SELECT station, valid, tair_c_max_qc, tair_c_min_qc, slrmj_tot_qc, rain_mm_tot_qc, dailyet_qc, tsoil_c_avg_qc, t12_c_avg_qc, t24_c_avg_qc, t50_c_avg_qc, calc_vwc_12_avg_qc, calc_vwc_24_avg_qc, calc_vwc_50_avg_qc, ws_mps_s_wvt_qc, ws_mps_max_qc, lwmv_1_qc, lwmv_2_qc, lwmdry_1_tot_qc, lwmcon_1_tot_qc, lwmwet_1_tot_qc, lwmdry_2_tot_qc, lwmcon_2_tot_qc, lwmwet_2_tot_qc, bpres_avg_qc from sm_daily WHERE valid >= '%s 00:00' and valid < '%s 00:00' and station in %s ) SELECT d.station, d.valid, s.date, s.soil04tn, s.soil04tx, s.rh, s.rh_min, s.rh_max, s.soil12tn, s.soil12tx, s.soil24tn, s.soil24tx, s.soil50tn, s.soil50tx, tair_c_max_qc, tair_c_min_qc, slrmj_tot_qc, rain_mm_tot_qc, dailyet_qc, tsoil_c_avg_qc, t12_c_avg_qc, t24_c_avg_qc, t50_c_avg_qc, calc_vwc_12_avg_qc, calc_vwc_24_avg_qc, calc_vwc_50_avg_qc, ws_mps_s_wvt_qc, ws_mps_max_qc, round(gddxx(50, 86, c2f( tair_c_max_qc ), c2f( tair_c_min_qc ))::numeric,1) as gdd50, lwmv_1_qc, lwmv_2_qc, lwmdry_1_tot_qc, lwmcon_1_tot_qc, lwmwet_1_tot_qc, lwmdry_2_tot_qc, lwmcon_2_tot_qc, lwmwet_2_tot_qc, bpres_avg_qc FROM soils s JOIN daily d on (d.station = s.station and s.date = d.valid) ORDER by d.valid ASC """ % ( sts.strftime("%Y-%m-%d"), ets.strftime("%Y-%m-%d"), str(tuple(stations)), sts.strftime("%Y-%m-%d"), ets.strftime("%Y-%m-%d"), str(tuple(stations)) ) cursor.execute(sql) values = [] for row in cursor: valid = row['valid'] station = row['station'] high = temperature(row['tair_c_max_qc'], 'C').value( 'F') if row['tair_c_max_qc'] is not None else -99 low = temperature(row['tair_c_min_qc'], 'C').value( 'F') if row['tair_c_min_qc'] is not None else -99 precip = (distance(row['rain_mm_tot_qc'], 'MM').value('IN') if row['rain_mm_tot_qc'] > 0 else 0) et = (distance(row['dailyet_qc'], 'MM').value('IN') if row['dailyet_qc'] is not None and row['dailyet_qc'] > 0 else 0) soil04t = temperature(row['tsoil_c_avg_qc'], 'C').value( 'F') if row['tsoil_c_avg_qc'] is not None else -99 soil04tn = temperature(row['soil04tn'], 'C').value( 'F') if row['soil04tn'] is not None else -99 soil04tx = temperature(row['soil04tx'], 'C').value( 'F') if row['soil04tx'] is not None else -99 soil12t = temperature(row['t12_c_avg_qc'], 'C').value( 'F') if row['t12_c_avg_qc'] is not None else -99 soil12tn = temperature(row['soil12tn'], 'C').value( 'F') if row['soil12tn'] is not None else -99 soil12tx = temperature(row['soil12tx'], 'C').value( 'F') if row['soil12tx'] is not None else -99 soil24t = temperature(row['t24_c_avg_qc'], 'C').value( 'F') if row['t24_c_avg_qc'] is not None else -99 soil24tn = temperature(row['soil24tn'], 'C').value( 'F') if row['soil24tn'] is not None else -99 soil24tx = temperature(row['soil24tx'], 'C').value( 'F') if row['soil24tx'] is not None else -99 soil50t = temperature(row['t50_c_avg_qc'], 'C').value( 'F') if row['t50_c_avg_qc'] is not None else -99 soil50tn = temperature(row['soil50tn'], 'C').value( 'F') if row['soil50tn'] is not None else -99 soil50tx = temperature(row['soil50tx'], 'C').value( 'F') if row['soil50tx'] is not None else -99 soil12vwc = (row['calc_vwc_12_avg_qc'] if row['calc_vwc_12_avg_qc'] is not None else -99) soil24vwc = (row['calc_vwc_24_avg_qc'] if row['calc_vwc_24_avg_qc'] is not None else -99) soil50vwc = (row['calc_vwc_50_avg_qc'] if row['calc_vwc_50_avg_qc'] is not None else -99) speed = (row['ws_mps_s_wvt_qc'] * 2.23 if row['ws_mps_s_wvt_qc'] is not None else -99) gust = (row['ws_mps_max_qc'] * 2.23 if row['ws_mps_max_qc'] is not None else -99) values.append(dict(station=station, valid=valid.strftime("%Y-%m-%d"), high=high, low=low, solar=row['slrmj_tot_qc'], rh=row['rh'], rh_min=row['rh_min'], rh_max=row['rh_max'], gdd50=row['gdd50'], precip=precip, sped=speed, gust=gust, et=et, soil04t=soil04t, soil12t=soil12t, soil24t=soil24t, soil50t=soil50t, soil04tn=soil04tn, soil04tx=soil04tx, soil12tn=soil12tn, soil12tx=soil12tx, soil24tn=soil24tn, soil24tx=soil24tx, soil50tn=soil50tn, soil50tx=soil50tx, soil12vwc=soil12vwc, soil24vwc=soil24vwc, soil50vwc=soil50vwc, lwmv_1=row['lwmv_1_qc'], lwmv_2=row['lwmv_2_qc'], lwmdry_1_tot=row['lwmdry_1_tot_qc'], lwmcon_1_tot=row['lwmcon_1_tot_qc'], lwmwet_1_tot=row['lwmwet_1_tot_qc'], lwmdry_2_tot=row['lwmdry_2_tot_qc'], lwmcon_2_tot=row['lwmcon_2_tot_qc'], lwmwet_2_tot=row['lwmwet_2_tot_qc'], bpres_avg=row['bpres_avg_qc'])) return values, cols
"""Go through our data sheets and cleanup entries that don't exactly match things that we would like to see""" import pyiem.cscap_utils as util from pyiem.util import get_dbconn from pandas.io.sql import read_sql pgconn = get_dbconn("sustainablecorn") df = read_sql("SELECT * from plotids", pgconn, index_col=None) df["key"] = df["uniqueid"] + "::" + df["plotid"] df.set_index("key", inplace=True) config = util.get_config() spr_client = util.get_spreadsheet_client(config) drive = util.get_driveclient(config) # Fake last conditional to make it easy to reprocess one site... res = (drive.files().list(q=("title contains 'Agronomic Data'"), maxResults=999).execute()) HEADERS = [ "uniqueid", "plotid", "depth", "tillage", "rotation", "soil6", "nitrogen", "drainage", "rep", "subsample", "landscape",
def plotter(fdict): """ Go """ ctx = get_autoplot_context(fdict, get_description()) station = ctx["station"] if station not in ctx["_nt"].sts: # This is needed. raise NoDataFound("Unknown station metadata.") varname = ctx["var"] hour = int(ctx["hour"]) month = ctx["month"] level = ctx["level"] agg = ctx["agg"] offset = 0 if month == "all": months = range(1, 13) elif month == "fall": months = [9, 10, 11] elif month == "winter": months = [12, 1, 2] offset = 32 elif month == "spring": months = [3, 4, 5] elif month == "mjj": months = [5, 6, 7] elif month == "summer": months = [6, 7, 8] else: ts = datetime.datetime.strptime("2000-" + month + "-01", "%Y-%b-%d") # make sure it is length two for the trick below in SQL months = [ts.month] name = ctx["_nt"].sts[station]["name"] stations = [station] if station.startswith("_"): name = ctx["_nt"].sts[station]["name"].split("--")[0] stations = ( ctx["_nt"].sts[station]["name"].split("--")[1].strip().split(" ")) pgconn = get_dbconn("postgis") if varname in ["tmpc", "dwpc", "height", "smps"]: leveltitle = " @ %s hPa" % (level, ) dfin = read_sql( """ select extract(year from f.valid + '%s days'::interval) as year, avg(""" + varname + """) as avg_""" + varname + """, min(""" + varname + """) as min_""" + varname + """, max(""" + varname + """) as max_""" + varname + """, count(*) from raob_profile p JOIN raob_flights f on (p.fid = f.fid) WHERE f.station in %s and p.pressure = %s and extract(hour from f.valid at time zone 'UTC') = %s and extract(month from f.valid) in %s GROUP by year ORDER by year ASC """, pgconn, params=(offset, tuple(stations), level, hour, tuple(months)), index_col="year", ) else: leveltitle = "" dfin = read_sql( """ select extract(year from f.valid + '%s days'::interval) as year, count(*), avg(""" + varname + """) as avg_""" + varname + """, min(""" + varname + """) as min_""" + varname + """, max(""" + varname + """) as max_""" + varname + """ from raob_flights f WHERE f.station in %s and extract(hour from f.valid at time zone 'UTC') = %s and extract(month from f.valid) in %s GROUP by year ORDER by year ASC """, pgconn, params=(offset, tuple(stations), hour, tuple(months)), index_col="year", ) # need quorums df = dfin[dfin["count"] > ((len(months) * 28) * 0.75)] if df.empty: raise NoDataFound("No data was found!") colname = "%s_%s" % (agg, varname) fig, ax = plt.subplots(1, 1) avgv = df[colname].mean() bars = ax.bar(df.index.values, df[colname], align="center") for i, _bar in enumerate(bars): val = df.iloc[i][colname] if val < avgv: _bar.set_color("blue") else: _bar.set_color("red") ax.set_xlim(df.index.min() - 1, df.index.max() + 1) rng = df[colname].max() - df[colname].min() ax.set_ylim(df[colname].min() - rng * 0.1, df[colname].max() + rng * 0.1) ax.axhline(avgv, color="k") ax.text(df.index.values[-1] + 2, avgv, "Avg:\n%.1f" % (avgv, )) ax.set_xlabel("Year") ax.set_ylabel("%s %s%s" % (PDICT4[agg], PDICT3[varname], leveltitle)) plt.gcf().text( 0.5, 0.9, ("%s %s %02i UTC Sounding\n" "%s %s%s over %s") % ( station, name, hour, PDICT4[agg], PDICT3[varname], leveltitle, MDICT[month], ), ha="center", va="bottom", ) ax.grid(True) return fig, df