Freq sheet: bin by date

[geekscrapy]

Is it possible to bin by date range like you can bin by integer?
Would this be useful for anyone else?

[edwardsnjd]

👍 Yes, that would be great. One of my most common Visidata actions is adding a derived column in order bucket data by month.

I suppose one difficulty of creating this feature would be choosing/specifying the bucket size. 🤔

I often choose to bucket by month (via an expression column date_column_n.strftime("YYYY-MM")) but have also wanted to bucket by week, by day of the week, or by hour of the day. My choice there isn't related to the overall range of time spanned by the data, so I don't think any automatic binning solution would cut it.

I don't have a good idea for how to configure the date bucketing in the same way we can for numerical binning either since I don't know if a global default makes sense.

If the frequency analysis sheet was interactive then I could imagine a plug-in/feature offering a set of key bindings to toggle between some date binning strategies. Maybe something like the menu based picker for aggregators? Although it'd be nice to retain the flexibility to specify a date formatter/function for custom date binning.

PS. Thank you to everyone in the Visidata community for this most excellent tool. ❤️

[saulpw]

Buckets are made based on the displayed string, so we could make a command to increase/decrease precision in the fmtstr for the current column, and so you could easily change the precision and rerun the freq. I could see zh/zl being used for this purpose on numeric columns, and doing the correct thing for both floats and datetimes.

H/t to @andycraig who made a change_precision plugin that inspired this idea.

[geekscrapy]

When you say increase and decrease precision, it would +- based on ms, s, h, day, M, Y etc?
I'd need to suggest based on an arbitrary amount of time really.
Does the numeric binning use displayed string?

[saulpw]

When you say increase and decrease precision, it would +- based on ms, s, h, day, M, Y etc?

Yes, going through Y Y-Q Y-M Y-W Y-M-D Y-M-D H Y-M-D H:M Y-M-D H:M:S Y-M-D H:M:S....

These are just format strings, nothing fancier.

Numeric binning still uses the underlying number, this would be for non-numeric binning.

[edwardsnjd]

These are just format strings, nothing fancier.

Ah yes, good call-out, the approach I mentioned isn't actually binning by time interval in the same sense as numeric binning because it doesn't include any missing time intervals.

Perhaps my approach doesn't do what @geekscrapy originally intended. Could there be room for both i.e. format string based binning and a more intuitive timespan based binning?

I could see zh/zl being used for this purpose on numeric columns, and doing the correct thing for both floats and datetimes.

That sounds great to me.

With a format based binning approach it would feel quite natural to me to have a configuration for the list of the date format strings to cycle through.

That would allow for less common options that don't fit into the broadening/narrowing precision UX e.g. I could include A in the list of formats to bin by day of the week.

[geekscrapy]

So we'd be looking to bin by a certain "period of time". When should these periods start? At the earliest date in the column?
We could use datetime.timedelta for each period. But it might be quite slow to get before starting binning

Maybe a solution to this would be to take the first date to be binned and center the timedelta on it and work from there? I've not looked at the current binning code 🤷‍♂️

[edwardsnjd]

❤️ Turns out visidata already does binning on date columns including missing intervals. I obviously hadn't tried that ever/before binning on a new generated strftime column!

I think the above discussion still makes sense for my use case because of bin alignment and formatting.

[saulpw]

When in doubt, try it oubt! :)

[geekscrapy]

❤️ Turns out visidata already does binning on date columns including missing intervals. I obviously hadn't tried that ever/before binning on a new generated strftime column!

I think the above discussion still makes sense for my use case because of bin alignment and formatting.

You got a hint how to do this? Please 🙏 😁

[edwardsnjd]

❤️ Turns out visidata already does binning on date columns including missing intervals.

You got a hint how to do this? Please 🙏 😁

Yep, Visidata used date bins for me with the following:

1. Turn on numerical binning in the options sheet: O then edit the option
2. Return to data sheet: q
3. Mark a column as dates: @
4. View frequency sheet for that column: F
5. No step 5, the date range is covered by date range bins in much the same way a numeric range would be.

Note that the bin size and alignment isn't as easy to control as it would be with the previously discussed features, so I think this binning is suitable for quick data overview rather than more detailed analysis. Still, great that date binning is already a feature!

[saulpw]

@geekscrapy do you have options.numeric_binning=True in your .visidatarc?

I'm thinking about making True the default, would anyone miss the old default? (It would mean that you would have to type the column explicitly with ~ first, to get the current behavior.)

[frosencrantz]

@saulpw I wonder if there could be two commands one that creates a binned frequency sheet and one that doesn't, rather than have it as an option. This is because I rarely go to the option sheet to toggle values, it takes a lot of steps.

If you want the binned Frequency sheet, you would just run the binning command, if not use the non-binning command.

Then another suggestion is that once you are in a frequency sheet there should be commands to toggle between binned and unbinned. One issue with the binning, is the size is also option based. I sort of wished once you are on a frequency sheet you could edit the binning; binned yes/no, if binned, number of bins. There are times I just wish the number of bins was based on the number of available screen rows to show all values without scrolling. Also, I sort of want string value binning, think of the pages of a Dictionary, where the words are ordered alphabetically, and then the bins are the words on a page.

[saulpw]

This is an interesting design discussion. As you mention, there are several approaches to handling variations on a command:

1. options to configure the command
2. different commands to create the sheet
3. commands on the sheet to toggle the behavior
4. a modal dialog "wizard" to select possible options and variants

Using options (1) is easiest for development, because it requires only two lines of code, both local to the feature: one to specify the option (including a basic helpstring), and another at point-of-use, to toggle the behavior. However, it can be cumbersome for the user to change an option at run-time, as you say.

Having different commands (2) is not much more difficult; it just requires the command function to take the option value (or proxy) as a parameter. However, a plethora of commands means that there can't be keybindings for all of them; so you'd have to execute them by longname, or create your own keybindings, or browse through a huge menu to find them. Placing all the commands in reasonable spots in the menu is also a challenge.

Creating more commands to toggle behavior after-the-fact (3) would be really nice, esp since you may not know which variant you want until you see the results. However, this also requires the most work, both for technical design and command discovery.

One possibility that hasn't been mentioned yet, is the traditional "wizard" dialog (4) that would present a modal form (with reasonable defaults) and allow you to explore and select various options. This is probably the best option, as it solves the issue of discovery, and I can easily see just two commands: Shift+F for the default instant freq table, and z Shift+F for the freq table dialog. Even g Shift+F might be rolled up into this dialog, and if we were super advanced, I could imagine the dialog being able to use some other set of columns (maybe the ones on the clipboard that were most recently copied from the Columns Sheet).

The idea for string-value binning is a clever one, too. It definitely makes the case that the two strategies aren't truly distinguished by numeric vs non-numeric data.

[frosencrantz]

I like the idea of modal dialog as it solves the issue of discovery and makes it easier for users to explore and select options. The ability to present a modal form with defaults taken from the options seems like it would be very useful. There are many times I wish I had more control at the time of performing a particular operation. So this is exciting.

Though I think the devil is in the details and the ease of use of the modal dialog is important. Like I feel all defaults should initially come from the options, not sure if they should be sticky during a session, so that if you change it, subsequent visits keep the last results. I would like to make sure it stays out of my way except for when I want to change away from the defaults. So it should be easy to accept the defaults like with Enter. Both keyboard and mouse navigation feel like they are important. I would be happy with the mouse or Tab/Up/Down Arrows being able to move between input boxes/selectors. Input boxes should be possible to edit immediately, I shouldn’t have to type ‘e’ to get into edit mode for an input box. Maybe there should be a way to reset defaults to initial values, after you made edits. I expect that there are many little details that make a great modal dialog that I’m missing.

[saulpw]

Yes, these details are all good, thanks for calling them out. One big question for me is, how much should it take over the interface? Maybe a lot of these wizards could just set the sidebar to a form. For example z Shift+F would give instructions and show the various options and their defaults, and then you could follow the instructions on the sheet you already have loaded, and either press Shift+F to do the default, or Shift+B to pop-up the sidebar as a sheet you can fill out, and Enter to execute from there.

[frosencrantz]

I think that would be worth trying. Is this the Guides discussed in #1733 or is it something more? It does sound like there is a separate sheet in the sidebar that would be ok.

My concern would be how good a match the Sheet UI is for a form. If it was like the options sheet, I feel it would be useful if there was a restricted mode where the editable cell is already in edit mode sort of like how Shift-Up or Shift-Down work once you are in edit mode. If you click on a row or navigate to a row only the value cell has focus and is ready to be modified. In this case I want something closer to a traditional UI form where it is not possible to edit the labels and once a field is focused it is ready to edit.

I do like having a quick way to override defaults and this sounds like it might be that and that would be very helpful even without the suggestion above.

[saulpw]

I agree, the standard Sheet is not so great for this kind of thing. I think what I'm calling a "form" would be more like you suggest, a restricted interface window with labels and dropdowns and clickable buttons, and in an edit mode by default when focused. Ideally it could be specified like the sidebar, as a simple text string. But it might need more layout and structure than that.