gpt4 book ai didi

python - 循环遍历 2 个 Pandas Dataframes 并将行值传递给计算距离的函数

转载 作者:太空宇宙 更新时间:2023-11-04 04:31:13 24 4
gpt4 key购买 nike

import pandas as pd
dict = {'Origin Region': [1.0, 2.0, 3.0, 4.0, 5.0, 1.0, 2.0, 5.0],
'Origin Latitude': [-36.45875, -36.24879, -36.789456, -38.14789, -36.15963, -36.159455, -36.2345, -36.12745],
'Origin Longitude': [145.14563, 145.15987, 145.87456, 146.75314, 145.75483, 145.78458, 145.123654, 145.11111]}

df = pd.DataFrame(dict)

centres_dict = {'Origin Region': [1.0, 2.0, 3.0, 4.0, 5.0],
'Origin Latitude': [-36.25361, -36.78541, -36.74859, -38.74123, -36.14538],
'Origin Longitude': [145.12345, 145.36241, 145.12365, 146.75314, 145.75483]}

centres_df = pd.DataFrame(centres_dict)

grouped_region = df.groupby('Origin Region')
for region, region_group in grouped_region:
outliers = region_group[['Origin Latitude', 'Origin Longitude']].where((region_group['Origin Latitude'] < -36.15))
outliers.dropna(inplace=True)
print(outliers)
if(~outliers.empty):
for index, outlier_value in outliers.iterrows():
for another_index, centre_value in centres_df.iterrows():
a = outlier_value['Origin Longitude']
b = outlier_value['Origin Latitude']
c = centres_df['Origin Longitude']
d = centres_df['Origin Latitude']
#find distance using the above and then find minimum distance

我正在尝试遍历数据帧 (df) 的每一组,然后根据某些条件过滤每组中的值,并在每个过滤后的值(离群值)与另一个数据帧中的所有值之间执行距离计算(中心_df)。

我有数据帧中的数据,我应该将它们转换成数组然后使用 scipy cdist 来计算距离吗?或者简单地使用循环并使用我自己的距离计算功能?我不确定执行此操作的最佳方法是什么。或者可以使用 apply 并调用我自己的距离函数?

最佳答案

不需要嵌套循环。只需通过循环将分组的离群值 join 到组内的centres 数据框。然后计算跨列的距离。然后最后,将数据框对象字典中的所有异常值框绑定(bind)在一起。

但是,要使用内置的 math 库对您的过程进行矢量化,这个 Python Haversine Formula 必须经过 numpy 化。

Numpy 版本的 haversine 公式(接收数组/系列而不是标量作为输入)

def haversine_np(lon1, lat1, lon2, lat2):
"""
Calculate the great circle distance between two points
on the earth (specified in decimal degrees)
"""
# convert decimal degrees to radians
lon1, lat1, lon2, lat2 = map(np.radians, [lon1, lat1, lon2, lat2])

# haversine formula
dlon = lon2 - lon1
dlat = lat2 - lat1
a = np.sin(dlat/2)**2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon/2)**2
c = 2 * np.arcsin(np.sqrt(a))
r = 6371 # Radius of earth in kilometers. Use 3956 for miles

return c * r

Pandas 过程

# SET ORIGIN REGION AS INDEX (FOR LATER JOIN)
centres_df = centres_df.set_index('Origin Region')

df_dict = {}
grouped_region = df.sort_values('Origin Region').groupby('Origin Region')

for region, region_group in grouped_region:
# BUILD OUTLIER DF WITH Origin_Region as INDEX
outliers = region_group[['Origin Latitude', 'Origin Longitude']]\
.where((region_group['Origin Latitude'] < -36.15))\
.dropna()\
.assign(Origin_Region = region)\
.set_index('Origin_Region')

# JOIN OUTLIERS WITH CENTRES DF, KEEPING ONLY MATCHED ROWS
outliers = outliers.join(centres_df, how='inner', lsuffix='', rsuffix='_')

# RUN CALCULATION (SEE NUMPY-IFIED haversine())
outliers['Distance_km'] = haversine_np(outliers['Origin Longitude'], outliers['Origin Latitude'],
outliers['Origin Longitude_'], outliers['Origin Latitude_'])

outliers['Origin Region'] = region

# ASSIGN TO DICTIONARY, RE-ORDERING COLUMNS
df_dict[region] = outliers.reindex(outliers.columns[[5,0,1,2,3,4]], axis='columns')

# CONCATENATE OUTSIDE LOOP FOR SINGLE OBJECT
final_df = pd.concat(df_dict, ignore_index=True)

输出

print(final_df)

# Origin Region Origin Latitude Origin Longitude Origin Latitude_ Origin Longitude_ Distance_km
# 0 1.0 -36.458750 145.145630 -36.25361 145.12345 22.896839
# 1 1.0 -36.159455 145.784580 -36.25361 145.12345 60.234887
# 2 2.0 -36.248790 145.159870 -36.78541 145.36241 62.354177
# 3 2.0 -36.234500 145.123654 -36.78541 145.36241 64.868402
# 4 3.0 -36.789456 145.874560 -36.74859 145.12365 67.040011
# 5 4.0 -38.147890 146.753140 -38.74123 146.75314 65.976398
# 6 5.0 -36.159630 145.754830 -36.14538 145.75483 1.584528

关于python - 循环遍历 2 个 Pandas Dataframes 并将行值传递给计算距离的函数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52615950/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com