vd
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
vd [2021/06/11 11:58] – dblume | vd [2021/08/06 13:24] – [Case Study: Exported CSV from PG&E] dblume | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== VisiData ====== | ====== VisiData ====== | ||
- | **[[https:// | + | **[[https:// |
- | ====== 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 " | | + | Set aggregator, usually to " | ||
- | | ; | Extract regex to new column. Ex, '' | + | | ; | Extract regex to new column. Ex, '' |
| %%^%% | rename the column. Might have to be " | | %%^%% | rename the column. Might have to be " | ||
| - | 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' | | , | Select all rows that match this column' | ||
+ | | " | 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 +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 '' | ||
+ | | q | Quit options mode. | | ||
+ | | @!, %, $ | Set DATE_START_TIME to date format and important, USAGE to float, COST to currency | | ||
+ | | = | Add a column, enter '' | ||
+ | | %%^%% | Rename COST/USAGE to '' | ||
+ | | . or g. | Select columns to graph them. Notice rate changes. Notice times of high use. | | ||
+ | | +, - | Navigate with hjkl, zoom in and out ([[https:// | ||
+ | |||
+ | ==== Protip: Use column view to set multiple columns at once ==== | ||
^ Key ^ Meaning ^ | ^ Key ^ Meaning ^ | ||
Line 52: | Line 74: | ||
{{: | {{: | ||
- | ===== Join Sheets ===== | + | ====== Join Sheets |
Use '' | Use '' | ||
^ 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 " | | & | (or g&) Join sheets. Type " | ||
| gu | Next time you're in Sheets, " | | gu | Next time you're in Sheets, " | ||
+ | |||
+ | ==== 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: | And the merge of the logs and active devices sheets would look like: | ||
Line 68: | Line 97: | ||
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 86: | Line 115: | ||
{{: | {{: | ||
- | Now you should have some basic info to keep exploring. | + | Now you should have some basic info to keep exploring. |
+ | |||
+ | ^ Key ^ Meaning ^ | ||
+ | | Ctrl+s | Save sheet | | ||
+ | | Ctrl+d | Save command log (cmdlog). Use extension " | ||
+ | |||
+ | 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