

In [1]:

    
%matplotlib inline
import nivapy3 as nivapy
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sn

plt.style.use('ggplot')



In [2]:

    
# Connect to NIVABASE
eng = nivapy.da.connect()









    



Connection successful.

ICPW: extract all time series data for TOC trends 1990 to 2016

1. Select project



In [3]:

    
# Query projects
prj_grid = nivapy.da.select_resa_projects(eng)
prj_grid









    



583 projects in the RESA database.



In [4]:

    
# Get project
prj_df = prj_grid.get_selected_df()
prj_df









    Out[4]:







  
    
      
      project_id
      project_number
      project_name
      contact_person
      project_description
    
  
  
    
      580
      4390
      None
      ICPW_TOCTRENDS_2018
      None
      None

2. Select stations



In [5]:

    
# Get project stations
stn_df = nivapy.da.select_resa_project_stations(prj_df, eng)
print(len(stn_df), 'stations in project.')
stn_df.head()









    



432 stations in project.






    Out[5]:







  
    
      
      station_id
      station_code
      station_name
      latitude
      longitude
      altitude
    
  
  
    
      0
      38323
      Tr18_BA-01
      Batizovské pleso
      49.15203
      20.12988
      1884
    
    
      1
      38085
      Tr18_CA01
      Ontario, Algoma Region, Batchawana Lake
      47.06000
      -84.39300
      497
    
    
      2
      38086
      Tr18_CA02
      Ontario, Algoma Region, Wishart Lake
      47.04100
      -84.40200
      388
    
    
      3
      38087
      Tr18_CA03
      Ontario, Algoma Region, Little Turkey Lake
      47.04100
      -84.40600
      375
    
    
      4
      38088
      Tr18_CA04
      Ontario, Algoma Region, Turkey Lake
      47.05000
      -84.40800
      372



In [6]:

    
# Map of stations
nivapy.spatial.quickmap(stn_df, 
                        popup='station_code',
                        cluster=True)









    Out[6]:

3. Get parameters



In [7]:

    
# Get available pars
st_dt = '1990-01-01'
end_dt = '2016-12-31'
par_grid = nivapy.da.select_resa_station_parameters(stn_df,
                                                    st_dt,
                                                    end_dt,
                                                    eng)
par_grid









    



34 parameters available for the selected stations and dates.



In [11]:

    
# Select all pars
par_df = par_grid.get_selected_df()
print(len(par_df), 'parameters selected.')
par_df.head()









    



33 parameters selected.






    Out[11]:







  
    
      
      parameter_id
      parameter_name
      unit
    
  
  
    
      1
      50
      Al
      µg/l
    
    
      2
      223
      As
      µg/l
    
    
      3
      959
      COLOUR
      None
    
    
      4
      11
      Ca
      mg/l
    
    
      5
      15
      Cd
      µg/l

4. Get water chemsitry



In [12]:

    
# Get chem
wc_df, dup_df = nivapy.da.select_resa_water_chemistry(stn_df,
                                                      par_df,
                                                      st_dt,
                                                      end_dt,
                                                      eng,
                                                      drop_dups=True,
                                                      lod_flags=False)
wc_df.head()









    Out[12]:







  
    
      
      station_id
      station_code
      station_name
      sample_date
      depth1
      depth2
      ALK-E_µEq/l
      Al_µg/l
      As_µg/l
      COLOUR_
      ...
      Pb_µg/l
      Qs_m3/s
      SO4_mg/l
      SiO2_mg SiO2/l
      TOC_mg C/l
      TOTN_µg/l N
      TOTP_µg/l P
      Temp_oC
      Zn_µg/l
      pH_
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    
  
  
    
      0
      38085
      Tr18_CA01
      Ontario, Algoma Region, Batchawana Lake
      1990-01-03
      0
      0
      50.5
      86.6
      NaN
      NaN
      ...
      NaN
      NaN
      5.63
      2.01
      4.17
      352.0
      6.1
      NaN
      8.2
      6.95
    
    
      1
      38085
      Tr18_CA01
      Ontario, Algoma Region, Batchawana Lake
      1990-01-09
      0
      0
      50.0
      84.0
      NaN
      NaN
      ...
      NaN
      NaN
      5.53
      2.01
      3.94
      598.0
      5.7
      NaN
      8.2
      6.05
    
    
      2
      38085
      Tr18_CA01
      Ontario, Algoma Region, Batchawana Lake
      1990-01-15
      0
      0
      50.9
      81.4
      NaN
      NaN
      ...
      NaN
      NaN
      5.47
      2.06
      3.69
      530.0
      5.8
      NaN
      7.3
      6.07
    
    
      3
      38085
      Tr18_CA01
      Ontario, Algoma Region, Batchawana Lake
      1990-01-24
      0
      0
      56.6
      76.8
      NaN
      NaN
      ...
      NaN
      NaN
      5.34
      2.05
      3.71
      493.0
      4.7
      NaN
      8.6
      5.93
    
    
      4
      38085
      Tr18_CA01
      Ontario, Algoma Region, Batchawana Lake
      1990-01-31
      0
      0
      59.9
      124.2
      NaN
      NaN
      ...
      NaN
      NaN
      5.64
      2.10
      3.85
      815.0
      8.9
      NaN
      8.9
      5.97
    
  

5 rows × 39 columns



In [13]:

    
# Save output
out_csv = r'../../update_autumn_2018/results/all_chem_1990-2016.csv'
wc_df.to_csv(out_csv, index=False, encoding='utf-8')

	station_id	station_code	station_name	latitude	longitude	altitude
0	38323	Tr18_BA-01	Batizovské pleso	49.15203	20.12988	1884
1	38085	Tr18_CA01	Ontario, Algoma Region, Batchawana Lake	47.06000	-84.39300	497
2	38086	Tr18_CA02	Ontario, Algoma Region, Wishart Lake	47.04100	-84.40200	388
3	38087	Tr18_CA03	Ontario, Algoma Region, Little Turkey Lake	47.04100	-84.40600	375
4	38088	Tr18_CA04	Ontario, Algoma Region, Turkey Lake	47.05000	-84.40800	372