如何用Python的Pandas遍历多工作表Excel文件并自动提取邮箱？

Got it, I’ve put together a complete Python solution using pandas that handles all your sheets (and even multiple Excel files if needed) to extract email columns. Here’s how it works:

Step-by-Step Code Implementation #

We’ll use pandas to load Excel files, plus regex to detect email patterns—this covers cases where column names might not explicitly mention "email" but contain valid email values.

import pandas as pd
import re
from pathlib import Path

# Regex pattern to match valid email addresses
EMAIL_PATTERN = r'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$'

def extract_email_columns(df):
    """Pull out columns that contain mostly email addresses from a DataFrame"""
    email_columns = []
    
    for col in df.columns:
        # Skip non-text columns (like numbers or dates)
        if df[col].dtype == 'object':
            # Calculate percentage of non-null values that are valid emails
            non_null_vals = df[col].dropna()
            if len(non_null_vals) == 0:
                continue
            email_ratio = non_null_vals.apply(lambda x: bool(re.fullmatch(EMAIL_PATTERN, str(x)))).sum() / len(non_null_vals)
            
            # Keep columns where at least 50% of values are emails (adjust threshold as needed)
            if email_ratio > 0.5:
                email_columns.append(col)
    
    return df[email_columns] if email_columns else None

def process_excel_file(file_path):
    """Process every sheet in a single Excel file and extract email columns"""
    sheet_results = {}
    xls = pd.ExcelFile(file_path)
    
    for sheet_name in xls.sheet_names:
        print(f"Processing sheet: {sheet_name} in {file_path.name}")
        df = xls.parse(sheet_name)
        email_data = extract_email_columns(df)
        
        if email_data is not None and not email_data.empty:
            sheet_results[f"{file_path.stem}_{sheet_name}"] = email_data
        else:
            print(f"No email columns found in sheet: {sheet_name}")
    
    return sheet_results

def process_directory(directory_path):
    """Batch-process all .xlsx files in a folder"""
    all_results = {}
    excel_files = list(Path(directory_path).glob("*.xlsx"))
    
    for file in excel_files:
        file_results = process_excel_file(file)
        all_results.update(file_results)
    
    return all_results

# ----------------------
# Example Usage
# ----------------------
# Process a single Excel file
single_file_output = process_excel_file(Path("my_file.xlsx"))

# Save extracted emails to a new Excel file (each original sheet gets its own tab)
with pd.ExcelWriter("extracted_emails.xlsx") as writer:
    for sheet_name, df in single_file_output.items():
        df.to_excel(writer, sheet_name=sheet_name, index=False)

# Uncomment below to process all Excel files in a folder
# folder_output = process_directory(Path("./your_excel_folder"))
# with pd.ExcelWriter("all_extracted_emails.xlsx") as writer:
#     # Excel limits sheet names to 31 characters, so truncate if needed
#     for sheet_name, df in folder_output.items():
#         df.to_excel(writer, sheet_name=sheet_name[:31], index=False)

Key Features Explained #

• Smart Email Detection: Uses regex to identify columns where most values are valid emails (you can tweak the 50% threshold in extract_email_columns to be stricter or more lenient).
• Full Sheet Traversal: Automatically loops through every sheet in your Excel file—no manual sheet name input needed.
• Batch File Support: The process_directory function lets you process all .xlsx files in a folder at once.
• Organized Output: Saves extracted emails into a new Excel file, with each original sheet’s data stored in a separate tab for easy access.

Customization Tips #

• If you know email column names: Simplify the extraction function to target columns with specific names (like "Email" or "Contact"):

  def extract_email_columns(df):
      email_cols = [col for col in df.columns if 'email' in col.lower()]
      return df[email_cols] if email_cols else None
  

• Adjust regex pattern: Modify EMAIL_PATTERN if you need to match specific email formats (e.g., only internal company domains).
• Change output format: Swap the Excel writer code to save as CSV files instead if that fits your workflow better.

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