Pandas按员工ID与月份分组统计销售额求助

Got it, let's walk through this step by step to get the exact result you're looking for. Here's how to calculate the number of sales per employee per month using Pandas:

Step 1: Prepare Your DataFrame #

First, make sure your date column is in datetime format (this is crucial for extracting the month later). If it's not already, convert it:

import pandas as pd

# Sample data (replace with your actual dataset)
data = {
    'date': ['2000-01-01', '2000-01-02'],
    'employee_ID': [12, 12],
    'price': [300, 250]
}
df = pd.DataFrame(data)

# Convert date column to datetime type
df['date'] = pd.to_datetime(df['date'])

Step 2: Convert Dates to Year-Month Format #

We need to aggregate by month, so let's modify the date column to show only the year and month (like 2000-01). You can do this in two ways:

• Using dt.to_period('M') (gives a period object, great for future date operations)
• Using dt.strftime('%Y-%m') (gives a string, if you prefer text format)

Choose one of these lines:

# Option 1: Period object (recommended for date consistency)
df['date'] = df['date'].dt.to_period('M')

# Option 2: String format
# df['date'] = df['date'].dt.strftime('%Y-%m')

Step 3: Group and Count Sales #

Now we'll group the data by date (year-month) and employee_ID, then count the number of sales entries in each group. We'll use size() to get the count, then reset_index() to turn the grouped indices back into columns, and rename the count column to total_sales:

# Group by month and employee ID, count sales
result = df.groupby(['date', 'employee_ID']).size().reset_index(name='total_sales')

Alternatively, if you want to count non-null values in the price column (useful if there are missing values), you can use:

result = df.groupby(['date', 'employee_ID'])['price'].count().reset_index(name='total_sales')

Final Result #

Running this code will give you the exact format you want:

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