How to average based on data range in a difference table in Pandas

Question

With two tables, Values and dates, I would like to get the average value between the date ranges. Values looks like:

Date	Value
2023-01-01 10:00	1
2023-01-01 11:00	2
2023-01-02 10:00	4
2023-01-04 10:00	4
2023-01-07 10:00	4

and dates looks like

Group	StartDay	EndDay
1	2023-01-01	2023-01-05
2	2023-01-03	2023-01-10

As you can see, the date ranges can overlap.

I am trying to calculate the averages over these ranges, so in this example the output should be something along the lines of

Group	StartDay	EndDay	Mean
1	2023-01-01	2023-01-05	2.75
2	2023-01-03	2023-01-10	4

Currently my code looks like (all one line):

Values.groupby(np.where(Values['Date'].between(Dates['StartDay'],Dates['EndDay']),'pre','post'))['value'].mean()

however this results in ValueError: Can only compare identically-labeled Series objects

This was based on other similar questions, however does not appear to apply here due to it being over two tables / using ranges.

score 3 · Accepted Answer · answered Feb 23 '23 at 15:07

Try:

# convert the values to datetime (if not already):
df1['Date'] = pd.to_datetime(df1['Date'])
df2['StartDay'] = pd.to_datetime(df2['StartDay'])
df2['EndDay'] = pd.to_datetime(df2['EndDay'])

df1 = df1.sort_values(by='Date').set_index('Date')

df2['Mean'] = df2.apply(lambda x: df1[x['StartDay']:x['EndDay']].mean(), axis=1)
print(df2)

Prints:

   Group   StartDay     EndDay  Mean
0      1 2023-01-01 2023-01-05  2.75
1      2 2023-01-03 2023-01-10  4.00

sammywemmy · Answer 2 · 2023-02-23T22:40:14.530

Looks like some form of inequality join - if that is the case, you can use conditional_join from pyjanitor to get your results, before grouping - should be faster than apply, depending on the data size:

# pip install pyjanitor
import pandas as pd
import janitor

df1 = pd.read_clipboard(sep="\t", engine='python', parse_dates = ['Date'])
df2 = pd.read_clipboard(sep="\t", engine='python', parse_dates = ['StartDay', 'EndDay'])

(df1
.conditional_join(
    df2, 
    # column from left, column from right, comparator
    ('Date', 'StartDay', '>='),  
    ('Date', 'EndDay', '<='), 
    # depending on the data size,
    # you might get more performance with numba
    use_numba = False,
    df_columns = 'Value')
.groupby(df2.columns.tolist())
.Value
.mean()
)

Group  StartDay    EndDay    
1      2023-01-01  2023-01-05    2.75
2      2023-01-03  2023-01-10    4.00
Name: Value, dtype: float64

How to average based on data range in a difference table in Pandas

2 Answers2