Include one row in multiple groupby groups

Question

I am grouping a time series by hour to perform an operation on each hour of data separately:

import pandas as pd
from datetime import datetime, timedelta

x = [2, 2, 4, 2, 2, 0]
idx = pd.date_range(
    start=datetime(2019, 1, 1),
    end=datetime(2019, 1, 1, 2, 30),
    freq=timedelta(minutes=30),
)

s = pd.Series(x, index=idx)
hourly = s.groupby(lambda x: x.hour)

print(s)
print("summed:")
print(hourly.sum())

which produces:

2019-01-01 00:00:00    2
2019-01-01 00:30:00    2
2019-01-01 01:00:00    4
2019-01-01 01:30:00    2
2019-01-01 02:00:00    2
2019-01-01 02:30:00    0
Freq: 30T, dtype: int64
summed:
0    4
1    6
2    2
dtype: int64

As expected.

I now want to know the area under the time series per hour, for which I can use numpy.trapz:

import numpy as np

def series_trapz(series):
    hours = [i.timestamp() / 3600 for i in series.index]
    return np.trapz(series, x=hours)

print("Area under curve")
print(hourly.agg(series_trapz))

But for this to work correctly, the boundaries between the groups must appear in both groups!

For example, the first group must be:

2019-01-01 00:00:00    2
2019-01-01 00:30:00    2
2019-01-01 01:00:00    4

and the second group must be

2019-01-01 01:00:00    4
2019-01-01 01:30:00    2
2019-01-01 02:00:00    2

etc.

Is this at all possible using pandas.groupby?

Answer 1

I don't think that I have your np.trapz logic completely correct here, but I think you can probably get what you want with .rolling(..., closed="both") so that the endpoints of the intervals are always included:

In [366]: s.rolling("1H", closed="both").apply(np.trapz).iloc[::2]
Out[366]:
2019-01-01 00:00:00    0.0
2019-01-01 01:00:00    5.0
2019-01-01 02:00:00    5.0
Freq: 60T, dtype: float64

Answer 2

I think you could repeat the limit of groups in your serie using Series.repeat:

r=(s.index.minute==0).astype(int)+1
new_s=s.repeat(r)
print(new_s)

2019-01-01 00:00:00    2
2019-01-01 00:30:00    2
2019-01-01 01:00:00    4
2019-01-01 01:00:00    4
2019-01-01 01:30:00    2
2019-01-01 02:00:00    2
2019-01-01 02:00:00    2
2019-01-01 02:30:00    0

---

Then you could use Series.groupby:

groups=(new_s.index.to_series().shift(-1,fill_value=0).dt.minute!=0).cumsum()
for i,group in new_s.groupby(groups):
    print(group)
    print('-'*50)

Name: col1, dtype: int64
2019-01-01 00:00:00    2
2019-01-01 00:30:00    2
2019-01-01 01:00:00    4
Name: col1, dtype: int64
--------------------------------------------------
2019-01-01 01:00:00    4
2019-01-01 01:30:00    2
2019-01-01 02:00:00    2
Name: col1, dtype: int64
--------------------------------------------------
2019-01-01 02:00:00    2
2019-01-01 02:30:00    0
Name: col1, dtype: int64
--------------------------------------------------