PANDAS VS SQL для работы с данными.

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

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

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

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

https://t.me/machinelearning_interview

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

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

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

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

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

1. через SQL-консоль СУБД;
2. также подключусь к БД через встроенные инструменты среды разработки и буду отправлять запросы через SQL-консоль;
3. с помощью Pandas через настроенный коннектор и метод Pandas.read_SQL() – данный способ позволяет обращаться к БД с помощью привычных SQL-запросов, результат записывается сразу в DataFrame;
4. с помощью методов библиотеки 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')

Здесь:

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

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

#	Способ обращения	Запрос	Время исполнения
1	SQL	SELECT * FROM titanic_data	0.063s
2	SQL консоль (IDE)	SELECT * FROM titanic_data	0.218s
3	Pandas.read_SQL()	pd.read_SQL(«SELECT * FROM titanic_data «,conn)	1.39s
4	DataFrame-методы	titanic_df	менее 1ms

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

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

#	Способ обращения	Запрос	Время исполнения
1	SQL	SELECT Name, Age, Sex, Ticket FROM titanic_db.titanic_data;	0.047s
2	SQL консоль (IDE)	SELECT Name, Age, Sex, Ticket FROM titanic_db.titanic_data;	0.172s
3	Pandas.read_SQL()	pd.read_SQL(«SELECT Name, Age, Sex, Ticket FROM titanic_db.titanic_data;»,conn)	1.06s
4	DataFrame-методы	titanic_df.loc[:, [‘Name’, ‘Sex’, ‘Age’, ‘Ticket’]]	менее 1ms

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

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

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

#	Способ обращения	Запрос	Время исполнения
1	SQL	SELECT Name, Sex FROM titanic_db.titanic_data WHERE Sex = ‘female’	0.015s
2	SQL консоль (IDE)	SELECT Name, Sex FROM titanic_db.titanic_data WHERE Sex = ‘female’	0.203s
3	Pandas.read_SQL()	pd.read_SQL(«SELECT * FROM titanic_data «,conn)	0.344s
4	DataFrame-методы	titanic_df[titanic_df.Sex == ‘female’].loc[:, [‘Name’, ‘Sex’]]	0.031s

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

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

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

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

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

#	Способ обращения	Запрос	Время исполнения
1	SQL	SELECT Name, Ticket, Sex  FROM titanic_db.titanic_data WHERE sex = ‘female’ and Ticket >= 30000;	0.047s
2	SQL консоль (IDE)	SELECT Name, Ticket, Sex  FROM titanic_db.titanic_data WHERE sex = ‘female’ and Ticket >= 30000;	0.141s
3	Pandas.read_SQL()	pd.read_SQL(«SELECT Name, Ticket, Sex  FROM titanic_db.titanic_data «             «where sex = ‘female’ and Ticket >= 30000;»,conn)	0.297s
4	DataFrame-методы	titanic_df[(titanic_df.Ticket >= 30000) & (titanic_df.Sex == ‘female’) ].loc[:, [‘Name’, ‘Sex’, ‘Ticket’]]	0.016s

С подобными запросами нужно соблюдать определённую осторожность. При считывании данных в 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) ключ, пол пассажира и его возраст.

#	Способ обращения	Запрос	Время исполнения
1	SQL	SELECT * FROM titanic_db.passengers_table passenger JOIN titanic_db.passengers_data pas_data ON passenger.ID = pas_data.ID	0.11s
2	SQL консоль (IDE)	SELECT * FROM titanic_db.passengers_table passenger JOIN titanic_db.passengers_data pas_data ON passenger.ID = pas_data.ID	0.219s
3	Pandas.read_SQL()	pd.read_SQL(«SELECT * FROM titanic_db.passengers_table passenger JOIN titanic_db.passengers_data pas_data ON passenger.ID = pas_data.ID”, conn)	1.11s
4	DataFrame-методы	pd.merge(left=people_table, right=people_data, on=’ID’)	0.047s

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

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

#	Способ обращения	Запрос	Время исполнения
1	SQL	SELECT * FROM titanic_db.passengers_data1 UNION all SELECT * FROM titanic_db.passengers_data2	0.031s = 31ms
2	SQL консоль (IDE)	SELECT * FROM titanic_db.passengers_data1 UNION all SELECT * FROM titanic_db.passengers_data2	0.109s = 109ms
3	Pandas.read_SQL()	pd.read_SQL(«select * from titanic_db.passengers_data1 union all select * from titanic_db.passengers_data2»,pd_conn)	1s = 1000ms
4	DataFrame-методы	pd.concat([people_data[0:20000], people_data[20001:]])	0.016s = 16ms

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

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

Операция с таблицей	Способ обращения	Запрос	Время исполнения
Вызов всей таблицы	SQL	SELECT * FROM database.table	2.367s
Вызов всей таблицы	DataFrame-методы	table_df	0.112s
Вызов нескольких столбцов	SQL	SELECT column_1, column_2, column_3, column_4 FROM database.table	0.923s
Вызов нескольких столбцов	DataFrame-методы	table_df.loc[:,[‘column_1’, ‘column_2’, ‘column_3’, ‘column_4’]]	0.019s
Вызов нескольких столбцов с условием	SQL	SELECT column_1, column_2, column_3, column_4 FROM database.table WHERE column_1 = ‘value’	0.717s
Вызов нескольких столбцов с условием	DataFrame-методы	table_df[table_df[‘column_1’] == ‘value’].loc[:, [‘Name’, ‘Sex’]]	0.073s

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

источник