基于索引分段的Pandas DataFrame插值及公交GPS缺失数据处理咨询

Hey Gabriel, let's break down how to handle those two distinct types of NaNs in your São Paulo bus GPS data—this is a common scenario with time-series sensor data, so we can tackle it step by step.

First, let's clarify the two cases we need to address:

• Data scarcity NaNs: Missing values during active bus hours where you expect valid speed data (these are the ones we want to interpolate).
• Inactive period NaNs: NaNs during known downtime (like early morning) where the bus is stationary—these should either stay as NaNs or be set to 0, depending on your analysis needs.

Step 1: Mark Inactive Periods #

First, we need to flag which time slots fall into the bus's inactive window. Let's assume you know the exact downtime (e.g., 0:00 to 5:00 AM)—adjust this to match your actual schedule.

import pandas as pd
import numpy as np

# Ensure your DataFrame is sorted by time (critical for interpolation!)
df = df.sort_index()

# Extract the hour from the timestamp index to identify inactive periods
df['hour'] = df.index.hour
# Define inactive hours (customize this range to your bus schedule)
df['is_inactive'] = (df['hour'] >= 0) & (df['hour'] < 5)

Step 2: Differentiated Interpolation & NaN Handling #

Now we'll apply different logic to each group:

For Active Period NaNs (Data Scarcity)

Use interpolate() with a time-aware method, since GPS data is time-indexed. The time method will interpolate based on the actual time gaps between data points, which is more accurate than linear interpolation for irregular time series.

You can tweak parameters like limit to cap how many consecutive NaNs you interpolate (to avoid filling long gaps that might indicate actual equipment issues) and limit_direction to control interpolation direction.

For Inactive Period NaNs

Choose either:

1. Set to 0: Since the bus is stationary, speed should logically be 0.
2. Keep as NaN: If you want to explicitly distinguish downtime from data gaps in later analysis.

Here's the code to implement both options:

# Interpolate only active period NaNs
df.loc[~df['is_inactive'], 'speed'] = df.loc[~df['is_inactive'], 'speed'].interpolate(
    method='time',  # Time-aware interpolation
    limit=30,       # Max 30 consecutive NaNs to interpolate (adjust as needed)
    limit_direction='both'  # Interpolate forward and backward
)

# Option 1: Replace inactive period NaNs with 0 (stationary speed)
df.loc[df['is_inactive'], 'speed'] = df.loc[df['is_inactive'], 'speed'].fillna(0)

# Option 2: Keep inactive period NaNs (uncomment if this fits your needs)
# df.loc[df['is_inactive'], 'speed'] = df.loc[df['is_inactive'], 'speed'].fillna(np.nan)

Step 3: Validate the Results #

Always visualize your processed data to make sure the interpolation and inactive period handling worked as expected:

import matplotlib.pyplot as plt

plt.figure(figsize=(14, 7))
plt.plot(df.index, df['speed'], linewidth=1, label='Processed Speed')
# Highlight inactive periods for clarity
plt.fill_between(df.index, 0, df['speed'].max(), where=df['is_inactive'], color='#ffcccc', alpha=0.3, label='Inactive (Bus Stopped)')
plt.xlabel('Timestamp')
plt.ylabel('Bus Speed')
plt.title('Interpolated Bus Speed with Inactive Periods Highlighted')
plt.legend()
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

Bonus Tips #

• If your inactive periods aren't fixed hourly windows (e.g., weekends or holidays), you can expand the is_inactive logic to include those dates using df.index.date or a calendar lookup.
• For more complex interpolation (e.g., considering bus route stops), you could add additional features like nearby stop locations and use a more advanced method, but time interpolation is a solid starting point for most GPS speed data.

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