基于浮点上下界与日期merge_asof合并两个Pandas DataFrame的技术咨询

Got it, let's break down how to solve this problem. You're correct that merge_asof is relevant here, but we need to combine it with range filtering to handle the occupancy bounds, then pick the latest valid rule based on filedate.

Step 1: Preprocess Your Data #

First, make sure both DataFrames have datetime columns sorted (this helps with efficient date matching later):

import pandas as pd

# Business DataFrame
df = pd.DataFrame({"date": ["2022-04-10", "2022-04-11", "2022-04-12", "2022-04-13"], "occupancy": [30, 55, 28, 100]})
df["date"] = pd.to_datetime(df["date"])
df = df.sort_values("date").reset_index(drop=True)

# Rules DataFrame
rules = pd.DataFrame({"lower": [0, 25, 50, 75, 25], "upper": [25, 50, 75, 100, 50], "action": [-15, -5, 20, 25, -10], "filedate": ["2022-04-01", "2022-04-01", "2022-04-01", "2022-04-01", "2022-04-12"]})
rules["filedate"] = pd.to_datetime(rules["filedate"])
rules = rules.sort_values("filedate").reset_index(drop=True)

Step 2: Option 1 - Cartesian Product + Filtering (Good for Small-to-Medium Data) #

This approach is straightforward and easy to debug:

1. Create a cross join of the two DataFrames to get all possible rule-business row pairs.
2. Filter pairs that meet your three conditions.
3. For each business row, keep only the rule with the latest filedate.
4. Merge back to the original business DataFrame to retain all rows (even those with no matching rules).

# Add an index to the business DataFrame for grouping later
df["idx"] = df.index

# Create cross join using a temporary key
cross_join = df.assign(key=1).merge(rules.assign(key=1), on="key").drop("key", axis=1)

# Filter rows that meet all conditions
filtered = cross_join[
    (cross_join["lower"] <= cross_join["occupancy"])
    & (cross_join["occupancy"] < cross_join["upper"])
    & (cross_join["filedate"] <= cross_join["date"])
]

# Get the latest valid rule for each business row
latest_rules = filtered.sort_values("filedate").groupby("idx").last().reset_index(drop=True)

# Merge back to original DataFrame
final_df = df.merge(latest_rules[["idx", "action"]], on="idx", how="left").drop("idx", axis=1)

Step 3: Option 2 - Apply Function (Intuitive for Small Data) #

If your dataset is small, using apply makes the logic explicit and easy to follow:

def get_latest_action(row):
    # Filter rules that match occupancy range and valid date
    valid_rules = rules[
        (rules["lower"] <= row["occupancy"])
        & (row["occupancy"] < rules["upper"])
        & (rules["filedate"] <= row["date"])
    ]
    if valid_rules.empty:
        return pd.NA
    # Return action from the rule with the most recent filedate
    return valid_rules.sort_values("filedate", ascending=False).iloc[0]["action"]

# Apply the function to each row in the business DataFrame
df["action"] = df.apply(get_latest_action, axis=1)

Step 4: Verify the Result #

Either method will give you the expected output:

Why Your Previous Approach Didn't Work #

The key differences from the post you referenced are:

• Data type: Your rules use floating-point ranges for occupancy, not date ranges, so we need explicit range checks instead of date-based joins.
• Matching logic: You need the latest valid rule (closest filedate <= business date) after filtering for occupancy, not an exact outer join.

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