opower PG&E csv file cleanup

index.md

Usage

sh opower-csv-to-csv < pge_*.csv

Purpose

Take your PG&E csv data export (pge_electric_interval_data_NNNNNNNNNN_YY-MM-DD_to_YYYY-MM-DD.csv) for the date range you want, and feed it through this script to clean it up from a file looking a bit like:

<U+FEFF>Name,MY NAME
Address,"NNN My Street, My City, STATE ZIP"
Account Number,NNNNNNNNNN
Service,NNNNNNNNNN

TYPE,DATE,START TIME,END TIME,USAGE,UNITS,COST,NOTES
Electric usage,YYYY-MM-DD,00:00,00:14,0.00,kWh,,

to a well-formed basic csv (assuming your NOTES column just contains nothing or "* This data was estimated" notes, here turned into a boolean 0/1):

DATE,START TIME,END TIME,USAGE,COST,ESTIMATED
YYYY-MM-DD,00:00,00:14,0.00,$0.00,0

(Getting rid of the probably always duplicated columns stating that the row is "Electric Usage" and in kWh.)

opower-csv-to-csv

#! /bin/bash
sed -E -n '/^TYPE/,$ s/(^[^,]*,|UNITS,|kWh,)//gp' \
| sed 's/NOTES$/ESTIMATED/;s/,$/,0/;s/,\* This data was estimated$/,1/'