如何在Pandas中重排DatetimeIndex并保留值，补全缺失小时为0

Got it, let's break down why your code is turning everything to NaN and fix it step by step. The core issue here is a mismatch in index data types—you're converting your DataFrame index to strings, but trying to reindex against datetime objects, so Pandas can't find any matching rows.

Here's the Corrected Code #

import pandas as pd
from datetime import datetime, timedelta

# Original data
data = {'date_time': ['2018-01-22 14:47:05.486877'], 'quantity': [31]}
df = pd.DataFrame(data, columns=['date_time', 'quantity'])

# Convert date_time to datetime and set as index (cleaner approach)
df['date_time'] = pd.to_datetime(df['date_time'])
df.set_index('date_time', inplace=True)

# Resample to hourly sums
df = df.resample('H').sum()

# Generate continuous hourly timestamps for the last 24 hours
current_date = datetime.now().replace(microsecond=0, second=0, minute=0)
d2 = current_date - timedelta(hours=24)
all_times = pd.date_range(d2, current_date, freq="H")

# Reindex with matching datetime index, fill missing values with 0 directly
df = df.reindex(all_times, fill_value=0)

print(df)

Key Fixes & Explanations #

• Removed the string conversion step: Your original code used df.index.map(lambda t: t.strftime('%Y-%m-%d %H:%M:%S')) to turn datetime indices into strings. But all_times is a datetime64[ns] range—Pandas can't match string indices to datetime objects, hence all NaNs. Keeping the index as datetime is critical here.
• Simplified index setup: Using set_index instead of manually assigning the index and deleting the column makes the code cleaner and less error-prone.
• One-step fill during reindex: Added fill_value=0 directly to the reindex call, so you don't need a separate fillna(0) line.

Quick Check to Verify #

If you ever run into similar issues, double-check the data types of your indices with:

print(df.index.dtype)
print(all_times.dtype)

Both should show datetime64[ns] to ensure proper matching.

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