使用Python Pandas筛选合规歌曲ID并识别不合格CSV文件

First off, let's fix the core issue with your filtering logic—your current code checks if a CUE group has zero composers or zero publishers entirely, but that doesn’t align with your requirement. You need to flag files containing any CUE where no single row has both a non-empty COMPOSER and PUBLISHER (e.g., CUEs with only composers, only publishers, or neither across all rows).

Here’s a refined, more robust version of your code with key improvements:

Key Improvements: #

• Corrected filtering logic: Checks for CUEs that lack at least one row with both composer and publisher data
• Cleaner data loading: Reduces hardcoding, makes column definitions reusable
• Better error handling: Adds SMTP exception catching and clearer email context
• Transparent reporting: Includes problematic CUEs in the email for easier debugging

Optimized Code #

import pandas as pd
import numpy as np
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# ----------------------
# Configuration (easy to update)
# ----------------------
CSV_FILE_PATH = 'New York Yankees Twins at Yankees-FNG-042318.csv'
EMAIL_FROM = ''
EMAIL_TO = 'xyz@abc.com'
SMTP_USERNAME = ''
SMTP_PASSWORD = ''
SMTP_SERVER = 'localhost'
SMTP_PORT = 587  # Common TLS port, adjust if your server uses a different one

# ----------------------
# Load and process data
# ----------------------
# Grab the cuesheet preparer email from the header
header_df = pd.read_csv(CSV_FILE_PATH, header=None, nrows=2)
cuesheet_preparer_email = header_df.iloc[0, 7]
print(f"Cuesheet preparer email: {cuesheet_preparer_email}")

# Define column names once for reusability
COLUMN_NAMES = [
    'CUE', 'SONG TITLE', 'USAGE', 'RUNNING TIME', 'COMPOSER',
    'COMPOSER PRO', 'COMPOSER % SHARE', 'PUBLISHER', 'PUBLISHER PRO',
    'PUBLISHER % SHARE', 'TRACK ID', 'LIBRARY', 'ARTIST', 'START TIME'
]

# Load main data, auto-convert empty/whitespace values to NaN
main_df = pd.read_csv(
    CSV_FILE_PATH,
    names=COLUMN_NAMES,
    skiprows=7,
    na_values=['', ' ']
)

# ----------------------
# Core validation logic
# ----------------------
# Flag rows that have both non-null composer and publisher
main_df['has_both_roles'] = main_df.apply(
    lambda row: pd.notna(row['COMPOSER']) and pd.notna(row['PUBLISHER']),
    axis=1
)

# Check each CUE group: does it have at least one valid row?
cue_validity = main_df.groupby('CUE')['has_both_roles'].any()

# Get list of invalid CUEs (no rows with both composer and publisher)
invalid_cues = cue_validity[~cue_validity].index.tolist()

# ----------------------
# Send alert email if invalid CUEs exist
# ----------------------
if invalid_cues:
    # Build email content with clear context
    msg = MIMEMultipart()
    msg['Subject'] = 'Invalid Cuesheet: Missing Combined Composer/Publisher Data'
    msg['From'] = EMAIL_FROM
    msg['To'] = EMAIL_TO

    email_body = f"""
    The submitted cuesheet contains invalid song IDs (CUEs) that do NOT have at least one row with both a composer and publisher:
    
    Invalid CUEs: {', '.join(map(str, invalid_cues))}
    
    Please review and correct these entries before resubmitting.
    """
    msg.attach(MIMEText(email_body, 'plain'))

    # Send email with error handling
    try:
        with smtplib.SMTP(SMTP_SERVER, SMTP_PORT) as server:
            server.starttls()
            server.login(SMTP_USERNAME, SMTP_PASSWORD)
            server.send_message(msg)
        print(f"Alert email sent successfully. Invalid CUEs: {invalid_cues}")
    except Exception as e:
        print(f"Failed to send email: {str(e)}")
else:
    print("All CUEs are valid—each has at least one row with both a composer and publisher.")

Additional Notes: #

• Naive value handling: Using na_values=['', ' '] in pd.read_csv() automatically converts empty or whitespace-only entries to NaN, which simplifies missing data checks.
• Groupby efficiency: groupby().any() is more efficient than counting rows, as it stops checking a group as soon as it finds one valid row.
• Email clarity: Including the list of invalid CUEs directly in the email helps recipients quickly identify and fix issues.
• Context managers: Using with smtplib.SMTP(...) ensures the connection is closed properly, even if an error occurs mid-process.

内容的提问来源于stack exchange，提问作者Abhi Reddy