← Back to Data Exploration with rakoon-ds
Practical Work 3

Countries from a REST API, holidays from a feed, joined without lying

Import from a URL: paginated XML with XPath, JSON from a REST API, and the one number that tells you a join went wrong

Duration 75 min
Level Intermediate
Session Session 3
Mission dep-3

Objectives

By the end of this session you will be able to:

  • Import a dataset from a URL instead of a file, and page through a REST API
  • Map an XML feed with XPath selectors, including attributes
  • Map a JSON array, and know when the automatic mapping is enough
  • Join two sources on a key and check the row count before and after
  • Recognise a join that multiplies rows, and fix it
  • Refresh a source and re-run the whole chain

Prerequisites

  • A rakoon-ds account on https://rakoon-ds.apps.way-up.io (free, browser only, nothing to install), and the group join code your instructor gives you
  • A recent Chrome, Edge or Firefox. Computation runs either in your own tab (browser engine) or on the server; the header pill tells you which
  • The mission for this session, assigned to your group. Open a project, then the Mission button in the workshop header: the panel opens next to Report. Click Check after each step
  • Practical works 1 and 2 finished
  • Nothing to install: the server fetches the URLs, not your browser
  • Transformation, chart and algorithm names come from the rakoon-ds registry and are served in French even when the interface is in English. Every step below gives you the French label you will click and, in code font, the registry key the mission checks against.

Data

Two public APIs, no key, no quota to worry about. Both were checked end to end from the studio on 2026-09-09; the row counts below are what came back.

SourceURLFormatWhat you get
World Bank, country listhttps://api.worldbank.org/v2/countryXML, paginated295 rows over 6 pages: code, name, region, income level, capital, latitude, longitude
Nager.Date, supported countrieshttps://date.nager.at/api/v3/AvailableCountriesJSON array204 rows: two-letter code and name
Nager.Date, public holidayshttps://date.nager.at/api/v3/PublicHolidays/2024/FRJSON array11 rows for France in 2024; change the code for another country

rakoon-ds refuses private and loopback addresses, credentials inside a URL, and anything that is not http or https. That is not fussiness: a server that fetches a URL chosen by a user is a request forgery waiting to happen.

Timing

The steps below add up to the announced duration. If you fall behind, Step 1 to Step 3 are the ones that must be finished.

#What you doTime
Step 1Paginated XML: the World Bank country list18 min
Step 2JSON: the list of countries the holiday API knows10 min
Step 3Join, and count the rows12 min
Step 4Holidays for two countries, stacked15 min
Step 5Aggregate, chart, and pin12 min
Step 6Refresh, re-run, and write it down8 min
Total75 min

Instructions

Step 1: Paginated XML: the World Bank country list18 min

  1. New project PW3 Countries.
  2. Add a dataset → Import from an API or online feed. The mapping dialog opens on its API / online feed tab.
  3. URL: https://api.worldbank.org/v2/country, method GET. Add one query parameter: per_page = 50.
  4. Click Analyze. It downloads one page and shows you what arrived: HTTP status, content type, size, detected format.

The analysis should report kind: xml, row selector .//country, 50 rows, and ten proposed columns. Look at the first one: its path is @id, not id, because the country code is an attribute of <country> and not a child element.

<countries page="1" pages="6" per_page="50" total="295">
  <country id="ABW">
    <iso2Code>AW</iso2Code>
    <name>Aruba</name>
    <region id="LCN" iso2code="ZJ">Latin America &amp; Caribbean</region>
    <capitalCity>Oranjestad</capitalCity>
    <longitude>-70.0167</longitude>
    <latitude>12.5167</latitude>
  </country>
</countries>
  1. Keep these columns: id, iso2Code, name, region, incomeLevel, capitalCity, longitude, latitude. Remove adminregion and lendingType.
  2. Now set pagination: kind page number, parameter page, start 1, max pages 10.
  3. The preview refreshes on its own one second after your last change. It should say 295 rows, 6 pages, and give a stop reason: vide (the seventh page came back empty).
  4. Name it worldbank-countries and click Import.

The XML from this API has no namespaces, so the paths stay readable. Feeds that do have them (Sandre, Atom, central bank feeds) would normally force you to write .//{http://some/namespace}Element; rakoon-ds strips namespaces once on arrival so the selector stays .//Element.

Step 2: JSON: the list of countries the holiday API knows10 min

  1. Add a dataset → Import from an API or online feed again.
  2. URL: https://date.nager.at/api/v3/AvailableCountries, no parameters, no pagination.
  3. Analyze: kind: json, row selector $ (the response is a plain array), 204 rows, two columns countryCode and name.
  4. Rename the second column to holidayApiName in the mapping panel: you are about to join with a table that already has a name.
  5. Name it nager-countries and Import.

Renaming at mapping time is not cosmetic. Two columns called name on either side of a join give you name and name.1, and three transformations later nobody knows which is which.

Step 3: Join, and count the rows12 min

Family Combiner, card Fusion (join) (join).

  1. Left dataset worldbank-countries, right dataset nager-countries.
  2. Join type left.
  3. Left key iso2Code, right key countryCode.
  4. Click Preview and read the note under the table.

Expected: rows_before: 295, rows_after: 295. A left join that keeps the row count is a left join that did what you meant.

Now break it on purpose, to see what a bad join looks like. Change the left key to region and the right dataset to worldbank-countries itself, then Preview:

rows_before: 295, rows_after: 15 459, rows_multiplied: 52.4, in red, before you apply anything. Each country matched all the countries of its region. A join on a non-unique key multiplies rows, and until this warning existed it did so in silence: on a past exam, 11 128 rows became 222 560 and nobody noticed.

  1. Put the correct keys back, name the result countries-with-holiday-api and apply.

Step 4: Holidays for two countries, stacked15 min

  1. Import https://date.nager.at/api/v3/PublicHolidays/2024/FR (JSON, selector $). Keep date, localName, name, countryCode, global. Name it holidays-2024-fr: 11 rows.
  2. Import the same URL with US instead of FR. Name it holidays-2024-us.
  3. Family Combiner, card Concaténation (concat): stack the two. Name it holidays-2024.
  4. On the result: family Dates, card Convertir en date (to_date) on date, then Décomposer une date/heure (date_parts) with month and weekday.

Two questions to answer from the data, not from memory:

  • How many public holidays does each of the two countries have in 2024, and in which months do they cluster?
  • The global column is false for some rows. What does a non-global holiday mean, and what would happen if you joined it to a dataset of events without also matching the region?

Step 5: Aggregate, chart, and pin12 min

  1. On holidays-2024: family Agréger, card Agréger (group by) (aggregate). Group by countryCode and date_month, aggregate name with count.
  2. Explore the result and build a Comptage / moyenne (barres) (bar) chart: X = month, colour = country. Pin it.
  3. On countries-with-holiday-api, build a Carte (géographique) (map) from latitude and longitude, coloured by incomeLevel. Pin it.

The map draws at most 2 000 points and does not currently say when it has sampled. With 295 countries you are far below that, but remember it the day you map 250 000 rows.

Step 6: Refresh, re-run, and write it down8 min

  1. Click the worldbank-countries node. Its panel has a Refresh button: it re-issues the request and replaces the content, keeping the same node and the same lineage.
  2. Then use Run all transformations at the top of the transformation panel. Everything downstream is recomputed.
  3. Open the Report panel and write the Données section: the two APIs, what one row is in each, the join key, the row count before and after, and one sentence on what you would do if the API changed its schema tomorrow.

An API key never goes in a query parameter: parameters are stored in clear because they are part of the URL. Put it in a header and tick confidential value: the value is then encrypted with the server key, bound to the project, and never returned by the API.

What you should have

  • Four imported sources: 295 countries (XML, 6 pages), 204 country codes (JSON), two holiday lists
  • One join checked at 295 rows in and 295 rows out, and one deliberate 52x explosion seen
  • One concatenation, one aggregation, two pinned charts
  • The Données section of the report written
  • The dep-3 mission at 5 / 5

Deliverables

  • Mission: dep-3 validated
  • Report: Données section, with the two pinned charts and their captions
  • The join table: for each of your two joins, the key, the type, and rows before / rows after

Bonus

  • Loop the holiday API over five more countries and concatenate all of them. At what point does doing it by hand stop being reasonable, and what would you want instead?
  • The World Bank list contains 295 "countries" while the UN recognises 193 member states. Find the aggregates (regions, income groups) hiding in the list and filter them out with filter_rows.
  • Try the two built-in examples of the import dialog (data.gouv.fr paginated JSON, and the French departments XML feed) and compare their pagination styles.
  • Read what happens when you ask for page 20 of an API that has 6 pages. The stop reason is part of the answer.

Resources