# How to filter data conditional on two columns
**URL:** https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173
**Category:** General Usage
**Tags:** data, dataframes
**Created:** [January 7, 2022, 3:07am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173 "2022-01-07T03:07:57Z")
**Posts on this page:** 18
**Page:** 1
### Author:  [@xinchin](https://discourse.julialang.org/u/xinchin)
#### Post date: [January 7, 2022, 3:07am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/1 "2022-01-07T03:07:57Z")
I’ve a dataframe which has the information about money transactions between customers in each region. I like to filter customers in each region which both receive and send money to each other.
suppose `from` and `to` are customer id:
```julia
df = DataFrame(
branch = [1,1,1,1,1,2,2],
from = [1,2,3,4,5,1,6],
to = [4,7,1,1,2,3,9]
)
```
the result should only include row 1 and 4.
---
### Author:  [@aplavin](https://discourse.julialang.org/u/aplavin)
#### Post date: [January 7, 2022, 7:07am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/2 "2022-01-07T07:07:37Z")
**UPDated**
This should work:
```julia
cpairs = Set(map(r -> (r.branch, r.from, r.to), eachrow(df)))
filter(r -> (r.branch, r.to, r.from) in cpairs, df)
```
---
### Author:  [@lawless-m](https://discourse.julialang.org/u/lawless-m)
#### Post date: [January 7, 2022, 2:47pm UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/3 "2022-01-07T14:47:36Z")
With the quite specific task
```julia
julia> filter(row->row.from in [1,4] && row.to in [1,4], df)
2×3 DataFrame
Row │ branch from to
│ Int64 Int64 Int64
─────┼──────────────────────
1 │ 1 1 4
2 │ 1 4 1
```
You probably have some more general idea in mind but why solve the harder general problem when you only want a specific answer 🙂
---
### Author:  [@nilshg](https://discourse.julialang.org/u/nilshg)
#### Post date: [January 7, 2022, 4:09pm UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/4 "2022-01-07T16:09:29Z")
I would probably do something like this:
First group by `to` and `from` to only get unique pairwise transfers (in this case this doesn’t do much, as all your transfers are unique, but in your real data I presume there are multiple transfers between the same customers):
```julia
julia> grouped = combine(groupby(df, [:from, :to], sort = true), nrow)
7×3 DataFrame
Row │ from to nrow
│ Int64 Int64 Int64
─────┼─────────────────────
1 │ 1 3 1
2 │ 1 4 1
3 │ 2 7 1
4 │ 3 1 1
5 │ 4 1 1
6 │ 5 2 1
7 │ 6 9 1
```
then add a column which indicates the transfer start and end point:
```julia
julia> grouped[!, :transfer] = string.(grouped.from) .* string.(grouped.to)
7-element Vector{String}:
"13"
"14"
"27"
"31"
"41"
"52"
"69"
```
now your question boils down to finding the rows for which the reverse of this row is also in the data:
```julia
julia> in(reverse.(grouped.transfer)).(grouped.transfer)
7-element BitVector:
1
1
0
1
1
0
0
```
(note that here each row is kept twice, once for each direction of transfer)
(note also that going via `String` is probably not the most efficient way, but a quick and simple illustration)
---
### Author:  [@jules](https://discourse.julialang.org/u/jules)
#### Post date: [January 7, 2022, 9:05pm UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/6 "2022-01-07T21:05:05Z")
With `DataFrameMacros.jl` (and Chain):
```julia
julia> @chain df begin
groupby(:branch)
@subset @c tuple.(:to, :from) .∈ Ref(Set(tuple.(:from, :to)))
end
2×3 DataFrame
Row │ branch from to
│ Int64 Int64 Int64
─────┼──────────────────────
1 │ 1 1 4
2 │ 1 4 1
```
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [January 7, 2022, 9:36pm UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/7 "2022-01-07T21:36:15Z")
```julia
tdf=transform(df, [:from,:to]=> ((x,y)->Set.(zip(x,y)))=>:ft)
g=groupby(tdf,[:branch,:ft])
filter(x->nrow(x)==2 ,g)
```
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [January 8, 2022, 12:42am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/8 "2022-01-08T00:42:01Z")
[DataFramesMeta.jl](https://github.com/JuliaData/DataFramesMeta.jl) is another transformation library. Unlike DataFrameMacros.jl it operates by columns as the default.
```julia
julia> @chain df begin
groupby(:branch)
@subset tuple.(:to, :from) .∈ Ref(Set(tuple.(:from, :to)))
end
2×3 DataFrame
Row │ branch from to
│ Int64 Int64 Int64
─────┼──────────────────────
1 │ 1 1 4
2 │ 1 4 1
```
---
### Author:  [@xinchin](https://discourse.julialang.org/u/xinchin)
#### Post date: [January 8, 2022, 7:42am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/9 "2022-01-08T07:42:24Z")
Thanks all for helpful answers, I just select the first one since it was the first 😃
Maybe I should compare their performance 🤔
---
### Author:  [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)
#### Post date: [January 8, 2022, 7:57am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/10 "2022-01-08T07:57:16Z")
@aplavin, your updated magic solution seems to also work if `Set()` is removed. Is it really needed?
---
### Author:  [@aplavin](https://discourse.julialang.org/u/aplavin)
#### Post date: [January 8, 2022, 9:47am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/11 "2022-01-08T09:47:18Z")
Without `Set` it works, but has a quadratic complexity: walk through the whole `cpairs` array for each row. `Set` makes it `O(n)`.
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [January 8, 2022, 10:40pm UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/13 "2022-01-08T22:40:54Z")
I don’t see where the information on the :branch is used, which, in this case, excludes the pair (1,4) (4,1) from the result.
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [January 8, 2022, 10:51pm UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/14 "2022-01-08T22:51:54Z")
my solution does not solve the following case correctly
```julia
df = DataFrame(
branch = [1,1,1,1,1,2,2],
from = [1,2,3,1,5,1,6],
to = [4,7,1,4,2,3,9]
)
```
---
### Author:  [@DataFrames](https://discourse.julialang.org/u/DataFrames)
#### Post date: [January 9, 2022, 5:41am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/15 "2022-01-09T05:41:10Z")
use
```julia
semijoin(df, df, on = [:branch=>:branch, :from=>:to, :to=>:from])
```
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [January 9, 2022, 4:53pm UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/16 "2022-01-09T16:53:17Z")
I had also thought of a solution that made use of the join functions, although not as elegantly as yours.
In fact, what I was looking for was an alternative route that was competitive in speed of execution.
To tell the truth, I didn’t even know the existence of the semijoin function.
If I have not misunderstood this take, in the case of many correspondences only one (the first?).
But is that what @xinchin is asking?
Perhaps an example a little richer than the one provided would be useful, with the expected result in the case of many occurrences if this is a case existing within the same branch (and how they would be distinguished in that case)
```julia
df = DataFrame(
branch = [1,1,2,1,1,2,2],
from = [1,2,3,4,5,1,6],
to = [4,7,1,1,2,3,9]
)
SplitApplyCombine.innerjoin(l->(l.branch,l.from,l.to),r->(r.branch,r.to,r.from),(l,r)->[(l.branch,l.from,l.to),(r.branch,r.from,r.to)],eachrow(df),eachrow(df))
grp=groupby(df, :branch)
[SplitApplyCombine.innerjoin(l->(l.branch,l.from,l.to),r->(r.branch,r.to,r.from),(l,r)->(l.branch,l.from,l.to),eachrow(g),eachrow(g)) for g in grp]
```
---
### Author:  [@xinchin](https://discourse.julialang.org/u/xinchin)
#### Post date: [January 10, 2022, 2:40am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/17 "2022-01-10T02:40:49Z")
wow! like it, I never looked at `semijoin` from this perspective 😃
---
### Author:  [@xinchin](https://discourse.julialang.org/u/xinchin)
#### Post date: [January 10, 2022, 2:46am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/18 "2022-01-10T02:46:46Z")
I want all rows taht fit to the conditions.
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [January 10, 2022, 9:37pm UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/19 "2022-01-10T21:37:34Z")
could there be a case like the following?
if so, what would the expected result be?
```julia
df = DataFrame(
branch = [1,1,2,1,1,2,2],
from = [1,2,3,4,1,1,6],
to = [4,7,1,1,4,3,9]
)
```
---
### Author:  [@xinchin](https://discourse.julialang.org/u/xinchin)
#### Post date: [January 11, 2022, 1:01am UTC](https://discourse.julialang.org/t/how-to-filter-data-conditional-on-two-columns/74173/20 "2022-01-11T01:01:53Z")
it would be all 5 rows that meet the conditions, duplicated rows are ok. (hypothetically if they shouldn’t be there, I can use `unique` to remove them)