基于另一DataFrame距离区间为GPS DataFrame添加地形列的优化求助

Hey there! Let's ditch that slow loop and optimize your terrain matching logic—vectorized operations in pandas/numpy will make this way faster, especially with large datasets.

First, let's clarify your original loop logic to make sure we replicate it correctly:

• You're using total_dist (cumulative distance) from GPS_df to match against dist nodes in terrain_df
• For each GPS point, if its cumulative distance is ≤ the current terrain dist node, assign that terrain type; otherwise, move to the next terrain node and assign its type
• This assumes terrain_df['dist'] is sorted in ascending order (your loop relies on this to increment i correctly)

Why Your Previous Attempts Failed #

1. np.digitize mistake: You used GPS_df['dist'] (single segment distance) instead of GPS_df['total_dist'] (cumulative distance)—that's the core issue! Also, your dictionary started enumerating from 1 but np.digitize returns indices starting at 0.
2. pd.cut issue: You probably missed setting up complete bins (like adding a starting 0 and ending inf to cover all ranges) or didn't sort the bins first.

Solution 1: pd.merge_asof (Most Intuitive) #

merge_asof is built exactly for this kind of "forward/backward nearest match" scenario. It's efficient and handles edge cases seamlessly.

# First, ensure both DataFrames are sorted by their distance keys
GPS_df_sorted = GPS_df.sort_values('total_dist').reset_index(drop=True)
terrain_df_sorted = terrain_df.sort_values('dist').reset_index(drop=True)

# Perform the asof merge: match each total_dist to the first terrain dist that is >= it
merged = pd.merge_asof(
    GPS_df_sorted,
    terrain_df_sorted,
    left_on='total_dist',
    right_on='dist',
    direction='forward'  # Find the smallest right key >= left key
)

# Assign the terrain back to your original GPS_df (restore original order if needed)
GPS_df['terrain'] = merged['terrain'].reindex(GPS_df.index)

# Handle cases where total_dist exceeds the largest terrain dist (fill with last terrain type)
last_terrain = terrain_df_sorted['terrain'].iloc[-1]
GPS_df['terrain'] = GPS_df['terrain'].fillna(last_terrain)

Solution 2: np.digitize (Fastest for Large Datasets) #

If you prefer a numpy-based approach, fix the earlier digitize code to use the correct column and indices:

# Ensure terrain_df is sorted by dist
terrain_df_sorted = terrain_df.sort_values('dist').reset_index(drop=True)
bins = terrain_df_sorted['dist'].values
terrain_labels = terrain_df_sorted['terrain'].values

# Get indices for each total_dist in GPS_df
indices = np.digitize(GPS_df['total_dist'], bins)

# Handle cases where total_dist exceeds the largest bin (map to last terrain type)
indices[indices >= len(terrain_labels)] = len(terrain_labels) - 1

# Assign terrain labels
GPS_df['terrain'] = terrain_labels[indices]

Solution 3: Fixed pd.cut #

If you want to use pd.cut, set up complete bins that cover all possible total_dist values:

# Create bins: start at 0, include all terrain dist nodes, end at infinity
bins = [0] + terrain_df['dist'].tolist() + [float('inf')]
# Terrain labels should match the number of intervals (len(bins)-1)
labels = terrain_df['terrain'].tolist()

# Assign terrain with pd.cut
GPS_df['terrain'] = pd.cut(
    GPS_df['total_dist'],
    bins=bins,
    labels=labels,
    include_lowest=True  # Ensure the first bin includes 0
)

All three methods are fully vectorized and will outperform your loop by orders of magnitude, especially with large GPS datasets.

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