基于Pandas按产品分组计算价格四分位数并实现卖家价格分类的技术问题

Let's break down how to solve this problem step by step, addressing both the grouped quantile calculation and the special case where all prices for a product are identical.

Step 1: Setup Sample Data #

First, let's replicate your input DataFrame to test our solution:

import pandas as pd

data = {
    'product': ['A', 'A', 'A', 'A', 'A', 'B'],
    'seller': ['Yo', 'Ka', 'Poy', 'Nyu', 'Poh', 'Poh'],
    'price': [10, 5, 7.5, 2.5, 1.25, 11.25]
}
df = pd.DataFrame(data)

Step 2: Define a Function to Calculate Grouped Quartiles #

We need a custom function to handle both normal cases and the special scenario where all prices in a product group are the same:

def calculate_group_quantiles(group):
    prices = group['price']
    # Check if all prices in the group are identical
    if prices.nunique() == 1:
        q_value = prices.iloc[0]
        return pd.Series({'1Q': q_value, '2Q': q_value, '3Q': q_value, '4Q': q_value})
    else:
        # Calculate the four required quantiles (0.25, 0.5, 0.75, 1.0)
        q1 = prices.quantile(0.25)
        q2 = prices.quantile(0.5)
        q3 = prices.quantile(0.75)
        q4 = prices.quantile(1.0)
        return pd.Series({'1Q': q1, '2Q': q2, '3Q': q3, '4Q': q4})

Step 3: Compute Quartiles per Product and Merge with Original Data #

Use groupby to apply our function to each product group, then merge the results back with the original DataFrame:

# Calculate quartiles for each product group
product_quantiles = df.groupby('product').apply(calculate_group_quantiles).reset_index()

# Merge the original data with the product-specific quartiles
merged_df = pd.merge(df, product_quantiles, on='product')

Step 4: Assign Quartile Category to Each Seller's Price #

Next, we'll create a function to map each price to its corresponding quartile, including handling the special case:

def assign_quartile_category(row):
    price = row['price']
    q1, q2, q3, q4 = row['1Q'], row['2Q'], row['3Q'], row['4Q']
    
    # Special case: all quartiles are the same, assign to 1Q
    if q1 == q4:
        return 1
    # Normal case: map price to the correct quartile
    if price <= q1:
        return 1
    elif price <= q2:
        return 2
    elif price <= q3:
        return 3
    else:
        return 4

Apply this function to create the Quartile column:

merged_df['Quartile'] = merged_df.apply(assign_quartile_category, axis=1)

Step 5: Reorder Columns to Match Desired Output #

Finally, adjust the column order to match your expected result:

final_df = merged_df[['product', 'seller', 'price', 'Quartile', '1Q', '2Q', '3Q', '4Q']]
print(final_df)

Output #

Running this code will produce exactly the result you're looking for:

product seller  price  Quartile    1Q   2Q    3Q    4Q
0       A     Yo   10.0         4  2.50  5.0  7.50  10.0
1       A     Ka    5.0         2  2.50  5.0  7.50  10.0
2       A    Poy    7.5         3  2.50  5.0  7.50  10.0
3       A    Nyu    2.5         1  2.50  5.0  7.50  10.0
4       A    Poh    1.25        1  2.50  5.0  7.50  10.0
5       B    Poh   11.25        1  11.25 11.25 11.25 11.25

Why Your Initial Attempt Didn't Work #

Your original code df['Price'].quantile([0.25,0.5,0.75,1]) calculates global quantiles across all prices in the DataFrame. By using groupby('product').apply(), we ensure we're computing quantiles per product group, which aligns with your requirement.

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