Idiomatic way to group time series data by week or month?

[reagle]

I could hammer something out with a complicated Python expression, but is there a more idiomatic way to take a date-type column and create a new one labeled by week or month?

[geekscrapy]

I've been thinking of using the numeric binning option of the Freq Sheet that could do this for dates. I'd love the bin, to be able to take an input like, 1 hour bin, 2 hours, 3 days 1 month etc.

[saulpw]

For month, there are a few ways (assuming date column is named "d" and already converted to a date/time with @:

a) go to ColumnsSheet and set the fmtstr for "d" column to %Y-%m
or b) add a regex capture column with ;^(\d+-\d+)
or c) add a new f-string column: =f'{d.year}-{d.month:02}'

For week, I've used =d.isocalendar() which has year, week number, and weekday, and either expanded it out or added e.g [2] for a specific field (this is how the vdusage script discerns weekdays vs weekends). If you want the week number you could also use %Y-%W like in (a) above.

[These options aren't great, but they're only as bad as using regex or strftime or f-strings would be anyway.]

I'd love to figure out a nicer interface for dates. Currently z@ means "customdate" which takes an input strptime parse string (in the odd cases that dateutil parses it incorrectly; or maybe for likely better performance on large datasets). Maybe it could pop up a dialog with some more user-friendly options.

[geekscrapy]

Yea, I found z@ very useful as I need to be dead certain it has the correct date format. It also assists when validating (to a degree) that I have parsed the row correctly. My workflow is usually, create a named regex for the rows, z@ the timestamp column, then SHIFT+F on the timestamp column to make sure there are no parsing errors (i.e. an incorrect field got parsed in the regex phase) or that I indeed have the correct date format (usually given by the customer.

FWIW another thing that I do often is normalise non timezone aware timestamps to UTC. I will usually do this with timedelta -+ a certain number of hours.

Maybe we could add the type of chooser I have for another plugin I created, when z@ is pressed, open a dialogue (fancy chooser) and provide the user with a selection of common date formats, and a "let me freehand" option for those that have to

[reagle]

BTW: How would I know z@ means this? My goto references are the quick reference and cheat sheet? I wish vd had (1) a proper command palette (i.e., that #247 was re-opened) for (fuzzy) discovery of commands and their shortcuts and (2) a "short cut means" palette or function. Or am I missing something?

[saulpw]

z@ is still a fairly new command and pretty specialized, in my opinion, so it's not announced in many places. (I see it's not in the manpage yet, even.) One thing you could do, is go to the sheet of commands (z Ctrl+H) and then g|date to select all the dates, and " to see only those entries, et voila, there's @ and z@. And if the inline description isn't very enlightening, well that's easy enough to change, if you can suggest a description that would have gotten you the answer you needed.

[reagle]

Thank you, I'm trying but not succeeding (such a newb!). I renamed the regex column d, which still doesn't work.

BTW: If you have an expression column, how do you edit the expression that was used to create it?

[saulpw]

Ah, each of those is a separate option; you don't have to do them all. So in your case, rename del_text_r_utc to d, or use del_text_r_utc in the f-string. Or set the fmtstr on del_text_r_utc, etc.

To edit the expression, go to the columns sheet and edit the expr column. We can make a shortcut command for the same thing really easily if you want. I tend to Shift+U to undo or - to hide the wrong column and just try again until I get it right.

[reagle]

Got it, thank you!