What's the best way to select the first (or last) row in each group?

[ghost]

Suppose I have the following data:

ID	Date	Number
1	2020-01-01	12
1	2020-03-07	10
1	2020-06-10	22
2	2020-05-14	24
2	2020-07-19	13

I want to group by the ID, sort by date, and select just the first row in each group to get the following:

ID	Date	Number
1	2020-01-01	12
2	2020-05-14	24

Is this possible in VisiData?

I thought about sorting by Date and ID, then numbering each group in a new column, then selecting the rows where the number is 1. However, I'm not sure how to do this (or any kind of analytic function) either:

ID	Date	Number	Order
1	2020-01-01	12	1
1	2020-03-07	10	2
1	2020-06-10	22	3
2	2020-05-14	24	1
2	2020-07-19	13	2

[saulpw]

Hi @DAVIDWALES, this is a good question. I can't think of an easy way to do this with stock VisiData, but you can add a simple command to the freq table to do what you want:

FreqTableSheet.addCommand('', 'select-source-first', 'for r in rows: source.select([r.sourcerows[0]])')

You can bind it to a keystroke if you want, or use Space to execute select-source-first without binding it.

[ghost]

Thanks Saul! And I guess you could do the same thing but grab the last row in a group by changing r.sourcerows[0] to r.sourcerows[-1]?

[saulpw]

Yup, exactly!

[jsvine]

FWIW, I personally do this via the dedupe plugin:

1. Set the relevant key column(s) (in your case, ID)
2. Hit Space to raise the command prompt
3. Type dedupe-rows + Enter

You can install the dedupe plugin via VisiData's built-in plugin manager or by installing it manually.

[ghost]

Ooh. I have the dedupe plugin. But I didn't think to dedupe based on key columns, rather than the whole row.