将Excel公式与Power Query数据清洗逻辑转换为Python脚本处理超大规模制表符分隔文本文件

Hey there! Since you're new to Python and need to translate your Excel/Power Query data cleaning logic for a huge 3M+ row TSV file, let's walk through this step by step. We'll use pandas—it's perfect for handling large datasets efficiently and aligns well with your existing workflow.

Required Libraries #

First, install pandas if you haven't already—it's the go-to tool for data manipulation in Python:

pip install pandas

The Cleaning Logic Breakdown #

Your Power Query workflow does three key things, which we'll replicate exactly:

1. Trim leading/trailing spaces from the Values column
2. Keep values with internal spaces as-is
3. Remove leading zeros from numeric values, while leaving non-numeric text (like those with hyphens/letters) unchanged

Full Python Script #

Here's the code optimized for large files, mirroring your Power Query logic:

import pandas as pd

def clean_values(s):
    # Step 1: Remove leading/trailing spaces (matches Excel's TRIM)
    s_trimmed = s.strip()
    
    # Step 2: If the value has internal spaces, keep it unchanged
    if ' ' in s_trimmed:
        return s_trimmed
    
    # Step 3: Try converting to numeric to strip leading zeros
    try:
        # Convert to integer (since your numeric examples are whole numbers)
        numeric_val = int(s_trimmed)
        # Convert back to string to automatically drop leading zeros
        return str(numeric_val)
    except ValueError:
        # If conversion fails (e.g., has letters/hyphens), return the trimmed value
        return s_trimmed

# Update these paths to match your files!
INPUT_FILE = "your_input_file.txt"  # Path to your tab-separated input file
OUTPUT_FILE = "cleaned_data.txt"    # Path for the cleaned output (TSV format)
CHUNK_SIZE = 100000                 # Process 100k rows at a time (adjust based on your memory)

# Process the file in chunks to avoid memory overload with 3M+ rows
first_write = True
for chunk in pd.read_csv(INPUT_FILE, sep="\t", chunksize=CHUNK_SIZE, dtype=str):
    # Apply the cleaning function to the Values column
    chunk["Values"] = chunk["Values"].apply(clean_values)
    
    # Write the chunk to the output file (preserve headers on first write)
    chunk.to_csv(
        OUTPUT_FILE,
        sep="\t",
        index=False,
        mode="w" if first_write else "a",
        header=first_write
    )
    first_write = False

print("Data cleaning complete! Check your output file.")

How It Works #

• Chunked Reading: Using chunksize lets us process the file in smaller batches, which is critical for avoiding memory crashes with 3 million rows.
• dtype=str: Ensures pandas doesn't automatically convert your string values to numbers (which would lose leading zeros before we can process them).
• Cleaning Function:
  • strip() removes leading/trailing spaces just like Excel's TRIM.
  • We check for internal spaces first and keep those values exactly as they are.
  • For numeric values, converting to int automatically drops leading zeros, then we convert back to a string.
  • Non-numeric values (like 00-563554-f or STO45642) are returned as-is after trimming.

Test with Your Sample Data #

To verify the function works, run this quick test with your example values:

test_values = [
    "000000000000056484",
    "00 564842",
    "00-563554-f",
    "STO45642",
    "45632"
]

for val in test_values:
    print(f"Original: {val} → Cleaned: {clean_values(val)}")

This will output exactly the expected results from your sample!

Notes #

• If your numeric values include decimals, modify the clean_values function to use float instead of int (you can add logic to remove trailing .0 if needed).
• For even faster performance, you could use pandas.Series.str methods instead of apply, but apply is more straightforward for this logic and still fast enough for 3M rows.

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