Как выполнять запросы к DataFrame в Pandas с помощью chDB

Pandas — популярная библиотека Python для обработки и анализа данных. В chDB 2 мы повысили производительность выполнения запросов к DataFrame в Pandas и добавили табличную функцию Python. В этом руководстве мы рассмотрим, как выполнять запросы к Pandas с помощью табличной функции Python.

Подготовка

Сначала создадим виртуальное окружение:

python -m venv .venv
source .venv/bin/activate

А теперь установим chDB. Убедитесь, что у вас установлена версия 2.0.2 или выше:

pip install "chdb>=2.0.2"

Теперь установим Pandas и ещё пару библиотек:

pip install pandas requests ipython

Для запуска команд в оставшейся части руководства мы будем использовать ipython, который можно запустить так:

ipython

Вы также можете использовать этот код в Python-скрипте или в любимом ноутбуке.

Создание DataFrame в Pandas из URL

Мы выполним запрос к данным из репозитория StatsBomb на GitHub. Для начала импортируем requests и pandas:

import requests
import pandas as pd

Затем загрузим в DataFrame один из JSON‑файлов matches:

response = requests.get(
  "https://raw.githubusercontent.com/statsbomb/open-data/master/data/matches/223/282.json"
)
matches_df = pd.json_normalize(response.json(), sep='_')

Давайте посмотрим, с какими данными мы будем работать:

matches_df.iloc[0]

match_id                                                                  3943077
match_date                                                             2024-07-15
kick_off                                                             04:15:00.000
home_score                                                                      1
away_score                                                                      0
match_status                                                            available
match_status_360                                                      unscheduled
last_updated                                           2024-07-15T15:50:08.671355
last_updated_360                                                             None
match_week                                                                      6
competition_competition_id                                                    223
competition_country_name                                            South America
competition_competition_name                                         Copa America
season_season_id                                                              282
season_season_name                                                           2024
home_team_home_team_id                                                        779
home_team_home_team_name                                                Argentina
home_team_home_team_gender                                                   male
home_team_home_team_group                                                    None
home_team_country_id                                                           11
home_team_country_name                                                  Argentina
home_team_managers              [{'id': 5677, 'name': 'Lionel Sebastián Scalon...
away_team_away_team_id                                                        769
away_team_away_team_name                                                 Colombia
away_team_away_team_gender                                                   male
away_team_away_team_group                                                    None
away_team_country_id                                                           49
away_team_country_name                                                   Colombia
away_team_managers              [{'id': 5905, 'name': 'Néstor Gabriel Lorenzo'...
metadata_data_version                                                       1.1.0
metadata_shot_fidelity_version                                                  2
metadata_xy_fidelity_version                                                    2
competition_stage_id                                                           26
competition_stage_name                                                      Final
stadium_id                                                                   5337
stadium_name                                                    Hard Rock Stadium
stadium_country_id                                                            241
stadium_country_name                                     United States of America
referee_id                                                                   2638
referee_name                                                        Raphael Claus
referee_country_id                                                             31
referee_country_name                                                       Brazil
Name: 0, dtype: object

Далее мы загрузим один из JSON‑файлов с событиями, а также добавим в этот DataFrame столбец match_id:

response = requests.get(
  "https://raw.githubusercontent.com/statsbomb/open-data/master/data/events/3943077.json"
)
events_df = pd.json_normalize(response.json(), sep='_')
events_df["match_id"] = 3943077

И снова посмотрим на первую строку:

with pd.option_context("display.max_rows", None):
    first_row = events_df.iloc[0]
    non_nan_columns = first_row[first_row.notna()].T
    display(non_nan_columns)

id                                   279b7d66-92b5-4daa-8ff6-cba8fce271d9
index                                                                   1
period                                                                  1
timestamp                                                    00:00:00.000
minute                                                                  0
second                                                                  0
possession                                                              1
duration                                                              0.0
type_id                                                                35
type_name                                                     Starting XI
possession_team_id                                                    779
possession_team_name                                            Argentina
play_pattern_id                                                         1
play_pattern_name                                            Regular Play
team_id                                                               779
team_name                                                       Argentina
tactics_formation                                                   442.0
tactics_lineup          [{'player': {'id': 6909, 'name': 'Damián Emili...
match_id                                                          3943077
Name: 0, dtype: object

Выполнение запросов к DataFrame в Pandas

Далее посмотрим, как выполнять запросы к этим DataFrame с помощью chDB. Для этого импортируем библиотеку:

import chdb

Мы можем выполнять запросы к DataFrame в Pandas с помощью табличной функции Python:

SELECT *
FROM Python(<name-of-variable>)

Итак, если мы хотим вывести список столбцов в matches_df, можно написать следующее:

chdb.query("""
DESCRIBE Python(matches_df)
SETTINGS describe_compact_output=1
""", "DataFrame")

                              name    type
                       match_id   Int64
                     match_date  String
                       kick_off  String
                     home_score   Int64
                     away_score   Int64
                   match_status  String
               match_status_360  String
                   last_updated  String
               last_updated_360  String
                     match_week   Int64
    competition_competition_id   Int64
      competition_country_name  String
  competition_competition_name  String
              season_season_id   Int64
            season_season_name  String
        home_team_home_team_id   Int64
      home_team_home_team_name  String
    home_team_home_team_gender  String
     home_team_home_team_group  String
          home_team_country_id   Int64
        home_team_country_name  String
            home_team_managers  String
        away_team_away_team_id   Int64
      away_team_away_team_name  String
    away_team_away_team_gender  String
     away_team_away_team_group  String
          away_team_country_id   Int64
        away_team_country_name  String
            away_team_managers  String
         metadata_data_version  String
metadata_shot_fidelity_version  String
  metadata_xy_fidelity_version  String
          competition_stage_id   Int64
        competition_stage_name  String
                    stadium_id   Int64
                  stadium_name  String
            stadium_country_id   Int64
          stadium_country_name  String
                    referee_id   Int64
                  referee_name  String
            referee_country_id   Int64
          referee_country_name  String

Затем мы можем узнать, какие судьи судили более одного матча, написав следующий запрос:

chdb.query("""
SELECT referee_name, count() AS count
FROM Python(matches_df)
GROUP BY ALL
HAVING count > 1
ORDER BY count DESC
""", "DataFrame")

                    referee_name  count
César Arturo Ramos Palazuelos      3
             Maurizio Mariani      3
             Piero Maza Gomez      3
   Mario Alberto Escobar Toca      2
Wilmar Alexander Roldán Pérez      2
        Jesús Valenzuela Sáez      2
       Wilton Pereira Sampaio      2
                Darío Herrera      2
               Andrés Matonte      2
                Raphael Claus      2

Теперь давайте изучим events_df.

chdb.query("""
SELECT pass_recipient_name, count()
FROM Python(events_df)
WHERE type_name = 'Pass' AND pass_recipient_name <> ''
GROUP BY ALL
ORDER BY count() DESC
LIMIT 10
""", "DataFrame")

               pass_recipient_name  count()
          Davinson Sánchez Mina       76
Ángel Fabián Di María Hernández       64
            Alexis Mac Allister       62
                 Enzo Fernandez       57
    James David Rodríguez Rubio       56
    Johan Andrés Mojica Palacio       55
         Rodrigo Javier De Paul       54
   Jefferson Andrés Lerma Solís       53
      Jhon Adolfo Arias Andrade       52
Carlos Eccehomo Cuesta Figueroa       50

Объединение DataFrame в Pandas

Мы также можем объединять DataFrames в запросе. Например, чтобы получить общее представление о матче, можно написать следующий запрос:

chdb.query("""
SELECT home_team_home_team_name, away_team_away_team_name, home_score, away_score,
       countIf(type_name = 'Pass' AND possession_team_id=home_team_home_team_id) AS home_passes,
       countIf(type_name = 'Pass' AND possession_team_id=away_team_away_team_id) AS away_passes,
       countIf(type_name = 'Shot' AND possession_team_id=home_team_home_team_id) AS home_shots,
       countIf(type_name = 'Shot' AND possession_team_id=away_team_away_team_id) AS away_shots
FROM Python(matches_df) AS matches
JOIN Python(events_df) AS events ON events.match_id = matches.match_id
GROUP BY ALL
LIMIT 5
""", "DataFrame").iloc[0]

home_team_home_team_name    Argentina
away_team_away_team_name     Colombia
home_score                          1
away_score                          0
home_passes                       527
away_passes                       669
home_shots                         11
away_shots                         19
Name: 0, dtype: object

Заполнение таблицы из DataFrame

Мы также можем создавать и заполнять таблицы ClickHouse из DataFrame. Если мы хотим создать таблицу в chDB, нужно использовать API сеансов с сохранением состояния. Давайте импортируем модуль session:

from chdb import session as chs

Инициализируйте сеанс:

sess = chs.Session()

Далее создадим базу данных:

sess.query("CREATE DATABASE statsbomb")

Затем создайте таблицу events на основе events_df:

sess.query("""
CREATE TABLE statsbomb.events ORDER BY id AS
SELECT * 
FROM Python(events_df)
""")

Затем можно выполнить запрос, который вернёт игрока, получившего больше всего пасов:

sess.query("""
SELECT pass_recipient_name, count()
FROM statsbomb.events
WHERE type_name = 'Pass' AND pass_recipient_name <> ''
GROUP BY ALL
ORDER BY count() DESC
LIMIT 10
""", "DataFrame")

               pass_recipient_name  count()
          Davinson Sánchez Mina       76
Ángel Fabián Di María Hernández       64
            Alexis Mac Allister       62
                 Enzo Fernandez       57
    James David Rodríguez Rubio       56
    Johan Andrés Mojica Palacio       55
         Rodrigo Javier De Paul       54
   Jefferson Andrés Lerma Solís       53
      Jhon Adolfo Arias Andrade       52
Carlos Eccehomo Cuesta Figueroa       50

Объединение DataFrame в Pandas и таблицы

Наконец, мы также можем изменить наш запрос с JOIN, чтобы объединить DataFrame matches_df с таблицей statsbomb.events:

sess.query("""
SELECT home_team_home_team_name, away_team_away_team_name, home_score, away_score,
       countIf(type_name = 'Pass' AND possession_team_id=home_team_home_team_id) AS home_passes,
       countIf(type_name = 'Pass' AND possession_team_id=away_team_away_team_id) AS away_passes,
       countIf(type_name = 'Shot' AND possession_team_id=home_team_home_team_id) AS home_shots,
       countIf(type_name = 'Shot' AND possession_team_id=away_team_away_team_id) AS away_shots
FROM Python(matches_df) AS matches
JOIN statsbomb.events AS events ON events.match_id = matches.match_id
GROUP BY ALL
LIMIT 5
""", "DataFrame").iloc[0]

home_team_home_team_name    Argentina
away_team_away_team_name     Colombia
home_score                          1
away_score                          0
home_passes                       527
away_passes                       669
home_shots                         11
away_shots                         19
Name: 0, dtype: object

​Подготовка

​Создание DataFrame в Pandas из URL

​Выполнение запросов к DataFrame в Pandas

​Объединение DataFrame в Pandas

​Заполнение таблицы из DataFrame

​Объединение DataFrame в Pandas и таблицы