GDPR合规需求：优化大Excel/CSV文件特定字符串识别效率

First off, the main bottleneck in your current approach is almost certainly loading entire large spreadsheets into memory at once—this eats up system resources and drags down processing speed. Below are practical, optimized solutions tailored to handle big files efficiently, even when you don’t know which columns contain your target strings.

Key Optimizations to Implement #

• Chunked File Reading: Process spreadsheets in small, manageable chunks instead of loading everything at once.
• Early Termination: Stop scanning a file as soon as you find the target string (no need to waste time checking every row if you already have a match).
• Vectorized Operations: Use library functions optimized for speed instead of manual cell-by-cell loops.
• Parallel Processing: Scan multiple files simultaneously to cut down total runtime when dealing with a large directory of spreadsheets.

Optimized Code for Excel Files (xlsx/xls) #

We’ll use pandas with chunking paired with openpyxl (for modern xlsx files) or xlrd (for older xls files). This approach reads the file in chunks and leverages fast vectorized checks to find matches quickly.

import pandas as pd
import os

def scan_excel_for_string(file_path, target_strings):
    # Adjust chunk size based on your available memory (1000 rows is a safe starting point)
    chunk_size = 1000
    
    # Iterate over chunks to avoid loading the entire file into memory
    for chunk in pd.read_excel(file_path, chunksize=chunk_size, engine='openpyxl'):
        # Convert all cells to strings to avoid type mismatch errors
        chunk_str = chunk.astype(str)
        # Check if any target string exists in the chunk
        for target in target_strings:
            if (chunk_str == target).any().any():
                return True  # Match found—exit early
    return False

def list_files_with_target(dir_path, target_strings):
    matching_files = []
    for root, dirs, files in os.walk(dir_path):
        for file in files:
            if file.endswith(('.xlsx', '.xls')):
                file_path = os.path.join(root, file)
                print(f"Scanning {file_path}...")
                if scan_excel_for_string(file_path, target_strings):
                    matching_files.append(file_path)
    return matching_files

# Usage example
gdpr_targets = ["user_email@example.com", "EU-123456789"]
matches = list_files_with_target("/path/to/your/spreadsheets", gdpr_targets)
print("Files containing target strings:", matches)

Why this works: #

• pd.read_excel(..., chunksize=chunk_size) loads only a subset of rows at a time, keeping memory usage low.
• (chunk_str == target).any().any() uses pandas’ optimized vectorized operations to check the entire chunk in one go—far faster than looping through each cell manually.
• Early termination means we stop processing a file as soon as we find a match, saving valuable time.

Optimized Code for CSV Files #

For CSV files, the built-in csv module is lightweight and efficient. We’ll read rows one by one and check each cell, exiting early when a match is found.

import csv
import os

def scan_csv_for_string(file_path, target_strings):
    with open(file_path, 'r', encoding='utf-8') as f:
        reader = csv.reader(f)
        for row in reader:
            for cell in row:
                # Use this line for exact matches
                if any(target == cell.strip() for target in target_strings):
                    return True
                # Uncomment below for substring matches (e.g., target is part of the cell content)
                # if any(target in cell.strip() for target in target_strings):
                #     return True
    return False

def list_files_with_target(dir_path, target_strings):
    matching_files = []
    for root, dirs, files in os.walk(dir_path):
        for file in files:
            if file.endswith('.csv'):
                file_path = os.path.join(root, file)
                print(f"Scanning {file_path}...")
                if scan_csv_for_string(file_path, target_strings):
                    matching_files.append(file_path)
    return matching_files

# Usage example
gdpr_targets = ["user_email@example.com", "EU-123456789"]
matches = list_files_with_target("/path/to/your/spreadsheets", gdpr_targets)
print("Files containing target strings:", matches)

Speed Up Further with Parallel Processing #

If you have hundreds of files to scan, processing them one by one is slow. Use Python’s multiprocessing module to scan multiple files at the same time, leveraging all available CPU cores:

from multiprocessing import Pool
import os

# Reuse the scan_excel_for_string and scan_csv_for_string functions from above

def process_file(file_tuple):
    file_path, targets = file_tuple
    if file_path.endswith(('.xlsx', '.xls')):
        return file_path if scan_excel_for_string(file_path, targets) else None
    elif file_path.endswith('.csv'):
        return file_path if scan_csv_for_string(file_path, targets) else None
    return None

def list_files_with_target_parallel(dir_path, target_strings):
    all_files = []
    for root, dirs, files in os.walk(dir_path):
        for file in files:
            if file.endswith(('.xlsx', '.xls', '.csv')):
                all_files.append((os.path.join(root, file), target_strings))
    
    # Use all available CPU cores to process files in parallel
    with Pool() as pool:
        results = pool.map(process_file, all_files)
    
    # Filter out non-matching files (marked as None)
    matching_files = [f for f in results if f is not None]
    return matching_files

# Usage example
gdpr_targets = ["user_email@example.com", "EU-123456789"]
matches = list_files_with_target_parallel("/path/to/your/spreadsheets", gdpr_targets)
print("Files containing target strings:", matches)

Additional Pro Tips #

• Filter File Types: Only scan actual spreadsheet files (skip images, PDFs, docs) to avoid unnecessary work.
• Adjust Chunk Size: If you have plenty of memory, increase chunk_size (e.g., 5000) to reduce the number of iterations. If memory is tight, decrease it.
• Case Insensitivity: To match regardless of case, convert both cell content and targets to lowercase: cell.strip().lower() and target.lower().
• Avoid Full File Load: Never use pd.read_excel() without chunksize for large files—it will hog memory and slow down your system.

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