How to find the index of max/min?

[nachocab]

Let's say I want to get the year and the cases where the max number cases happened in table1

In R there's which.max and which.min, but I haven't seen an equivalent in arquero.

This doesn't work:

table1
  .groupby("country")
  .rollup({
    cases: op.max("cases"),
    yearMaxCases: (d) => op.indexof(d.cases, op.max("cases"))
  })
  .view()

[jheer]

One way to do this is to use filter to reduce the table to just the max (or min) values. Note that a minimum or maximum value may appear more than once. If desired, you can then use another filter to limit the result to a single max/min per group.

aq.table({
    g: [1, 1, 1, 1, 2, 2, 2, 2],
    v: [1, 1, 2, 2, 3, 3, 4, 4],
    y: [1, 2, 3, 4, 1, 2, 3, 4]
  })
  .groupby('g')
  .filter(d => op.max(d.v) === d.v)   // remove rows that do not contain max(v)
  .filter(d => op.row_number() === 1) // retain only the first row in each group
  .view()

[nachocab]

Thanks for the quick answer!

This is useful if I only want a single aggregate column, but what if I want more than one (like max and also min)?

I guess one option would be to calculate this on a different table for each additional aggregate column and then join them. 🤷‍♂️

[jheer]

Or, you might first compute the values that correspond to the min/max and then aggregate:

aq.table({
    g: [1, 1, 1, 1, 2, 2, 2, 2],
    v: [1, 1, 2, 2, 3, 3, 4, 4],
    y: [1, 2, 3, 4, 1, 2, 3, 4]
  })
  .groupby('g')
  .derive({
    ymin: d => op.min(d.v) === d.v ? d.y : NaN, // only keep values corresponding to min
    ymax: d => op.max(d.v) === d.v ? d.y : NaN  // only keep values corresponding to max
  })
  .rollup({
    min:  d => op.min(d.v),
    max:  d => op.max(d.v),
    ymin: d => op.min(d.ymin), // use earliest value if multiple minima
    ymax: d => op.min(d.ymax)  // use earliest value if multiple maxima
  })
  .view()

[nachocab]

That makes much more sense. Thank you!

It might be a good idea to include a link to the github discussions on the arquero homepage. It took me a while to find it. 😅

[jheer]

Good point! I've updated the Arquero readme and homepage.