Bin column by time intervals and calculate the sum

Question

I have the pandas DataFrame:

df = pd.DataFrame({
    "computer": [0, 1, 2, 3, 0, 1, 2, 3],
    "time":     [0, 0, 1, 1, 2, 3, 4, 5],
    "value":    [4, 1, 5, 6, 1, 0, 3, 4],
})
df["time"] = pd.to_datetime(df["time"], unit="s")
df.set_index(["computer", "time"])

I would like to bin time column into 4s intervals and sum the values. The output would look like:

df = pd.DataFrame({
    "computer":   [0, 1, 2, 3, 2, 3],
    "start_time": [0, 0, 0, 0, 4, 4],
    "value":      [5, 1, 5, 6, 3, 4],
})
df["start_time"] = pd.to_datetime(df["start_time"], unit="s")
df.set_index(["computer", "start_time"])

I've tried to apply the code provided by Pandas group by time windows, but I'm unable to get it working due to the multiindex. I've also tried the resample method, but the same problem applies.

score 1 · Answer 1 · answered Jan 16 '19 at 21:08

Use, reset_index, then groupby and resample:

df.reset_index(level=0).groupby('computer').resample('4s')['value']\
   .sum().to_frame().sort_index(level=1)

OR

df.reset_index('computer').groupby('computer').resample('4s')['value']\
   .sum().to_frame().sort_index(level=1)

Output:

                              value
computer time                      
0        1970-01-01 00:00:00      5
1        1970-01-01 00:00:00      1
2        1970-01-01 00:00:00      5
3        1970-01-01 00:00:00      6
2        1970-01-01 00:00:04      3
3        1970-01-01 00:00:04      4

score 1 · Accepted Answer · answered Jan 16 '19 at 21:14

Use groupby with pd.Grouper:

u = (df.groupby(['computer', pd.Grouper(key='time', freq='4s')])
       .sum()
       .sort_index(level=1)
       .reset_index())
u['time'] = u['time'].dt.second

u
   computer  time  value
0         0     0      5
1         1     0      1
2         2     0      5
3         3     0      6
4         2     4      3
5         3     4      4

Instead of having a separate groupby and resample, you can allow one groupby call to handle both the grouping on "computer", and the resampling "time" together.

Bin column by time intervals and calculate the sum

2 Answers2