Project 3: Finding Relationships in Baseball

Published

May 1, 2020

Walkthrough

SQL Refresher

Background

When you hear the word “relationship” what is the first thing that comes to mind? Probably not baseball. But a relationship is simply a way to describe how two or more objects are connected. There are many relationships in baseball such as those between teams and managers, players and salaries, even stadiums and concession prices.

The graphs on Data Visualizations from Best Tickets show many other relationships that exist in baseball.

Client Request

For this project, the Client wants SQL queries that they can use to retrieve data for use on their website without needing Python. They would also like to see the results in Lets-Plot charts.

Data

Note

Every data science project should start with data, and our class projects are no different. Each project will have ‘URL’ and ‘Information’ links like the ones below. Project 3 will use the Lahman Baseball Database. You will need to download the database and set it up on your computer to complete the project. Place it inside the DS250 Projects folder within your repository next to the project_3.qmd file. Note: Right click the ‘Download’ link and select “Save Link As” to download the data to your computer.

Download: lahmansbaseballdb
Information: Lahman Data Dictionary
Setup Instructions: See SQL Setup

Readings

Optional References

Questions and Tasks (Core)

  1. Write an SQL query to create a new dataframe about baseball players who attended BYU-Idaho. The new table should contain five columns: playerID, schoolID, salary, and the yearID/teamID associated with each salary. Order the table by salary (highest to lowest) and print out the table in your report.

  2. This three-part question requires you to calculate batting average (number of hits divided by the number of at-bats)

    1. Write an SQL query that provides playerID, yearID, and batting average for players with at least 1 at bat that year. Sort the table from highest batting average to lowest, and then by playerid alphabetically. Show the top 5 results in your report.
    2. Use the same query as above, but only include players with at least 10 at bats that year. Print the top 5 results.
    3. Now calculate the batting average for players over their entire careers (all years combined). Only include players with at least 100 at bats, and print the top 5 results.

  3. Pick any two baseball teams and compare them using a metric of your choice (average salary, home runs, number of wins, etc). Write an SQL query to get the data you need, then make a graph using Lets-Plot to visualize the comparison. What do you learn?

Questions and Tasks (Stretch)

Here is an example Stretch question(s) for this project. Your instructor may assign different Stretch question(s). You must comment in Canvas when submitting your project if you completed any of the Stretch questions.

  1. Advanced Salary Distribution by Position (with Case Statement):

    • Write an SQL query that provides a summary table showing the average salary for players in each position (e.g., pitcher, catcher, outfielder) across all years. Include the following columns:

      • position
      • average_salary
      • total_players
      • highest_salary

    • The highest_salary column should display the highest salary ever earned by a player in that position. If no player in that position has a recorded salary, display “N/A” for the highest salary.

    • Additionally, create a new column called salary_category using a case statement:

      • If the average salary is above $1 million, categorize it as “High Salary.”
      • If the average salary is between $500,000 and $1 million, categorize it as “Medium Salary.”
      • Otherwise, categorize it as “Low Salary.”

    • Order the table by average salary in descending order.

    • Print the top 10 rows of this summary table.

  2. Advanced Career Longevity and Performance (with Subqueries):

    • Calculate the average career length (in years) for players who have played at least one game. Then, identify the top 10 players with the longest careers (based on the number of years they played). Include their:

      • playerID
      • first_name
      • last_name
      • career_length

    • The career_length should be calculated as the difference between the maximum and minimum yearID for each player.

Submission:

When you have completed the report, you will need to follow this process to submit your work:

  1. Have the Course Work Portfolio open in VS Code and open Projects/Project0.qmd
  2. Click Preview Button in VS Code in the top right of the screen
    1. This will render the project but also entire course work portfolio into HTML files for review
    2. Confirm everything displas as you would like it to
    3. How you see it will be how it is viewed for grading
    4. If there is an error in any cell of the quarto files, the rendering will stop and you will need to fix the error before rendering again (if you get stuck post your error in Slack)
  3. Once the report is confirmed close the preview and open the GitHub Desktop application
  4. Confirm you are in the correct repository in the top left corner of the screen
  5. Confirm you are on the correct branch Main in the top left corner of the screen (Never change off the Main branch)
  6. Type a summary of the changes in the Summary box
  7. Click Commit to main blue button in the bottom left corner
  8. Click Push origin blue button in the middle right of the screen
    1. This will push all your changes in the project .qmd file to GitHub
    2. The publish.yml file will kick off an automated process to render the project into HTML files
    3. The HTML files will be published to GitHub pages in the gh-pages branch
    4. The URL to the published project will be in the deployment section in GitHub
      1. In GitHub Desktop click Open in GitHub to navigete to the repository
      2. Click on the Actions tab and make sure there were no errors in the rendering process
      3. Click on the deployment section of the main page of the repository to find the URL
      4. Navigate to the URL and confirm it displays as you intended
      5. Copy the URL and submit it in Canvas

Deliverables:

Use this template to submit your Client Report. The template has two sections:

  1. A short elevator pitch that highlights key values or metrics from the results. Describing these key insights to interest or hook the reader to want to read more about your work. The writing style should be more technical with some creative elements. Do not summarize what you did.
  2. Answers to the questions | tasks. Each should include a written description of your results, code cells with comments, charts and/or tables.
  3. A short summary of work must be submitted in the comments in Canvas wwhen you submit the URL. Rate your own work on a scale of 1-5. 1 being poor and 5 being excellent. Include a short description of why you rated your work the way you did.
Back to top