Skip to main content

Timeseries Processing V2

This directory contains the new implementation of timeseries CSV processing with dynamic frequency detection and LPS tag support.

Overview​

The v2 implementation provides:

  • Dynamic frequency detection: Determines reading frequency from actual data patterns
  • Mixed-frequency support: Handles devices that change frequency over time
  • Dual tag creation: Creates both consumption (cubic feet) and demand (liters/second) tags
  • Pandas-based processing: Efficient data analysis and manipulation
  • Comprehensive reporting: Detailed analysis and summary reports

Scripts​

1. analyze_frequency_patterns.py​

Purpose: Read-only analysis script to profile timeseries data patterns.

Usage:

python v2/analyze_frequency_patterns.py <csv_file> [--output <report_file>]

Examples:

# Print report to console
python v2/analyze_frequency_patterns.py data/timeseries.csv

# Save report to file
python v2/analyze_frequency_patterns.py data/timeseries.csv --output analysis_report.txt

Output:

  • Frequency distribution statistics
  • Devices with mixed frequencies
  • Anomalies (unexpected period counts)
  • Recommended tag counts
  • Data quality insights

2. parse_timeseries_csv_v2.py​

Purpose: Main processing script that creates DeviceTags and DeviceTagDatum records.

Usage:

python v2/parse_timeseries_csv_v2.py --csv <csv_file> [options]

Options:

  • --csv: Path to timeseries CSV file (required)
  • --dry-run: Run analysis without database writes
  • --no-lps: Skip LPS tag creation (consumption tags only)

Examples:

# Dry run (no database writes)
python v2/parse_timeseries_csv_v2.py --csv data/timeseries.csv --dry-run

# Production run (creates all tags and data)
python v2/parse_timeseries_csv_v2.py --csv data/timeseries.csv

# Create only consumption tags (skip LPS)
python v2/parse_timeseries_csv_v2.py --csv data/timeseries.csv --no-lps

Workflow​

Before processing, run the analysis script to understand your data:

python v2/analyze_frequency_patterns.py data/timeseries.csv --output analysis_report.txt

Review the report to:

  • Verify frequency distribution matches expectations
  • Identify devices with mixed frequencies
  • Check for data quality issues
  • Estimate number of tags that will be created

Step 2: Dry Run​

Test the processing logic without database writes:

python v2/parse_timeseries_csv_v2.py --csv data/timeseries.csv --dry-run

Review the output to:

  • Confirm tag names are correct
  • Verify record counts
  • Check for any errors or warnings

Step 3: Production Run​

Once satisfied with analysis and dry run:

python v2/parse_timeseries_csv_v2.py --csv data/timeseries.csv

Monitor the logs for:

  • Progress updates
  • Any warnings or errors
  • Final summary report

Configuration​

Database connection settings are in parse_timeseries_csv_v2.py:

DB_HOST = "localhost"
DB_PORT = 5432
DB_NAME = "dnv_10_7_2025"
DB_USER = "postgres"
DB_PASSWORD = "gq010102"

Update these values for your environment before running.

Frequency Classification​

The script automatically classifies frequencies based on periods per year:

Periods/YearFrequency TypeTag Suffix
1annual_annual_consumption / _annual_demand_lps
2semiannual_semiannual_consumption / _semiannual_demand_lps
4quarterly_quarterly_consumption / _quarterly_demand_lps
12monthly_monthly_consumption / _monthly_demand_lps

Tag Naming Convention​

Tags follow this pattern:

  • Consumption tags: {DeviceName}_{frequency}_consumption

    • Example: WTRMN00345_quarterly_consumption
    • Unit: cubic feet

  • LPS tags: {DeviceName}_{frequency}_demand_lps

    • Example: WTRMN00345_quarterly_demand_lps
    • Unit: liters/second

LPS Conversion​

Cubic feet consumption is converted to average liters per second (LPS) based on the time period:

Formula:

LPS = (consumption_cuft × 28.3168) / period_seconds

Period Seconds:

  • Monthly: 2,629,440 seconds (30.44 days average)
  • Quarterly: 7,888,320 seconds (3 months)
  • Semi-annual: 15,776,640 seconds (6 months)
  • Annual: 31,557,600 seconds (365.25 days)

Example:

Quarterly consumption: 1000 cubic feet
Liters: 1000 × 28.3168 = 28,316.8 L
LPS: 28,316.8 / 7,888,320 = 0.003589 L/s

CSV Requirements​

The input CSV must contain these columns:

  • REG_SERIAL: Device serial number (joins to Devices table)
  • READING_YEAR: Year of reading
  • READING_PERIOD: Period number (1-4 for quarterly, 1-12 for monthly, etc.)
  • CONSUMPTION: Consumption value in cubic feet

Optional columns (for reference/debugging):

  • ROUTE_CATEGORY
  • BILLING_CYCLE
  • ACCOUNT

Database Schema​

DeviceTags Table​

CREATE TABLE "DeviceTags" (
  "Id" SERIAL PRIMARY KEY,
  "Name" VARCHAR UNIQUE,
  "TagType" VARCHAR,
  "Unit" VARCHAR,
  "DeviceId" INTEGER REFERENCES "Devices"("Id")
);

DeviceTagData Table​

CREATE TABLE "DeviceTagData" (
  "DeviceTagId" INTEGER REFERENCES "DeviceTags"("Id"),
  "UnixTime" BIGINT,
  "Value" DOUBLE PRECISION,
  PRIMARY KEY ("DeviceTagId", "UnixTime")
);

Logs​

Both scripts create log files in the v2 directory:

  • analyze_frequency_patterns.log
  • parse_timeseries_csv_v2.log

Logs include:

  • Timestamps for all operations
  • Progress updates
  • Warnings and errors
  • Summary statistics

Troubleshooting​

"Missing required columns" error​

Cause: CSV doesn't have required columns Solution: Verify CSV has REG_SERIAL, READING_YEAR, READING_PERIOD, CONSUMPTION

"Devices not found in database" warning​

Cause: Some REG_SERIAL values don't exist in Devices table Solution: Review missing serials in log, ensure devices are loaded first

"Unexpected period count" warning​

Cause: Device has unusual number of readings per year (e.g., 3, 5-11, >12) Solution: Review anomalies in analysis report, may indicate data quality issues

Performance issues with large datasets​

Cause: Processing millions of records Solution:

  • Increase BATCH_SIZE in script (default: 10,000)
  • Run during off-peak hours
  • Consider processing in chunks by year

Validation Queries​

After processing, validate results with these SQL queries:

Check tag counts​

-- Should be 1:1 ratio between consumption and LPS tags
SELECT
  COUNT(CASE WHEN "TagType" LIKE '%_consumption' THEN 1 END) as consumption_tags,
  COUNT(CASE WHEN "TagType" LIKE '%_demand_lps' THEN 1 END) as lps_tags
FROM "DeviceTags";

Check datum counts per tag type​

SELECT
  dt."TagType",
  COUNT(*) as datum_count
FROM "DeviceTagData" dtd
JOIN "DeviceTags" dt ON dtd."DeviceTagId" = dt."Id"
GROUP BY dt."TagType"
ORDER BY dt."TagType";

Verify consumption/LPS pairs have same timestamps​

SELECT
  d."Name",
  dt_cons."Name" as consumption_tag,
  dt_lps."Name" as lps_tag,
  dtd_cons."UnixTime",
  dtd_cons."Value" as cuft,
  dtd_lps."Value" as lps
FROM "Devices" d
JOIN "DeviceTags" dt_cons ON d."Id" = dt_cons."DeviceId"
  AND dt_cons."TagType" LIKE '%_consumption'
JOIN "DeviceTags" dt_lps ON d."Id" = dt_lps."DeviceId"
  AND dt_lps."TagType" LIKE '%_demand_lps'
  AND REPLACE(dt_cons."TagType", '_consumption', '') = REPLACE(dt_lps."TagType", '_demand_lps', '')
JOIN "DeviceTagData" dtd_cons ON dt_cons."Id" = dtd_cons."DeviceTagId"
JOIN "DeviceTagData" dtd_lps ON dt_lps."Id" = dtd_lps."DeviceTagId"
  AND dtd_cons."UnixTime" = dtd_lps."UnixTime"
WHERE d."Name" = 'WTRMN00345'
ORDER BY dtd_cons."UnixTime";

Comparison with V1​

FeatureV1V2
Frequency detectionMetadata (ROUTE_CATEGORY)Actual data patterns
Mixed frequenciesNot supportedFully supported
Tags per device11-4 (as needed)
LPS tagsNoYes
Data processingRow-by-rowPandas bulk operations
Analysis toolsLimitedComprehensive

Dependencies​

Required Python packages:

pandas>=2.0.0
psycopg2-binary>=2.9.0

Install with:

pip install pandas psycopg2-binary

Support​

For issues or questions:

  1. Review logs for detailed error messages
  2. Check validation queries to verify data integrity
  3. Compare with v1 implementation in ../v1/ directory
  4. Review documentation in parent directory

References​

Last updated on by Jake