# Filter\_rows using row\_key of a different source

**URL:** <https://discuss.hail.is/t/filter-rows-using-row-key-of-a-different-source/897>\
**Category:** Hail Query & hailctl\
**Created:** [March 7, 2019, 9:57pm UTC](https://discuss.hail.is/t/filter-rows-using-row-key-of-a-different-source/897 "2019-03-07T21:57:46Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![lindsayvm](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/lindsayvm/32/261_2.png) [@lindsayvm](https://discuss.hail.is/u/lindsayvm)\
**Post date:** [March 7, 2019, 9:57pm UTC](https://discuss.hail.is/t/filter-rows-using-row-key-of-a-different-source/897/1 "2019-03-07T21:57:46Z")

</div>

I want to apply a filter on the variants that is based on only for a part of the samples.  
I first want to create a new mt that is filtered cols, and after that on rows, such that the filter on rows is only based on part of the samples. Then use the row\_ key (locus) of this new mt to select for variants of the initial mt.

mt 1 is initial matrixtable, and create new mt2 by filtering samples for controls.

```python
> mt1
> mt2 = mt.filter_cols(mt.condition == "control")

```

For mt2 filter variants for hardy-weinberg p-value.

```python
mt2 = mt2.filter_rows(mt1.variant_qc.p_value_hwe < 10**-5)

```

Ensure that mt2 has row key locus just like mt1.

```python
mt2 = mt2.key_rows_by("locus")

```

I want to use the key of mt2 to select variants of mt1. Thus, keep variants that pass hwe based on only the control samples and not cases.

```python
idx = mt2.row.select("locus")
mt1 = mt.filter_rows(mt1.locus == idx)  

```

**TypeError: Invalid ‘==’ comparison, cannot compare expressions of type ‘locus\<GRCh37\>’ and 'struct{locus: locus\<GRCh37\>}'**

---

<div class="post-metadata">

**Author:** ![danking](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/danking/32/43_2.png) [@danking](https://discuss.hail.is/u/danking)\
**Post date:** [March 7, 2019, 10:20pm UTC](https://discuss.hail.is/t/filter-rows-using-row-key-of-a-different-source/897/2 "2019-03-07T22:20:56Z")

</div>

You’re looking for a combination of the “join” syntax and the `rows()` table:

```python
mt2 = mt2.key_rows_by("locus")
mt1 = mt1.filter_rows(hl.is_defined(mt2.rows()[mt1.locus]))

```

`mt2.rows()` says, give me the rows table (the variants table, without genotypes) of `mt2`.

`mt2.rows()[mt1.locus]` is a “join”, which finds the row in `mt2.rows()` such that it’s row key is equal to `mt1.locus`. If no such matching row exists, `mt2.rows()[mt1.locus]` is a missing value.

`hl.is_defined(x)` checks if something is _not_ a missing value.

---

<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:** [March 8, 2019, 6:06pm UTC](https://discuss.hail.is/t/filter-rows-using-row-key-of-a-different-source/897/3 "2019-03-08T18:06:22Z")

</div>

There’s a new nicer syntax for this:

```auto
mt1 = mt1.filter_rows(hl.is_defined(mt2.rows()[mt1.locus]))

```

It’s called a “semi join”:

```auto
mt1 = mt1.semi_join_rows(mt2.rows())

```

---

<div class="post-metadata">

**Author:** ![lindsayvm](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/lindsayvm/32/261_2.png) [@lindsayvm](https://discuss.hail.is/u/lindsayvm)\
**Post date:** [March 8, 2019, 8:17pm UTC](https://discuss.hail.is/t/filter-rows-using-row-key-of-a-different-source/897/6 "2019-03-08T20:17:28Z")

</div>

@danking Thanks, that works!

@tpoterba

```auto
mt1 = mt1.semi_join_rows(mt2.rows())

```

Gives the following error:  
**AttributeError: MatrixTable instance has no field, method, or property 'semi\_join\_rows. Did you mean: MatrixTable method: 'union\_rows’**

When I use ‘union\_rows’ instead:  
**hail.matrixtable.MatrixTable, found hail.table.Table: \<hail.table.Table object at 0x7fbec5d6a4a8\>'**  
However, both mt1 and mt2 are typed as MatrixTable.

What is going wrong here?

---

<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:** [March 8, 2019, 8:26pm UTC](https://discuss.hail.is/t/filter-rows-using-row-key-of-a-different-source/897/7 "2019-03-08T20:26:12Z")

</div>

semi\_join\_rows was added just a couple days ago, you’ll need version 0.2.11 or later

---

<div class="post-metadata">

**Author:** ![lindsayvm](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/lindsayvm/32/261_2.png) [@lindsayvm](https://discuss.hail.is/u/lindsayvm)\
**Post date:** [March 8, 2019, 9:34pm UTC](https://discuss.hail.is/t/filter-rows-using-row-key-of-a-different-source/897/8 "2019-03-08T21:34:22Z")

</div>

@tpoterba Great, thanks!