R语言使用unnest函数按正则拆分Excel列时出现重复结果问题

Hey there! Let's work through this Excel cell splitting issue you're dealing with. From what you shared, the main problem is that your regex-based splitting is causing unintended duplicates in other columns—super frustrating, right? Let's break this down step by step.

First, let's nail down the correct regex pattern for your requirement: matching lines that start with a number (including two-digit numbers with a leading plus sign) immediately followed by a period. Here's the pattern you need:

^(\+?\d+)\.\s*(.*)

• ^ Anchors the match to the start of the string (so we only catch leading numbers)
• (\+?\d+) Captures the leading number (optional plus sign, followed by one or more digits)
• \. Matches the literal period
• \s* Skips any whitespace right after the period
• (.*) Captures the rest of the cell content

Now, the column duplication issue almost always happens when your processing logic isn't properly isolating the target column, or you're reusing data structures without resetting state between rows. Below are clean implementations for two common tools to fix this:

Option 1: Python with Pandas (Most Flexible) #

This approach processes only the target column and adds new columns for the split data, avoiding accidental overwrites or duplicates in other columns:

import pandas as pd
import re

# Load your input Excel file
df = pd.read_excel("your_input_file.xlsx")

# Compile the regex pattern for efficiency
split_pattern = re.compile(r"^(\+?\d+)\.\s*(.*)")

def split_target_cell(content):
    # Handle empty cells to avoid errors
    if pd.isna(content):
        return (None, None)
    # Convert to string to handle numeric cells
    content_str = str(content)
    match = split_pattern.match(content_str)
    if match:
        # Return the extracted number and remaining content
        return (match.group(1), match.group(2))
    else:
        # Return original content if no match is found
        return (None, content_str)

# Apply the function to your target column (replace 'Target_Column' with your actual column name)
df[["Extracted_Number", "Remaining_Content"]] = df["Target_Column"].apply(
    lambda x: pd.Series(split_target_cell(x))
)

# Save the cleaned output to a new Excel file
df.to_excel("fixed_output_file.xlsx", index=False)

Key Fixes for Duplicates: #

• We only modify the target column and add new columns—no touching existing data in other columns.
• Each row is processed independently, so there's no cross-row data leakage causing repeats.
• Empty cells are handled explicitly to prevent unexpected behavior.

Option 2: Excel VBA (Directly in Excel) #

If you prefer working within Excel itself, this VBA script processes rows one by one, writing split results only to specified columns:

Sub SplitCellsWithoutDuplicates()
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "^(\+?\d+)\.\s*(.*)"
    regex.Global = False
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row  # Replace "A" with your target column letter
    
    Dim i As Long
    For i = 2 To lastRow  # Skip header row (adjust to 1 if no header)
        Dim cellVal As String
        cellVal = ws.Cells(i, "A").Value
        
        If regex.Test(cellVal) Then
            Dim matches As Object
            Set matches = regex.Execute(cellVal)
            ws.Cells(i, "B").Value = matches(0).SubMatches(0)  # Column for extracted number
            ws.Cells(i, "C").Value = matches(0).SubMatches(1)  # Column for remaining content
        Else
            ws.Cells(i, "C").Value = cellVal  # Keep original content if no match
        End If
    Next i
End Sub

Just update the column letters (A, B, C) to match your sheet's structure, and you're good to go. This script ensures only the columns you specify are modified, eliminating the loop/duplicate issue.

If you share specific snippets of your input, current output, and expected output, we can tweak this even further—but this should resolve the duplicate column problem you're seeing!

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