Analyzing Train Delays in India Using DBSCAN¶

from bs4 import BeautifulSoup
import requests
import pandas as pd
import numpy as np

def get_table(train_number):
    # The link takes two inputs, train number and date in YYYYMMDD (2025-12-13)
    link = 'https://runningstatus.in/status/'+ str(train_number)+'-on-20251213'
    try:
        response = requests.get(link, timeout=15)
    except:
        # Timeout happens if there is no train for the train number on the date
        print("timeout for", train_number)
        return pd.DataFrame()
    if response.status_code == 200:
        page_content = response.text
        soup = BeautifulSoup(page_content, 'html.parser')
    else:
        print(train_number, 'Failed')
        return pd.DataFrame()
    table = soup.find("table")
    last_updated = None
    for thead in table.find_all("thead"):
        # thead contains the header row in the table
        last_row = thead.find("tr")
        # tr is the row
        if last_row:
            td = last_row.find("td", colspan=True)
            if td and "Last Updated" in td.get_text(strip=True):
                last_updated = td.get_text(strip=True)
                break

    rows = []
    tbody = table.find("tbody")
    for tr in tbody.find_all("tr"):
        # td is every cell (table data)
        tds = tr.find_all("td")
        if len(tds) != 4:
            continue
            # should have 4 rows

        # --- Station cell ---
        station_cell = tds[0]
        abbr = station_cell.find("abbr") # this is the station name abbreviation
        station_name = abbr.get_text(strip=True) if abbr else station_cell.get_text(strip=True)
        station_code = abbr["title"] if (abbr and abbr.has_attr("title")) else None

        delay_small = station_cell.find("small") # this is delay status
        delay_status = delay_small.get_text(strip=True) if delay_small else None

        # --- Arrival cell ---
        arrival_cell = tds[1]
        arrival_text = arrival_cell.get_text(" ", strip=True)  # e.g., "02:11 PM / 02:11 PM" or "Source"
        arrival_status_tag = arrival_cell.find("span")
        arrival_status = arrival_status_tag.get_text(strip=True) if arrival_status_tag else None

        # Parse arrival times if present
        arr_sch, arr_act = None, None
        if "/" in arrival_text:
            parts = [p.strip() for p in arrival_text.split("/")]
            if len(parts) == 2:
                arr_sch, arr_act = parts

        # --- Departure cell ---
        departure_cell = tds[2]
        departure_text = departure_cell.get_text(" ", strip=True)  # e.g., "02:12 PM / 02:12 PM" or "Destination"
        departure_status_tag = departure_cell.find("span")
        departure_status = departure_status_tag.get_text(strip=True) if departure_status_tag else None

        dep_sch, dep_act = None, None
        if "/" in departure_text:
            parts = [p.strip() for p in departure_text.split("/")]
            if len(parts) == 2:
                dep_sch, dep_act = parts

        # --- PF cell ---
        pf = tds[3].get_text(strip=True)

        rows.append({
            "Station Name": station_name,
            "Station Code": station_code,
            "Delay Status": delay_status,
            "Arrival Scheduled": arr_sch,
            "Arrival Actual": arr_act,
            "Arrival Status": arrival_status,
            "Departure Scheduled": dep_sch,
            "Departure Actual": dep_act,
            "Departure Status": departure_status,  # e.g., "Destination"
            "PF": pf
        })

    df = pd.DataFrame(rows)
    df['train_number'] = train_number
    print(train_number)
    return df

train_details = pd.DataFrame()
for train_number in range(12101, 12999):
    train_details = pd.concat([train_details, get_table(train_number)], ignore_index=False)
for train_number in range(22101, 22999):
    train_details = pd.concat([train_details, get_table(train_number)], ignore_index=False)
train_details

train_details.columns = ['station_name', 'station_code', 'delay_status', 'scheduled_arrival', 'actual_arrival', 'arrival_status', 'scheduled_departure', 'actual_departure', 'departure_status', 'pf', 'train_number']

train_details[['actual_departure', 'delay_departure']] = train_details['actual_departure'].str.split('M', n=1, expand=True)
train_details[['actual_arrival', 'arrival_departure']] = train_details['actual_arrival'].str.split('M', n=1, expand=True)
time_cols = ['scheduled_arrival', 'actual_arrival', 'scheduled_departure', 'actual_departure']
train_details['actual_departure'] = train_details['actual_departure'] + 'M'
train_details['actual_arrival'] = train_details['actual_arrival'] + 'M'
for time_col in time_cols:
    train_details[time_col] = pd.to_datetime(train_details[time_col])

train_details['arrival_delay'] = (train_details.actual_arrival - train_details.scheduled_arrival)/pd.Timedelta(minutes=1)
train_details['departure_delay'] = (train_details.actual_departure - train_details.scheduled_departure)/pd.Timedelta(minutes=1)
train_details['arrival_delay'] = np.maximum(0, train_details['arrival_delay'].fillna(0))
train_details['departure_delay'] = np.maximum(0, train_details['departure_delay'].fillna(0))
train_details

# Filtering for midnight issues: 
# When the day changes and the arrival is just after midnight, the delay is shown as 24*60=1440 rather than the actual delay
train_details.loc[train_details.arrival_delay >= 1350, 'arrival_delay'] = 1440 - train_details[train_details.arrival_delay >= 1350].arrival_delay
train_details.sort_values('arrival_delay', ascending=True).tail(25)

delays = train_details.groupby(['station_name', 'station_code']).aggregate({
    'arrival_delay': 'mean',
    'departure_delay': 'mean',
    'delay_status': 'count'
}).reset_index()
delays = delays[delays.delay_status>=10].reset_index(drop=True)
delays.sort_values('delay_status', ascending=False)

import config
import requests

def get_lat_lng(location, gmaps_key=config.gmaps_key, region_bias = 'in'):
    """
    Returns (lat, lng) for a given location using Google Geocoding API.
    Raises informative exceptions on common failure modes.
    """
    url = "https://maps.googleapis.com/maps/api/geocode/json"
    params = {
        "address": location,
        "key": gmaps_key,
    }
    # Optional: region bias (e.g., "in" for India) to improve relevance
    if region_bias:
        params["region"] = region_bias

    response = requests.get(url, params=params, timeout=15)
    response.raise_for_status()  # network/HTTP-level errors

    payload = response.json()

    status = payload.get("status")
    if status != "OK":
        # Common statuses: ZERO_RESULTS, OVER_QUERY_LIMIT, REQUEST_DENIED, INVALID_REQUEST
        error_message = payload.get("error_message")
        return 0,0

    results = payload.get("results", [])
    if not results:
        return 0,0

    location_obj = results[0]["geometry"]["location"]  # guaranteed when status == OK
    lat = location_obj["lat"]
    lng = location_obj["lng"]
    return lat, lng

delays['address'] = delays['station_name']+' ('+ delays.station_code + ') Railway station, India'
delays[['lat','long']] = delays['address'].apply(lambda addr: pd.Series(get_lat_lng(addr), index=["lat", "lng"]))
delays

# Fixing mapping issues
delays.loc[delays.address == 'Kareli (KY) Railway station, India', ['lat', 'long']] = [22.931886, 79.066079]

from sklearn.cluster import DBSCAN
from sklearn.preprocessing import MinMaxScaler
import numpy as np

scaler = MinMaxScaler().set_output(transform="pandas")
X = scaler.fit_transform(delays[['arrival_delay', 'lat', 'long']])

# 3) Run DBSCAN with a single eps in meters
eps_meters = 0.07
min_samples = 5

db = DBSCAN(eps=eps_meters, min_samples=min_samples, metric='euclidean')
delays['labels'] = db.fit_predict(X)

delays['labels'].unique()

array([ 0,  1, -1,  2,  3])

# Details of each cluster
delays.groupby('labels').aggregate({
    'arrival_delay':['min', 'max', np.mean, np.std],
    'departure_delay':['min', 'max', np.mean, np.std]
})

# Set the default colors
my_colors_list = {-1: 'black', 0: 'green', 1: 'blue', 2: 'orange', 3: 'red'}
delays['label_colors'] = delays.labels.map(my_colors_list)

import geopandas as gpd
import matplotlib.pyplot as plt
from matplotlib.lines import Line2D

# Has India map with state boundaries
map_gdf = gpd.read_file('https://gist.githubusercontent.com/jbrobst/56c13bbbf9d97d187fea01ca62ea5112/raw/e388c4cae20aa53cb5090210a42ebb9b765c0a36/india_states.geojson')

fig, ax = plt.subplots(figsize=(10, 10))
map_gdf.plot(ax=ax, color='white', edgecolor='lightgrey')
delays.plot(x='long', y='lat', c = 'label_colors', kind='scatter', alpha=0.75, ax=ax)
plt.title("Clusters of train stations based on delays on 13/14-12-2025")

# Add legends
legend_spec = [
    ("black",  "Outliers"),
    ("green",  "No delays"),
    ("blue",   "Average delays"),
    ("orange", "High delays"),
    ("red",    "Severe delays"),
]

# Build legend handles
handles = [Line2D([0], [0],marker='o', color='w', label=label, markerfacecolor=color, markersize=10) for color, label in legend_spec]
ax.legend(handles=handles, title="Cluster meaning", loc="upper right", frameon=True)
plt.show();

fig, ax = plt.subplots(figsize=(10, 10))
map_gdf.plot(ax=ax, color='white', edgecolor='lightgrey')
delays.plot(x='long', y='lat', s = 'delay_status', c = 'arrival_delay', colormap='Reds', kind='scatter', ax=ax)
plt.title("Average delays on on 13/14-12-2025")
plt.show()