I need all the pitches and who threw them. I have downloaded the data into a sqlite db using the PitchRx library in R. All the pitch data is stored in a table called "pitch" and the pitcher name and ID number is stored in "atbat". The way I saw this done on this site uses the SQL code:
SELECT * FROM atbat INNER JOIN pitch ON
(atbat.num = pitch.num AND atbat.url = pitch.url)
WHERE atbat.pitcher_name = 'Mariano Rivera'
But there are duplicates of pairs in the "pitch" table with "num" and "url", so you could be merging Mariano Rivera's name with a pitch from a different pitcher who threw the same day. I don't believe this joining the tables correctly. What is the correct way to join this data?
Note: I am using the SQL code in the sqlite3 library in python so there may be issues with the SQL code.
url
andnum
, because there are duplicate rows in my scraped dataset for some reason. I used the following code to identify the duplicate rows when grouping byurl
andnum
: SELECT , COUNT() FROM mlbam_atbats GROUP BY url,num HAVING COUNT(*) > 1 It turns out there 14,439 rows that are affected, comprised of pairs of rows (one of each row pair being a duplicate). I tried to use the following code to delete one row of each of the pairs, but way more rows were deleted than just the intended duplicated rows (from here [https – LeeZee Oct 1 '17 at 17:20