Grouping Pandas df based on a specified START_DATE & END_DATE from a separate df

user_16539669 0
Viewed 42

I am working with two pandas dataframes that I am trying to groupby the same date ranges. I want to use this sample df that we can call 'hours' as a basis to set the START_DATE & END_DATE which I was able to do by just grouping by every 5 records by index. This is what the 'hours' dataframe looks like:

     HOURS   MIN_DATE   MAX_DATE
0    93.00 2021-01-05 2021-01-12
1   203.25 2021-01-13 2021-01-19
2   210.00 2021-01-20 2021-01-26
3   185.75 2021-01-27 2021-02-02
4   180.25 2021-02-03 2021-02-09
5   172.25 2021-02-10 2021-02-16

Then I have a separate df that I want to summarize with the same date ranges that I'll call 'models' which looks like this:

MODEL       DATE  MODEL_1  MODEL_2  MODEL_3  MODEL_4  MODEL_5  MODEL_6  
0     2021-01-05        0        2        0        0        0        0   
1     2021-01-06        0        0        0        0        3        0   
2     2021-01-07        0        0        0        0        0        0   
3     2021-01-13        3        0        0        0        0        0   
4     2021-01-14        0        0        1        1        1        0 
5     2021-01-15        0        0        0        0        0        0   
6     2021-01-20        0        0        0        0        0        1   
7     2021-01-21        0        3        0        0        0        1

I ultimately am looking for this result:

      MIN_DATE   MAX_DATE   MODEL_1  MODEL_2  MODEL_3  MODEL_4  MODEL_5  MODEL_6 
0   2021-01-05 2021-01-12         0        2        0        0        3        0
1   2021-01-13 2021-01-19         3        0        1        1        1        0
2   2021-01-20 2021-01-26         0        3        0        0        0        2

I haven't been able to find a way to use .groupby() on the 'models' data using the MIN_DATE & MAX_DATE from the 'hours' data. Is there a different operation I should be using or is there a way to use those dates to summarize the model data?

Thanks

pandas date-range dataframe python
2 Answers

Try using pd.IntervalIndex and groupby:

# First let's ensure that all DATE columns are datetime dtype:
hours_df[['MIN_DATE', 'MAX_DATE']] = hours_df[['MIN_DATE', 'MAX_DATE']].apply(pd.to_datetime)

model_df['DATE'] = pd.to_datetime(model_df['DATE'])

# Create IntervalIndex using from_arrays
hours_df['interval'] = pd.IntervalIndex.from_arrays(hours_df['MIN_DATE'], hours_df['MAX_DATE'], closed='both')

#set 'interval' as index of hours_df
hours_df = hours_df.set_index('interval')

# groupby and sum
model_df.groupby(hours_df.loc[model_df['DATE']].index).sum()

Output:

                          MODEL_1  MODEL_2  MODEL_3  MODEL_4  MODEL_5  MODEL_6
interval                                                                      
[2021-01-05, 2021-01-12]        0        2        0        0        3        0
[2021-01-13, 2021-01-19]        3        0        1        1        1        0
[2021-01-20, 2021-01-26]        0        3        0        0        0        2
user_63615310

Try:

# convert the columns first:
df1["MIN_DATE"] = pd.to_datetime(df1["MIN_DATE"])
df1["MAX_DATE"] = pd.to_datetime(df1["MAX_DATE"])
df2["DATE"] = pd.to_datetime(df2["DATE"])

# convert min_date/max_date to datarange
df1["tmp"] = df1.apply(
    lambda x: pd.date_range(x["MIN_DATE"], x["MAX_DATE"]), axis=1
)
# explode + save the index to column "index"
df1 = df1.explode("tmp").reset_index()

# merge + groupby on the saved index
print(
    df2.merge(df1, left_on="DATE", right_on="tmp")
    .groupby("index")
    .agg(
        {
            "MIN_DATE": "min",
            "MAX_DATE": "max",
            **{f"MODEL_{i}": "sum" for i in range(1, 7)},
        }
    )
)

Prints:

        MIN_DATE   MAX_DATE  MODEL_1  MODEL_2  MODEL_3  MODEL_4  MODEL_5  MODEL_6
index                                                                            
0     2021-01-05 2021-01-12        0        2        0        0        3        0
1     2021-01-13 2021-01-19        3        0        1        1        1        0
2     2021-01-20 2021-01-26        0        3        0        0        0        2
user_100359850
Related