如何为DataFrame补充缺失小时的0计数行？

Got it, let's fix this up for you. The goal is to fill in every missing hour (0-23) for each date in your DataFrame, setting count to 0 where there's no data. Here are two solid approaches using pandas:

Approach 1: Use MultiIndex & Reindex (Most Efficient) #

This method is fast and scalable, especially if you're working with large datasets:

1. First, ensure your date column is a datetime type (critical for consistent date handling):

import pandas as pd

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

2. Generate a complete set of (date, hour) pairs for all unique dates and 0-23 hours:

# Get all unique dates from your data
unique_dates = df['date'].unique()
# Create a range of all 24 hours
all_hours = range(24)

# Build a full MultiIndex combining every date with every hour
full_index = pd.MultiIndex.from_product(
    [unique_dates, all_hours],
    names=['date', 'hour']
)

3. Reindex your original DataFrame to match this full index, filling missing count values with 0:

# Set the original DataFrame's index to (date, hour)
df_full = df.set_index(['date', 'hour'])
# Reindex to the full set of pairs, fill missing counts with 0
df_full = df_full.reindex(full_index, fill_value=0).reset_index()

Approach 2: Group by Date & Apply a Fill Function (More Intuitive) #

If you prefer a more readable, step-by-step approach, group each date and fill in missing hours individually:

import pandas as pd

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

def fill_missing_hours(group):
    # Create a DataFrame with all 24 hours
    full_hours_df = pd.DataFrame({'hour': range(24)})
    # Merge with the group's data, keeping all hours
    merged = pd.merge(full_hours_df, group, on='hour', how='left')
    # Fill missing count values with 0, and preserve the date
    merged['count'] = merged['count'].fillna(0)
    merged['date'] = group['date'].iloc[0]
    return merged

# Apply the function to each date group, then clean up the index
df_full = df.groupby('date').apply(fill_missing_hours).reset_index(drop=True)

Both methods will give you the exact output you want: every date will have rows for hours 0 through 23, with count set to 0 where no data existed originally.

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