Python中如何将字符串列表与DataFrame进行匹配？

首先，我来帮你实现需求：把交易描述列表中的每个字符串，和DataFrame里的关键词做匹配，返回对应的分类变量（只要DataFrame的value列里有任意单词出现在交易字符串中，就返回对应的variable）。

第一步：准备可运行的数据 #

先把你提供的原始数据转换成Python可以直接使用的格式：

import pandas as pd

# 你的交易描述列表
listvalue = [
    "POS 541919XXXXXX5316 WWW PAYTM COM POS DEBIT",
    "POS 541919XXXXXX5316 HASBRO CLOTHING POS DEBIT",
    "Salary for the month of April 2018"
]

# 构造分类映射的DataFrame（根据你提供的内容整理）
category_data = [
    ["ATM", "Cash withdrawal"],
    ["Auto & Fuel", "fuel"],
    ["Expense", "fees"],
    ["Expense", "goods"],
    ["Expense", "stationery"],
    ["Expense", "purchase"],
    ["Expense", "material"],
    ["Expense", "telephone"],
    ["Food/Restaurent", "food"],
    ["Food/Restaurent", "catering"],
    ["General", "others"],
    ["Groceries", "big bazar"],
    ["Income", "salary"],
    ["Income", "deposit"],
    ["Income", "rewards"],
    ["Medical", "dr"],
    ["Medical", "doctor"],
    ["Medical", "dr."],
    ["Medical", "nursing"],
    ["Medical", "pharmacist"],
    ["Medical", "physician"],
    ["Medical", "hospital"],
    ["Medical", "medicine"],
    ["Mobile recharge", "airtel"],
    ["Payment", "tranfer"],
    ["Payment", "payment"],
    ["Shopping", "cloths"],
    ["Shopping", "clothing"],
    ["Travel", "travel"]
]

df_categories = pd.DataFrame(category_data, columns=["variable", "value"])

第二步：实现匹配逻辑 #

写一个简单的匹配函数，这里用不区分大小写的匹配规则，避免因为大小写差异导致匹配失败：

def get_transaction_category(transaction_str):
    # 遍历分类映射表，检查关键词是否存在于交易描述中
    for _, row in df_categories.iterrows():
        if row["value"].lower() in transaction_str.lower():
            return row["variable"]
    # 如果没有找到匹配的关键词，返回默认分类
    return "Uncategorized"

# 对所有交易描述应用匹配函数
category_results = [get_transaction_category(desc) for desc in listvalue]

第三步：查看匹配结果 #

运行代码后，你可以打印出每个交易对应的分类：

for desc, category in zip(listvalue, category_results):
    print(f"Transaction: {desc}\nCategory: {category}\n")

输出结果如下：

Transaction: POS 541919XXXXXX5316 WWW PAYTM COM POS DEBIT
Category: Uncategorized

Transaction: POS 541919XXXXXX5316 HASBRO CLOTHING POS DEBIT
Category: Shopping

Transaction: Salary for the month of April 2018
Category: Income

可选优化：处理多匹配场景 #

如果一个交易描述可能匹配多个关键词，你可以修改函数来返回所有匹配的分类：

def get_all_matching_categories(transaction_str):
    matching_categories = [
        row["variable"] 
        for _, row in df_categories.iterrows() 
        if row["value"].lower() in transaction_str.lower()
    ]
    return matching_categories if matching_categories else ["Uncategorized"]

# 应用优化后的函数
multi_match_results = [get_all_matching_categories(desc) for desc in listvalue]

这样如果有多个匹配项，会返回对应的分类列表。

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