Using filter in rollup?

[stas-sl]

Hello, thanks for the great package! I'm still learning it and I got stuck at the the following task, which should be easy...

I have a table like this:

const t = aq.table({
  group:  ["a", "a", "a", "b", "b", "b", "b", "b"],
  answer: ["y", "n", "y", "y", "n", "n", "n", "y"]
})

group	answer
a	y
a	n
a	y
b	y
b	n
b	n
b	n
b	y

I"d like to convert it to this:

group	y_count	n_count
a	2	1
b	2	3

I know, it is easy to use groupby("group", "answer").count() to get the counts, but I need them in the same row. I've tried to write like this:

t.groupby("group").rollup({
  y_count: (d) => d.filter((x) => x.answer === "y").count(),
  n_count: (d) => d.filter((x) => x.answer === "n").count()
})

But seems that filtering inside rollup not working as it returns same counts for both column (total rows).

Also I tried to use aq.escape, but also without success, as they are not supported inside rollup.

Another idea was to chain groupby with pivot:

t.groupby("group").pivot("answer", { value: x => x.count() })

but I also got the same result - total number of rows in group without splitting by answer.

[jheer]

Hmm, it looks like pivot is not handling count() as expected. I'll need to look into that.

In the meantime, here is an alternative that works using sum:

t.groupby('group')
 .pivot('answer', { value: () => op.sum(1) })

[stas-sl]

Great, thanks!

[stas-sl]

After your hint to use sum instead of count, I've come up with a solution that works with rollup:

t.groupby("group").rollup({
  y_count: (d) => d.sum(d.answer === "y"),
  n_count: (d) => d.sum(d.answer === "n")
})

which is so simple 😳

[mhkeller]

This syntax is very nice. I hadn't seen it documented that d also has op functions attached to it. Is that explained somewhere? It seems to yield the same result as the following:

t.groupby("group").rollup({
  y_count: (d) => op.sum(d.answer === "y"),
  n_count: (d) => op.sum(d.answer === "n")
})

Are these two usages equivalent or is there any advantage to one or the other?

[stas-sl]

TBH, I'm not sure where this syntax comes from 🤔. In tutorials I see that it is usually used the way you wrote it. Maybe I just mixed it with python/pandas as I more used to it, but it suddenly worked for arquero as well. I don't think there should be difference internally, except that if it is indeed undocumented, there could be higher possibility it could stop working some day.

[stas-sl]

Except this, it works like a charm!

I used it to calculate Kaplan-Meier survival function. Before arquero it was a mix of d3/lodash/loops/nested function calls and now it is just a chain of straightforward operations. Especially I liked, how at first I implemented everything for all data, and then just inserted single groupby in the middle of the chain and it calculated correctly all subsequent rolling sums/sorting/lag inside groups!

const KMtable = aq
  .from(data)
  .params({ split })
  .derive({
    age_days: (d) => +d.age_days,
    group: (d, $) => $.split ? aq.substring(d.created_at, 0, 4) : "all"
  })
  .groupby("group", "age_days")
  .rollup({
    closed: (d) => aq.sum(d.is_closed),
    open: (d) => aq.sum(!d.is_closed)
  })
  .groupby("group")
  .orderby(aq.desc("age_days"))
  .derive({ cum_open: aq.rolling((d) => aq.sum(d.open + d.closed)) })
  .derive({ hazard: (d) => d.closed / d.cum_open })
  .orderby("age_days")
  .derive({ survival: aq.rolling((d) => aq.product(1 - d.hazard)) })
  .derive({ survival: (d) => aq.lag(d.survival) || 1 })
  .objects();

[mhkeller]

@stas-sl Just curious, what are you using to generate these plots? Is this in observable?

[stas-sl]

@mhkeller, yep, exactly, observable + vega-lite, you can check it out here https://observablehq.com/@stas-sl/github-issues-survival-analysis