I have some transaction data that after grouping by date and FacilityID that looks like the below after the grouping. I’m trying to calculate the quarter over quarter change, so the sum of the total spend of all of the Facilities, when they have spend in all 3 months of both the current quarter and the 3 months of the prior year quarter. So in this example, I would just want the sum of spend for Facility #1, for April-June 2024 over Facility #1 April-June 2023 sum of total spend to get the change. Facility 2 should be excluded because it doesn’t have any spend in April of 2023 or 2024.

This is the code I’ve tried so far, but it’s including Facility 2 in the code as well, when it should be excluded since it doesn’t have any data in April 2024 and 2023.

import pandas as pd
import datetime

def open_file(path, quarter_number, months):
    df_raw = pd.DataFrame({'Date':["2024-04-01","2024-05-01","2024-06-01", "2024-06-01","2024-05-01","2023-04-01","2023-05-01","2023-06-01","2024-05-01","2024-06-01","2023-05-01","2023-06-01", "2023-04-01","2024-05-01","2024-06-01"],
                         'FacilityID': [1,1,1,1,1,1,1,1,2,2,2,2,3,4,4],
                         'TotalSpend': [100,110,120,50,70,90,100,110,150,140,120,60,90,190,150]
    }).set_index('Date')
    df = df_raw.groupby(['Date', 'FacilityID'])['TotalSpend'].sum()
    print(df)

    cur_dates = []
    prev_dates = []

    for month in months:
        cur_date = datetime.date(2024, month, 1)
        prev_date = datetime.date(cur_date.year - 1, month, 1)
        cur_dates.append(cur_date.strftime('%Y-%m-%d'))
        prev_dates.append(prev_date.strftime('%Y-%m-%d'))

    cur_quarter_data = pd.concat(
        [df.loc[date] if date in df.index.levels[0] else pd.Series(dtype='float64') for date in cur_dates])

    prev_quarter_data = pd.concat(
        [df.loc[date] if date in df.index.levels[0] else pd.Series(dtype='float64') for date in prev_dates])

    common_facilities = cur_quarter_data.index.intersection(prev_quarter_data.index)


    cur_quarter_vals = cur_quarter_data.loc[common_facilities]
    prev_quarter_vals = prev_quarter_data.loc[common_facilities]

    yoy_change = (cur_quarter_vals.sum() - prev_quarter_vals.sum()) / prev_quarter_vals.sum() * 100
    return yoy_change

if __name__ == "__main__":
    change = open_file("path",2 ,[4,5,6])
    print(change)