Skip to main content
 首页 » 编程设计

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

2024年12月31日13daizhj

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