Pandas时序DataFrame中错误值的条件回填与删除处理

Alright, let's work through this time-series cleaning task together. I'll break it down into actionable steps with code examples so you can follow along easily.

First, make sure your Time column is formatted as a datetime type—this is non-negotiable for calculating time gaps accurately. If it's stored as strings, convert it first:

import pandas as pd
import numpy as np

# Convert Time column to datetime
df['Time'] = pd.to_datetime(df['Time'])

Next, we'll mark rows where Value < 90 as invalid, then compute the time difference between each row and the previous row in the same ID group (converted to hours for easy comparison):

# Flag invalid Value entries
df['is_invalid'] = df['Value'] < 90

# Calculate time gap (in hours) from the previous row in the same ID group
df['time_gap_hours'] = df.groupby('ID')['Time'].diff().dt.total_seconds() / 3600

Now we'll handle invalid rows based on the 2-hour threshold:

• For invalid rows with a time gap ≤ 2 hours from the prior ID record: Fill with the previous valid (or existing) Value plus a tiny random perturbation. We'll add a safety clip to ensure the filled value never drops below 90.
• For invalid rows with a time gap > 2 hours, or rows that are the first entry for an ID (where there's no prior record): Delete these rows entirely.

# 1. Fill invalid rows with time gap ≤ 2 hours
fill_mask = df['is_invalid'] & (df['time_gap_hours'] <= 2)
# Use previous row's Value + small perturbation (adjust range as needed)
df.loc[fill_mask, 'Value'] = df.loc[fill_mask, 'Value'].shift(1) + np.random.uniform(-0.3, 0.3, size=sum(fill_mask))
# Ensure filled values stay ≥90 (safety net)
df.loc[fill_mask, 'Value'] = df.loc[fill_mask, 'Value'].clip(lower=90.0)

# 2. Delete remaining invalid rows
# This covers rows with gap >2 hours AND first-row invalid entries (where time_gap_hours is NaN)
df = df[~(df['is_invalid'] & (df['time_gap_hours'] > 2))]
df = df[~(df['is_invalid'] & df['time_gap_hours'].isna())]

# Clean up helper columns (optional but recommended)
df = df.drop(['is_invalid', 'time_gap_hours'], axis=1)

Let's use a sample DataFrame to see how this works:

# Sample input data
data = {
    'ID': ['A', 'A', 'A', 'B', 'B', 'B'],
    'Time': ['2024-01-01 08:00', '2024-01-01 09:30', '2024-01-01 12:00', '2024-01-01 07:00', '2024-01-01 08:30', '2024-01-01 11:00'],
    'Value': [95, 88, 92, 87, 93, 89]
}
df = pd.DataFrame(data)

After running the code:

• ID A's second row (Value=88) has a 1.5-hour gap from the prior row → gets filled with ~95 ±0.3 (e.g., 94.8 or 95.2)
• ID B's first row (Value=87) is the first entry for the ID → gets deleted
• ID B's third row (Value=89) has a 2.5-hour gap from the prior row → gets deleted
• All valid rows stay intact

• Adjust the perturbation range (-0.3 to 0.3) to fit your needs—just make sure the clip ensures values never drop below 90.
• If you have consecutive invalid rows (e.g., two back-to-back Value<90 entries), the logic will use the last valid prior entry for filling (since shift(1) will grab the previous row's Value, even if it was filled in the step above).

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