Советы по выявлению неиспользуемых значений в ваших таблицах с помощью Python
Если вы работали с реляционными базами данных, вы должны знать SQL соединения — у них много вариантов использования, но в этом посте я сосредоточусь на очистке данных.
При выполнении левого, правого или полного внешнего соединения вы создаете таблицы, в которых присутствуют либо все записи, либо только записи из определенных таблиц. Для строки без совпадения помещается нулевое значение. Таким образом, соединения чрезвычайно полезны для определения отсутствующих или неиспользуемых значений.
Представьте, что в вашей базе данных есть таблица 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 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 может показаться очевидным, так как это удобный пакет для данных в 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
Ну а результат неутешителен, вроде ничего не делал. По умолчанию функция объединяет два кадра данных в единственном столбце, поэтому мы получаем все идентификаторы пользователей и ничего больше (кроме индекса). Под капотом он выполняет правильное объединение, но поскольку у нас нет дополнительных столбцов, отображать нечего. Нам нужно добавить параметр 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 показывает, существует ли запись в обоих списках или только в первом. Мы можем сделать результат еще лучше, установив исходные индексы DataFrames в единственный существующий столбец и объединив их:
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
Хотя этот подход работает, он очень неуклюж, если вы хотите объединить несколько списков (таблиц).
Наборы
Хотя это и не объединение, с помощью наборов в Python (обратите внимание, наборы не могут содержать повторяющиеся значения) можно достичь желаемого результата — определить значения, на которые нет ссылок.
set_users = set(tables['users']) set_table1 = set(tables['table1']) unreferenced_ids = set_users - set_table1
Вычитая один набор из другого, можно найти разницу между двумя наборами — элементами, присутствующими в users, но отсутствующими в table1 наборе. Это можно повторить и с оставшимися таблицами.
Использование циклов
Решение, которое сработало для меня лучше всего, состоит в том, чтобы перебирать списки (таблицы) и добавлять значения 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.
Подводя итог, если вы привыкли выполнять левые соединения таблиц в своей реляционной базе данных и хотите добиться чего-то подобного в Python, у вас есть несколько вариантов. Есть панды, но, как ни странно, выполнить соединение двух отдельных столбцов, чтобы найти значения, на которые нет ссылок, непросто. Кроме того, вы можете использовать Sets, чтобы получить разницу уникальных значений двух столбцов. Но, вероятно, лучший вариант — использовать простые циклы, особенно если вы хотите идентифицировать несопоставленные записи в нескольких таблицах.
