Joins

Outer Joins

One can perform a full outer join on the time indexes of two TS objects $x$ and $y$ in the following ways:

merge(x, y)
ojoin(x, y)
[x y]
hcat(x, y)

Where there are dates in the index of one that do not exist in the other, values will be filled with NaN objects. As the missing functionality matures in Julia's base syntax, it will eventually replace NaN in this context, since unfortunately NaN is only applicable for Float64 element types.

Base.merge — Function

merge(x::TS,y::TS;join::Char='o')::TS

Merge two time series objects together by index with an optionally specified join type parameter.

...

Arguments

x::TS: Left side of the join.
y::TS: Right side of the join.

Optional args:

join::Char='o'::TS: Specifies the logic used to perform the merge, and may take on the values 'o' (outer join), 'i' (inner join), 'l' (left join), or 'r' (right join). Defaults to outer join, whose result is the same as hcat(x, y) or [x y].

...

source

Temporal.ojoin — Function

ojoin(x::TS,y::TS)::TS

Outer join two TS objects by index.

Equivalent to x OUTER JOIN y ON x.index = y.index.

...

Arguments

x::TS: Left side of the join.
y::TS: Right side of the join.

...

source

Example


julia> x = TS(rand(252))
252x1 TS{Float64,Dates.Date}: 2019-05-16 to 2020-01-22

Index       A
2019-05-16  0.0135
2019-05-17  0.9222
2019-05-18  0.0969
2019-05-19  0.232
2019-05-20  0.0616
2019-05-21  0.0256
2019-05-22  0.2756
⋮
2020-01-15  0.2364
2020-01-16  0.4078
2020-01-17  0.3757
2020-01-18  0.2427
2020-01-19  0.516
2020-01-20  0.8646
2020-01-21  0.5263
2020-01-22  0.2726

julia> y = TS(rand(252), x.index .- Month(6))
252x1 TS{Float64,Dates.Date}: 2018-11-16 to 2019-07-22

Index       A
2018-11-16  0.741
2018-11-17  0.201
2018-11-18  0.3601
2018-11-19  0.8428
2018-11-20  0.4698
2018-11-21  0.3062
2018-11-22  0.4223
⋮
2019-07-15  0.8058
2019-07-16  0.0517
2019-07-17  0.3143
2019-07-18  0.9464
2019-07-19  0.8489
2019-07-20  0.7066
2019-07-21  0.3233
2019-07-22  0.1987

julia> [x y]
431x2 TS{Float64,Dates.Date}: 2018-11-16 to 2020-01-22

Index       A       A
2018-11-16  NaN     0.741
2018-11-17  NaN     0.201
2018-11-18  NaN     0.3601
2018-11-19  NaN     0.8428
2018-11-20  NaN     0.4698
2018-11-21  NaN     0.3062
2018-11-22  NaN     0.4223
⋮
2020-01-15  0.2364  NaN
2020-01-16  0.4078  NaN
2020-01-17  0.3757  NaN
2020-01-18  0.2427  NaN
2020-01-19  0.516   NaN
2020-01-20  0.8646  NaN
2020-01-21  0.5263  NaN
2020-01-22  0.2726  NaN

Inner Joins

You can do inner joins on TS objects using the ijoin function, which will remove any observations corresponding to time steps where at least one of the joined objects is missing a row. This will basically keep only the rows where the time index of the left side and the right side intersect.

Temporal.ijoin — Function

ijoin(x::TS,y::TS)::TS

Inner join two TS objects by index.

Equivalent to x INNER JOIN y on x.index = y.index.

...

Arguments

x::TS: Left side of the join.
y::TS: Right side of the join.

...

source

Example


julia> x = TS(rand(252))
252x1 TS{Float64,Dates.Date}: 2019-05-16 to 2020-01-22

Index       A
2019-05-16  0.8526
2019-05-17  0.5787
2019-05-18  0.196
2019-05-19  0.6228
2019-05-20  0.5773
2019-05-21  0.6466
2019-05-22  0.7255
⋮
2020-01-15  0.2562
2020-01-16  0.7441
2020-01-17  0.7613
2020-01-18  0.0202
2020-01-19  0.4251
2020-01-20  0.8751
2020-01-21  0.5909
2020-01-22  0.9135

julia> y = TS(rand(252), x.index .- Month(6))
252x1 TS{Float64,Dates.Date}: 2018-11-16 to 2019-07-22

Index       A
2018-11-16  0.5924
2018-11-17  0.7952
2018-11-18  0.0339
2018-11-19  0.2128
2018-11-20  0.6874
2018-11-21  0.4887
2018-11-22  0.2701
⋮
2019-07-15  0.9485
2019-07-16  0.1343
2019-07-17  0.2052
2019-07-18  0.1026
2019-07-19  0.2419
2019-07-20  0.2712
2019-07-21  0.5486
2019-07-22  0.6145

julia> ijoin(x, y)
ERROR: ArgumentError: number of rows of each array must match (got (67, 68))

Left/Right Joins

Left and right joins are performed similarly to inner joins and the typical SQL join queries using the index field each object as the joining key.

Left Join: keep all observations of the left side of the join, fill the right side with NaN's where missing the corresponding time index
Right Join: keep all observations of the right side of the join, fill the left side with NaN's where missing the corresponding time index

Temporal.ljoin — Function

ljoin(x::TS, y::TS)::TS

Left join two TS objects by index.

Equivalent to x LEFT JOIN y ON x.index = y.index.

...

Arguments

x::TS: Left side of the join.
y::TS: Right side of the join.

...

source

Temporal.rjoin — Function

rjoin(x::TS, y::TS)::TS

Right join two TS objects by index.

Equivalent to x RIGHT JOIN y ON x.index = y.index.

...

Arguments

x::TS: Left side of the join.
y::TS: Right side of the join.

...

source

Example


julia> x = TS(rand(252))
252x1 TS{Float64,Dates.Date}: 2019-05-16 to 2020-01-22

Index       A
2019-05-16  0.9315
2019-05-17  0.2851
2019-05-18  0.4727
2019-05-19  0.5239
2019-05-20  0.8506
2019-05-21  0.0623
2019-05-22  0.0445
⋮
2020-01-15  0.5265
2020-01-16  0.2678
2020-01-17  0.6413
2020-01-18  0.7467
2020-01-19  0.649
2020-01-20  0.9841
2020-01-21  0.3905
2020-01-22  0.3416

julia> y = TS(rand(252), x.index .- Month(6))
252x1 TS{Float64,Dates.Date}: 2018-11-16 to 2019-07-22

Index       A
2018-11-16  0.4106
2018-11-17  0.0253
2018-11-18  0.8195
2018-11-19  0.6797
2018-11-20  0.8516
2018-11-21  0.5604
2018-11-22  0.7756
⋮
2019-07-15  0.1556
2019-07-16  0.3056
2019-07-17  0.0976
2019-07-18  0.7951
2019-07-19  0.8698
2019-07-20  0.7484
2019-07-21  0.8929
2019-07-22  0.839

julia> ljoin(x, y)
252x2 TS{Float64,Dates.Date}: 2019-05-16 to 2020-01-22

Index       A       A
2019-05-16  0.9315  0.2566
2019-05-17  0.2851  0.3129
2019-05-18  0.4727  0.5007
2019-05-19  0.5239  0.2246
2019-05-20  0.8506  0.171
2019-05-21  0.0623  0.5091
2019-05-22  0.0445  0.0877
⋮
2020-01-15  0.5265  NaN
2020-01-16  0.2678  NaN
2020-01-17  0.6413  NaN
2020-01-18  0.7467  NaN
2020-01-19  0.649   NaN
2020-01-20  0.9841  NaN
2020-01-21  0.3905  NaN
2020-01-22  0.3416  NaN

julia> rjoin(x, y)
246x2 TS{Float64,Dates.Date}: 2018-11-16 to 2019-07-22

Index       A       A
2018-11-16  NaN     0.4106
2018-11-17  NaN     0.0253
2018-11-18  NaN     0.8195
2018-11-19  NaN     0.6797
2018-11-20  NaN     0.8516
2018-11-21  NaN     0.5604
2018-11-22  NaN     0.7756
⋮
2019-07-15  0.1239  0.8964
2019-07-16  0.1257  0.4902
2019-07-17  0.8796  0.4664
2019-07-18  0.0998  0.6436
2019-07-19  0.7728  0.8977
2019-07-20  0.5639  0.943
2019-07-21  0.56    0.1556
2019-07-22  0.8607  0.3056