# Query.jl - how to select identical rows differing by one column

**URL:** <https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367>\
**Category:** Data\
**Created:** [January 7, 2019, 9:26pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367 "2019-01-07T21:26:52Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 7, 2019, 9:26pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/1 "2019-01-07T21:26:52Z")

</div>

Hi, I have a dataframe of columns A,B,C, O.

How can I write a query which shows me those rows which have same pattern in the columns A,B,C but different value for column O ?

example:

A, B, C, O  
2, 3, 4, 10  
2 ,3, 4, 15  
1, 5, 6, 20  
…

the result should show only the first 2 rows.

Thanks

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [January 7, 2019, 11:21pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/2 "2019-01-07T23:21:53Z")

</div>

A, B, C, O  
2, 3, 4, 10  
2 ,3, 4, 15  
2, 5, 6, 20  
1, 5, 6, 20  
1, 5, 6, 21  
3, 10, 20, 1

Whats the output for the above?

---

<div class="post-metadata">

**Author:** ![Seif\_Shebl](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@Seif\_Shebl](https://discourse.julialang.org/u/Seif_Shebl)\
**Post date:** [January 7, 2019, 11:26pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/3 "2019-01-07T23:26:55Z")

</div>

I think

```julia
2, 3, 4, 10
2 ,3, 4, 15
1, 5, 6, 20
1, 5, 6, 21

```

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [January 7, 2019, 11:52pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/4 "2019-01-07T23:52:58Z")

</div>

If that’s the case then it’s group by to find out the count and only keep those groups with count \> 1

---

<div class="post-metadata">

**Author:** ![Nosferican](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nosferican/32/9275_2.png) [@Nosferican](https://discourse.julialang.org/u/Nosferican)\
**Post date:** [January 7, 2019, 11:59pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/5 "2019-01-07T23:59:17Z")

</div>

Wouldn’t that be a SQL-like `select distinct A, B, C, O from source`? In other words, is just the unique rows of the stream of selected columns.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [January 8, 2019, 12:11am UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/6 "2019-01-08T00:11:05Z")

</div>

He wants to drop rows where a,b,c as a group has less than 2 rows

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [January 8, 2019, 1:17am UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/7 "2019-01-08T01:17:28Z")

</div>

This should do it:

```julia
df |>
  @groupby({_.A,_.B,_.C}) |>
  @filter(length(_)>1) |> 
  @mapmany(_, (i,j)->j) |>
  DataFrame

```

First we group things, then we filter out the groups that have only one element, then we unpack the groups again to go back to a table.

I think I should really add a more convenient way to ungroup tables… `@mapmany(_, (i,j)->j)` does work, but is a bit unwieldy and hard to remember…

---

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 8, 2019, 12:40pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/8 "2019-01-08T12:40:50Z")

</div>

Thanks for asking.

the output for those would be:  
A, B, C, O  
2, 3, 4, 10  
2, 3, 4, 15  
1, 5, 6, 20  
1, 5, 6 20

the O = 20 is not part of the utput becase A,B,C are not the same.

---

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 8, 2019, 12:40pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/9 "2019-01-08T12:40:57Z")

</div>

correct.

---

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 8, 2019, 12:41pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/10 "2019-01-08T12:41:00Z")

</div>

what if O does not contain numeric values? Are you suggesting we categorize the column O first ?

---

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 8, 2019, 12:41pm UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/11 "2019-01-08T12:41:03Z")

</div>

I need to see what i am doing wrong with the query you suggested as at the moment it does not give me the wanted results.

maybe because the following result is comming from the query:

A, B, C, O

1, 5 ,9, 20  
1, 5 ,9, 20  
1, 5 ,9, 20  
1, 5, 9, 21

and on a big dataset the lines might still be many. In fact only the last 2 rows would be required. the first 2 are redundant.

Perhaps a bit of background helps.

I have a model for which A, B, C represent the inputs and O is the output of the model. The model specification says that if the same imput pattern is presented, there should be only one outcome on the output.  
However due to design “bugs” of the model, the model might have hidden states for which depending from the sequence of the input pattern the output might really be different.

Simulation is used to generate the dataset and clearly I do not design for the hidden states but they are there. Such query should help to find out what are those combination of the input pattern which lead to different output.

The output should be automatically a table with rows forming a unique set (I assume). I believe is an interesting application case.

Many thanks for the help.

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [January 10, 2019, 6:36am UTC](https://discourse.julialang.org/t/query-jl-how-to-select-identical-rows-differing-by-one-column/19367/12 "2019-01-10T06:36:33Z")

</div>

Hm, tricky… I guess the right solution here would be that per group that satisfies the `length>1` condition, we want to get the unique rows, where unique is based on just looking at the `O` column. There are multiple difficulties with that at the moment 🙂 First, ideally we would have a `unique` function with a `by` keyword. But, unfortunately, right now, we don’t… And then I need to think hard how one could combine that with the way I’m handling groups in Query.jl… It might just work or not, I’m not sure right now.

So, no solution for now, I’m afraid. But I’ve opened an issue, maybe I’ll come up with a good way for this at some point: [Find a solution for selecting unique group members · Issue #233 · queryverse/Query.jl · GitHub](https://github.com/queryverse/Query.jl/issues/233)

Thanks for bringing this case up, really super valuable to hear about these cases, it helps a lot to have tough real world cases like this!