基于Pandas构建多属性产品价格表：高效存储与查询方案咨询

Great job on that initial optimization—cutting runtime from 9 minutes to 21 seconds is exactly the kind of win that comes from leaning into Pandas' vectorized strengths instead of row-by-row loops. Let's break down why your current two options aren't ideal, then walk through a more Pandas-idiomatic approach.

Why Your Current Options Fall Short #

• Option 1 (High-Dimensional DataFrame): Pandas is built for 2D tabular data. Expanding to a multi-dimensional structure would force you into clunky workarounds like nested arrays or deprecated objects (like Panel), which hurt readability, maintainability, and even performance—Pandas isn't optimized for high-dimensional operations the way tools like xarray are.
• Option 2 (Multiple Independent DataFrames): Splitting attributes into separate tables creates unnecessary complexity. You'll end up juggling constant merges or lookups, increasing bug risk and making groupby/aggregation operations far more cumbersome.

The Pandas-Idiomatic Approach: Unified Tabular Data with Lookup Tables #

The key is to keep your core price data in a clean 2D DataFrame, and attach seasonal attributes via vectorized joins or mappings—no object types, no high dimensions, no juggling multiple tables. Here's how to implement it:

Step 1: Build a Season Lookup Table #

First, create a small, static DataFrame that defines your seasons. This acts as a single source of truth for seasonal metadata:

import pandas as pd

# Define your season rules (adjust dates/names to match your use case)
season_lookup = pd.DataFrame({
    "season_name": ["Spring", "Summer", "Fall", "Winter"],
    "start_date": pd.to_datetime(["2024-03-01", "2024-06-01", "2024-09-01", "2024-12-01"]),
    "end_date": pd.to_datetime(["2024-05-31", "2024-08-31", "2024-11-30", "2025-02-28"])
})

Step 2: Map Seasons to Your Price Data #

Link your core price table to this lookup table using vectorized operations (no loops!). There are two common scenarios:

Scenario A: Seasons are date-based (all products follow the same seasons)

Use pd.cut to assign a season to every date in your price table, then merge in the full seasonal metadata:

# Assume your core price table looks like this:
# price_df = pd.DataFrame({"product_id": [...], "date": [...], "price": [...]})

# Assign season names to each date
price_df["season_name"] = pd.cut(
    price_df["date"],
    bins=season_lookup["start_date"].tolist() + [pd.Timestamp.max],
    labels=season_lookup["season_name"].tolist(),
    include_lowest=True
)

# Merge to add start/end dates
price_df = price_df.merge(season_lookup, on="season_name", how="left")

Scenario B: Seasons are product-specific (different products have unique seasonal windows)

Adjust your lookup table to include product_id, then merge directly with your price table:

# Product-specific season lookup
product_season_lookup = pd.DataFrame({
    "product_id": ["prod_001", "prod_001", "prod_002", "prod_002"],
    "season_name": ["Peak", "Off-Peak", "High", "Low"],
    "start_date": pd.to_datetime(["2024-04-01", "2024-10-01", "2024-05-01", "2024-11-01"]),
    "end_date": pd.to_datetime(["2024-09-30", "2025-03-31", "2024-10-31", "2025-04-30"])
})

# Merge with price data to attach seasonal attributes
price_df = price_df.merge(product_season_lookup, on=["product_id", "date"], how="left")

Step 3: Fast Queries & Calculations #

With all data in a single 2D DataFrame, you can leverage Pandas' optimized methods for lightning-fast operations:

• Filter by season: price_df.loc[price_df["season_name"] == "Spring"]
• Calculate seasonal average price: price_df.groupby(["product_id", "season_name"])["price"].mean()
• Filter dates within a season: price_df.loc[(price_df["date"] >= price_df["start_date"]) & (price_df["date"] <= price_df["end_date"])]

Key Benefits of This Approach #

• Performance: All operations are vectorized (no loops!), so you'll keep that 21-second runtime edge.
• Maintainability: A single lookup table makes it easy to update seasonal rules without touching your core price data.
• Idiomatic Pandas: Follows the library's design principles—clean tabular data, explicit joins, and built-in optimized methods.

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