如何使用Python Pandas实现基于POS描述匹配的信用卡交易分类

Hey there! Let's work through how to categorize your credit card transactions using Pandas with that lookup table you have. The key here is matching the messy transaction descriptions to the merchant names in your lookup, even when there are variations in the descriptions (like different Amazon/AMZN strings or Shell Oil with location codes).

Problem Overview #

You have two DataFrames:

• df1: Contains transaction details with Description (the messy merchant string) and Amount
• df_lookup: A reference table mapping merchant names (Name) to consistent transaction categories (Category)

We need to add a Category column to df1 where each transaction gets the category from df_lookup if its Description contains the corresponding merchant Name.

Example Data #

Sample Transaction Data (df1)

Lookup Table (df_lookup)

Expected Output

Solution #

Let's break this down into actionable steps with code you can copy-paste and test.

Step 1: Set Up Sample Data (for testing)

First, let's recreate the example DataFrames so you can verify the solution works:

import pandas as pd
import numpy as np  # For the vectorized approach later

# Create transaction DataFrame
data1 = {
    'Description': ['AMAZON.COM*ajlja09ja AMZN.COM', 'AMZN Mktp US *ajlkadf', 'AMZN Prime *an9adjah', 'Shell Oil 4106541031', 'Shell Oil 4163046510'],
    'Amount': [10, 15, 20, 20, 25]
}
df1 = pd.DataFrame(data1)

# Create lookup table DataFrame
lookup_data = {
    'Name': ['AMAZON', 'AMZN', 'Shell Oil'],
    'Category': ['Amazon', 'Amazon', 'Gas']
}
df_lookup = pd.DataFrame(lookup_data)

Step 2: Method 1 - Simple apply Function (Great for Small Datasets)

This approach is straightforward and easy to read. We'll define a helper function that checks if any merchant name from the lookup exists in the transaction description, then returns the corresponding category.

# Create a list of (merchant name, category) pairs from the lookup table
lookup_pairs = list(zip(df_lookup['Name'], df_lookup['Category']))

# Helper function to find the category for a given description
def get_transaction_category(description):
    for merchant_name, category in lookup_pairs:
        if merchant_name in description:
            return category
    return 'Uncategorized'  # Fallback if no match is found

# Apply the function to create the new Category column
df1['Category'] = df1['Description'].apply(get_transaction_category)

Step 3: Method 2 - Vectorized Approach (Faster for Large Datasets)

If you have thousands of transactions, the apply method can be slow. Instead, use Pandas' vectorized string operations and numpy.select for better performance:

# Create a list of conditions: check if description contains each merchant name
conditions = [df1['Description'].str.contains(name, case=False) for name in df_lookup['Name']]
# Corresponding category choices for each condition
choices = df_lookup['Category']

# Assign categories using np.select
df1['Category'] = np.select(conditions, choices, default='Uncategorized')

Note: Added case=False to make the match case-insensitive (optional, adjust if you need exact case matching).

Verify the Result #

Run print(df1) and you'll see exactly the expected output we listed earlier. Any transaction that matches a merchant name in the lookup will get the correct category, and unmatched ones will default to 'Uncategorized' (you can change this fallback value if needed).

Quick Tips #

• If your lookup table has overlapping merchant names (e.g., "AMAZON" and "AMAZON Prime"), order the lookup table so longer/more specific names come first—this ensures the correct match takes priority.
• For case-insensitive matching, add case=False to the str.contains() call in either method.

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