Site-Specific Time Series Data

Access Token. Please get in touch with the WaDE Team at WaDE_WSWC@hotmail.com to get an API access token. The token protects the API from bots and attacks. The WaDE team plans to support a personalized token for each user as part of the WesDAAT user management system. We apologize for the inconvenience.

Retrieves historical time series data for two types of data in WaDE for a site by passing in the appropriate options; you can search for available inventory in the system.

Reservoirs and Observation Site Time Series Water Data Reservoir and Observation Site data refer to the historic water flows or volumes by gage stations that States operate year-round or seasonally. This data is separate from streamflow information gathered through federal agencies such as USGS or the U.S. Bureau of Reclamation. Knowing river and reservoir status can help with long-term planning and flood forecasting modeling. Two relative measurements often used are discharge and gage height. Discharge is the rate at which a volume of water passes by a particular location. Gage height is a measurement of the distance between the water's surface above the stream gage's datum reference point (i.e., a unique reference point used to accommodate for changes in a stream channel over time). Reservoirs and Observation Site Water Recordings demo: https://waterdataexchangewswc.shinyapps.io/SiteSpecificReservoirAndObservationSiteDemo/
Public Supply Time Series Data - (Water Use) Refers to the reported deliveries to urban water service areas, known as the place of use (i.e., farmstead, municipal city boundary, residential use, etc.), along with the water source intake locations, known as point of diversion (i.e., river, canal, groundwater, etc.). Put another way, water is withdrawn from site-specific locations and then applied (i.e., depleted) at a different location. Site-Specific Public Supply Water use demo: https://waterdataexchangewswc.shinyapps.io/AggregatedBudgetWaterUseDemo/

Here is the API endpoint and its parameters

Click on the arrow next to the "response" block n the link below to see the API schema and an example response.

retrieves time series data for given site and specific variable for a site

By passing in the appropriate options, you can search for available inventory in the system

GEThttps://wade-api.azure-api.net/v1/SiteVariableAmounts

Query parameters

Response

search results matching criteria

Body

TotalSiteVariableAmountsCountinteger

Organizationsarray of SiteVariableAmountsOrganization (object)

Request

Response

Example Python code for using the API

# Needed Libararies
!pip install numpy
!pip install pandas
!pip install openpyxl
import openpyxl
import os
import numpy as np
import pandas as pd
import requests
import json

pd.set_option('display.max_columns', 999)  # How to display all columns of a Pandas DataFrame in Jupyter Notebook
print ("Libararies have been imported")

# Create output dataframes to store each dictionary section of the API return.
Organizations = pd.DataFrame()
WaterSources = pd.DataFrame()
VariableSpecifics = pd.DataFrame()
Methods = pd.DataFrame()
BeneficialUses = pd.DataFrame()
SiteVariableAmounts = pd.DataFrame()
Sites = pd.DataFrame()
print("done")

# Pre-set base elements of API call for easy construction.
# Save as strings.
# uat connection

# base API URL.
base_url_API = "https://wade-api.azure-api.net/v1/SiteVariableAmounts?" 

# set start & end dates of interest in MM/DD/YYYY format.
param1 = "StartDate=01/01/2000&EndDate=01/01/2012"

# limit search to state of interest, use abbreviation.
param2 = "State=UT"

# security API Key.
key = "key=38f422d1ada34907a91aff4532fa4669"

print("done")

# loop over the API calls through pages (index) one-at-a-time. 

StartIndex = 0  # will do bounds of 1000
loopCounter = 0  # counter for the number of loops we want


# The loop
for loopCounter in range (30):

    StartIndex_param = "StartIndex=" + str(StartIndex)
    print (StartIndex_param)
    
    # combine the API parameters together
    callString = base_url_API + param1 + "&" + param2 + "&" + StartIndex_param + "&" + key
    print (callString)
    
    # Call the API
    # check if API has a response
    try: 
        # The get
        response_dict = requests.get(callString).json()
        
        # Indexing and list slicing to append to individual tables.
        # Organizations Data       
        o_json_data = response_dict['Organizations'][0]
        o_tempDF = pd.json_normalize(o_json_data)
        Organizations = pd.DataFrame(o_tempDF, columns=['OrganizationName',
                                                        'OrganizationPurview',
                                                        'OrganizationWebsite',
                                                        'OrganizationState',
                                                        'OrganizationContactEmail',
                                                        'OrganizationPhoneNumber',
                                                        'OrganizationContactName',
                                                        'OrganizationContactEmail'], index=[0])

        # WaterSource Data
        ws_json_data = response_dict['Organizations'][0]['WaterSources']
        ws_tempDF = pd.json_normalize(ws_json_data)
        WaterSources = WaterSources.append(ws_tempDF)
        
        # VariableSpecifics Data
        v_json_data = response_dict['Organizations'][0]['VariableSpecifics']
        v_tempDF = pd.json_normalize(v_json_data)
        VariableSpecifics = VariableSpecifics.append(v_tempDF)
         
        # Methods Data
        m_json_data = response_dict['Organizations'][0]['Methods']
        m_tempDF = pd.json_normalize(m_json_data)
        Methods = Methods.append(m_tempDF)
        
        # BeneficialUses Data
        bu_json_data = response_dict['Organizations'][0]['BeneficialUses']
        bu_tempDF = pd.json_normalize(bu_json_data)
        BeneficialUses = BeneficialUses.append(bu_tempDF)
        
        # SiteVariableAmounts Data
        ssa_json_data = response_dict['Organizations'][0]['SiteVariableAmounts']
        ssa_tempDF = pd.json_normalize(ssa_json_data)
        ssa_tempDF['StartIndex'] = str(StartIndex) #tracking StartIndex used
        SiteVariableAmounts = SiteVariableAmounts.append(ssa_tempDF)
        
        # Sites Data
        s_json_data = response_dict['Organizations'][0]['Sites']
        s_tempDF = pd.json_normalize(s_json_data)
        Sites = Sites.append(s_tempDF)
          
    except:
        print("StartIndex_param has no data")
        
    
    StartIndex = StartIndex + 1000
    
    print("------------")
    
WaterSources = WaterSources.drop_duplicates().sort_values(by=['WaterSourceUUID']).reset_index(drop=True)
VariableSpecifics = VariableSpecifics.drop_duplicates().reset_index(drop=True)
Methods = Methods.drop_duplicates().sort_values(by=['MethodUUID']).reset_index(drop=True)
BeneficialUses = BeneficialUses.drop_duplicates().sort_values(by=['Term']).reset_index(drop=True)
Sites = Sites.drop_duplicates(subset=['SiteUUID']).sort_values(by=['SiteUUID']).reset_index(drop=True)

print("done")

Creating easy to ready table for related POD sites per POU site from RelatedPODSites and RelatedPOUSites fields.
Create temp POU table, create temp POD table, then concatenate into single output table.

# Create new POU specific dataframe, populate with items from Sites dataframe
RelatedSitesPOU = pd.DataFrame()
RelatedSitesPOU['SourceSiteUUID'] = Sites['SiteUUID']
RelatedSitesPOU['SourcePODorPOU'] = Sites['PODorPOUSite']
RelatedSitesPOU['RelatedPODSites'] = Sites['RelatedPODSites']
RelatedSitesPOU = RelatedSitesPOU[RelatedSitesPOU['SourcePODorPOU'] == 'POU'].reset_index(drop=True)

try:
    # explode the entries in the RelatedPODSites column, reset index.
    RelatedSitesPOU = RelatedSitesPOU.assign(RelatedPODSites=RelatedSitesPOU['RelatedPODSites']).explode('RelatedPODSites').reset_index(drop=True)

    # fetch column RelatedPODSites as a Series, then return dataframe where the column labels are the keys of the dictionaries.
    RelatedSitesPOU = pd.concat([RelatedSitesPOU, RelatedSitesPOU["RelatedPODSites"].apply(pd.Series)], axis=1)

    # rename columns
    RelatedSitesPOU = RelatedSitesPOU.rename(columns={"SiteUUID": "RelatedSiteUUID"})

    # drop index & RelatedPODSites columns from dataframe (no longer needed).
    RelatedSitesPOU = RelatedSitesPOU.drop(columns=["RelatedPODSites", 0])
    
except:
    print("No POU sites for the returend SiteVariableAmounts records.")
    RelatedSitesPOU = pd.DataFrame()
    RelatedSitesPOU = RelatedSitesPOU.append(pd.Series(), ignore_index=True)


# drop null rows
RelatedSitesPOU = RelatedSitesPOU.dropna()

print(len(RelatedSitesPOU))
RelatedSitesPOU.head(1)

# Create new POD specific dataframe, populate with items from Sites dataframe
RelatedSitesPOD = pd.DataFrame()
RelatedSitesPOD['SourceSiteUUID'] = Sites['SiteUUID']
RelatedSitesPOD['SourcePODorPOU'] = Sites['PODorPOUSite']
RelatedSitesPOD['RelatedPOUSites'] = Sites['RelatedPOUSites']
RelatedSitesPOD = RelatedSitesPOD[RelatedSitesPOD['SourcePODorPOU'] == 'POD'].reset_index(drop=True)

try: 
    # explode the entries in the RelatedPOUSites column, reset index.
    RelatedSitesPOD = RelatedSitesPOD.assign(RelatedPOUSites=RelatedSitesPOD['RelatedPOUSites']).explode('RelatedPOUSites').reset_index(drop=True)

    # fetch column RelatedPOUSites as a Series, then return dataframe where the column labels are the keys of the dictionaries.
    RelatedSitesPOD = pd.concat([RelatedSitesPOD, RelatedSitesPOD["RelatedPOUSites"].apply(pd.Series)], axis=1)

    # rename columns
    RelatedSitesPOD = RelatedSitesPOD.rename(columns={"SiteUUID": "RelatedSiteUUID"})

    # drop index & RelatedPOUSites columns from dataframe (no longer needed).
    RelatedSitesPOD = RelatedSitesPOD.drop(columns=["RelatedPOUSites", 0])
    
except:
    print("No POD sites for the returend SiteVariableAmounts records.")
    RelatedSitesPOD = pd.DataFrame()
    RelatedSitesPOD = RelatedSitesPOD.append(pd.Series(), ignore_index=True)
    

# drop null rows
RelatedSitesPOD = RelatedSitesPOD.dropna()

print(len(RelatedSitesPOD))
RelatedSitesPOD.head(1)

# Concatenate POU with POD data
try:
    frames = [RelatedSitesPOU, RelatedSitesPOD]
    RelatedSites = pd.concat(frames).reset_index(drop=True)
    RelatedSites = RelatedSites.sort_values(by=['SourcePODorPOU', 'SourceSiteUUID', 'RelatedSiteUUID', 'StartDate', 'EndDate'])
    print(len(RelatedSites))
    RelatedSites.head(1)
except:
    print("No POD to POU relations in the data.")
    RelatedSites = pd.DataFrame()

with pd.ExcelWriter('utssps_APITest_StartIndex.xlsx') as writer:
    Organizations.to_excel(writer, sheet_name='Organizations')
    WaterSources.to_excel(writer, sheet_name='WaterSources')
    VariableSpecifics.to_excel(writer, sheet_name='VariableSpecifics')
    Methods.to_excel(writer, sheet_name='Methods')
    BeneficialUses.to_excel(writer, sheet_name='BeneficialUses')
    SiteVariableAmounts.to_excel(writer, sheet_name='SiteVariableAmounts')
    Sites.to_excel(writer, sheet_name='Sites')
    RelatedSites.to_excel(writer, sheet_name='RelatedSites')

print("done")

PreviousWater Rights Data API End-point NextAggregate Water Budget Time Series Data

Last updated 3 months ago