View win_expectancy.sql
WITH d AS | |
( | |
SELECT DISTINCT | |
majorleagueid, | |
gamepk, | |
inning, | |
battingteamscore, | |
pitchingteamscore, | |
battingteamscoreendgame, | |
pitchingteamscoreendgame |
View scores_per_inning.sql
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 |
View pitcher_cumulative.sql
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, |
View script.py
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" |
View s_retrosheet_events.sql
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, |
View s_retrosheet_games.sql
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, |
View correrScript.txt
./script.sh añoInicial añoFinal | |
./script.sh 1969 2019 |
View salida.log
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 |
View script.sh
#! /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" |
View obtenerPartidos.py
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 |
NewerOlder