removing similar data after grouping and sorting python

user_17035737 0
Viewed 60

I have this data:

lat = [79.211, 79.212, 79.214, 79.444, 79.454, 79.455, 82.111, 82.122, 82.343, 82.231, 79.211, 79.444]
lon = [0.232,  0.232,  0.233,  0.233,  0.322,  0.323,  0.321,  0.321,  0.321,  0.411,  0.232,  0.233]
val = [2.113,  2.421,  2.1354, 1.3212, 1.452,  2.3553, 0.522,  0.521,  0.5421, 0.521,  1.321,  0.422]

df = pd.DataFrame({"lat": lat, 'lon': lon, 'value':val})

and I am grouping it by lat & lon and then sorting by the value column and taking the top 5 as shown below:

grouped = df.groupby(["lat", "lon"])
val_max = grouped['value'].max()
df_1 = pd.DataFrame(val_max)
df_1  = df_1.sort_values('value', ascending = False)[0:5]

The output I get is this:


                value
lat     lon 
79.212  0.232   2.4210
79.455  0.323   2.3553
79.214  0.233   2.1354
79.211  0.232   2.1130
79.454  0.322   1.4520

I want to remove any row that is within 1 of the last decimal place of any of the above. So we see that row 1 is almost the same location as row 4 and row 2 is almost the same location as row 5 so 4 and 5 would be replaced by the next ranked lat lon, which would make the output:

                value
lat     lon 
79.212  0.232   2.4210
79.455  0.323   2.3553
79.214  0.233   2.1354
82.343  0.321   0.5421
82.111  0.321   0.5220

Please le me know how I can do this.

pandas dataframe numpy python
1 Answers

You could sort the dataframe, like this:

grouped = df.groupby(["lat", "lon"])
val_max = grouped["value"].max()
df_1 = pd.DataFrame(val_max)
df_1 = (
    df_1.sort_values("value", ascending=False).reset_index().sort_values(["lat", "lon"])
)

Then, iterate on each row and compare it to the previous one, find and drop similar ones :

# Find similar rows and mark them in a new "match" column
df_1["match"] = ""
for i in range(df_1.shape[0] + 1):
    if i == 0:
        continue
    df_1.loc[
        (df_1.iloc[i, 0] - df_1.iloc[i - 1, 0] <= 0.001)
        | (df_1.iloc[i, 1] - df_1.iloc[i - 1, 1] <= 0.001),
        "match",
    ] = pd.NA

# Remove empty rows
df_1 = df_1.dropna(how="all").reset_index(drop=True)

# Remove unwanted rows and cleanup
index = [i - 1 for i in df_1[df_1["match"].isna()].index]
df_1 = df_1.drop(index=index).drop(columns="match").reset_index(drop=True)

Which outputs:

print(df_1)

      lat    lon   value
0  79.212  0.232  2.4210
1  79.214  0.233  2.1354
2  79.444  0.233  1.3212
3  79.455  0.323  2.3553
4  82.111  0.321  0.5220
5  82.122  0.321  0.5210
6  82.231  0.411  0.5210
7  82.343  0.321  0.5421
user_112460560
Related