EU Open Data Portal API: A short guide
The EU Open Data Portal gives access to open data published by EU institutions, agencies
In this Jupyter
In this tutorial we will take a look at the unemployment numbers for Europe, 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
The EU Open Data Portal's website is: http://data.europa.eu/euodp/en/home. Please take a look to get a feel of what this portal has to offer.
You can actually take a look at all the datasets listed in the EU Open Data Portal: https://data.europa.eu/euodp/data/api/3/action/package_list.
As you can see, the majority of the datasets have names which consist of a random combination of letters and numbers. It does not really give us any useful insight into what's available. The CKAN API also offers another option, the tag list: https://data.europa.eu/euodp/data/api/3/action/tag_list.
The tag list gives us an idea of what kind of datasets are available. For this tutorial, we will check out what 'unemployment' numbers are available. We will use the CKAN's 'package_search' functionality, to get the related datasets.
# Topic of interest
query = 'unemployement'
# The base url for search queries
base_url = 'https://data.europa.eu/euodp/data/api/3/action/package_search?q='
# Limit the number of results (max 1,000)
limit = 20
# Build the HTTP request, with the 'base url', 'query' & 'limit'
response = requests.get(base_url + query + '&rows=' + str(limit))
# 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
search_result = response_dict['result'] # extract all the packages from the response
pprint.pprint(search_result) # pretty print the list to the screen
There is a total of 164 results ('count': 164) for the search term 'unemployment'. This is the same as when you search on the website of the data portal: http://data.europa.eu/euodp/en/data/dataset?q=unemployment&ext_boolean=all
Even though we "pretty print" our search results in this Jupyter Notebook, it is still not very readable. Let's build a small loop that prints the 'title' of the datasets. Please note we have limited our search query to 20 results, so with index 0 to 19.
for i, x in enumerate(search_result['results']):
print(i, x['title'])
You can find some more details about the datasets in the JSON response we printed earlier. We will continue with the 'Total unemployment rate' dataset, listed at index 5. We will pretty print the details about this particular dataset again.
pprint.pprint(search_result['results'][5])
Let's take a look first at the web page of the dataset at the Open Data Portal. The URL is:
search_result['results'][5]['url']
There are various ways to look at the data on the portal. You can see it in table format, in a graph or on a map. We are more interested in the raw data itself. The number of resources (data formats) available is:
len(search_result['results'][5]['resources'])
Browsing through the dataset details above, it seems there is a "tap separated (TSV)" / "unzipped" version of the data amongst the 7 resources. Its index location is 1.
search_result['results'][5]['resources'][1]
The URL to the raw data is:
search_result['results'][5]['resources'][1]['url']
For readability lateron, we can put the URL in a variable called 'unzipped'
unzipped = search_result['results'][5]['resources'][1]['url']
# http://ec.europa.eu/eurostat/estat-navtree-portlet-prod/BulkDownloadListing?file=data/tps00203.tsv.gz&unzip=true
And we can load the raw data into a Pandas DataFrame:
df = pd.read_csv(unzipped, sep='\t') # use separator '\t' for tab-separated values
# Print the first 5 rows of the DataFrame for inspection
df.head()
The column names are numbers and the first column has a weird header. It is best to inspect the column headers a bit more before we continue.
print(df.columns)
If you look closely, all the column headers with years in them contain spaces at the end. To remove them:
df.columns = df.columns.str.strip()
Also, the data is not entirely ready for use yet and needs some
# Create a new column at the end of the DataFrame called 'country', with the values of the first column
df['country'] = df[df.columns[0]]
# Use only the last two letters of the value, to filter out the country codes
df['country'] = df['country'].str[-2:]
# Inspect the DataFrame. What are the types of the columns? Float or integers we can use directly? Probably not...
df.info()
As we will look at the year '2017' only, we would need the values of this column to be numeric. We can transform them while erasing the errors from the DataFrame (NaN's or non-numeric).
df = df[pd.to_numeric(df['2017'], errors='coerce').notnull()]
df['2017'] = df['2017'].astype(float)
# Check the first rows of the DataFrame again
df.head()
Finally, we will plot the values for the year '2017' for the various countries. For readability, let's create a new DataFrame called 'last_year' with only the columns '2017' and 'country'.
# Use only the last two columns of the DataFrame
last_year = df[df.columns[-2:]]
# Check again the first 5 rows
last_year.head()
The data is ready to be
# For charting, use IPython's "magic" in Jupyter Notebook to directly show the plot on the screen.
%matplotlib inline
# Plot the values in a chart, with ascending values
last_year.sort_values('2017', ascending=True).plot( x='country', y='2017', kind='bar')
<matplotlib.axes._subplots.AxesSubplot at 0x19ffefce2e8>
Raw data¶
It is quite a hassle to search through all the datasets to find raw data. Below is a small loop which only prints the datasets which have a link to the raw data available.
for x in search_result['results']: # Loop through every dataset in the search results
for i in x['resources']: # Check for every data set if the word 'unzip' is in the URL
if 'unzip' in i['url']: # If so, print the 'title' and the URL to the screen
print(x['title'])
print(i['url'])
About the author:
Joris H., Python & open source enthusiast. Entrepreneur @ PythonSherpa - https://www.pythonsherpa.com