正则表达式还是原生Python？补全CSV缺失值适配Pandas导入

Great question! Let's walk through all feasible approaches to solve your problem—from regex tricks to native Python, and even a pandas workaround you might not have considered.

First, Clarify the Core Rules #

To recap the key constraints we're working with:

• Columns are separated by two or more spaces (we'll handle single spaces too, since your sample uses them)
• There are exactly two uppercase letter columns in each line
• Between these two uppercase columns, we need exactly 4 numeric values; if there are fewer, fill the gaps with na

1. Regex Approach (With Callback Function) #

Regex can handle this neatly using a substitution callback, which lets us dynamically calculate how many nas to insert. Here's how:

import re

orig = [
    "a1 2.3 ABC 4 DEFG 567 b890",
    "a2 3.0 HI 4 5 JKL 67 c65",
    "b1 1.2 MNOP 3 45 67 89 QR 987 d64 e112"
]

def fill_missing_nas(match):
    # Extract the three groups: first uppercase column, middle numeric part, second uppercase column
    upper_col1 = match.group(1)
    numeric_section = match.group(2)
    upper_col2 = match.group(3)
    
    # Split the numeric section into individual values (ignore extra spaces)
    nums = [val for val in numeric_section.split() if val]
    # Calculate how many NAs we need to add
    missing_count = 4 - len(nums)
    # Build the new numeric section with NAs
    updated_numerics = ' '.join(nums + ['na'] * missing_count)
    
    # Return the replaced segment
    return f"{upper_col1} {updated_numerics} {upper_col2}"

# Regex pattern to match two uppercase columns with numeric values in between
# Handles multiple spaces between values/columns
pattern = re.compile(r'([A-Z]+)\s+([\d\.\s]+)\s+([A-Z]+)')
corr = [pattern.sub(fill_missing_nas, line) for line in orig]

print(corr)

How It Works: #

• The regex captures the two uppercase columns and the numeric content between them
• The callback function splits the numeric content, counts how many values are present, and adds enough nas to reach 4
• We substitute the original numeric section with the updated one

2. Native Python Approach (No Regex) #

If regex feels intimidating, a straightforward native Python solution is often easier to debug and maintain:

orig = [
    "a1 2.3 ABC 4 DEFG 567 b890",
    "a2 3.0 HI 4 5 JKL 67 c65",
    "b1 1.2 MNOP 3 45 67 89 QR 987 d64 e112"
]

def process_single_line(line):
    # Split the line into individual parts (handles any number of spaces)
    parts = line.split()
    # Find the indices of the two uppercase columns
    upper_indices = [idx for idx, part in enumerate(parts) if part.isupper()]
    col1_idx, col2_idx = upper_indices[0], upper_indices[1]
    
    # Extract existing numeric values between the two uppercase columns
    existing_nums = parts[col1_idx + 1 : col2_idx]
    # Calculate missing NAs and build the full numeric list
    full_nums = existing_nums + ['na'] * (4 - len(existing_nums))
    
    # Reconstruct the line with filled NAs
    new_parts = parts[:col1_idx + 1] + full_nums + parts[col2_idx:]
    return ' '.join(new_parts)

corr = [process_single_line(line) for line in orig]
print(corr)

Why This Is Great: #

• It's explicit: you can see exactly where we're extracting values, counting gaps, and rebuilding the line
• No regex syntax to memorize—easy to tweak if your rules change (e.g., more than two uppercase columns later)

3. Pandas Workaround #

You mentioned struggling with pandas' CSV reader, but we can pre-process the lines first to align columns, then import into pandas smoothly:

import pandas as pd
from io import StringIO

orig = [
    "a1 2.3 ABC 4 DEFG 567 b890",
    "a2 3.0 HI 4 5 JKL 67 c65",
    "b1 1.2 MNOP 3 45 67 89 QR 987 d64 e112"
]

# First, process all lines to fill NAs (using the native Python function above)
processed_lines = [process_single_line(line) for line in orig]

# Read the processed lines into pandas
df = pd.read_csv(StringIO('\n'.join(processed_lines)), sep='\s+', header=None)

# Optional: Rename columns for clarity (adjust based on your actual data structure)
first_line_parts = processed_lines[0].split()
upper_indices = [idx for idx, part in enumerate(first_line_parts) if part.isupper()]
col_names = (
    first_line_parts[:upper_indices[0]+1] +
    [f'numeric_{i+1}' for i in range(4)] +
    first_line_parts[upper_indices[1]+1:]
)
df.columns = col_names

print(df)

Key Notes: #

• We pre-process the lines to fill NAs first, so pandas can read the data with aligned columns
• Using sep='\s+' tells pandas to split on any number of spaces, which handles your column separator rule
• You can easily rename columns to match your actual data schema

Which Approach Should You Choose? #

• Regex: Best if you're comfortable with regex and want a concise solution
• Native Python: Best for readability and ease of debugging, especially if you're new to regex
• Pandas: Best if you need to immediately analyze the data after filling gaps

内容的提问来源于stack exchange，提问作者Mr. T