如何为Pandas DataFrame补全缺失时间戳并填充默认值-1？

Got it, let's work through this problem—this is a really common task when cleaning time-series data with pandas. Here's a straightforward, step-by-step solution:

Step 1: Ensure your timestamp column is a datetime type #

First, you need to make sure pandas recognizes your timestamp column as a proper datetime object. If it's stored as a string or Unix timestamp, convert it first:

import pandas as pd

# If your timestamp is a string (e.g., "2024-05-20 12:00:00")
df['timestamp'] = pd.to_datetime(df['timestamp'])

# If it's a Unix timestamp (seconds since epoch)
df['timestamp'] = pd.to_datetime(df['timestamp'], unit='s')

Step 2: Generate the full continuous timestamp sequence #

Next, create a complete sequence of timestamps with exactly 60-second intervals, spanning from the earliest to the latest timestamp in your original data:

# Grab the first and last timestamps from your data
start_time = df['timestamp'].min()
end_time = df['timestamp'].max()

# Generate the full sequence (freq='60S' means 60-second intervals)
full_timestamps = pd.date_range(start=start_time, end=end_time, freq='60S')

Step 3: Fill in missing rows and populate missing values with -1 #

You have two simple ways to do this—pick whichever fits your workflow better:

Option 1: Use reindex (cleaner if your timestamp is the index) #

# Set the original DataFrame's index to the timestamp column
df = df.set_index('timestamp')

# Reindex to the full sequence, filling missing values with -1
df_full = df.reindex(full_timestamps, fill_value=-1)

# If you want the timestamp back as a column instead of index
df_full = df_full.reset_index().rename(columns={'index': 'timestamp'})

Option 2: Use merge (great if you want to keep the original structure) #

# Convert the full timestamp sequence to a DataFrame
full_df = pd.DataFrame({'timestamp': full_timestamps})

# Left join with your original data to preserve all timestamps
df_full = pd.merge(full_df, df, on='timestamp', how='left')

# Fill all missing fields with -1
df_full = df_full.fillna(-1)

Quick Bonus Tips #

• If your original data has duplicate timestamps, add df = df.drop_duplicates(subset='timestamp') before generating the full sequence to avoid issues.
• If you need the final timestamp column to be a Unix timestamp (integer) instead of datetime, convert it back with:

  df_full['timestamp'] = df_full['timestamp'].astype(int) // 10**9  # Convert to seconds since epoch
  

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