Python, how to average month from 15th to 15th of each month, or nth to nth

Question

I have a pandas dataframe that looks like below with a datetime column and a value column

timestamp	value
1/1/2023	100
1/2/2023	90
....
12/12/2023	85

i know how to group from month to month, but looking for something a bit more nuanced to calculate the average monthly value but from the 15th of one month to the 15th of the next month, looking something like below

timestamp	mean_value
1/15/2023 - 2/15/2023	95
2/15/2023 - 3/15/2023	93
....
11/15/2023 - 12/15/2023	84

I tried grouping from month to month and shifting a time column by 15, but this isnt exact since every month has a different amount of days..

Answer 1

I believe either of these should work:

(df.groupby(df['date'].dt.day.eq(15).cumsum(),as_index=False)
 .agg({'date':lambda x: '{} - {}'.format(*x.dt.strftime('%m/%d/%Y').iloc[[0,-1]].tolist()),
       'value':'mean'}))

or

(df.groupby((df['date'] - pd.to_timedelta('14D')).dt.to_period('M'),as_index=False)
 .agg({'date':lambda x: '{} - {}'.format(*x.dt.strftime('%m/%d/%Y').iloc[[0,-1]].tolist()),
       'value':'mean'}))