Lesson 4. Programmatically Accessing Geospatial Data Using APIs

Learning Objectives

After completing this tutorial, you will be able to:

  • Extract geospatial (x,y) coordinate information embedded within a JSON hierarchical data structure.
  • Convert data imported in JSON format into a Geopandas DataFrame.
  • Create a map of geospatial data.

What You Need

You will need a computer with internet access to complete this lesson.

In this lesson, you work with JSON data accessed via the Colorado information warehouse. The data will contain geospatial information nested within it that will allow us to create a map of the data.

Working with Geospatial Data

Check out the map Colorado DWR Current Surface Water Conditions map.

Remember from the previous lesson, APIs can be used for many different things. Web developers (people who program and create web sites and cool applications) can use APIs to create user friendly interfaces - like the map in the previous example that allows us to look at and interact with data. These APIs are similar to, if not the same as, the ones that you often use to access data in Python.

In this lesson, you will access the data used to create the map at the link above using Python.

import requests
import folium
import urllib
from pandas.io.json import json_normalize
import pandas as pd
import folium
from geopandas import GeoDataFrame
from shapely.geometry import Point
# Get URL
water_base_url = "https://data.colorado.gov/resource/j5pc-4t32.json?"
water_full_url = water_base_url + "station_status=Active" + "&county=BOULDER"

ATTENTION WINDOWS USERS: We have noticed a bug where on windows machines, sometimes the https URL doesn’t work. Instead try the same url as above but without the s - like this: water_base_url = "http://data.colorado.gov/resource/j5pc-4t32.json?" This change has resolved many issues on windows machines so give it a try if you are having problems with the API.

data = requests.get(water_full_url)

Remember that the JSON structure supports hierarchical data and can be NESTED. If you look at the structure of the .json file below, you can see that the location object, is nested with three sub objects:

  • latitude
  • longitude
  • needs_recoding

Since data.json() is a list you can print out just the first few items of the list to look at your data as a sanity check.

[{'station_name': 'ZWECK AND TURNER DITCH',
  'div': '1',
  'location': {'latitude': '40.185033',
   'needs_recoding': False,
   'longitude': '-105.185789'},
  'dwr_abbrev': 'ZWETURCO',
  'data_source': 'Cooperative Program of CDWR, NCWCD & SVLHWCD',
  'amount': '0.10',
  'station_type': 'Diversion',
  'wd': '5',
  'http_linkage': {'url': 'https://dwr.state.co.us/SurfaceWater/data/detail_graph.aspx?ID=ZWETURCO&MTYPE=DISCHRG'},
  'date_time': '2020-04-01T10:00:00.000',
  'county': 'BOULDER',
  'variable': 'DISCHRG',
  'stage': '0.04',
  'station_status': 'Active'},
 {'station_name': 'SAINT VRAIN CREEK AT LYONS, CO',
  'div': '1',
  'location': {'latitude': '40.220702',
   'needs_recoding': False,
   'longitude': '-105.26349'},
  'dwr_abbrev': 'SVCLYOCO',
  'data_source': 'Co. Division of Water Resources',
  'usgs_station_id': '06724000',
  'amount': '34.20',
  'station_type': 'Stream',
  'wd': '5',
  'http_linkage': {'url': 'https://dwr.state.co.us/SurfaceWater/data/detail_graph.aspx?ID=SVCLYOCO&MTYPE=DISCHRG'},
  'date_time': '2020-04-01T10:15:00.000',
  'county': 'BOULDER',
  'variable': 'DISCHRG',
  'stage': '2.95',
  'station_status': 'Active'}]

Convert JSON to Pandas DataFrame

Now that you have pulled down the data from the website, you have it in the JSON format. For the next step, you will use the json_normalize() function from the Pandas library to convert this data into a Pandas DataFrame.

This function helps organize and flatten data into a semi-structed table. To learn more, check out the documentation!

from pandas.io.json import json_normalize
result = json_normalize(data.json())
/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning: pandas.io.json.json_normalize is deprecated, use pandas.json_normalize instead
  """Entry point for launching an IPython kernel.
0ZWECK AND TURNER DITCH1ZWETURCOCooperative Program of CDWR, NCWCD & SVLHWCD0.10Diversion52020-04-01T10:00:00.000BOULDERDISCHRG0.04Active40.185033False-105.185789https://dwr.state.co.us/SurfaceWater/data/deta...NaNNaN
1SAINT VRAIN CREEK AT LYONS, CO1SVCLYOCOCo. Division of Water Resources34.20Stream52020-04-01T10:15:00.000BOULDERDISCHRG2.95Active40.220702False-105.26349https://dwr.state.co.us/SurfaceWater/data/deta...06724000NaN
2OLIGARCHY DITCH DIVERSION1OLIDITCOCooperative Program of CDWR, NCWCD & SVLHWCD2.76Diversion52020-04-01T08:45:00.000BOULDERDISCHRG0.19Active40.196422False-105.206592https://dwr.state.co.us/SurfaceWater/data/deta...NaNNaN
3PANAMA RESERVOIR OUTLET1PNMOUTCOCo. Division of Water Resources0.07Stream62020-04-01T10:45:00.000BOULDERDISCHRGNaNActive40.087583False-105.072502https://dwr.state.co.us/SurfaceWater/data/deta...NaNNaN
4CLOUGH AND TRUE DITCH1CLODITCOCooperative Program of CDWR, NCWCD & SVLHWCD0.00Diversion52020-04-01T10:00:00.000BOULDERDISCHRGNaNActive40.193758False-105.21039https://dwr.state.co.us/SurfaceWater/data/deta...NaNNaN
Index(['station_name', 'div', 'dwr_abbrev', 'data_source', 'amount',
       'station_type', 'wd', 'date_time', 'county', 'variable', 'stage',
       'station_status', 'location.latitude', 'location.needs_recoding',
       'location.longitude', 'http_linkage.url', 'usgs_station_id', 'flag'],

Data Cleaning for Visualization

Now you can clean up the data. Notice that your longitude and latitude values are stored as strings. Do you think can create a map if these values are stored as strings?


You can convert the strings to type float as follows.

result['location.latitude'] = result['location.latitude'].astype(float)
result['location.longitude'] = result['location.longitude'].astype(float)

Now that you have numeric values for mapping, make sure that are are no missing values.

(57, 18)

There are no nan values in this data. However, if there were, you could remove rows where a column has a nan value in a specific column with the following: result_nonan = result.dropna(subset=['location.longitude', 'location.latitude'])

Data Visualization

You will use the folium package to visualize the data. One approach you could take would be to convert your Pandas DataFrame to a Geopandas DataFrame for easy mapping.

geometry = [Point(xy) for xy in zip(result['location.longitude'], result['location.latitude'])]
crs = {'init': 'epsg:4326'}
gdf = GeoDataFrame(result, crs=crs, geometry=geometry)
0ZWECK AND TURNER DITCH1ZWETURCOCooperative Program of CDWR, NCWCD & SVLHWCD0.10Diversion52020-04-01T10:00:00.000BOULDERDISCHRG0.04Active40.185033False-105.185789https://dwr.state.co.us/SurfaceWater/data/deta...NaNNaNPOINT (-105.18579 40.18503)
1SAINT VRAIN CREEK AT LYONS, CO1SVCLYOCOCo. Division of Water Resources34.20Stream52020-04-01T10:15:00.000BOULDERDISCHRG2.95Active40.220702False-105.263490https://dwr.state.co.us/SurfaceWater/data/deta...06724000NaNPOINT (-105.26349 40.22070)
2OLIGARCHY DITCH DIVERSION1OLIDITCOCooperative Program of CDWR, NCWCD & SVLHWCD2.76Diversion52020-04-01T08:45:00.000BOULDERDISCHRG0.19Active40.196422False-105.206592https://dwr.state.co.us/SurfaceWater/data/deta...NaNNaNPOINT (-105.20659 40.19642)
3PANAMA RESERVOIR OUTLET1PNMOUTCOCo. Division of Water Resources0.07Stream62020-04-01T10:45:00.000BOULDERDISCHRGNaNActive40.087583False-105.072502https://dwr.state.co.us/SurfaceWater/data/deta...NaNNaNPOINT (-105.07250 40.08758)
4CLOUGH AND TRUE DITCH1CLODITCOCooperative Program of CDWR, NCWCD & SVLHWCD0.00Diversion52020-04-01T10:00:00.000BOULDERDISCHRGNaNActive40.193758False-105.210390https://dwr.state.co.us/SurfaceWater/data/deta...NaNNaNPOINT (-105.21039 40.19376)

Then, you can plot the data using the folium functions GeoJson() and add_to() to add the data from the Geopandas DataFrame to the map object.

m = folium.Map([40.01, -105.27], zoom_start= 10, tiles='cartodbpositron')


Great! You now have an interactive map in your notebook!

You can also cluster the markers, and add a popup to each marker, so you can give your viewers more information about station: such as its name and the amount of precipitation measured.

For this example below, you will work with the Pandas DataFrame you originally created from the JSON, instead of the Geopandas GeoDataFrame.

# Get the latitude and longitude from result as a list
locations = result[['location.latitude', 'location.longitude']]
coords = locations.values.tolist()
from folium.plugins import MarkerCluster

m = folium.Map([40.01, -105.27], zoom_start= 10, tiles='cartodbpositron')

marker_cluster = MarkerCluster().add_to(m)

for point in range(0, len(coords)):
    folium.Marker(location = coords[point], popup= 'Name: ' + result['station_name'][point] + ' ' + 'Precip: ' + str(result['amount'][point])).add_to(marker_cluster)


Leave a Comment