如何正确计算全系统加权平均值？当前计算结果偏差过大

Hey there, let's break down why your weighted average results are coming out way too large, and how to fix them to get the correct customer_journey_time value (~0.698) you're expecting.

The Root Cause of the Issue #

Looking at your code, the mistake is in how you're computing the weighted averages. Right now, you first sum each time metric across all rows in the month group, then multiply that sum by the total number of customers and divide by the same total—this just leaves you with the original sum of the metric, not a weighted average.

For a proper weighted average (weighted by number_of_customers), you need to:

1. Calculate the weighted sum of each metric (each row's metric value multiplied by its number_of_customers, summed together)
2. Divide that weighted sum by the total number of customers in the group

Corrected Code #

First, your initial data processing steps are solid—no changes needed there. Here's the fixed version of your systemwide calculation code:

# Calculate systemwide weighted averages per month
cjtp_systemwide = cjtp_df.groupby(["month"]).agg(
    # Compute weighted sum for each metric: sum(metric * number_of_customers)
    weighted_bus_stop=("additional_bus_stop_time", lambda x: (x * cjtp_df.loc[x.index, "number_of_customers"]).sum()),
    weighted_travel_time=("additional_travel_time", lambda x: (x * cjtp_df.loc[x.index, "number_of_customers"]).sum()),
    weighted_journey_time=("customer_journey_time", lambda x: (x * cjtp_df.loc[x.index, "number_of_customers"]).sum()),
    total_customers=("number_of_customers", "sum")
)

# Compute the actual weighted averages
cjtp_systemwide["additional_bus_stop_time"] = cjtp_systemwide["weighted_bus_stop"] / cjtp_systemwide["total_customers"]
cjtp_systemwide["additional_travel_time"] = cjtp_systemwide["weighted_travel_time"] / cjtp_systemwide["total_customers"]
cjtp_systemwide["customer_journey_time"] = cjtp_systemwide["weighted_journey_time"] / cjtp_systemwide["total_customers"]

# Drop temporary weighted sum columns
cjtp_systemwide.drop(["weighted_bus_stop", "weighted_travel_time", "weighted_journey_time"], axis=1, inplace=True)

# Add systemwide metadata columns
cjtp_systemwide["borough"] = "Systemwide"
cjtp_systemwide["trip_type"] = "Systemwide"
cjtp_systemwide["route_id"] = "Systemwide"
cjtp_systemwide["period"] = "Systemwide"

# Reset index and combine with original dataframe
cjtp_systemwide.reset_index(inplace=True)
cjtp_df = pd.concat([cjtp_df, cjtp_systemwide])
cjtp_df.sort_values(by=["month", "borough", "trip_type"], ascending=[True, True, True], inplace=True)
cjtp = cjtp_df.reset_index(drop=True)

# Check the results
print(cjtp[cjtp["borough"] == "Systemwide"]["customer_journey_time"])

How This Works #

• The lambda functions in agg() calculate the weighted sum for each metric by multiplying each row's metric value by its corresponding customer count, then summing those products.
• We then divide each weighted sum by the total number of customers in the month group to get the true weighted average.
• After cleaning up temporary columns, we add the systemwide metadata and combine with the original data as you did before.

This should give you the customer_journey_time value close to 0.698 that you're expecting.

内容的提问来源于stack exchange，提问作者numa