如何用Python+Excel按行政区(Borough)计算对应房价均值？

不管是处理Excel中的现有数据，还是你生成的模拟数据集，都可以用Pandas的groupby方法快速按行政区分组计算房价均值，步骤如下：

一、处理Excel中的数据 #

你的Excel文件已经包含Price和Borough列，直接读取后分组计算即可，简化后的代码如下：

import pandas as pd

# 读取Excel数据
file_path = "/Users/my_name/Documents/Startup Ideas/Python Data /file.xlsx"
df = pd.read_excel(file_path, sheet_name="Sheet1")

# 按行政区分组，计算各行政区房价均值
borough_price_mean = df.groupby("Borough")["Price"].mean()

# 可选：按均值从高到低排序，让结果更直观
borough_price_mean_sorted = borough_price_mean.sort_values(ascending=False)

# 打印结果
print(borough_price_mean_sorted)

如果需要确保你的指定行政区列表中的所有区域都出现在结果里（哪怕该区域在Excel中没有数据），可以用reindex补充：

BOROUGHS = ["Chelsea", "Kensington", "Westminster", "Pimlico", "Bank", "Holborn", "Camden", "Islington", "Angel", "Battersea", "Knightsbridge", "Bermondsey", "Newham"]

# 重新索引，确保所有指定行政区都被包含
borough_price_mean_full = borough_price_mean.reindex(BOROUGHS)
print(borough_price_mean_full)

二、处理你生成的模拟数据集data3 #

模拟数据集已经自带Borough列，直接用同样的groupby逻辑即可：

import pandas as pd
import numpy as np
import random

SIZE = 70_000
BOROUGHS = ["Chelsea", "Kensington", "Westminster", "Pimlico", "Bank", "Holborn", "Camden", "Islington", "Angel", "Battersea", "Knightsbridge", "Bermondsey", "Newham"]

np.random.seed(1)
data3 = pd.DataFrame({
    "Sq. feet" : np.random.randint(low=75, high=325, size=SIZE),
    "Price" : np.random.randint(low=200000, high=1250000, size=SIZE),
    "Borough" : [random.choice(BOROUGHS) for _ in range(SIZE)]
}) 

# 计算各行政区房价均值
data3_borough_mean = data3.groupby("Borough")["Price"].mean().sort_values(ascending=False)
print(data3_borough_mean)

补充说明 #

• groupby("Borough")["Price"].mean()的逻辑是：先按Borough列的值分组，然后对每个组的Price列计算平均值。
• 如果需要将结果转换为DataFrame格式方便后续处理或导出，可以用.reset_index()：

  borough_mean_df = borough_price_mean_sorted.reset_index()
  borough_mean_df.columns = ["Borough", "Average Price"]  # 重命名列名
  

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