基于时间索引的Pandas DataFrame特定时间区间均值计算求助

Got it, let's break down how to solve this problem step by step. Here's a practical implementation that fits your exact requirements:

Step 1: Parse Input Time Range & Generate 5-Minute Intervals #

First, we'll take your input time range (like "17:00-18:00"), split it into start/end times, then generate all consecutive 5-minute intervals within that window.

import pandas as pd

def generate_time_intervals(input_time):
    # Split the input string into start and end time components
    start_str, end_str = input_time.split("-")
    # Convert to datetime objects (using a dummy date since we only care about time)
    start = pd.to_datetime(start_str, format="%H:%M")
    end = pd.to_datetime(end_str, format="%H:%M")
    
    # Generate all 5-minute interval start times
    interval_starts = pd.date_range(start=start, end=end, freq="5min")
    # Create human-readable interval labels like "17:00 - 17:05"
    interval_labels = [
        f"{t.strftime('%H:%M')} - {(t + pd.Timedelta(minutes=5)).strftime('%H:%M')}" 
        for t in interval_starts[:-1]
    ]
    return interval_labels, interval_starts

Step 2: Prepare Resampled Data for Time-Based Grouping #

Your resampled DataFrame includes dates in the index. We'll extract just the time component, then calculate the average points for each 5-minute time slot across all dates.

# Assuming your pre-resampled DataFrame is named `resampled_df`
# Add a column for the time slot (e.g., "17:40") extracted from the timestamp index
resampled_df["time_slot"] = resampled_df.index.strftime("%H:%M")
# Calculate average points per time slot (aggregating across all dates)
time_slot_avg = resampled_df.groupby("time_slot")["points"].mean().round(1)

Step 3: Map Generated Intervals to Average Points #

Now we'll match each generated interval to its corresponding average, filling missing intervals with - as requested.

def get_interval_averages(input_time, time_slot_avg):
    interval_labels, interval_starts = generate_time_intervals(input_time)
    # Convert the time slot averages to a dictionary for quick lookup
    avg_lookup = time_slot_avg.to_dict()
    
    # Build the result list
    results = []
    for idx, label in enumerate(interval_labels):
        # Get the start time of the interval to match our lookup keys
        slot_key = interval_starts[idx].strftime("%H:%M")
        # Use the average if available, else "-"
        avg_points = avg_lookup.get(slot_key, "-")
        results.append({"interval": label, "points": avg_points})
    
    # Convert to a DataFrame for clean output
    return pd.DataFrame(results)

Step 4: Test with Your Sample Data #

Let's put it all together using the sample data you provided:

# Recreate your sample resampled DataFrame
resampled_data = {
    "timestamp": ["5/29/2017 17:40", "5/29/2017 17:45", "5/29/2017 17:50", 
                  "5/29/2017 17:55", "5/29/2017 18:00", "5/30/2017 17:30", 
                  "5/30/2017 17:35", "5/30/2017 17:40", "5/30/2017 17:45", 
                  "5/30/2017 17:50", "5/30/2017 17:55", "5/30/2017 18:00"],
    "points": [8, 1, 4, 3, 8, 3, 3, 7, 8, 5, 7, 1]
}
resampled_df = pd.DataFrame(resampled_data)
resampled_df["timestamp"] = pd.to_datetime(resampled_df["timestamp"])
resampled_df.set_index("timestamp", inplace=True)

# Run the pipeline with your input time range
input_time = "17:00-18:00"
final_result = get_interval_averages(input_time, time_slot_avg)

print(final_result)

Sample Output: #

interval points
0  17:00 - 17:05      -
1  17:05 - 17:10      -
2  17:10 - 17:15      -
3  17:15 - 17:20      -
4  17:20 - 17:25      -
5  17:25 - 17:30      -
6  17:30 - 17:35      3
7  17:35 - 17:40      3
8  17:40 - 17:45    7.5
9  17:45 - 17:50    4.5
10 17:50 - 17:55    4.5
11 17:55 - 18:00      5

Key Notes: #

• Missing time slots automatically get filled with - as you wanted.
• We rounded averages to 1 decimal place for readability—you can remove the round(1) if you need full precision.
• This approach works seamlessly across multiple dates, as it aggregates all values for the same 5-minute time slot.

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