DBSCAN का उपयोग करके भारत में ट्रेन की देरी का विश्लेषण¶

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

def get_table(train_number):
    # लिंक दो इनपुट लेता है, ट्रेन नंबर और तारीख YYYYMMDD (2025-12-13) में
    link = 'https://runningstatus.in/status/'+ str(train_number)+'-on-20251213'
    try:
        response = requests.get(link, timeout=15)
    except:
        # टाइमआउट तब होता है जब तारीख पर ट्रेन नंबर के लिए कोई ट्रेन नहीं होती
        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 में तालिका में हेडर पंक्ति होती है
        last_row = thead.find("tr")
        # tr पंक्ति है
        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 प्रत्येक सेल (तालिका डेटा) है
        tds = tr.find_all("td")
        if len(tds) != 4:
            continue
            # 4 पंक्तियाँ होनी चाहिए

        # --- स्टेशन सेल ---
        station_cell = tds[0]
        abbr = station_cell.find("abbr") # यह स्टेशन नाम का संक्षिप्त रूप है
        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") # यह देरी की स्थिति है
        delay_status = delay_small.get_text(strip=True) if delay_small else None

        # --- आगमन सेल ---
        arrival_cell = tds[1]
        arrival_text = arrival_cell.get_text(" ", strip=True)  # उदाहरण: "02:11 PM / 02:11 PM" या "Source"
        arrival_status_tag = arrival_cell.find("span")
        arrival_status = arrival_status_tag.get_text(strip=True) if arrival_status_tag else None

        # यदि आगमन समय मौजूद है तो उसे पार्स करें
        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 = tds[2]
        departure_text = departure_cell.get_text(" ", strip=True)  # उदाहरण: "02:12 PM / 02:12 PM" या "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 सेल ---
        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,  # उदाहरण: "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

# मध्यरात्रि की समस्याओं के लिए फ़िल्टरिंग: 
# जब दिन बदलता है और आगमन मध्यरात्रि के तुरंत बाद होता है, तो देरी को 24*60=1440 के रूप में दिखाया जाता है बजाय वास्तविक देरी के
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,
    }
    # वैकल्पिक: क्षेत्र पूर्वाग्रह (उदाहरण: "in" भारत के लिए) प्रासंगिकता में सुधार के लिए
    if region_bias:
        params["region"] = region_bias

    response = requests.get(url, params=params, timeout=15)
    response.raise_for_status()  # नेटवर्क/HTTP-स्तरीय त्रुटियाँ

    payload = response.json()

    status = payload.get("status")
    if status != "OK":
        # सामान्य स्थिति: 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"]  # जब स्थिति == 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

# मैपिंग समस्याओं को ठीक करना
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) DBSCAN को एकल eps में मीटर में चलाएँ
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])

# प्रत्येक क्लस्टर का विवरण
delays.groupby('labels').aggregate({
    'arrival_delay':['min', 'max', np.mean, np.std],
    'departure_delay':['min', 'max', np.mean, np.std]
})

# डिफ़ॉल्ट रंग सेट करना
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

# भारत का मानचित्र जिसमें राज्य की सीमाएँ हैं
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")

# लिजेंड जोड़ना
legend_spec = [
    ("black",  "Outliers"),
    ("green",  "No delays"),
    ("blue",   "Average delays"),
    ("orange", "High delays"),
    ("red",    "Severe delays"),
]

# लिजेंड हैंडल्स बनाना
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()