基于Pandas DataFrame的行业/组级Top-Bottom配对生成与阈值筛选

Got it, let's tackle this problem step by step. Here's how you can generate top-bottom score pairs per industry/group in a Pandas DataFrame, then filter only those pairs where the score difference exceeds your specified threshold.

Step 1: Set Up Sample Data #

First, let's create a sample DataFrame to work with (you can replace this with your actual data):

import pandas as pd

# Example dataset with Industry, Company, and Score columns
data = {
    'Industry': ['Energy', 'Energy', 'Energy', 'Energy', 'Tech', 'Tech', 'Tech', 'Healthcare', 'Healthcare'],
    'Company': ['JKL', 'BCA', 'XYZ', 'ABC', 'GHI', 'DEF', 'MNO', 'PQR', 'STU'],
    'Score': [95, 88, 72, 65, 92, 85, 78, 90, 60]
}
df = pd.DataFrame(data)

Step 2: Define a Function to Create & Filter Pairs #

We'll write a custom function that handles each industry group: it sorts scores, pairs top performers with bottom performers, calculates the score difference, and filters pairs that meet your threshold.

def generate_filtered_pairs(group, threshold=15):
    # Sort the group by Score in descending order
    sorted_group = group.sort_values('Score', ascending=False).reset_index(drop=True)
    
    # Calculate how many valid pairs we can form (ignore middle element if odd count)
    num_pairs = len(sorted_group) // 2
    
    # Extract top N and bottom N entries; reverse bottom to pair top1 with bottom1, etc.
    top_entries = sorted_group.iloc[:num_pairs].reset_index(drop=True)
    bottom_entries = sorted_group.iloc[-num_pairs:].sort_values('Score', ascending=True).reset_index(drop=True)
    
    # Merge top and bottom into pairs, add suffixes to distinguish columns
    pairs_df = pd.concat([
        top_entries.add_suffix('_top'),
        bottom_entries.add_suffix('_bottom')
    ], axis=1)
    
    # Calculate the score difference between each pair
    pairs_df['Score_Difference'] = pairs_df['Score_top'] - pairs_df['Score_bottom']
    
    # Filter pairs where the difference exceeds your threshold
    return pairs_df[pairs_df['Score_Difference'] > threshold]

Step 3: Apply the Function to Each Industry Group #

Use groupby to apply our function across every industry, then clean up the result:

# Apply the function to each industry group
final_result = df.groupby('Industry').apply(generate_filtered_pairs).reset_index(drop=True)

# Print the result
print(final_result)

What the Output Looks Like #

Running this code will give you a DataFrame like this:

Industry_top Company_top  Score_top Industry_bottom Company_bottom  Score_bottom  Score_Difference
0          Energy         JKL         95          Energy            ABC            65                30
1          Energy         BCA         88          Energy            XYZ            72                16
2     Healthcare         PQR         90     Healthcare            STU            60                30

Notice that the Tech group didn't make the cut: its only valid pair (GHI with MNO) had a score difference of 14, which is below our threshold of 15.

Key Notes #

• Handling Odd Group Sizes: If an industry has an odd number of entries, the middle entry (with the median score) will be ignored since we can't form a valid top-bottom pair for it.
• Adjust Threshold: Simply change the threshold parameter in generate_filtered_pairs to match your needs (e.g., threshold=20 to only keep pairs with a 20+ score gap).
• Custom Sorting: If you need to sort by something other than score (e.g., date), modify the sort_values line in the function.

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