Pandas按营收筛选Top N团队后分组及多列保留问题

First, let's fix the empty DataFrame issue since that's blocking everything else. Then we'll tackle your two core requirements.

1. Fixing the Empty DataFrame After Reading Excel #

Your current read_excel code sets index_col='Date', but your sample Excel doesn’t include a Date column. If your actual sheet lacks this column too, that’s likely why you’re getting an empty DataFrame—Pandas tries to use a non-existent column as the index, causing unexpected behavior. Also, wrapping the result in pd.DataFrame() is redundant since pd.read_excel() already returns a DataFrame.

Here’s the corrected read code:

import pandas as pd

# Remove index_col if there's no 'Date' column in your sheet
df = pd.read_excel('c:/Test.xlsx', sheet_name="Sheet1", parse_dates=True)

# Verify the DataFrame loads correctly
print("Loaded DataFrame shape:", df.shape)
print(df.head())

If you do have a Date column, double-check the name matches exactly (Pandas is case-sensitive) and that it’s not empty in the Excel file.

2. Extracting Top 10 Teams & Filtering for GroupBy #

Your current approach has a couple of key issues:

• df['Revenue'].head(10) grabs the first 10 revenue values, not the top 10 teams by total revenue.
• Using df['team'].isin(rev) in your groupby creates boolean groups (True/False) instead of filtering to just the top teams.

Here’s the correct workflow to isolate and analyze the top 10 teams:

# Step 1: Calculate total revenue per team (since your data is monthly entries per team)
team_totals = df.groupby('team')['Revenue'].sum().reset_index()

# Step 2: Get the top 10 teams by total revenue (convert to a list of team names)
top_10_teams = team_totals.nlargest(10, 'Revenue')['team'].tolist()

# Step 3: Filter your original DataFrame to only include these top teams
filtered_df = df[df['team'].isin(top_10_teams)]

# Step 4: Group the filtered data to get totals (or use for further analysis)
top_team_revenue = filtered_df.groupby('team', as_index=False)['Revenue'].sum()\
                              .sort_values('Revenue', ascending=False)

This gives you a clean DataFrame with just the top 10 teams and their total revenue, sorted from highest to lowest.

3. Adding Non-Grouped Columns (Location, Team Lead) to GroupBy Results #

Assuming location and team lead are consistent for all entries of a team (each team has one fixed location and lead), you can use Pandas’ agg() method to include these columns in your groupby. Use first() as the aggregation function since all values for a team will be identical.

Modify the groupby step like this:

# Group by team, sum revenue, and include location/team lead
grouped_with_extra_cols = filtered_df.groupby('team', as_index=False).agg(
    Total_Revenue=('Revenue', 'sum'),
    Team_Location=('location', 'first'),
    Team_Lead=('team lead', 'first')
).sort_values('Total_Revenue', ascending=False)

If location or team lead can vary per row for a team (unlikely but possible), use aggregations like unique() to get all distinct values, or mode()[0] to get the most common one.

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