# Using DataFrames to split overlapping datetime intervals into non overlapping intervals

**URL:** <https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149>\
**Category:** General Usage\
**Tags:** question, dataframes, dataframesmeta\
**Created:** [July 4, 2023, 3:52am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149 "2023-07-04T03:52:06Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [July 4, 2023, 3:52am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/1 "2023-07-04T03:52:07Z")

</div>

```julia

test = DataFrame(Time1 = [DateTime(2022, 1, 1, 10, 0, 0), DateTime(2022, 1, 1, 10, 5, 0), DateTime(2022, 1, 1, 10, 14, 0)], Time2 = [DateTime(2022, 1, 1, 10, 15, 0), DateTime(2022, 1, 1, 10, 10, 0), DateTime(2022, 1, 1, 10, 20, 0)], 
Value = [10, 15, 20])

```

![image](https://global.discourse-cdn.com/julialang/original/3X/4/2/42995709d02721704bda85a8af13a78c93a985d9.png)  
Given the test dataframe above, I want to convert it to every single time range that can be constructed with the Value column summed up for all the times that lie in that time range as shown in the result below:

```julia
result = DataFrame(Time1 = [DateTime(2022, 1, 1, 10, 0, 0), DateTime(2022, 1, 1, 10, 5, 0), DateTime(2022, 1, 1, 10, 10, 0), DateTime(2022, 1, 1, 10, 14, 0), DateTime(2022, 1, 1, 10, 15, 0)], 
Time2 = [DateTime(2022, 1, 1, 10, 5, 0), DateTime(2022, 1, 1, 10, 10, 0), DateTime(2022, 1, 1, 10, 14, 0), DateTime(2022, 1, 1, 10, 15, 0), DateTime(2022, 1, 1, 10, 20, 0)], ValueSum = [10, 25, 10, 30,20])

```

![image](https://global.discourse-cdn.com/julialang/original/3X/d/8/d855b3166b5ce9d39c53cae932a533c2d468f878.png)

Any idea how to achieve this?

---

<div class="post-metadata">

**Author:** ![gzagatti](https://avatars.discourse-cdn.com/v4/letter/g/97f17d/32.png) [@gzagatti](https://discourse.julialang.org/u/gzagatti)\
**Post date:** [July 4, 2023, 5:45am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/2 "2023-07-04T05:45:37Z")

</div>

I faced a similar problem on a dataset with similar format as yours. You can find a session resolution algorithm that I proposed in this demo paper: [A Large-scale Disease Outbreak Analytics System based on Wi-Fi Session Logs | IEEE Conference Publication | IEEE Xplore](https://ieeexplore.ieee.org/document/9474895)

 ![image](https://global.discourse-cdn.com/julialang/original/3X/a/6/a6529bb17860f7558a01b3ae2635e5aa0e19ca60.png)

 ![image](https://global.discourse-cdn.com/julialang/original/3X/b/d/bd62b020c9d8f822283017cb81f8696a3dc0d325.png)

And here is the code to resolve the sessions in SQL: [https://github.com/NUS-IDS/cofi/blob/master/bin/02\_stationary\_view\_v2.py](https://github.com/NUS-IDS/cofi/blob/master/bin/02_stationary_view_v2.py)

I reckon the algorithm could be adapted to Julia.

---

<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:** [July 4, 2023, 6:36am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/3 "2023-07-04T06:36:40Z")

</div>

![overlappintervals](https://global.discourse-cdn.com/julialang/original/3X/3/0/30b3cd7cbc0828eff54358a67dea1bc2e7c4ead4.jpeg)

---

<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:** [July 4, 2023, 8:36am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/4 "2023-07-04T08:36:54Z")

</div>

Here’s at least a start on generating the result dataframe:

```julia
julia> all_times = sort(unique([test.Time1; test.Time2]));

julia> time_pairs = [(all_times[i], all_times[i+1]) for i ∈ 1:length(all_times)-1];

julia> res = DataFrame(start = first.(time_pairs), ending = last.(time_pairs))
5×2 DataFrame
 Row │ start ending
     │ DateTime DateTime
─────┼──────────────────────────────────────────
   1 │ 2022-01-01T10:00:00 2022-01-01T10:05:00
   2 │ 2022-01-01T10:05:00 2022-01-01T10:10:00
   3 │ 2022-01-01T10:10:00 2022-01-01T10:14:00
   4 │ 2022-01-01T10:14:00 2022-01-01T10:15:00
   5 │ 2022-01-01T10:15:00 2022-01-01T10:20:00

```

You can then go through the original dataframe and check overlap row by row, although whether that is feasible or whether you need a smarter algorithm will pobably depend on how large your actual data is…

---

<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:** [July 4, 2023, 8:55am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/5 "2023-07-04T08:55:51Z")

</div>

```julia
julia> res.ValueSum=cumsum(last.(sort(mapreduce(((s,e,v),)->[(s,v),(e,-v)] ,vcat, zip(test.Time1, test.Time2, test.Value)))))[1:end-1] 
5-element Vector{Int64}:
 10
 25
 10
 30
 20

julia> res
5×3 DataFrame
 Row │ start ending ValueSum 
     │ DateTime DateTime Int64
─────┼────────────────────────────────────────────────────
   1 │ 2022-01-01T10:00:00 2022-01-01T10:05:00 10
   2 │ 2022-01-01T10:05:00 2022-01-01T10:10:00 25
   3 │ 2022-01-01T10:10:00 2022-01-01T10:14:00 10
   4 │ 2022-01-01T10:14:00 2022-01-01T10:15:00 30
   5 │ 2022-01-01T10:15:00 2022-01-01T10:20:00 20

```

---

<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:** [July 4, 2023, 9:55am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/6 "2023-07-04T09:55:48Z")

</div>

I have some doubts about the presence of unique, in case there are more intervals with the same start.  
I have a mental model of considering the timestamps as stations on a railway line. A little train leaves from the first station and at each station (startTime/stoptime) people get on and/or off, each with her weight. The ValueSum is the load that the train carries for each leg.

```julia
julia> test = DataFrame(Time1 = [DateTime(2022, 1, 1, 10, 0, 0), DateTime(2022, 1, 1, 10, 5, 0), DateTime(2022, 1, 1, 10, 5, 0),DateTime(2022, 1, 1, 10, 14, 0)], Time2 = [DateTime(2022, 1, 1, 10, 15, 0), DateTime(2022, 1, 1, 10, 10, 0),DateTime(2022, 1, 1, 10, 14, 0), DateTime(2022, 1, 1, 10, 20, 0)],
       Value = [10, 15,5, 20])
4×3 DataFrame
 Row │ Time1 Time2 Value 
     │ DateTime DateTime Int64
─────┼─────────────────────────────────────────────────
   1 │ 2022-01-01T10:00:00 2022-01-01T10:15:00 10
   2 │ 2022-01-01T10:05:00 2022-01-01T10:10:00 15
   3 │ 2022-01-01T10:05:00 2022-01-01T10:14:00 5
   4 │ 2022-01-01T10:14:00 2022-01-01T10:20:00 20

julia> dfr=DataFrame(t1=sort([test.Time1; test.Time2])[1:end-1],t2=sort([test.Time1; test.Time2])[2:end])
7×2 DataFrame
 Row │ t1 t2
     │ DateTime DateTime
─────┼──────────────────────────────────────────
   1 │ 2022-01-01T10:00:00 2022-01-01T10:05:00
   2 │ 2022-01-01T10:05:00 2022-01-01T10:05:00
   3 │ 2022-01-01T10:05:00 2022-01-01T10:10:00
   4 │ 2022-01-01T10:10:00 2022-01-01T10:14:00
   5 │ 2022-01-01T10:14:00 2022-01-01T10:14:00
   6 │ 2022-01-01T10:14:00 2022-01-01T10:15:00
   7 │ 2022-01-01T10:15:00 2022-01-01T10:20:00

julia> dfr.ValueSum=cumsum(last.(sort(mapreduce(((s,e,v),)->[(s,v),(e,-v)] ,vcat, zip(test.Time1, test.Time2, test.Value)))))[1:end-1] 
7-element Vector{Int64}:
 10
 15
 30
 15
 10
 30
 20

julia> dfr
7×3 DataFrame
 Row │ t1 t2 ValueSum 
     │ DateTime DateTime Int64
─────┼────────────────────────────────────────────────────
   1 │ 2022-01-01T10:00:00 2022-01-01T10:05:00 10
   2 │ 2022-01-01T10:05:00 2022-01-01T10:05:00 15
   3 │ 2022-01-01T10:05:00 2022-01-01T10:10:00 30
   4 │ 2022-01-01T10:10:00 2022-01-01T10:14:00 15
   5 │ 2022-01-01T10:14:00 2022-01-01T10:14:00 10
   6 │ 2022-01-01T10:14:00 2022-01-01T10:15:00 30
   7 │ 2022-01-01T10:15:00 2022-01-01T10:20:00 20

julia> filter(r->r.t1!=r.t2,dfr)
5×3 DataFrame
 Row │ t1 t2 ValueSum 
     │ DateTime DateTime Int64
─────┼────────────────────────────────────────────────────
   1 │ 2022-01-01T10:00:00 2022-01-01T10:05:00 10
   2 │ 2022-01-01T10:05:00 2022-01-01T10:10:00 30
   3 │ 2022-01-01T10:10:00 2022-01-01T10:14:00 15
   4 │ 2022-01-01T10:14:00 2022-01-01T10:15:00 30
   5 │ 2022-01-01T10:15:00 2022-01-01T10:20:00 20

```

---

<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:** [July 4, 2023, 11:42am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/7 "2023-07-04T11:42:29Z")

</div>

> [@nilshg](#):
>
> Here’s at least a start on generating the result dataframe:  
> \<…\>  
> You can then go through the original dataframe and check overlap row by row, although whether that is feasible or whether you need a smarter algorithm will pobably depend on how large your actual data is…

You may also try `FlexiJoins`, and `innerjoin` the original table to this one by @nilshg with final intervals. Use something like `innerjoin((res, test), by_predicate(x->x.start..x.end, ⊆, x->x.Time1..x.Time2), groupby=1)` and sum afterwards. Not tested rn, maybe the `groupby` argument doesn’t work for dataframes – then just group and aggregate afterwards.

---

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [July 4, 2023, 3:09pm UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/8 "2023-07-04T15:09:29Z")

</div>

Wow you guys are amazing. Thanks @rocco_sprmnt21 @nilshg @aplavin , will play around with the solutions and see which one works well!

---

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [July 22, 2023, 5:34am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/9 "2023-07-22T05:34:29Z")

</div>

@aplavin I see that you have a new package IntervalUnion. Do you think that would help create non overlapping intervals?  
I haven’t had a chance to work on a solution to the question posted here but I saw your package on twitter so wondered if the package would help

---

<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:** [July 23, 2023, 7:00am UTC](https://discourse.julialang.org/t/using-dataframes-to-split-overlapping-datetime-intervals-into-non-overlapping-intervals/101149/10 "2023-07-23T07:00:58Z")

</div>

[IntervalUnions.jl](https://gitlab.com/aplavin/IntervalUnions.jl) is for representing unions of intervals with corresponding set operations, like `in`, `union`, etc. It should be possible to add its efficient support to `FlexiJoins`, even though I don’t have actual plans to do that in the near future.

But as I understand `IntervalUnions` is not really related to this particular problem discussed here.