DevGang
Авторизоваться

Эквивалент 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}'] = table

Pandas

Может показаться очевидным использовать 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, но, на удивление, выполнить объединение двух отдельных столбцов, чтобы найти значения без ссылок, непросто. В качестве альтернативы вы можете использовать наборы, чтобы получить разницу уникальных значений двух столбцов. Но, вероятно, ваш лучший вариант - использовать простые циклы, особенно если вы хотите идентифицировать несопоставимые записи в нескольких таблицах.

#Python #Pandas
Комментарии
Чтобы оставить комментарий, необходимо авторизоваться