Как переписать и оптимизировать ваши SQL-запросы на Pandas

Аналитики данных, инженеры и учёные обычно хорошо знакомы с SQL. Язык запросов по-прежнему широко используется для работы с реляционными базами данных любого типа.

Однако, в настоящее время, всё больше и больше, особенно для аналитиков данных, растут технические требования, и ожидается, что люди, по крайней мере, знают основы некоторых языков программирования. При работе с данными Python и Pandas являются обычным дополнением к списку требований в описании вакансий.

Хотя Pandas может быть новым для людей, знакомых с SQL, концепции селектов, фильтрации и агрегирования данных в SQL легко переносятся в Pandas. Давайте рассмотрим в этой статье некоторые распространённые SQL-запросы и способы их написания и оптимизации в Pandas.

Не стесняйтесь следовать статье в блокноте или собственной IDE. Вы можете скачать набор данных с Kaggle здесь, ведь он доступен для бесплатного использования по лицензии CC0 1.0 Universal (CC0 1.0) Public Domain Dedication.

https://t.me/addlist/2Ls-snqEeytkMDgy – Машинное обучение, анализ данных и sql, подборка полезных каналов для датасаентистов.

Просто импортируйте и запустите следующий код, и давайте начнем!

import pandas as pd
from functools import reduce

df = pd.read_csv("/Users/byrondolon/Desktop/Updated_sales.csv")
df.columns = [i.replace(" ", "_") for i in df.columns]py

Простые примеры SQL-запросов и их эквивалентов Pandas

Запрос всей таблицы

Давайте взглянем на классический запрос SELECT ALL из таблицы.

Вот вариант SQL:

SELECT * FROM df

А вот Pandas:

df

Всё, что вам нужно сделать, это вызвать DataFrame в Pandas, чтобы вернуть всю таблицу и все её столбцы.

Вы также можете просто просмотреть небольшое подмножество таблицы в качестве быстрой проверки перед написанием более сложного запроса. В SQL вы должны использовать LIMIT 10 или что-то подобное, чтобы получить только выбранное количество строк. В Pandas аналогичным образом вы можете вызывать df.head(10) или df.tails(10) для получения первых или последних 10 строк таблицы.

Запрос таблицы без нулевых значений

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

SELECT * FROM df WHERE Order_ID IS NOT NULL

В Pandas у вас есть два варианта:

# Option 1
df.dropna(subset="Order_ID")

# Option 2
df.loc[df["Order_ID"].notna()]

Теперь в таблице, которую мы получаем, нет нулевых значений из столбца Order_ID. Оба варианта вернут таблицу без нулевых значений, но они работают немного по-разному.

Вы можете использовать метод dropna в Pandas для возврата DataFrame без каких-либо пустых строк, указав в параметре subset, из каких столбцов вы хотите удалить пустые значения.

В качестве альтернативы метод loc, позволяющий передать маску или логическую метку, которую вы можете указать для фильтрации фрейма данных. Здесь мы передаём df["Order_ID"].notna(), который, если бы вы вызывали его сам по себе, вернул бы серию значений True и False, которые могут сопоставляться с исходными строками DataFrame для определения того, является ли значение Order_ID null.

Запрос определённых столбцов из таблицы

Далее, вместо того, чтобы выбирать все столбцы из таблицы, давайте выберем только несколько определённых. В SQL вы бы написали названия столбцов в части запроса SELECT следующим образом:

SELECT Order_ID, Product, Quantity_Ordered FROM df

В Pandas мы бы написали такой код:

df[["Order_ID", "Product", "Quantity_Ordered"]]

Чтобы выбрать определённое подмножество столбцов, вы можете передать список их имён в DataFrame в Pandas. Вы также можете определить список отдельно, например, для ясности:

target_cols = ["Order_ID", "Product", "Quantity_Ordered"]
df[target_cols]

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

GROUP BY в SQL и Pandas

Теперь мы можем перейти к агрегированию данных. В SQL мы делаем это, передавая столбец в предложения SELECT и GROUP BY, которые мы хотим сгруппировать, а затем также добавляя столбец к совокупному показателю, такому как COUNT, в предложении SELECT. Например, это позволит нам сгруппировать все отдельные строки Order_ID в исходной таблице для каждой Product и подсчитать их количество. Запрос может выглядеть так:

SELECT 
    Product, 
    COUNT(Order_ID)
FROM df
WHERE Order_ID IS NOT NULL
GROUP BY Product

В Pandas это будет выглядеть следующим образом:

df[df["Order_ID"].notna()].groupby(["Product"])["Order_ID"].count()

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

1. Добавьте groupby и передайте столбец (или список столбцов), по которому вы хотите сгруппировать DataFrame;
2. Передайте имя столбца в квадратных скобках в необработанном сгруппированном DataFrame;
3. Вызовите метод count (или любой другой агрегированный), чтобы выполнить агрегирование в DataFrame для целевого столбца.

Для лучшей читабельности мы можем присвоить условие переменной (это пригодится позже) и отформатировать запрос, чтобы его было легче читать.

condition = df["Order_ID"].notna()
grouped_df = (
    df.loc[condition]
    .groupby("Product")
    ["Order_ID"]  # select column to count
    .count()
)
grouped_df

Полный SQL-запрос, переведенный и эффективно написанный в Pandas

Теперь, когда у нас есть большинство компонентов полного SQL-запроса, давайте рассмотрим более сложный запрос и посмотрим, как он будет выглядеть в Pandas.

SELECT 
    Product,
    COUNT(Order_ID)
FROM df
WHERE Order_ID IS NOT NULL
AND Purchase_Address LIKE "%Los Angeles%"
AND Quantity_Ordered == 1
GROUP BY Product
ORDER BY COUNT(Order_ID) DESC

Здесь мы немного добавляем к нашему предыдущему запросу, включив несколько условий фильтрации, а также ORDER BY, чтобы таблица, возвращаемая в нашем запросе, сортировалась по показателю, который нам необходим. Поскольку в этом запросе есть ещё несколько компонентов, давайте шаг за шагом рассмотрим, как мы реализуем это в Pandas.

Во-первых, вместо того, чтобы передавать несколько условий при вызове метода loc, давайте определим список условий и назначим их переменной FILTER_CONDITIONS.

FILTER_CONDITIONS = [
    df["Order_ID"].notna(),
    df["Purchase_Address"].str.contains("Los Angeles"),
    df["Quantity_Ordered"] == "1",
]

Как и прежде, переданное условие loc должно быть маской Pandas, которая оценивается либо как True, либо как False. Можно передать несколько условий в loc, но синтаксис должен выглядеть следующим образом:

df.loc[condition_1 & condition_2 & condition_3]

Однако просто передать список таких условий не получится:

df.loc[FILTER_CONDITIONS]  
# doesn't work -> you can't just pass a list into loc

Вы получите сообщение об ошибке, если попробуете вышеописанное, потому что каждое условие должно быть разделено оператором & для условий «и» (или оператором |, если вам нужны условия «или»). Вместо этого мы можем написать какой-нибудь быстрый код для возврата условий в правильном формате. Мы воспользуемся методом functools.reduce, чтобы сложить условия вместе.

Если вы хотите увидеть, как это выглядит в блокноте, и увидеть, как выглядит объединение некоторых строк с помощью функции reduce, попробуйте ввести следующее:

reduce(lambda x, y: f"{x} & {y}", ["condition_1", "condition_2", "condition_3"])

Этот запрос выводит строку следующим образом:

>>> 'condition_1 & condition_2 & condition_3'

Возвращаясь к нашим фактическим условиям Pandas, мы можем написать следующее (без форматирования строки и просто используя наш определённый список условий в переменной FILTER_CONDITIONS):

reduce(lambda x, y: x & y, FILTER_CONDITIONS)

Что делает reduce, так это применяет функцию кумулятивно к элементам, присутствующим в итерируемой таблице, или в нашем случае запускает лямбда-функцию над элементами в нашем списке FILTER_CONDITIONS, которая объединяет каждый из них с оператором &. Это выполняется до тех пор, пока не останется ни одного условия, или в данном случае для всех трёх условий это будет эффективным возвратом:

df["Order_ID"].notna() & df["Purchase_Address"].str.contains("Los Angeles") & df["Quantity_Ordered"] == "1"

Наконец, давайте добавим список условий для создания окончательной группы по запросу в Pandas:

final_df = (
    df
    .loc[reduce(lambda x, y: x & y, FILTER_CONDITIONS)]
    .groupby("Product")
    .size()
    .sort_values(ascending=False)
)

Вы заметите два дополнительных отличия от предыдущего запроса:

1. Вместо того, чтобы указывать конкретный столбец для подсчёта, мы можем просто вызвать метод size, который вернёт количество строк в DataFrame (как и раньше, когда каждое значение Order_ID было уникальным и предназначалось для представления одной строки, когда мы подсчитывали её);
2. Есть несколько разных способов выполнить ORDER BY в Pandas — один из них — просто вызвать sort_values и перейти к сортировке в порядке убывания ascending=False.

Если вы хотите использовать предыдущий синтаксис для агрегирования данных, это будет выглядеть так:

final_df = (
    df
    .loc[reduce(lambda x, y: x & y, FILTER_CONDITIONS)]
    .groupby("Product")
    ["Order_ID"].count()
    .sort_values(ascending=False)
)

Выходные данные обоих методов будут такими же, как и раньше, то есть сериями с колонками, по которым вы группируете, и подсчётами для каждого продукта.

Если вместо этого вы хотите вывести DataFrame, вы можете вызвать метод reset_index для серии, чтобы вернуть исходные имена столбцов для столбца, по которому вы группировались, и столбца, по которому вы агрегируете (в данном случае мы группировались по “Product” и подсчитывали “Order_ID”.

final_df.reset_index()

И вот оно! Все компоненты полного SQL-запроса, но, наконец, написанные на Pandas. Некоторые из вещей, которые мы можем сделать для оптимизации этого процесса для работы с данными с течением времени, включают:

• Помещение различных списков столбцов в SELECT или GROUP BY для их собственных переменных или функций (чтобы вы или пользователь могли изменять их с течением времени);
• Перемещение логики для объединения списка столбцов для условия фильтра в отдельную функцию, чтобы конечному пользователю не приходилось путаться в том, что reduce делает ;
• После передачи reset_index мы можем переименовать выходной столбец (или столбцы) для ясности, например, в «Count_Order_ID».