如何用Pandas按40ms窗口聚合时序数据？支持多列不同聚合吗？

Great question! Let's break this down into actionable steps using Pandas, covering both the uniform mean aggregation and custom per-column functions.

Step 1: Get Your Data Ready First #

First, you need to ensure your timestamp column is properly parsed as a datetime type—this is critical for time-based grouping. Here's how to load your CSV correctly:

import pandas as pd
import numpy as np

# Load the CSV, parsing the timestamp column as datetime
df = pd.read_csv("your_data.csv", parse_dates=["Timestamp"])

# If your timestamp column wasn't parsed automatically, convert it manually:
# df["Timestamp"] = pd.to_datetime(df["Timestamp"], format="%Y-%m-%d %H:%M:%S.%f")

Step 2: Aggregate Col2-Col13 with 40ms Window Mean #

To group your data into 40-millisecond windows and calculate the mean (ignoring nulls) for Col2 through Col13, you have two straightforward options:

Option A: Use resample (if timestamp is your DataFrame index) #

If you set the timestamp as your index, resample is clean and efficient:

# Set timestamp as index
df.set_index("Timestamp", inplace=True)

# Resample into 40ms windows and compute mean for Col2-Col13
mean_result = df["Col2":"Col13"].resample("40ms").mean(skipna=True)

Option B: Use pd.Grouper (no index required) #

If you prefer to keep the timestamp as a regular column, use pd.Grouper to define the time window:

# Group by 40ms windows and calculate mean for Col2-Col13
mean_result = df.groupby(pd.Grouper(key="Timestamp", freq="40ms"))["Col2":"Col13"].mean(skipna=True)

Note: skipna=True is the default for mean(), but I've included it explicitly to match your requirement of ignoring nulls.

Step 3: Apply Different Aggregation Functions Per Column #

Absolutely, you can mix and match aggregation functions for different columns using a dictionary with agg(). For example, if you want Col2 to use mode, Col5 to use mean, Col7 to use median, here's how to set it up:

First, define your aggregation rules. For mode, we need a small lambda to handle edge cases (like multiple modes or all null values):

aggregation_rules = {
    "Col2": lambda x: x.mode().iloc[0] if not x.mode().empty else np.nan,
    "Col5": "mean",
    "Col7": "median",
    # Add other columns here with their desired functions
    "Col3": "max",
    "Col4": "min",
    "Col6": "mean",
    # ... include Col8 to Col13 as needed
}

Then apply the grouping with these rules:

custom_result = df.groupby(pd.Grouper(key="Timestamp", freq="40ms")).agg(aggregation_rules)

The lambda for mode ensures we get a single value (taking the first mode if there are multiple) and returns NaN if all values in the window are null.

Quick Notes to Remember #

• Double-check your timestamp format: If Pandas can't parse it automatically, use pd.to_datetime() with the correct format string (e.g., format="%Y-%m-%d %H:%M:%S.%f" for microsecond timestamps).
• freq="40ms" is a valid Pandas offset alias—this tells Pandas to create windows every 40 milliseconds.
• For irregular time series (where timestamps aren't evenly spaced), groupby(pd.Grouper(...)) is more reliable than resample, which assumes regular intervals.

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