基于搜索结果创建列并替换值：Pandas代码优化与分类规则外置方案问询

Let's tackle your two questions one by one with practical, cleaner solutions:

First: A More Elegant Way to Create the Category Column #

Your current approach uses a temporary value and overwrites it step-by-step, which works but gets messy as you add more categories. Here are two better alternatives:

Option 1: Use numpy.select (Best for Multiple Rules)

This lets you define all your conditions and corresponding categories in one go, no temporary value needed:

#!/usr/bin/env python3
import pandas as pd
import numpy as np

example_dataset = {
 "Date" : ['01 Mar 2022', '02 Apr 2022', '10 Apr 2022', '15 Apr 2022'],
 "Transaction Type" : ['Contactless payment', 'Payment to', 'Contactless payment', 'Contactless payment'],
 "Description" : ['Tesco Store', 'Dentist', 'Cinema', 'Sainsburys'],
 "Amount" : ['156.00', '55', '21.50', '176.10']
}

df = pd.DataFrame(example_dataset)
df['Date'] = pd.to_datetime(df['Date'], format='%d %b %Y')

# Define conditions and their matching categories
conditions = [
    df['Description'].str.contains('Tesco|Sainsbury'),
    df['Description'].str.contains('Dentist|Cinema')
]
category_labels = ['Groceries', 'Stuff']

# Assign categories (use 'Other' as default for unmatched entries)
df['Category'] = np.select(conditions, category_labels, default='Other')

print(df)

This is scalable—just add new entries to conditions and category_labels as you need more rules.

Option 2: Use str.replace with Regex Mapping

If you prefer a more concise dictionary-based approach:

# Define regex patterns mapped to categories
category_map = {
    r'.*(Tesco|Sainsbury).*': 'Groceries',
    r'.*(Dentist|Cinema).*': 'Stuff'
}

# Replace descriptions with categories, fill unmatched with 'Other'
df['Category'] = df['Description'].replace(category_map, regex=True).fillna('Other')

Second: Store Rules in an External File (For Easier Maintenance) #

Absolutely! Storing your keyword-category mappings in an external file lets you update rules without touching your Python code. Here's how to implement this with a JSON file (simple and human-readable):

Step 1: Create a category_rules.json file

{
  "Groceries": ["Tesco", "Sainsbury"],
  "Stuff": ["Dentist", "Cinema"]
}

Step 2: Load the Rules and Apply Them in Python

#!/usr/bin/env python3
import pandas as pd
import numpy as np
import json

# Load the external rules file
with open('category_rules.json', 'r') as f:
    category_rules = json.load(f)

# Generate conditions and category labels dynamically
conditions = []
category_labels = []
for category, keywords in category_rules.items():
    # Join keywords into a regex pattern
    regex_pattern = '|'.join(keywords)
    conditions.append(df['Description'].str.contains(regex_pattern, case=False))  # case-insensitive match
    category_labels.append(category)

# Your existing data loading code
example_dataset = {
 "Date" : ['01 Mar 2022', '02 Apr 2022', '10 Apr 2022', '15 Apr 2022'],
 "Transaction Type" : ['Contactless payment', 'Payment to', 'Contactless payment', 'Contactless payment'],
 "Description" : ['Tesco Store', 'Dentist', 'Cinema', 'Sainsburys'],
 "Amount" : ['156.00', '55', '21.50', '176.10']
}

df = pd.DataFrame(example_dataset)
df['Date'] = pd.to_datetime(df['Date'], format='%d %b %Y')

# Assign categories
df['Category'] = np.select(conditions, category_labels, default='Other')

print(df)

Alternative: Use a CSV File (If You Prefer Spreadsheet-Like Editing)

If you or your team prefer working with CSV instead of JSON, create a category_rules.csv file:

Groceries,Tesco
Groceries,Sainsbury
Stuff,Dentist
Stuff,Cinema

Then load it with pandas:

import pandas as pd

# Load and group CSV rules into a dictionary
rules_df = pd.read_csv('category_rules.csv', header=None, names=['Category', 'Keyword'])
category_rules = rules_df.groupby('Category')['Keyword'].apply(list).to_dict()

# Then proceed with the same condition generation as above

Now you can add new categories or keywords just by editing the external file—no code changes required!

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