如何在Pandas中为工作日时序数据补充缺失时间戳行？

Great question! Handling this kind of constrained irregular time series can feel tedious with manual loops, but pandas has some built-in tools that make this way cleaner. Here's a streamlined approach that avoids messy date checks and repetitive code:

Step 1: Preprocess the Original Data #

First, make sure your datetime column is properly typed (pandas datetime64), which is essential for time-based operations:

import pandas as pd

# Your sample data
raw_data = {
    'date time': [
        '2020-07-30 10:00:00', '2020-07-30 14:00:00',
        '2020-07-31 10:00:00', '2020-07-31 14:00:00', '2020-07-31 18:00:00',
        '2020-08-03 14:00:00', '2020-08-04 14:00:00'
    ],
    'value': [5, 3, 6, 4.5, 7, 5.5, 5]
}

df = pd.DataFrame(raw_data)
df['date time'] = pd.to_datetime(df['date time'])

Step 2: Generate the Complete Target Timestamp Series #

Instead of manually looping through dates and checking weekends, use pandas' bdate_range (business date range) to automatically get only weekdays. Then combine these dates with your fixed target hours:

# Get the full date range from your data
start_date = df['date time'].dt.date.min()
end_date = df['date time'].dt.date.max()

# Generate all weekdays in the range (no weekends included!)
business_days = pd.bdate_range(start=start_date, end=end_date)

# Your fixed measurement hours
target_hours = [10, 14, 18]

# Create the full list of required timestamps
full_timestamps = [
    pd.Timestamp(f"{day} {hour}:00:00") 
    for day in business_days 
    for hour in target_hours
]

Step 3: Merge and Fill Missing Values #

Create a base DataFrame with the full timestamp list, then merge it with your original data to fill in existing values and leave missing ones as NaN:

# Create empty DataFrame with all required timestamps
full_df = pd.DataFrame({'date time': full_timestamps})

# Merge with original data to populate values
result_df = pd.merge(full_df, df, on='date time', how='left')

What This Does #

• pd.bdate_range eliminates the need to manually check for weekends—it only returns Monday-to-Friday dates.
• The list comprehension for full_timestamps is lightweight and readable, combining each weekday with your three fixed hours.
• The left merge ensures every required timestamp is present, with NaN filling in where no measurement existed in the raw data.

Result Preview #

The result_df will exactly match your desired output:

This approach is far more maintainable than manual loops—if you ever need to adjust the target hours or date range, you only need to tweak a few lines of code.

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