Differences

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

--- vd [2021/06/11 12:14] – [Set Column Types, Widths, Names, Importance] dblume
+++ vd [2021/08/06 13:24] – [Case Study: Exported CSV from PG&E] dblume
@@ Line 1: / Line 1: @@
 ====== VisiData ======
-**[[https://www.visidata.org/|VisiData]]** is a CLI table explorer with vim key bindings. These are notes to myself.
+**[[https://www.visidata.org/|VisiData]]** is an interactive CLI table explorer with vim key bindings. Here is [[https://jsvine.github.io/intro-to-visidata/index.html|a really great tutorial]]. These are notes to myself.
-====== Tips ======
+====== Source Table Recommendations ======
-===== Source Table Recommendations =====
 Gather data with more granularity than you think you need. You often need three tables:
@@ Line 13: / Line 11: @@
   - **Mappings from product ID to product name**: (Maybe from platform ID or from product ID.)
-===== Open VisiData and Set Data Types =====
+====== Open VisiData and Set Data Types ======
   $ vd logs.csv active_devices.csv id_name.csv
-==== Set Column Types, Widths, Names, Importance ====
+===== Set Column Types, Widths, Names, Importance =====
 ^ Key ^ Meaning ^
@@ Line 25: / Line 23: @@
 | # | Column is integer. (Set aggregator to sum.) |
 | + | Set aggregator, usually to "sum" |
-| ; | Extract regex to new column. Ex, ''(video|audio)'', ''(^..)'' or ''(^([STUVW]...|..))'' |
+| ; | Extract regex to new column. Ex, ''(video|audio)'', ''(^..)'' or ''(^([STUVW]%%...%%|..))'' |
 | %%^%% | rename the column. Might have to be "product_id" or "platform_id" |
 | - | Hide column |
@@ Line 36: / Line 34: @@
 | " | Open duplicate sheet with only selected rows |
-=== Protip: Use column view to set multiple columns at once ===
+==== Case Study: Exported CSV from PG&E ====
+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:
+    tail +6 pge_electric_interval_data.csv | vd -f csv -
+Then prepare your PG&E data like so:
+^ Key ^ Meaning ^
+| - | Hide columns TYPE, END TIME, UNITS and NOTES |
+| C | Go to column mode and... |
+| t | Select the DATE and START TIME columns |
+| & | Make a new column that merges them |
+| q, - | Quit the Column mode, hide DATE and START TIME columns |
+| O | Go to options mode and... |
+| e | Set ''disp_date_fmt'' to ''%Y-%m-%d %H:%M'' |
+| q | Quit options mode. |
+| @!, %, $ | Set DATE_START_TIME to date format and important, USAGE to float, COST to currency |
+| = | Add a column, enter ''COST/USAGE'', (Make it float with ''%'') |
+| %%^%% | Rename COST/USAGE to ''kWh rate'' |
+| . 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]]) |
+==== Protip: Use column view to set multiple columns at once ====
 ^ Key ^ Meaning ^
@@ Line 53: / Line 74: @@
 {{:general:active_devices.png?direct|}}
-===== Join Sheets =====
+====== Join Sheets ======
 Use ''!'' to set Key columns. Use ''F'' (or ''gF'') to remove dupes of Keys by making a Frequency Table.
@@ Line 63: / Line 84: @@
 | & | (or g&) Join sheets. Type "inner" for an inner join. |
 | gu | Next time you're in Sheets, "global unselect" to unselect the sheets you selected. |
+==== Protip: Remove insignificant noisy rows ====
+^ Key ^ Meaning ^
+| %%z|%% | Select rows matching Python expression. Type "blits > 10000" |
+| " | Make a new sheet with selected rows |
 And the merge of the logs and active devices sheets would look like:
@@ Line 70: / Line 97: @@
 Do a similar thing to join the Joined Sheet with the product Name sheet.
-===== Process Data =====
+====== Process Data ======
 In your final Joined sheet, add rows that are calculations of other rows.