# Iterate across two DataFrames using Query.jl

**URL:** <https://discourse.julialang.org/t/iterate-across-two-dataframes-using-query-jl/7103>\
**Category:** New to Julia\
**Tags:** query\
**Created:** [November 16, 2017, 5:02pm UTC](https://discourse.julialang.org/t/iterate-across-two-dataframes-using-query-jl/7103 "2017-11-16T17:02:18Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![phillc](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/phillc/32/2628_2.png) [@phillc](https://discourse.julialang.org/u/phillc)\
**Post date:** [November 16, 2017, 5:02pm UTC](https://discourse.julialang.org/t/iterate-across-two-dataframes-using-query-jl/7103/1 "2017-11-16T17:02:18Z")

</div>

Pretty basic question it seems to me, but I can’t figure out how to iterate across multiple dataframes. I’ve been trying to do this with Query.jl

**Problem:** First Dataframe contains names. Second Dataframe contains names. Count the number of times the names in First DataFrame appear in Second Dataframe.

```julia
using Query, DataFrames

df_one = DataFrame(name=["John", "Sally", "Kirk"])
df_two = DataFrame(name=["Sally", "Sally", "Kirk", "John", "Kirk", "Kirk", "John", "Sally", "Sally"])

name_tot = @from i in df_one begin
    @from j in df_two
    @where j.name == i.name
    @let count = length(j.name)
    @select count
    @gather DataFrame
    end

println(name_tot)

```

I’ve tried a few different variations, but none return the desired result; a DataFrame showing the counts of each name in Second Dataframe.

I’m sure it’s just a noob question and I I’m not understanding something fundamental.

---

<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:** [November 16, 2017, 5:11pm UTC](https://discourse.julialang.org/t/iterate-across-two-dataframes-using-query-jl/7103/2 "2017-11-16T17:11:34Z")

</div>

I think you are looking for a [group join](http://www.david-anthoff.com/Query.jl/stable/querycommands.html#Group-join-1):

```julia
@from i in df_one begin
@join j in df_two on i.name equals j.name into k
@select {i.name, count=length(k)}
@collect DataFrame                               
end

# output

3×2 DataFrames.DataFrame
│ Row │ name │ count │
├─────┼─────────┼───────┤
│ 1 │ "John" │ 2 │
│ 2 │ "Sally" │ 4 │
│ 3 │ "Kirk" │ 3 │

```

The kind of formulation you used will do an inner join: the two `@from` clauses generate the cross-product, and then the `@where` clause will filter it down to what you would have gotten from an inner join right away.

---

<div class="post-metadata">

**Author:** ![phillc](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/phillc/32/2628_2.png) [@phillc](https://discourse.julialang.org/u/phillc)\
**Post date:** [November 16, 2017, 6:25pm UTC](https://discourse.julialang.org/t/iterate-across-two-dataframes-using-query-jl/7103/3 "2017-11-16T18:25:23Z")

</div>

> [@davidanthoff](#):
>
> I think you are looking for a group join:

And so I was. Thanks for the solution and such a quick response. It looks amazingly like the examples in the documentation I’ve been starting at for the last hour. Didn’t even consider a group join as the way forward.

Thanks again.

---

<div class="post-metadata">

**Author:** ![phillc](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/phillc/32/2628_2.png) [@phillc](https://discourse.julialang.org/u/phillc)\
**Post date:** [November 16, 2017, 7:44pm UTC](https://discourse.julialang.org/t/iterate-across-two-dataframes-using-query-jl/7103/4 "2017-11-16T19:44:00Z")

</div>

A related follow on… Not sure if it’s a bug or by design, but it would appear new column names can’t be exactly the same as range variables.

```julia
df_new = @from i in df_one begin
        @join j in df_two on i.name equals j.name into k
        @let count = length(k)
        @select {i.name, count=count}
        @collect DataFrame                               
        end
   
println(df_new)

#output

3×2 DataFrames.DataFrame
│ Row │ name │ _2_ │
├─────┼─────────┼─────┤
│ 1 │ "John" │ 2 │
│ 2 │ "Sally" │ 4 │
│ 3 │ "Kirk" │ 3 │

```

I did expect that new column to be called “count” not “_2_”. Anything to the left of an “=” in a @select statement to be considered a column name?

---

<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:** [November 16, 2017, 10:07pm UTC](https://discourse.julialang.org/t/iterate-across-two-dataframes-using-query-jl/7103/5 "2017-11-16T22:07:57Z")

</div>

That looks like a bug to me: [https://github.com/davidanthoff/Query.jl/issues/163](https://github.com/davidanthoff/Query.jl/issues/163)

---

<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:** [November 19, 2018, 11:27pm UTC](https://discourse.julialang.org/t/iterate-across-two-dataframes-using-query-jl/7103/6 "2018-11-19T23:27:24Z")

</div>

That last issue mentioned is now fixed in a PR, soon on `master` as well.