如何比较两个Pandas DataFrame的值及处理新增价格点的销售数据补全

Got it, let's break this down into two clear parts: handling missing sales entries when you add new price points to price, and comparing values across your two DataFrames. Here's how I'd tackle each step:

1. 新增价格点时预估销售数据并更新sales_tracking #

First, let's assume your DataFrames look something like this (tweak column names if yours are different):

• price: Contains SKU, price_point (and maybe other metadata like date added)
• sales_tracking: Contains SKU, price_point, total_orders, total_visits

Step 1: Identify missing (SKU, price_point) combinations #

We need to find which price points in price don't have a matching entry in sales_tracking:

import pandas as pd

# Create sets of (SKU, price_point) tuples for easy comparison
price_sku_price = set(zip(price['SKU'], price['price_point']))
sales_sku_price = set(zip(sales_tracking['SKU'], sales_tracking['price_point']))

# Find combinations that exist in price but not in sales_tracking
missing_entries = price_sku_price - sales_sku_price

# Convert these to a DataFrame to work with
missing_df = pd.DataFrame(missing_entries, columns=['SKU', 'price_point'])

Step 2: Estimate total_orders and total_visits #

How you estimate these depends on your data and business logic. Here are two common approaches:

Option A: Use average values (simple, good for quick estimates)

If you don't have enough data for complex models, use averages for the SKU (or global averages if the SKU is new):

# Calculate average orders/visits per SKU from existing sales data
sku_avg_sales = sales_tracking.groupby('SKU')[['total_orders', 'total_visits']].mean().reset_index()

# Merge averages with missing entries
missing_df = missing_df.merge(sku_avg_sales, on='SKU', how='left')

# For SKUs with no historical data, fill with global averages
global_avg = sales_tracking[['total_orders', 'total_visits']].mean()
missing_df[['total_orders', 'total_visits']] = missing_df[['total_orders', 'total_visits']].fillna(global_avg)

# Round to integers since you can't have partial orders/visits
missing_df[['total_orders', 'total_visits']] = missing_df[['total_orders', 'total_visits']].round().astype(int)

Option B: Use linear regression (more accurate for price-sensitive sales)

If you have historical price-sales data for a SKU, use a simple linear model to predict based on price point:

from sklearn.linear_model import LinearRegression

# Precompute global averages as a fallback
global_avg = sales_tracking[['total_orders', 'total_visits']].mean()

def predict_sales(sku, new_price):
    # Get historical data for the SKU
    sku_history = sales_tracking[sales_tracking['SKU'] == sku]
    
    # Not enough data? Return global average
    if len(sku_history) < 2:
        return (max(0, global_avg['total_orders']), max(0, global_avg['total_visits']))
    
    # Train models for orders and visits
    X = sku_history[['price_point']]
    model_orders = LinearRegression().fit(X, sku_history['total_orders'])
    model_visits = LinearRegression().fit(X, sku_history['total_visits'])
    
    # Predict and ensure non-negative values
    pred_orders = max(0, model_orders.predict([[new_price]])[0])
    pred_visits = max(0, model_visits.predict([[new_price]])[0])
    
    return (round(pred_orders), round(pred_visits))

# Apply prediction to missing entries
missing_df[['total_orders', 'total_visits']] = missing_df.apply(
    lambda row: predict_sales(row['SKU'], row['price_point']),
    axis=1,
    result_type='expand'
)

Step 3: Update sales_tracking #

Append the estimated entries to your sales tracking DataFrame:

sales_tracking = pd.concat([sales_tracking, missing_df], ignore_index=True)

2. Compare values across price and sales_tracking #

There are a few ways to compare these DataFrames depending on what you need to check:

Check for mismatched (SKU, price_point) combinations #

See if there are entries in one DataFrame that don't exist in the other:

# Entries in sales_tracking but not in price
extra_sales_entries = sales_sku_price - price_sku_price
if extra_sales_entries:
    print(f"Found sales entries with no matching price point: {extra_sales_entries}")

# Entries in price but not in sales_tracking (we already handled these, but double-check)
missing_sales_entries = price_sku_price - sales_sku_price
if missing_sales_entries:
    print(f"Found price points with no sales data: {missing_sales_entries}")

Compare numerical values for matching combinations #

If you want to check differences (e.g., validate predicted vs actual sales later), merge the DataFrames and calculate deviations:

# Merge on SKU and price_point to get matching records
comparison_df = price.merge(sales_tracking, on=['SKU', 'price_point'], how='inner')

# Example: If price has an expected_orders column, compare to actual total_orders
# comparison_df['order_deviation'] = comparison_df['total_orders'] - comparison_df['expected_orders']

# Or use Pandas' compare() to see exact value differences
aligned_price = price.set_index(['SKU', 'price_point'])
aligned_sales = sales_tracking.set_index(['SKU', 'price_point'])

# Only compare common columns and indices
common_cols = aligned_price.columns.intersection(aligned_sales.columns)
common_idx = aligned_price.index.intersection(aligned_sales.index)

value_differences = aligned_price.loc[common_idx, common_cols].compare(aligned_sales.loc[common_idx, common_cols])
print("Value differences between matching entries:")
print(value_differences)

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