# Accessing full dataframe after a join within a Query.jl query

**URL:** <https://discourse.julialang.org/t/accessing-full-dataframe-after-a-join-within-a-query-jl-query/3076>\
**Category:** Data\
**Created:** [April 5, 2017, 8:41pm UTC](https://discourse.julialang.org/t/accessing-full-dataframe-after-a-join-within-a-query-jl-query/3076 "2017-04-05T20:41:38Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![tlnagy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tlnagy/32/5815_2.png) [@tlnagy](https://discourse.julialang.org/u/tlnagy)\
**Post date:** [April 5, 2017, 8:41pm UTC](https://discourse.julialang.org/t/accessing-full-dataframe-after-a-join-within-a-query-jl-query/3076/1 "2017-04-05T20:41:38Z")

</div>

This is transferring my question from [Possible to join and then do a groupby? · Issue #104 · queryverse/Query.jl · GitHub](https://github.com/davidanthoff/Query.jl/issues/104#issuecomment-291908459) as suggested by @davidanthoff. I have two dataframes that look like this:

```julia
df1 = DataFrame(id=[1,2,3], rel_freq=[0.5, 1, 1.5], class=["a", "a", "c"])
df2 = DataFrame(id=[1,2,3,4], rel_freq=[0.75, 0.75, 1.75, 1], class=["a", "a", "c", "c"])
println(df1)
println(df2)

#output

3×3 DataFrames.DataFrame
│ Row │ id │ rel_freq │ class │
├─────┼────┼──────────┼───────┤
│ 1 │ 1 │ 0.5 │ "a" │
│ 2 │ 2 │ 1.0 │ "a" │
│ 3 │ 3 │ 1.5 │ "c" │
4×3 DataFrames.DataFrame
│ Row │ id │ rel_freq │ class │
├─────┼────┼──────────┼───────┤
│ 1 │ 1 │ 0.75 │ "a" │
│ 2 │ 2 │ 0.75 │ "a" │
│ 3 │ 3 │ 1.75 │ "c" │
│ 4 │ 4 │ 1.0 │ "c" │

```

I want to calculate pvalues using a Mann Whitney test between the diffs of the `rel_freqs` for all classes and compare them to class `c`.

##Desired approach (single query)

The following is my attempt to do this in one query:

```julia
x = @from i in df1 begin
    @join j in df2 on i.id equals j.id
    @select {i.id, log2fc=log2(get(j.rel_freq)/get(i.rel_freq)), i.class} into k
    @let negcontrol = @from l in k begin # try to build a list of log2fc of the negative controls
        @where contains(l.class, "c")
        @select l.log2fc
        @collect
    end
    @group k by k.gene into g
    @let test = MannWhitneyUTest(map(j->get(j.log2fc), g), negcontrol) #compare this group versus neg controls
    @select {
        gene=g.key, 
        avg_freq=mean(j->j.log2fc, g),
        pval= -log10(pvalue(test)) # calculate p value for this group versus neg controls
    }
    @collect DataFrame
end

```

The main problem seems to be that I select all rows of the joined dataframe that contain “c” for class and then create an array of the `log2fc` column for all of them to use in the Mann-Whitney test.

## Working approach (multiple separate queries)

Here’s code that gives the correct result, but doesn’t do so in a single query:

```julia-auto
x = @from i in df1 begin
    @join j in df2 on i.id equals j.id
    @select {i.id, log2fc=log2(get(j.rel_freq)/get(i.rel_freq)), i.class}
    @collect DataFrame
end

y = @from i in x begin
    @where contains(get(i.class), "c")
    @select i.log2fc
    @collect 
end

z = @from i in x begin
    @group i by i.class into g
    @where !contains(get(g[1].class), "c")
    @let test = MannWhitneyUTest(map(j->j.log2fc, g), y)
    @select {
        gene = get(g.key), 
        med_log2fc = median(map(j->j.log2fc, g)),
        pval = -log10(pvalue(test)),
    }
    @collect DataFrame
end

```

## Desired output

```julia-auto
z
# output

1×3 DataFrames.DataFrame
│ Row │ gene │ med_log2fc │ pval │
├─────┼──────┼────────────┼───────────┤
│ 1 │ "a" │ 0.0849625 │ -0.124939 │

```

---

<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:** [April 6, 2017, 3:46pm UTC](https://discourse.julialang.org/t/accessing-full-dataframe-after-a-join-within-a-query-jl-query/3076/2 "2017-04-06T15:46:21Z")

</div>

Are you using Query `master`, or the last released version?

---

<div class="post-metadata">

**Author:** ![tlnagy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tlnagy/32/5815_2.png) [@tlnagy](https://discourse.julialang.org/u/tlnagy)\
**Post date:** [April 6, 2017, 8:26pm UTC](https://discourse.julialang.org/t/accessing-full-dataframe-after-a-join-within-a-query-jl-query/3076/3 "2017-04-06T20:26:03Z")

</div>

I’m on v0.3.1 on top of julia 0.5.1

---

<div class="post-metadata">

**Author:** ![tlnagy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tlnagy/32/5815_2.png) [@tlnagy](https://discourse.julialang.org/u/tlnagy)\
**Post date:** [April 6, 2017, 8:26pm UTC](https://discourse.julialang.org/t/accessing-full-dataframe-after-a-join-within-a-query-jl-query/3076/4 "2017-04-06T20:26:46Z")

</div>

I just upgraded to the latest release.

---

<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:** [April 7, 2017, 9:37pm UTC](https://discourse.julialang.org/t/accessing-full-dataframe-after-a-join-within-a-query-jl-query/3076/5 "2017-04-07T21:37:20Z")

</div>

I don’t think this can be done in one query, and your three query version is almost certainly the most efficient way to do this.

Your line that starts with `@let negcontrol = @from l in k begin` will certainly not work: `k` is the new range variable that will refer to the current row, so using `k` as the source for another query won’t work. A query of this form:

```julia
@from i in X begin
@select i into k
...

```

will be rewritten as

```julia
temp1 = @from i in X begin
    @select i
end
@from k in temp1 begin 
...
end

```