Эквивалент SQL Left Join в Python и его использование при очистке данных
Если вы работали с реляционными базами данных, вы должны знать SQL joins — у них много вариантов использования, но в в данной статье мы сосредоточимся на очистке данных.
При выполнении left, right или full outer joins вы создаете таблицы, в которых присутствуют либо все записи, либо только записи из определенных таблиц. Для строки, в которой нет совпадений, помещается нулевое значение. Таким образом, соединения (Join) чрезвычайно полезны для определения отсутствующих или не связанных значений.
Представьте, что у вас есть таблица users в вашей базе данных, которая содержит всех ваших пользователей. Кроме того, у вас есть несколько других таблиц, которые ссылаются на идентификаторы таблицы users, такие как posts, logins, subscriptions и т.д. Вы заинтересованы в том, чтобы выяснить, кто из пользователей может быть удален из базы данных, поскольку они не взаимодействовали с вашим сайтом осмысленно. Это можно сделать, проверив, есть ли ссылки на идентификаторы в другом месте.
SELECT
u.userID AS 'User ID',
p.userID AS 'Post table'
FROM users AS u
LEFT JOIN posts AS p ON u.userID = p.userID
-- as users can have multiple posts
GROUP BY p.userID;
User ID Post table
--------- ----------
1 Null
2 Null
3 3
4 4
5 NullПриведенная выше таблица показывает, что пользователи 1, 2, 5 не создали никаких сообщений. Возможно, вы захотите продолжить расследование и добавить logins и subscriptions — это нормально, но если у вас много дополнительных таблиц, к которым вы хотите присоединиться таким образом, у вас могут возникнуть некоторые проблемы с производительностью. СОВЕТ: если вы играете со скриптами SQL, никогда не делайте этого в своей рабочей базе данных, создайте сначала локальная копия.
Анализ таблиц на Python
Если у вас возникают проблемы с производительностью или вам нужны лучшие инструменты для анализа вашей базы данных, одна из идей - обратиться к python, поскольку у него прекрасная экосистема для обработки данных. Вы можете использовать, например, SQLAlchemy или функции SQL magic от Jupyter Notebook для получения записей и сохранения их в списках (или словарях).
Чтобы продемонстрировать, как выполнить левое внешнее соединение в python, в данном случае мы не собираемся подключаться к базе данных, вместо этого создадим некоторые случайные данные и сохраним их в словаре. У нас будет таблица users со всеми возможными идентификаторами пользователей и пять других таблиц, случайным образом ссылающихся на идентификаторы:
import random
import pandas as pd
# defining range for userIDs, one to ten
r = (1, 11)
s, e = r
# creating dict to hold 'tables' and adding all possible user IDs
tables = {}
tables['users'] = list(range(*r))
# generating ten tables with random IDs from the initial defined range of userIDs
for i in range(1, 6):
table = random.sample(range(*r), random.randint(s-1, e-1))
tables[f'table{i}'] = tablePandas
Может показаться очевидным использовать pandas, поскольку это основной пакет для данных в python. У него есть две функции для объединения таблиц, pd.merge() и pd.join() (также pd.concat() — обратите внимание, что это работает немного по-другому), но эти функции работают лучше всего, если у вас есть по крайней мере два столбца, один из которых вы объединяете, а другой содержит ваш ценности. Но это не наш случай, поскольку у нас есть только списки идентификаторов.
Давайте посмотрим, что произойдет, если мы объединим два из этих списков, tables['users'] и tables['table1']:
df_users = pd.DataFrame(tables['users'])
df_table1 = pd.DataFrame(tables['table1'])
pd.merge(df_users, df_table1, how='left')OUTPUT:
0
---
0 1
1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
9 10Что ж, результат разочаровывает, похоже, это ничего не дало. По умолчанию функция объединяет два DataFrames в единственном столбце, таким образом, мы получаем все идентификаторы пользователей и ничего больше (кроме индекса). Под капотом он действительно выполняет правильное соединение, но поскольку у нас нет дополнительных столбцов, отображать нечего. Нам нужно добавить параметр indicator=True, чтобы увидеть результат:
pd.merge(df_users, df_table1, how='left', indicator=True)OUTPUT:
0 _merge
--------- ---------
0 1 left_only
1 2 left_only
2 3 both
3 4 both
4 5 left_only
5 6 left_only
6 7 both
7 8 left_only
8 9 left_only
9 10 bothСтолбец _merge показывает, существует ли запись в обоих списках или только в первом. Мы можем сделать результат еще лучше, установив индексы исходных фреймов данных для единственного существующего столбца и объединив их:
pd.merge(df_users.set_index(0), df_table1.set_index(0), how='left',
left_index=True, right_index=True, indicator=True)
OUTPUT:
_merge
---------
0
1 left_only
2 left_only
3 both
4 both
5 left_only
6 left_only
7 both
8 left_only
9 left_only
10 bothХотя этот подход работает, он действительно неуклюж, если вы хотите объединить несколько списков (таблиц).
Set
Хотя это не объединение, с помощью наборов Python (обратите внимание, наборы не могут содержать дублирующиеся значения) может быть достигнут желаемый результат — идентификация значений, на которые нет ссылок.
set_users = set(tables['users'])
set_table1 = set(tables['table1'])
unreferenced_ids = set_users - set_table1Вычтя один набор из другого, вы можете найти разницу между двумя Set — элементами, присутствующими в users, но не в Set table1. Это можно повторить и с остальными таблицами.
Использование loop
Решение, которое сработало лучше всего, - это перебирать списки (таблицы) и добавлять значения None для идентификаторов без ссылок. Это возможно, потому что списки упорядочены, и мы можем перебирать все идентификаторы пользователей и проверять, существуют ли они в других таблицах.
# creating a new dict
final_tables = {}
# transfering user IDs
final_tables['users'] = tables.pop('users')
# looping through the tables
for key, value in tables.items():
# creating a new column
column = []
# checking values against all user IDs
for user in final_tables['users']:
# adding True if ID is referenced
if user in value:
column.append(True)
# adding None if ID is not referenced
else:
column.append(None)
final_tables[key] = column
# converting the new dict holding the processed tables to a dataframe
df = pd.DataFrame.from_dict(final_tables).set_index('users')OUTPUT:
table1 table2 table3 table4 table5
------ ------ ------ ------ ------
users
1 True True True True True
2 True None Nooe True None
3 None True True None True
4 None None True True True
5 True None None True None
6 True True True None True
7 None None True True True
8 True True None True None
9 True None True None None
10 None None True True NoneТаблица, показывающая, как идентификаторы пользователей ссылаются на другие таблицы в фрейме данных pandas.
Подводя итог, если вы привыкли выполнять left table joins в своей реляционной базе данных и хотите достичь чего-то подобного в Python, у вас есть несколько вариантов. Существует pandas, но, на удивление, выполнить объединение двух отдельных столбцов, чтобы найти значения без ссылок, непросто. В качестве альтернативы вы можете использовать наборы, чтобы получить разницу уникальных значений двух столбцов. Но, вероятно, ваш лучший вариант - использовать простые циклы, особенно если вы хотите идентифицировать несопоставимые записи в нескольких таблицах.
