如何计算表内各地点间距离并统计符合条件的数量（支持Power BI/Python）

原始数据 #

需求说明 #

计算每个地点与其他所有地点的直线距离（采用Haversine公式，单位为公里），统计距离大于指定阈值（示例为151公里）的地点数量，将统计结果作为新列Bigger Than 151添加到表格中，最终输出如下：

实现方案 #

方案1：Power Query（M语言） #

1. 把原始数据导入Power Query，先确认Latitude和Longitude列是数值类型，不是的话转成数值。
2. 添加自定义列，命名为Bigger Than 151，粘贴以下M代码：

let
    CurrentPlace = [Place],
    CurrentLat = [Latitude] * Number.PI/180,
    CurrentLon = [Longitude] * Number.PI/180,
    // 计算所有地点与当前地点的距离
    DistanceTable = Table.AddColumn(Source, "Distance", each 
        let
            Lat2 = [Latitude] * Number.PI/180,
            Lon2 = [Longitude] * Number.PI/180,
            DLat = Lat2 - CurrentLat,
            DLon = Lon2 - CurrentLon,
            A = Number.Sin(DLat/2)^2 + Number.Cos(CurrentLat)*Number.Cos(Lat2)*Number.Sin(DLon/2)^2,
            C = 2*Number.Atan2(Number.Sqrt(A), Number.Sqrt(1-A)),
            Distance = 6371*C // 地球半径取6371公里
        in
            Distance
    ),
    // 排除自身，统计距离超151的数量
    Count = List.Count(Table.SelectRows(DistanceTable, each [Place] <> CurrentPlace and [Distance] > 151)[Place])
in
    Count

3. 关闭Power Query并上载数据，就能看到带新列的表格。

方案2：DAX计算列 #

1. 在Power BI模型中选中原始数据表，新建计算列，命名为Bigger Than 151，输入以下DAX公式：

Bigger Than 151 = 
VAR CurrentLat = 'Table'[Latitude] * PI()/180
VAR CurrentLon = 'Table'[Longitude] * PI()/180
VAR DistanceTable = 
    ADDCOLUMNS(
        FILTER('Table', 'Table'[Place] <> EARLIER('Table'[Place])),
        "Distance", 
        6371 * 2 * ATAN2(
            SQRT(
                SIN(([Latitude] * PI()/180 - CurrentLat)/2)^2 + 
                COS(CurrentLat) * COS([Latitude] * PI()/180) * SIN(([Longitude] * PI()/180 - CurrentLon)/2)^2
            ),
            SQRT(1 - (
                SIN(([Latitude] * PI()/180 - CurrentLat)/2)^2 + 
                COS(CurrentLat) * COS([Latitude] * PI()/180) * SIN(([Longitude] * PI()/180 - CurrentLon)/2)^2
            ))
        )
    )
RETURN
    COUNTROWS(FILTER(DistanceTable, [Distance] > 151))

2. 公式输入完成后，计算列自动生成，表格更新为目标格式。

方案3：Python（Power BI集成） #

1. 进入Power Query，点击「转换」选项卡中的「运行Python脚本」。
2. 粘贴以下代码（需提前用pip install haversine安装haversine库）：

import pandas as pd
from haversine import haversine

df = dataset

def count_over_threshold(row):
    current_coords = (row['Latitude'], row['Longitude'])
    count = 0
    for _, other_row in df.iterrows():
        if row['Place'] == other_row['Place']:
            continue
        other_coords = (other_row['Latitude'], other_row['Longitude'])
        distance = haversine(current_coords, other_coords, unit='km')
        if distance > 151:
            count +=1
    return count

df['Bigger Than 151'] = df.apply(count_over_threshold, axis=1)

print(df)

3. 运行脚本后，Power Query会加载处理好的数据，关闭并上载即可。

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