User Tools

Site Tools

Trace: • private • Coffee • Windows 10 Setup • Minecraft • Apple • Chrome • VisiData
vd-pge

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
vd-pge [2021/08/09 23:48]
dblume [Set Column Types, Widths, Names, Importance] 		vd-pge [2021/08/10 16:55] (current)
dblume [Select Rows to Analyze]
Line 1: Line 1:
 ====== Analyzing PG&E data with VisiData ====== ====== Analyzing PG&E data with VisiData ======
  
-PG&E CSVs come with 5 rows of metadata followed by Type, Date, Start Time, End Time, Usage, Units, Cost, Notes columns. Delete the five rows of metadata in a text editor, or use ''tail'' to remove them like so:+At the PG&E website, you can download a CSV table of your energy usage. Click "Energy Usage Details" and then the "Green Button" at their site to download your data. 
 + 
 +{{:general:vd-pge-green-button.png?direct|}} 
 + 
 +PG&E CSVs come with 5 rows of metadata followed by ''Type'', ''Date'', ''Start Time'', ''End Time'', ''Usage'', ''Units'', ''Cost'', ''Notes'' columns. Delete the five rows of metadata in a text editor, or use ''tail'' to remove them like so (or with ''tail -n +6'' for older tail):
  
     tail +6 pge_electric_interval_data.csv | vd -f csv -     tail +6 pge_electric_interval_data.csv | vd -f csv -
Line 13: Line 17:
 | = | Create a new column for weekday. Enter "DATE.weekday()" | | = | Create a new column for weekday. Enter "DATE.weekday()" |
 | = | Create a new column for weekday name. Enter "DATE.strftime('%A')" | | = | Create a new column for weekday name. Enter "DATE.strftime('%A')" |
-| %%^%% | Rename "START TIME" to "START_TIME" because column names should be one word for Python expressions. |+| %%^%% | Rename "START TIME" to one word since column names should be one word for Python expressions. |
 | C | Go to column mode and... | | C | Go to column mode and... |
 | t | Select the DATE and START TIME columns | | t | Select the DATE and START TIME columns |
 | & | Make a new column that merges them | | & | Make a new column that merges them |
-| q, - | Quit the Column mode, hide DATE and START TIME columns |+| - | (Optionally delete some columns' rows from column mode to really delete columns from the sheet.) | 
 +| q, - | Quit the Column mode, hide (sets their width to 0) DATE and START TIME columns |
 | O | Go to options mode and... | | O | Go to options mode and... |
-| e | Set ''disp_date_fmt'' to ''%Y-%m-%d %H:%M'' |+| e | Set ''disp_date_fmt'' to ''%Y-%m-%d %H:%M'' (or do it in column mode's ''fmtstr'' for the one column.) |
 | q | Quit options mode. | | q | Quit options mode. |
 | @!, %, $ | Set DATE_START_TIME to date format and important, USAGE to float, COST to currency | | @!, %, $ | Set DATE_START_TIME to date format and important, USAGE to float, COST to currency |
Line 25: Line 30:
 | %%^%% | Rename COST/USAGE to ''kWh rate'' | | %%^%% | Rename COST/USAGE to ''kWh rate'' |
 | H,V and _ | Move and resize the columns. | | H,V and _ | Move and resize the columns. |
 +
 +You should end up with a sheet with usable data. Note that ''@'' means "date format", ''#'' means "integer", ''%'' means "float" and ''$'' means "currency". Also note that we haven't set aggregator types yet. We'll use those for frequency tables.
 +
 +{{:general:vd-pge-data-ready.png?direct|}}
 +
 +===== View average usage by hour of day =====
 +
 +^ Key ^ Meaning ^
 +| - | Hide all columns, leaving only ''time'', ''USAGE'', and ''COST'' |
 +| + | On ''USAGE'' and ''COST'', set aggregator type to... say, ''avg'' for average. |
 +| F | On the ''time'' column, tap ''F'' to get a frequency table. |
 +| [ | The frequency table is sorted on the new aggregations, on ''time'' column, use ''['' to sort by time again. |
 +| - | Remove the new ''count'' column. |
 +| C, e, "%H:%M", q, @! | Go to column mode, set ''time'' column format to ''%H:%M'', exit column mode, set ''time'' to date format. |
 +| g. | Display a graph with all visible columns. |
 +| -, + | Zoom in and out of the graph. |
 +| q, q | Quit the graph, quit the frequency sheet |
 +| C, t, ge, 8 | Column mode, toggle ''date_time'', ''DATE'', ''wday_num'', ''wday'', edit width to 8, to see these columns again. |
 +
 +{{:general:vd-pge-graph-by-hour-of-day.png?direct|}}
 +
 +===== View average usage by day of week =====
 +
 +^ Key ^ Meaning ^
 +| - | Hide all columns, leaving only ''wday_num'', ''time'', ''USAGE'', and ''COST'' |
 +| C, t, & | Column mode, toggle ''wday_num'', ''time'', make a new column that joins them. |
 +| = | With ''TIME'' as string format, make a new row as ''int(int(TIME[:2])/4)*4'' |
  
 ===== Select Rows to Analyze ===== ===== Select Rows to Analyze =====
 +
 +Directly selecting ranges of rows:
  
 ^ Key ^ Meaning ^ ^ Key ^ Meaning ^
-| %%|%% | On DATE_START_TIME column, select by regex, like "-11-" for November | +| s,t,u | Select/toggle/unselect current row. | 
-| " | To open a new sheet with just those selected rows. | +| , | Select all rows matching value of current cell | 
-| q | Quit the new sheet mode. |+| gs,gt,gu | ... all rows | 
 +| zs,zt,zu | ... from top to cursor | 
 +| gzs,gzt,gzu | ... from cursor to bottom (Or... ''zs,gt'', one less thing to remember) | 
 +| " | Open duplicate sheet with only selected rows. | 
 + 
 +Select based on a pattern:
  
 ^ Key ^ Meaning ^ ^ Key ^ Meaning ^
-| %%z|%% | select rows matching Python expr "DATE_START_TIME [-3:] == '-07'" |+| %%|%% | On DATE_START_TIME column, select by regex, like "-11-" for November | 
 +| %%z|%% | Select rows matching Python expr "DATE_START_TIME [-3:] == '-07'" |
 | " | To open a new sheet with just those selected rows. | | " | To open a new sheet with just those selected rows. |
-| q | Quit the new sheet mode. |+
  
 ===== Visualize Data ===== ===== Visualize Data =====
Line 43: Line 83:
 | . or g. | Select columns to graph them. Notice rate changes. Notice times of high use. | | . or g. | Select columns to graph them. Notice rate changes. Notice times of high use. |
 | +, - | Navigate with hjkl, zoom in and out ([[https://www.visidata.org/docs/graph/|more]]) | | +, - | Navigate with hjkl, zoom in and out ([[https://www.visidata.org/docs/graph/|more]]) |
 +
 +====== Further Resources ======
 +
 +I have another tutorial at [[vd]] that merges/joins three CSV tables.
vd-pge.1628578113.txt.gz · Last modified: 2021/08/09 23:48 by dblume

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki