berkeley44
commented
4 years ago I am having this problem also. Looks like MLB has changed several column headings. Any chance the script can be edited?
Open anosillus opened 4 years ago
berkeley44
commented
4 years ago I am having this problem also. Looks like MLB has changed several column headings. Any chance the script can be edited?
berkeley44
commented
4 years ago events1029_df <- get_payload(start = "2019-09-30", end = "2019-10-29") Gathering Gameday data, please be patient... Starting download, this may take a while... Error:
tfs_zulu= NULL must be a column name or position, not NULL Runrlang::last_error()to see where the error occurred. In addition: There were 21 warnings (use warnings() to see them)
keberwein
commented
4 years ago Well gentlemen, looks like the MLB finally pulled the plug on this API. It's been depricated for the last several years since they're moving to Statcast. When I tried to trouble-shoot this bug, I was just getting 404 errors all around. See link.
http://gd2.mlb.com/components/game/mlb
I will probably pull this package from CRAN shortly. In the meantime, I would suggest using Bill Petti's baseballr package. It's the closest we're going to get and it pulls from Statcast.
JosephAlanLane
commented
4 years ago @keberwein I've noticed links to exact .xml still work though?
For example, the link below still gets data from gd2: http://gd2.mlb.com/components/game/mlb/year_2017/month_04/day_02/gid_2017_04_02_nyamlb_tbamlb_1/inning/inning_all.xml
Does this mean you're leaving the entire project behind? :(
keberwein
commented
4 years ago The old stuff is still available, but if you look at the root directory, there is no data after 2018. The MLB said that they were planning on depricating this API a couple years ago in favor of the Statcast API. I'm thinking that has finally happened. The only option (for this package) would require a complete re-write, so I haven't really decided what to do yet. In the meantime, the baseballr package uses Statcast, so you might be able to make that one work for you.
i3roly
commented
2 years ago fwiw i've managed to use a variant of what is here, from 2011 and the "baseball hacks" contribution by o' reilly, to get most of the data.
from what i can see, the only big omission in the new data is the absence of the "on_Xb" attribute for each pitch, which i'm investigating. it seems now this data is in its own tag in the inning_X.xml file:
GagansMacPro:chapter 3 Gagan$ cat games/year_2020/month_07/day_23/gid_2020_07_23_nyamlb_wasmlb_1/inning/inning_1.xml
<?xml version="1.0" ?>
<!--NOTICE: This file is no longer actively supported. Please use the MLB Stats API (http://statsapi.mlb.com/docs/) as an alternative. Copyright 2021 MLB Advanced Media, L.P. Use of any content on this page acknowledges agreement to the terms posted here http://gdx.mlb.com/components/copyright.txt-->
<inning next="Y" home_team="was" away_team="nya" num="1">
<top>
<action away_team_runs="0" home_team_runs="0" event_es="Game Advisory" event="Game Advisory" event_num="1" des_es="Status Change - Pre-Game" des="Status Change - Pre-Game" o="0" s="0" b="0" tfs_zulu="2020-07-23T19:54:20.804Z" tfs="195420" pitch="1" player="543305"/>
<action away_team_runs="0" home_team_runs="0" event_es="Game Advisory" event="Game Advisory" event_num="2" des_es="Status Change - Warmup" des="Status Change - Warmup" o="0" s="0" b="0" tfs_zulu="2020-07-23T22:48:57.946Z" tfs="224857" pitch="1" player="543305"/>
<action away_team_runs="0" home_team_runs="0" event_es="Game Advisory" event="Game Advisory" event_num="3" des_es="Status Change - In Progress" des="Status Change - In Progress" o="0" s="0" b="0" tfs_zulu="2020-07-23T23:08:02.734Z" tfs="230802" pitch="1" player="543305"/>
<atbat num="1" away_team_runs="0" home_team_runs="0" play_guid="19114bf8-28ee-4ec8-afa9-7e28ecc5caf5" event_es="Groundout" event="Groundout" event_num="4" des_es="Aaron Hicks grounds out sharply, second baseman Starlin Castro to first baseman Eric Thames." des="Aaron Hicks grounds out sharply, second baseman Starlin Castro to first baseman Eric Thames." p_throws="R" pitcher="453286" b_height="6' 1"" stand="L" batter="543305" end_tfs_zulu="2020-07-23T23:10:42.914Z" start_tfs_zulu="2020-07-23T19:54:20.804Z" start_tfs="195420" o="1" s="2" b="2">
<pitch id="5" event_num="5" des_es="Status Change - In Progress" des="Ball" mt="" cc="" play_guid="ff48cd18-1048-4dab-9b4f-570c491659d9" spin_rate="placeholder" spin_dir="placeholder" nasty="" zone="placeholder" type_confidence="placeholder" pitch_type="FF" break_length="3.6" break_angle="34.8" break_y="24.0" az="-15.69" ay="28.24" ax="-12.7" vz0="-3.51" vy0="-138.72" vx0="7.5" z0="5.37" y0="50.0" x0="-3.05" pz="3.06" px="-1.16" pfx_z="8.38" pfx_x="-6.45" end_speed="87.8" start_speed="95.3" sv_id="" y="156.25" x="161.39" tfs_zulu="2020-07-23T23:09:06.854Z" sz_bot="1.84" sz_top="3.85" tfs="230906" code="*B" type="B"/>
<pitch id="6" event_num="6" des_es="Ball" des="Ball" mt="" cc="" play_guid="925c540a-c277-4b85-b1a5-15e532c0b2a9" spin_rate="placeholder" spin_dir="placeholder" nasty="" zone="placeholder" type_confidence="placeholder" pitch_type="FF" break_length="3.6" break_angle="37.2" break_y="24.0" az="-12.48" ay="30.15" ax="-11.96" vz0="-4.48" vy0="-139.77" vx0="7.6" z0="5.19" y0="50.01" x0="-3.13" pz="2.75" px="-1.17" pfx_z="9.91" pfx_x="-6.02" end_speed="88.1" start_speed="96.1" sv_id="" y="164.4" x="161.51" tfs_zulu="2020-07-23T23:09:32.381Z" sz_bot="1.75" sz_top="3.58" tfs="230932" code="*B" type="B"/>
<pitch id="7" event_num="7" des_es="Ball" des="Called Strike" mt="" cc="" play_guid="251f0161-8f16-49a9-be55-6318501806c1" spin_rate="placeholder" spin_dir="placeholder" nasty="" zone="placeholder" type_confidence="placeholder" pitch_type="FF" break_length="3.6" break_angle="33.6" break_y="24.0" az="-14.1" ay="30.88" ax="-12.8" vz0="-6.0" vy0="-140.29" vx0="11.55" z0="5.15" y0="50.0" x0="-2.85" pz="2.07" px="0.48" pfx_z="9.04" pfx_x="-6.4" end_speed="88.5" start_speed="96.7" sv_id="" y="182.79" x="98.62" tfs_zulu="2020-07-23T23:09:50.086Z" sz_bot="1.65" sz_top="3.33" tfs="230950" code="C" type="C"/>
<pitch id="8" event_num="8" des_es="Called Strike" des="Called Strike" mt="" cc="" play_guid="450eb718-7158-44be-a20d-b8990d21b7a5" spin_rate="placeholder" spin_dir="placeholder" nasty="" zone="placeholder" type_confidence="placeholder" pitch_type="CU" break_length="12.0" break_angle="12.0" break_y="24.0" az="-39.87" ay="22.13" ax="7.68" vz0="0.32" vy0="-116.06" vx0="4.69" z0="5.52" y0="50.0" x0="-3.19" pz="1.85" px="-0.41" pfx_z="-5.65" pfx_x="5.64" end_speed="73.7" start_speed="79.7" sv_id="" y="188.77" x="132.7" tfs_zulu="2020-07-23T23:10:08.910Z" sz_bot="1.65" sz_top="3.51" tfs="231008" code="C" type="C"/>
<pitch id="9" event_num="9" des_es="Called Strike" des="In play, out(s)" mt="" cc="" play_guid="19114bf8-28ee-4ec8-afa9-7e28ecc5caf5" spin_rate="placeholder" spin_dir="placeholder" nasty="" zone="placeholder" type_confidence="placeholder" pitch_type="FF" break_length="3.6" break_angle="32.4" break_y="24.0" az="-14.96" ay="30.13" ax="-12.02" vz0="-4.47" vy0="-139.63" vx0="9.9" z0="5.01" y0="50.0" x0="-2.99" pz="2.41" px="-0.19" pfx_z="8.68" pfx_x="-6.06" end_speed="88.1" start_speed="96.1" sv_id="" y="173.83" x="124.35" tfs_zulu="2020-07-23T23:10:31.342Z" sz_bot="1.56" sz_top="3.41" tfs="231031" code="X" type="X"/>
<runner id="543305" event="Groundout" event_num="10" end="" start=""/>**
</atbat>
<atbat num="2" away_team_runs="0" home_team_runs="0" play_guid="d0f42282-73d6-40a7-befa-81f51d7e924e" event_es="Single" event="Single" event_num="11" des_es="Aaron Judge singles on a sharp ground ball to left fielder Andrew Stevenson." des="Aaron Judge singles on a sharp ground ball to left fielder Andrew Stevenson." p_throws="R" pitcher="453286" b_height="6' 7"" stand="R" batter="592450" end_tfs_zulu="2020-07-23T23:11:56.305Z" start_tfs_zulu="2020-07-23T23:11:07.945Z" start_tfs="231107" o="1" s="1" b="1">
<pitch id="12" event_num="12" des_es="In play, no out" des="Ball" mt="" cc="" play_guid="c4cf4e14-ef60-46c1-901a-4fc95bab1f37" spin_rate="placeholder" spin_dir="placeholder" nasty="" zone="placeholder" type_confidence="placeholder" pitch_type="FF" break_length="3.6" break_angle="39.6" break_y="24.0" az="-12.02" ay="28.64" ax="-13.22" vz0="-8.43" vy0="-140.2" vx0="9.85" z0="5.31" y0="50.01" x0="-2.86" pz="1.5" px="-0.17" pfx_z="10.02" pfx_x="-6.57" end_speed="89.1" start_speed="96.6" sv_id="" y="198.3" x="123.61" tfs_zulu="2020-07-23T23:11:09.648Z" sz_bot="1.99" sz_top="3.99" tfs="231109" code="*B" type="B"/>
<pitch id="13" event_num="13" des_es="Ball" des="Called Strike" mt="" cc="" play_guid="21e4f501-7831-4ffa-8fde-8549ae762b86" spin_rate="placeholder" spin_dir="placeholder" nasty="" zone="placeholder" type_confidence="placeholder" pitch_type="SL" break_length="8.4" break_angle="3.6" break_y="24.0" az="-33.16" ay="21.98" ax="1.19" vz0="-0.96" vy0="-126.31" vx0="7.04" z0="5.36" y0="50.0" x0="-3.23" pz="2.34" px="-0.33" pfx_z="-0.6" pfx_x="0.73" end_speed="80.9" start_speed="86.7" sv_id="" y="175.55" x="129.58" tfs_zulu="2020-07-23T23:11:26.277Z" sz_bot="2.02" sz_top="4.07" tfs="231126" code="C" type="C"/>
<pitch id="14" event_num="14" des_es="Called Strike" des="In play, no out" mt="" cc="" play_guid="d0f42282-73d6-40a7-befa-81f51d7e924e" spin_rate="placeholder" spin_dir="placeholder" nasty="" zone="placeholder" type_confidence="placeholder" pitch_type="FF" break_length="3.6" break_angle="40.8" break_y="24.0" az="-13.25" ay="30.74" ax="-14.23" vz0="-4.99" vy0="-140.6" vx0="9.14" z0="5.29" y0="50.0" x0="-2.97" pz="2.64" px="-0.61" pfx_z="9.42" pfx_x="-7.08" end_speed="88.6" start_speed="96.7" sv_id="" y="167.47" x="140.08" tfs_zulu="2020-07-23T23:11:44.314Z" sz_bot="1.75" sz_top="3.76" tfs="231144" code="D" type="D"/>
<runner id="592450" event="Single" event_num="15" end="1B" start=""/>
</atbat>
so what i am thinking (just spitballing here) is that the on_Xb attribute will need to be updated while the data is being loaded into the database, since the pitch data is entered sequentially anyways. this will require a bit of thinking, hopefully not too much, that will evaluate the existing tuple and modifying its on_Xb attribute accordingly. and, of course, this kind of approach would have to persist over multiple at-bats.
in short, creating and maintaining a 'baserunner state diagram' is necessary when the pitches are being entered.
i haven't looked at your code closely, but i can't imagine a parallel approach being favourable to what i'm suggesting. i personally prefer my pitch_id/game_id/atbat_id being sequential and tidy. (btw i just recently did manage to parallelise the "spider" (data grabbing), which is nice for sure.)
i am not sure if the new 'statcast api' really offers anything more than what was there before, to be honest. even the trajectory data provided in earlier years is still in the newer XML files.
the new statcast api is not favourable to the approach being taken in this github, but in my opinion the only reason MLB made this change is to try and monetise their data while preventing outside enthusiasts from building their trove.
i3roly
commented
2 years ago newmlb=# select * from games where date>'1976-01-01' limit 10;
game_id | date | home | away | game | umpire | wind | wind_dir | temp | type | runs_home | runs_away | local_time | venue | att
---------+---------------------+------+------+------+--------+------+-----------+------+------+-----------+-----------+---------------------+--------------------------+-------
7401 | 1976-04-08 00:00:00 | cin | hou | 1 | | 0 | | 72 | 1 | 11 | 5 | 1976-04-08 03:33:00 | Riverfront Stadium | 52949
7402 | 1976-04-08 00:00:00 | ml4 | nya | 1 | | 0 | | 44 | 1 | 5 | 0 | 1976-04-08 03:33:00 | Milwaukee County Stadium | 44868
7403 | 1976-04-09 00:00:00 | sdn | atl | 1 | | 0 | | 44 | 1 | 8 | 2 | 1976-04-10 02:05:00 | San Diego Stadium | 44728
7404 | 1976-04-09 00:00:00 | bal | bos | 1 | | 0 | | 44 | 1 | 1 | 0 | 1976-04-09 03:33:00 | Memorial Stadium | 46425
7405 | 1976-04-09 00:00:00 | sln | chn | 1 | | 0 | | 45 | 1 | 5 | 0 | 1976-04-10 00:05:00 | Busch Stadium | 30761
7406 | 1976-04-09 00:00:00 | cha | kca | 1 | | 8 | Out To LF | 45 | 1 | 4 | 0 | 1976-04-10 00:05:00 | Comiskey Park | 40318
7407 | 1976-04-09 00:00:00 | sfn | lan | 1 | | 0 | | 45 | 1 | 4 | 2 | 1976-04-09 03:33:00 | Candlestick Park | 37261
7408 | 1976-04-09 00:00:00 | tex | min | 1 | | 0 | | 45 | 1 | 2 | 1 | 1976-04-10 00:05:00 | Arlington Stadium | 28947
7409 | 1976-04-09 00:00:00 | cal | oak | 1 | | 0 | | 45 | 1 | 2 | 5 | 1976-04-10 02:05:00 | Anaheim Stadium | 30194
7410 | 1976-04-09 00:00:00 | nyn | mon | 1 | | 0 | | 45 | 1 | 3 | 2 | 1976-04-09 03:33:00 | Shea Stadium | 17013
(10 rows)newmlb=# select * from atbats where game_id=7401;
ab_id | game_id | inning | num | ball | strike | outs | batter | pitcher | stand | des | event | hit_x | hit_y | launch_speed | launch_angle | total_distance | hit_traj | hit_hardness | hit_location | hit_type
--------+---------+--------+-----+------+--------+------+--------+---------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------+-------+--------------+--------------+----------------+---------------+--------------+--------------+----------
568372 | 7401 | 6 | 50 | 0 | 0 | 0 | 114723 | 122543 | L | Cesar Geronimo doubles (1) on a to left fielder Wilbur Howard. | Double | | | | | | | | | H
568326 | 7401 | 1 | 4 | 0 | 0 | 1 | 115136 | 121145 | L | Ken Griffey grounds into a force out, second baseman Larry Milbourne to shortstop Roger Metzger. Pete Rose out at 2nd. Ken Griffey to 1st. | Forceout | | | | | | ground_ball | medium | 4 | O
568327 | 7401 | 1 | 5 | 0 | 0 | 2 | 119371 | 121145 | L | Joe Morgan reaches on a fielder's choice out, third baseman Enos Cabell unassisted. Ken Griffey out at home. | Fielders Choice Out | | | | | | fly_ball | medium | 5 | O
568328 | 7401 | 1 | 6 | 0 | 0 | 3 | 110849 | 121145 | R | Johnny Bench out to center fielder Cesar Cedeno. | Batter Out | | | | | | | | | O
568329 | 7401 | 2 | 7 | 0 | 3 | 1 | 123988 | 119822 | R | Bob Watson strikes out swinging. | Strikeout | | | | | | | | | O
568330 | 7401 | 2 | 8 | 0 | 3 | 2 | 116573 | 119822 | R | Cliff Johnson strikes out swinging. | Strikeout | | | | | | | | | O
568331 | 7401 | 2 | 9 | 0 | 0 | 3 | 115188 | 119822 | L | Greg Gross out, second baseman Joe Morgan to first baseman Tony Perez. | Batter Out | | | | | | | | | O
568332 | 7401 | 2 | 10 | 4 | 0 | 0 | 120404 | 121145 | R | Tony Perez walks. | Walk | | | | | | | | |
568333 | 7401 | 2 | 11 | 0 | 0 | 1 | 114333 | 121145 | R | George Foster out to center fielder Cesar Cedeno. | Batter Out | | | | | | | | | O
568334 | 7401 | 2 | 12 | 0 | 0 | 1 | 112549 | 121145 | R | Dave Concepcion singles on a to left fielder Wilbur Howard. Tony Perez scores. Dave Concepcion to 2nd. | Single | | | | | | | | | H
568335 | 7401 | 2 | 13 | 0 | 0 | 2 | 114723 | 121145 | L | Cesar Geronimo out, second baseman Larry Milbourne to first baseman Bob Watson. Dave Concepcion to 3rd. | Batter Out | | | | | | | | | O
568336 | 7401 | 2 | 14 | 0 | 0 | 2 | 119822 | 121145 | R | Gary Nolan singles on a to right fielder Greg Gross. Dave Concepcion scores. Gary Nolan to 2nd. Gary Nolan advances to 2nd, on a fielding error by right fielder Greg Gross. | Single | | | | | | | | | H
568337 | 7401 | 2 | 15 | 0 | 0 | 2 | 121454 | 121145 | L | Pete Rose triples (1). Gary Nolan scores. | Triple | | | | | | | | | H
568338 | 7401 | 2 | 16 | 0 | 0 | 3 | 115136 | 121145 | L | Ken Griffey out to left fielder Wilbur Howard. | Batter Out | | | | | | | | | O
568339 | 7401 | 3 | 17 | 0 | 0 | 0 | 119047 | 119822 | L | Larry Milbourne doubles (1). | Double | | | | | | | | | H
568340 | 7401 | 3 | 18 | 0 | 0 | 1 | 119001 | 119822 | L | Roger Metzger pops out to shortstop Dave Concepcion. | Pop Out | | | | | | popup | medium | 6 | O
568341 | 7401 | 3 | 19 | 0 | 0 | 2 | 121145 | 119822 | R | J.R. Richard pops out to first baseman Tony Perez. | Pop Out | | | | | | popup | medium | 3 | O
568342 | 7401 | 3 | 20 | 0 | 0 | 3 | 116216 | 119822 | L | Wilbur Howard lines out to first baseman Tony Perez. | Lineout | | | | | | line_drive | medium | 3 | O
568343 | 7401 | 3 | 21 | 0 | 0 | 0 | 119371 | 121145 | L | Joe Morgan singles on a to center fielder Cesar Cedeno. | Single | | | | | | | | | H
568344 | 7401 | 3 | 22 | 0 | 0 | 1 | 110849 | 121145 | R | Johnny Bench grounds out to first baseman Bob Watson. | Groundout | | | | | | ground_ball | medium | 3 | O
568345 | 7401 | 3 | 23 | 0 | 0 | 1 | 120404 | 121145 | R | Tony Perez reaches on a fielder's choice. Joe Morgan scores. | Fielders Choice | | | | | | fly_ball | medium | | H
568346 | 7401 | 3 | 24 | 0 | 0 | 2 | 114333 | 121145 | R | George Foster flies out to catcher Cliff Johnson. | Flyout | | | | | | fly_ball | medium | 2 | O
568347 | 7401 | 3 | 25 | 0 | 0 | 3 | 112549 | 121145 | R | Dave Concepcion grounds into a force out, shortstop Roger Metzger to second baseman Larry Milbourne. Tony Perez out at 2nd. Dave Concepcion to 1st. | Forceout | | | | | | ground_ball | medium | 6 | O
568348 | 7401 | 4 | 26 | 0 | 3 | 1 | 111847 | 119822 | R | Enos Cabell strikes out swinging. | Strikeout | | | | | | | | | O
568349 | 7401 | 4 | 27 | 0 | 0 | 2 | 112153 | 119822 | R | Cesar Cedeno out to right fielder Ken Griffey. | Batter Out | | | | | | | | | O
568350 | 7401 | 4 | 28 | 0 | 0 | 3 | 123988 | 119822 | R | Bob Watson out, third baseman Pete Rose to first baseman Tony Perez. | Batter Out | | | | | | | | | O
568351 | 7401 | 4 | 29 | 0 | 0 | 1 | 114723 | 121145 | L | Cesar Geronimo out to center fielder Cesar Cedeno. | Batter Out | | | | | | | | | O
568352 | 7401 | 4 | 30 | 0 | 3 | 2 | 119822 | 121145 | R | Gary Nolan strikes out swinging. | Strikeout | | | | | | | | | O
568353 | 7401 | 4 | 31 | 0 | 3 | 3 | 121454 | 121145 | L | Pete Rose strikes out swinging. | Strikeout | | | | | | | | | O
568354 | 7401 | 5 | 32 | 4 | 0 | 0 | 116573 | 119822 | R | Cliff Johnson walks. | Walk | | | | | | | | |
568355 | 7401 | 5 | 33 | 0 | 0 | 0 | 115188 | 119822 | L | Greg Gross singles on a to right fielder Ken Griffey. Cliff Johnson to 3rd. | Single | | | | | | | | | H
568356 | 7401 | 5 | 34 | 0 | 0 | 1 | 119047 | 119822 | L | Larry Milbourne grounds into a force out, second baseman Joe Morgan to shortstop Dave Concepcion. Cliff Johnson scores. Greg Gross out at 2nd. Larry Milbourne to 1st. | Forceout | | | | | | ground_ball | medium | 4 | O
568357 | 7401 | 5 | 35 | 0 | 0 | 1 | 119001 | 119822 | L | Roger Metzger singles on a to right fielder Ken Griffey. Larry Milbourne to 3rd. | Single | | | | | | | | | H
568358 | 7401 | 5 | 36 | 0 | 0 | 3 | 111260 | 119822 | L | Ken Boswell grounds into a double play, second baseman Joe Morgan to shortstop Dave Concepcion to first baseman Tony Perez. Roger Metzger out at 2nd. Ken Boswell out at 1st. | Grounded Into DP | | | | | | ground_ball | medium | 4 | O
568359 | 7401 | 5 | 37 | 0 | 0 | 0 | 115136 | 122543 | L | Ken Griffey singles on a to center fielder Cesar Cedeno. | Single | | | | | | | | | H
568360 | 7401 | 5 | 38 | 0 | 0 | 0 | 119371 | 122543 | L | Joe Morgan singles on a to right fielder Greg Gross. Ken Griffey to 3rd. | Single | | | | | | | | | H
568361 | 7401 | 5 | 39 | 0 | 3 | 1 | 110849 | 122543 | R | Johnny Bench strikes out swinging. | Strikeout | | | | | | | | | O
568362 | 7401 | 5 | 40 | 0 | 0 | 1 | 120404 | 122543 | R | Tony Perez doubles (1) on a to left fielder Wilbur Howard. Ken Griffey scores. Joe Morgan scores. | Double | | | | | | | | | H
568363 | 7401 | 5 | 41 | 0 | 0 | 2 | 114333 | 122543 | R | George Foster out to center fielder Cesar Cedeno. | Batter Out | | | | | | | | | O
568364 | 7401 | 5 | 42 | 0 | 0 | 3 | 112549 | 122543 | R | Dave Concepcion pops out to catcher Cliff Johnson. | Pop Out | | | | | | popup | medium | 2 | O
568365 | 7401 | 6 | 43 | 0 | 0 | 1 | 116216 | 119822 | L | Wilbur Howard out, second baseman Joe Morgan to first baseman Tony Perez. | Batter Out | | | | | | | | | O
568366 | 7401 | 6 | 44 | 0 | 0 | 1 | 111847 | 119822 | R | Enos Cabell singles on a to center fielder Cesar Geronimo. | Single | | | | | | | | | H
568367 | 7401 | 6 | 45 | 0 | 0 | 1 | 112153 | 119822 | R | Cesar Cedeno hits a home run (1). Enos Cabell scores. | Home Run | | | | | | | | | H
568368 | 7401 | 6 | 46 | 0 | 0 | 1 | 123988 | 119822 | R | Bob Watson hits a home run (1). | Home Run | | | | | | | | | H
568369 | 7401 | 6 | 47 | 4 | 0 | 1 | 116573 | 111227 | R | Cliff Johnson walks. | Walk | | | | | | | | |
568370 | 7401 | 6 | 48 | 0 | 0 | 2 | 115188 | 111227 | L | Greg Gross lines out to left fielder George Foster. | Lineout | | | | | | line_drive | medium | 7 | O
568371 | 7401 | 6 | 49 | 0 | 0 | 3 | 119047 | 111227 | L | Larry Milbourne out, first baseman Tony Perez to pitcher Pedro Borbon. | Batter Out | | | | | | | | | O
568322 | 7401 | 1 | 0 | 0 | 0 | 1 | 116216 | 119822 | L | Wilbur Howard out to center fielder Cesar Geronimo. | Batter Out | | | | | | | | | O
568323 | 7401 | 1 | 1 | 0 | 0 | 2 | 111847 | 119822 | R | Enos Cabell out, shortstop Dave Concepcion to first baseman Tony Perez. | Batter Out | | | | | | | | | O
568324 | 7401 | 1 | 2 | 0 | 0 | 3 | 112153 | 119822 | R | Cesar Cedeno out to right fielder Ken Griffey. | Batter Out | | | | | | | | | O
568325 | 7401 | 1 | 3 | 0 | 0 | 0 | 121454 | 121145 | L | Pete Rose singles on a to center fielder Cesar Cedeno. | Single | | | | | | | | | H
568373 | 7401 | 6 | 51 | 0 | 0 | 0 | 111227 | 122543 | R | Pedro Borbon singles. Cesar Geronimo to 3rd. | Single | | | | | | bunt_grounder | medium | | H
568374 | 7401 | 6 | 52 | 0 | 0 | 0 | 121454 | 122543 | L | Pete Rose singles on a to center fielder Cesar Cedeno. Cesar Geronimo scores. Pedro Borbon to 2nd. | Single | | | | | | | | | H
568375 | 7401 | 6 | 53 | 0 | 0 | 1 | 115136 | 112711 | L | Ken Griffey grounds into a force out, second baseman Larry Milbourne to shortstop Roger Metzger. Pedro Borbon to 3rd. Pete Rose out at 2nd. Ken Griffey to 1st. | Forceout | | | | | | ground_ball | medium | 4 | O
568376 | 7401 | 6 | 54 | 0 | 0 | 1 | 119371 | 112711 | L | Joe Morgan singles on a to right fielder Greg Gross. Pedro Borbon scores. Ken Griffey to 3rd. Joe Morgan to 2nd. Joe Morgan advances to 2nd, on a fielding error by right fielder Greg Gross. | Single | | | | | | | | | H
568377 | 7401 | 6 | 55 | 4 | 0 | 1 | 110849 | 110278 | R | Johnny Bench walks. | Walk | | | | | | | | |
568378 | 7401 | 6 | 56 | 4 | 0 | 1 | 120404 | 110278 | R | Tony Perez walks. Ken Griffey scores. Joe Morgan to 3rd. Johnny Bench to 2nd. | Walk | | | | | | | | |
568379 | 7401 | 6 | 57 | 0 | 0 | 1 | 114333 | 110278 | R | George Foster singles on a to center fielder Cesar Cedeno. Joe Morgan scores. Johnny Bench scores. Tony Perez to 3rd. | Single | | | | | | | | | H
568380 | 7401 | 6 | 58 | 0 | 0 | 3 | 112549 | 110278 | R | Dave Concepcion hits into a double play, right fielder Greg Gross to catcher Cliff Johnson. Tony Perez out at home. | Double Play | | | | | | | | | O
568381 | 7401 | 7 | 59 | 0 | 0 | 0 | 119001 | 111227 | L | Roger Metzger singles on a to first baseman Tony Perez. | Single | | | | | | | | | H
568382 | 7401 | 7 | 60 | 0 | 0 | 0 | 112902 | 111227 | L | Jose Cruz singles on a to left fielder George Foster. Roger Metzger to 3rd. | Single | | | | | | | | | H
568383 | 7401 | 7 | 61 | 0 | 0 | 1 | 116216 | 111227 | L | Wilbur Howard pops out to third baseman Pete Rose. | Pop Out | | | | | | popup | medium | 5 | O
568384 | 7401 | 7 | 62 | 0 | 0 | 1 | 111847 | 111227 | R | Enos Cabell singles on a to shortstop Dave Concepcion. Roger Metzger scores. Jose Cruz to 2nd. | Single | | | | | | | | | H
568385 | 7401 | 7 | 63 | 0 | 0 | 2 | 112153 | 111227 | R | Cesar Cedeno out to right fielder Ken Griffey. Jose Cruz to 3rd. | Batter Out | | | | | | | | | O
568386 | 7401 | 7 | 64 | 0 | 0 | 3 | 123988 | 111227 | R | Bob Watson out to right fielder Ken Griffey. | Batter Out | | | | | | | | | O
568387 | 7401 | 7 | 65 | 0 | 0 | 0 | 114723 | 110571 | L | Cesar Geronimo singles on a to center fielder Cesar Cedeno. | Single | | | | | | | | | H
568388 | 7401 | 7 | 66 | 0 | 0 | 1 | 111227 | 110571 | R | Pedro Borbon grounds into a force out, pitcher Mike Barlow to shortstop Roger Metzger. Cesar Geronimo out at 2nd. Pedro Borbon to 1st. | Forceout | | | | | | ground_ball | medium | 1 | O
568389 | 7401 | 7 | 67 | 0 | 3 | 2 | 121454 | 110571 | L | Pete Rose strikes out swinging. | Strikeout | | | | | | | | | O
568390 | 7401 | 7 | 68 | 0 | 0 | 3 | 115136 | 110571 | L | Ken Griffey out, second baseman Larry Milbourne to first baseman Bob Watson. | Batter Out | | | | | | | | | O
568391 | 7401 | 8 | 69 | 0 | 0 | 0 | 116573 | 111227 | R | Cliff Johnson singles on a to left fielder George Foster. | Single | | | | | | | | | H
568392 | 7401 | 8 | 70 | 0 | 0 | 0 | 115188 | 111227 | L | Greg Gross singles on a to center fielder Cesar Geronimo. Cliff Johnson to 2nd. | Single | | | | | | | | | H
568393 | 7401 | 8 | 71 | 0 | 0 | 2 | 119047 | 111227 | L | Larry Milbourne grounds into a double play, second baseman Joe Morgan to shortstop Dave Concepcion to first baseman Tony Perez. Cliff Johnson to 3rd. Greg Gross out at 2nd. Larry Milbourne out at 1st. | Grounded Into DP | | | | | | ground_ball | medium | 4 | O
568394 | 7401 | 8 | 72 | 0 | 0 | 3 | 119001 | 111227 | L | Roger Metzger flies out to left fielder George Foster. | Flyout | | | | | | fly_ball | medium | 7 | O
568395 | 7401 | 8 | 73 | 4 | 0 | 0 | 119371 | 114315 | L | Joe Morgan walks. | Walk | | | | | | | | |
568396 | 7401 | 8 | 74 | 0 | 3 | 1 | 110849 | 114315 | R | Johnny Bench strikes out swinging. | Strikeout | | | | | | | | | O
568397 | 7401 | 8 | 75 | 0 | 0 | 1 | 120404 | 114315 | R | Tony Perez singles on a to right fielder Greg Gross. Joe Morgan to 2nd. | Single | | | | | | | | | H
568398 | 7401 | 8 | 76 | 0 | 0 | 2 | 114333 | 114315 | R | George Foster pops out to first baseman Bob Watson. | Pop Out | | | | | | popup | medium | 3 | O
568399 | 7401 | 8 | 77 | 0 | 0 | 3 | 112549 | 114315 | R | Dave Concepcion grounds into a force out, pitcher Ken Forsch to shortstop Roger Metzger. Tony Perez out at 2nd. Dave Concepcion to 1st. | Forceout | | | | | | ground_ball | medium | 1 | O
568400 | 7401 | 9 | 78 | 4 | 0 | 0 | 121281 | 111227 | R | Leon Roberts walks. | Walk | | | | | | | | |
568401 | 7401 | 9 | 79 | 0 | 0 | 1 | 116216 | 111227 | L | Wilbur Howard grounds into a force out, second baseman Joe Morgan to shortstop Dave Concepcion. Leon Roberts out at 2nd. Wilbur Howard to 1st. | Forceout | | | | | | ground_ball | medium | 4 | O
568402 | 7401 | 9 | 80 | 0 | 0 | 2 | 111847 | 111227 | R | Enos Cabell grounds into a force out, pitcher Pedro Borbon to shortstop Dave Concepcion. Wilbur Howard out at 2nd. Enos Cabell to 1st. | Forceout | | | | | | ground_ball | medium | 1 | O
568403 | 7401 | 9 | 81 | 0 | 0 | 3 | 112153 | 111227 | R | Cesar Cedeno pops out to third baseman Pete Rose. | Pop Out | | | | | | popup | medium | 5 | O
(82 rows)edit: just providing a sorted pitch table, since it's still running (gotta load 'er in man) so it's not gonna return what you typically would expect.
pitch_id | ab_id | des | type | id | x | y | start_speed | end_speed | sz_top | sz_bot | pfx_x | pfx_z | px | pz | x0 | y0 | z0 | vx0 | vy0 | vz0 | ax | ay | az | break_y | break_angle | break_length | spin_rate | spin_dir | zone | p_time | p_ext | time | on_1b | on_2b | on_3b | sv_id | pitch_type | type_confidence
----------+--------+------------------------------------------------------------------------------------------------------------------------------------+------+----+---+---+-------------+-----------+--------+--------+-------+-------+----+----+----+----+----+-----+-----+-----+----+----+----+---------+-------------+--------------+-----------+----------+------+--------+-------+------+--------+--------+--------+-------+------------+-----------------
10624940 | 568322 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624941 | 568323 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624942 | 568324 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624943 | 568325 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624944 | 568326 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 121454 | | | | |
10624945 | 568327 | Ken Griffey steals (1) 2nd base. Ken Griffey to 3rd. Ken Griffey advances to 3rd, on a throwing error by catcher Cliff Johnson. | | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 115136 | | | | |
10624946 | 568327 | In play, out(s) | X | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 115136 | | |
10624947 | 568328 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | | |
10624948 | 568329 | Swinging Strike | S | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624949 | 568329 | Swinging Strike | S | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624950 | 568329 | Swinging Strike | S | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624951 | 568330 | Swinging Strike | S | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624952 | 568330 | Swinging Strike | S | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624953 | 568330 | Swinging Strike | S | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624954 | 568331 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624955 | 568332 | Ball | B | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624956 | 568332 | Ball | B | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624957 | 568332 | Ball | B | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624958 | 568332 | Ball | B | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624959 | 568333 | Wild pitch by pitcher J. R. Richard. Tony Perez to 2nd. | | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | | | |
10624960 | 568333 | In play, out(s) | X | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | | |
10624961 | 568334 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | | |
10624962 | 568335 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 112549 | | | |
10624963 | 568336 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 112549 | | |
10624964 | 568337 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119822 | | | |
10624965 | 568338 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 121454 | | |
10624966 | 568339 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 121454 | | |
10624967 | 568340 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119047 | | | |
10624968 | 568341 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119047 | | | |
10624969 | 568342 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119047 | | | |
10624970 | 568343 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119047 | | | |
10624971 | 568344 | Joe Morgan steals (1) 2nd base. Joe Morgan to 3rd. Joe Morgan advances to 3rd, on a throwing error by catcher Cliff Johnson. | | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | | |
10624972 | 568344 | In play, out(s) | X | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | |
10624973 | 568345 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | |
10624974 | 568346 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | | | |
10624975 | 568347 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | | | |
10624976 | 568348 | Swinging Strike | S | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624977 | 568348 | Swinging Strike | S | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624978 | 568348 | Swinging Strike | S | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624979 | 568349 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624980 | 568350 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624981 | 568351 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624982 | 568352 | Swinging Strike | S | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624983 | 568352 | Swinging Strike | S | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624984 | 568352 | Swinging Strike | S | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624985 | 568353 | Swinging Strike | S | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624986 | 568353 | Swinging Strike | S | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624987 | 568353 | Swinging Strike | S | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624988 | 568354 | Ball | B | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624989 | 568354 | Ball | B | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624990 | 568354 | Ball | B | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624991 | 568354 | Ball | B | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10624992 | 568355 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | | | |
10624993 | 568356 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 115188 | | 116573 | | |
10624994 | 568357 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119047 | | | | |
10624995 | 568358 | In play, out(s) | X | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119001 | | 119047 | | |
10624996 | 568359 | In play, no out | D | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119047 | | |
10624997 | 568360 | Ken Griffey steals (2) 2nd base. | | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 115136 | | | | |
10624998 | 568360 | In play, no out | D | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 115136 | | | |
10624999 | 568361 | Joe Morgan steals (2) 2nd base. | | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | 115136 | | |
10625000 | 568361 | Swinging Strike | S | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | 115136 | | |
10625001 | 568361 | Swinging Strike | S | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | 115136 | | |
10625002 | 568361 | Swinging Strike | S | 4 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | 115136 | | |
10625003 | 568362 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | 115136 | | |
10625004 | 568363 | Wild pitch by pitcher Jose Sosa. Tony Perez to 3rd. | | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | | |
10625005 | 568363 | In play, out(s) | X | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | |
10625006 | 568364 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | |
10625007 | 568365 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | |
10625008 | 568366 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10625009 | 568367 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111847 | | | | |
10625010 | 568368 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10625011 | 568369 | Ball | B | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10625012 | 568369 | Ball | B | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10625013 | 568369 | Ball | B | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10625014 | 568369 | Ball | B | 4 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10625015 | 568370 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | | | |
10625016 | 568371 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | | | |
10625017 | 568372 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10625018 | 568373 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 114723 | | | |
10625019 | 568374 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111227 | | 114723 | | |
10625020 | 568375 | In play, out(s) | X | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 121454 | 111227 | | | |
10625021 | 568376 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 115136 | | 111227 | | |
10625022 | 568377 | Ball | B | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | 115136 | | |
10625023 | 568377 | Ball | B | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | 115136 | | |
10625024 | 568377 | Ball | B | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | 115136 | | |
10625025 | 568377 | Ball | B | 4 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | 115136 | | |
10625026 | 568378 | Ball | B | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 110849 | 119371 | 115136 | | |
10625027 | 568378 | Ball | B | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 110849 | 119371 | 115136 | | |
10625028 | 568378 | Ball | B | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 110849 | 119371 | 115136 | | |
10625029 | 568378 | Ball | B | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 110849 | 119371 | 115136 | | |
10625030 | 568379 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | 110849 | 119371 | | |
10625031 | 568380 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 114333 | | 120404 | | |
10625032 | 568381 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | | |
10625033 | 568382 | In play, no out | D | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119001 | | | | |
10625034 | 568383 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 112902 | | 119001 | | |
10625035 | 568384 | In play, run(s) | E | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 112902 | | 119001 | | |
10625036 | 568385 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111847 | 112902 | | | |
10625037 | 568386 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111847 | | 112902 | | |
10625038 | 568387 | In play, no out | D | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 112902 | | |
10625039 | 568388 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 114723 | | | | |
10625040 | 568389 | Swinging Strike | S | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111227 | | | | |
10625041 | 568389 | Swinging Strike | S | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111227 | | | | |
10625042 | 568389 | Swinging Strike | S | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111227 | | | | |
10625043 | 568390 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111227 | | | | |
10625044 | 568391 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
10625045 | 568392 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | | | |
10625046 | 568393 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 115188 | 116573 | | | |
10625047 | 568394 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | |
10625048 | 568395 | Ball | B | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | |
10625049 | 568395 | Ball | B | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | |
10625050 | 568395 | Ball | B | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | |
10625051 | 568395 | Ball | B | 4 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116573 | | |
10625052 | 568396 | Swinging Strike | S | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | | |
10625053 | 568396 | Swinging Strike | S | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | | |
10625054 | 568396 | Swinging Strike | S | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | | |
10625055 | 568397 | In play, no out | D | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | | |
10625056 | 568398 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | 119371 | | | |
10625057 | 568399 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 120404 | 119371 | | | |
10625058 | 568400 | Ball | B | 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | |
10625059 | 568400 | Ball | B | 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | |
10625060 | 568400 | Ball | B | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | |
10625061 | 568400 | Ball | B | 4 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 119371 | | | |
10625062 | 568401 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 121281 | | | | |
10625063 | 568402 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 116216 | | | | |
10625064 | 568403 | In play, out(s) | X | 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 111847 | | | | |
(125 rows)
(most of the players, i think)
newmlb=# select distinct * from players where eliasid in (select on_1b from pitches where ab_id in (select ab_id from atbats where game_id=7401) );
eliasid | first | pref | middle | last | throws | bats | sz_top | sz_bot | birth | debut | retired | country | state | city
---------+----------+--------+-----------+-----------+--------+------+--------+--------+------------+------------+------------+---------+-------+----------------
110849 | Johnny | Johnny | Lee | Bench | R | R | 3.411 | 1.565 | 1947-12-07 | 1967-08-28 | 1983-09-29 | US | OK | Oklahoma City
111227 | Pedro | Pedro | | Borbon | R | R | 3.467 | 1.589 | 1946-12-02 | 1969-04-09 | 1980-05-25 | DO | | Mao
111847 | Enos | Enos | Milton | Cabell | R | R | 3.575 | 1.681 | 1949-10-08 | 1972-09-17 | 1986-09-29 | US | KS | Fort Riley
112902 | Jose | Jose | | Cruz | L | L | 3.371 | 1.535 | 1947-08-08 | 1970-09-19 | 1988-07-19 | PR | | Arroyo
114333 | George | George | Arthur | Foster | R | R | 3.411 | 1.565 | 1948-12-01 | 1969-09-10 | 1986-09-06 | US | AL | Tuscaloosa
114723 | Cesar | Cesar | Francisco | Geronimo | L | L | 3.467 | 1.589 | 1948-03-11 | 1969-04-16 | 1983-08-28 | DO | | El Seibo
115136 | George | Ken | Kenneth | Griffey | L | L | 3.371 | 1.535 | 1950-04-10 | 1973-08-25 | 1991-05-31 | US | PA | Donora
115188 | Gregory | Greg | Eugene | Gross | L | L | 3.319 | 1.513 | 1952-08-01 | 1973-09-05 | 1989-09-27 | US | PA | York
116216 | Wilbur | Wilbur | Leon | Howard | R | S | 3.467 | 1.589 | 1949-01-08 | 1973-09-04 | 1978-09-30 | US | NC | Lowell
116573 | Clifford | Cliff | | Johnson | R | R | 3.549 | 1.627 | 1947-07-22 | 1972-09-13 | 1986-09-30 | US | TX | San Antonio
119001 | Roger | Roger | Henry | Metzger | R | S | 3.371 | 1.535 | 1947-10-10 | 1970-06-16 | 1980-08-10 | US | TX | Fredericksburg
119047 | Lawrence | Larry | William | Milbourne | R | S | 3.371 | 1.535 | 1951-02-14 | 1974-04-06 | 1984-09-29 | US | NJ | Port Norris
119371 | Joe | Joe | Leonard | Morgan | R | L | 3.179 | 1.474 | 1943-09-19 | 1963-09-21 | 1984-09-30 | US | TX | Bonham
120404 | Atanacio | Tony | | Perez | R | R | 3.467 | 1.589 | 1942-05-14 | 1964-07-26 | 1986-10-05 | CU | | Camaguey
121281 | Leon | Leon | Kauffman | Roberts | R | R | 3.49 | 1.601 | 1951-01-22 | 1974-09-03 | 1984-09-30 | US | MI | Vicksburg
121454 | Peter | Pete | Edward | Rose | R | S | 3.319 | 1.513 | 1941-04-14 | 1963-04-08 | 1986-08-17 | US | OH | Cincinnatifeels good, not gonna lie.
it is WAY faster, by the way. the entire 75 season took like 5-6 hours. this shit is so tight.
i can't wait till it's done. hoping the newer data won't cause any problems. i figured handling the old data so well that it shouldn't be a problem.
this is gonna be unreal. the resolution of my db is higher than MLB's. they no longer offer pitch-resolution data.
seeing jr's dad advance two bases between two pitches is as-good as it gets man.
fucking jr, you're the goat man.
i3roly
commented
2 years ago i find the new API to be inferior to the old one, but what can ya do.
whining about it won't change the fact the new API completely ignores the difficulty of storing and recording baserunners. their live-feed may display them but it certainly does not save it. it seems MLB treats the live data differently from what's stored.
anyways, i needed to test a huge edge-case.
the situation can arise where we have a pinch-runner who steals a base. this is difficult for two reasons
the only saving grace about the new API is it does, in a 'pitch' entry (or what it calls "playEvents"), tell us a pinch-runner replaced the original baserunner.
to make matters worse, if this pinch-runner steals a base during this at-bat, we have to figure out how to advance them, meaning our internal representation of the game state has to deal with a huge 'hole'.
i have done it! i used emilio bonifacio because he was the first example i found that broke my representation.
here's a game where bonifacio is substituted as a pinch-runner and steals a base in that same at-bat.
newmlb2013=# select * from games where game_id=3978;
game_id | date | home | away | game | umpire | wind | wind_dir | temp | mlb_id | runs_home | runs_away | local_time | venue | att
---------+---------------------+------+------+------+--------+------+----------+------+--------+-----------+-----------+---------------------+-----------------+------
3978 | 2013-05-06 00:00:00 | tba | tor | 1 | | 0 | | 72 | 347222 | 7 | 8 | 2013-05-06 23:10:00 | Tropicana Field | 9952
(1 row)next, find the at-bat where bonifacio is substituted as a pinch-runner, in this case for adam lind: (an aside, i made a 'design choice' not to include 'game advisory' events that can be coaching visits or substitutions, as it would make my intended (and planned) future analysis much easier when i'm pulling blocks of pitch data for players.)
newmlb2013=# select * from atbats where game_id=3978 order by ab_id asc;
ab_id | game_id | inning | num | ball | strike | outs | batter | pitcher | stand | des | event | hit_x | hit_y | launch_speed | launch_angle | total_distance | hit_traj | hit_hardness | hit_location | hit_type
--------+---------+--------+-----+------+--------+------+--------+---------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+--------+--------------+--------------+----------------+-------------+--------------+--------------+—————
**snip**
304805 | 3978 | 9 | 77 | 4 | 2 | 0 | 452252 | 407845 | L | Adam Lind walks. | Walk | | | | | | | | |
304806 | 3978 | 9 | 78 | 3 | 3 | 1 | 458675 | 407845 | L | Colby Rasmus strikes out swinging. | Strikeout | | | | | | | | | O
**snip**lastly, verify that bonifacio advances on base, in spite of not having been a hitter (meaning we have to first substitute him for adam lind, then move him somehow):
newmlb2013=# select * from pitches where ab_id=304806 order by pitch_id asc;
pitch_id | ab_id | des | type | id | x | y | start_speed | end_speed | sz_top | sz_bot | pfx_x | pfx_z | px | pz | x0 | y0 | z0 | vx0 | vy0 | vz0 | ax | ay | az | break_y | break_angle | break_length | spin_rate | spin_dir | zone | p_time | p_ext | time | on_1b | on_2b | on_3b | sv_id | pitch_type | type_confidence
----------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+------+----+--------+--------+-------------+-----------+--------+--------+-------+-------+-------+------+-------+-------+------+-------+---------+-------+--------+-------+--------+---------+-------------+--------------+-----------+----------+------+--------+-------+----------+--------+-------+--------+---------------+------------+-----------------
10289965 | 304806 | Called Strike | C | 1 | 112.45 | 160.60 | 96.10 | 88.80 | 3.61 | 1.73 | -5.39 | 6.84 | -0.23 | 1.74 | -0.94 | 50.00 | 5.81 | 3.92 | -140.66 | -8.00 | -10.82 | 31.15 | -18.36 | 24.00 | 26.40 | 4.80 | 1809 | 218 | 8 | | | 00:00:25 | 466988 | | | 03472226-0796 | FT | NaN
10289966 | 304806 | Missed Bunt | M | 2 | 113.30 | 164.06 | 96.50 | 89.30 | 3.44 | 1.50 | -5.84 | 6.30 | -0.42 | 1.73 | -1.06 | 50.00 | 5.78 | 3.90 | -141.24 | -7.87 | -11.83 | 31.04 | -19.34 | 24.00 | 27.60 | 4.80 | 1793 | 222 | 7 | | | 00:00:20 | 466988 | | | 03472226-0796 | FT | NaN
10289967 | 304806 | Pickoff Attempt 1B | 1 | 3 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 466988 | | | | |
10289968 | 304806 | Ball | B | 4 | 190.56 | 128.65 | 84.60 | 78.00 | 3.57 | 1.69 | -5.77 | 3.05 | -2.69 | 3.06 | -1.50 | 50.00 | 5.99 | -1.08 | -124.02 | -1.55 | -8.96 | 25.36 | -27.36 | 24.00 | 18.00 | 7.20 | 1189 | 241 | 11 | | | 00:00:23 | 466988 | | | 03472226-0796 | CH | NaN
10289969 | 304806 | Ball | B | 5 | 163.09 | 171.83 | 85.10 | 78.30 | 3.42 | 1.58 | -5.39 | 4.79 | -1.97 | 1.37 | -1.10 | 50.00 | 5.79 | -0.41 | -124.63 | -5.83 | -8.41 | 26.85 | -24.63 | 24.00 | 18.00 | 7.20 | 1315 | 228 | 13 | | | 00:00:41 | 466988 | | | 03472226-0796 | CH | NaN
10289970 | 304806 | Emilio Bonifacio steals (3) 2nd base. Emilio Bonifacio to 3rd. Emilio Bonifacio advances to 3rd, on a throwing error by catcher Jose Molina. | | 6 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 466988 | | | | |
10289971 | 304806 | Ball | B | 7 | 100.43 | 120.88 | 98.50 | 90.50 | 3.45 | 1.61 | -6.66 | 7.05 | 0.04 | 3.51 | -0.30 | 50.00 | 6.15 | 3.41 | -144.38 | -4.48 | -14.03 | 34.47 | -17.26 | 24.00 | 36.00 | 4.80 | 2057 | 223 | 12 | | | 00:00:27 | | | 466988 | 03472226-0796 | FT | NaN
10289972 | 304806 | Swinging Strike | S | 8 | 157.08 | 168.37 | 86.60 | 79.80 | 3.44 | 1.50 | -8.55 | 5.25 | -1.83 | 1.50 | -0.90 | 50.00 | 5.83 | 0.45 | -126.87 | -6.11 | -13.85 | 27.55 | -23.60 | 24.00 | 28.80 | 7.20 | 1862 | 238 | 13 | | | 00:00:09 | | | 466988 | 03472226-0796 | CH | NaN
(8 rows)now, verify the player is emilio:
newmlb2013=# select * from players where eliasid=466988;
eliasid | first | pref | middle | last | throws | bats | sz_top | sz_bot | birth | debut | retired | country | state | city
---------+--------+--------+--------+-----------+--------+------+--------+--------+------------+------------+---------+---------+-------+---------------
466988 | Emilio | Emilio | Jose | Bonifacio | R | S | 3.301 | 1.504 | 1985-04-23 | 2007-09-02 | | DO | | Santo Domingo
(1 row)
in the words of the butabi brothers:
I WAS LIKE "EMILIOOOOOOOOO!"
I tried to get 2019 year's data. But the 2019 year's data downloads failed. Except 2019 year's, it is working well. ( I tested 2013 to 2018 )
This is the error message and my code.