Кто кого: Pandas VS SQL

Еще порядка 10 лет назад для работы по исследованию данных было достаточно SQL, как инструмента для выборки данных и формирования отчетов по ним. Но время не стоит на месте, и примерно в 2012 году стала стремительно набирать популярность Python-библиотека Pandas. И вот сегодня уже сложно представить работу Data Scientist’а без данного модуля.

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

Итак, SQL (язык структурированных запросов — от англ. Structed Query Language) — это декларативный язык программирования, применяемый для получения и обработки данных с помощью создания запросов внешне похожих по синтаксису на предложения, написанные на английском языке.

Pandas — это модуль для обработки и анализа данных в табличном формате и формате временных рядов на языке Python. Библиотека работает поверх математического модуля более низкого уровня NumPy. Название модуля происходит от эконометрического понятия «панельные данные» (или как его еще называют «лонгитюдные данные» — это данные, которые состоят из повторяющихся наблюдений одних и тех же выбранных единиц, при этом наблюдения производятся в последовательные периоды времени).

Теперь можно приступить к рассмотрению обоих инструментов для работы с данными, при анализе буду сравнивать следующие моменты:

  • синтаксис запросов;
  • время исполнения запросов;
  • сложность понимания/восприятия структуры запроса.

Для анализа рассмотрим один из самых популярных датасетов — описание пассажиров Титаника. Датасет можно скачать с ресурса Kaggle. Общий объем данных по пассажирам представляет менее 1500 строк, но, чтобы мой эксперимент был более наглядным и показательным, все данные я продублировала до 40 000 строк.

­По получившемуся датасету я создала базу данных. Использовалась одна из наиболее популярных систем управления базами данных – MySQL. Также данные были считаны в DataFrame – тип данных библиотеки Pandas, который представляет собой проиндексированный многомерный массив.

Взаимодействовать с данными буду следующими методами:

  • через SQL-консоль СУБД;
  • также подключусь к БД через встроенные инструменты среды разработки и буду отправлять запросы через SQL-консоль;
  • с помощью Pandas через настроенный коннектор и метод Pandas.read_SQL() – данный способ позволяет обращаться к БД с помощью привычных SQL-запросов, результат записывается сразу в DataFrame;
  • с помощью методов библиотеки Pandas для работы с DataFrame.

Итак, приступим.

Выполняем основные импорты:

# импорт для работы с библиотекой Pandas import Pandas as pd # импорт для настройки подключения к БД import pymySQL

Подготовка данных:

# записываем данных в DataFrame titanic_df = pd.read_csv('DATA_PATH') # настраиваем подключение к БД conn = pymySQL.connect(host='HOST',port='PORT',user='USER', passwd='PASSWORD',db='titanic_db')

Здесь:

  • titanic_db – база данных, в которой хранится информация;
  • titanic_data – таблица, из которой мы получаем данные;
  • titanic_df – DataFrame, в котором хранится датасет.

Напомню, что данные в таблице titanic_data и в DataFrame titanic_df – полностью идентичны. Начну с первого самого простого запроса – выведу всю таблицу:

Уже на этом этапе можно увидеть различия в синтаксисе обращений: при вызове DataFrame-объекта можно обойтись без “select” и “from”, без которых невозможно представить ни один SQL-запрос, а это, в свою очередь, позволяет сделать запрос короче. Время исполнения запросов, как видно, отличается, но вопрос идет о миллисекундах, полагаю, что в обычной ситуации этой разницы можно и не заметить, но просто вывод DataFrame все-таки оказывается самым быстрым вариантом.

На предыдущем шаге я выводила все столбцы, теперь попробую вывести только имя, возраст, пол и, например, данные о билете пассажира:

В SQL-запросе легко можно получить необходимые колонки из датасета, просто перечислив их наименования после “select”. С помощью Pandas-запроса одну колонку, например «Name», можно получить вызвав её так: titanic_df[‘Name’]. Но если же необходимо больше одного столбца, то это делается с помощью метода «dataframe.loc[…]» (от англ. location), который дает доступ к группам строк и столбцов по меткам. Запрос получился примерно таким же по длине, но вот для понимания он уже не так прост. С помощью этого запроса, во-первых, можно прописать промежуток строк, которые необходимо вывести, для этого в первой части квадратных скобок стоит знак «:». Можно границы не вписывать, тогда в выводе увидим весь DataFrame. Во-вторых, во второй части квадратных скобок после запятой прописывается список требуемых имен столбцов. Таким образом, можно сказать, что SQL-запрос в данной ситуации оказывается более читабельным, но по скорости Pandas-запрос оказывается быстрее.

Теперь попробую немного усложнить запросы, добавив условия:

В первом случае выведу данные всех пассажиров женского пола (выводить буду колонки, содержащие информацию имени и пола пассажира):

Обратиться к конкретной колонке DataFrame можно несколькими способами:

  • dataframe.Column_name – способ, используемый в примере запроса;
  • dataframe[‘Column_name’].

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

И опять же, как и в предыдущем запросе в Pandas иногда приходится использовать метод «dataframe.loc[…]», из-за чего вновь страдает читабельность кода. На этот раз SQL-запрос выигрывает по скорости примерно в 2 раза относительно Pandas-запроса.

Теперь добавлю еще одно условие для пассажиров женского пола, посмотрим, у кого в поле Tikcet(Билет) стоит значение более 30 000:

С подобными запросами нужно соблюдать определённую осторожность. При считывании данных в DataFrame не напрямую из базы данных, а, например, из csv-файла, часто возникает ошибка несоответствия типов. Это может быть вызвано тем, что где-то неверно по разделителям считался датасет, во время манипуляции с данными произошла замена значений в ячейке или в исходном файле оказалось несколько незаполненных ячеек. Все описанные случаи можно запросто не заметить и словить ошибку. Чтобы узнать тип данных колонки, например, ‘Ticker’, в DataFrame достаточно в консоли прописать titanic_df.Ticket или titanic_df[‘Ticket’] и увидеть следующую информацию: Name: Ticket, Length: 40000, dtype: int64. Тип данных в столбце ‘Ticket’ — int64. Если известно, что во всех ячейках колонки DataFrame хранится просто int и при преобразовании int64 к int значения не обрежутся, то все что нужно, чтобы исправить ошибку, это выполнить преобразование типов с помощью запроса: titanic_df[‘Ticket’].astype(‘int’). Либо int’овое значение «30 000» преобразовать к NumPy-типу следующим образом: numpy.int64(30000). Имея опыт работы с данной библиотекой, Data Scientist будет знать, как исправлять подобного рода ошибки, а вот на начальных этапах это может оказаться большим затруднением в работе.

Долго рассуждая о проблеме типов, чуть не забыла упомянуть еще одно отличие синтаксиса. Для записи сложных условий в SQL используются AND, OR, NOT, соответственный эквивалент им в Pandas будут «&», «|», «!». По скорости выполнения Pandas-запрос оказался быстрее почти в 3 раза, но если вспомнить, что мне еще пришлось решать ошибку, а на начальных этапах на это может уйти как минимум минут 10-15, то полученные 16 миллисекунд уже могут казаться не такими привлекательными.

С условиями стало понятнее, осталось только разобраться с объединением таблиц. Для этого имеющийся датасет я разбила на 2 таблицы. В первой (passengers_table) будет храниться ключ и имя пассажира, а во второй (passengers_data) ключ, пол пассажира и его возраст.

Как можно заметить, чтобы объединить данные в Pandas я воспользовалась методом pd.merge(…) (от англ. объединять, сливать). Метод оказался короче SQL-запроса, да и к тому же довольно приятным для восприятия. Отмечу, что ключевые слова (“left”, “right” и др) указывать необязательно, но поскольку данный метод позволяет указывать ряд других параметров, чтобы просто не запутать себя и вас, в коде они прописаны. Но если честно, с этими параметрами может оказаться, так сказать, палка о двух концах, то есть чем больше параметров придется указать, тем более нагруженным станет код, что вновь приведет к потере читабельности кода. Короткий Pandas-запрос оказался примерно в 2 раза медленнее, чем объединение таблиц в SQL.

И интереса ради можно проверить конкатенацию/объединение таблиц. Для этого разобью таблицу с информацией о пассажирах (passengers_data) на 2 равные таблицы по 20000 строк.

Относительно синтаксиса замечу интересный момент: в Pandas всегда можно получить n подряд идущих строк с помощью записи dataframe[start:end], кроме того можно указывать отрицательные значения. Например, запрос df[:-n] выведет все строки, кроме n последних, а запрос df[-n:] вернет только n последних записей. В SQL для этого существует возможность воспользоваться записью «limit start, end». Чтобы объединить все таблицы в Pandas, просто запишу в лист внутри метода concat, согласитесь, что выглядит довольно просто и понятно. А вот SQL предлагает обращаться к каждой таблице отдельно, да и кроме того проигрывает по времени почти в два раза.

С пассажирами Титаника понятно, но как работают SQL и Pandas с реальной базой данных? Для оценки скорости инструментов использовался датасет клиентов банка размером 100000 строк на 30 столбцов.

Как видно из таблицы Pandas работает с данными быстрее от 5 до 20 раз в зависимости от операции. Но не стоит забывать, что предварительно Pandas’у необходимо время для сохранения датасета в объект типа DataFrame, для чего в нашем случаем для таблицы 100000х30 потребовалось 30,587 секунды, что очень много.

Какие выводы можно сделать? В сравнительной таблице у нас было указано 4 способа взаимодействия с данными, но речь всегда велась только о двух. Я не говорила о запросах через SQL-консоль внутри IDE и SQL-запрос внутри Pandas, по причине того что они практически аналогичны обычному SQL-запросу, но требуют дополнительной подготовки для работы, это, во-первых. А во-вторых, они всегда оказывались на порядок дольше, поскольку данные способы как минимум работают не напрямую с данными, а через коннекторы обращаются к БД. Но, как и говорилось ранее, в данном сравнении речь шла о миллисекундах, велика вероятность, что при работе с подобной, не особо большой БД, разницы по времени выполнения запросов можно и не заметить. Если вы адепт SQL-запросов, но обстоятельства вынуждают работать с Pandas, не унывайте!

К тому же результаты работы методов Pandas’а не могут не радовать! Если нужно много обработки в рамках одних и тех же таблиц, и мы без проблем их можем выгрузить, то эта библиотека незаменима!

А теперь вернусь к основному сравнению. SQL-запросы все-таки зачастую оказывались хоть и более громоздкими, но более понятными для восприятия. Да, чтобы спокойно работать с Pandas, скорее всего, придется потратить много времени на изучение и борьбу с непонятно откуда взявшимися ошибками. Но за это можно получить неплохую скорость, краткость кода, гибкое изменение формата и формы данных, удобное манипулированные индексами и данными, а также мощный инструмент для агрегаций и преобразований. Помимо прочего, Pandas дает возможность работы с разными форматами (xlsx, csv, pickle, sql и др), а также встроенные методы работы с датой и временем.

0
9 комментариев
Написать комментарий...
Alexander
Pandas’у необходимо время для сохранения датасета в объект типа DataFrame

А что там с требуемыми вычислительными ресурсами? Сдаётся мне Pandas потребует колоссальное количество оперативки там где MySQL будет вполне сносно работать на очень скромном железе.

Ответить
Развернуть ветку
NTA
Автор

Если посредством MySQL вы лишь обращаетесь к базам данных, то сам запрос обрабатывается СУБД, и вам предоставляется только готовый результат. Поэтому сравнивать затраты оперативной памяти не совсем корректно. Но, если не брать в расчёт все затраты СУБД на все операции, то вы правы.
Но помимо самого запроса, данные, обёрнутые в Pandas Dataframe. можно использовать в Python, изменить их и ообратиться снова, как к самой обычной таблице, без сохранения её обратно в БД. В этом заключается основное преимущество Pandas над работой через СУБД.

Ответить
Развернуть ветку
Alexander

Разумеется я сравнивал потребление памяти всей системы, включая СУБД. MySQL для построения выборок не требует загрузки всей БД в оперативку, в отличие от Pandas.

Что касается манипуляций с данными - да согласен, в памяти это делать и проще и конечно намного быстрее

Ответить
Развернуть ветку
NTA
Автор

Благодарим за интерес к теме!

Ответить
Развернуть ветку
NTA
Автор

Уточним у автора и вернемся с ответом :)

Ответить
Развернуть ветку
Частное Яйцо

Устал листать Вашу ленту публикаций и читать статьи, завтра продолжу)) Для себя нашёл очень много нужного и полезного, особенно на Питоне (кое что интересное и на моей сакральной Яве есть). Автор, огромное Вам спасибо!

Ответить
Развернуть ветку
NTA
Автор

Нашей команде очень приятны ваши слова :) Будем стараться быть полезными и дальше )

Ответить
Развернуть ветку
Антон Волкоморов

1) Срез по столбцам делается проще: df[['col_name_1', 'col_name_1']]
2) Почему-то забывают про сводные таблицы в панде https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html
3) А с помощью библиотеки matplotlib, данные сразу можно визуализировать в виде графиков.

Ответить
Развернуть ветку
NTA
Автор

1) Вы правы, вызывать несколько колонок таким образом и правда проще. Стоило более подробно остановиться на всех возможностях обращения к элементам Dataframe через loc
2) Тоже хороший комментарий, можно описать возможность реализации агрегированных функций из SQL пандасской pivot_table.
3) В посте не стояло цели описать все способы работы с датафреймами, я скорее пытался показать аналогичные sql-запросам методы pandas. Pandas.DataFrame - очень удобный инструмент для работы с табличными данными, а что делать с ними дальше уже зависит от задачи.
Благодарю вас за комментарий )

Ответить
Развернуть ветку
Читать все 9 комментариев
null