Python中DataFrame值匹配：基于TEST、NAME及多SEQUENCE匹配索引

Hey there! I know this has been bugging you for days, so let's break this down clearly. You've got a DataFrame with a unique MultiIndex made up of TEST, NAME, and SEQUENCE, and you need to pull out only the index tuples that match your config's specific TEST/NAME values and have a SEQUENCE in your predefined list. Here's how to do it step by step:

First, Let's Define Example Data & Config #

Let's start with sample data that mirrors your setup—this will make it easier to follow along:

import pandas as pd

# Your config (adjust these values to match your actual setup)
config = {
    "TEST": "TEST_A",
    "NAME": "NAME_X",
    "SEQUENCE": [111, 222, 333]
}

# Sample index DataFrame with MultiIndex
data = {"additional_data": [10, 20, 30, 40, 50]}
multi_index = pd.MultiIndex.from_tuples(
    [
        ("TEST_A", "NAME_X", 111),
        ("TEST_A", "NAME_X", 222),
        ("TEST_A", "NAME_Y", 111),
        ("TEST_B", "NAME_X", 222),
        ("TEST_A", "NAME_X", 444)
    ],
    names=["TEST", "NAME", "SEQUENCE"]
)
index_df = pd.DataFrame(data, index=multi_index)

Method 1: Boolean Indexing (Most Direct) #

This approach uses the MultiIndex's level values to build your filter conditions:

# Combine all three conditions into a single boolean mask
filter_mask = (
    # Match config's TEST value
    index_df.index.get_level_values("TEST") == config["TEST"]
    # Match config's NAME value
    & index_df.index.get_level_values("NAME") == config["NAME"]
    # Match any SEQUENCE in the config's list
    & index_df.index.get_level_values("SEQUENCE").isin(config["SEQUENCE"])
)

# Get the matched index tuples
matched_indexes = index_df[filter_mask].index

# If you need the indexes as a list instead of a MultiIndex object
matched_index_list = list(matched_indexes)

Method 2: Using query() (More Readable for Complex Filters) #

If you prefer a more human-readable syntax, you can temporarily reset the index to columns and use query():

# Reset index to columns to use query
temp_df = index_df.reset_index()

# Filter using the config values
filtered_df = temp_df.query(
    'TEST == @config["TEST"] and NAME == @config["NAME"] and SEQUENCE in @config["SEQUENCE"]'
)

# Convert back to the original MultiIndex format
matched_indexes = filtered_df.set_index(["TEST", "NAME", "SEQUENCE"]).index

Key Notes #

• Case Sensitivity: Make sure the names of your MultiIndex levels (TEST, NAME, SEQUENCE) exactly match the keys in your config—Pandas is case-sensitive here.
• Handling Multiple Values: If your config has multiple TEST or NAME values (e.g., config["TEST"] = ["TEST_A", "TEST_B"]), replace the == check with .isin(config["TEST"]) instead.
• Unique Index Guarantee: Since your index is a unique triple, the results will automatically be distinct matches without duplicates.

Testing either of these methods will give you exactly the index values you need—only those that align with your config's TEST, NAME, and allowed SEQUENCE values.

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