Skip to content

csv_loader_architecture.md

Latest commit

 

History

History
81 lines (64 loc) · 2.44 KB

csv_loader_architecture.md

File metadata and controls

81 lines (64 loc) · 2.44 KB
Raw

CSV Loader Architecture

Current Problem

The LocalPulse API is returning 503 "csv_file_invalid" errors when loading CSV files due to:

  • Rigid encoding requirements (only UTF-8)
  • Fixed delimiter expectations (only comma)
  • No header alias support
  • Inflexible timestamp parsing
  • Schema mismatches treated as I/O errors

Solution Architecture

graph TD
    A[CSV File] --> B[app/utils/csv_loader.py]
    B --> C[detect_encoding]
    B --> D[detect_sep]
    B --> E[rename_with_aliases]
    B --> F[parse_timestamps]
    B --> G[load_traffic/load_weather]
    
    C --> H[UTF-8/UTF-8-SIG Detection]
    D --> I[CSV Sniffer for Delimiter]
    E --> J[Header Alias Mapping]
    F --> K[pandas.to_datetime with coerce]
    G --> L[pandas DataFrame Processing]
    
    L --> M[Numeric Coercion & Clip]
    L --> N[Drop Invalid Timestamps]
    L --> O[Validate Minimal Schema]
    
    O --> P{Schema Valid?}
    P -->|Yes| Q[Return list[dict]]
    P -->|No| R[422 csv_schema error]
    
    S[File I/O Error] --> T[503 csv_file_io error]
    
    Q --> U[packages/signals/ingest_csv.py]
    U --> V[routes_signals.py]
    V --> W[/signals/current & /actions endpoints]
Loading

Key Components

1. app/utils/csv_loader.py

New robust CSV loader using pandas internally but maintaining list-of-dicts API compatibility.

2. Encoding Detection

  • Try UTF-8 first
  • Fallback to UTF-8-SIG for BOM handling

3. Delimiter Detection

  • Use csv.Sniffer on first 4KB
  • Fallback to comma if detection fails

4. Header Alias Mapping

Map various header names to canonical schema:

  • Traffic: timestamp, area, flow, [optional: category]
  • Weather: timestamp, pm25, wind_mps, [optional: temp_c]

5. Flexible Timestamp Parsing

  • Use pandas.to_datetime with errors="coerce"
  • Drop rows with NaT (invalid timestamps)

6. Error Handling

  • File I/O issues: 503 "csv_file_io"
  • Schema issues: 422 with missing columns
  • Other errors: 503 "csv_unknown"

Integration Strategy

  1. Create new csv_loader.py module
  2. Delegate ingest_csv.py functions to new implementation
  3. No changes needed in routes_signals.py or other callers
  4. Maintain exact same return format (list of dictionaries)

Testing Strategy

  • Test with various header aliases (Chinese/English)
  • Test with different delimiters (comma, semicolon, tab, pipe)
  • Test with different encodings (UTF-8, UTF-8-SIG)
  • Test error conditions (missing files, schema mismatches)
  • Verify endpoints return 200 with valid CSVs