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

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

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

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

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

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

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

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

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

SELECT * FROM df

А вот Pandas:

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

Теперь мы можем перейти к агрегированию данных. В 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, где мы включили фильтр, теперь мы делаем три вещи:

Добавьте groupby и передайте столбец (или список столбцов), по которому вы хотите сгруппировать DataFrame;
Передайте имя столбца в квадратных скобках в необработанном сгруппированном DataFrame;
Вызовите метод 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.

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) )

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

Вместо того, чтобы указывать конкретный столбец для подсчёта, мы можем просто вызвать метод size, который вернёт количество строк в DataFrame (как и раньше, когда каждое значение Order_ID было уникальным и предназначалось для представления одной строки, когда мы подсчитывали её);
Есть несколько разных способов выполнить 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».

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

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

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

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

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

GROUP BY в SQL и Pandas

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