Python正则表达式匹配金额前文本：DataFrame字段解析异常排查

Let's break down the issues with your original regex and walk through a corrected solution that handles all your test cases properly.

Key Issues in the Original Regex #

1. Incorrect Month Matching: Using [JAN|FEB|...] treats | as a literal character, so it won't match the month abbreviations correctly. You need a non-capturing group instead.
2. Date Group Overwriting: The {2} repetition on your date group means only the last date match gets stored in group 1, not both dates combined.
3. Greedy Text Matching: The (.*[\s]|.*[^\$]|.*[^-]) logic for group 3 is overly greedy and will consume up to the end of the string, making it impossible to capture the amount properly.
4. Invalid Amount Character Class: [\d|,]+ includes | as a valid character, which isn't intended—you just want digits and commas.

Corrected Solution #

Step 1: Updated Regex #

re_1 = r'((?:(?:JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)\s*\d{1,2}\s*){2})(.*?)(?:\s*([-+]?\$[\d,]+\.\d+))?$'

Let's break this down:

• (?:(?:JAN|FEB|...)\\s*\\d{1,2}\\s*){2}: Non-capturing group for a single month+date (with optional spaces), repeated twice. The outer capturing group ensures we get both dates as a single string.
• (.*?): Non-greedy match for your description text (group 3), stopping as soon as it hits an optional space followed by an amount, or the end of the string.
• (?:\\s*([-+]?\\$[\\d,]+\\.\\d+))?$: Optional non-capturing group for the amount section. It matches optional spaces, then the amount (with optional +/- sign, dollar symbol, digits, commas, and decimal), and captures the amount in group 4. The trailing ?$ makes this whole section optional for cases without an amount.

Step 2: Refined Parsing Function #

import pandas as pd
import re

def parse_values(args):
    # Use the corrected regex
    re_1 = r'((?:(?:JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)\s*\d{1,2}\s*){2})(.*?)(?:\s*([-+]?\$[\d,]+\.\d+))?$'
    # Strip whitespace from c1 to avoid edge case issues
    c1_clean = args['c1'].strip()
    m = re.match(re_1, c1_clean)
    
    if m is None:
        # Handle cases where regex doesn't match
        args['dt'] = pd.NA
        args['txt'] = pd.NA
        args['amt'] = pd.NA
        return args
    
    # Extract and clean the date pair
    args['dt'] = m.group(1).strip()
    # Extract and clean the description text
    args['txt'] = m.group(2).strip()
    # Extract amount if present, fallback to c2/c3 otherwise
    amt_match = m.group(3)
    if amt_match is not None:
        args['amt'] = amt_match
    else:
        args['amt'] = args['c2'] if pd.isnull(args['c3']) else args['c3']
    
    return args

Step 3: Test with Your Data #

tt = [
    {'c1':'OCT 7 OCT 8 HURRY CURRY THORNHILL ','c2':'$16.84'},
    {'c1':'OCT 7 OCT 8 HURRY CURRY THORNHILL','c2':'$16.84'},
    {'c1':'MAR 15 MAR 16 LOBLAWS FOODS INC - EAST YORK -$80,00,7770.70'},
    {'c1':'MAR 15 MAR 16 LOBLAWS FOODS INC - EAST YORK-$2070.70'},
    {'c1':'MAR 15 MAR 16 LOBLAWS FOODS INC - EAST YORK$2070.70'},
    {'c1':'MAR 15 MAR 16 LOBLAWS FOODS INC - EAST YORK $80,00,7770.70'}
]
t = pd.DataFrame(tt, columns=['c1','c2','c3','c4'])
t = t.apply(parse_values, axis=1)

# Check the parsed columns
print(t[['dt', 'txt', 'amt']])

Expected Output #

dt                          txt             amt
0  OCT 7 OCT 8     HURRY CURRY THORNHILL          $16.84
1  OCT 7 OCT 8     HURRY CURRY THORNHILL          $16.84
2  MAR 15 MAR 16   LOBLAWS FOODS INC - EAST YORK  -$80,00,7770.70
3  MAR 15 MAR 16   LOBLAWS FOODS INC - EAST YORK  -$2070.70
4  MAR 15 MAR 16   LOBLAWS FOODS INC - EAST YORK  $2070.70
5  MAR 15 MAR 16   LOBLAWS FOODS INC - EAST YORK  $80,00,7770.70

This solution handles all your edge cases:

• Optional spaces between dates, text, and amount
• Text containing - and $
• Cases where no amount is present in c1 (falls back to c2/c3)
• All your test data rows are parsed correctly

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