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.

water_full_url
'https://data.colorado.gov/resource/j5pc-4t32.json?station_status=Active&county=BOULDER'
data = requests.get(water_full_url)
type(data.json())
list

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.

data.json()[:2]
[{'station_name': 'BLOWER DITCH',
  'amount': '0.00',
  'station_status': 'Active',
  'county': 'BOULDER',
  'wd': '4',
  'dwr_abbrev': 'BLWDITCO',
  'data_source': 'Cooperative SDR Program of CDWR & NCWCD',
  'http_linkage': {'url': 'http://www.dwr.state.co.us/SurfaceWater/data/detail_graph.aspx?ID=BLWDITCO&MTYPE=DISCHRG'},
  'div': '1',
  'date_time': '2018-11-28T07:00:00',
  'usgs_station_id': 'BLWDITCO',
  'variable': 'DISCHRG',
  'location': {'latitude': '40.257844',
   'needs_recoding': False,
   'longitude': '-105.164397'},
  'station_type': 'Diversion'},
 {'station_name': 'BOULDER-LARIMER BYPASS NEAR BERTHOUD',
  'amount': '0.80',
  'station_status': 'Active',
  'county': 'BOULDER',
  'wd': '4',
  'dwr_abbrev': 'BOUBYPCO',
  'data_source': 'Co. Division of Water Resources',
  'http_linkage': {'url': 'http://www.dwr.state.co.us/SurfaceWater/data/detail_graph.aspx?ID=BOUBYPCO&MTYPE=DISCHRG'},
  'div': '1',
  'date_time': '2018-11-28T07:15:00',
  'stage': '0.15',
  'variable': 'DISCHRG',
  'location': {'latitude': '40.258726',
   'needs_recoding': False,
   'longitude': '-105.175817'},
  'station_type': 'Diversion'}]

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())
result.head()
amountcountydata_sourcedate_timedivdwr_abbrevhttp_linkage.urllocation.latitudelocation.longitudelocation.needs_recodingstagestation_namestation_statusstation_typeusgs_station_idvariablewd
00.00BOULDERCooperative SDR Program of CDWR & NCWCD2018-11-28T07:00:001BLWDITCOhttp://www.dwr.state.co.us/SurfaceWater/data/d...40.257844-105.164397FalseNaNBLOWER DITCHActiveDiversionBLWDITCODISCHRG4
10.80BOULDERCo. Division of Water Resources2018-11-28T07:15:001BOUBYPCOhttp://www.dwr.state.co.us/SurfaceWater/data/d...40.258726-105.175817False0.15BOULDER-LARIMER BYPASS NEAR BERTHOUDActiveDiversionNaNDISCHRG4
20.00BOULDERCo. Division of Water Resources2018-11-28T07:30:001BOULARCOhttp://www.dwr.state.co.us/SurfaceWater/data/d...40.258367-105.174957False-0.09BOULDER-LARIMER DITCH NEAR BERTHOUDActiveDiversionNaNDISCHRG4
30.00BOULDERCooperative SDR Program of CDWR & NCWCD2018-11-28T07:00:001CULDITCOhttp://www.dwr.state.co.us/SurfaceWater/data/d...40.260827-105.198567FalseNaNCULVER DITCHActiveDiversionCULDITCODISCHRG4
415.50BOULDERNorthern Colorado Water Conservancy District (...2018-11-28T07:00:001LITTH1COhttp://www.northernwater.org/WaterProjects/Eas...40.256276-105.209416False0.98LITTLE THOMPSON #1 DITCHActiveDiversionES1901DISCHRG4
type(result)
pandas.core.frame.DataFrame
result.columns
Index(['amount', 'county', 'data_source', 'date_time', 'div', 'dwr_abbrev',
       'http_linkage.url', 'location.latitude', 'location.longitude',
       'location.needs_recoding', 'stage', 'station_name', 'station_status',
       'station_type', 'usgs_station_id', 'variable', 'wd'],
      dtype='object')

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?

result['location.latitude'][0]
'40.257844'

You can convert the strings to type float as follows.

result['location.latitude'] = result['location.latitude'].astype(float)
result['location.latitude'][0]
40.257844
result['location.longitude'] = result['location.longitude'].astype(float)
result['location.longitude'][0]
-105.164397

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

result.shape
(50, 17)
result['location.longitude'].isna().any()
False
result['location.latitude'].isna().any()
False

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)
gdf.head()
amountcountydata_sourcedate_timedivdwr_abbrevhttp_linkage.urllocation.latitudelocation.longitudelocation.needs_recodingstagestation_namestation_statusstation_typeusgs_station_idvariablewdgeometry
00.00BOULDERCooperative SDR Program of CDWR & NCWCD2018-11-28T07:00:001BLWDITCOhttp://www.dwr.state.co.us/SurfaceWater/data/d...40.257844-105.164397FalseNaNBLOWER DITCHActiveDiversionBLWDITCODISCHRG4POINT (-105.164397 40.257844)
10.80BOULDERCo. Division of Water Resources2018-11-28T07:15:001BOUBYPCOhttp://www.dwr.state.co.us/SurfaceWater/data/d...40.258726-105.175817False0.15BOULDER-LARIMER BYPASS NEAR BERTHOUDActiveDiversionNaNDISCHRG4POINT (-105.175817 40.258726)
20.00BOULDERCo. Division of Water Resources2018-11-28T07:30:001BOULARCOhttp://www.dwr.state.co.us/SurfaceWater/data/d...40.258367-105.174957False-0.09BOULDER-LARIMER DITCH NEAR BERTHOUDActiveDiversionNaNDISCHRG4POINT (-105.174957 40.258367)
30.00BOULDERCooperative SDR Program of CDWR & NCWCD2018-11-28T07:00:001CULDITCOhttp://www.dwr.state.co.us/SurfaceWater/data/d...40.260827-105.198567FalseNaNCULVER DITCHActiveDiversionCULDITCODISCHRG4POINT (-105.198567 40.260827)
415.50BOULDERNorthern Colorado Water Conservancy District (...2018-11-28T07:00:001LITTH1COhttp://www.northernwater.org/WaterProjects/Eas...40.256276-105.209416False0.98LITTLE THOMPSON #1 DITCHActiveDiversionES1901DISCHRG4POINT (-105.209416 40.256276)

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')
folium.GeoJson(gdf).add_to(m)

m

Great - now you have a map!

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)

m

Leave a Comment