Рубрики
Без рубрики

Как переписать SQL-запросы в Панд и многое другое

Введение в Pandas для разработчиков, уже знакомых с SQL.

Автор оригинала: Irina Truong.

Пятнадцать лет назад было всего несколько навыков, которые разработчик программного обеспечения должен был бы хорошо знать, и у него или у нее был бы приличный шанс на 95% перечисленных должностей. Эти навыки были:

  • Объектно-ориентированное программирование.
  • Языки сценариев.
  • JavaScript, и…
  • SQL.

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

В наши дни данные приходят во многих формах и формах, и это больше не синоним “реляционной базы данных”. Вы можете получить CSV-файлы, обычный текст, паркет, HDF5 и кто знает, что еще. Вот где Панды библиотека сияет.

Что такое панды?

Библиотека анализа данных Python, называемая Pandas , представляет собой библиотеку Python, созданную для анализа и обработки данных. Он с открытым исходным кодом и поддерживается Anaconda. Он особенно хорошо подходит для структурированных (табличных) данных. Для получения дополнительной информации см. http://pandas.pydata.org/pandas-docs/stable/index.html .

Что я могу с этим сделать?

Все запросы, которые вы ранее отправляли к данным в SQL, и многое другое!

Отлично! С чего мне начать?

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

SQL-это декларативный язык программирования : https://en.wikipedia.org/wiki/List_of_programming_languages_by_type#Declarative_languages .

С помощью SQL вы объявляете то, что хотите, в предложении, которое почти читается как английский.

Синтаксис Pandas сильно отличается от SQL. В Pandas вы применяете операции к набору данных и связываете их в цепочку , чтобы преобразовать и изменить данные так, как вы хотите.

Нам понадобится разговорник!

Анатомия 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')

Я получил эти данные по адресу http://ourairports.com/data/ .

ВЫБЕРИТЕ, ГДЕ, DISTINCT, LIMIT

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

выберите * из аэропортов аэропорты
выберите * из пределов аэропортов 3 аэропорты.руководитель(3)
выберите id из аэропортов, где аэропорты[airports.ident].id
выберите отдельный тип из аэропорта аэропорты.тип.уникальный()

ВЫБЕРИТЕ с несколькими условиями

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

выберите * из аэропортов, где и аэропорты[(airports.iso_region) & (airports.type)]
выберите идентификатор, название, муниципалитет из аэропортов, где и аэропорты[(airports.iso_region) & (airports.type)][[‘ident’, ‘name’, ‘municipality’]]

ЗАКАЗ ПО

По умолчанию Панды будут сортировать вещи в порядке возрастания. Чтобы обратить это вспять, предоставьте.

выберите * из airport_freq, где заказ по типу airport_freq[airport_free.airport_ident].sort_values(‘тип’)
выберите * из airport_freq, где заказ по типу desc airport_freq[airport_free.airport_ident].sort_values(‘тип’,)

В… НЕ В

Мы знаем, как фильтровать значение, но как насчет списка значений — В состоянии? В панд, .is в() оператор работает таким же образом. Чтобы отрицать любое условие, используйте ~ .

выберите * из аэропортов, где введите (“вертодром”, “воздушный шар”) аэропорты[airports.type.isin([‘вертодром’, ‘воздушный шар’])]
выберите * из аэропортов, в которых тип не указан (“вертодром”, “воздушный шар”).) аэропорты[~airports.type.isin([‘вертодром’, ‘воздушный шар’])]

ГРУППИРОВАТЬ ПО, СЧИТАТЬ, УПОРЯДОЧИВАТЬ ПО

Группировка проста: используйте оператор .groupby () . Существует тонкая разница между семантикой COUNT в SQL и Pandas. В Pandas .count() вернет количество ненулевых/NaN значений. Чтобы получить тот же результат , что и SQL COUNT , используйте .size() .

выберите iso_country, type, count(*) из группы аэропортов по iso_country, введите order по iso_country, введите аэропорты.группа по([‘iso_country’, ‘type’]).размер()
выберите iso_country, type, count(*) из группы аэропортов по iso_country, введите order по iso_country, count(*) desc аэропорты.группировать по ([‘iso_country’, ‘type’]).size().to_frame(‘size’).reset_index().sort_values ([‘iso_country’, ‘size’], по возрастанию=[True, False])

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

выберите iso_country, type, count(*) из группы аэропортов по iso_country, введите order по iso_country, введите аэропорты.группа по([‘iso_country’, ‘type’]).размер()
выберите iso_country, type, count(*) из группы аэропортов по iso_country, введите order по iso_country, count(*) desc аэропорты.группировать по ([‘iso_country’, ‘type’]).size().to_frame(‘size’).reset_index().sort_values ([‘iso_country’, ‘size’], по возрастанию=[True, False])

Что это за хитрость с .to_frame() и . reset_index() ? Поскольку мы хотим отсортировать по вашему вычисляемому полю ( размер ), это поле должно стать частью фрейма данных . После группировки в Панд мы получаем другой тип, называемый Group By Object . Поэтому нам нужно преобразовать его обратно в Фрейм данных . С помощью .reset_index () мы перезапускаем нумерацию строк для нашего фрейма данных.

ИМЕЮЩИЙ

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

выберите тип, количество(*) из аэропортов, где группа по типу имеет количество(*) > 1000 заказ по количеству(*) desc аэропорты[airports.iso_country].groupby(‘type’).filter(lambda g: len(g) >)

Лучшие записи N

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

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

выберите iso_country из by_country order by size desc limit 10 by_country.самый большой(10,)
выберите iso_country из by_country order by size desc limit 10 смещение 10 by_country.самый большой(20,).хвост(10)

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

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

Вычислите минимальную, максимальную, среднюю и среднюю длину взлетно-посадочной полосы:

выберите max(length_ft), min(length_ft), mean(length_ft), median(length_ft) из взлетно-посадочных полос взлетно-посадочные полосы.agg({‘length_ft’: [‘min’, ‘max’, ‘mean’, ‘median’]})

Вы заметите, что в этом SQL-запросе каждая статистика представляет собой столбец. Но с этой агрегацией Панд каждая статистика представляет собой строку:

Не о чем беспокоиться —просто перенесите фрейм данных с помощью .T для получения столбцов:

ПРИСОЕДИНИТЬСЯ

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

выберите airport_ident, тип, описание, frequency_mhz из airport_freq присоединиться к аэропортам on.id где airport_freq.merge(аэропорты[аэропорты.ident][[‘id’]],,,)[[‘airport_ident’, ‘type’, ‘description’, ‘frequency_mhz’]]

СОЮЗ ВСЕ и СОЮЗ

Используйте pd.concat() для ОБЪЕДИНЕНИЯ ВСЕХ двух фреймов данных:

выберите имя, муниципалитет из аэропортов, в которых объединяются все выберите имя, муниципалитет из аэропортов, в которых pd.concat([аэропорты[аэропорты.ident][[‘имя’, ‘муниципалитет’]], аэропорты[аэропорты.ident][[‘имя’, ‘муниципалитет’]]])

Чтобы дедупликировать вещи (эквивалент UNION ), вам также нужно добавить .drop_duplicates() .

ВСТАВЛЯТЬ

До сих пор мы выбирали вещи, но вам, возможно, придется также изменить их в процессе вашего исследовательского анализа. Что делать, если вы хотите добавить некоторые недостающие записи?

В Панд нет такой вещи, как ВСТАВКА . Вместо этого вы создадите новый фрейм данных, содержащий новые записи, а затем объедините их:

создание героев таблицы (целое число идентификатора, текст имени);
вставить в значения героев (1, “Гарри Поттер”);
вставить в значения героев (2, “Рон Уизли”);
вставить в значения героев (3, “Гермиона Грейнджер”); pd.concat([df1,)

ОБНОВЛЕНИЕ

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

обновление аэропортов, установленных там, где ident airports.loc[аэропорты[‘ident’],

УДАЛИТЬ

Самый простой (и наиболее читаемый) способ “удалить” вещи из фрейма данных Pandas-это подмножество фрейма данных в строки, которые вы хотите сохранить. Кроме того, вы можете получить индексы строк для удаления и .drop() строки, используя эти индексы:

удалить из lax_freq, где
lax_freq.drop(lax_freq[max_freq.type].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']

все это произойдет на месте.

И даже больше!

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

  • Экспорт во множество форматов:
df.to_csv(...)  # csv file
df.to_hdf(...)  # HDF5 file
df.to_pickle(...)  # serialized object
df.to_sql(...)  # to SQL database
df.to_excel(...)  # to Excel sheet
df.to_json(...)  # to JSON string
df.to_html(...)  # render as HTML table
df.to_feather(...)  # binary feather-format
df.to_latex(...)  # tabular environment table
df.to_stata(...)  # Stata binary data files
df.to_msgpack(...)	# msgpack (serialize) object
df.to_gbq(...)  # to a Google BigQuery table.
df.to_string(...)  # console-friendly tabular output.
df.to_clipboard(...) # clipboard that can be pasted into Excel
  • Заговор:
top_10.plot(
    x='iso_country', 
    y='airport_count',
    kind='barh',
    figsize=(10, 7),
    title='Top 10 countries with most airports')

чтобы увидеть действительно хорошие графики!

  • Поделитесь им.

Лучшим средством для обмена результатами запросов панд, графиками и тому подобными вещами являются записные книжки Jupyter ( http://jupyter.org/ ). На самом деле, некоторые люди (например, Джейк Вандерплас, который потрясающий) публикуют целые книги в блокнотах Jupyter: https://github.com/jakevdp/PythonDataScienceHandbook .

Это так просто создать новый ноутбук:

$ pip install jupyter
$ jupyter notebook

После этого:

  • перейдите на локальный хост:8888
  • нажмите “Создать” и дайте вашей записной книжке имя
  • запрос и отображение данных
  • создайте репозиторий GitHub и добавьте свой ноутбук (файл с расширением .ipynb ).

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

А теперь начинается ваше путешествие с пандами!

Я надеюсь, что теперь вы убеждены, что библиотека Pandas может служить вам, а также вашему старому другу SQL для целей исследовательского анализа данных — а в некоторых случаях даже лучше. Пришло время получить в свои руки некоторые данные для запроса!

Переиздано с https://codeburst.io/how-to-rewrite-your-sql-queries-in-pandas-and-more-149d341fc53e .

Читайте ещё по теме: