Audit Delhi's bus GTFS timetable before you trust its times

The feed

Open Transit Data Delhi is run for the Delhi government with IIIT-Delhi. The Static Data page offers the bus feed as one zip, after a short form asking for your name and purpose. When we checked on 27 September 2026, the page listed:

The documentation page lists six files: agency.txt, calendar.txt, stops.txt, routes.txt, trips.txt and stop_times.txt. They follow the GTFS reference. There is no shapes.txt or frequencies.txt in that list. Column names are fixed by GTFS, such as trip_id, arrival_time, stop_id and stop_sequence. Still, check the downloaded file's header, because optional columns vary between feeds. The data is more than two years old, so it is the first limitation in your README.

Why this is worth an employer's attention

Most portfolio projects assume the data is right. An audit shows that you ask first. Transit and mobility firms, logistics platforms and consultancies that assess public data for clients all need someone who can write "this field is usable for X, not for Y" and back it with tests. The same method applies to a vendor extract at work, where a column may have been generated rather than measured.

The checks

1. 1Row counts against the counts shown on the download page.
2. 2Referential integrity: every stop_times.stop_id exists in stops, every trip_id in trips, every trips.route_id in routes and every service_id in calendar.
3. 3Sequence and time order: stop_sequence is unique within a trip, and arrival times never decrease along it.
4. 4Past-midnight times: GTFS allows hours of 24 and above for trips that run after midnight. Parse them as elapsed seconds, not clock times, or late trips vanish.
5. 5Implied speed: straight-line distance between consecutive stops divided by scheduled time. If times came from one constant speed over road distance, straight-line speed will vary only with road detours, not with traffic.
6. 6Coordinates: stops outside a Delhi-NCR bounding box, or duplicate coordinates under different stop IDs.

Code: the audit on a synthetic feed

The feed below is synthetic. Stop names, coordinates and times are invented, and some faults are planted on purpose. The code uses SQLite for the integrity checks and pandas for the speed test.

import sqlite3
import numpy as np
import pandas as pd

stops = pd.DataFrame({
    "stop_id": ["S1", "S2", "S3", "S4", "S5"],
    "stop_lat": [28.6300, 28.6340, 28.6395, 28.6440, 28.6500],
    "stop_lon": [77.2200, 77.2260, 77.2310, 77.2390, 77.2440],
})
trips = pd.DataFrame({"trip_id": ["T1", "T2", "T3"], "route_id": ["R1", "R1", "R9"], "service_id": ["WK"] * 3})
routes = pd.DataFrame({"route_id": ["R1"]})          # R9 missing: planted fault
st = pd.DataFrame({
    "trip_id": ["T1"] * 5 + ["T2"] * 5 + ["T3"] * 3,
    "stop_sequence": [1, 2, 3, 4, 5, 1, 2, 3, 3, 4, 1, 2, 3],   # T2 repeats sequence 3
    "stop_id": ["S1", "S2", "S3", "S4", "S5", "S1", "S2", "S3", "S4", "S5", "S1", "S2", "S9"],
    "arrival_time": ["06:00:00", "06:02:10", "06:04:40", "06:07:30", "06:09:50",
                     "23:50:00", "23:52:10", "23:54:40", "23:57:30", "23:59:50",
                     "24:05:00", "24:07:10", "24:06:00"],   # T3 runs past midnight and goes backwards
})

con = sqlite3.connect(":memory:")
for name, df in {"stops": stops, "trips": trips, "routes": routes, "stop_times": st}.items():
    df.to_sql(name, con, index=False)
checks = {
    "stop_times with unknown stop": "SELECT COUNT(*) FROM stop_times s LEFT JOIN stops p USING(stop_id) WHERE p.stop_id IS NULL",
    "trips with unknown route": "SELECT COUNT(*) FROM trips t LEFT JOIN routes r USING(route_id) WHERE r.route_id IS NULL",
    "duplicate (trip, sequence)": "SELECT COUNT(*) FROM (SELECT trip_id, stop_sequence FROM stop_times GROUP BY 1, 2 HAVING COUNT(*) > 1)",
}
for label, q in checks.items():
    print(f"{label}: {con.execute(q).fetchone()[0]}")

def to_secs(t):
    h, m, s = (int(x) for x in t.split(":"))
    return h * 3600 + m * 60 + s                       # 24:05:00 stays after 23:59:50

st["secs"] = st["arrival_time"].map(to_secs)
st = st.sort_values(["trip_id", "stop_sequence"]).merge(stops, on="stop_id", how="left")
st["dt"] = st.groupby("trip_id")["secs"].diff()
print("backwards time steps:", st.loc[st["dt"] < 0, ["trip_id", "stop_sequence"]].to_dict("records"))

lat, lon = np.radians(st["stop_lat"]), np.radians(st["stop_lon"])
dlat, dlon = lat.groupby(st["trip_id"]).diff(), lon.groupby(st["trip_id"]).diff()
a = np.sin(dlat / 2) ** 2 + np.cos(lat) * np.cos(lat.groupby(st["trip_id"]).shift()) * np.sin(dlon / 2) ** 2
st["km"] = 2 * 6371 * np.arcsin(np.sqrt(a))
st["kmph"] = st["km"] / (st["dt"] / 3600)
ok = st[(st["dt"] > 0) & st["kmph"].notna()]
print(ok.groupby("trip_id")["kmph"].agg(["count", "mean", "std"]).round(1).to_string())

In the synthetic feed, the checks find one unknown stop, one unknown route, one duplicated sequence and one backward time step. The speed table shows T1 and T2 at almost the same speed on every segment, which is what a constant-speed generator produces. Real traffic would make morning and late-night trips differ. Run the same comparison across hours of the day on the real feed. If implied speed does not change between 09:00 and 23:00, you have confirmed the documentation's warning from the data itself.

Analysis plan

• Download the zip, record the date and the last-updated line, and reproduce the page's counts.
• Run the integrity, order, midnight and coordinate checks, and tabulate failures by file.
• Compute implied straight-line speed per segment, then the median by hour of day and by route.
• Classify fields: usable (routes, stops, trip counts by service day), usable with caution (first and last trip), not usable (stop-to-stop travel times).
• Compare with the metro feed on the DMRC Static Data page, last updated 10 August 2023.

Deliverables

• Audit table: check, file, rows tested, failures, severity.
• Speed chart: median implied speed by hour of day, showing a flat line or not.
• Fitness-for-use table: each field and the questions it can and cannot answer.
• README: download date, form purpose stated, file counts, the documentation's warning quoted, the feed's age and the checks you could not run.

Interview questions

• Why can a GTFS time read 24:05:00, and what breaks if you parse it as a clock time?
• How would you show a timetable was generated rather than scheduled?
• Straight-line speed is not road speed. Why is it still a fair test here?
• Which of your findings would you send to the data publisher, and how would you phrase them?

For a frequency analysis built on the same feed, see the Delhi bus open-data project. Data validation before analysis runs through the Data Analyst programme, taught live online with optional in-person labs at our Noida centre.