如何基于价格数值匹配合并结构不同的DataFrame并修正匹配错误

Let's break down why your current approach is failing and walk through a robust solution to map the type values correctly.

Problem Recap #

You have two DataFrames:

• ABC: Contains transaction IDs, free-text price descriptions, and an unknown type that needs updating.
• XYZ: Has clean price values and corresponding types you want to map to ABC.

Your current code uses regex substring matching, but it's causing mismatches (e.g., 190.78 not matching "food", 190.77 incorrectly matching "food") and fails to align prices properly.

Why Your Current Code Fails #

1. Column Name Case Mismatch: Your code references XYZ['PRICE'] and XYZ['TYPE'], but your XYZ DataFrame uses lowercase column names (price, type). This creates an empty mapping dictionary, so no valid matches happen at all.
2. String-Based Matching Flaws: Even if column names were fixed, matching raw strings leads to issues:
   • Format differences (ABC's prices have currency symbols like Rs./DLR.; XYZ's prices have commas for thousands separators) break exact matches.
   • Regex substring matching can accidentally match partial numbers (e.g., 190.78 would be incorrectly found in 1190.78, leading to wrong mappings).

Step-by-Step Solution #

Instead of string matching, we'll convert prices to numerical values for precise, reliable matching. Here's how to do it:

1. Prepare the XYZ Mapping Dictionary #

First, clean XYZ's price column to remove commas and convert to floats, then create a numeric-to-type mapping:

import pandas as pd

# Example XYZ DataFrame setup (match your actual data)
xyz_data = {
    'price': ['190.78', '191.00', '2,000', '1,599.00'],
    'type': ['food', 'movie', 'football', 'basketball']
}
XYZ = pd.DataFrame(xyz_data)

# Clean prices and create a numeric-to-type mapping
XYZ['price_num'] = XYZ['price'].str.replace(',', '').astype(float)
price_type_map = dict(zip(XYZ['price_num'], XYZ['type']))

2. Extract Numeric Prices from ABC #

Use regex to pull out the numeric price from ABC's free-text price column, then clean and convert it to float:

# Example ABC DataFrame setup
abc_data = {
    'id': ['easdca', 'vbbngy', 'awerfa', 'zxcmo5'],
    'price': [
        'Rs.1,599.00 was trasn by you',
        'txn of INR 191.00 using',
        'Rs.190.78 credits was used by you',
        'DLR.2000 credits was used by you'
    ],
    'type': ['unknown'] * 4
}
ABC = pd.DataFrame(abc_data)

# Extract valid numeric price from free text
# Regex matches numbers with optional commas (thousands separators) and decimals
ABC['price_num'] = ABC['price'].str.extract(r'(\d{1,3}(?:,\d{3})*(?:\.\d+)?)', expand=False)
# Clean commas and convert to float for precise matching
ABC['price_num'] = ABC['price_num'].str.replace(',', '').astype(float)

3. Map the Type Values #

Now use the numeric prices to map the correct type, and preserve unknown for any unmatched values:

ABC['type'] = ABC['price_num'].map(price_type_map).fillna(ABC['type'])

Final Result #

Your updated ABC DataFrame will look like this:

id                                   price        type  price_num
0  easdca  Rs.1,599.00 was trasn by you  basketball    1599.00
1  vbbngy        txn of INR 191.00 using        movie     191.00
2  awerfa  Rs.190.78 credits was used by you       food     190.78
3  zxcmo5    DLR.2000 credits was used by you    football    2000.00

Key Improvements #

• Numeric Matching: Eliminates format-related mismatches (currency symbols, commas) for precise alignment.
• Robust Extraction: The regex ensures we only pull valid price numbers from the free text, avoiding partial matches.
• Fallback Handling: fillna keeps the original unknown type for any prices that don't exist in XYZ.

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