Flightradar24熊猫分组和矢量化.一个无循环的解

Question

我希望对flightradar数据执行快速操作，以查看距离中的速度是否与报告的速度相匹配。我有多次飞行，被告知不要在熊猫数据框上运行双循环。下面是一个示例数据框架:

import pandas as pd
from datetime import datetime
from shapely.geometry import Point
from geopy.distance import distance
dates = ['2020-12-26 15:13:01', '2020-12-26 15:13:07','2020-12-26 15:13:19','2020-12-26 15:13:32','2020-12-26 15:13:38']
datetimes = [datetime.fromisoformat(date) for date in dates]
data = {'UTC': datetimes, 
'Callsign': ["1", "1","2","2","2"],
'Position':[Point(30.542175,-91.13999200000001), Point(30.546204,-91.14020499999999),Point(30.551443,-91.14417299999999),Point(30.553909,-91.15136699999999),Point(30.554489,-91.155075)]
}
df = pd.DataFrame(data)

我要做的是添加一个名为"dist"的新列。如果这一列是新呼号的第一个元素，它将为0;如果不是，它将是一个点与前一个点之间的距离。

结果df应该是这样的:

df1 = df
dist = [0,0.27783309075379214,0,0.46131362750613436,0.22464461718704595]
df1['dist'] = dist

我所尝试的是首先分配一个组索引:

df['group_index'] = df.groupby('Callsign').cumcount()

然后groupby

然后尝试应用函数:

df['dist'] = df.groupby('Callsign').apply(lambda g: 0 if g.group_index == 0 else distance((g.Position.x , g.Position.y),
(g.Position.shift().x , g.Position.shift().y)).miles)

我希望这会给我每个组的第一个索引0，然后对所有其他组运行距离函数并返回以英里为单位的值。然而，它不工作。

代码出错至少有一个原因，即shaely对象的。x和。y属性是在序列上而不是在对象上调用的。

任何关于如何解决这个问题的想法将不胜感激。

Answer 1

1. 按呼号和时间戳排序
2. 使用移位点的临时列计算相邻行之间的距离
3. 对于每个新呼号的第一行，将距离设置为0
删除临时列

df = df.sort_values(by=['Callsign', 'UTC'])
df['Position_prev'] = df['Position'].shift().bfill()
def get_dist(row):
return distance((row['Position'].x, row['Position'].y),
(row['Position_prev'].x, row['Position_prev'].y)).miles
df['dist'] = df.apply(get_distances, axis=1)
# Flag row if callsign is different from previous row callsign
new_callsign_rows = df['Callsign'] != df['Callsign'].shift()
# Zero out the first distance of each callsign group
df.loc[new_callsign_rows, 'dist'] = 0.0
# Drop shifted column
df = df.drop(columns='Position_prev')
print(df)
UTC Callsign                              Position      dist
0 2020-12-26 15:13:01        1  POINT (30.542175 -91.13999200000001)  0.000000
1 2020-12-26 15:13:07        1  POINT (30.546204 -91.14020499999999)  0.277833
2 2020-12-26 15:13:19        2  POINT (30.551443 -91.14417299999999)  0.000000
3 2020-12-26 15:13:32        2  POINT (30.553909 -91.15136699999999)  0.461314
4 2020-12-26 15:13:38        2          POINT (30.554489 -91.155075)  0.224645