Fetching data from the opendata.swiss API: A short tutorial in Python
opendata.swiss is the Swiss authorities’ portal for open data. Currently, 65 governmental organizations (many federal agencies, but also cantonal agencies, SBB and Post) provide access to 7,057 datasets. The portal offers an easily searchable catalogue of available datasets. Manually downloading the datasets can be cumbersome and the retrieval of data through the API can save time.
In this Jupyter Notebook we will retrieve data from open data portal "opendata.swiss". The portal is based on the open source project CKAN. CKAN stands for Comprehensive Knowledge Archive Network. It provides an extensive API for the metadata of the open data catalogue. This means that the information about the datasets can be retrieved from CKAN, but the data itself will have to be downloaded from the servers of the contributors ("opendata.swiss" in this cases).
In this tutorial we will first take a look at GDP of Basel and then at the population of Switzerland using Python 3. Let's start with importing some packages we will use for this exercise.
import pprint
import requests # 2.18.4
import json # 2.0.9
import pandas as pd # 0.23.0
Like mentioned, the CKAN API functions as a catalog for datasets. We need to define the URL for "opendata.swiss".
# Package list of the Swiss open data portal
packages = 'https://opendata.swiss/api/3/action/package_list'
Let's get a list of all the datasets (called packages in CKAN) listed by "opendata.swiss".
# Make the HTTP request
response = requests.get(packages)
# Use the json module to load CKAN's response into a dictionary
response_dict = json.loads(response.content)
# Check the contents of the response
assert response_dict['success'] is True # make sure if response is OK
result = response_dict['result'] # extract all the packages from the response
pprint.pprint(result) # pretty print the list to the screen
This is quite an extensive list. For this exercise we will take one from this list, called "bruttoinlandprodukt". Other examples could be: 'bevolkerung', 'elektroautos', or 'bevolkerungsdaten-im-zeitvergleich'.
# Specify the package you are interested in:
package = 'bruttoinlandprodukt'
Now let's download the package/dataset information. We need to take a few steps:
# Base url for package information. This is always the same.
base_url = 'https://opendata.swiss/api/3/action/package_show?id='
# Construct the url for the package of interest
package_information_url = base_url + package
# Make the HTTP request
package_information = requests.get(package_information_url)
# Use the json module to load CKAN's response into a dictionary
package_dict = json.loads(package_information.content)
# Check the contents of the response.
assert package_dict['success'] is True # again make sure if response is OK
package_dict = package_dict['result'] # we only need the 'result' part from the dictionary
pprint.pprint(package_dict) # pretty print the package information to screen
Did you walk through the information above? Is this indeed the dataset you are interested in? In the description we can see that it is the GDP (or Bruttoinlandprodukt in German) of the canton of Basel-Stadt. This is important as it might be mixed up with the GDP of the country or an other canton or region. If yes, then you need to download the dataset. It is also important to know the format of the dataset, for next steps. This information is also listed in the package information above.
# Get the url for the data from the dictionary
data_url = package_dict['resources'][0]['url']
print('Data url: ' + data_url)
# Print the data format
data_format = package_dict['resources'][0]['format']
print('Data format: ' + data_format)
Notice that this particular dataset is hosted at GitHub. When downloading from GitHub, it is better to request the raw data. We need to rewrite the URL a little bit to get there.
# If data is hosted at GitHub, always download the raw data
if data_url.startswith('https://github.com/'):
data_url = data_url.replace('https://github.com/', 'https://raw.githubusercontent.com/')
data_url = data_url.replace('tree/', '')
print('Data url: ' + data_url)
Feel free to take a look at the URL's above. It is good to take a sneak peak so you know what the data will look like. The dataset can come in different formats, so let's specify which ones we are willing to accept and load them into a Pandas DataFrame.
# List of formats we work with in this excercise
csv = ['comma-separated-values', 'CSV', 'csv']
tsv = ['tab-separated-values', 'TSV', 'tsv']
xls = ['XLS']
# Download the data to a Pandas DataFrame. Use seperate function calls, depending on the format of the dataset.
if any(s in data_format for s in csv): # pd.read_csv()
df = pd.read_csv(data_url)
elif any(s in data_format for s in tsv): # pd.read_csv() and specify the delimiter
df = pd.read_csv(data_url, sep='\t')
elif any(s in data_format for s in xls): # pd.read_excel()
df = pd.read_excel(data_url)
else:
print('Sorry, the data format is not supported for this exercise')
# Print the first rows to the screen to inspect the dataset
df.head(5)
As you can see, we need to make a few adjustments before we can continue. It is best to clean up the dataset before you start doing your analysis.
# Remove the column 'DateTime', because it is empty
df.drop('DateTime', axis=1, inplace=True)
# Make 'Jahr' the index
df.set_index('Jahr', inplace=True)
That's it! Now let's visualize the data with Pandas built-in plot functionality, which is based on 'matplotlib'. Voilà, here we see the trend of the GDP of Basel since 1980.
# Use IPython's "magic" in Jupyter Notebook to directly show the plot on the screen.
%matplotlib inline
df.plot()
Are you interested in accessing open data in a structured from through an API?
Check out our blog post on Nova-DB. The API offers free API access to many open and public datasets in a nicely structured form.
For Advanced Users¶
For advanced users only: download the dataset called "bevolkerungsdaten-im-zeitvergleich" and make a chart of the Swiss population over time. Please be aware this is an Excel file and you need to do a lot of cleaning to get to the final result. You probably need to print the DataFrame to the screen after every step to inspect if the operation was successful and what needs to be done next.
# Check the package information, as done above
package_information = requests.get('https://opendata.swiss/api/3/action/package_show?id=bevolkerungsdaten-im-zeitvergleich')
package_dict = json.loads(package_information.content)
package_dict = package_dict['result']
data_url = package_dict['resources'][0]['url']
# Download the data into a Pandas DataFrame
df = pd.read_excel(data_url)
# Transpose the DataFrame, to get the years in a column
df = df.transpose()
# Drop the first (useless) line from the DataFrame. Use 'inplace=True' so it amends the existing DataFrame (not a copy)
df.drop(df.index[0], inplace=True)
# Keep only the columns of interest: year & residents
df = df.iloc[:, [2,6]]
# Rename the columns
df.columns = ['year', 'residents']
# Reset the index. Use 'drop=True' so no duplicate is made of the old index
df = df.reset_index(drop=True)
# Fix the year "2010" at row index 20
df.year[20] = 2010
# Convert all the years to integers so they can be transformed to DateTime later
df.year = df.year.apply(int)
# Convert the years to DateTime format
df.year = pd.to_datetime(df.year, format='%Y')
# Make the years the index of the DataFrame
df.set_index('year', inplace=True)
# Finally print the DataFrame to screen
print(df)
# Last but not least: make a chart!
df.plot()
