User Tools

Site Tools


vd

Differences

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

Link to this comparison view

Next revision
Previous revision
Next revisionBoth sides next revision
vd [2021/06/11 11:45] – created dblumevd [2021/08/06 13:24] – [Case Study: Exported CSV from PG&E] dblume
Line 1: Line 1:
 ====== VisiData ====== ====== VisiData ======
  
-**[[https://www.visidata.org/|VisiData]]** is 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: 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.)   - **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   $ vd logs.csv active_devices.csv id_name.csv
  
-==== Set Column Types, Widths, Names, Importance ====+===== Set Column Types, Widths, Names, Importance =====
  
 ^ Key ^ Meaning ^ ^ Key ^ Meaning ^
Line 25: Line 23:
 | # | Column is integer. (Set aggregator to sum.) | | # | Column is integer. (Set aggregator to sum.) |
 | + | Set aggregator, usually 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" | | %%^%% | rename the column. Might have to be "product_id" or "platform_id" |
 | - | Hide column | | - | Hide column |
Line 34: Line 32:
 | d | Delete row. (I only use this on Sheets sheet.) | | d | Delete row. (I only use this on Sheets sheet.) |
 | , | Select all rows that match this column's value | | , | Select all rows that match this column's value |
 +| " | 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 ^ ^ Key ^ Meaning ^
Line 44: Line 66:
 | q | Leave that table. | | q | Leave that table. |
  
-===== Join Sheets =====+For example, your "logs.csv" would end up looking like: 
 + 
 +{{:general:logs.png?direct|}} 
 + 
 +Your active_devices.csv would look like this: 
 + 
 +{{:general:active_devices.png?direct|}} 
 + 
 +====== Join Sheets ======
  
 Use ''!'' to set Key columns. Use ''F'' (or ''gF'') to remove dupes of Keys by making a Frequency Table. Use ''!'' to set Key columns. Use ''F'' (or ''gF'') to remove dupes of Keys by making a Frequency Table.
  
 ^ Key ^ Meaning ^ ^ Key ^ Meaning ^
 +|   | The key columns to join have to match names. Check and rename with ''^''. |
 | S | Go to Sheets sheet | | S | Go to Sheets sheet |
 | t | (toggle) Select the sheets you want to join by key columns. | | t | (toggle) Select the sheets you want to join by key columns. |
 | & | (or g&) Join sheets. Type "inner" for an inner join. | | & | (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. | | 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:
 +
 +{{:general:merge_logs_active.png?direct|}}
  
 Do a similar thing to join the Joined Sheet with the product Name sheet. 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. In your final Joined sheet, add rows that are calculations of other rows.
Line 72: Line 113:
 | . | (''g.'' for multiple columns) Make a graph based on Key column. | | . | (''g.'' for multiple columns) Make a graph based on Key column. |
  
-Now you should have some basic info to keep exploring.+{{:general:joined_sheets_pct.png?direct|}} 
 + 
 +Now you should have some basic info to keep exploring. When you want to save what you've done: 
 + 
 +^ Key ^ Meaning ^ 
 +| Ctrl+s | Save sheet | 
 +| Ctrl+d | Save command log (cmdlog). Use extension ".vd". You can replay what you've done later! | 
 + 
 +The commandlog is a CSV file too. The first lines are the open file commands. So to replay, you only have to specify the command log like so: 
 + 
 +  $ vd --play=my_cmdlog.vd --replay-wait=0.5
  
  
vd.txt · Last modified: 2023/05/17 14:00 by dblume