# Annotate rows with aggregations by grouped columns

**URL:** <https://discuss.hail.is/t/annotate-rows-with-aggregations-by-grouped-columns/770>\
**Category:** Hail Query & hailctl\
**Created:** [November 16, 2018, 3:15pm UTC](https://discuss.hail.is/t/annotate-rows-with-aggregations-by-grouped-columns/770 "2018-11-16T15:15:31Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tsoare](https://avatars.discourse-cdn.com/v4/letter/t/b782af/32.png) [@tsoare](https://discuss.hail.is/u/tsoare)\
**Post date:** [November 16, 2018, 3:15pm UTC](https://discuss.hail.is/t/annotate-rows-with-aggregations-by-grouped-columns/770/1 "2018-11-16T15:15:31Z")

</div>

I’m likely missing something basic because I am trying to find the best way to annotate rows with aggregations by grouped columns. For example, the case of adding call\_rate in cases and controls to the original MatrixTable.

ann = mt.annotate\_rows(group\_call\_rate = hl.agg.group\_by(hl.struct(case\_status = mt[‘Is.Control’]),  
hl.agg.fraction(hl.is\_defined(mt.GT))))

However this returns a dict (and ideally I’d like an array, like with PCA scores).

±----------------------------------------+  
| group\_call\_rate |  
±----------------------------------------+  
| dict\<struct{case\_status: str}, float64\> |  
±----------------------------------------+  
| {(“No”):9.99e-01,(“Yes”):9.60e-01} |  
| {(“No”):9.99e-01,(“Yes”):9.92e-01} |  
| {(“No”):9.97e-01,(“Yes”):9.93e-01} |  
| {(“No”):1.00e+00,(“Yes”):9.90e-01} |  
| {(“No”):1.00e+00,(“Yes”):9.91e-01} |  
| {(“No”):1.00e+00,(“Yes”):9.71e-01} |  
| {(“No”):1.00e+00,(“Yes”):9.77e-01} |  
| {(“No”):1.00e+00,(“Yes”):9.93e-01} |  
| {(“No”):1.00e+00,(“Yes”):9.98e-01} |  
| {(“No”):1.00e+00,(“Yes”):9.98e-01} |  
±----------------------------------------+  
showing top 10 rows

I also tried group\_cols\_by but I haven’t been able to annotate the values back to the original MatrixTable.

results = (mt.group\_cols\_by(case\_status = mt[‘Is.Control’])  
.aggregate(call\_rate = agg.fraction(hl.is\_defined(mt.GT))))  
results.describe()

results.entry.show(10)  
±--------------±-----------±------------±----------+  
| locus | alleles | case\_status | call\_rate |  
±--------------±-----------±------------±----------+  
| locus | array | str | float64 |  
±--------------±-----------±------------±----------+  
| chr1:39203 | [“C”,“T”] | “No” | 9.99e-01 |  
| chr1:39203 | [“C”,“T”] | “Yes” | 9.60e-01 |  
| chr1:39224 | [“C”,“T”] | “No” | 9.99e-01 |  
| chr1:39224 | [“C”,“T”] | “Yes” | 9.92e-01 |  
| chr1:115746 | [“C”,“T”] | “No” | 9.97e-01 |  
| chr1:115746 | [“C”,“T”] | “Yes” | 9.93e-01 |  
| chr1:133160 | [“G”,“A”] | “No” | 1.00e+00 |  
| chr1:133160 | [“G”,“A”] | “Yes” | 9.90e-01 |  
| chr1:135203 | [“G”,“A”] | “No” | 1.00e+00 |  
| chr1:135203 | [“G”,“A”] | “Yes” | 9.91e-01 |  
±--------------±-----------±------------±----------+  
showing top 10 rows

Maybe this is possible with collect()? But this workaround doesn’t seem optimal…

Thanks so much!

---

<div class="post-metadata">

**Author:** ![tpoterba](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/tpoterba/32/61_2.png) [@tpoterba](https://discuss.hail.is/u/tpoterba)\
**Post date:** [November 16, 2018, 4:43pm UTC](https://discuss.hail.is/t/annotate-rows-with-aggregations-by-grouped-columns/770/2 "2018-11-16T16:43:49Z")

</div>

> However this returns a dict (and ideally I’d like an array, like with PCA scores).

what would the elements be? There’s probably a way to transform this dictionary into the array you want.

---

<div class="post-metadata">

**Author:** ![tsoare](https://avatars.discourse-cdn.com/v4/letter/t/b782af/32.png) [@tsoare](https://discuss.hail.is/u/tsoare)\
**Post date:** [November 16, 2018, 4:46pm UTC](https://discuss.hail.is/t/annotate-rows-with-aggregations-by-grouped-columns/770/3 "2018-11-16T16:46:50Z")

</div>

For this case, I am simply thinking the values [9.99e-01, 9.60e-01], without keys. (I do realize that dropping the keys from the dict might be blasphemy.)

---

<div class="post-metadata">

**Author:** ![tpoterba](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/tpoterba/32/61_2.png) [@tpoterba](https://discuss.hail.is/u/tpoterba)\
**Post date:** [November 16, 2018, 4:49pm UTC](https://discuss.hail.is/t/annotate-rows-with-aggregations-by-grouped-columns/770/4 "2018-11-16T16:49:12Z")

</div>

you can just do `.values()` on the result.

But you don’t know whether or case or control comes first!