Scrape HTML Tables in Python with Pandas read_html

import pandas as pd

# The URL of the Wikipedia page we want to scrape
wiki_url = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'  # Changed Example URL

# Use read_html to parse tables from the URL
list_of_tables = pd.read_html(wiki_url)

# Let's see how many tables Pandas found
print(f"Found {len(list_of_tables)} tables on the page.")

# Often, the main table is one of the first ones. Let's grab the first table.
# Note: You might need to inspect the list_of_tables to find the correct index
sp500_table = list_of_tables[0]

# Display the first few rows to see what we got
print(sp500_table.head())

import pandas as pd

# The URL of the Wikipedia page
wiki_url = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'

# Use read_html with the 'match' parameter
# We're looking for a table that specifically contains "Ticker symbol"
try:
    tables_matched = pd.read_html(wiki_url, match="Ticker symbol")
    print(f"Found {len(tables_matched)} table(s) matching the criteria.")

    # If matches are found, proceed with the first one
    if tables_matched:
        sp500_data = tables_matched[0]
        print("\nFirst few rows of the matched table:")
        print(sp500_data.head())
    else:
        print("No tables found matching 'Ticker symbol'.")
except ValueError as e:
    print(f"An error occurred: {e}")
    print("This might happen if no table matches the specified text.")

# (Assuming sp500_data contains the DataFrame from the previous step)
print("\nDataFrame Info before cleanup:")
sp500_data.info()
print("\nSample data before cleanup:")
print(sp500_data.head())

import pandas as pd
import re

# (Assuming wiki_url and match criteria are set as before)
# wiki_url = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'
# tables_matched = pd.read_html(wiki_url, match="Ticker symbol")
# sp500_data = tables_matched[0] # Make sure this line runs successfully


# Function to sanitize column names
def sanitize_colnames(col_name):
    # Handle potential MultiIndex tuples by joining them
    if isinstance(col_name, tuple):
        col_name = '_'.join(filter(None, col_name)) # Join non-empty parts
    # Remove footnote references like [1], [a], etc.
    col_name = re.sub(r'\[.*?\]', '', str(col_name))
    # Replace spaces and special chars with underscore, convert to lower
    col_name = re.sub(r'\W+', '_', col_name).lower().strip('_')
    return col_name


# Apply the sanitization function to the DataFrame's columns
sp500_data.columns = [sanitize_colnames(col) for col in sp500_data.columns]

print("\nDataFrame Info after cleaning column names:")
sp500_data.info()

print("\nSample data after cleaning column names:")
print(sp500_data.head())

# (Assuming sp500_data has cleaned column names now)
# Convert 'date_first_added' column to datetime objects
# Sometimes this column might not exist or have a different name after cleaning
date_col_name = 'date_first_added'  # Adjust if needed after checking cleaned names

if date_col_name in sp500_data.columns:
    sp500_data[date_col_name] = pd.to_datetime(
        sp500_data[date_col_name], errors='coerce'
    )
else:
    # Handle cases where specific columns might be named differently or missing
    # Check sp500_data.columns to find the correct date column name
    # Example: try finding a column containing 'date'
    potential_date_cols = [col for col in sp500_data.columns if 'date' in col]
    if potential_date_cols:
        # Attempt conversion on the first likely candidate
        date_col_name = potential_date_cols[0]
        sp500_data[date_col_name] = pd.to_datetime(
            sp500_data[date_col_name], errors='coerce'
        )
        print(f"Attempted date conversion on column: {date_col_name}")
    else:
        print("Could not automatically identify the date column for conversion.")

# Identify potential numeric columns (e.g., based on typical names or inspection)
# This is example only - the S&P500 list doesn't have many obvious numeric cols besides maybe CIK
numeric_cols_potential = ['cik']  # Central Index Key - should be numeric

for col in numeric_cols_potential:
    if col in sp500_data.columns:
        # 'coerce' turns errors into NaN (Not a Number)
        sp500_data[col] = pd.to_numeric(sp500_data[col], errors='coerce')
        # If you need integers and can handle NaNs, use Int64
        # sp500_data[col] = sp500_data[col].astype('Int64')

print("\nDataFrame Info after data type conversion attempts:")
sp500_data.info()

print("\nFinal sample data:")
print(sp500_data.head())

import pandas as pd
import re

# URL of the target page
wiki_url = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'
match_text = "Ticker symbol"  # Text to find the correct table

print(f"Attempting to read table from: {wiki_url}")
print(f"Looking for tables containing: '{match_text}'")

try:
    # Read HTML tables matching the specified text
    tables_matched = pd.read_html(wiki_url, match=match_text)
    print(f"\nFound {len(tables_matched)} table(s) matching the criteria.")

    if not tables_matched:
        print("No matching table found. Exiting.")
        exit()

    # Assume the first matched table is the one we want
    data_table = tables_matched[0]

    print("\nInitial table head:")
    print(data_table.head())

    # --- Column Name Cleaning ---
    def sanitize_colnames(col_name):
        if isinstance(col_name, tuple):
            col_name = '_'.join(filter(None, col_name))
        # Remove content within square brackets (like [a], [b])
        col_name = re.sub(r'\[.*?\]', '', str(col_name))
        # Replace non-alphanumeric characters with underscore, convert to lower, strip ends
        col_name = re.sub(r'\W+', '_', col_name).lower().strip('_')
        return col_name

    data_table.columns = [sanitize_colnames(col) for col in data_table.columns]

    print("\nColumn names after cleaning:")
    print(data_table.columns.tolist())

    # --- Data Type Conversion ---

    # Convert date column
    date_col_name = None
    # Find columns containing 'date_added' or 'date_first_added'
    potential_date_cols = [
        col for col in data_table.columns
        if 'date_added' in col or 'date_first_added' in col
    ]
    if potential_date_cols:
        date_col_name = potential_date_cols[0]
        # Convert the identified date column to datetime objects
        data_table[date_col_name] = pd.to_datetime(
            data_table[date_col_name], errors='coerce'
        )
        print(f"\nConverted '{date_col_name}' to datetime.")
    else:
        print("\nCould not find a suitable date column to convert.")

    # Convert CIK column to numeric (potentially nullable integer)
    cik_col_name = 'cik'
    if cik_col_name in data_table.columns:
        # Convert CIK column to numeric, coercing errors to NaN
        data_table[cik_col_name] = pd.to_numeric(
            data_table[cik_col_name], errors='coerce'
        )
        # Optionally convert to nullable integer if NaNs are acceptable
        data_table[cik_col_name] = data_table[cik_col_name].astype('Int64')
        print(f"Converted '{cik_col_name}' to numeric (Int64).")
    else:
        print(f"\nColumn '{cik_col_name}' not found for numeric conversion.")

    # --- Display Final Results ---

    print("\nFinal DataFrame Info:")
    data_table.info()

    print("\nFinal Cleaned Data (first 5 rows):")
    print(data_table.head())

except ValueError as e:
    print(f"\nAn error occurred: {e}")
    print("This often means no table matched the 'match' criteria.")
except Exception as e:
    print(f"\nAn unexpected error occurred: {e}")