Соединения в стиле базы данных

Вводные сведения о соединениях

Чтобы получить полное представление об изучаемой теме, часто требуется объединить два или более наборов данных. Например, предположим, что у нас есть два следующих набора данных:

julia> using DataFrames

julia> people = DataFrame(ID=[20, 40], Name=["John Doe", "Jane Doe"])
2×2 DataFrame
 Row │ ID     Name
     │ Int64  String
─────┼─────────────────
   1 │    20  John Doe
   2 │    40  Jane Doe

julia> jobs = DataFrame(ID=[20, 40], Job=["Lawyer", "Doctor"])
2×2 DataFrame
 Row │ ID     Job
     │ Int64  String
─────┼───────────────
   1 │    20  Lawyer
   2 │    40  Doctor

Может понадобиться использовать больший набор данных, содержащий имена и должности для каждого идентификатора. Это можно сделать с помощью функции innerjoin:

julia> innerjoin(people, jobs, on = :ID)
2×3 DataFrame
 Row │ ID     Name      Job
     │ Int64  String    String
─────┼─────────────────────────
   1 │    20  John Doe  Lawyer
   2 │    40  Jane Doe  Doctor

В теории реляционных баз данных эта операция обычно называется соединением. Столбцы, используемые для определения строки, которые должны быть объединены в процессе соединения, называются ключами.

Для выполнения семи видов соединений существуют следующие функции.

innerjoin: вывод содержит строки для значений ключа, которые существуют во всех переданных фреймах данных.
leftjoin: вывод содержит строки для значений ключа, которые существуют в первом (левом) аргументе независимо от того, существует ли это значение во втором (правом) аргументе.
rightjoin: вывод содержит строки для значений ключа, которые существуют во втором (правом) аргументе независимо от того, существует ли это значение в первом (левом) аргументе.
outerjoin: вывод содержит строки для значений ключа, которые существуют в любом переданном фрейме данных.
semijoin: аналогично внутреннему соединению, но вывод ограничен столбцами из первого (левого) аргумента.
antijoin: вывод содержит строки для значений ключа, которые существуют в первом (левом), но не во втором (правом) аргументе. Как и в случае с semijoin, вывод ограничен столбцами из первого (левого) аргумента.
crossjoin: вывод представляет собой декартово произведение строк из всех переданных фреймов данных.

Дополнительные сведения см. на странице Википедии о соединениях SQL.

Вот примеры различных видов соединений.

julia> jobs = DataFrame(ID=[20, 60], Job=["Lawyer", "Astronaut"])
2×2 DataFrame
 Row │ ID     Job
     │ Int64  String
─────┼──────────────────
   1 │    20  Lawyer
   2 │    60  Astronaut

julia> innerjoin(people, jobs, on = :ID)
1×3 DataFrame
 Row │ ID     Name      Job
     │ Int64  String    String
─────┼─────────────────────────
   1 │    20  John Doe  Lawyer

julia> leftjoin(people, jobs, on = :ID)
2×3 DataFrame
 Row │ ID     Name      Job
     │ Int64  String    String?
─────┼──────────────────────────
   1 │    20  John Doe  Lawyer
   2 │    40  Jane Doe  missing

julia> rightjoin(people, jobs, on = :ID)
2×3 DataFrame
 Row │ ID     Name      Job
     │ Int64  String?   String
─────┼────────────────────────────
   1 │    20  John Doe  Lawyer
   2 │    60  missing   Astronaut

julia> outerjoin(people, jobs, on = :ID)
3×3 DataFrame
 Row │ ID     Name      Job
     │ Int64  String?   String?
─────┼────────────────────────────
   1 │    20  John Doe  Lawyer
   2 │    40  Jane Doe  missing
   3 │    60  missing   Astronaut

julia> semijoin(people, jobs, on = :ID)
1×2 DataFrame
 Row │ ID     Name
     │ Int64  String
─────┼─────────────────
   1 │    20  John Doe

julia> antijoin(people, jobs, on = :ID)
1×2 DataFrame
 Row │ ID     Name
     │ Int64  String
─────┼─────────────────
   1 │    40  Jane Doe

Перекрестные соединения — это единственный вид соединения, в котором не используется ключ on:

julia> crossjoin(people, jobs, makeunique = true)
4×4 DataFrame
 Row │ ID     Name      ID_1   Job
     │ Int64  String    Int64  String
─────┼───────────────────────────────────
   1 │    20  John Doe     20  Lawyer
   2 │    20  John Doe     60  Astronaut
   3 │    40  Jane Doe     20  Lawyer
   4 │    40  Jane Doe     60  Astronaut

Сравнение значений ключей и значений с плавающей запятой

Значения ключей из двух или более фреймов данных сравниваются с помощью функции isequal. Это соответствует типам Set и Dict в Julia Base.

Не рекомендуется использовать в качестве ключей числа с плавающей запятой: сравнения со значениями с плавающей запятой могут быть неожиданными и непредсказуемыми. Если вы используете ключи с плавающей запятой, обратите внимание, что по умолчанию возникает ошибка, когда ключи включают значения -0.0 (отрицательный ноль) или NaN. Вот пример:

julia> innerjoin(DataFrame(id=[-0.0]), DataFrame(id=[0.0]), on=:id)
ERROR: ArgumentError: Currently for numeric values `NaN` and `-0.0` in their real or imaginary components are not allowed. Such value was found in column :id in left data frame. Use CategoricalArrays.jl to wrap these values in a CategoricalVector to perform the requested join.

Это поведение можно переопределить, заключив значения ключей в категориальный вектор.

Соединение по столбцам ключей с разными именами

Чтобы соединить фреймы данных по ключам, которые имеют разные имена в левой и правой таблицах, можно передать пары left => right в качестве аргумента on:

julia> a = DataFrame(ID=[20, 40], Name=["John Doe", "Jane Doe"])
2×2 DataFrame
 Row │ ID     Name
     │ Int64  String
─────┼─────────────────
   1 │    20  John Doe
   2 │    40  Jane Doe

julia> b = DataFrame(IDNew=[20, 40], Job=["Lawyer", "Doctor"])
2×2 DataFrame
 Row │ IDNew  Job
     │ Int64  String
─────┼───────────────
   1 │    20  Lawyer
   2 │    40  Doctor

julia> innerjoin(a, b, on = :ID => :IDNew)
2×3 DataFrame
 Row │ ID     Name      Job
     │ Int64  String    String
─────┼─────────────────────────
   1 │    20  John Doe  Lawyer
   2 │    40  Jane Doe  Doctor

Вот еще один пример с несколькими столбцами.

julia> a = DataFrame(City=["Amsterdam", "London", "London", "New York", "New York"],
                     Job=["Lawyer", "Lawyer", "Lawyer", "Doctor", "Doctor"],
                     Category=[1, 2, 3, 4, 5])
5×3 DataFrame
 Row │ City       Job     Category
     │ String     String  Int64
─────┼─────────────────────────────
   1 │ Amsterdam  Lawyer         1
   2 │ London     Lawyer         2
   3 │ London     Lawyer         3
   4 │ New York   Doctor         4
   5 │ New York   Doctor         5

julia> b = DataFrame(Location=["Amsterdam", "London", "London", "New York", "New York"],
                     Work=["Lawyer", "Lawyer", "Lawyer", "Doctor", "Doctor"],
                     Name=["a", "b", "c", "d", "e"])
5×3 DataFrame
 Row │ Location   Work    Name
     │ String     String  String
─────┼───────────────────────────
   1 │ Amsterdam  Lawyer  a
   2 │ London     Lawyer  b
   3 │ London     Lawyer  c
   4 │ New York   Doctor  d
   5 │ New York   Doctor  e

julia> innerjoin(a, b, on = [:City => :Location, :Job => :Work])
9×4 DataFrame
 Row │ City       Job     Category  Name
     │ String     String  Int64     String
─────┼─────────────────────────────────────
   1 │ Amsterdam  Lawyer         1  a
   2 │ London     Lawyer         2  b
   3 │ London     Lawyer         3  b
   4 │ London     Lawyer         2  c
   5 │ London     Lawyer         3  c
   6 │ New York   Doctor         4  d
   7 │ New York   Doctor         5  d
   8 │ New York   Doctor         4  e
   9 │ New York   Doctor         5  e

Обработка повторяющихся ключей и отслеживание исходного фрейма данных

Кроме того, обратите внимание, что в последнем соединении строки 2 и 3 имели одинаковые значения переменных on в обоих соединенных DataFrame. В такой ситуации innerjoin, outerjoin, leftjoin и rightjoin выдадут все комбинации совпадающих строк. В нашем примере в результате были созданы строки от 2 до 5. Такое же поведение можно наблюдать для строк 4 и 5 в обоих соединенных DataFrame.

Чтобы проверить, что столбцы, переданные в качестве аргумента on, определяют уникальные ключи (согласно isequal) в каждом входном фрейме данных, можно задать именованный аргумент validate в виде двухэлементного кортежа или пары значений Bool, каждый элемент которого указывает, следует ли запускать проверку для соответствующего фрейма данных. Вот пример операции соединения, описанной выше.

julia> innerjoin(a, b, on = [(:City => :Location), (:Job => :Work)], validate=(true, true))
ERROR: ArgumentError: Merge key(s) are not unique in both df1 and df2. df1 contains 2 duplicate keys: (City = "London", Job = "Lawyer") and (City = "New York", Job = "Doctor"). df2 contains 2 duplicate keys: (Location = "London", Work = "Lawyer") and (Location = "New York", Work = "Doctor").

Наконец, используя именованный аргумент source, вы можете добавить в результирующий фрейм данных столбец, указывающий, появилась ли данная строка только в левом, правом или в обоих фреймах данных. Вот пример:

julia> a = DataFrame(ID=[20, 40], Name=["John", "Jane"])
2×2 DataFrame
 Row │ ID     Name
     │ Int64  String
─────┼───────────────
   1 │    20  John
   2 │    40  Jane

julia> b = DataFrame(ID=[20, 60], Job=["Lawyer", "Doctor"])
2×2 DataFrame
 Row │ ID     Job
     │ Int64  String
─────┼───────────────
   1 │    20  Lawyer
   2 │    60  Doctor

julia> outerjoin(a, b, on=:ID, validate=(true, true), source=:source)
3×4 DataFrame
 Row │ ID     Name     Job      source
     │ Int64  String?  String?  String
─────┼─────────────────────────────────────
   1 │    20  John     Lawyer   both
   2 │    40  Jane     missing  left_only
   3 │    60  missing  Doctor   right_only

Обратите внимание, что в этот раз мы также использовали именованный аргумент validate, и это не привело к ошибкам, поскольку ключи, определенные в обоих исходных фреймах данных, были уникальными.

Переименование соединенных столбцов

Часто требуется отслеживать исходный фрейм данных. Эта возможность поддерживается с помощью именованного аргумента renamecols:

julia> innerjoin(a, b, on=:ID, renamecols = "_left" => "_right")
1×3 DataFrame
 Row │ ID     Name_left  Job_right
     │ Int64  String     String
─────┼─────────────────────────────
   1 │    20  John       Lawyer

В приведенном выше примере мы добавили суффикс "_left" к столбцам без ключей из левой таблицы и суффикс "_right" к столбцам без ключей из правой таблицы.

В качестве альтернативы допускается передача функции, преобразующей имена столбцов:

julia> innerjoin(a, b, on=:ID, renamecols = lowercase => uppercase)
1×3 DataFrame
 Row │ ID     name    JOB
     │ Int64  String  String
─────┼───────────────────────
   1 │    20  John    Lawyer

Сопоставление отсутствующих значений в соединениях

По умолчанию при попытке выполнить соединение по ключу, имеющему отсутствующие (missing) значения, возникает ошибка:

julia> df1 = DataFrame(id=[1, missing, 3], a=1:3)
3×2 DataFrame
 Row │ id       a
     │ Int64?   Int64
─────┼────────────────
   1 │       1      1
   2 │ missing      2
   3 │       3      3

julia> df2 = DataFrame(id=[1, 2, missing], b=1:3)
3×2 DataFrame
 Row │ id       b
     │ Int64?   Int64
─────┼────────────────
   1 │       1      1
   2 │       2      2
   3 │ missing      3

julia> innerjoin(df1, df2, on=:id)
ERROR: ArgumentError: Missing values in key columns are not allowed when matchmissing == :error. `missing` found in column :id in left data frame.

Если вы хотите, чтобы значения missing рассматривались как равные, передайте именованный аргумент matchmissing=:equal:

julia> innerjoin(df1, df2, on=:id, matchmissing=:equal)
2×3 DataFrame
 Row │ id       a      b
     │ Int64?   Int64  Int64
─────┼───────────────────────
   1 │       1      1      1
   2 │ missing      2      3

Или же можно отбросить все строки со значениями missing. В этом случае передайте matchmissing=:notequal:

julia> innerjoin(df1, df2, on=:id, matchmissing=:notequal)
1×3 DataFrame
 Row │ id      a      b
     │ Int64?  Int64  Int64
─────┼──────────────────────
   1 │      1      1      1

Указание порядка строк в результате соединения

По умолчанию порядок строк, получаемых в результате операции соединения, не определен:

julia> df_left = DataFrame(id=[1, 2, 4, 5], left=1:4)
4×2 DataFrame
 Row │ id     left
     │ Int64  Int64
─────┼──────────────
   1 │     1      1
   2 │     2      2
   3 │     4      3
   4 │     5      4

julia> df_right = DataFrame(id=[2, 1, 3, 6, 7], right=1:5)
5×2 DataFrame
 Row │ id     right
     │ Int64  Int64
─────┼──────────────
   1 │     2      1
   2 │     1      2
   3 │     3      3
   4 │     6      4
   5 │     7      5

julia> outerjoin(df_left, df_right, on=:id)
7×3 DataFrame
 Row │ id     left     right
     │ Int64  Int64?   Int64?
─────┼─────────────────────────
   1 │     2        2        1
   2 │     1        1        2
   3 │     4        3  missing
   4 │     5        4  missing
   5 │     3  missing        3
   6 │     6  missing        4
   7 │     7  missing        5

Чтобы в результате сохранился порядок строк левой таблицы, передайте именованный аргумент order=:left:

julia> outerjoin(df_left, df_right, on=:id, order=:left)
7×3 DataFrame
 Row │ id     left     right
     │ Int64  Int64?   Int64?
─────┼─────────────────────────
   1 │     1        1        2
   2 │     2        2        1
   3 │     4        3  missing
   4 │     5        4  missing
   5 │     3  missing        3
   6 │     6  missing        4
   7 │     7  missing        5

Обратите внимание, что в этом случае ключи, отсутствующие в левой таблице, помещаются после ключей, присутствующих в ней.

Аналогично order=:right сохраняет порядок правой таблицы (и помещает ключи, отсутствующие в ней, в конец):

julia> outerjoin(df_left, df_right, on=:id, order=:right)
7×3 DataFrame
 Row │ id     left     right
     │ Int64  Int64?   Int64?
─────┼─────────────────────────
   1 │     2        2        1
   2 │     1        1        2
   3 │     3  missing        3
   4 │     6  missing        4
   5 │     7  missing        5
   6 │     4        3  missing
   7 │     5        4  missing

Левое соединение на месте

Распространенной операцией является добавление данных из эталонной таблицы в основную таблицу. Такое обновление на месте можно выполнить с помощью функции leftjoin!. В этом случае левая таблица обновляется на месте соответствующими строками из правой таблицы.

julia> main = DataFrame(id=1:4, main=1:4)
4×2 DataFrame
 Row │ id     main
     │ Int64  Int64
─────┼──────────────
   1 │     1      1
   2 │     2      2
   3 │     3      3
   4 │     4      4

julia> leftjoin!(main, DataFrame(id=[2, 4], info=["a", "b"]), on=:id);

julia> main
4×3 DataFrame
 Row │ id     main   info
     │ Int64  Int64  String?
─────┼───────────────────────
   1 │     1      1  missing
   2 │     2      2  a
   3 │     3      3  missing
   4 │     4      4  b

Обратите внимание, что при этом порядок и количество строк в левой таблице не изменяется. Поэтому, в частности, не допускается наличие повторяющихся ключей в правой таблице:

julia> leftjoin!(main, DataFrame(id=[2, 2], info_bad=["a", "b"]), on=:id)
ERROR: ArgumentError: duplicate rows found in right table