# Annotate matrixtable with count from another table

**URL:** <https://discuss.hail.is/t/annotate-matrixtable-with-count-from-another-table/950>\
**Category:** Hail Query & hailctl\
**Created:** [May 3, 2019, 10:56am UTC](https://discuss.hail.is/t/annotate-matrixtable-with-count-from-another-table/950 "2019-05-03T10:56:51Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![marksantcroos](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/marksantcroos/32/270_2.png) [@marksantcroos](https://discuss.hail.is/u/marksantcroos)\
**Post date:** [May 3, 2019, 10:56am UTC](https://discuss.hail.is/t/annotate-matrixtable-with-count-from-another-table/950/1 "2019-05-03T10:56:51Z")

</div>

Hi,

I would like to annotate a matrixtable with a region count from a column in another table.

From the documentation I came as far as the to detect the presence like this:  
`vcf_mt.annotate_rows(region = hl.is_defined(bed_table[vcf_mt.locus]))`

But instead of a boolean, I would like to get a count, so something like:  
`vcf_mt.annotate_rows(count = bed_table.aggregate(hl.agg.count_where(bed_table.interval.contains(vcf_mt.locus))))`

That doesn’t work though, with the following error:

> ExpressionException: Cannot combine expressions from different source objects.  
> Found fields from 2 objects:  
> \<hail.table.Table object at 0x7fa8289c9c88\>: [‘interval’]  
> \<hail.matrixtable.MatrixTable object at 0x7fa82859d438\>: [‘locus’]

Any suggestions on how to do this (efficiently) are highly welcome!

Thanks!

Mark

---

<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:** [May 3, 2019, 11:50am UTC](https://discuss.hail.is/t/annotate-matrixtable-with-count-from-another-table/950/2 "2019-05-03T11:50:12Z")

</div>

If the `bed_table` weren’t keyed by an interval, then could compute the count per key in the bed table and do a standard join. However, it is keyed by intervals, and don’t currently support one-to-many joins in this case.

Luckily, we have an open pull request to add this feature!

> <https://github.com/hail-is/hail/pull/5937>

The code will look like:

```python
vcf_mt.annotate_rows(count = hl.len(bed_table.index(mt.locus, all_matches=True)))

```

---

<div class="post-metadata">

**Author:** ![marksantcroos](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/marksantcroos/32/270_2.png) [@marksantcroos](https://discuss.hail.is/u/marksantcroos)\
**Post date:** [May 3, 2019, 2:24pm UTC](https://discuss.hail.is/t/annotate-matrixtable-with-count-from-another-table/950/3 "2019-05-03T14:24:19Z")

</div>

Ha! Just checked out his branch and that does the job indeed!