如何在Pandas DataFrame中分组后累加毫秒数？

Hey there, let's work through this problem step by step. First, let's recap the data we're dealing with:

time                     key  isValue
2018-03-04 00:00:06.520  1    NaN
2018-03-04 00:00:07.230  1    NaN
2018-03-04 00:00:08.140  1    1
2018-03-04 00:00:08.720  1    1
2018-03-04 00:00:09.110  1    1
2018-03-04 00:00:09.650  1    NaN
2018-03-04 00:00:10.360  1    NaN
2018-03-04 00:00:11.150  1    NaN
2018-03-04 00:00:11.770  2    NaN
2018-03-04 00:00:12.320  2    NaN
2018-03-04 00:00:12.910  2    1
2018-03-04 00:00:13.250  2    1
2018-03-04 00:00:13.960  2    1
2018-03-04 00:00:14.550  2    NaN
2018-03-04 00:00:15.250  2    NaN

Your goal is to group by key, calculate the total milliseconds of the periods where isValue=1, and output in the format key : X.xxx. The code you tried (df.groupby(["key"])["time"].cumsum()) doesn't work because it's just adding raw timestamps together—this doesn't account for time differences or filter the rows where isValue is actually 1.

Correct Implementation #

Let's break this into clear steps:

1. Ensure the time column is datetime type

First, make sure Pandas recognizes your time column as datetime (if it isn't already):

import pandas as pd

# Convert time column to datetime
df['time'] = pd.to_datetime(df['time'])

2. Calculate total milliseconds for valid periods

Based on your sample output (where key=2 gives 1.050, which matches the time from the first isValue=1 to the last isValue=1 in that group), here's the code to compute the total duration of the valid segment for each key:

# Filter rows where isValue is 1, then group by key and calculate the time between first and last valid entry
result = df[df['isValue'] == 1].groupby('key')['time'].agg(
    lambda x: (x.iloc[-1] - x.iloc[0]).total_seconds() * 1000
).round(3)

If instead you want to sum all the small intervals between consecutive valid rows (e.g., between 00:00:08.140 and 00:00:08.720, plus 00:00:08.720 and 00:00:09.110), use this code instead:

result = df[df['isValue'] == 1].groupby('key')['time'].apply(
    lambda x: x.diff().dt.total_seconds().sum() * 1000
).round(3)

3. Output in your desired format

Print the results to match the key : X.xxx format:

for key, ms in result.items():
    print(f"{key} : {ms:.3f}")

Example Output #

For your sample data, the first method (segment duration) will give:

1 : 970.000
2 : 1050.000

Which aligns with your sample's 2 : 1.050 (since 1050ms = 1.05s).

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