如何使用Pandas对数据框中早于最后日期的行执行聚合计算（均值与标准差）

Great question! Your current approach works, but we can make this more Pandas-idiomatic (and far more robust) by focusing on date-based filtering instead of relying on row positions with iloc[:-1]. Here's a cleaner, more reliable way to achieve your goal:

Step-by-Step Breakdown #

1. Convert the day column to datetime: This ensures proper date comparison (critical since your raw data uses string-formatted dates).
2. Identify the latest date in the dataset: Use max() to grab the final date across all rows.
3. Filter out rows from the last date: Keep only entries where day is earlier than this latest date.
4. Group and aggregate in one step: Use agg() to compute both mean and standard deviation for each country in a single, readable operation.

Full Code Implementation #

import pandas as pd

# Sample DataFrame
df = pd.DataFrame(
    data={
        "day": ['2021-01-01', '2021-01-01', '2021-01-02', '2021-01-02', '2021-01-03', '2021-01-03'],
        "country": ["France", "Brazil", "France", "Brazil", "France", "Brazil"],
        "n": [1, 2, 3, 4, 5, 6]
    }
)

# Convert date strings to datetime objects for accurate comparison
df['day'] = pd.to_datetime(df['day'])

# Get the final date present in the dataset
last_day = df['day'].max()

# Keep only rows where the date is before the last day
filtered_df = df[df['day'] < last_day]

# Group by country and calculate both mean and standard deviation
result = filtered_df.groupby('country')['n'].agg(['mean', 'std'])

# Format standard deviation to 4 decimal places to match your expected output
result['std'] = result['std'].round(4)

# Reset index to match the flat DataFrame structure you want
result = result.reset_index()

print(result)

Output #

country  mean    std
0  Brazil     3  1.4142
1  France     2  1.4142

Why This Is Superior to Your Original Approach #

Your initial code uses iloc[:-1] to exclude the last row of each group, which relies on two shaky assumptions:

• Each country has exactly one row on the last date
• Rows in each group are perfectly sorted by date

This isn't always reliable (e.g., if a country has multiple entries on the last date, or your dataset is unsorted). The date-based filtering approach is explicit, robust, and easier to read—it directly implements your requirement of excluding rows from the final date, regardless of row position or group size.

内容的提问来源于stack exchange，提问作者Be Chiller Too