Cómo consultar DataFrames de Pandas con chDB

Pandas es una biblioteca popular para la manipulación y el análisis de datos en Python. En la versión 2 de chDB, hemos mejorado el rendimiento de las consultas sobre DataFrames de Pandas y hemos incorporado la función de tabla Python. En esta guía, aprenderemos a consultar Pandas con la función de tabla Python.

Configuración

Primero, creemos un entorno virtual:

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

Y ahora instalaremos chDB. Asegúrate de tener la versión 2.0.2 o una superior:

pip install "chdb>=2.0.2"

Y ahora vamos a instalar Pandas y un par de librerías más:

pip install pandas requests ipython

Vamos a usar ipython para ejecutar los comandos del resto de la guía; puedes iniciarlo ejecutando:

ipython

También puedes usar el código en un script de Python o en tu notebook favorito.

Crear un DataFrame de Pandas a partir de una URL

Vamos a consultar algunos datos del repositorio de GitHub de StatsBomb. Primero, importemos requests y pandas:

import requests
import pandas as pd

A continuación, cargaremos uno de los archivos JSON de partidos en 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='_')

Veamos los datos con los que vamos a trabajar:

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

A continuación, cargaremos uno de los archivos JSON de eventos y también añadiremos una columna llamada match_id al 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

Y, de nuevo, echemos un vistazo a la primera fila:

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

Consultar DataFrames de Pandas

A continuación, veamos cómo consultar estos DataFrames con chDB. Importaremos la biblioteca:

import chdb

Podemos consultar DataFrames de Pandas mediante la función de tabla Python:

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

Así, si quisiéramos listar las columnas de matches_df, podríamos escribir lo siguiente:

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

Luego podríamos averiguar qué árbitros han arbitrado más de un partido escribiendo la siguiente 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

Ahora, veamos 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

Unir DataFrames de Pandas

También podemos unir DataFrames en una consulta. Por ejemplo, para obtener una visión general del partido, podríamos escribir la siguiente 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

Rellenar una tabla a partir de un DataFrame

También podemos crear y rellenar tablas de ClickHouse a partir de DataFrames. Si queremos crear una tabla en chDB, debemos usar la API de Stateful Session. Importemos el módulo session:

from chdb import session as chs

Inicializa una sesión:

sess = chs.Session()

A continuación, crearemos una base de datos:

sess.query("CREATE DATABASE statsbomb")

A continuación, crea la tabla events a partir de events_df:

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

A continuación, podemos ejecutar la consulta que devuelve el jugador que más pases recibe:

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

Unir un DataFrame de Pandas con una tabla

Por último, también podemos actualizar nuestra consulta para hacer join entre el DataFrame matches_df y la tabla 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

​Configuración

​Crear un DataFrame de Pandas a partir de una URL

​Consultar DataFrames de Pandas

​Unir DataFrames de Pandas

​Rellenar una tabla a partir de un DataFrame

​Unir un DataFrame de Pandas con una tabla