Pandas新手求助：如何按5秒间隔对时间序列透视表各列求和？

Hey there! Let's get this sorted out for you—since you're new to Pandas, this is a straightforward grouping task once you know the right approach. Here's how you can transform your pivot table to sum every 5 rows per column, while keeping the first timestamp of each group as your index:

Step 1: Import Required Libraries #

First, make sure you have these imported (you probably already do, but just in case):

import pandas as pd
import numpy as np

Step 2: Group and Sum Your Data #

Assuming your existing pivot table is stored in a DataFrame called df, use this code to create your desired output:

# Create a grouping key that groups every 5 rows together
group_indices = np.arange(len(df)) // 5

# Group by the key and calculate column sums
summarized_df = df.groupby(group_indices).sum()

# Replace the default group index with the first timestamp from each original group
summarized_df.index = df.iloc[::5].index

# Optional: Round values to match the decimal precision in your example
summarized_df = summarized_df.round(2)

How This Works #

Let's break down what each part does:

• np.arange(len(df)) // 5: Generates a sequence like [0,0,0,0,0,1,1,1,1,1,...]—this acts as a label to group every 5 consecutive rows together.
• df.groupby(group_indices).sum(): Groups the DataFrame using the label sequence and calculates the sum of each column for every group.
• df.iloc[::5].index: Picks the first timestamp (from your detected_at index) of each 5-row group, replacing the generic group numbers with meaningful time labels.
• round(2): Adjusts the decimal places to match the format in your desired output (feel free to adjust the number if you need more/less precision).

Example Verification #

For your first 5 rows (08:00:00 to 08:00:04), summing column 6 gives:
-86.5 + (-78.5) + (-82.75) + (-83.333333) + (-87) = -418.083333
Rounding to 2 decimals gives -418.08, which is very close to your example's -418.05 (likely a minor difference in original data precision).

This method will work seamlessly even if your time series has more rows—just keep extending your DataFrame, and the grouping will automatically handle it.

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