andres-alvarado

WITH d AS 
(
   SELECT DISTINCT
      majorleagueid,
      gamepk,
      inning,
      battingteamscore,
      pitchingteamscore,
      battingteamscoreendgame,
      pitchingteamscoreendgame 

andres-alvarado

WITH data2 AS (
  SELECT
    CAST(SUBSTR(game_id, 4, 4) AS UNSIGNED) season,
    game_id,
    fld_team_id team_id,
    inn_ct inning,
    SUM(IF(bat_dest_id >= 4, 1, 0) + IF(run1_dest_id >= 4, 1, 0) + IF(run2_dest_id >= 4, 1, 0) + IF(run3_dest_id >= 4,1,0)) runs
  FROM s_retrosheet_events
  WHERE
    CAST(SUBSTR(game_id, 4, 4) AS UNSIGNED) = 2019

andres-alvarado

WITH data AS (
  SELECT
    CAST(SUBSTR(game_id, 4, 4) AS UNSIGNED) season,
    CAST(SUBSTR(game_id, 4) AS UNSIGNED) date,
    game_id,
    pit_id,
    SUM(event_outs_ct) AS outs,
    SUM(IF(bat_dest_id >= 4, 1, 0) + IF(run1_dest_id >= 4, 1, 0) + IF(run2_dest_id >= 4, 1, 0) + IF( run3_dest_id >= 4, 1, 0 )) runs,
    SUM(IF(event_cd = 3, 1, 0 )) strikeouts,
    SUM(IF(event_cd = 23, 1, 0 )) homeruns,

andres-alvarado

import pandas as pd
import glob
from sqlalchemy import create_engine as ce, types

def insertToDatabase( p_df, p_tbl ):

    # Idealmente, poner en un archivo .const.
    # Se pone aqui para mas facilidad y hacer menos lineas.
    con = "mysql://usuario:contrasenia@localhost/nombreDeBaseDeDatos?use_unicode=1&charset=utf8"

andres-alvarado

CREATE TABLE `s_retrosheet_events` (
  `GAME_ID` TEXT,
  `AWAY_TEAM_ID` TEXT,
  `INN_CT` BIGINT DEFAULT NULL,
  `BAT_HOME_ID` BIGINT DEFAULT NULL,
  `OUTS_CT` BIGINT DEFAULT NULL,
  `BALLS_CT` BIGINT DEFAULT NULL,
  `STRIKES_CT` BIGINT DEFAULT NULL,
  `PITCH_SEQ_TX` TEXT,
  `AWAY_SCORE_CT` BIGINT DEFAULT NULL,

andres-alvarado

CREATE TABLE `s_retrosheet_games` (
  `GAME_ID` TEXT,
  `GAME_DT` BIGINT DEFAULT NULL,
  `GAME_CT` BIGINT DEFAULT NULL,
  `GAME_DY` TEXT,
  `START_GAME_TM` BIGINT DEFAULT NULL,
  `DH_FL` TEXT,
  `DAYNIGHT_PARK_CD` TEXT,
  `AWAY_TEAM_ID` TEXT,
  `HOME_TEAM_ID` TEXT,

andres-alvarado

./script.sh añoInicial añoFinal

./script.sh 1969 2019

andres-alvarado

Procesando temporada 1969
	Procesando eve
		Descargando archivos
		Descomprimiendo archivos
		Ejecutando Chadwick para Eventos
		Ejecutando Chadwick para Partidos
		Ejecutando Chadwick para Sustituciones
		Limpiando Directorio
	Procesando post
		Descargando archivos

andres-alvarado

#! /bin/bash

# Temporadas
from_season=${1}
to_season=${2}

# Constantes para descargar y procesar archivos de Rtrosheet
retrosheet_url="https://www.retrosheet.org/events/"
season_type_files="eve post"

andres-alvarado

import requests

def partidoEsValido( id_partido ):

    pagina = f"http://www.beisbolcubano.cu/estadisticas/BoxScore?idJuego={id_partido}"
    peticion = requests.get(pagina)

    if peticion.status_code == 200:
        return True