Python实现DataFrame中相似银行名称的分组与数量统计

开发者阿泽

2026-5-22

Got it, let's tackle this problem step by step. You've got a 30k-row DataFrame of bank names where most entries are just regional variations of core brands, and you need to group them into their base parent brands (like ROYAL BANK and BARCLAYS) with accurate counts. Here's a practical, scalable solution tailored to your needs:

Step 1: Define Core Brand Match Logic

First, we'll target the core brand keywords you already identified (ROYAL BANK and BARCLAYS) and build a case-insensitive matching system to catch all their regional variants. For unrecognized brands, we'll flag them as "OTHER" for later refinement.

Step 2: Scalable Pandas Implementation

For a 30k-row dataset, vectorized operations are far faster than row-wise apply(). Here's the code:

import pandas as pd
import numpy as np

# Replace this with your actual DataFrame
sample_banks = [
    "ROYAL BANK OF CANADA", "THE ROYAL BANK OF SCOTLAND PLC",
    "ROYAL BANK OF AUSTRALIA", "ROYAL BANK NEW ZEALAND",
    "ROYAL BANK CARIBBEAN LIMITED", "ROYAL BANK OF IRELAND",
    "ROYAL BANK GROUP", "ROYAL BANK SERVICES",
    "ROYAL BANK CORPORATION", "ROYAL BANK HOLDINGS",
    "ROYAL BANK USA", "ROYAL BANK EUROPE",
    "ROYAL BANK ASIA",  # Total 13 ROYAL BANK entries
    "BARCLAYS UK", "BARCLAYS PLC", "BARCLAYS AFRICA",
    "BARCLAYS INVESTMENT BANK", "BARCLAYS CARD SERVICES",
    "BARCLAYS HOLDINGS", "BARCLAYS INTERNATIONAL"  # Total 7 BARCLAYS entries
]
df = pd.DataFrame({"bank_name": sample_banks})

# Define brand match conditions (case-insensitive)
brand_conditions = [
    df["bank_name"].str.upper().str.contains("ROYAL BANK"),
    df["bank_name"].str.upper().str.contains("BARCLAYS")
]
brand_labels = ["ROYAL BANK", "BARCLAYS"]

# Assign brand groups
df["brand_group"] = np.select(brand_conditions, brand_labels, default="OTHER")

# Get final count per group
group_counts = df["brand_group"].value_counts().reset_index()
group_counts.columns = ["Brand", "Count"]

print(group_counts)

Step 3: Expected Output

Running this code will give you exactly the grouping you're looking for:

Brand  Count
0  ROYAL BANK     13
1    BARCLAYS      7

Since you don't know all bank names upfront, you can:

Check the "OTHER" group entries after the first run
Extract new core brand keywords (e.g., "HSBC", "CITIBANK") from those entries
Add new conditions and labels to the brand_conditions and brand_labels lists
Re-run the code to expand your grouping

This approach is efficient enough to handle your 30k-row dataset without performance issues, and it's easy to extend as you identify more core brands.

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