I have a dataset like this -
| slice | time_id | weight |
|---|---|---|
| 1 | 0 | 10 |
| 2 | 0 | 20 |
| 3 | 0 | 30 |
| 1 | 1 | 10 |
| 2 | 1 | 5 |
| 3 | 1 | 25 |
| 1 | 2 | 30 |
| 2 | 2 | 10 |
| 3 | 2 | 20 |
What will be the best method to get a rank column, which computes the rank for slices corresponding to the previous time_id? For instance, the output for the same table should look something like this -
| slice | time_id | weight | Rank |
|---|---|---|---|
| 1 | 0 | 10 | 0 |
| 2 | 0 | 20 | 0 |
| 3 | 0 | 30 | 0 |
| 1 | 1 | 10 | 3 |
| 2 | 1 | 5 | 2 |
| 3 | 1 | 25 | 1 |
| 1 | 2 | 30 | 2 |
| 2 | 2 | 10 | 3 |
| 3 | 2 | 20 | 1 |
I tried the excel rank function (had to manually set the rank range in the function, rank(slice, range) for every time_id), but it is too much manual work and I have slices and time periods in the hundreds. So what will be the best way to achieve this?
My objective is to find the top n slices for a time_id, based on weight during time_id-1