如何高效优化Pandas按时间小时分组计算均值的代码？

Hey Andrea, your current code gets the job done, but we can make it way faster and cleaner by leaning into Pandas' built-in datetime handling and vectorized operations—no more nested loops or manual sum/count calculations!

What's Slow About Your Current Code? #

Your approach uses nested loops over unique days and hours, and repeatedly calculates group sums and value counts for each hour. This leads to redundant computations and Python-level loops, which are slow compared to Pandas' optimized C-backed operations, especially with large datasets.

Optimized Solution #

Here's a streamlined approach that leverages Pandas' datetime tools and groupby to do all the heavy lifting efficiently:

import pandas as pd

# Step 1: Combine date and time columns into a single datetime object (critical for easy time manipulation)
df['datetime'] = pd.to_datetime(df[0] + ' ' + df[1])

# Step 2: Extract the date and formatted hour (e.g., '00:00:00') as grouping keys
df['Day'] = df['datetime'].dt.date
df['Timetag'] = df['datetime'].dt.hour.astype(str).str.zfill(2) + ':00:00'

# Step 3: Group by Day and Timetag, then calculate the mean of columns 2 (Temp) and 3 (Hum)
dfObj = df.groupby(['Day', 'Timetag'])[[2, 3]].mean().reset_index()

# Rename columns to match your desired output
dfObj.columns = ['Day', 'Timetag', 'Temp', 'Hum']

Optional: Fill Missing Hours (If Needed) #

If you need to ensure every day has all 24 hours (even hours with no data, filled with NaN or a default value), add this step:

# Generate a complete list of 24-hour timetags
all_hours = [f'{hour:02d}:00:00' for hour in range(24)]

# Create a multi-index of all unique days + all 24 hours
full_index = pd.MultiIndex.from_product(
    [df['Day'].unique(), all_hours],
    names=['Day', 'Timetag']
)

# Reindex the grouped dataframe to fill in missing hours
dfObj = dfObj.set_index(['Day', 'Timetag']).reindex(full_index).reset_index()

# Optional: Replace NaNs with a default value (e.g., 0 or keep NaN as is)
# dfObj = dfObj.fillna(0)

Why This Is Better #

• Speed: Pandas' groupby and datetime operations are vectorized and optimized in C—this will run orders of magnitude faster than your nested loops, especially with large datasets.
• Simplicity: No more manual sum/count calculations (.mean() handles that automatically) or checking if hours exist.
• Maintainability: The code is shorter, easier to read, and simpler to modify (e.g., adding median or min/max stats is just changing .mean() to another aggregation function).

Performance Comparison #

For a dataset with 100k rows:

• Your original code might take several seconds (or minutes for larger data)
• The optimized version will finish in milliseconds

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