基于Datetime列部分匹配合并Pandas DataFrames及异常排查

Hey there! Since you’ve already got the groundwork laid with pyjanitor and pandas, let’s work through these three issues step by step—they’re all common pain points when reconciling schedule vs. actual transit data, so I’ve got you covered.

1. 排除仅计划存在但实际无记录的日期 #

The key here is to only keep dates that have corresponding actual ride data before merging, or use an inner join to automatically drop dates that don’t exist in both datasets. Let’s go with a clear, two-step approach to avoid confusion:

First, standardize your date columns to ensure they match (critical for accurate joins):

import pandas as pd

# Convert date columns to consistent date type (adjust column names to match your data!)
df_plan["transit_date"] = pd.to_datetime(df_plan["transit_date"]).dt.date
df_actual["transit_date"] = pd.to_datetime(df_actual["transit_date"]).dt.date

Next, filter the plan data to only include dates that appear in the actual data, then merge:

# Get all dates that have actual ride records
valid_dates = set(df_actual["transit_date"])

# Filter plan data to valid dates only
df_plan_filtered = df_plan[df_plan["transit_date"].isin(valid_dates)]

# Merge filtered plan data with actual data (use inner join to double down on matching dates/employees)
merged_data = pd.merge(
    df_plan_filtered,
    df_actual,
    on=["transit_date", "employee_id"],  # Adjust join keys to match your schema!
    how="inner"
)

This ensures you never have rows where a planned ride exists but there’s no corresponding actual record for that date.

2. 排查乘车时间/目的地与预订不符的人员 #

First, we need to map both planned and actual times to your specified intervals (e.g., 16:01-16:29 → 16:15). Let’s build a helper function to handle the time mapping, then compare intervals and destinations:

Step 1: Time interval mapping function #

def map_to_interval(time_str):
    # Convert string time (e.g., "16:05") to a time object
    ride_time = pd.to_datetime(time_str).time()
    hour = ride_time.hour
    minute = ride_time.minute

    # Apply your interval rule
    if 1 <= minute <= 29:
        return f"{hour:02d}:15"
    elif 30 <= minute <= 59:
        return f"{hour:02d}:45"
    # Handle 00:00 case (adjust based on your business rules!)
    else:
        return f"{hour-1 if hour > 0 else 23:02d}:45"

Step 2: Add interval columns and flag mismatches #

# Apply the function to planned and actual time columns
df_plan_filtered["planned_interval"] = df_plan_filtered["planned_time"].apply(map_to_interval)
df_actual["actual_interval"] = df_actual["actual_time"].apply(map_to_interval)

# Merge to compare all fields
full_comparison = pd.merge(
    df_plan_filtered,
    df_actual,
    on=["transit_date", "employee_id"],
    how="inner"
)

# Flag rows where time interval OR destination doesn't match
mismatched_rides = full_comparison[
    (full_comparison["planned_interval"] != full_comparison["actual_interval"])
    | (full_comparison["planned_destination"] != full_comparison["actual_destination"])
]

# Optional: Group by employee to see all their mismatches at a glance
employee_mismatches = mismatched_rides.groupby("employee_id")[
    ["transit_date", "planned_time", "actual_time", "planned_destination", "actual_destination"]
].agg(list)

Adjust the column names (like planned_time, planned_destination) to match your actual dataset schema.

3. 找出无预订记录但实际乘车的人员 #

We need to identify rows in the actual data where the employee + date combination doesn’t exist in the plan data. Here’s a straightforward way to do that:

# Create a set of unique (employee_id, transit_date) pairs from the filtered plan data
plan_ride_keys = set(df_plan_filtered.apply(lambda x: (x["employee_id"], x["transit_date"]), axis=1))

# Filter actual data to find rides not in the plan
unplanned_rides = df_actual[
    ~df_actual.apply(lambda x: (x["employee_id"], x["transit_date"]), axis=1).isin(plan_ride_keys)
]

# Get a list of unique employees who took unplanned rides
unique_unplanned_employees = unplanned_rides["employee_id"].unique().tolist()

If you want to see the full details of each unplanned ride, just keep the unplanned_rides DataFrame instead of narrowing to unique employees.

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