# Using a DataFrame to calculate another column in a separate DataFrame

**URL:** <https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747>\
**Category:** Performance\
**Tags:** performance, dataframes\
**Created:** [June 4, 2020, 5:20pm UTC](https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747 "2020-06-04T17:20:24Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Derek\_Vetsch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/derek_vetsch/32/8552_2.png) [@Derek\_Vetsch](https://discourse.julialang.org/u/Derek_Vetsch)\
**Post date:** [June 4, 2020, 5:20pm UTC](https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747/1 "2020-06-04T17:20:24Z")

</div>

I am using a DataFrame `invoices` that looks like this:  
 ![image](https://global.discourse-cdn.com/julialang/original/3X/d/6/d6a36463e1192065a5b8a09d6457ea787703eb1d.png)  
to count the number of invoices for a given contract on a given date that have happened. The functions I’m using to do this are:

```julia
using DataFrames, DataFramesMeta, Dates, Lazy

function num_invoices_setup(dt::Date, cak_id::String)
    (@> begin
        invoices::DataFrame
        @where :contract_id .== cak_id
        @where :due_date .<= dt
    end)::DataFrame # begin
end # function

function num_invoices(dt::Date, cak_id::String, mod_post_date::Union{Date, Missing}, mod_effective_date::Union{Date, Missing})
    df::DataFrame = num_invoices_setup(dt, cak_id)
    if mod_effective_date |> ismissing
        return (nrow(df))::Int64
    else
        return (filter(r -> r.due_date > mod_effective_date, df) |> nrow)::Int64
    end # if
end # function

```

Everything works but takes a really long time to complete when using `num_invoices` to calculate a column in a DataFrame that is ~1.2M rows.

Here is the output of `@code_warntype` for num\_invoices:

```julia
![Annotation warntype|690x348](upload://dxnug8XCKKaFGaspoPcUrO10yDi.png) 

```

So right now it is taking about 2 hours for `num_invoices`. Any suggestions for how to speed this up?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [June 4, 2020, 6:08pm UTC](https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747/2 "2020-06-04T18:08:30Z")

</div>

Welcome to the forum! Glad you are using dataframes.

2 hours is a _very_ long time for this kind of calculation. This should be done in a few seconds at most using `combine`. Using the latest version of DataFrames you can do

```julia
combine(groupby(df, [:contract_id, :due_date]), nrow)

```

As a side note, you really don’t need the type annotations you are using. They don’t help with performance and will make things more fragile than they need to be.

---

<div class="post-metadata">

**Author:** ![Derek\_Vetsch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/derek_vetsch/32/8552_2.png) [@Derek\_Vetsch](https://discourse.julialang.org/u/Derek_Vetsch)\
**Post date:** [June 6, 2020, 12:59am UTC](https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747/3 "2020-06-06T00:59:09Z")

</div>

Thanks for the reply. Sorry, I think I explained my issue poorly. So I need to call `num_invoices` as the function in `transform!`, so the two hours comes from ~0.007 seconds times the 1.25 million rows in the DataFrame that `transform!` updates. I agree that your implementation is cleaner and more concise, but am struggling to find a way to speed this up beyond that…

---

<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:** [June 6, 2020, 9:26am UTC](https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747/4 "2020-06-06T09:26:59Z")

</div>

Can you maybe make an example DataFrame with some random data that roughly matches what you’re working on and the output you’re expecting?

I agree with Peter that it feels like it should be possible to do this a lot faster, particularly since your code only seems to count rows of subgroups of your DataFrame, but it’s currently not clear to me what exactly you’re trying to do here.

---

<div class="post-metadata">

**Author:** ![dlakelan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dlakelan/32/8491_2.png) [@dlakelan](https://discourse.julialang.org/u/dlakelan)\
**Post date:** [June 6, 2020, 12:37pm UTC](https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747/5 "2020-06-06T12:37:46Z")

</div>

it sounds a little like you’re doing a “join” and should do the join all at once [Joins · DataFrames.jl](https://juliadata.github.io/DataFrames.jl/stable/man/joins/)

---

<div class="post-metadata">

**Author:** ![Derek\_Vetsch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/derek_vetsch/32/8552_2.png) [@Derek\_Vetsch](https://discourse.julialang.org/u/Derek_Vetsch)\
**Post date:** [June 10, 2020, 5:02am UTC](https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747/6 "2020-06-10T05:02:51Z")

</div>

Sorry for the delayed response - couldn’t get to a REPL for a few days.  
A join is exactly what I am trying to do… I probably should have thought of that…

Just out of curiosity, is there a way way to do something akin to SQL `left join tbl1 on tbl1.column1 = tbl2.column2 and tbl1.column3 < tbl2.column4`? The less than condition doesn’t seem super intuitive from reading the docs, but I imagine it’s more likely a me problem.

---

<div class="post-metadata">

**Author:** ![dlakelan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dlakelan/32/8491_2.png) [@dlakelan](https://discourse.julialang.org/u/dlakelan)\
**Post date:** [June 10, 2020, 6:36pm UTC](https://discourse.julialang.org/t/using-a-dataframe-to-calculate-another-column-in-a-separate-dataframe/40747/7 "2020-06-10T18:36:00Z")

</div>

I believe what you want is a join on column1=column2 followed by a filter on column3 \< column4

If you have some complicated SQL you’d like to carry out on this data, you could always try slamming the DataFrames into SQLite and then running an SQL query against it.