Uber EDA #1

import os

import kagglehub
import pandas as pd

# Download data from kaggle
path = kagglehub.dataset_download("yashdevladdha/uber-ride-analytics-dashboard")

# Load data into Pandas DataFrame
csv_file = os.path.join(path, "ncr_ride_bookings.csv")
df = pd.read_csv(csv_file)
print("✅ Data loaded successfully!")

print(df.__class__.__name__)

# Inspect first 5 rows
df.head()

# Inspect last 2 rows
df.tail(2)

print(f"📈 Total rides: {len(df):,}")

print(f"📊 Dataset shape: {df.shape}")
nrow, ncol = df.shape
print(f" - rows: {nrow:,}")
print(f" - columns: {ncol:,}")

df.columns

df.info()

df[["Time", "Vehicle Type", "Pickup Location", "Avg VTAT"]].head()

df.describe()

df.describe().T

df.Time

df.Time.__class__.__name__

df["Booking Status"]

df.Time.values

df.Time.values.__class__.__name__

df["Vehicle Type"] == "Uber XL"

df.loc[df["Vehicle Type"] == "Uber XL", ("Time", "Vehicle Type", "Pickup Location", "Avg VTAT")]

(
    df.loc[
        df["Vehicle Type"] == "Uber XL", ("Time", "Vehicle Type", "Pickup Location", "Avg VTAT")
    ].reset_index(drop=True)
)

df.loc[
    (df["Vehicle Type"] == "Uber XL")
    | (df["Vehicle Type"] == "Bike"),  # or df["Vehicle Type"].isin(["Uber XL", "Bike"])
    ("Time", "Vehicle Type", "Pickup Location", "Avg VTAT"),
]

df.iloc[100:105, [0, 1, 3, 5]]

df.agg({"Time": ["min", "max"], "Avg VTAT": ["min", "max", "mean"]})

# An indexed series of missing value counts
na_counts = df.isnull().sum()
na_counts

na_counts.__class__.__name__

na_counts.index

na_counts["Customer Rating"]

fig = na_counts.plot(kind="barh", title="Missing Value Counts")

import plotly.express as px

fig = px.bar(na_counts, title="Missing Value Counts")
fig.show()

# Number of rides per vehicle type with different colors per type
fig = px.bar(
    df["Vehicle Type"].value_counts(),
    title="Number of Rides per Vehicle Type",
    color=df["Vehicle Type"].value_counts().index,
)
fig.show()

# Driver Ratings histogram
fig = px.histogram(df, x="Driver Ratings", nbins=20, title="Driver Ratings Histogram")
fig.show()

fig = px.scatter(df, x="Time", y="Avg CTAT", color="Vehicle Type", title="Avg VTAT over Time")
fig.show()

df.head()

df["Date"].astype(str) + " " + df["Time"].astype(str)

# New date time column
df["DateTime"] = pd.to_datetime(df["Date"].astype(str) + " " + df["Time"].astype(str))

df["DateTime"].head()

fig = px.scatter(df, x="DateTime", y="Avg CTAT", color="Vehicle Type", title="Avg VTAT over Time")
fig.show()

avg_ctat_hourly = (
    df.loc[:, ["DateTime", "Avg CTAT", "Vehicle Type"]]  # select only relevant columns
    .set_index("DateTime")  # set datetime as index (needed to allow resampling)
    .resample("h")  # resample to hourly frequency
    # aggregate by mean for each hour and vehicle type
    .agg({"Avg CTAT": "mean", "Vehicle Type": "first"})
    .reset_index()  # reset index to get datetime back as a column
    .dropna()  # drop missing values
)

avg_ctat_hourly

fig = px.line(
    avg_ctat_hourly, x="DateTime", y="Avg CTAT", color="Vehicle Type", title="Avg CTAT over Time"
)
fig.show()

import plotly.graph_objects as go
from plotly.subplots import make_subplots
from statsmodels.nonparametric.smoothers_lowess import lowess

vehicle_types = df["Vehicle Type"].unique()

fig = make_subplots(rows=len(vehicle_types), cols=1)

for i, vehicle_type in enumerate(vehicle_types, start=1):
    x = avg_ctat_hourly.loc[avg_ctat_hourly["Vehicle Type"] == vehicle_type, "DateTime"]
    y = avg_ctat_hourly.loc[avg_ctat_hourly["Vehicle Type"] == vehicle_type, "Avg CTAT"]

    # Scatter
    fig.add_trace(go.Scatter(x=x, y=y, name=vehicle_type, mode="lines"), row=i, col=1)

    # LOWESS smoothing
    lowess_result = lowess(y, x, frac=0.02)  # frac controls smoothing
    lowess_x = lowess_result[:, 0]
    lowess_y = lowess_result[:, 1]
    fig.add_trace(
        go.Scatter(
            x=x, y=lowess_y, name=f"{vehicle_type} (LOWESS)", mode="lines", showlegend=False
        ),
        row=i,
        col=1,
    )

fig.update_layout(height=300 * len(vehicle_types), title_text="Avg CTAT over Time by Vehicle Type")

fig.show()

df["Hour"] = df["DateTime"].dt.hour
df_agg_hourly = (
    df.groupby(["Hour", "Vehicle Type"])["Avg CTAT"]  # group by hour and vehicle type
    .mean()  # aggregate by mean
    .reset_index()  # reset index to get hour and vehicle type back as columns
)

df_agg_hourly.head()

fig = px.line(
    df_agg_hourly, x="Hour", y="Avg CTAT", color="Vehicle Type", title="Avg CTAT by Hour of Day"
)
fig.show()

# box plot for Premier Sedans per hour of the day
fig = px.box(
    df.loc[df["Vehicle Type"] == "Premier Sedan", :],
    x="Hour",
    y="Avg CTAT",
    title="Avg CTAT Distribution for Premier Sedans by Hour of Day",
)
fig.show()

# Day of the week
df["DayOfWeek"] = df["DateTime"].dt.day_name()

fig = px.box(
    df.loc[df["Vehicle Type"] == "Premier Sedan", :],
    x="DayOfWeek",
    y="Avg CTAT",
    title="Avg CTAT Distribution for Premier Sedans by Day of Week",
)
fig.show()

# Helper function to categorize booking status
def categorize_status(status):
    """Categorize booking status into Completed, Cancelled, No Driver Found, Other."""
    if status == "Completed":
        return "Completed"
    if "Cancelled" in str(status):
        return "Cancelled"
    if status == "No Driver Found":
        return "No Driver Found"
    return "Other"

# Create new status variable using .apply
df["Status_Category"] = df["Booking Status"].apply(categorize_status)

fig = px.pie(df, names="Status_Category", title="Booking Status Distribution")
fig.show()

# Booking status by vehicle type
df_booking_status = df.groupby(["Vehicle Type", "Booking Status"]).size().reset_index(name="Count")

fig = px.bar(
    df_booking_status,
    x="Vehicle Type",
    y="Count",
    barmode="group",
    title="Number of Rides per Vehicle Type",
    color="Booking Status",
)
fig.show()

# Correlations between numerical variables
df_num = df.loc[
    :,
    ("Avg VTAT", "Avg CTAT", "Booking Value", "Ride Distance", "Driver Ratings", "Customer Rating"),
]
df_num.dropna(inplace=True)  # drop rows with missing values

df_num.corr()

# Box plot with booking value per vehicle type
fig = px.box(
    df,
    x="Vehicle Type",
    y="Booking Value",
    title="Booking Value Distribution per Vehicle Type",
    log_y=True,
)
fig.show()