如何按日期计算DataFrame中每日Top 10%数值的均值

Got it, let's walk through how to solve this problem. You have a DataFrame with dates and corresponding values, and you want to get the average of the top 10% values for each day. Here's a straightforward approach using pandas:

Step 1: Set up your data #

First, let's import the necessary libraries and create your sample DataFrame:

import pandas as pd
import datetime

# 示例数据集
sample_data = {
    'Date': [datetime.date(2021, 4, 1)] * 30,
    'value': [3.35, 1.85, 1.3, 1.85, 1.85, 1.17, 1.17, 2.8, 1.43, 2.54,
              1.22, 2.54, 1.17, 1.17, 2.71, 5.98, 1.39, 1.48, 16.46, 1.43,
              8.39, 33.99, 2.54, 11.8, 2.13, 2.24, 2.92, 1.35, 1.54, 2.52]
}

df = pd.DataFrame(sample_data)

Step 2: Calculate the top 10% mean per day #

We'll use groupby to handle each date's data separately, then filter for the top 10% values and compute their average:

# 分组计算每日Top10%数值的均值
daily_top10_mean = df.groupby('Date').apply(
    lambda group: group[group['value'] >= group['value'].quantile(0.9)]['value'].mean()
).reset_index(name='top_10%_mean')

# 查看结果
print(daily_top10_mean)

代码解释： #

• groupby('Date'): Groups the DataFrame by each unique date, so we process one day's data at a time.
• group['value'].quantile(0.9): Calculates the 90th percentile for the values in the current date group—this is the threshold for the top 10%.
• group[group['value'] >= threshold]: Filters the group to keep only values that fall into the top 10%.
• ['value'].mean(): Computes the average of those top 10% values.
• reset_index(...): Converts the grouped result back into a clean DataFrame with a descriptive column name for the mean.

Example Output #

For your sample data, the top 10% of 30 values is 3 entries (33.99, 16.46, 11.8). Their average is ~20.75, so the output will look like:

Date  top_10%_mean
0  2021-04-01     20.750000

Alternative Approach (More Readable) #

If you prefer a step-by-step breakdown, you can first add the 90th percentile for each date to the original DataFrame, then filter and compute the mean:

# 添加每日90分位数列
df['daily_90th_percentile'] = df.groupby('Date')['value'].transform(lambda x: x.quantile(0.9))

# 筛选Top10%的数值
top_10_values = df[df['value'] >= df['daily_90th_percentile']]

# 计算每日均值
daily_top10_mean = top_10_values.groupby('Date')['value'].mean().reset_index(name='top_10%_mean')

This works exactly the same way, but breaks the process into clearer steps—great for debugging or explaining to others.

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