如何通过Pandas GroupBy按规则修正地点组内的错误经纬度值？

Hey there! Let's break this down step by step since you're new to Pandas GroupBy — it's totally manageable once you get the hang of it. Here's a tailored solution for your exact use case:

1. First: Flag Site Consistency (Skip BR Groups) #

First, we need to identify which groups have consistent site values (since you mentioned BR groups have inconsistent site strings and can't be fixed, we'll skip those entirely).

We'll create a helper function to check each group's site consistency, then tag every row with the status:

def check_site_consistency(group):
    # Skip any group where 'BR' appears in site (per your note)
    if group['site'].str.contains('BR').any():
        return 'skip'
    # Check if all site values in the group are identical
    if group['site'].nunique() == 1:
        return 'consistent'
    else:
        return 'inconsistent'

# Add a site status column to the original dataframe
df['site_status'] = df.groupby('location_name')['site'].apply(check_site_consistency)

2. Fix Lat/Long for Valid Groups #

Next, we'll write a function to correct lat/long values only for groups that:

• Have consistent site values
• Have more than 3 records
• Only have 1 outlier lat/long value (matching your condition)

Here's the function to handle the corrections:

def fix_lat_long(group):
    # Skip groups that aren't eligible for correction
    site_status = group['site_status'].iloc[0]
    if site_status != 'consistent' or len(group) <= 3:
        return group
    
    # Fix Latitude: Replace single outlier with the most common value
    lat_value_counts = group['lat'].value_counts()
    # Check if there's a clear majority (only one outlier)
    if len(lat_value_counts) >= 2 and lat_value_counts.iloc[1] == 1:
        dominant_lat = lat_value_counts.index[0]
        # Replace the outlier rows
        group.loc[group['lat'] != dominant_lat, 'lat'] = dominant_lat
    
    # Fix Longitude: Same logic as latitude
    long_value_counts = group['long'].value_counts()
    if len(long_value_counts) >= 2 and long_value_counts.iloc[1] == 1:
        dominant_long = long_value_counts.index[0]
        group.loc[group['long'] != dominant_long, 'long'] = dominant_long
    
    return group

# Apply the fix across all location groups
fixed_df = df.groupby('location_name').apply(fix_lat_long).reset_index(drop=True)

3. Verify the Corrections #

To make sure everything worked as expected, you can spot-check the results with this snippet:

# Check corrected groups (consistent site + >3 records)
valid_groups = fixed_df[fixed_df['site_status'] == 'consistent'].groupby('location_name')

for location, group in valid_groups:
    if len(group) > 3:
        print(f"Location: {location}")
        print("Unique Lat Values:", group['lat'].unique())
        print("Unique Long Values:", group['long'].unique())
        print("---")

Key Notes for You (As a Pandas Newbie) #

• groupby('location_name').apply() lets us run custom logic on each location's subset of data — think of it as looping through each location group, fixing it, then putting everything back together.
• value_counts() is perfect here because it sorts values by how often they appear, so the first result is the "mainstream" value you want to use.
• BR groups are completely untouched (we marked them as 'skip' so the fix function leaves them alone), which matches your requirement.

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