遍历Dataframe计算指定Symbol每5分钟最小值并导出为CSV

Got it, let's break this down and build a solution that fits your needs. We'll use pandas to handle the DataFrame manipulation—its built-in tools are perfect for time-based grouping and aggregation tasks like this.

Step 1: Load and Prepare the Data #

First, let's get your sample data into a properly formatted pandas DataFrame, and make sure the Time column is parsed as datetime (this is critical for accurate time window grouping):

import pandas as pd

# Your input raw data
raw_data = [
    ["aaa", "2018-09-21 11:01:00", "saturday", 1, 2],
    ["aaa", "2018-09-21 11:02:00", "saturday", 2, 5],
    ["aaa", "2018-09-21 11:03:00", "saturday", 5, 1],
    ["aaa", "2018-09-21 11:04:00", "saturday", 7, 3],
    ["aaa", "2018-09-21 11:05:00", "saturday", 6, 4],
    ["aaa", "2018-09-21 11:06:00", "saturday", 2, 2],
    ["aaa", "2018-09-21 11:07:00", "saturday", 3, 5],
    ["aaa", "2018-09-21 11:08:00", "saturday", 2, 2],
    ["aaa", "2018-09-21 11:09:00", "saturday", 2, 3],
    ["aaa", "2018-09-21 11:09:15", "saturday", 2, 5],
    ["aaa", "2018-09-21 11:10:00", "saturday", 2, 3]
]

# Create DataFrame and parse the Time column as datetime
df = pd.DataFrame(raw_data, columns=["symbol", "Time", "Day", "x_value", "y_value"])
df["Time"] = pd.to_datetime(df["Time"])

Step 2: Filter for Your Target Symbol #

Next, we'll narrow down the DataFrame to only include rows where the symbol matches your target (in this case, "aaa"):

target_symbol = "aaa"
filtered_df = df[df["symbol"] == target_symbol].copy()

Step 3: Group into 5-Minute Windows and Aggregate #

This is the core part of the task. We'll use pandas' resample function to group the data into 5-minute intervals, then calculate all the metrics you need: minimum values for x_value and y_value, plus row count, window start/end times, and symbol/day info.

# Set Time as the index (required for the resample function)
filtered_df = filtered_df.set_index("Time")

# Aggregate data over 5-minute windows ("5T" is shorthand for 5 minutes)
aggregated_data = filtered_df.resample("5T").agg(
    symbol=("symbol", "first"),  # Keep the symbol (same for all rows in the window)
    day=("Day", "first"),        # Retain the day value
    start=("Time", lambda x: x.min().time().strftime("%H:%M:%S")),  # Window start time
    end=("Time", lambda x: x.max().time().strftime("%H:%M:%S")),    # Window end time
    count=("symbol", "count"),   # Number of rows in the window
    x_value=("x_value", "min"),  # Minimum x_value in the window
    y_value=("y_value", "min")   # Minimum y_value in the window
).reset_index(drop=True)

Step 4: Export to CSV #

Finally, we'll save the aggregated result to a CSV file that matches the structure you provided:

aggregated_data.to_csv("5min_window_aggregation.csv", index=False)

Sample Output #

Running this code will generate a CSV with two rows (your data spans two 5-minute windows: 11:01–11:05 and 11:06–11:10):

symbol,day,start,end,count,x_value,y_value
aaa,saturday,11:01:00,11:05:00,5,1,1
aaa,saturday,11:06:00,11:10:00,6,2,2

Quick note: If your expected output was meant to show sums instead of minima (since your sample expected output has x_value=13, which is the sum of the second window's x values), just replace "min" with "sum" in the aggregation function for x_value and y_value. That will give you total values per window instead of the lowest values.

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