THINK FIRST·CODE LATER

← All labs

Flatten nested JSON from a bike-share API

Problem

A bike-share API (in the style of the GBFS standard) returns JSON like:

{"last_updated": "2026-03-02T08:15:00", "data": {"stations": [
  {"id": "S1", "name": "Station North", "capacity": 20,
   "location": {"lat": 28.01, "lon": 120.65}, "status": {"bikes": 5, "docks": 15}}, ...]}}

Read it from standard input, flatten the stations with pd.json_normalize, and print:

  1. columns: id, name, capacity, location.lat, location.lon, status.bikes, status.docks
  2. the table name, status.bikes, capacity, fill sorted by fill (= bikes / capacity, 2 decimals), without index;
  3. empty stations: … (names with 0 bikes, or none);
  4. system fill rate: 37.0% (total bikes / total capacity, :.1%);
  5. updated: ….

Data for this lab

Your program reads its data from standard input. Download the file, then give it to your program as shown below. Start with data set 1: its expected output is given.

v2-4-data-1.jsonl

{"last_updated": "2026-03-02T08:15:00", "data": {"stations": [
 {"id": "S1", "name": "Station North", "capacity": 20, "location": {"lat": 28.01, "lon": 120.65}, "status": {"bikes": 5, "docks": 15}},
 {"id": "S2", "name": "Station Library", "capacity": 10, "location": {"lat": 28.02, "lon": 120.66}, "status": {"bikes": 0, "docks": 10}},
 {"id": "S3", "name": "Station Park", "capacity": 16, "location": {"lat": 28.00, "lon": 120.64}, "status": {"bikes": 12, "docks": 4}}
]}}
What your program must print for v2-4-data-1.jsonl
columns: id, name, capacity, location.lat, location.lon, status.bikes, status.docks
           name  status.bikes  capacity  fill
Station Library             0        10  0.00
  Station North             5        20  0.25
   Station Park            12        16  0.75
empty stations: Station Library
system fill rate: 37.0%
updated: 2026-03-02T08:15:00

Run your program first, then open this and compare line by line: your output must contain these lines exactly.

How to give the file to my program

Terminal (macOS, Linux, or Windows Command Prompt), in the folder that holds both files:

python my_lab.py < v2-4-data-1.jsonl

Windows PowerShell (the default terminal of VS Code on Windows) does not understand <:

Get-Content v2-4-data-1.jsonl | python my_lab.py

PyCharm: Run → Edit Configurations → tick Redirect input from and choose the file.

Jupyter: put this at the top of the cell that holds your program:

import sys
sys.stdin = open("v2-4-data-1.jsonl", encoding="utf-8")

Write it here, in Jupyter or in your IDE, then paste it. Run it on the sample input yourself before comparing. Your code stays in your browser — it is never sent to or stored on the server.