Pandas：基于多列对数据表的行运行计算，并将输出存储在新列中

Question

我试图计算两个地点的距离，我得到了两个目的地的经度和纬度。在我的CSV中，我有4列（lat1、lon1、lat2、lon2），我如何应用下面的代码，以便创建第5列“距离”，并使用下面的代码计算距离

import math
from math import sin, cos, sqrt, atan2, radians

# approximate radius of earth in km
R = 6373.0

#Test
lat1 = radians(25.2296756)
lon1 = radians(36.0122287)
lat2 = radians(51.406374)
lon2 = radians(20.9251681)

dlon = lon2 - lon1
dlat = lat2 - lat1

a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
c = 2 * atan2(sqrt(a), sqrt(1 - a))

distance = R * c

print("Result:", distance)
print("Should be:", 3181.11, "km")

数据帧：

df = pd.DataFrame({'Normalised': {(0, 'London,', 'United', 'Kingdom'): '-',
  (1, 'Johannesburg,', 'South', 'Africa'): '-',
  (2, 'London,', 'United', 'Kingdom'): '-',
  (3, 'Johannesburg,', 'South', 'Africa'): '-',
  (4, 'London,', 'United', 'Kingdom'): '-'},
 'City': {(0, 'London,', 'United', 'Kingdom'): 'New',
  (1, 'Johannesburg,', 'South', 'Africa'): 'London,',
  (2, 'London,', 'United', 'Kingdom'): 'New',
  (3, 'Johannesburg,', 'South', 'Africa'): 'London,',
  (4, 'London,', 'United', 'Kingdom'): 'Singapore,'},
 'Pair': {(0, 'London,', 'United', 'Kingdom'): 'York,',
  (1, 'Johannesburg,', 'South', 'Africa'): 'United',
  (2, 'London,', 'United', 'Kingdom'): 'York,',
  (3, 'Johannesburg,', 'South', 'Africa'): 'United',
  (4, 'London,', 'United', 'Kingdom'): 'Singapore'},
 'Departure': {(0, 'London,', 'United', 'Kingdom'): 'United',
  (1, 'Johannesburg,', 'South', 'Africa'): 'Ki...',
  (2, 'London,', 'United', 'Kingdom'): 'United',
  (3, 'Johannesburg,', 'South', 'Africa'): 'Ki...',
  (4, 'London,', 'United', 'Kingdom'): 'SIN'},
 'Code': {(0, 'London,', 'United', 'Kingdom'): 'Stat.',
  (1, 'Johannesburg,', 'South', 'Africa'): 'JNB',
  (2, 'London,', 'United', 'Kingdom'): 'Stat',
  (3, 'Johannesburg,', 'South', 'Africa'): 'JNB',
  (4, 'London,', 'United', 'Kingdom'): 'LHR'},
 'Arrival': {(0, 'London,', 'United', 'Kingdom'): 'LHR',
  (1, 'Johannesburg,', 'South', 'Africa'): 'LHR',
  (2, 'London,', 'United', 'Kingdom'): 'LHR',
  (3, 'Johannesburg,', 'South', 'Africa'): 'LHR',
  (4, 'London,', 'United', 'Kingdom'): '1.3'},
 'Code.1': {(0, 'London,', 'United', 'Kingdom'): 'JFK',
  (1, 'Johannesburg,', 'South', 'Africa'): '-26.1',
  (2, 'London,', 'United', 'Kingdom'): 'JFK',
  (3, 'Johannesburg,', 'South', 'Africa'): '-26.1',
  (4, 'London,', 'United', 'Kingdom'): '103.98'},
 'Departure_lat': {(0, 'London,', 'United', 'Kingdom'): 51.5,
  (1, 'Johannesburg,', 'South', 'Africa'): 28.23,
  (2, 'London,', 'United', 'Kingdom'): 51.5,
  (3, 'Johannesburg,', 'South', 'Africa'): 28.23,
  (4, 'London,', 'United', 'Kingdom'): 51.47},
 'Departure_lon': {(0, 'London,', 'United', 'Kingdom'): -0.45,
  (1, 'Johannesburg,', 'South', 'Africa'): 51.47,
  (2, 'London,', 'United', 'Kingdom'): -0.45,
  (3, 'Johannesburg,', 'South', 'Africa'): 51.47,
  (4, 'London,', 'United', 'Kingdom'): -0.45},
 'Arrival_lat': {(0, 'London,', 'United', 'Kingdom'): 40.64,
  (1, 'Johannesburg,', 'South', 'Africa'): -0.45,
  (2, 'London,', 'United', 'Kingdom'): 40.64,
  (3, 'Johannesburg,', 'South', 'Africa'): -0.45,
  (4, 'London,', 'United', 'Kingdom'): np.nan},
 'Arrival_lon': {(0, 'London,', 'United', 'Kingdom'): -73.79,
  (1, 'Johannesburg,', 'South', 'Africa'): np.nan,
  (2, 'London,', 'United', 'Kingdom'): -73.79,
  (3, 'Johannesburg,', 'South', 'Africa'): np.nan,
  (4, 'London,', 'United', 'Kingdom'): np.nan}})

Answer 1

你没有提供数据，所以我根据你的问题编了我自己的；只需在列上使用这些函数的numpy版本

import pandas as pd
import numpy as np

row = pd.Series({
    "lat1": 25.2296756,
    "lon1": 36.0122287,
    "lat2": 51.406374,
    "lon2": 20.9251681
})
df = pd.concat([row]*5, axis=1).T.apply(np.radians)

df["dlon"] = df.lon2 - df.lon1
df["dlat"] = df.lat2 - df.lat1

R = 6373
a = np.sin(df.dlat / 2)**2 + np.cos(df.lat1) * np.cos(df.lat2) * np.sin(df.dlon / 2)**2
c = 2 * np.arctan2(np.sqrt(a), np.sqrt(1 - a))
df["distance"] = R*c

生成的数据帧如下所示：

       lat1      lon1     lat2      lon2      dlon     dlat    distance
0  0.440341  0.628532  0.89721  0.365213 -0.263319  0.45687  3181.11039
1  0.440341  0.628532  0.89721  0.365213 -0.263319  0.45687  3181.11039
2  0.440341  0.628532  0.89721  0.365213 -0.263319  0.45687  3181.11039
3  0.440341  0.628532  0.89721  0.365213 -0.263319  0.45687  3181.11039
4  0.440341  0.628532  0.89721  0.365213 -0.263319  0.45687  3181.11039

Answer 2

可以为距离计算定义自定义函数。然后，使用^{}调用并在每一行上应用该函数，以获取每一行的距离

1。定义用于距离计算的自定义函数，如下所示：

import math
from math import sin, cos, sqrt, atan2, radians

def get_distance(in_lat1, in_lon1, in_lat2, in_lon2):
    # approximate radius of earth in km
    R = 6373.0

    lat1 = radians(in_lat1)
    lon1 = radians(in_lon1)
    lat2 = radians(in_lat2)
    lon2 = radians(in_lon2)

    dlon = lon2 - lon1
    dlat = lat2 - lat1

    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
    c = 2 * atan2(sqrt(a), sqrt(1 - a))

    distance = R * c

    return distance

2。使用^{}调用并在每行上应用函数，以获取每行的距离，如下所示：

df['Distance'] = df.apply(lambda x: get_distance(x['Departure_lat'], x['Departure_lon'], x['Arrival_lat'], x['Arrival_lon']), axis=1)

演示

输入数据帧

        City  Departure_lat  Departure_lon  Arrival_lat  Arrival_lon
0  CityName1      25.229676      36.012229    51.406374    20.925168

输出

        City  Departure_lat  Departure_lon  Arrival_lat  Arrival_lon    Distance
0  CityName1      25.229676      36.012229    51.406374    20.925168  3181.11039

Answer 3

您可以将dlon、dlat、a和c全部设置为一些临时列，然后从这些临时列开始计算（或者将它们全部放在一个难以读取的行中）

比如：

df['dlon'] = df['Arrival_lon'] - df['Departure_lon']
df['dlat'] = df['Arrival_lat'] - df['Departure_lat']

df['a'] = sin(df['dlat'] / 2)**2 + cos(df['Departure_lat']) * cos(df['Arrival_lat']) * sin(df['dlon'] / 2)**2
df['c'] = 2 * atan2(sqrt(df['a']), sqrt(1 - df['a']))

df['distance'] = R * df['c']

然后，如果需要，您可以.drop()所有这些额外的列，但这将创建df['distance']，作为为每行计算的新列

如果我在代码中有输入错误，我不会感到惊讶，但希望你能理解。每个df[xxx] = 行组成一个新列