2

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.

Improve this question
4
  • But now, I'm having trouble creating the composite primary key of url and num, because there are duplicate rows in my scraped dataset for some reason. I used the following code to identify the duplicate rows when grouping by url and num: 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
  • Why do you need yet another account?! – Stanislav Kralin Oct 1 '17 at 17:59
  • I don't, I seemed to have trouble logging into the previously created one to make this last post. – LeeZee Oct 1 '17 at 18:00
  • I've been staring at this mod flag for a week now but I don't know what to do – philshem♦ Oct 10 '17 at 12:20
2

The following SQL statement works fine to merge the tables. 

SELECT * FROM atbat INNER JOIN pitch ON
(atbat.num = pitch.num AND atbat.url = pitch.url)
Improve this answer
2

While your own solution to this dilemma is correct, it might help someone else to explain why your posted solution works, especially if they used another tutorial using another approach to scrape MLB game data, one that doesn't give you the URL of the webpage identifying the location from where game-specific data was scraped.

Initially, I struggled with the same question. Namely, how can we be certain that the correct pitch will be correctly merged with its corresponding at-bat upon merging the "pitch" table with the "at_bat" table?

I realized that the at-bat numbers for each game (column num) start numbering from the first pitch of the game and are sequentially numbered through to the end of the game; The numbering doesn't stop at the end of each pitcher's appearance and start again with the onset of a new pitcher's appearance as I (and I'm guessing you) originally thought.

So, given that, one can merge the two tables using an inner join on a composite primary key consisting of num and url. So, to uniquely identify each row, we need the appropriate game-specific information including game-id, game date, away team, home team (which are included in and indicated by the url) and the at-bat number (which is reflected by num) such that each pitcher's unique pitch (pitch_num) will fit within each unique at-bat (num) within each game.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.