Is there a better way to understand the connections between derived sheets?

[frosencrantz]

I was reminded recently about an issue in software. Users like me will come up with a solution to a problem they have and say they want that solution. But there might be a better, more workable solution that could be found if the original problem is explained. This is my effort to explain my problem.

This thread is to give some context on #1647 which is an idea on how to get better naming for a derived sheet for the simple case of selecting on one cell value, and creating a subsheet.

I wanted to explain the base problem. I'm having an issue with Visidata where it can become difficult to keep track of the changes I've made to my data as I progress through multiple levels of sub-sheets. Once I go too deep or want to do a parallel or similar query, I often get lost and have to spend time trying to find the sheet I want or recreate it. One problem is that the naming of derived sheets doesn't provide enough detail, and it can be hard to see which sheet a sheet is derived from, or how related sheets are derived from their parent, or see their family tree. Some of this information is there, I just want it to be easier to track.

If I build up a CLI pipeline, there would be a lot of state in the commands and the iterations. If I created a SQL query there is a lot of state there within the query.

I’m hoping others have ideas on how we might be able to improve this, such as by implementing more informative sheet naming conventions or a way to view the sheet hierarchy in a more visual or intuitive way?

PS. Also, very happy to see there has been a flurry of fixes. Thanks Saul and Anja!

[saulpw]

Hey @frosencrantz, this is very useful! You're totally right that explaining the problem, while often tricky to capture the subtle essence of it, can be more fruitful than asking for a limited solution first.

In this case, there are a couple of ideas I've been noodling over that might be relevant:

1. a new "sheets panel" that would come up above the status bar, above where the current sheet name is. Maybe it only has the top 3-5 sheets, but the idea is that sometimes your context involves multiple sheets, and having to go to a separate sheets sheet is jarring to your current context. This might be useful particularly with joins, as you can now join the top 2 sheets with & from the top sheet (not sure if people know about this command). The sheets panel would include the sheet shortcuts so you can easily move between them; kind of like the bottom tabs in Excel.
2. Since derived sheets form a linear path from the leaf/most-derived sheet up to the root/source sheet, perhaps the sheet stack should really be a view of these leaves, instead of all sheets along all paths. We can make it so that pushing a derived sheet would replace its parent with itself, and q out of the derived sheet would replace it with its parent, so that it would still feel very much like it does currently with the sheet stack. And the backtick could repush the parent or g` could repush the root source, if you really wanted to get them on the stack. Then you'd be always dealing with the most-specific sheets, and could feel free to ignore the intermediates that got you there.
3. I find the sheet names to be utterly useless for navigating once I've drilled down more than a couple of levels. In recent versions we made names be a list instead of a string, and so we could display e.g. the first and last name components only. We could also change the _selectedref to something shorter, and ideally remember something about the selection in order to make the name be more meaningful.
4. But really, navigating by sheet name like this is awkward. Perhaps what we want is a kind of linear notebook, where you are working within VisiData in order to come up with a single cell in the notebook, and then when you've found the interesting bit (whether a sheet, row, or single value), you capture that into the cell, and then it moves onto the next cell. So you could then export this notebook as e.g. .ipynb, and ultimately reload the notebook and revisit previous cells (ideally with reloaded or different data).

How do you feel about these suggestions towards your problem, taken either individually or in concert?

[frosencrantz]

I thought I had already replied to this on Sunday night when I replied to the macros ticket.
Thanks for the great response Saul. There is a lot here to take in, and I think I like much of what you are suggesting.

It is common for me to have clean data, but for the questions I’m trying to answer I might need to make some tweaks, that might be starting with a subset, unfurling/expanding nested data. There might be some minor changes like adding a column with the filename extensions taken from a filename column or the query part of a URL column. I’ll call this polishing data. This ends up being my real starting point, but might already be a few sheets in. Then I’ll use Visidata to run down a quick path of sheets to find part of the answer to my question, I’ll want to keep this sheet available, and quickly accessible. I’ll then need to go back, typically to that polished data sheet but also possibly something down the quick path, and track down a separate part of the answer. Each of the next nth attempts will be similar.

I’ve been trying to remember to rename some sheets once I get a useful part of the answer, or to this polished data sheet. But I forget to do it, plus it takes time to come up with something useful... That’s why if the names were more descriptive it would help identify the correct spot to go back to. I do really like that when diving into rows in a FreqTableSheet that VisiData does use the row value to generate the name. So I like the suggestion in #3 to somehow remember about the selection and include that in the name. Also, I would be ok with a way to override the default of “_selectedref”. I think if it were an option, I would likely make it a symbol or two like a pipe or a greater than. Also, I guess if it was a whole table was a copy, it would make sense to include that in the name. I've thought about having a naming class like the Colorizer classes, that could take the parent(s) and the new child and some state information and create a new name for the new sheet. It might even make sense for it to rename the parent, and reuse the parent's name for the new child. I mostly thought about this in the context of columns, where I wanted derived columns to take on the parent's name, and update the parent's name, but I think it could also apply to sheet naming.

I also liked the suggestion in #2 might provide a lot of help with quickly creating a lot of sheets without cluttering the SheetsSheet. Making it easy to ignore these intermediate sheets, but making them accessible as needed/wanted.

This idea made me think it might be useful to have a way to “put a pin in that” command that with a single command/key you could say this is an important sheet, basically a boolean field added to sheet metadata, A pinned sheet would have additional properties, like it might also imply that the sheet should be guarded, and it should be possible to see only pinned sheets, PinnedSheetsSheet. This might help reduce the need to rename sheets, just pin the ones’ of interest.

I need to think some more about these suggestions. I like #1 and #4. Like #4 does sound very much like what I explained in my common case.

Also, I like #1. I’ve thought for a while it would be useful if VisiData had tabs like Excel. I’ve thought it should be possible to populate the tabs via an IndexSheet or a FreqTableSheet, and with the easy jumping between sheets you talked about. Also, it seemed like #1 would be similar to how it is possible to build up a pipeline in the shell from multiple commands. Going left would go back a step, going right would go further one step. Though it would still need to be clearer about which each of these iterations is.

There is a lot to think about.

[frosencrantz]

@saulpw, I was wondering if some form of this will make it into 3.0?

Without trying the changes I do think that #3 would be very helpful. If it was possible to remember something about the current selection. There is a simple suggestion of this in #1647. I think for simple cases it will be possible to name the selection but what happens in cases like:

• Select cursor to bottom or top of sheet
• Select with search or comma multiple times
• Select with Python expressions
• Toggling or flipping the selection gt

Maybe it would be useful if the name also included the percentage or fraction of the rows selected of the original sheet

I like #2 it feels like it could help with the overload of sheets in the SheetsSheet! One thing I would expect is that there would still be a way to see all the sheets, even the hidden ones.

I like all the suggestions. I feel like #3 and #2 would be the most useful.

[saulpw]

(3) seems very doable. (2) had some issues, though I haven't given up on it completely yet. I think if I were to put sincere effort into this, I would think hard about a new model for structuring a larger-scale VisiData workflow, something more like (4). It's in the background for sure.

[frosencrantz]

I like #4 linear notebook results, but what would the UI be like? Perhaps some sort of IndexSheet, and the user would tag a sheet as belonging to the IndexSheet (notebook)? How would it be different from the SheetsSheet?

[saulpw]

I'm thinking a sheet with 4 columns: name, value, explanation, code. So you wind up saving/pinning a value (which could be a single value, or a column of values, or a sheet), give it a name, and the code column would have a SQL query or Python code or the VisiData commands required to generate the value from the source data. And this could be saved in notebook form, and ideally loaded from the same notebook form (so roundtrippable).

[frosencrantz]

I would need to try it. This could be useful. Would this notebook sheet be able to regenerate the data given new starting data? Would this be similar to a Jupyter notebook in that sense?

[saulpw]

That would be ideal! It might be ambitious, but I think this is the general direction I want to go for reproducibility--generating code in another language to get the desired result, and then using that code as the source expression to a VisiData loader.

[daviewales]

I have been hoping for a future where I can press 10 keys in visidata, then save as python to get a Python script (or duckdb sql?) which will recreate the current sheet given the starting sheets. I'm hoping the Ibis and vdsql work will tend in this direction. This could work even with the current UI, if each command maps to intermediate code (Ibis, duckdb, polars?), then runs that code, and each sheet remembers the code used to produce it from the previous sheet/s.

However, because of joins, the data structure to hold this together will need to be a DAG, rather than a linear table.
Or maybe not, because there is a linear sequence of commands, even if some of the commands are joins which take input from multiple tables?