<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Explore "core" ICPW data
Prior to updating the "core" ICPW datasets in RESA, I need to get an overview of what's already in the database and what isn't.
Step1:  1. Query ICPW projects
The are 18 projects (one for each country) currently in RESA. We also have data for some countries that do not yet have a project defined (e.g. the Netherlands).
Step2:  2. Get station list
There are 262 stations currently associated with the projects in RESA.
Step3:  3. Get parameters
Get a list of parameters available at these stations. I assume that all data submissions to ICPW will report pH, so extracting pH data should be a good way to get an indication of which stations actually have data.
Step4:  4. Get chemistry data
Step5:  So, there are 262 stations within the "core" ICPW projects, but 24 of these have no data whatsoever associated with them (listed above).
5. Date for last sample by country
The code below gets the most recent pH sample in the database for each country.
<ASSISTANT_TASK:>
Python Code:
# Connect to db
eng = nivapy.da.connect()
Explanation: Explore "core" ICPW data
Prior to updating the "core" ICPW datasets in RESA, I need to get an overview of what's already in the database and what isn't.
End of explanation
# Query projects
prj_grid = nivapy.da.select_resa_projects(eng)
prj_grid
prj_df = prj_grid.get_selected_df()
print(len(prj_df))
prj_df
Explanation: 1. Query ICPW projects
The are 18 projects (one for each country) currently in RESA. We also have data for some countries that do not yet have a project defined (e.g. the Netherlands).
End of explanation
# Get stations
stn_df = nivapy.da.select_resa_project_stations(prj_df, eng)
print(len(stn_df))
stn_df.head()
# Map
nivapy.spatial.quickmap(stn_df, popup='station_code')
Explanation: 2. Get station list
There are 262 stations currently associated with the projects in RESA.
End of explanation
# Select parameters
par_grid = nivapy.da.select_resa_station_parameters(stn_df,
                                                    '1970-01-01',
                                                    '2019-01-01',
                                                    eng)
par_grid
# Get selected pars
par_df = par_grid.get_selected_df()
par_df
Explanation: 3. Get parameters
Get a list of parameters available at these stations. I assume that all data submissions to ICPW will report pH, so extracting pH data should be a good way to get an indication of which stations actually have data.
End of explanation
# 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()
# How many stations have pH data
len(wc_df['station_code'].unique())
# 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
# 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
Explanation: 4. Get chemistry data
End of explanation
# 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())
Explanation: So, there are 262 stations within the "core" ICPW projects, but 24 of these have no data whatsoever associated with them (listed above).
5. Date for last sample by country
The code below gets the most recent pH sample in the database for each country.
End of explanation
<END_TASK>