# Filtering one DataFrame using parameters of another, with different lengths

**URL:** <https://discourse.julialang.org/t/filtering-one-dataframe-using-parameters-of-another-with-different-lengths/95737>\
**Category:** New to Julia\
**Tags:** dataframes\
**Created:** [March 8, 2023, 2:57pm UTC](https://discourse.julialang.org/t/filtering-one-dataframe-using-parameters-of-another-with-different-lengths/95737 "2023-03-08T14:57:30Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [March 8, 2023, 2:57pm UTC](https://discourse.julialang.org/t/filtering-one-dataframe-using-parameters-of-another-with-different-lengths/95737/1 "2023-03-08T14:57:30Z")

</div>

Hi, I have the following issue:

I have a pretty big df that basicly stores coordinates, that looks like this:

```julia
|coordinate_gen|
|--------------|
| 752566|
| 842013|
| 903426|
| 903428|
| 59033249|

```

This has 1233013 rows, so it is quite big.

And then I have another df, that only has 20.600 rows, and three rows basically:

```julia
| ID| start| ends|
|---|-------|------|
|amd| 752540|752589|
|dmc| 903420|903429|
|chv| 10| 15|

```

What I am trying to do (and failing) is to filter all the rows from the bigger **df1** (the one that stores de **coordinate\_gen** ) so I get every row that has a value between the values of **df2**  **start** and **ends** , and add a column with the corresponding **id**.

The result should look like this:

```julia
|coordinate_gen| ID|
|--------------|-------|
| 752566| amd| # bigger than 752540 and smaller than 752589
| 842013|missing| # can't be set between any values
| 903426| dmc| # bigger than 903420and smaller than 903429
| 903428| dmc| # bigger than 903420 and smaller than 903429
| 59033249|missing| # can't be set between any values

```

I thought about simply using `filter`, but I don’t know how to add the condition that comes from another dataframe with a different length.

Any help is welcome,

Thanks a lot  
J

---

<div class="post-metadata">

**Author:** ![artemsolod](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/artemsolod/32/20704_2.png) [@artemsolod](https://discourse.julialang.org/u/artemsolod)\
**Post date:** [March 8, 2023, 3:06pm UTC](https://discourse.julialang.org/t/filtering-one-dataframe-using-parameters-of-another-with-different-lengths/95737/2 "2023-03-08T15:06:33Z")

</div>

You are probably looking for `Asof join` operation, followed by `filter`. [[ANN] FlexiJoins.jl: fresh take on joining datasets](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655) might be of use

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 8, 2023, 3:07pm UTC](https://discourse.julialang.org/t/filtering-one-dataframe-using-parameters-of-another-with-different-lengths/95737/3 "2023-03-08T15:07:08Z")

</div>

You might want to look at FlexiJoins

> **[Alexander Plavin / FlexiJoins.jl · GitLab](https://gitlab.com/aplavin/FlexiJoins.jl)**
>
> GitLab.com

Basically you are doing a `leftjoin` of `small_df` onto `big_df`, where the predicate is `coordinate_gen in start:ends`

---

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [March 8, 2023, 4:45pm UTC](https://discourse.julialang.org/t/filtering-one-dataframe-using-parameters-of-another-with-different-lengths/95737/4 "2023-03-08T16:45:51Z")

</div>

Thanks!

I just read the documentation, I think it will help me solve my problem.

The only issue that I have is that is not clarified how can you use two conditions, something like `by_pred(:coordinate_gen, >=, :gene_start, & :coordinate_gen, >=, :gene_end)`, as this gives a mistake, and `FlexiJoins.innerjoin((df1, df2), by_pred(:Physical_Position, in, [:gene_start, :gene_end)) ` also does not work, probably because I am doing the collection wrong.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [March 8, 2023, 5:25pm UTC](https://discourse.julialang.org/t/filtering-one-dataframe-using-parameters-of-another-with-different-lengths/95737/5 "2023-03-08T17:25:14Z")

</div>

```julia
using DataFrames, IntervalSets

cg=[
        752566,
        842013,
        903426,
        903428,
      59033249]

df1=DataFrame(;cg)

 ID=[ 
"amd",
"dmc",
"chv"]
      
      
 start=[ 
 752540,
 903420,
     10]

ends=[
752589,
903429,
    15]

df2=DataFrame(;ID,start,ends)

ints=ClosedInterval{Int64}.(df2.start,df2.ends)

idx=map(g->findfirst(i->∈(g,i), ints), df1.cg)

df1.IDgen=[!isnothing(i) ? ID[i] : missing for i in idx]

```

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [March 8, 2023, 5:29pm UTC](https://discourse.julialang.org/t/filtering-one-dataframe-using-parameters-of-another-with-different-lengths/95737/6 "2023-03-08T17:29:29Z")

</div>

> [@Juan\_Mac\_Donagh](#):
>
> by\_pred(:Physical\_Position, in, [:gene\_start, :gene\_end))

This doesn’t look like valid julia syntax at all.  
In FlexiJoins, you generally pass functions that extract join keys from dataset entries. Symbols to denote property names in just a convenient shortcut that works at the top level.  
Eg, to create an interval, use regular IntervalSets syntax:

```julia
by_pred(:Physical_Position, in, x -> x.gene_start..x.gene_end) # closed interval

```