如何在Pandas中同时使用内置与自定义聚合函数？

Hey there! Since you didn't share your original working code, I'll cover the two most common scenarios where folks run into issues adding quantile(.25) to their aggregations: Pandas and SQL. Let's break them down:

Suppose your original working code looked something like this (grouping by a column with basic aggregations):

import pandas as pd

# Sample dataset
df = pd.DataFrame({
    'category': ['A', 'A', 'B', 'B', 'B'],
    'value': [10, 20, 30, 40, 50]
})

# Original working aggregation
original_result = df.groupby('category').agg({
    'value': ['mean', 'sum']
})

When you tried adding quantile(.25) directly, you might have written code like this (which throws an error):

# Incorrect approach that causes errors
wrong_result = df.groupby('category').agg({
    'value': ['mean', 'sum', quantile(.25)]  # Error here
})

Correct Implementation in Pandas #

You need to pass the quantile calculation as a lambda function, or use Pandas' named aggregation syntax for cleaner code:

Option 1: Using lambda functions

correct_result = df.groupby('category').agg({
    'value': ['mean', 'sum', lambda x: x.quantile(0.25)]
})
# Rename columns for readability
correct_result.columns = ['value_mean', 'value_sum', 'value_25th_quantile']

Option 2: Using named aggregation (more readable)

correct_result = df.groupby('category').agg(
    value_mean=('value', 'mean'),
    value_sum=('value', 'sum'),
    value_25th_quantile=('value', lambda x: x.quantile(0.25))
)

If your original query looked like this (basic grouped aggregations):

SELECT 
    category,
    AVG(value) AS value_mean,
    SUM(value) AS value_sum
FROM your_table
GROUP BY category;

Adding a quantile directly won't work across all SQL dialects—each database has its own function for calculating percentiles:

Correct Implementation by SQL Dialect #

• PostgreSQL: Use PERCENTILE_CONT (continuous) or PERCENTILE_DISC (discrete)

  SELECT 
      category,
      AVG(value) AS value_mean,
      SUM(value) AS value_sum,
      PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value) AS value_25th_quantile
  FROM your_table
  GROUP BY category;
  

• MySQL: Use window functions with PERCENTILE_CONT

  SELECT DISTINCT
      category,
      AVG(value) OVER (PARTITION BY category) AS value_mean,
      SUM(value) OVER (PARTITION BY category) AS value_sum,
      PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category) AS value_25th_quantile
  FROM your_table;
  

• BigQuery: Use APPROX_QUANTILES (returns an array, pick the 25th percentile at index 1)

  SELECT 
      category,
      AVG(value) AS value_mean,
      SUM(value) AS value_sum,
      APPROX_QUANTILES(value, 4)[OFFSET(1)] AS value_25th_quantile
  FROM your_table
  GROUP BY category;
  

If your use case is with another tool (like PySpark, R, etc.), share your original code and the exact error message, and I can tweak this answer to fit your scenario!

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