Как использовать SQL в pandas с помощью запросов pandasql

Python SQL

SQL, или Структурированный язык запросов, является языком программирования, используемым для доступа, извлечения, манипуляции и исследования данных, хранящихся в реляционных базах данных. pandas — это открытая Python- библиотека, специально разработанная для манипуляции и анализа данных.

В этом учебнике мы будем讨论 когда и как мы можем (и когда мы не можем) использовать SQL-функциональность в рамках pandas. Кроме того, мы посмотрим на различные примеры внедрения этого подхода и сравним результаты с эквивалентным кодом в чистом pandas.

为什么在pandas中使用SQL？

根据引言中的定义，为什么还要在pandas这个包含所有数据分析功能的包中使用SQL呢？

答案是，在某些情况下，特别是对于复杂的程序，SQL-запросы выглядят гораздо более простыми и читабельными, чем соответствующий код в pandas. Это особенно верно для тех людей, которые сначала использовали SQL для работы с данными, а потом выучили pandas.

Если вам нужнее обучение по pandas, вы можете посмотреть на наш курс Data Manipulation with pandas и Pandas Tutorial: DataFrames in Python.

Чтобы увидеть, как работает читаемость SQL, предположим, что у нас есть таблица (датафрейм) с именем penguins, содержащая различную информацию о пингвинах (и мы будем работать с такой таблицей позднее в этом уроке). Чтобы извлечь все уникальные виды пингвинов, которые являются мужчинами и у которых ласты длиннее 210 мм, нам потребовался бы следующий код в pandas:

penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique()

Вместо этого, чтобы получить ту же информацию, используя SQL, мы бы выполнили следующий код:

SELECT DISTINCT species FROM penguins WHERE sex = 'Male' AND flipper_length_mm > 210

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

SELECT DISTINCT species
  FROM penguins 
 WHERE sex = 'Male' 
   AND flipper_length_mm > 210

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

Как использовать pandasql

Python-библиотека pandasql позволяет запрашивать датафреймы pandas, выполняя команды SQL, не подключаясь к какому-либо серверу SQL. Под капотом она использует синтаксис SQLite, автоматически распознает любой датафрейм pandas и обрабатывает его как обычную таблицу SQL.

Настройка вашего окружения

В первую очередь, нам нужно установить pandasql:

pip install pandasql

Затем мы импортируем необходимые пакеты:

from pandasql import sqldf
import pandas as pd

Выше мы прямо импортировали функцию sqldf из pandasql, которая, по сути, является практически единственной значимой функцией библиотеки. Как имя указывает, она используется для запроса данных с использованием синтаксиса SQL. помимо этой функции в pandasql также присутствуют два простого встроенных набора данных, которые можно загрузить с помощью понятных функций load_births() и load_meat().

Синтаксис pandasql

Синтаксис функции sqldf очень прост:

sqldf(query, env=None)

В этом query является обязательным параметром, который принимает SQL запрос в виде строки, а env – необязательный (и редко используемый) параметр, который может быть либо locals() или globals() и позволяет sqldf доступ к соответствующему набору переменных в вашем Python с environment.

Функция sqldf возвращает результат запроса в виде dataframe pandas.

Когда мы можем использовать pandasql

Библиотека pandasql позволяет работать с данными, используя Data Query Language (DQL), который является одним из подмножеств SQL. Другими словами, с помощью pandasql мы можем выполнять запросы на данных, хранящихся в базе данных, чтобы извлечь необходимую информацию. В частности, мы можем получить, извлечь, фильтровать, сортировать, группировать, объединять, агрегировать данные и выполнять математические или логические операции с ними.

Когда мы не можем использовать pandasql

pandasql не позволяет использовать другие подмножества SQL кроме DQL. Это意味着 мы не можем использовать pandasql для изменения (обновления, очистки, добавления и т. д.) таблиц или изменения (обновления, удаления или добавления) данных в таблице.

В добавление к этому, поскольку эта библиотека основана на синтаксисе SQL, мы должны быть осторожны с известными неладnostями в SQLite.

Примеры использования pandasql

Теперь мы подробнее посмотрим, как выполнять SQL-запросы на данных pandas dataframe с помощью функции sqldf из pandasql. Чтобы иметь какие-то данные для тренировки, давайте загрузим один из встроенных наборов данных библиотеки seaborn — penguins:

import seaborn as sns
penguins = sns.load_dataset('penguins')
print(penguins.head())

Вывод:

   species island     bill_length_mm  bill_depth_mm  flipper_length_mm  \
0  Adelie  Torgersen            39.1           18.7              181.0   
1  Adelie  Torgersen            39.5           17.4              186.0   
2  Adelie  Torgersen            40.3           18.0              195.0   
3  Adelie  Torgersen             NaN            NaN                NaN   
4  Adelie  Torgersen            36.7           19.3              193.0   

   body_mass_g     sex  
0       3750.0    Male  
1       3800.0  Female  
2       3250.0  Female  
3          NaN     NaN  
4       3450.0  Female

Если вам нужно обновить ваши знания SQL, наша требовательность основам SQL является хорошей отправной точкой.

Извлечение данных с помощью pandasql

print(sqldf('''SELECT species, island 
FROM penguins 
LIMIT 5'''))

Вывод:

  species  island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

Выше мы извлекли информацию о видах и географии первых пяти пингвинов из dataframe penguins. Обратите внимание, что выполнение функции sqldf возвращает pandas dataframe:

print(type(sqldf('''SELECT species, island 
                      FROM penguins 
                     LIMIT 5''')))

Вывод:

<class 'pandas.core.frame.DataFrame'>

Чисто с помощью pandas, это было бы:

print(penguins[['species', 'island']].head())

Вывод:

   species island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

Другой пример — извлечение уникальных значений из столбца:

print(sqldf('''SELECT DISTINCT species 
                 FROM penguins'''))

Вывод:

     species
0     Adelie
1  Chinstrap
2     Gentoo

С помощью pandas, это было бы:

print(penguins['species'].unique())

Вывод:

['Adelie' 'Chinstrap' 'Gentoo']

Сортировка данных с помощью pandasql

print(sqldf('''SELECT body_mass_g 
                 FROM penguins 
                ORDER BY body_mass_g DESC 
                LIMIT 5'''))

Вывод:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0

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

В pandas, это было бы:

print(penguins['body_mass_g'].sort_values(ascending=False, 
ignore_index=True).head())

Вывод:

0    6300.0
1    6050.0
2    6000.0
3    6000.0
4    5950.0
Name: body_mass_g, dtype: float64

Фильтрация данных с помощью pandasql

Давайте попробуем тот же пример, который мы упомянули в главе “Зачем использовать SQL в pandas”: извлечение уникальных видов пингвинов, которые являются мужчинами и у которых ласты длиннее 210 мм:

print(sqldf('''SELECT DISTINCT species
                 FROM penguins 
                WHERE sex = 'Male' 
                  AND flipper_length_mm > 210'''))

Вывод:

     species
0  Chinstrap
1     Gentoo

Выше мы отфильтровали данные на основе двух условий: sex = 'Male' и flipper_length_mm > 210.

Тот же код в pandas будет выглядеть немного более громоздко:

print(penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique())

Вывод:

['Chinstrap' 'Gentoo']

Группировка и агрегация данных с помощью pandasql

Теперь давайте применим группировку данных и агрегацию, чтобы найти самый длинный клюв для каждого вида в dataframe:

print(sqldf('''SELECT species, MAX(bill_length_mm)
                 FROM penguins 
                GROUP BY species'''))

Вывод:

     species  MAX(bill_length_mm)
0     Adelie                 46.0
1  Chinstrap                 58.0
2     Gentoo                 59.6

Тот же код в pandas:

print(penguins[['species', 'bill_length_mm']].groupby('species', as_index=False).max())

Вывод:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6

Выполнение математических операций с помощью pandasql

С помощью pandasql мы можем легко выполнять математические или логические операции над данными. Давайте представим, что мы хотим вычислить соотношение длины клюва к глубине для каждого пингвина и вывести первые пять значений этой меры:

print(sqldf('''SELECT bill_length_mm / bill_depth_mm AS length_to_depth
                 FROM penguins
                ORDER BY length_to_depth DESC
                LIMIT 5'''))

Вывод:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599

Обратите внимание, что на этот раз мы использовали псевдоним length_to_depth для столбца с значениями соотношения. В противном случае, мы бы получили столбец с чудовищным именем bill_length_mm / bill_depth_mm.

В pandas нам нужно было бы сначала создать новый столбец с значениями соотношения:

penguins['length_to_depth'] = penguins['bill_length_mm'] / penguins['bill_depth_mm']
print(penguins['length_to_depth'].sort_values(ascending=False, ignore_index=True).head())

Вывод:

0    3.612676
1    3.510490
2    3.505882
3    3.492424
4    3.458599
Name: length_to_depth, dtype: float64

Заключение

Закрываясь, в этой статье мы исследовали, почему и когда мы можем комбинировать функциональность SQL с pandas, чтобы написать лучшее, более эффективное код. Мы обсудили, как установить и использовать библиотеку pandasql для этой цели, а также какие ограничения у этого пакета. Наконец, мы рассмотрели множество популярных примеров практического использования pandasql и, в каждом случае, сравнили код с его аналогом в pandas.

Теперь у тебя есть все, что нужно для применения SQL с pandas в реальных проектах. отличное место для твоей практики является DataLab DataCamp, AI-enabled data notebook с отличной поддержкой SQL.

Source:
https://www.datacamp.com/tutorial/how-to-use-sql-in-pandas-using-pandasql-queries