使用Python For Loop识别缺失保养里程并生成虚拟行

Alright, let's work through how to solve this problem. You've got a pandas DataFrame with 25k+ vehicles, each with some completed service milestones, and you need to identify all missed services for each vehicle then generate rows marking each milestone as either service availed or service missed. Here's a step-by-step solution:

Step 1: Define Your Service Rules First #

First, we need to lock in the service interval rules. From your examples, it looks like services follow fixed km intervals (e.g., every 10,000km starting at 10,000km, or every 30,000km). We'll make these parameters configurable so you can tweak them to match your actual requirements.

Step 2: Example Input Data #

Let's start with a small sample DataFrame to test the solution:

import pandas as pd

# Sample input DataFrame matching your example cases
df = pd.DataFrame({
    'Registration number of vehicle': ['ABC123', 'ABC123', 'ABC123', 'XYZ789'],
    'KM Service Done': [10000, 20000, 70000, 210000]
})

Step 3: Solution Code #

We'll use pandas groupby to process each vehicle individually, generate all expected service milestones, then compare against completed services to mark statuses:

# Configure service parameters (adjust these to match your actual rules)
SERVICE_INTERVAL = 10000  # Change to 30000 if your interval is 30k for some vehicles
MIN_SERVICE_KM = 10000
MAX_SERVICE_KM = 800000

def process_single_vehicle(group):
    # Get the vehicle's registration number
    reg_number = group['Registration number of vehicle'].iloc[0]
    # Convert completed services to a set for fast lookups
    completed_services = set(group['KM Service Done'])
    
    # Determine the upper limit for services: either the vehicle's highest completed service or max allowed km
    highest_completed = group['KM Service Done'].max() if not group.empty else MIN_SERVICE_KM
    upper_limit = min(highest_completed, MAX_SERVICE_KM)
    
    # Generate all expected service milestones
    all_expected_services = range(MIN_SERVICE_KM, upper_limit + SERVICE_INTERVAL, SERVICE_INTERVAL)
    
    # Build rows for each milestone with status
    vehicle_rows = []
    for km in all_expected_services:
        status = 'service availed' if km in completed_services else 'service missed'
        vehicle_rows.append({
            'Registration number of vehicle': reg_number,
            'KM Service Done': km,
            'Service Status': status
        })
    
    # Optional: If you want to include milestones beyond the highest completed service (e.g., 240k for XYZ789),
    # replace the upper_limit line with this:
    # upper_limit = MAX_SERVICE_KM
    
    return pd.DataFrame(vehicle_rows)

# Process each vehicle group and combine results
final_df = df.groupby('Registration number of vehicle', group_keys=False).apply(process_single_vehicle)

# Reset index for cleaner output
final_df = final_df.reset_index(drop=True)

# Print or export the result
print(final_df)

Step 4: Key Notes & Adjustments #

• Handle Duplicates: If your raw data has duplicate entries for the same vehicle and service km, add df = df.drop_duplicates(subset=['Registration number of vehicle', 'KM Service Done']) before processing to avoid redundant status checks.
• Variable Intervals: If different vehicles have different service intervals, add an Service Interval column to your original DataFrame, then modify the process_single_vehicle function to pull the interval from the group instead of using a global constant.
• Performance: For 25k+ vehicles, this approach is efficient enough. If you need even faster processing, you could optimize with vectorized operations, but groupby.apply works reliably for this scale.

Example Output #

For the sample input with SERVICE_INTERVAL=10000, the output will look like this:

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