Comment interroger des DataFrames Pandas avec chDB

Pandas est une bibliothèque Python populaire pour la manipulation et l’analyse de données. Dans la version 2 de chDB, nous avons amélioré les performances des requêtes sur les DataFrames Pandas et introduit la fonction de table Python. Dans ce guide, nous allons voir comment interroger Pandas à l’aide de la fonction de table Python.

Préparation

Commençons par créer un environnement virtuel :

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

Et maintenant, nous allons installer chDB. Assurez-vous d’avoir la version 2.0.2 ou supérieure :

pip install "chdb>=2.0.2"

Et maintenant, nous allons installer Pandas ainsi que quelques autres bibliothèques :

pip install pandas requests ipython

Nous allons utiliser ipython pour exécuter les commandes dans la suite de ce guide. Vous pouvez le lancer en exécutant :

ipython

Vous pouvez également utiliser le code dans un script Python ou dans le notebook de votre choix.

Création d’un Pandas DataFrame à partir d’une URL

Nous allons récupérer des données depuis le dépôt GitHub de StatsBomb. Importons d’abord requests et pandas :

import requests
import pandas as pd

Ensuite, nous chargerons l’un des fichiers JSON de matchs dans un DataFrame :

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

Voyons avec quelles données nous allons travailler :

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

Ensuite, nous allons charger l’un des fichiers JSON d’événements et ajouter également une colonne nommée match_id à ce DataFrame :

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

Et de nouveau, examinons la première ligne :

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

Interroger des DataFrames Pandas

Voyons maintenant comment interroger ces DataFrames avec chDB. Importons la bibliothèque :

import chdb

Nous pouvons interroger des DataFrames Pandas en utilisant la fonction de table Python :

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

Ainsi, si nous voulions afficher les colonnes de matches_df, nous pourrions écrire ce qui suit :

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

Nous pouvons alors déterminer quels arbitres ont officié lors de plus d’un match en écrivant la requête suivante :

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

Examinons maintenant 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

Jointure de DataFrames Pandas

Nous pouvons également effectuer une jointure entre des DataFrames dans une requête. Par exemple, pour obtenir un aperçu du match, nous pourrions écrire la requête suivante :

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

Remplir une table à partir d’un DataFrame

Nous pouvons également créer et remplir des tables ClickHouse à partir de DataFrames. Si nous voulons créer une table dans chDB, nous devons utiliser l’API Stateful Session. Importons le module de session :

from chdb import session as chs

Initialisez une session :

sess = chs.Session()

Ensuite, nous allons créer une base de données :

sess.query("CREATE DATABASE statsbomb")

Ensuite, créez une table events à partir de events_df :

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

Nous pouvons ensuite exécuter la requête qui renvoie le joueur qui reçoit le plus de passes :

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

Jointure entre un DataFrame Pandas et une table

Enfin, nous pouvons également mettre à jour notre requête de jointure pour effectuer la jointure entre le DataFrame matches_df et la table 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

​Préparation

​Création d’un Pandas DataFrame à partir d’une URL

​Interroger des DataFrames Pandas

​Jointure de DataFrames Pandas

​Remplir une table à partir d’un DataFrame

​Jointure entre un DataFrame Pandas et une table