08. joins

Introduction to DataFrames¶

Bogumił Kamiński, Apr 21, 2017

Reference¶

• https://github.com/JuliaComputing/JuliaBoxTutorials/tree/master/introductory-tutorials/broader-topics-and-ecosystem/intro-to-julia-DataFrames

Series¶

• https://deepstat.tistory.com/69 (01. constructors)(in English)
• https://deepstat.tistory.com/70 (01. constructors)(한글)
• https://deepstat.tistory.com/71 (02. basicinfo)(in English)
• https://deepstat.tistory.com/72 (02. basicinfo)(한글)
• https://deepstat.tistory.com/73 (03. missingvalues)(in English)
• https://deepstat.tistory.com/74 (03. missingvalues)(한글)
• https://deepstat.tistory.com/75 (04. loadsave)(in English)
• https://deepstat.tistory.com/76 (04. loadsave)(한글)
• https://deepstat.tistory.com/77 (05. columns)(in English)
• https://deepstat.tistory.com/78 (05. columns)(한글)
• https://deepstat.tistory.com/79 (06. rows)(in English)
• https://deepstat.tistory.com/80 (06. rows)(한글)
• https://deepstat.tistory.com/81 (07. factors)(in English)
• https://deepstat.tistory.com/82 (07. factors)(한글)
• https://deepstat.tistory.com/83 (08. joins)(in English)
• https://deepstat.tistory.com/84 (08. joins)(한글)

In [1]:

using DataFrames # load package

Joining DataFrames¶

Preparing DataFrames for a join¶

In [2]:

x = DataFrame(ID=[1,2,3,4,missing], name = ["Alice", "Bob", "Conor", "Dave","Zed"])
y = DataFrame(id=[1,2,5,6,missing], age = [21,22,23,24,99])
println(x)
println(y)

5×2 DataFrame
│ Row │ ID      │ name   │
│     │ Int64⍰  │ String │
├─────┼─────────┼────────┤
│ 1   │ 1       │ Alice  │
│ 2   │ 2       │ Bob    │
│ 3   │ 3       │ Conor  │
│ 4   │ 4       │ Dave   │
│ 5   │ missing │ Zed    │
5×2 DataFrame
│ Row │ id      │ age   │
│     │ Int64⍰  │ Int64 │
├─────┼─────────┼───────┤
│ 1   │ 1       │ 21    │
│ 2   │ 2       │ 22    │
│ 3   │ 5       │ 23    │
│ 4   │ 6       │ 24    │
│ 5   │ missing │ 99    │

In [3]:

rename!(x, :ID=>:id) # names of columns on which we want to join must be the same

Out[3]:

	id	name
	Int64⍰	String
1	1	Alice
2	2	Bob
3	3	Conor
4	4	Dave
5	missing	Zed

Standard joins: inner, left, right, outer, semi, anti¶

In [4]:

join(x, y, on=:id) # :inner join by default, missing is joined

Out[4]:

	id	name	age
	Int64⍰	String	Int64
1	1	Alice	21
2	2	Bob	22
3	missing	Zed	99

In [5]:

join(x, y, on=:id, kind=:left)

Out[5]:

	id	name	age
	Int64⍰	String	Int64⍰
1	1	Alice	21
2	2	Bob	22
3	3	Conor	missing
4	4	Dave	missing
5	missing	Zed	99

In [6]:

join(x, y, on=:id, kind=:right)

Out[6]:

	id	name	age
	Int64⍰	String⍰	Int64
1	1	Alice	21
2	2	Bob	22
3	missing	Zed	99
4	5	missing	23
5	6	missing	24

In [7]:

join(x, y, on=:id, kind=:outer)

Out[7]:

	id	name	age
	Int64⍰	String⍰	Int64⍰
1	1	Alice	21
2	2	Bob	22
3	3	Conor	missing
4	4	Dave	missing
5	missing	Zed	99
6	5	missing	23
7	6	missing	24

In [8]:

join(x, y, on=:id, kind=:semi)

Out[8]:

	id	name
	Int64⍰	String
1	1	Alice
2	2	Bob
3	missing	Zed

In [9]:

join(x, y, on=:id, kind=:anti)

Out[9]:

	id	name
	Int64⍰	String
1	3	Conor
2	4	Dave

Cross join¶

In [10]:

# cross-join does not require on argument
# it produces a Cartesian product or arguments
function expand_grid(;xs...) # a simple replacement for expand.grid in R
    reduce((x,y) -> join(x, DataFrame(Pair(y...)), kind=:cross),
           DataFrame(Pair(xs[1]...)), xs[2:end])
end

expand_grid(a=[1,2], b=["a","b","c"], c=[true,false])

ArgumentError: unable to construct DataFrame from Pair{Int64,Int64}

Stacktrace:
 [1] DataFrame(::Pair{Int64,Int64}) at /home/yt/.julia/packages/DataFrames/1PqZ3/src/other/tables.jl:32
 [2] #expand_grid#3(::Base.Iterators.Pairs{Symbol,Array{T,1} where T,Tuple{Symbol,Symbol,Symbol},NamedTuple{(:a, :b, :c),Tuple{Array{Int64,1},Array{String,1},Array{Bool,1}}}}, ::Function) at ./In[10]:4
 [3] (::getfield(Main, Symbol("#kw##expand_grid")))(::NamedTuple{(:a, :b, :c),Tuple{Array{Int64,1},Array{String,1},Array{Bool,1}}}, ::typeof(expand_grid)) at ./none:0
 [4] top-level scope at In[10]:7

In [11]:

?reduce

search: reduce mapreduce

Out[11]:

reduce(op, itr; [init])

Reduce the given collection itr with the given binary operator op. If provided, the initial value init must be a neutral element for op that will be returned for empty collections. It is unspecified whether init is used for non-empty collections.

For empty collections, providing init will be necessary, except for some special cases (e.g. when op is one of +, *, max, min, &, |) when Julia can determine the neutral element of op.

Reductions for certain commonly-used operators may have special implementations, and should be used instead: maximum(itr), minimum(itr), sum(itr), prod(itr), any(itr), all(itr).

The associativity of the reduction is implementation dependent. This means that you can't use non-associative operations like - because it is undefined whether reduce(-,[1,2,3]) should be evaluated as (1-2)-3 or 1-(2-3). Use foldl or foldr instead for guaranteed left or right associativity.

Some operations accumulate error. Parallelism will be easier if the reduction can be executed in groups. Future versions of Julia might change the algorithm. Note that the elements are not reordered if you use an ordered collection.

Examples¶

jldoctest
julia> reduce(*, [2; 3; 4])
24

julia> reduce(*, [2; 3; 4]; init=-1)
-24

---

reduce(f, A; dims=:, [init])

Reduce 2-argument function f along dimensions of A. dims is a vector specifying the dimensions to reduce, and the keyword argument init is the initial value to use in the reductions. For +, *, max and min the init argument is optional.

The associativity of the reduction is implementation-dependent; if you need a particular associativity, e.g. left-to-right, you should write your own loop or consider using foldl or foldr. See documentation for reduce.

Examples¶

jldoctest
julia> a = reshape(Vector(1:16), (4,4))
4×4 Array{Int64,2}:
 1  5   9  13
 2  6  10  14
 3  7  11  15
 4  8  12  16

julia> reduce(max, a, dims=2)
4×1 Array{Int64,2}:
 13
 14
 15
 16

julia> reduce(max, a, dims=1)
1×4 Array{Int64,2}:
 4  8  12  16

Complex cases of joins¶

In [12]:

x = DataFrame(id1=[1,1,2,2,missing,missing],
              id2=[1,11,2,21,missing,99],
              name = ["Alice", "Bob", "Conor", "Dave","Zed", "Zoe"])
y = DataFrame(id1=[1,1,3,3,missing,missing],
              id2=[11,1,31,3,missing,999],
              age = [21,22,23,24,99, 100])
println(x)
println(y)

6×3 DataFrame
│ Row │ id1     │ id2     │ name   │
│     │ Int64⍰  │ Int64⍰  │ String │
├─────┼─────────┼─────────┼────────┤
│ 1   │ 1       │ 1       │ Alice  │
│ 2   │ 1       │ 11      │ Bob    │
│ 3   │ 2       │ 2       │ Conor  │
│ 4   │ 2       │ 21      │ Dave   │
│ 5   │ missing │ missing │ Zed    │
│ 6   │ missing │ 99      │ Zoe    │
6×3 DataFrame
│ Row │ id1     │ id2     │ age   │
│     │ Int64⍰  │ Int64⍰  │ Int64 │
├─────┼─────────┼─────────┼───────┤
│ 1   │ 1       │ 11      │ 21    │
│ 2   │ 1       │ 1       │ 22    │
│ 3   │ 3       │ 31      │ 23    │
│ 4   │ 3       │ 3       │ 24    │
│ 5   │ missing │ missing │ 99    │
│ 6   │ missing │ 999     │ 100   │

In [13]:

join(x, y, on=[:id1, :id2]) # joining on two columns

Out[13]:

	id1	id2	name	age
	Int64⍰	Int64⍰	String	Int64
1	1	1	Alice	22
2	1	11	Bob	21
3	missing	missing	Zed	99

In [14]:

join(x, y, on=[:id1], makeunique=true) # with duplicates all combinations are produced (here :inner join)

Out[14]:

	id1	id2	name	id2_1	age
	Int64⍰	Int64⍰	String	Int64⍰	Int64
1	1	1	Alice	11	21
2	1	1	Alice	1	22
3	1	11	Bob	11	21
4	1	11	Bob	1	22
5	missing	missing	Zed	missing	99
6	missing	missing	Zed	999	100
7	missing	99	Zoe	missing	99
8	missing	99	Zoe	999	100

In [15]:

join(x, y, on=[:id1], kind=:semi) # but not by :semi join (as it would duplicate rows)

Out[15]:

	id1	id2	name
	Int64⍰	Int64⍰	String
1	1	1	Alice
2	1	11	Bob
3	missing	missing	Zed
4	missing	99	Zoe