在R语言中基于另一数据框的价格区间计算字段

Got it, let's solve this problem step by step. Since you're dealing with multiple fee ranges (and don't want to rely on common IDs to join tables), here are two scalable approaches using pandas that work well even with a large number of intervals.

First, let's define sample data frames to make the example concrete:

import pandas as pd

# Your sales data (simplified to 5 records)
sales_df = pd.DataFrame({
    'sale_id': [1, 2, 3, 4, 5],
    'sales_amount': [150, 800, 2500, 5000, 12000]
})

# Pricing fee structure (adjust columns to match your actual data)
pricing_fees = pd.DataFrame({
    'min_amount': [0, 1000, 5000, 10000],
    'max_amount': [999, 4999, 9999, float('inf')],
    'fee_rate': [0.02, 0.015, 0.01, 0.008]  # 2%, 1.5%, 1%, 0.8%
})

Approach 1: Using pd.cut for Binning #

This method is straightforward if you just need to map sales amounts to fee rates based on fixed intervals.

Steps: #

1. Sort and validate the fee ranges: Ensure your pricing_fees is sorted by min_amount to avoid misalignment.

   pricing_fees = pricing_fees.sort_values('min_amount').reset_index(drop=True)
   

2. Create bins and labels: Extract the interval edges and corresponding fee rates from pricing_fees.

   # Bins are the start of each range plus the final max value
   bins = pricing_fees['min_amount'].tolist() + [pricing_fees['max_amount'].iloc[-1]]
   # Labels are the fee rates for each bin
   fee_labels = pricing_fees['fee_rate'].tolist()
   

3. Assign fee rates to sales records: Use pd.cut to bin each sales amount and map it to the correct fee rate.

   # `include_lowest=True` ensures the first bin includes the minimum value (0 in our case)
   sales_df['fee_rate'] = pd.cut(
       sales_df['sales_amount'],
       bins=bins,
       labels=fee_labels,
       include_lowest=True
   )
   

4. Calculate the final fee: Multiply the sales amount by the assigned fee rate.

   sales_df['fee'] = sales_df['sales_amount'] * sales_df['fee_rate']
   

Approach 2: Using merge_asof for Range Matching #

This method is ideal if you need to bring additional columns from pricing_fees into your sales data, or if you want more control over how ranges are matched. It’s also highly efficient for large datasets.

Steps: #

1. Sort both data frames: merge_asof requires both data frames to be sorted on the key column (sales amount / min amount).

   sales_sorted = sales_df.sort_values('sales_amount').reset_index(drop=True)
   pricing_sorted = pricing_fees.sort_values('min_amount').reset_index(drop=True)
   

2. Perform the range merge: Use merge_asof to match each sales amount to the appropriate fee range. The direction='backward' parameter ensures we find the largest min_amount that is less than or equal to the sales amount.

   merged = pd.merge_asof(
       sales_sorted,
       pricing_sorted,
       left_on='sales_amount',
       right_on='min_amount',
       direction='backward'
   )
   

3. Filter invalid matches (optional): If your fee ranges don’t cover all possible sales amounts, you can filter out records where the sales amount exceeds the max_amount:

   merged = merged[merged['sales_amount'] <= merged['max_amount']]
   

4. Calculate the fee and restore original order:

   merged['fee'] = merged['sales_amount'] * merged['fee_rate']
   # Get back the original order by merging with the original sales_df
   sales_df = sales_df.merge(merged[['sale_id', 'fee_rate', 'fee']], on='sale_id')
   

Key Notes: #

• Interval Boundaries: Adjust pd.cut parameters (like right=False) if your fee ranges are inclusive of the upper bound instead of the lower.
• Scalability: Both methods work efficiently even with hundreds of fee ranges—no need for manual conditional logic.
• Flexibility: If your fees are fixed (not percentage-based), just replace fee_rate with a fixed_fee column in the calculations.

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