# DataFrame query

**URL:** <https://discourse.julialang.org/t/dataframe-query/86430>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [August 27, 2022, 1:04pm UTC](https://discourse.julialang.org/t/dataframe-query/86430 "2022-08-27T13:04:28Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![roh\_codeur](https://avatars.discourse-cdn.com/v4/letter/r/ce73a5/32.png) [@roh\_codeur](https://discourse.julialang.org/u/roh_codeur)\
**Post date:** [August 27, 2022, 1:04pm UTC](https://discourse.julialang.org/t/dataframe-query/86430/1 "2022-08-27T13:04:28Z")

</div>

hi

I have a dataframe as below

```julia
   Row │ Date Ticker Close frontContract secondContract 
       │ Date String Float64 String String         
───────┼─────────────────────────────────────────────────────────────
     **1 │ 2019-01-31 E1F19 97.5275 E1F19 E1G19**
     2 │ 2019-01-30 E1F19 97.53 E1F19 E1G19
     3 │ 2019-01-29 E1F19 97.53 E1F19 E1G19
     4 │ 2019-01-21 E1F19 97.525 E1F19 E1G19
     **5 │ 2019-01-31 E1G19 98 E1F19 E1G19**

```

it has rows by Ticker and Date. For each date, I would have a front and a second Contract. I have to be able to calculate the difference between the Close price for a date for Front and Second contract. for instance, E1F19 has a frontContract of E1F19 and E1G19. the difference for 2019-01-31 would be 97.5275 - 98.

I have tried, creating it as a Matrix and using ShuftedArrays to lag and then calculating the difference and then taking it back to a Dataframe. I am sure there is a better way of doing this.

Can someone provide me with some pointers please?

thanks  
Roh

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [August 27, 2022, 2:09pm UTC](https://discourse.julialang.org/t/dataframe-query/86430/2 "2022-08-27T14:09:53Z")

</div>

> [@roh\_codeur](#):
>
> the difference for 2019-01-31 would be 97.5275 - 98.

Why would it be the case? Why not `98 - 97.5275`? Also what would it be if you had one observation for a given day, or more than 2 observations?

---

<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:** [August 27, 2022, 2:36pm UTC](https://discourse.julialang.org/t/dataframe-query/86430/3 "2022-08-27T14:36:01Z")

</div>

it is not clear what you are looking for. But could such a thing be useful to you?

```julia
julia> innerjoin(df,df[:,[:Ticker,:Close]], on=:secondCdContract=>:Ticker,makeunique=true)
5×6 DataFrame
 Row │ Date Ticker Close frontContract secondCdContract Close_1 
     │ String15 String7 Float64 String7 String7 Float64 
─────┼────────────────────────────────────────────────────────────────────────
   1 │ 31.01.2019 E1F19 97.5275 E1F19 E1G19 98.0
   2 │ 30.01.2019 E1F19 97.53 E1F19 E1G19 98.0
   3 │ 29.01.2019 E1F19 97.53 E1F19 E1G19 98.0
   4 │ 21.01.2019 E1F19 97.525 E1F19 E1G19 98.0
   5 │ 31.01.2019 E1G19 98.0 E1F19 E1G19 98.0

```

---

<div class="post-metadata">

**Author:** ![roh\_codeur](https://avatars.discourse-cdn.com/v4/letter/r/ce73a5/32.png) [@roh\_codeur](https://discourse.julialang.org/u/roh_codeur)\
**Post date:** [August 27, 2022, 2:43pm UTC](https://discourse.julialang.org/t/dataframe-query/86430/4 "2022-08-27T14:43:49Z")

</div>

I would only have one observation per day. It could be that I don’t have any observation at all, in that case I set the spread to NaN

Thanks

---

<div class="post-metadata">

**Author:** ![roh\_codeur](https://avatars.discourse-cdn.com/v4/letter/r/ce73a5/32.png) [@roh\_codeur](https://discourse.julialang.org/u/roh_codeur)\
**Post date:** [August 27, 2022, 2:44pm UTC](https://discourse.julialang.org/t/dataframe-query/86430/5 "2022-08-27T14:44:48Z")

</div>

Yeah I have tried that as well. This works with a left join. I am wondering if there is another way to achieve this, am using it as a learning opportunity

---

<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:** [August 27, 2022, 3:18pm UTC](https://discourse.julialang.org/t/dataframe-query/86430/6 "2022-08-27T15:18:12Z")

</div>

```julia
julia> innerjoin(df,df[:,[:Date,:Ticker,:Close]], on=[:secondCdContract=>:Ticker, :Date],makeunique=true)
2×6 DataFrame
 Row │ Date Ticker Close frontContract secondCdContract Close_1 
     │ String15 String7 Float64 String7 String7 Float64 
─────┼────────────────────────────────────────────────────────────────────────
   1 │ 31.01.2019 E1F19 97.5275 E1F19 E1G19 98.0
   2 │ 31.01.2019 E1G19 98.0 E1F19 E1G19 98.0

```

---

<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:** [August 27, 2022, 6:46pm UTC](https://discourse.julialang.org/t/dataframe-query/86430/7 "2022-08-27T18:46:39Z")

</div>

two other proposals, since you have not explained in detail the desired result

```julia
julia> leftjoin(df,df[:,1:3], on=[:secondCdContract=>:Ticker, :Date],makeunique=true)  
5×6 DataFrame
 Row │ Date Ticker Close frontContract secondCdContract Close_1   
     │ String15 String7 Float64 String7 String7 Float64?  
─────┼──────────────────────────────────────────────────────────────────────────
   1 │ 31.01.2019 E1F19 97.5275 E1F19 E1G19 98.0        
   2 │ 31.01.2019 E1G19 98.0 E1F19 E1G19 98.0        
   3 │ 30.01.2019 E1F19 97.53 E1F19 E1G19 missing
   4 │ 29.01.2019 E1F19 97.53 E1F19 E1G19 missing
   5 │ 21.01.2019 E1F19 97.525 E1F19 E1G19 missing

julia> combine(groupby(df,:Date), :Close=>diff=>:spread)
1×2 DataFrame
 Row │ Date spread  
     │ String15 Float64
─────┼─────────────────────
   1 │ 31.01.2019 0.4725

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [August 27, 2022, 8:06pm UTC](https://discourse.julialang.org/t/dataframe-query/86430/8 "2022-08-27T20:06:11Z")

</div>

> [@rocco\_sprmnt21](#):
>
> `combine(groupby(df,:Date), :Close=>diff=>:spread)`

Yes - this is what I would would say is a good solution assuming that the source `df` is properly pre-sorted (I would assume it is).