# Complex filtering of DataFrame
**URL:** https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650
**Category:** Data
**Tags:** dataframes
**Created:** [March 29, 2022, 3:19am UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650 "2022-03-29T03:19:41Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@amrods](https://discourse.julialang.org/u/amrods)
#### Post date: [March 29, 2022, 3:19am UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/1 "2022-03-29T03:19:41Z")
I have this data frame that I want to filter:
```julia
using DataFrames
using StatsBase
df = DataFrame(id = repeat(sample(1:1000, 100; replace=false), inner=2), s = repeat(1:2, inner=1, outer=100))
insertcols!(df, :t => rand(2:100, 200))
insertcols!(df, :c => df.t .+ (rand(200) .> 0.25))
```
that results in a `DataFrame` with 4 columns: `id`, `s`, `t`, and `c`. For some `id`s, there are situations where `c > t` for both values of `s`. I want to select only those cases, but I can’t figure out how to do it.
I have tried this:
```julia
gdf = groupby(df, :id)
df2 = filter([:t, :c] => (t, c) -> c > t, gdf)
```
but that also selects cases for when `c > t` for only one value of `s`. Any suggestions?
EDIT: Found a way:
```julia
insertcols!(df, :ind => df.c .> df.t)
gdf = groupby(df, :id)
tot = transform(gdf, :ind => sum => :total)
df2 = filter(:total => x -> (x .== 2), tot)
```
Is there a more elegant way?
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [March 29, 2022, 7:21am UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/2 "2022-03-29T07:21:07Z")
I assume you want something simple. Then you can do this:
```julia
DataFrame(filter(sdf -> all(sdf.c .> sdf.t), groupby(df, :id)))
```
(this is not the most efficient way to do it, but I believe the code is easiest to follow)
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [March 29, 2022, 11:01am UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/3 "2022-03-29T11:01:58Z")
a different way of proceeding that perhaps better reflects the attempt you made.
You select the rows for which c\> t and then select only the groups that have a pair of rows.
```julia
df2 = filter([:t, :c] => (t, c) -> c > t, df)
filter(x->x.nrow==2,combine(groupby(df2, :id),:,nrow))
```
---
### Author:  [@amrods](https://discourse.julialang.org/u/amrods)
#### Post date: [March 29, 2022, 12:01pm UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/4 "2022-03-29T12:01:16Z")
Can you give some pointers about a more efficient way to do it?
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [March 29, 2022, 1:26pm UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/5 "2022-03-29T13:26:17Z")
try this, waiting for better one
```julia
even=reshape(Tables.rowtable(df),:,2)[2:2:end]
odd=reshape(Tables.rowtable(df)),:,2)[1:2:end]
DataFrame(reduce(vcat, [[f,s] for (f,s) in zip(odd,even) if f.c > f.t && s.c > s.t]))
```
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [March 29, 2022, 1:44pm UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/6 "2022-03-29T13:44:34Z")
This seems like unnecessarily obtuse code, fwiw.
A “more efficient way to do it” would be to se the Pair syntax for `filter` rather than an anonymous function which applies to each row.
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [March 29, 2022, 3:37pm UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/7 "2022-03-29T15:37:27Z")
If you want to be efficient you take advantage of the fact that the original data frame is already pre-grouped by `:id`, then you can do:
```julia
function testfun(t, c)
@assert length(t) == length(c)
@assert iseven(length(t))
@assert firstindex(t) == firstindex(c) == 1
cond = fill(true, length(t))
@inbounds for i in 1:2:length(t)
if t[i] >= c[i] || t[i+1] >= c[i+1]
cond[i] = false
cond[i+1] = false
end
end
return cond
end
```
and then do
```julia
df[testfun(df.t, df.c), :]
```
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [March 29, 2022, 3:38pm UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/8 "2022-03-29T15:38:02Z")
Could you elaborate on this observation and also explain the filter solution you are referring to?
I meant efficient in the sense of execution time. The solution that I have proposed is much faster than the other views, if I have not misused the benchmark tool.
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [March 29, 2022, 3:45pm UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/9 "2022-03-29T15:45:21Z")
I assume @pdeffebach meant:
```julia
DataFrame(filter([:c, :t] => (c, t) -> all(((x, y),) -> x > y, zip(c, t)), groupby(df, :id)))
```
it is faster than my original code, but slower than what I have proposed above.
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [July 22, 2022, 1:48pm UTC](https://discourse.julialang.org/t/complex-filtering-of-dataframe/78650/10 "2022-07-22T13:48:37Z")
Given that, by construction, in the present dataframe in 3 cases out of 4 C is greater than t,
common sense suggests filling the vector with all `true` and changing only about 1/4 of the elements.
However, I noticed that a small improvement can be obtained by splitting the comparison `c >= t` into two parts and putting the equality check first: `c == t || c> t`.
In fact, for the case in question and with the following dataframe, the calculation time of the “good” indices is more than halved.
```julia
df = DataFrame(id = repeat(sample(1:10^5, 10000; replace=false), inner=2), s = repeat(1:2, inner=1, outer=10000))
insertcols!(df, :t => rand(2:100, 20000))
insertcols!(df, :c => df.t .+ (rand(20000) .> 0.25))
julia> function testfun(t, c)
@assert length(t) == length(c)
@assert iseven(length(t))
@assert firstindex(t) == firstindex(c) == 1
cond = fill(true, length(t))
@inbounds for i in 1:2:length(t)
if t[i] >= c[i] || t[i+1] >= c[i+1]
cond[i] = false
cond[i+1] = false
end
end
return cond
end
testfun (generic function with 1 method)
julia> @btime testfun(df.t, df.c);
15.500 μs (2 allocations: 19.61 KiB)
julia> function testfun(t, c)
@assert length(t) == length(c)
@assert iseven(length(t))
@assert firstindex(t) == firstindex(c) == 1
cond = fill(true, length(t))
@inbounds for i in 1:2:length(t)
if t[i] == c[i]|| t[i+1] == c[i+1] ||t[i] > c[i] || t[i+1] > c[i+1]
cond[i] = false
cond[i+1] = false
end
end
return cond
end
testfun (generic function with 1 method)
julia> @btime testfun(df.t, df.c);
7.300 μs (2 allocations: 19.61 KiB)
```
I wonder, at this point, how the expression with `>=` is internally evaluated