---
name: bts-transportation-services-index-ingestion
description: "How to get BTS Transportation Services Index data into a pandas DataFrame and Parquet: access, the request, the format, limits, quirks and terms."
---

# Load BTS transport indexes and rail inputs into Parquet

Source entry: https://signal-lab-jet.vercel.app/datasets/bts-transportation-services-index
Provider: https://data.bts.gov/Research-and-Statistics/Transportation-Services-Index-and-Seasonally-Adjus/bw6n-ddqk
Checked against the provider's documentation: 2026-11-01

## Access
- No account or key. The DOT data portal exposes JSON and CSV endpoints.
- The example selects the three headline indexes and seasonally adjusted rail inputs.

## Request
```bash
curl 'https://data.bts.gov/resource/bw6n-ddqk.json?$select=obs_date,tsi_freight,tsi_passenger,tsi_total,rail_frt_carloads_d11,rail_frt_intermodal_d11,rail_pm_d11&$order=obs_date&$limit=5000'
```

## Format
JSON rows keyed by monthly observation date. Selected numeric fields are freight, passenger and total TSI plus seasonally adjusted rail freight carloads, intermodal traffic and passenger-miles.

## Load into pandas and Parquet
Needs Python 3 with requests, pandas and pyarrow.
```python
"""BTS Transportation Services Index -> pandas -> Parquet. No key."""
import requests
import pandas as pd

URL = "https://data.bts.gov/resource/bw6n-ddqk.json"
params = {
    "$select": "obs_date,tsi_freight,tsi_passenger,tsi_total,rail_frt_carloads_d11,rail_frt_intermodal_d11,rail_pm_d11",
    "$order": "obs_date",
    "$limit": 5000,
}
r = requests.get(URL, params=params, timeout=60)
r.raise_for_status()
df = pd.DataFrame(r.json())
df["obs_date"] = pd.to_datetime(df["obs_date"], utc=True)
for column in df.columns.drop("obs_date"):
    df[column] = pd.to_numeric(df[column], errors="coerce")
df.to_parquet("bts_transportation_services_index.parquet", index=False)
print(len(df), "monthly observations", df["obs_date"].min().date(), "to", df["obs_date"].max().date(),
      "latest freight TSI", df.iloc[-1]["tsi_freight"])
```
Run by us on 2026-11-01 (python 3.13.5 | pandas 3.1.0 | pyarrow 26.0.0 | requests 2.34.2). Result: 320 monthly observations 2000-01-01 to 2026-08-01 latest freight TSI 134.4

## Limits
- Rate limit: Not stated.
- The checked table contains one national row per month.
- The latest checked observation was August 2026.

## Quirks
- The API returns numbers as JSON strings; convert selected measure columns explicitly.
- Columns ending in _D11 are seasonally adjusted inputs; similarly named columns without that suffix can be unadjusted.
- TSI levels are indexes based on the 2000 annual average, not tons, trips or monetary values.

## Terms and attribution
- The dataset metadata identifies the table as Public Domain U.S. Government.
- No attribution requirement was stated in the checked metadata.
- Terms page: https://www.usa.gov/government-copyright

## Sources
- https://data.bts.gov/api/views/bw6n-ddqk: "measures the movement of freight and passengers"
- https://data.bts.gov/api/views/bw6n-ddqk: "The Transportation Services Freight Index is a weighted average of monthly data for trucking, freight rail, waterborne, pipeline, and air freight."
- https://data.bts.gov/api/views/bw6n-ddqk: "Observation Date ( Month and Year)"
- https://data.bts.gov/api/views/bw6n-ddqk: "Public Domain U.S. Government"

## Help improve this skill
Missing a source, or see something to improve? Call the MCP tool suggest_source or feedback (or POST /api/suggest or /api/feedback). Reviewed ideas: https://signal-lab-jet.vercel.app/agent-suggestions

Information for educational purposes only. Free tiers, limits and licenses belong to the providers and may change; check the provider's own terms before using the data.