基于label和month分组后，如何将销量求和值按月记录数做除法？

Let's break down how to achieve your goal step by step. You need to compute, for each combination of label and month, the average of the Quantity values (sum divided by the number of records in the group). Here are two reliable ways to do this in pandas:

Method 1: Using groupby with agg() (Simplest Approach) #

This method lets you compute both the sum and record count in one go, then combine them to get your desired result.

import pandas as pd

# Assume your DataFrame is named df
result = df.groupby(['label', 'month']).agg(
    total_quantity=('Quantity', 'sum'),
    record_count=('Quantity', 'size')
).assign(
    avg_quantity=lambda x: x['total_quantity'] / x['record_count']
).reset_index()

# If you only need the final average with label and month:
result_simplified = result[['label', 'month', 'avg_quantity']]

Explanation: #

• groupby(['label', 'month']): Groups the data by both columns as required.
• agg(...): Calculates two key metrics for each group:
  • total_quantity: Sum of the Quantity column.
  • record_count: Total number of records in the group (using size ensures we count all rows, even if Quantity has null values).
• assign(...): Creates a new column avg_quantity by dividing the sum by the record count.
• reset_index(): Converts the grouped index back to regular columns for readability.

Method 2: Using groupby with apply() (If You Prefer This Approach) #

If you want to stick with apply(), you can define a custom function that computes the average directly for each group:

def calculate_group_average(group):
    # Sum of Quantity divided by the number of records in the group
    return pd.Series({
        'avg_quantity': group['Quantity'].sum() / len(group),
        # Optional: include sum and count if you want to keep them
        'total_quantity': group['Quantity'].sum(),
        'record_count': len(group)
    })

result = df.groupby(['label', 'month']).apply(calculate_group_average).reset_index()

Explanation: #

• The custom function calculate_group_average takes each grouped subset of data as input.
• len(group) gives you the exact number of records in the group—this is the piece you were missing earlier!
• We return a Series with the computed values, which apply() will combine into a clean DataFrame.

Example Output #

Suppose your input data looks like this:

The resulting DataFrame will be:

This matches exactly what you described: sum of sales divided by the number of records per label-month group.

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