Scraping Baseball-Reference.com with Python BeautifulSoup and pandas


This handy little program scrapes batting statistics data from an HTML table on the Boston Red Sox page at www.baseball-reference.com. It converts that into a pandas DataFrame, then does various cleanup to the data set. And then the data is inserted into a PostgreSQL table.

# -*- coding: utf-8 -*-
"""
Get Red Sox batting statistics from baseball-reference.com.
Turn it into a pandas DataFrame.
Insert the data into PostgreSQL.

"""
import requests
from bs4 import BeautifulSoup
import lxml
import pandas as pd
import os
from sqlalchemy import create_engine
import psycopg2
import io

DATABASE_URL=os.environ['DATABASE_URL']

def red_sox_batting_stats():
    # Get a page from the web
    url = 'https://www.baseball-reference.com/teams/BOS/2018.shtml'

    response = requests.get(url)

    # Process page from the web.
    soup = BeautifulSoup(response.text, 'lxml')

    # Find the batting stats table.
    table = soup.find('table', id='team_batting')

    # Create a matrix:
    # For each row, get the text from each cell.
    # The table has empty cells which must be replaced by something,
    # e.g. 'null', in order to preserve row length.
    outer_list = []
    for row in table.find_all('tr'):
        inner_list = []
        for cell in row.find_all('td'):
            text = cell.text
            inner_list.append(''.join(text) if text else 'null')
        outer_list.append(inner_list)

    # Create a pandas DataFrame from the matrix.  
    df = pd.DataFrame(outer_list)
    df.columns = ['Pos', 'Name', 'Age', 'G', 'PA', 'AB', 'R', 'H', '2B', \
        '3B', 'HR', 'RBI', 'SB', 'CS', 'BB', 'SO', 'BA', 'OBP', 'SLG', 'OPS', \
        'OPS+', 'TB', 'GDP', 'HBP', 'SH', 'SF', 'IBB']

    # Drop rows that do not contain stats.
    df = df[~df['Pos'].isin(['null'])]
    df = df.dropna()
    # Replace 'null' with 0. Replace (strings in parentheses) with nothing.
    # Replace '*' with nothing. Replace '#' with nothing.
    df = df.replace('null', 0).replace(r'\(.*\)', '', regex=True) \
        .replace(r'\*', '', regex=True).replace(r'\#', '', regex=True)
   
    # print(df.to_string()) # Print the DataFrame to the screen.

    # Insert the DataFrame into PostgreSQL
    engine = create_engine(DATABASE_URL)
    conn = engine.raw_connection()
    cur = conn.cursor()
    output = io.StringIO()
    df.to_csv(output, sep='\t', header=False, index=True)
    output.seek(0)
    cur.copy_from(output, 'batting_stats_current_season')
    conn.commit()

if __name__ == '__main__':
    red_sox_batting_stats()

Comments

Post a Comment

Popular posts from this blog

Creating a PostgreSQL Database, the Use of Database Templates, and Simple Command Line Tool Commands

A PostgreSQL server installation includes database templates. These are used when creating new databases. By default, template1 is copied when you create a new database. This makes it easy to create copies of a customized database design. You can customize template1, and then easily make as many copies as you like, and they will all start out with the same objects, such as tables, languages, etc. Specify template0 to create a standard, out-of-the-box database with no custom additions that may have been added to your server installation. Make sure that you do not make any changes to template0. Create a database using template0, from a command line A database can be created in various ways. Here we will create one from the command line, using the tool PostgreSQL createdb. By default, the logged in user will be the owner of a newly created database. While logged in as user postgres, from a command line, create a database named exampledb, using template0: bash-4.2$ createdb...
Read more

Library Book

On the way home from work I stopped by the library to pick up a book I ordered last week, The Know-It-All: One Man's Humble Quest to Become the World's Smartest Person in the World , by A. J. Jacobs. It's about the author's quest to read the entire Encyclopedia Britannica. First, I feel compelled to say that if you're not taking advantage of the fact that you get to use your library membership to order books online and have them dropped off at your local branch, do it! Start doing it right away. I contend that this is the second most crucial use of my tax dollars, after paying firemen. A blurb by P. J. O'Rourke, found on the back of the book: " The Know-It-All is a terrific book. It's a lot shorter than the encyclopedia, and funnier, and you'll remember more of it. Plus, if it falls off the shelf onto your head, you'll live." [Hours later, about fifty-nine minutes of which I spent replying to my grandmother's email, the rest, I'm n...
Read more

Date Dimension Table Definition

A few weeks ago I found this definition of a date dimension table, created by a smart person named  Nicholas Duffy . I'm placing it here so I can find it easily. DROP TABLE IF EXISTS dim_date; CREATE TABLE dim_date ( date_dim_id INT NOT NULL, date_actual DATE NOT NULL, epoch BIGINT NOT NULL, day_suffix VARCHAR(4) NOT NULL, day_name VARCHAR(9) NOT NULL, day_of_week INT NOT NULL, day_of_month INT NOT NULL, day_of_quarter INT NOT NULL, day_of_year INT NOT NULL, week_of_month INT NOT NULL, week_of_year INT NOT NULL, week_of_year_iso CHAR(10) NOT NULL, month_actual INT NOT NULL, month_name VARCHAR(9) NOT NULL, month_name_abbreviated CHAR(3) NOT NULL, quarter_actual INT NOT NULL, quarter_name VARCHAR(9) NOT NULL, year_actual INT NOT ...
Read more