海量数据多条件最佳匹配：按Family&Pivot Code筛选最大Quantity且含Reference1的行

Hey there! Let's tackle this problem step by step since you're dealing with a large dataset (5k+ rows) and need a solid matching rule for your Family + Pivot Code groups. Below is a clear, actionable solution tailored to your needs:

We'll structure the rules in priority order to cover all edge cases you mentioned (and some common ones you might encounter):

核心筛选优先级 #

• 第一优先级（理想匹配）：For each Family + Pivot Code combination, filter rows that meet both:
  1. Has the maximum Quantity value in the group (if multiple rows tie for max Quantity, keep all that meet the second condition)
  2. Has a valid Price 1 corresponding to Reference 1 (define "valid" based on your data—e.g., non-null, greater than 0, or matching a specific format)
• 第二优先级（fallback when no ideal match exists）：If no rows in the group have a valid Price 1 + Reference 1, filter the row(s) with the maximum Quantity value in the group (regardless of Reference/Price column status)
• 兜底规则：If a Family + Pivot Code combination has no corresponding rows at all, mark it as No Match or leave blank based on your business needs

高效实现示例（Python Pandas，适合海量数据） #

Since you're working with 5k+ rows, Pandas is perfect for fast, vectorized operations (no slow loops!). Here's reusable code you can adapt:

import pandas as pd

# Load your dataset (adjust file path/format as needed)
df = pd.read_csv("your_large_dataset.csv")

# Step 1: Define what counts as a valid Price 1 (customize this to your data rules)
df["is_valid_price1"] = df["Price 1"].notna() & (df["Price 1"] > 0)

# Step 2: Flag rows with the maximum Quantity in their Family+Pivot Code group
group_max_qty = df.groupby(["Family", "Pivot Code"])["Quantity"].transform("max")
df["is_max_qty"] = df["Quantity"] == group_max_qty

# Step 3: Get all first-priority matches
priority1_matches = df[(df["is_max_qty"] & df["is_valid_price1"])].copy()

# Step 4: Find groups with no first-priority matches, then get second-priority matches
groups_without_priority1 = df.groupby(["Family", "Pivot Code"]).filter(
    lambda group: not (group["is_max_qty"] & group["is_valid_price1"]).any()
)
priority2_matches = groups_without_priority1[groups_without_priority1["is_max_qty"]].copy()

# Step 5: Combine results and clean up temporary columns
final_matches = pd.concat([priority1_matches, priority2_matches])
final_matches = final_matches.drop(columns=["is_valid_price1", "is_max_qty"]).reset_index(drop=True)

Key Notes #

• If multiple rows in a group tie for max Quantity and have valid Price 1, the code keeps all of them. If you need to narrow this down further, add an extra filter (e.g., pick the row with the smallest Reference 1 or latest date, if you have a date column)
• Adjust the is_valid_price1 logic to match your actual data requirements—for example, if Reference 1 must also be non-null, add df["Reference 1"].notna() to the condition
• This approach is optimized for large datasets: using transform and groupby.filter avoids slow row-wise loops, so it’ll handle 5k+ rows (or even 100k+) quickly

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