如何在Pandas中基于薪资区间进行匹配

Hey there! The problem with your current merge code is that it’s only looking for exact matches across Language, SalaryBegin, and SalaryEnd—but your requirement is to check if a salary range from table3 is fully contained within a range from table1, plus matching the language. Let’s fix this step by step.

Step 1: Set Up Example Data #

First, let’s replicate your sample tables in Pandas (in case you need to test the code):

import pandas as pd

# Table 1 (your table1)
table1 = pd.DataFrame({
    "Name": ["Tom", "Jeff", "Hideo", "TomoHiro"],
    "Language": ["C+=", "C--", "JAVA", "RATCHET"],
    "SalaryBegin": [20, 21, 22, 19],
    "SalaryEnd": [30, 32, 29, 20]
})

# Table 3 (your table3)
table3 = pd.DataFrame({
    "Name": ["Tom", "jeff", "Hideo"],
    "Language": ["python", "python", "JAVA"],
    "SalaryBegin": [26, 22, 23],
    "SalaryEnd": [27, 23, 26]
})

Step 2: Fix the Matching Logic #

We need to:

1. Match rows by Language (first standardize case to avoid mismatches like "java" vs "JAVA")
2. Filter rows where table3's salary range is fully contained within table1's range

Option 1: Merge + Boolean Filter #

# Standardize language case to ensure matches are case-insensitive
table1["Language"] = table1["Language"].str.upper()
table3["Language"] = table3["Language"].str.upper()

# Merge tables on matching Language first
merged = pd.merge(table3, table1, on="Language", suffixes=("_table3", "_table1"))

# Filter for rows where table3's range is inside table1's range
matched_results = merged[
    (merged["SalaryBegin_table3"] >= merged["SalaryBegin_table1"]) &
    (merged["SalaryEnd_table3"] <= merged["SalaryEnd_table1"])
]

# Print the result
print(matched_results)

Option 2: Use query() for Cleaner Filtering #

If you prefer more readable syntax, replace the filter step with query():

# Same language standardization as above
table1["Language"] = table1["Language"].str.upper()
table3["Language"] = table3["Language"].str.upper()

merged = pd.merge(table3, table1, on="Language", suffixes=("_table3", "_table1"))
matched_results = merged.query(
    "SalaryBegin_table3 >= SalaryBegin_table1 and SalaryEnd_table3 <= SalaryEnd_table1"
)

Expected Output #

Running either option will return the row you expected (Hideo's matching entry):

Name Language  SalaryBegin_table3  SalaryEnd_table3 Name_table1  SalaryBegin_table1  SalaryEnd_table1
2  Hideo     JAVA                  23                26       Hideo                  22                29

Why Your Original Code Failed #

Your initial merge was looking for exact matches on all three columns (Language, SalaryBegin, SalaryEnd). Since Hideo's salary ranges aren't identical (they're nested), this approach couldn't catch the match. By merging first on language and then filtering for range containment, we get the behavior you need.

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