Pandas按Region分组统计Rep并新增条件列问题求助

Looks like you're trying to group your sales data by Region, count Rep occurrences, and add a custom label based on how many unique Reps exist in each Region. Let's break down how to get your desired output step by step.

First, let's align on the labeling rules we need to implement:

• If a Region has only one unique Rep, label it with {count}-same (e.g., East has only Jones with 2 entries → "2-same")
• If a Region has more than 3 unique Reps, combine all Reps into a single entry with the total region record count and label ">3 difference"
• If a Region has 2-3 unique Reps:
  • Label entries with a count greater than 1 as {count}-different
  • Label entries with a count of 1 as "-"

Here's the complete code to achieve this:

import pandas as pd

# Load your data (update excel_path to match your file location)
df1 = pd.read_excel(excel_path, sheet_name='SalesOrders', index_col=0)

# Step 1: Count how many times each Rep appears in each Region
rep_counts = df1.groupby(['Region', 'Rep']).size().reset_index(name='Count')

# Step 2: Calculate key summary stats per Region
region_summary = df1.groupby('Region').agg(
    total_count=('Rep', 'size'),  # Total entries in the Region
    rep_unique_count=('Rep', 'nunique'),  # Number of unique Reps
    all_reps=('Rep', lambda x: '/'.join(x.unique()))  # Join all Rep names with slashes
).reset_index()

# Step 3: Merge Rep counts with Region summary data
df_combined = rep_counts.merge(region_summary, on='Region')

# Step 4: Process each category of Region
# Category 1: Regions with only 1 unique Rep
class1 = df_combined[df_combined['rep_unique_count'] == 1].copy()
class1['same/diff'] = class1['Count'].apply(lambda x: f"{x}-same")
class1 = class1[['Region', 'Rep', 'Count', 'same/diff']]

# Category 2: Regions with more than 3 unique Reps (merge into one row)
class2 = df_combined[df_combined['rep_unique_count'] > 3].groupby('Region').agg(
    Rep=('all_reps', 'first'),
    Count=('total_count', 'first'),
    **{'same/diff': lambda x: ">3 difference"}
).reset_index()

# Category 3: Regions with 2-3 unique Reps (keep individual Rep rows)
class3 = df_combined[(df_combined['rep_unique_count'] >= 2) & (df_combined['rep_unique_count'] <= 3)].copy()
class3['same/diff'] = class3.apply(
    lambda row: f"{row['Count']}-different" if row['Count'] > 1 else "-",
    axis=1
)
class3 = class3[['Region', 'Rep', 'Count', 'same/diff']]

# Step 5: Combine all categories and sort the final result
final_df = pd.concat([class1, class2, class3], ignore_index=True)
final_df = final_df.sort_values('Region').reset_index(drop=True)

# Print the output
print(final_df)

Output Verification #

For your sample input data, this code will produce:

Note: Your expected result shows West > Sorvino with Count 2—this likely a typo in your input data (your sample input only has one Sorvino entry in West). If your actual data has two entries for Sorvino in West, the code will automatically update the count to 2 and label to "2-different".

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