在交通大数据分析场景中，Pandas 和 NumPy 是处理 Excel 流水数据

在交通大数据分析场景中，Pandas 和 NumPy 是处理 Excel 流水数据（如ETC交易流水、门架通行记录）的核心工具。以下是典型工作流与关键代码示例：

✅ 1. 读取 Excel 流水数据（含多Sheet/大文件优化）

import pandas as pd
import numpy as np

# 高效读取（跳过空行、指定列、类型预设减少内存）
df = pd.read_excel(
    "toll_transaction.xlsx",
    sheet_name="2024Q3", 
    usecols=["txn_id", "plate_no", "entry_time", "exit_time", "toll_amt", "gate_id"],
    dtype={"plate_no": "string", "gate_id": "category"},
    parse_dates=["entry_time", "exit_time"]
)

✅ 2. 门架通行数据清洗（去重、异常时间、车牌标准化）

# 去除重复记录（按车牌+门架+时间去重）
df = df.drop_duplicates(subset=["plate_no", "gate_id", "entry_time"], keep="first")

# 过滤异常时间（如未来时间、间隔超24h的entry/exit）
df = df[
    (df["entry_time"] <= pd.Timestamp.now()) & 
    ((df["exit_time"] - df["entry_time"]) <= pd.Timedelta("24H"))
]

# 车牌统一格式（去除空格、转大写、补全省份简称）
df["plate_no"] = df["plate_no"].str.strip().str.upper().str.replace(r"^(\d+)$", r"粤\1", regex=True)

✅ 3. 关联分析：门架通行路径还原（需按车牌+时间排序）

# 按车牌分组，按时间排序，生成通行序列
df_sorted = df.sort_values(["plate_no", "entry_time"]).reset_index(drop=True)
df_sorted["seq"] = df_sorted.groupby("plate_no").cumcount() + 1

# 计算相邻门架通行时间差与距离（需关联门架地理信息表）
gate_geo = pd.read_csv("gate_locations.csv")  # 含 gate_id, lat, lon
df_merged = df_sorted.merge(gate_geo, on="gate_id", how="left")
df_merged["next_gate"] = df_merged.groupby("plate_no")["gate_id"].shift(-1)
df_merged["next_time"] = df_merged.groupby("plate_no")["entry_time"].shift(-1)
df_merged["dwell_sec"] = (df_merged["next_time"] - df_merged["entry_time"]).dt.total_seconds()

✅ 4. 聚合分析（高频统计、时段分布、逃费识别）

# 每日ETC交易量 & 平均金额
daily_stats = df.groupby(df["entry_time"].dt.date).agg(
    txn_count=("txn_id", "count"),
    avg_toll=("toll_amt", "mean"),
    total_revenue=("toll_amt", "sum")
).round(2)

# 高频通行车辆（疑似套牌或营运车）
top_plates = df["plate_no"].value_counts().head(20)

# 识别“入口有记录、出口无记录”的潜在逃费行为（需结合出口流水表）
entry_only = set(df[df["exit_time"].isna()]["plate_no"]) - set(df[df["exit_time"].notna()]["plate_no"])

✅ 5. 性能优化技巧（大数据量必备）

• 使用 dtype 显式声明列类型（尤其 category 对 gate_id/plate_no）
• 大文件用 chunksize 分块读取 + pd.concat()
• 时间字段优先转为 pd.DatetimeIndex 加速切片
• 聚合前用 .select_dtypes(include=[np.number]) 筛选数值列

如需进一步实现：
🔹 门架轨迹热力图（结合geopandas + folium）
🔹 基于通行时序的LSTM异常检测模型
🔹 自动生成日报PDF（用 jinja2 + weasyprint）
欢迎提供具体数据样例或业务目标，可定制完整分析Pipeline。
处理 GB 级 Excel 门架数据时，直接 pd.read_excel() 极易内存溢出（Excel 文件本身压缩但加载后会解压为稠密 DataFrame，内存占用常达原始文件 5–10 倍）。以下是经过生产验证的 高效、低内存、可扩展 处理方案：

✅ 核心原则：不全量加载 → 按需分块 → 类型极致压缩 → 替代格式优先

🔹 1. 【首选】转为 Parquet / HDF5（一次性转换，永久提速）

Excel 不是分析格式，应首次读取即转换为列式存储：

# 一次性转换（耗时但一劳永逸）
df = pd.read_excel("gate_data.xlsx", 
                   dtype={"plate_no": "string", "gate_id": "category"},
                   parse_dates=["pass_time"])
# 压缩保存（ZSTD 最佳，支持分区）
df.to_parquet("gate_data.parquet", 
              engine="pyarrow", 
              compression="zstd",
              use_dictionary=True)  # 对字符串/分类列高压缩

# 后续秒级加载（内存降低60%+，速度提升5–20倍）
df = pd.read_parquet("gate_data.parquet", 
                     filters=[("pass_time", ">=", "2024-01-01")])  # 支持谓词下推

💡 提示：用 pyarrow 引擎 + use_dictionary=True 可使车牌号等重复字符串内存下降 90%。

🔹 2. 【必须】分块读取（chunksize）+ 流式处理

若必须读 Excel（如上游只提供 .xlsx）：

# 分块处理（每块10万行，避免单次加载超限）
chunks = []
for chunk in pd.read_excel("gate_data.xlsx", 
                          chunksize=100_000,
                          dtype={"plate_no": "string", "gate_id": "category"},
                          parse_dates=["pass_time"]):
    # ✅ 立即清洗 & 聚合，不保留原始chunk
    cleaned = (chunk
               .dropna(subset=["plate_no", "pass_time"])
               .assign(hour=chunk["pass_time"].dt.hour)
               .groupby(["gate_id", "hour"]).size().reset_index(name="cnt"))
    chunks.append(cleaned)

# 合并聚合结果（非合并原始数据！）
result = pd.concat(chunks).groupby(["gate_id", "hour"]).sum().reset_index()

⚠️ 关键：绝不 pd.concat([chunk1, chunk2, ...]) 全量拼接原始数据！ 只拼接轻量聚合结果。

🔹 3. 【进阶】使用 openpyxl / xlrd 流式读单元格（超大文件且只需部分列）

from openpyxl import load_workbook

# 只读指定行列（跳过格式/公式/空白），内存恒定 ~10MB
wb = load_workbook("gate_data.xlsx", read_only=True, data_only=True)
ws = wb.active

# 迭代读取（内存友好）
data = []
for row in ws.iter_rows(min_row=2, max_row=1000000, values_only=True):  # 限制行数
    if row[0] and row[3]:  # 非空车牌 & 时间
        data.append((str(row[0]).strip(), pd.to_datetime(row[3], errors="coerce")))

df = pd.DataFrame(data, columns=["plate_no", "pass_time"])

🔹 4. 【关键优化】类型压缩（节省 50–80% 内存）

def reduce_mem_usage(df):
    for col in df.columns:
        col_type = df[col].dtype
        if col_type != "object":
            c_min, c_max = df[col].min(), df[col].max()
            if str(col_type)[:3] == "int":
                if c_min >= np.iinfo(np.int8).min and c_max <= np.iinfo(np.int8).max:
                    df[col] = df[col].astype(np.int8)
                elif c_min >= np.iinfo(np.int16).min and c_max <= np.iinfo(np.int16).max:
                    df[col] = df[col].astype(np.int16)
            elif str(col_type)[:5] == "float":
                df[col] = pd.to_numeric(df[col], downcast="float")
        else:
            df[col] = df[col].astype("category")  # 车牌、门架ID、省份等
    return df

df = reduce_mem_usage(df)  # 调用后内存立降

🔹 5. 【终极方案】Dask / Modin（无缝替代 Pandas，自动并行）

# 安装：pip install dask[complete] 或 pip install modin[pandas]
import dask.dataframe as dd
# 或 from modin import pandas as mpd

# Dask：延迟计算，自动分块+并行
ddf = dd.read_excel("gate_data.xlsx", 
                    dtype={"plate_no": "string"}, 
                    parse_dates=["pass_time"])
result = ddf.groupby("gate_id").toll_amt.mean().compute()  # 触发计算

# Modin：API 完全兼容 Pandas，加速 2–4 倍（无需改代码）
import modin.pandas as mpd
df = mpd.read_excel("gate_data.xlsx")  # 自动多核

📌 总结：GB级Excel门架数据处理黄金路径

如需，我可为你生成：
🔹 一键转换脚本（Excel → Parquet + 类型优化 + 分区）
🔹 Dask 分布式集群部署配置（YARN/K8s）
🔹 门架数据实时入湖（Flink + Delta Lake）Pipeline

欢迎提供文件结构（列名、行数、典型大小），可定制最优方案。