import requests
import pandas as pd
# SANDAG API endpoint
sandag_api_url = "https://opendata.sandag.org/resource/ewu3-gvdq.json"
# Fetch the data from the API
response = requests.get(sandag_api_url)
if response.status_code == 200:
data = response.json()
# Convert JSON data into a Pandas DataFrame
df = pd.DataFrame(data)
# Display only relevant columns (modify as needed based on actual data structure)
if not df.empty:
print(df.head(10).to_markdown()) # Print first 10 rows as a clean table
else:
print("The dataset is empty.")
else:
print("Failed to retrieve data. Status code:", response.status_code)
| | osm_id | street_name | geometry | length | road_type | _2021_am_peak_period_mean | _2021_pm_peak_period_mean | _2022_am_peak_period_mean | _2022_pm_peak_period_mean | _2023_am_peak_period_mean | _2023_pm_peak_period_mean | speed_change_am_1_yr | speed_change_am_2_yr | speed_change_pm_1_yr | speed_change_pm_2_yr | bidirectional | aadt_change_1_yr | aadt_change_2_yr | _2021_aadt_range | _2022_aadt_range | _2023_aadt_range |
|---:|----------:|:----------------------------------------|:-------------------------------------------------------------------------------------------------------------------------------------------------------------|---------:|:------------|----------------------------:|----------------------------:|----------------------------:|----------------------------:|----------------------------:|----------------------------:|-----------------------:|-----------------------:|-----------------------:|-----------------------:|:----------------|-------------------:|-------------------:|:-------------------|:-------------------|:-------------------|
| 0 | 355826447 | Main Street, CA 67 | {'type': 'LineString', 'coordinates': [[-116.889689681449, 33.0279774895603], [-116.890426916415, 33.0273929914994]]} | 94.59 | primary | 43.36 | 43.2 | 42.9733 | 43.72 | 39.8 | 41.2267 | -0.074 | -0.082 | -0.057 | -0.046 | True | -0.574 | -0.544 | 20,000-39,999 | 20,000-39,999 | 10,000-19,999 |
| 1 | 355826447 | Main Street, CA 67 | {'type': 'LineString', 'coordinates': [[-116.889689681449, 33.0279774895603], [-116.890426916415, 33.0273929914994]]} | 94.59 | primary | 39.36 | 35.5067 | 38.4 | 34.92 | 36.8667 | 32.72 | -0.04 | -0.063 | -0.063 | -0.078 | True | -0.574 | -0.544 | 20,000-39,999 | 20,000-39,999 | 10,000-19,999 |
| 2 | 436296441 | North Harbor Drive | {'type': 'LineString', 'coordinates': [[-117.173666890867, 32.7253630248911], [-117.173568170672, 32.7251748977268], [-117.173507634703, 32.7250465614732]]} | 38.22 | primary | 29.2133 | 25.4 | 27.28 | 23.9733 | 24.5067 | 17.9733 | -0.102 | -0.161 | -0.25 | -0.292 | False | -0.118 | -0.171 | 20,000-39,999 | 20,000-39,999 | 20,000-39,999 |
| 3 | 160432923 | North Harbor Drive | {'type': 'LineString', 'coordinates': [[-117.219997209758, 32.7263641966812], [-117.22033695624, 32.7261902256203]]} | 37.21 | primary | 32.3067 | 29.6133 | 26.44 | 25.1733 | 24.2267 | 22.6 | -0.084 | -0.25 | -0.102 | -0.237 | False | -0.277 | -0.216 | 10,000-19,999 | 10,000-19,999 | 5,000-9,999 |
| 4 | 436300819 | North Coast Highway 101, S21 | {'type': 'LineString', 'coordinates': [[-117.299178815615, 33.0580789534318], [-117.299491926271, 33.0585589570975]]} | 60.83 | primary | 23.9067 | 22.6267 | 23.7733 | 23.64 | 25.92 | 23.6 | 0.09 | 0.084 | -0.002 | 0.043 | True | -0.809 | -0.875 | 20,000-39,999 | 10,000-19,999 | 1,000-4,999 |
| 5 | 436300819 | North Coast Highway 101, S21 | {'type': 'LineString', 'coordinates': [[-117.299178815615, 33.0580789534318], [-117.299491926271, 33.0585589570975]]} | 60.83 | primary | 24.2933 | 23.2133 | 22.52 | 21.2 | 25.3333 | 23.6 | 0.125 | 0.043 | 0.113 | 0.017 | True | -0.809 | -0.875 | 20,000-39,999 | 10,000-19,999 | 1,000-4,999 |
| 6 | 232293293 | South El Camino Real | {'type': 'LineString', 'coordinates': [[-117.259543588598, 33.0408828267652], [-117.259556999644, 33.040319749124], [-117.259548990269, 33.0401949518963]]} | 76.52 | primary | 27.4267 | 26.1733 | 26.3333 | 19.4667 | 26.7333 | 24.3467 | 0.015 | -0.025 | 0.251 | -0.07 | True | -0.797 | -0.852 | 20,000-39,999 | 20,000-39,999 | 5,000-9,999 |
| 7 | 232293293 | South El Camino Real | {'type': 'LineString', 'coordinates': [[-117.259543588598, 33.0408828267652], [-117.259556999644, 33.040319749124], [-117.259548990269, 33.0401949518963]]} | 76.52 | primary | 29.7467 | 29.36 | 27.1867 | 27.4267 | 35.64 | 35.6933 | 0.311 | 0.198 | 0.301 | 0.216 | True | -0.797 | -0.852 | 20,000-39,999 | 20,000-39,999 | 5,000-9,999 |
| 8 | 436265159 | Palomar Airport Road, S12 | {'type': 'LineString', 'coordinates': [[-117.259379675822, 33.1306332676999], [-117.259527383585, 33.1306224643578]]} | 13.81 | primary | 33.16 | 31.3867 | 30.7333 | 30.28 | 33.0267 | 31.5333 | 0.075 | -0.004 | 0.041 | 0.005 | False | -0.018 | -0.165 | 20,000-39,999 | 20,000-39,999 | 20,000-39,999 |
| 9 | 436288515 | El Cajon Boulevard East, US 80 Historic | {'type': 'LineString', 'coordinates': [[-117.030102581459, 32.768345984109], [-117.029810518693, 32.7684027947873]]} | 28.03 | primary | 22.56 | 20.7467 | 20.0933 | 18.8267 | 22.4 | 20.24 | 0.115 | -0.007 | 0.075 | -0.024 | False | 0.099 | -0.069 | 5,000-9,999 | 5,000-9,999 | 5,000-9,999 |
print("Columns in dataset:", df.columns.tolist())
Columns in dataset: ['osm_id', 'street_name', 'geometry', 'length', 'road_type', '_2021_am_peak_period_mean', '_2021_pm_peak_period_mean', '_2022_am_peak_period_mean', '_2022_pm_peak_period_mean', '_2023_am_peak_period_mean', '_2023_pm_peak_period_mean', 'speed_change_am_1_yr', 'speed_change_am_2_yr', 'speed_change_pm_1_yr', 'speed_change_pm_2_yr', 'bidirectional', 'aadt_change_1_yr', 'aadt_change_2_yr', '_2021_aadt_range', '_2022_aadt_range', '_2023_aadt_range']
import requests
import pandas as pd
# SANDAG API endpoint
sandag_api_url = "https://opendata.sandag.org/resource/ewu3-gvdq.json"
# Fetch the data from the API
response = requests.get(sandag_api_url)
if response.status_code == 200:
data = response.json()
# Convert JSON data into a Pandas DataFrame
df = pd.DataFrame(data)
# Select relevant columns: Street Name and Speeds from 2023
columns_to_keep = ["street_name", "_2023_am_peak_period_mean", "_2023_pm_peak_period_mean"]
# Check if the columns exist before filtering
df_filtered = df[columns_to_keep].dropna()
# Display the cleaned table
print(df_filtered.head(10)) # Show first 10 rows
else:
print("Failed to retrieve data. Status code:", response.status_code)
street_name _2023_am_peak_period_mean \
0 Main Street, CA 67 39.8
1 Main Street, CA 67 36.86666666666666
2 North Harbor Drive 24.50666666666667
3 North Harbor Drive 24.22666666666667
4 North Coast Highway 101, S21 25.92
5 North Coast Highway 101, S21 25.33333333333333
6 South El Camino Real 26.73333333333333
7 South El Camino Real 35.63999999999999
8 Palomar Airport Road, S12 33.026666666666664
9 El Cajon Boulevard East, US 80 Historic 22.4
_2023_pm_peak_period_mean
0 41.22666666666667
1 32.720000000000006
2 17.973333333333333
3 22.6
4 23.600000000000005
5 23.59999999999999
6 24.34666666666667
7 35.693333333333335
8 31.533333333333335
9 20.240000000000002