Skip to main content

Quick Start Guide - Timeseries Processing V2

This guide will get you up and running with the new v2 timeseries processing in minutes.

Prerequisites​

  1. Python 3.8+ installed
  2. PostgreSQL database with Devices table populated
  3. Timeseries CSV file with required columns

Installation​

Step 1: Install Dependencies​

pip install pandas psycopg2-binary

Or add to your requirements.txt:

pandas>=2.0.0
psycopg2-binary>=2.9.0

Step 2: Configure Database Connection​

Edit v2/parse_timeseries_csv_v2.py (lines 39-43):

DB_HOST = "localhost"
DB_PORT = 5432
DB_NAME = "your_database_name"  # Change this
DB_USER = "postgres"
DB_PASSWORD = "your_password"    # Change this

Do the same for v2/validate_results.py if you plan to use validation.

Basic Usage​

Generate and process test data to verify everything works:

# Generate test data
python v2/generate_test_data.py --output test_data.csv --devices 10

# Analyze the test data
python v2/analyze_frequency_patterns.py test_data.csv

# Process in dry-run mode (no database writes)
python v2/parse_timeseries_csv_v2.py --csv test_data.csv --dry-run

# If dry-run looks good, process for real
python v2/parse_timeseries_csv_v2.py --csv test_data.csv

# Validate results
python v2/validate_results.py

Option 2: Process Real Data​

# Step 1: Analyze your data first
python v2/analyze_frequency_patterns.py your_data.csv --output analysis_report.txt

# Step 2: Review the analysis report
cat analysis_report.txt  # or open in text editor

# Step 3: Dry-run to preview what will be created
python v2/parse_timeseries_csv_v2.py --csv your_data.csv --dry-run

# Step 4: Process for real
python v2/parse_timeseries_csv_v2.py --csv your_data.csv

# Step 5: Validate results
python v2/validate_results.py --check-conversions

What Gets Created​

For each device with readings, you get:

Consumption Tags​

  • One tag per frequency type the device has used
  • Example: WTRMN00345_quarterly_consumption
  • Unit: cubic feet
  • Contains total consumption per period

LPS Tags (Liters Per Second)​

  • Parallel tag for hydraulic modeling
  • Example: WTRMN00345_quarterly_demand_lps
  • Unit: liters/second
  • Contains average flow rate for the period

Example Output​

If device WTRMN00345 has:

  • Annual readings from 2018-2019 (2 years)
  • Quarterly readings from 2020-2023 (4 years)

You get:

✓ WTRMN00345_annual_consumption (2 data points)
✓ WTRMN00345_annual_demand_lps (2 data points)
✓ WTRMN00345_quarterly_consumption (16 data points)
✓ WTRMN00345_quarterly_demand_lps (16 data points)

Common Workflows​

Workflow 1: Initial Data Load​

# 1. Ensure devices are loaded into database first
python v1/parse_devices_csv.py devices.csv

# 2. Analyze timeseries patterns
python v2/analyze_frequency_patterns.py timeseries.csv --output analysis.txt

# 3. Review analysis, then process
python v2/parse_timeseries_csv_v2.py --csv timeseries.csv

# 4. Validate
python v2/validate_results.py --check-conversions

Workflow 2: Incremental Update​

If you already have data and want to add more:

# Process new data (ON CONFLICT will update existing records)
python v2/parse_timeseries_csv_v2.py --csv new_timeseries.csv

# Validate
python v2/validate_results.py

Workflow 3: Consumption Only (Skip LPS)​

If you don't need LPS tags:

python v2/parse_timeseries_csv_v2.py --csv timeseries.csv --no-lps

Validation​

Quick Validation​

python v2/validate_results.py

Checks:

  • ✓ Tag counts (consumption vs LPS should be 1:1)
  • ✓ Tag pairing (each device has matching pairs)
  • ✓ Timestamp consistency
  • ✓ Frequency distribution

Detailed Validation with Spot Checks​

python v2/validate_results.py --check-conversions --sample-size 20

Also checks:

  • ✓ LPS conversion accuracy (spot-checks calculations)

Device-Specific Validation​

python v2/validate_results.py --sample-device WTRMN00345

Shows detailed summary for a specific device.

Troubleshooting​

Issue: "Missing required columns"​

Problem: CSV doesn't have required columns

Solution: Ensure your CSV has:

  • REG_SERIAL
  • READING_YEAR
  • READING_PERIOD
  • CONSUMPTION

Issue: "Devices not found in database"​

Problem: REG_SERIAL values don't exist in Devices table

Solution:

  1. Load devices first: python v1/parse_devices_csv.py devices.csv
  2. Check that REG_SERIAL in timeseries matches SerialNumber in Devices

Issue: "Unexpected period count: 3"​

Problem: Device has 3 readings in a year (unusual)

Solution: This is a data quality issue. The script will:

  • Create a tag with type unknown_3_consumption
  • Log a warning
  • Continue processing
  • You should investigate the source data

Issue: Performance is slow​

Problem: Large dataset taking too long

Solutions:

  1. Increase batch size in script (edit BATCH_SIZE constant)
  2. Run during off-peak hours
  3. Process in chunks by year range

Understanding the Logs​

Logs are written to v2/*.log files:

Key Log Messages​

Good signs:

✓ Tag counts are balanced (1:1 ratio)
✓ All timestamps are consistent
✓ All sampled conversions are accurate

Warnings to review:

⚠ 150 device-frequency combinations not found in Devices table
⚠ Unexpected period count: 3
⚠ Devices with multiple frequencies: 245

Errors to fix:

✗ Tag counts are NOT balanced!
✗ Some conversions have errors

SQL Queries for Manual Validation​

Check what was created​

-- Count tags by type
SELECT
  "TagType",
  COUNT(*) as count
FROM "DeviceTags"
WHERE "TagType" LIKE '%consumption' OR "TagType" LIKE '%demand_lps'
GROUP BY "TagType"
ORDER BY "TagType";

-- Sample device data
SELECT
  d."Name",
  dt."TagType",
  COUNT(dtd."UnixTime") as data_points
FROM "Devices" d
JOIN "DeviceTags" dt ON d."Id" = dt."DeviceId"
LEFT JOIN "DeviceTagData" dtd ON dt."Id" = dtd."DeviceTagId"
WHERE d."Name" = 'WTRMN00345'
GROUP BY d."Name", dt."TagType"
ORDER BY dt."TagType";

Verify LPS conversions​

-- Check a few LPS calculations manually
SELECT
  d."Name",
  dt_cons."TagType" as freq_type,
  dtd_cons."Value" as cuft,
  dtd_lps."Value" as lps,
  (dtd_cons."Value" * 28.3168) as liters,
  -- For quarterly: 7,888,320 seconds
  (dtd_cons."Value" * 28.3168 / 7888320) as expected_lps
FROM "Devices" d
JOIN "DeviceTags" dt_cons ON d."Id" = dt_cons."DeviceId"
  AND dt_cons."TagType" = 'quarterly_consumption'
JOIN "DeviceTags" dt_lps ON d."Id" = dt_lps."DeviceId"
  AND dt_lps."TagType" = 'quarterly_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"
LIMIT 5;

Next Steps​

Once you've successfully processed your data:

  1. Integrate with your application

    • Query DeviceTags to get available tags per device
    • Query DeviceTagData for timeseries data
    • Use LPS tags for hydraulic modeling

  2. Set up scheduled updates

    • Create cron job or scheduled task
    • Run analysis + processing monthly/quarterly
    • Run validation after each update

  3. Monitor data quality

    • Review analysis reports regularly
    • Track devices with mixed frequencies
    • Investigate anomalies

Getting Help​

Summary Commands​

# Complete workflow
pip install pandas psycopg2-binary
python v2/analyze_frequency_patterns.py data.csv --output report.txt
python v2/parse_timeseries_csv_v2.py --csv data.csv --dry-run
python v2/parse_timeseries_csv_v2.py --csv data.csv
python v2/validate_results.py --check-conversions

That's it! You're now ready to process timeseries data with v2. 🚀

Last updated on by Jake