import pandas as pd import numpy as np import math import matplotlib.pyplot as plt

读取数据

data = pd.read_excel(r"D:\pythonProject3\新数据第四问\商品数据.xlsx")

划分网格

min_lat = data['商品GPS纬度'].min() max_lat = data['商品GPS纬度'].max() min_lon = data['商品GPS经度'].min() max_lon = data['商品GPS经度'].max()

lat_grid = np.arange(math.floor(min_lat), math.ceil(max_lat)+0.05, 0.05) lon_grid = np.arange(math.floor(min_lon), math.ceil(max_lon)+0.05, 0.05)

统计每个网格内的商品数量

data['网格编号'] = pd.cut(data['商品GPS纬度'], lat_grid, labels=False) * 1000 + pd.cut(data['商品GPS经度'], lon_grid, labels=False) grid_count = data['网格编号'].value_counts().reset_index() grid_count.columns = ['网格编号', '新商品数量']

计算网格中心

grid_center = pd.DataFrame() grid_center['网格编号'] = grid_count['网格编号'] grid_center['商品GPS纬度'] = grid_center['网格编号'].apply(lambda x: lat_grid[int(x/1000)] + 0.025) grid_center['商品GPS经度'] = grid_center['网格编号'].apply(lambda x: lon_grid[int(x%1000)] + 0.025)

生成新的商品编号

grid_center['新商品编号'] = range(1, len(grid_center)+1)

合并结果

result = pd.merge(grid_center, grid_count, on='网格编号')

可视化

plt.scatter(data['商品GPS经度'], data['商品GPS纬度'], alpha=0.5, label='原始商品') plt.scatter(result['商品GPS经度'], result['商品GPS纬度'], c='red', label='新商品中心') plt.legend() plt.xlabel('商品GPS经度') plt.ylabel('商品GPS纬度') plt.title('商品分布图') plt.show()

输出结果到Excel

print(result) #result.to_excel(r"D:\pythonProject3\新数据\商品打包结果.xlsx", index=False)