Кто кого: 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.

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

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

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

Здесь:

• 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 и др), а также встроенные методы работы с датой и временем.