Como consultar DataFrames do Pandas com chDB

Pandas é uma biblioteca popular para manipulação e análise de dados em Python. Na versão 2 do chDB, melhoramos o desempenho das consultas em DataFrames do Pandas e introduzimos a função de tabela Python. Neste guia, aprenderemos a consultar o Pandas usando a função de tabela Python.

Configuração

Primeiro, vamos criar um ambiente virtual:

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

Agora vamos instalar o chDB. Certifique-se de ter a versão 2.0.2 ou superior:

pip install "chdb>=2.0.2"

E agora vamos instalar o Pandas e mais algumas bibliotecas:

pip install pandas requests ipython

Vamos usar o ipython para executar os comandos no restante do guia, que pode ser iniciado executando:

ipython

Você também pode usar o código em um script em Python ou no seu notebook favorito.

Criando um DataFrame do Pandas a partir de uma URL

Vamos consultar alguns dados do repositório da StatsBomb no GitHub. Primeiro, vamos importar requests e pandas:

import requests
import pandas as pd

Então, vamos carregar um dos arquivos JSON de partidas em um 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='_')

Vamos ver com quais dados vamos trabalhar:

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

Em seguida, vamos carregar um dos arquivos JSON de eventos e também adicionar uma coluna chamada match_id a esse 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

E, mais uma vez, vamos dar uma olhada na primeira linha:

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

Consultando DataFrames do Pandas

Agora, vamos ver como consultar esses DataFrames usando o chDB. Vamos importar a biblioteca:

import chdb

Podemos consultar DataFrames do Pandas com a função de tabela Python:

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

Então, se quiséssemos listar as colunas de matches_df, poderíamos escrever o seguinte:

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

Poderíamos então descobrir quais árbitros apitaram mais de uma partida escrevendo a seguinte consulta:

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

Agora, vamos explorar 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

Fazendo junção de DataFrames do Pandas

Também podemos fazer junção de DataFrames em uma consulta. Por exemplo, para obter uma visão geral da partida, poderíamos escrever a seguinte consulta:

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

Preenchendo uma tabela a partir de um DataFrame

Também podemos criar e preencher tabelas no ClickHouse a partir de DataFrames. Se quisermos criar uma tabela no chDB, precisamos usar a Stateful Session API. Vamos importar o módulo session:

from chdb import session as chs

Inicialize uma sessão:

sess = chs.Session()

Em seguida, vamos criar um banco de dados:

sess.query("CREATE DATABASE statsbomb")

Em seguida, crie a tabela events com base em events_df:

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

Em seguida, podemos executar a consulta que retorna o jogador que mais recebeu 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

Fazendo a junção entre um DataFrame do Pandas e uma tabela

Por fim, também podemos atualizar nossa consulta de junção para unir o DataFrame matches_df à tabela 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

​Configuração

​Criando um DataFrame do Pandas a partir de uma URL

​Consultando DataFrames do Pandas

​Fazendo junção de DataFrames do Pandas

​Preenchendo uma tabela a partir de um DataFrame

​Fazendo a junção entre um DataFrame do Pandas e uma tabela