基于Pandas修正CSV中项目步骤时间戳列错位问题

Hey there! Let's fix those misplaced end timestamps step by step. Here's a straightforward approach that works for your sample data and scales to your 100k+ projects with 40 steps:

Step 1: Identify Rows Needing Correction #

First, we target rows where:

• Finish has a timestamp (project is completed)
• Step 5 End has a timestamp (the misplaced value)
• Step 5 Start is empty (meaning the project never actually entered Step 5)

Step 2: Find the Correct End Column #

For each qualifying row, we loop through all steps to find the last step where the start timestamp exists but the end timestamp is missing—this is where the misplaced value should go.

Step 3: Implement the Fix #

Here's the full code:

import pandas as pd

# Read your data (adjust the file path as needed)
df = pd.read_csv("sampledata.csv", sep=";")

# Dynamically get all step numbers from the columns (works for 5 or 40 steps)
steps = sorted([int(col.split()[1]) for col in df.columns if 'Step' in col and 'Start' in col])

def fix_misplaced_end(row):
    # Check if the row meets the error criteria
    if pd.notna(row['Finish']) and pd.notna(row['Step 5 End']) and pd.isna(row['Step 5 Start']):
        last_incomplete_step = None
        # Iterate through steps to find the last one with a start but no end
        for step_num in steps:
            start_col = f"Step {step_num} Start"
            end_col = f"Step {step_num} End"
            if pd.notna(row[start_col]) and pd.isna(row[end_col]):
                last_incomplete_step = step_num
        
        # If we found the correct step, move the value
        if last_incomplete_step is not None:
            target_end_col = f"Step {last_incomplete_step} End"
            row[target_end_col] = row['Step 5 End']
            row['Step 5 End'] = pd.NA  # Clear the misplaced value

    return row

# Apply the fix to the entire dataframe
df = df.apply(fix_misplaced_end, axis=1)

# Verify the result (optional)
print(df)

Testing the Fix Against Your Sample #

After running the code, your sample data will be corrected as expected:

• Project C: Step 2 End gets the value from Step 5 End
• Project D: Step 1 End gets the value from Step 5 End
• Project E: Step 4 End gets the value from Step 5 End
• Projects A and B remain unchanged since they don't meet the error criteria

Performance Note for Large Datasets #

Since you have 100k+ rows, using apply() might take a few seconds. If you need faster performance, we can optimize this with vectorized operations using numpy, but the above code is clear and easy to maintain, which is usually the priority unless speed is critical.

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