Сравнение pandas с SQL

Многие пользователи pandas имеют некоторое представление о SQL, этот материал рассказывает о том, как различные операции SQL могут быть выполнены при помощи библиотеки pandas.

В большинстве примеров к материалу будет использоваться данные из тестов библиотеки pandas. Прочитаем данные в DataFrame под названием tips и предположим, что имеется таблица базы данных с тем же именем и структурой.

>>> import pandas as pd
>>> import numpy as np

url = (
    "https://raw.githubusercontent.com/pandas-dev"
    "/pandas/main/pandas/tests/io/data/csv/tips.csv"
)

>>> tips = pd.read_csv(url)
>>> tips.head()
#    total_bill   tip     sex smoker  day    time  size
# 0       16.99  1.01  Female     No  Sun  Dinner     2
# 1       10.34  1.66    Male     No  Sun  Dinner     3
# 2       21.01  3.50    Male     No  Sun  Dinner     3
# 3       23.68  3.31    Male     No  Sun  Dinner     2
# 4       24.59  3.61  Female     No  Sun  Dinner     4

Копии данных по сравнению с операциями на месте
Большинство операций pandas возвращают копии Series/DataFrame. Чтобы изменения "вступили в силу", нужно либо присвоить новую переменную или перезаписать исходный Series/DataFrame:
# ! Абстрактный код
# новая переменная
sorted_df = df.sort_values("col1")
# перезапись исходного `DataFrame`
df = df.sort_values("col1")
Некоторые методы имеют аргумент inplace=True или copy=False, например df.replace(5, inplace=True). Активно обсуждается вопрос об удалении inplace и copy для большинства методов, за исключением небольшого подмножества методов.

Содержание:

Сравнение pandas с SQL SELECT;
Сравнение pandas с SQL WHERE;
Сравнение pandas с SQL GROUP BY;
Сравнение pandas с SQL JOIN;
Сравнение pandas с SQL INNER JOIN;
Сравнение pandas с SQL LEFT OUTER JOIN;
Сравнение pandas с SQL RIGHT OUTER JOIN;
Сравнение pandas с SQL FULL OUTER JOIN;
Сравнение pandas с SQL UNION ALL;
Первые n строк со смещением;
Первые n строк в группе;
Сравнение pandas с SQL UPDATE;
Сравнение pandas с SQL DELETE;

Операция SQL `SELECT`

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

SELECT total_bill, tip, smoker, time FROM tips;

В pandas выбор столбцов осуществляется путем передачи списка имен столбцов в DataFrame:

>>> tips[["total_bill", "tip", "smoker", "time"]]
#      total_bill   tip smoker    time
# 0         16.99  1.01     No  Dinner
# 1         10.34  1.66     No  Dinner
# 2         21.01  3.50     No  Dinner
# 3         23.68  3.31     No  Dinner
# 4         24.59  3.61     No  Dinner
# ..          ...   ...    ...     ...
# 239       29.03  5.92     No  Dinner
# 240       27.18  2.00    Yes  Dinner
# 241       22.67  2.00    Yes  Dinner
# 242       17.82  1.75     No  Dinner
# 243       18.78  3.00     No  Dinner
# 
# [244 rows x 4 columns]

Вызов DataFrame без списка имен столбцов отобразит все столбцы (аналогично * в SQL).

В SQL можно добавить вычисляемый столбец:

SELECT *, tip/total_bill as tip_rate FROM tips;

С помощью pandas можно использовать метод DataFrame.assign() для добавления нового столбца:

>>> tips.assign(tip_rate=tips["tip"] / tips["total_bill"])
#      total_bill   tip     sex smoker   day    time  size  tip_rate
# 0         16.99  1.01  Female     No   Sun  Dinner     2  0.059447
# 1         10.34  1.66    Male     No   Sun  Dinner     3  0.160542
# 2         21.01  3.50    Male     No   Sun  Dinner     3  0.166587
# 3         23.68  3.31    Male     No   Sun  Dinner     2  0.139780
# 4         24.59  3.61  Female     No   Sun  Dinner     4  0.146808
# ..          ...   ...     ...    ...   ...     ...   ...       ...
# 239       29.03  5.92    Male     No   Sat  Dinner     3  0.203927
# 240       27.18  2.00  Female    Yes   Sat  Dinner     2  0.073584
# 241       22.67  2.00    Male    Yes   Sat  Dinner     2  0.088222
# 242       17.82  1.75    Male     No   Sat  Dinner     2  0.098204
# 243       18.78  3.00  Female     No  Thur  Dinner     2  0.159744
# 
# [244 rows x 8 columns]

Операция SQL `WHERE`

Фильтрация в SQL осуществляется с помощью предложения WHERE.

SELECT * FROM tips WHERE time = 'Dinner';

DataFrame можно фильтровать несколькими способами; наиболее интуитивным из них является использование "логической индексации".

>>> tips[tips["total_bill"] > 10].head()
#    total_bill   tip     sex smoker  day    time  size
# 0       16.99  1.01  Female     No  Sun  Dinner     2
# 1       10.34  1.66    Male     No  Sun  Dinner     3
# 2       21.01  3.50    Male     No  Sun  Dinner     3
# 3       23.68  3.31    Male     No  Sun  Dinner     2
# 4       24.59  3.61  Female     No  Sun  Dinner     4

Приведенный выше оператор просто передает серию объектов True/False в DataFrame, возвращая все строки с True. Смотрите подробнее, как работает логическая индексация:

Как и в случае с операторами OR и AND в SQL, в DataFrame можно передать несколько условий с помощью | (ИЛИ) и & (И).

Условие AND в SQL:

SELECT * FROM tips WHERE time = 'Dinner' AND tip > 5.00;

Условие AND в pandas:

>>> tips[(tips["time"] == "Dinner") & (tips["tip"] > 5.00)]
#      total_bill    tip     sex smoker  day    time  size
# 23        39.42   7.58    Male     No  Sat  Dinner     4
# 44        30.40   5.60    Male     No  Sun  Dinner     4
# 47        32.40   6.00    Male     No  Sun  Dinner     4
# 52        34.81   5.20  Female     No  Sun  Dinner     4
# 59        48.27   6.73    Male     No  Sat  Dinner     4
# 116       29.93   5.07    Male     No  Sun  Dinner     4
# 155       29.85   5.14  Female     No  Sun  Dinner     5
# 170       50.81  10.00    Male    Yes  Sat  Dinner     3
# 172        7.25   5.15    Male    Yes  Sun  Dinner     2
# 181       23.33   5.65    Male    Yes  Sun  Dinner     2
# 183       23.17   6.50    Male    Yes  Sun  Dinner     4
# 211       25.89   5.16    Male    Yes  Sat  Dinner     4
# 212       48.33   9.00    Male     No  Sat  Dinner     4
# 214       28.17   6.50  Female    Yes  Sat  Dinner     3
# 239       29.03   5.92    Male     No  Sat  Dinner     3

Условие OR в SQL:

SELECT * FROM tips WHERE size >= 5 OR total_bill > 45;

Условие OR в pandas:

>>> tips[(tips["size"] >= 5) | (tips["total_bill"] > 45)]
#      total_bill    tip     sex smoker   day    time  size
# 59        48.27   6.73    Male     No   Sat  Dinner     4
# 125       29.80   4.20  Female     No  Thur   Lunch     6
# 141       34.30   6.70    Male     No  Thur   Lunch     6
# 142       41.19   5.00    Male     No  Thur   Lunch     5
# 143       27.05   5.00  Female     No  Thur   Lunch     6
# 155       29.85   5.14  Female     No   Sun  Dinner     5
# 156       48.17   5.00    Male     No   Sun  Dinner     6
# 170       50.81  10.00    Male    Yes   Sat  Dinner     3
# 182       45.35   3.50    Male    Yes   Sun  Dinner     3
# 185       20.69   5.00    Male     No   Sun  Dinner     5
# 187       30.46   2.00    Male    Yes   Sun  Dinner     5
# 212       48.33   9.00    Male     No   Sat  Dinner     4
# 216       28.15   3.00    Male    Yes   Sat  Dinner     5

Оператор SQL WHERE ... IN ... определяет, соответствует ли значение какому-либо значению в списке или подзапросе. Синтаксис оператора WHERE ... IN ... следующий:

SELECT * FROM tips WHERE 'day'  IN ('Sun', 'Sat') and sex='Female'

Аналог оператора SQL WHERE ... IN ... в pandas - это метод DataFrame.isin(). Следовательно приведенный выше SQL запрос в pandas будет выглядеть следующим образом:

>>> tips[(tips['day'].isin(['Sun', 'Sat'])) & (tips['sex'] == 'Female')]
#      total_bill   tip     sex smoker  day    time  size
# 0         16.99  1.01  Female     No  Sun  Dinner     2
# 4         24.59  3.61  Female     No  Sun  Dinner     4
# 11        35.26  5.00  Female     No  Sun  Dinner     4
# 14        14.83  3.02  Female     No  Sun  Dinner     2
# 16        10.33  1.67  Female     No  Sun  Dinner     3
# ..          ...   ...     ...    ...   ...     ...   ...
# 215       12.90  1.10  Female    Yes  Sat  Dinner     2
# 219       30.14  3.09  Female    Yes  Sat  Dinner     4
# 229       22.12  2.88  Female    Yes  Sat  Dinner     2
# 238       35.83  4.67  Female     No  Sat  Dinner     3
# 240       27.18  2.00  Female    Yes  Sat  Dinner     2

Проверка NULL выполняется с помощью методов .notna() и .isna(). Для демонстрации работы методов создадим новый DataFrame с пропущенными значениями:

frame = pd.DataFrame(
    {"col1": ["A", "B", np.nan, "C", "D"], "col2": ["F", np.nan, "G", "H", "I"]}
)

>>> frame
#   col1 col2
# 0    A    F
# 1    B  NaN
# 2  NaN    G
# 3    C    H
# 4    D    I

Предположим, что есть таблица той же структуры, что и DataFrame по имени frame. В SQL можно отобрать только записи, где col2 имеют значение IS NULL, с помощью следующего запроса:

SELECT * FROM frame WHERE col2 IS NULL;

В pandas это достигается методом DataFrame.isna()

>>> frame[frame["col2"].isna()]
#   col1 col2
# 1    B  NaN

Получение элементов, где col1 НЕ РАВНО NULL, может быть выполнено:

SELECT * FROM frame WHERE col1 IS NOT NULL;

В pandas это достигается методом DataFrame.notna()

>>> frame[frame["col1"].notna()]
#   col1 col2
# 0    A    F
# 1    B  NaN
# 3    C    H
# 4    D    I

Операция SQL `GROUP BY`

В pandas операции SQL GROUP BY выполняются с помощью метода groupby() с аналогичным названием. Групповые операции обычно относится к процессу, в котором необходимо разделить набор данных на группы, применить какую-либо функцию (обычно агрегирование), а затем объединить группы вместе.

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

SELECT sex, count(*) FROM tips GROUP BY sex;
/*
Female     87
Male      157
*/

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

>>> tips.groupby("sex").size()
# sex
# Female     87
# Male      157
# dtype: int64

Обратите внимание, что в коде с pandas используется .size(), а не .count(). Это связано с тем, что метод .count() применяет функцию к каждому столбцу, возвращая количество записей NOT NULL в каждом столбце.

>>> tips.groupby("sex").count()
#         total_bill  tip  smoker  day  time  size
# sex                                             
# Female          87   87      87   87    87    87
# Male           157  157     157  157   157   157

В качестве альтернативы, можно было бы применить метод .count() к отдельному столбцу:

>>> tips.groupby("sex")["total_bill"].count()
# sex
# Female     87
# Male      157
# Name: total_bill, dtype: int64

Кроме того, можно применять несколько агрегатных функций одновременно. Например, предположим, что необходимо увидеть, как сумма tip различается в зависимости от дня недели. Метод DataFrame.agg() позволяет передать в DataFrame словарь с функциями, которые нужно применять к конкретным столбцам.

В SQL:

SELECT day, AVG(tip), COUNT(*) FROM tips GROUP BY day;
/*
Fri   2.734737   19
Sat   2.993103   87
Sun   3.255132   76
Thu  2.771452   62
*/

В pandas:

>>> tips.groupby("day").agg({"tip": "mean", "day": "size"})
#            tip  day
# day                
# Fri   2.734737   19
# Sat   2.993103   87
# Sun   3.255132   76
# Thur  2.771452   62

Группировка по более чем по одному столбцу осуществляется путем передачи списка столбцов в метод DataFrame.groupby().

В SQL:

SELECT smoker, day, COUNT(*), AVG(tip) FROM tips GROUP BY smoker, day;
/*
smoker day
No     Fri      4  2.812500
       Sat     45  3.102889
       Sun     57  3.167895
       Thu    45  2.673778
Yes    Fri     15  2.714000
       Sat     42  2.875476
       Sun     19  3.516842
       Thu    17  3.030000
*/

В pandas:

>>> tips.groupby(["smoker", "day"]).agg({"tip": ["size", "mean"]})
#              tip          
#             size      mean
# smoker day                
# No     Fri     4  2.812500
#        Sat    45  3.102889
#        Sun    57  3.167895
#        Thur   45  2.673778
# Yes    Fri    15  2.714000
#        Sat    42  2.875476
#        Sun    19  3.516842
#        Thur   17  3.030000

Операции SQL `JOIN`

JOIN могут выполняться с помощью методов DataFrame.join() или DataFrame.merge(). По умолчанию DataFrame.join() объединяет DataFrames по их индексам. Каждый метод имеет параметры, позволяющие указать тип выполняемого соединения (ЛЕВОЕ, ПРАВОЕ, ВНУТРЕННЕЕ, ПОЛНОЕ) или столбцы для объединения (имена столбцов или индексы).

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

Предположим, что есть две таблицы в БД с тем же именем и структурой, что и у DataFrame созданных ниже.

>>> df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})
>>> df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})

SQL `INNER JOIN`

SELECT * FROM df1 INNER JOIN df2 ON df1.key = df2.key;

Метод DataFrame.merge() по умолчанию выполняет INNER JOIN.

>>> pd.merge(df1, df2, on="key")
#   key   value_x   value_y
# 0   B  0.813688  1.352346
# 1   D  0.590218 -0.841098
# 2   D  0.590218  1.331496

Метод DataFrame.merge() также предлагает параметры для случаев, когда необходимо соединить один столбец DataFrame с индексом другого DataFrame.

>>> indexed_df2 = df2.set_index("key")
>>> pd.merge(df1, indexed_df2, left_on="key", right_index=True)
#   key   value_x   value_y
# 1   B  0.813688  1.352346
# 3   D  0.590218 -0.841098
# 3   D  0.590218  1.331496

SQL `LEFT OUTER JOIN`

Отображаем все записи из df1.

SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.key = df2.key;

Эквивалент в pandas:

>>> pd.merge(df1, df2, on="key", how="left")
#   key   value_x   value_y
# 0   A -0.179623       NaN
# 1   B  0.813688  1.352346
# 2   C -0.510149       NaN
# 3   D  0.590218 -0.841098
# 4   D  0.590218  1.331496

SQL `RIGHT OUTER JOIN`

Все записи из df2.

SELECT * FROM df1 RIGHT OUTER JOIN df2 ON df1.key = df2.key;

Эквивалент в pandas:

>>> pd.merge(df1, df2, on="key", how="right")
#   key   value_x   value_y
# 0   B  0.813688  1.352346
# 1   D  0.590218 -0.841098
# 2   D  0.590218  1.331496
# 3   E       NaN -1.630031

SQL `FULL OUTER JOIN`

Библиотека pandas также допускает FULL OUTER JOIN, которые показывают оба набора данных, независимо от того, находят ли совпадения объединяемые столбцы.

SELECT * FROM df1 FULL OUTER JOIN df2 ON df1.key = df2.key;

Эквивалент в pandas:

>>> pd.merge(df1, df2, on="key", how="outer")
#   key   value_x   value_y
# 0   A -0.179623       NaN
# 1   B  0.813688  1.352346
# 2   C -0.510149       NaN
# 3   D  0.590218 -0.841098
# 4   D  0.590218  1.331496
# 5   E       NaN -1.630031

SQL `UNION ALL`

SQL операцию UNION ALL можно выполнить с помощью функции pandas.concat().

>>> df1 = pd.DataFrame(
...     {"city": ["Chicago", "San Francisco", "New York City"], "rank": range(1, 4)}
... )
>>> df2 = pd.DataFrame(
...     {"city": ["Chicago", "Boston", "Los Angeles"], "rank": [1, 4, 5]}
... )

Запрос в SQL:

SELECT city, rank FROM df1
UNION ALL
SELECT city, rank FROM df2;
/*
         city  rank
      Chicago     1
San Francisco     2
New York City     3
      Chicago     1
       Boston     4
  Los Angeles     5
*/

Эквивалент в pandas:

>>> pd.concat([df1, df2])
#             city  rank
# 0        Chicago     1
# 1  San Francisco     2
# 2  New York City     3
# 0        Chicago     1
# 1         Boston     4
# 2    Los Angeles     5

UNION в SQL аналогичен UNION ALL, однако UNION удалит повторяющиеся строки.

SELECT city, rank FROM df1
UNION
SELECT city, rank FROM df2;
-- обратите внимание, что в этот раз есть только одна запись `Chicago`
/*
         city  rank
      Chicago     1
San Francisco     2
New York City     3
       Boston     4
  Los Angeles     5
*/

В pandas такого поведения можно добиться использовав метод DataFrame.concat() в сочетании с DataFrame.drop_duplicates().

>>> pd.concat([df1, df2]).drop_duplicates()
#             city  rank
# 0        Chicago     1
# 1  San Francisco     2
# 2  New York City     3
# 1         Boston     4
# 2    Los Angeles     5

Первые n строк со смещением

-- MySQL
SELECT * FROM tips ORDER BY tip DESC LIMIT 10 OFFSET 5;

Эквивалент в pandas:

>>> tips.nlargest(10 + 5, columns="tip").tail(10)
#      total_bill   tip     sex smoker   day    time  size
# 183       23.17  6.50    Male    Yes   Sun  Dinner     4
# 214       28.17  6.50  Female    Yes   Sat  Dinner     3
# 47        32.40  6.00    Male     No   Sun  Dinner     4
# 239       29.03  5.92    Male     No   Sat  Dinner     3
# 88        24.71  5.85    Male     No  Thur   Lunch     2
# 181       23.33  5.65    Male    Yes   Sun  Dinner     2
# 44        30.40  5.60    Male     No   Sun  Dinner     4
# 52        34.81  5.20  Female     No   Sun  Dinner     4
# 85        34.83  5.17  Female     No  Thur   Lunch     4
# 211       25.89  5.16    Male    Yes   Sat  Dinner     4

Первые `n` строк в группе

-- аналитическая функция Oracle `ROW_NUMBER()`
SELECT * FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rn
  FROM tips t
)
WHERE rn < 3
ORDER BY day, rn;

Эквивалент в pandas:

(
    tips.assign(
        rn=tips.sort_values(["total_bill"], ascending=False)
        .groupby(["day"])
        .cumcount()
        + 1
    )
    .query("rn < 3")
    .sort_values(["day", "rn"])
)
#      total_bill    tip     sex smoker   day    time  size  rnk
# 95        40.17   4.73    Male    Yes   Fri  Dinner     4  1.0
# 90        28.97   3.00    Male    Yes   Fri  Dinner     2  2.0
# 170       50.81  10.00    Male    Yes   Sat  Dinner     3  1.0
# 212       48.33   9.00    Male     No   Sat  Dinner     4  2.0
# 156       48.17   5.00    Male     No   Sun  Dinner     6  1.0
# 182       45.35   3.50    Male    Yes   Sun  Dinner     3  2.0
# 197       43.11   5.00  Female    Yes  Thur   Lunch     4  1.0
# 142       41.19   5.00    Male     No  Thur   Lunch     5  2.0

то же самое, используя функцию DataFrame.rank(method='first')

(
    tips.assign(
        rnk=tips.groupby(["day"])["total_bill"].rank(
            method="first", ascending=False
        )
    )
    .query("rnk < 3")
    .sort_values(["day", "rnk"])
)
#      total_bill    tip     sex smoker   day    time  size  rnk
# 95        40.17   4.73    Male    Yes   Fri  Dinner     4  1.0
# 90        28.97   3.00    Male    Yes   Fri  Dinner     2  2.0
# 170       50.81  10.00    Male    Yes   Sat  Dinner     3  1.0
# 212       48.33   9.00    Male     No   Sat  Dinner     4  2.0
# 156       48.17   5.00    Male     No   Sun  Dinner     6  1.0
# 182       45.35   3.50    Male    Yes   Sun  Dinner     3  2.0
# 197       43.11   5.00  Female    Yes  Thur   Lunch     4  1.0
# 142       41.19   5.00    Male     No  Thur   Lunch     5  2.0

Найдем tips с rank < 3 для каждой группыsex при tip < 2.

-- аналитическая функция Oracle `RANK()`
SELECT * FROM (
  SELECT
    t.*,
    RANK() OVER(PARTITION BY sex ORDER BY tip) AS rnk
  FROM tips t
  WHERE tip < 2
)
WHERE rnk < 3
ORDER BY sex, rnk;

Обратите внимание, что при использовании функции DataFrame.rank(method='min') столбец rnk_min остается неизменным для того же самого tip (как функция Oracle RANK())

(
    tips[tips["tip"] < 2]
    .assign(rnk_min=tips.groupby(["sex"])["tip"].rank(method="min"))
    .query("rnk_min < 3")
    .sort_values(["sex", "rnk_min"])
)
#      total_bill   tip     sex smoker  day    time  size  rnk_min
# 67         3.07  1.00  Female    Yes  Sat  Dinner     1      1.0
# 92         5.75  1.00  Female    Yes  Fri  Dinner     2      1.0
# 111        7.25  1.00  Female     No  Sat  Dinner     1      1.0
# 236       12.60  1.00    Male    Yes  Sat  Dinner     2      1.0
# 237       32.83  1.17    Male    Yes  Sat  Dinner     2      2.0

Операция SQL `UPDATE`

UPDATE tips SET tip = tip*2 WHERE tip < 2;

Эквивалент в pandas:

>>> tips.loc[tips["tip"] < 2, "tip"] *= 2
>>> tips.head()
#    total_bill   tip     sex smoker  day    time  size
# 0       16.99  2.02  Female     No  Sun  Dinner     2
# 1       10.34  3.32    Male     No  Sun  Dinner     3
# 2       21.01  3.50    Male     No  Sun  Dinner     3
# 3       23.68  3.31    Male     No  Sun  Dinner     2
# 4       24.59  3.61  Female     No  Sun  Dinner     4

Подробнее о DataFrame.loc[]

Операция SQL `DELETE`

DELETE FROM tips WHERE tip > 9;

Эквивалент в pandas:

# просто пересохраняем `DataFrame`
tips = tips.loc[tips["tip"] <= 9]

Сравнение pandas с SQL

Содержание:

Операция SQL SELECT

Операция SQL WHERE

Операция SQL GROUP BY

Операции SQL JOIN

SQL INNER JOIN

SQL LEFT OUTER JOIN

SQL RIGHT OUTER JOIN

SQL FULL OUTER JOIN

SQL UNION ALL

Первые n строк со смещением

Первые n строк в группе

Операция SQL UPDATE

Операция SQL DELETE

Операция SQL `SELECT`

Операция SQL `WHERE`

Операция SQL `GROUP BY`

Операции SQL `JOIN`

SQL `INNER JOIN`

SQL `LEFT OUTER JOIN`

SQL `RIGHT OUTER JOIN`

SQL `FULL OUTER JOIN`

SQL `UNION ALL`

Первые `n` строк в группе

Операция SQL `UPDATE`

Операция SQL `DELETE`