Перевод статьи «How to rewrite your SQL queries in Pandas, and more»

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

Сейчас данные бывают разных форм и не всегда под ними подразумевают «реляционные базы данных». Это могут быть CSV-файлы, простой текст, Parquet, HDF5 и многое другое. Здесь вам и поможет библиотека Pandas.

Что такое Pandas?

Pandas — это библиотека на языке Python, созданная для анализа и обработки данных. Имеет открытый исходный код и поддерживается разработчиками Anaconda. Эта библиотека хорошо подходит для структурированных (табличных) данных. Дополнительную информацию можно найти в документации. Pandas позволяет формировать запросы к данным и многое другое.

SQL — декларативный язык. Он позволяет объявлять всё таким образом, что запрос похож на обычное предложение в английском языке. Синтаксис Pandas сильно отличается от SQL. Здесь вы применяете операции к набору данных и объединяете их в цепочку для преобразования и изменения.

Разбор SQL-запроса

SQL-запрос состоит из нескольких ключевых слов. Между этими словами добавляются характеристики данных, которые вы хотите видеть. Пример каркаса запросов без конкретики:

• SELECT… FROM… WHERE…
• GROUP BY… HAVING…
• ORDER BY…
• LIMIT… OFFSET…

Есть и другие выражения, но эти самые основные. Чтобы перевести выражения в Pandas, нужно сначала загрузить данные:

import pandas as pd  airports = pd.read_csv('data/airports.csv') airport_freq = pd.read_csv('data/airport-frequencies.csv') runways = pd.read_csv('data/runways.csv')

Скачать эти данные можно здесь.

SELECT, WHERE, DISTINCT, LIMIT

Ниже представлено несколько вариантов выражений с оператором SELECT. Ненужные результаты отсекаются с помощью LIMIT и отфильтровываются с помощью WHERE. Для удаления дублированных результатов используется DISTINCT.

SQL	Pandas
select * from airports	airports
select * from airports limit 3	airports.head(3)
select id from airports where ident = 'KLAX'	airports[airports.ident == 'KLAX'].id
select distinct type from airport	airports.type.unique()

SELECT с множественным условием

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

SQL	Pandas
select * from airports where iso_region = 'US-CA' and type = 'seaplane_base'	airports[(airports.iso_region == 'US-CA') & (airports.type == 'seaplane_base')]
select ident, name, municipality from airports where iso_region = 'US-CA' and type = 'large_airport'	airports[(airports.iso_region == 'US-CA') & (airports.type == 'large_airport')][['ident', 'name', 'municipality']]

ORDER BY

По умолчанию Pandas сортирует данные по возрастанию. Для обратной сортировки используйте выражение ascending=False.

SQL	Pandas
select * from airport_freq where airport_ident = 'KLAX' order by type	airport_freq[airport_freq.airport_ident == 'KLAX'].sort_values('type')
select * from airport_freq where airport_ident = 'KLAX' order by type desc	airport_freq[airport_freq.airport_ident == 'KLAX'].sort_values('type', ascending=False)

IN и NOT IN

Чтобы фильтровать не одно значение, а целые списки, существует условие IN. В Pandas оператор .isin() работает точно так же. Чтобы отменить любое условие, используйте ~ (тильда).

SQL	Pandas
select * from airports where type in ('heliport', 'balloonport')	airports[airports.type.isin(['heliport', 'balloonport'])]
select * from airports where type not in ('heliport', 'balloonport')	airports[~airports.type.isin(['heliport', 'balloonport'])]

GROUP BY, COUNT, ORDER BY

Группировка осуществляется с помощью оператора .groupby(). Есть небольшая разница между семантикой COUNT в SQL и Pandas. В Pandas .count() вернёт значения non-null/NaN. Для получения результата как в SQL, используйте .size().

SQL	Pandas
select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, type	airports.groupby(['iso_country', 'type']).size()
select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, count(*) desc	airports.groupby(['iso_country', 'type']).size().to_frame('size').reset_index().sort_values(['iso_country', 'size'], ascending=[True, False])

Ниже приведена группировка по нескольким полям. По умолчанию Pandas сортирует по одному и тому же списку полей, поэтому в первом примере нет необходимости в .sort_values(). Если нужно использовать разные поля для сортировки или DESC вместо ASC, как во втором примере, выборку необходимо задавать явно:

SQL	Pandas
select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, type	airports.groupby(['iso_country', 'type']).size()
select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, count(*) desc	airports.groupby(['iso_country', 'type']).size().to_frame('size').reset_index().sort_values(['iso_country', 'size'], ascending=[True, False])

Использование .to_frame() и .reset_index() обуславливается сортировкой по конкретному полю (size). Это поле должно быть частью типа DataFrame. После группировки в Pandas в результате получается другой тип, называемый GroupByObject. Поэтому нужно преобразовать его обратно в DataFrame. С помощью .reset_index() перезапускается нумерация строк для фрейма данных.

HAVING

В SQL можно дополнительно фильтровать сгруппированные данные, используя условие HAVING. В Pandas можно использовать .filter() и предоставить функцию Python (или лямбда-выражение), которая будет возвращать True, если группа данных должна быть включена в результат.

SQL	Pandas
select type, count(*) from airports where iso_country = 'US' group by type having count(*) > 1000 order by count(*) desc	airports[airports.iso_country == 'US'].groupby('type').filter(lambda g: len(g) > 1000).groupby('type').size().sort_values(ascending=False)

Первые N записей

Допустим, сделаны некоторые предварительные запросы и теперь имеется фрейм данных с именем by_country, который содержит количество аэропортов в каждой стране:

В следующем примере упорядочим данные по airport_count и выберем только первые 10 стран с наибольшим количеством аэропортов. Второй пример — более сложный случай, в котором выбираются «следующие 10» после первых 10 записей:

SQL	Pandas
select iso_country from by_country order by size desc limit 10	by_country.nlargest(10, columns='airport_count')
select iso_country from by_country order by size desc limit 10 offset 10	by_country.nlargest(20, columns='airport_count').tail(10)

Агрегатные функции: MIN, MAX, MEAN

Учитывая фрейм данных выше (данные взлётно-посадочной полосы), рассчитаем минимальную, максимальную и среднюю длину ВПП.

SQL	Pandas
select max(length_ft), min(length_ft), mean(length_ft), median(length_ft) from runways	runways.agg({'length_ft': ['min', 'max', 'mean', 'median']})

Заметьте, что с SQL-запросом данные представляют собой столбцы. Но в Pandas данные представлены строками.

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

JOIN

Используйте .merge(), чтобы присоединить фреймы данных в Pandas. Необходимо указать, к каким столбцам нужно присоединиться (left_on и right_on), а также тип соединения: inner (по умолчанию), left (соответствует LEFT OUTER в SQL), right (RIGHT OUTER в SQL) или outer (FULL OUTER в SQL).

SQL	Pandas
select airport_ident, type, description, frequency_mhz from airport_freq join airports on airport_freq.airport_ref = airports.id where airports.ident = 'KLAX'	airport_freq.merge(airports[airports.ident == 'KLAX'][['id']], left_on='airport_ref', right_on='id', how='inner')[['airport_ident', 'type', 'description', 'frequency_mhz']]

UNION ALL и UNION

pd.concat() — эквивалент UNION ALL в SQL.

SQL	Pandas
select name, municipality from airports where ident = 'KLAX' union all select name, municipality from airports where ident = 'KLGB'	pd.concat([airports[airports.ident == 'KLAX'][['name', 'municipality']], airports[airports.ident == 'KLGB'][['name', 'municipality']]])

Эквивалентом UNION (дедупликация) является .drop_duplicates().

INSERT

Пока осуществлялась только выборка, но в процессе предварительного анализа данные можно изменить. В Pandas для добавления нужных данных нет эквивалента INSERT в SQL. Вместо этого следует создать новый фрейм данных, содержащий новые записи, а затем объединить два фрейма.

SQL	Pandas
create table heroes (id integer, name text);	df1 = pd.DataFrame({'id': [1, 2], 'name': ['Harry Potter', 'Ron Weasley']})
insert into heroes values (1, 'Harry Potter');	df2 = pd.DataFrame({'id': [3], 'name': ['Hermione Granger']})
insert into heroes values (2, 'Ron Weasley');
insert into heroes values (3, 'Hermione Granger');	pd.concat([df1, df2]).reset_index(drop=True)

UPDATE

Предположим, теперь нужно исправить некоторые неверные данные в исходном фрейме.

SQL	Pandas
update airports set home_link = 'http://www.lawa.org/welcomelax.aspx' where ident == 'KLAX'	airports.loc[airports['ident'] == 'KLAX', 'home_link'] = 'http://www.lawa.org/welcomelax.aspx'

DELETE

Самый простой и удобный способ удалить данные из фрейма в Pandas — это разбить фрейм на строки. Затем получить индексы строк и использовать их в методе .drop().

SQL	Pandas
delete from lax_freq where type = 'MISC'	lax_freq = lax_freq[lax_freq.type != 'MISC']
	lax_freq.drop(lax_freq[lax_freq.type == 'MISC'].index)

Неизменность

По умолчанию большинство операторов в Pandas возвращают новый объект. Некоторые операторы принимают параметр inplace=True, что позволяет работать с исходным фреймом данных вместо нового. Например, вот так можно сбросить индекс на месте:

df.reset_index(drop=True, inplace=True)

Однако оператор .loc (выше в примере с UPDATE) просто находит индексы записей для их обновления, и значения меняются на месте. Также, если все значения в столбце обновлены (df['url'] = 'http://google.com') или добавлен новый столбец (df['total_cost'] = df['price'] * df['quantity']), эти данные изменятся на месте.

Аналитик

Nexign, Санкт-Петербург

tproger.ru Вакансии на tproger.ru

Pandas — это больше, чем просто механизм запросов. С данными можно делать и другие преобразования.

Экспорт во множество форматов

df.to_csv(...)  # в csv-файл df.to_hdf(...)  # в HDF5-файл df.to_pickle(...)  # в сериализованный объект df.to_sql(...)  # в базу данных SQL df.to_excel(...)  # в файл Excel df.to_json(...)  # в строку JSON df.to_html(...)  # отображение в качестве HTML-таблицы df.to_feather(...)  # в двоичный feather-формат df.to_latex(...)  # в табличную среду df.to_stata(...)  # в бинарные файлы данных Stata df.to_msgpack(...)  # msgpack-объект (сериализация) df.to_gbq(...)  # в BigQuery-таблицу (Google) df.to_string(...)  # в консольный вывод df.to_clipboard(...) # в буфер обмена, который может быть вставлен в Excel

Составление графиков

top_10.plot(     x='iso_country',      y='airport_count',     kind='barh',     figsize=(10, 7),     title='Top 10 countries with most airports')

Получим:

Возможность поделиться своими работами

Лучшее место для публикации запросов Pandas, графиков и тому подобного — Jupyter notebook. Некоторые люди (например Джейк Вандерплас) публикуют там даже целые книги. Новую запись в блокноте создать просто:

$ pip install jupyter $ jupyter notebook

После этого вы можете:

• перейти по адресу localhost:8888;
• нажать «New» и дать имя вашему блокноту;
• запросить и отобразить данные;
• создать репозиторий GitHub и добавить туда свой блокнот (файл с расширением .ipynb).

У GitHub есть отличный встроенный просмотрщик для блокнотов Jupyter с оформлением Markdown.

Вам также может быть полезна подборка книг по SQL.

«Use the index, Luke»: подборка книг по SQL и теории баз данныхtproger.ru

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

Перейти к регистрации