In [1]:
%matplotlib inline
import pandas as pd
import nivapy3 as nivapy
import matplotlib.pyplot as plt
plt.style.use('ggplot')
In [2]:
# Connect to db
eng = nivapy.da.connect()
In [3]:
# Query projects
prj_grid = nivapy.da.select_resa_projects(eng)
prj_grid
In [4]:
prj_df = prj_grid.get_selected_df()
print(len(prj_df))
prj_df
Out[4]:
In [5]:
# Get stations
stn_df = nivapy.da.select_resa_project_stations(prj_df, eng)
print(len(stn_df))
stn_df.head()
Out[5]:
In [6]:
# Map
nivapy.spatial.quickmap(stn_df, popup='station_code')
Out[6]:
In [7]:
# Select parameters
par_grid = nivapy.da.select_resa_station_parameters(stn_df,
'1970-01-01',
'2019-01-01',
eng)
par_grid
In [9]:
# Get selected pars
par_df = par_grid.get_selected_df()
par_df
Out[9]:
In [10]:
# Get data
wc_df, dup_df = nivapy.da.select_resa_water_chemistry(stn_df,
par_df,
'1970-01-01',
'2019-01-01',
eng,
lod_flags=False,
drop_dups=True)
wc_df.head()
Out[10]:
In [11]:
# How many stations have pH data
len(wc_df['station_code'].unique())
Out[11]:
In [12]:
# Which stations do not have pH data?
all_stns = set(stn_df['station_code'].unique())
no_ph = list(all_stns - set(wc_df['station_code'].unique()))
no_ph_stns = stn_df.query('station_code in @no_ph').reset_index()
print(len(no_ph_stns))
no_ph_stns
Out[12]:
In [13]:
# What data do these stations have?
par_grid2 = nivapy.da.select_resa_station_parameters(no_ph_stns,
'1970-01-01',
'2019-01-01',
eng)
par_grid2
In [14]:
# Most recent datab
for idx, row in prj_df.iterrows():
# Get stations
cnt_stns = nivapy.da.select_resa_project_stations([row['project_id'],], eng)
# Get pH data
wc, dups = nivapy.da.select_resa_water_chemistry(cnt_stns,
[1,], # pH
'1970-01-01',
'2019-01-01',
eng,
lod_flags=False,
drop_dups=True)
# Print results
print(row['project_name'], '\t', len(cnt_stns), '\t', wc['sample_date'].max())
These results are largely as expected:
US. Complete up to 2012. John sent an entirely new dataset for the 2018 trends work, which can be used to replace the existing data series for the "core" stations as well
Norway. Transferred automatically
Canada. Partly processed for the trends work
UK. Partly processed for the trends work
Finland. Jussi sent an entirely new dataset (with changes suggested to site selections) in 2018
Sweden. Update using data collected via API. Also need to modify site selections
Belarus. Data up to 2010 are already in the database. Completed templates are available for 2012 to 2014 (2011 data are missing). No response to calls for data since 2014
Czech Republic. 2016 data needs to be uploaded
Italy. Data submitted during 2017 and 2018 need adding. Several new sites need creating
Poland. Recent data submissions seem very complex! Need to check through e-mails
Switzerland. Templates need combining and adding
Latvia. Data for 2016 and 2017 need combining and tidying
Estonia. No data for 2012 or 2014. Data from 2013, 2015, 2016 and 2017 needs merging and adding
Ireland. An entirely new set of stations and codes have been proposed. Some overlap with old stations, but need checking
Montenegro. Data from 2006 to 2009 are available in Excel. Need transferring to template and adding to database. Nothing since 2009
Armenia. Data from 2004 to 2008 are in Excel. Nothing since and no replies to annual Calls for Data
Germany. Excel templates cover 2015 to 2017. Nothing for 2013 or 2014? Some site codes have changes and some are no longer monitored. Also some old errors to fix - see e-mails?
Austria. Data up to 2012 are in Excel. No data since then
Moldova. Data from 2014 to 2017 in Excel. No project or stations in database
Netherlands. Excel data supplied in 2016. No idea what to make of these spreadsheets! No stations or project in RESA
Russia. Data provided from 2009 to 2014 in Excel. No stations or projects in database
Slovakia. Not currently in the "core" ICPW project, but data supplied by Jiri as part of the trends work. Fits well with Polish data